|
|
Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs
Page 2 of 7
Cardinality Feedback -- a feature introduced in Version 11g -- monitors the execution of SQL statements and reoptimizes if the actual cardinality, such as the number of rows returned from the query, varies greatly from the cardinality estimates. A new feature in 12c called Adaptive Plan takes the next step in SQL auto-tuning. Instead of choosing the final execution plan at parse time, Optimizer defers the final choice among multiple sub-plans until execution time.
Essentially, Optimizer introduces a piece of code, aptly named Statistics Collector, into SQL execution. Statistics Collector buffers rows from early steps in the execution plan. Depending upon the number of rows retrieved, the Optimizer chooses the final execution plan. The chosen plan will be reused for subsequent executions of the statement if the cursor is shared. A just-in-time Optimizer!
Adaptive Reoptimization, similar to the Cardinality Feedback feature, affects only the subsequent executions of a SQL statement. If the Optimizer estimates are vastly different from the execution statistics, then the Optimizer uses the execution statistics as a feedback mechanism and reparses the SQL statement during the next execution.
Generally, the quality of the statistics directly equates to the quality of execution plans generated by the Optimizer, bugs notwithstanding. In Version 12c, if the quality of available statistics is not good enough, then the Optimizer can dynamically sample the tables to recollect statistics. This dynamic statistics collection uses the same methods as dynamic sampling available in earlier releases, except that, in Database 12c, these statistics are also stored for future use.
Performance features
Version 12c introduces numerous performance enhancements. I will review a few of the more important ones.
Traditionally, queries with union or union all branches execute one after another, meaning that one branch of the union or union all is executed, followed by the next branch, and so on. Version 12c introduces concurrent execution of union branches, meaning that one set of parallel servers will be executing one branch, a second set of parallel servers will be executing a different union all branch, and so on, all at the same time.
This concurrent execution feature will be very useful if the majority of the query execution time is spent outside of the database, such as when waiting for a SQL*Net message from a remote database link or for an SOA call response. The effective use of this new feature could reduce wait time dramatically, improving SQL elapsed time. (Incidentally, with Version 12c, SQL*Net packets can be compressed for database traffic, helping to reduce latency in a WAN environment.)
A classic problem with parallelism is that all of the parallel servers required for an operation may not be available at the moment of parallel statement initiation, leading to parallel statements executing across a smaller number of parallel servers. Parallel statement queuing -- a feature introduced in Version 11.2 -- resolved the problem by queuing up the sessions whenever sufficient parallel servers were not available. With Database 12c the user can construct multiple parallel statement queues using the database resource manager, bypass parallel statement queueing for critical statements, and group multiple parallel statement together to reduce wait time in parallel statement queues.