Pending Statistics (11.1)

Starting in Oracle 11.1, we have the option to gather statistics without publishing them to the optimizer. When using pending statistics we can allow the optimizer to use these statistics by setting the parameter OPTIMIZER_USE_PENDING_STATISTICS to true. After checking the new statistics we can publish or delete them.

SQL Plan Management (11.1)

SQL Plan Management allows the optimizer to maintain execution plan history for repeating SQL statements. Using this history, the optimizer can prevent plan changes for these SQL statements. The optimizer will save the new plan and will verify that the performance of the new plan is better than the old ones. After the verification, the optimizer can accept the plan and start using it.

Invisible Indexes (11.1)

Invisible indexes are regular indexes that are not used by the optimizer. We can create invisible indexes (or change indexes to be invisible) to verify how a new index or the deletion of an index will change the SQL plans. An index can be set to visible or invisible using the “create index” or “alter index” commands. To allow the optimizer to use invisible indexes, set the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to true.

Real Application Testing (11.1)

Oracle Real Application Testing option contains two components, Database Replay and SQL Performance Analyzer. These features help us to identify performance issues before hardware and software changes.

• Database Replay: Database replay allows us to capture the real workload of the production system and replay it on a test environment. The test environment can have different hardware, Oracle version, parameters, etc. This way we can assess the impact of these changes on our production database.
• SQL Performance Analyzer (SPA): the SPA allows us to identify SQL performance changes between the production and test environments. Using SPA we run the same SQL statements on both environments and see the actual improvement or degradation of these statements in the new environment.

Instance Caging (11.2)

Instance caging in 11.2 allows us to limit the amount of CPUs the instance can use. To use instance caging, we set the CPU_COUNT parameter to the desired amount of CPUs. This feature allows us to have better load sharing when having several instances running on the same server.

Automatic Parallelism (11.2)

Automatic parallelism in 11.2 consists of three features:

• Automatic degree of parallelism: Oracle will decide which statements to run in parallel and the parallel degree
• Statement queuing: long statements that can’t get enough parallel slaves will not be executed in serial, but will be queued until they can get the desired parallel degree
• In memory parallel execution: enables the parallel slaves to use the buffer cache when accessing data blocks, instead of performing direct read from the physical disks.

To fully enable this feature the PARALLEL_DEGREE_POLICY parameter should be set to AUTO. When the parameter is set to LIMITED, only the automatic parallel degree is enabled, and only for queries that we set to run in parallel.

To entirely disable this feature and revert to pre 11.2 behavior, set the PARALLEL_DEGREE_POLICY to MANUAL.