New interesting features are available in 11G related to statistics collection
- New statistics preferences
- Hash based sampling for column statistics
- Multicolumn statistics
- Expression statistics
The Hakan factor is used to unique map rows in a Bitmap Index to the rows in the base table. This factor is related to the max number of rows that can be stored in a single block of a table. Several factors like the column type and not null constraints influence the Hakan factor. The factor will also be recalculated when a table is modified until there is the first bitmap Index is created. Than the Hakan factor has to be protected for the existing bitmap indexes.
If a new table created to exchange data with the partitioned table, with a table layout, that include columns added after the bitmap index creation on the partitioned table, will most likely result in a different Hakan factor.
If the Hakan Factor do not match during exchange partitions, the prognosis is either of the error messages below:
- ORA-14642: “Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
- ORA-14643: “Hakan factor mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
The error can appear only if you exchange a partition with bitmap indexes
In previous release 11G to manage database you could use Oracle Enterprise Database Console. In Oracle 12C it is replaced by new lightweight administration tool Enterprise Manager Database Express.
Features of the new version:
- out-of-box simple management for single database (or database cluster)
- small footprint 50-100 MB in database
- minimal requirements for CPU in database(sql calls), rendering is done on client side
- simple configuration
- performance monitoring
- configuration management
- diagnostic and tuning
- interface in web browser(requires flash plugin)
If you want to load data in direct mode into a table you should set all dependent indexes in UNUSABLE state. Session parameter SKIP_UNUSABLE_INDEXES set to TRUE enables to ignore indexes with status UNUSABLE during direct load.
However If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.
This article presents how to install Suse Linux Enterprise Server SP3 – service pack 3.
I assume you have already downloaded Suse Linux Enterprise Server SP3 64 bit(it’s about 4GB) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Suse openSuse 64 bit. 1GB ram(I set 4GB for future use) and 64G for disk is enough plus increase video memory to 128M and turn on acceleration 3D. Rest of options you can keep default.
In case you want to collect trace statistics you can do it in many ways in Oracle. One of the most popular method is by setting parameter event with value sql_trace on session level.
alter session set events 'sql_trace level <trace_level>'
This article shows how to load text or binary files into Oracle database table column using sqlldr tool. It’s very useful and common practices.
With 11g release 1 Oracle has introduced new package DBMS_HPROF. It’s very useful for DBAs and developers to analyze execution of PL/SQL code. It enables to gather, store and organize a lot of information about executed code.
Informations gathered by this package are supported by Oracle sqldeveloper which is another great news :).
The PL/SQL hierarchical profiler features:
- easy to install and use
- collect execution times for SQL and PL/SQL code
- keeps results in database tables
- Provides subprogram-level execution summary information, such as:
- Number of calls to the subprogram
- Time spent in the subprogram itself
- Time spent in the subprogram itself and in its descendent’s subprograms
- Detailed parent-children information, for example:
- All callers of a given subprogram (parents)
- All subprograms that a given subprogram called (children)
- How much time was spent in subprogram x when called from y
- How many calls to subprogram x were from y
It’s no new feature in 11g but it’s worth to describe it. Package DBMS_TRACE enables to trace execution of PL/SQL code in database.Oracle collects the trace data as the program executes and writes it to database tables.
New enhancements are added in 11g for dynamic SQL in PL/SQL :
- SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
- CLOB can be used for DBMS_SQL.PARSE
- DBMS_SQL cursor can be converted to REF CURSOR and vice versa
- Mored data types(including object types, collections) are supported in DBMS_SQL