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.
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.
Also new in Version 12c, multiple indexes can be created on the same set of columns. For example, the user can create bitmap and b-tree indexes on the same set of columns, or even create a unique and non-unique index on the same set of columns. Multiple indexes will be useful whenever you want to convert an index from one type to another type, or convert a partitioned index to a nonpartitioned or vice versa, with minimal downtime. Of course, the user can choose to maintain multiple indexes on the same set of columns permanently, too.
In Oracle Database, the concept of a service involves a connection property specified to connect to a desired instance. We commonly use services to balance the workload among the instances of a Real Application Cluster, for example. Version 12c introduces Global Data Services, which balances the workload not only among instances, but also among the databases.
Having trouble installing and setting up Win10? You aren’t alone. Here are many of the most common...
Win7 Update scans got you fuming? Here’s how to make the most of Microsoft’s 'magic' speed-up patch
Picking an Android phone can be difficult, but we're here to help. These are the top Android phones you...
Our dystopian future of machine learning breaking bad is already unfolding before our eyes
After two months with no patches, Tuesday's release introduced several severe bugs, two of which...
Voice-controlled assistants are proliferating, and opening them to third-party app makers is proving to...
A race condition flaw has been fixed in the mainline Linux kernel, but some Red Hat, Canonical, and...