If you are already satisfied with current query performance, reanalyzing a schema could cause significant performance degradation and ruin the tuning efforts of the development staff. Very few Oracle installations are dynamic enough to require frequent schema reanalysis. Rarely change CBO parameters —Changing the Optimizer initialization parameters can be risky because a single parameter change could have an adverse influence on the performance of the entire database.
Changing vital parameters on a production system should only be done after careful evaluation and testing. Require developers to tune their SQL —Uns-avvy developers falsely assume that their singular goal is to compose SQL statements that deliver correct query results. On the other hand, a shrewd developer realizes that formulating the SQL is only half the job and takes steps to ensure that SQL accesses the database in an optimal manner. To enforce compliance across the board, successful organizations require a formal review of the execution plan for all new SQL before production migration can occur.
When you are responsible for administering these types of systems, it may be prudent to reanalyze the schema each time the database is reloaded with new data. Highly volatile tables —In these environments, the size of tables and the characteristics of index columns change dramatically. If, for example, you have a table that has rows one day and , rows a few days later, then you probably should consider a periodic reanalysis of the schema statistics.
The best developer is not necessarily the one producing the most lines of SQL code in the least amount of time. Instead, an effective developer is the one who writes SQL that performs optimally. Strict Management —Require that all SQL that is migrating into production first undergo a formal evaluation of the execution plan to verify that the SQL has been optimally tuned. Oracle University has excellent classes on CBO optimization techniques.
Search BC Oracle Sites. Listing A. A subplan is a portion of a plan that the optimizer can switch to as an alternative at run time. For example, a nested loops join could be switched to a hash join during execution. An optimizer statistics collector is a row source inserted into a plan at key points to collect run-time statistics. These statistics help the optimizer make a final decision between multiple subplans. During statement execution, the statistics collector gathers information about the execution, and buffers some rows received by the subplan.
Based on the information observed by the collector, the optimizer chooses a subplan. At this point, the collector stops collecting statistics and buffering rows, and permits rows to pass through instead. On subsequent executions of the child cursor, the optimizer continues to use the same plan unless the plan ages out of the cache, or a different optimizer feature for example, adaptive cursor sharing or statistics feedback invalidates the plan. This example shows how the optimizer can choose a different plan based on information collected at runtime.
An adaptive query plan for this statement shows two possible plans, one with a nested loops join and the other with a hash join:. If few rows are filtered, however, then scanning the right table in a hash join is preferable. The following graphic shows the adaptive process. For the query in the preceding example, the adaptive portion of the default plan contains two subplans, each of which uses a different join method.
The optimizer automatically determines when each join method is optimal, depending on the cardinality of the left side of the join. If the row count is below the threshold determined by the optimizer, then the optimizer chooses the nested loops join; otherwise, the optimizer chooses the hash join. The Note section of the execution plan indicates whether the plan is adaptive, and which rows in the plan are inactive. Typically, parallel execution requires data redistribution to perform operations such as parallel sorts, aggregations, and joins. Oracle Database can use many different data distributions methods.
The database chooses the method based on the number of rows to be distributed and the number of parallel server processes in the operation.
The database may choose the broadcast distribution method. In this case, each parallel server process receives each row in the result set. If a data skew is encountered during the data redistribution, then it could adversely affect the performance of the statement. The database is more likely to pick a hash distribution to ensure that each parallel server process receives an equal number of rows. The hybrid hash distribution technique is an adaptive parallel data distribution that does not decide the final data distribution method until execution time.
The optimizer inserts statistic collectors in front of the parallel server processes on the producer side of the operation. If the number of rows is less than a threshold, defined as twice the degree of parallelism DOP , then the data distribution method switches from hash to broadcast. Otherwise, the distribution method is a hash. The following graphic depicts a hybrid hash join between the departments and employees tables, with a query coordinator directing 8 parallel server processes: P5-P8 are producers, whereas P1-P4 are consumers.
Each producer has its own consumer. The database inserts a statistics collector in front of each producer process scanning the departments table. The query coordinator aggregates the collected statistics. The distribution method is based on the run-time statistics. In Figure , the number of rows is below the threshold 8 , which is twice the DOP 4 , so the optimizer chooses a broadcast technique for the departments table.
Consider an example that returns a greater number of rows. In the following plan, the threshold is 8, or twice the specified DOP of 4.
11 SQL Tuning Overview
However, because the statistics collector Step 10 discovers that the number of rows 27 is greater than the threshold 8 , the optimizer chooses a hybrid hash distribution rather than a broadcast distribution. The time column should show , but shows so the plan can fit the page. When the optimizer generates a star transformation plan, it must choose the right combination of bitmap indexes to reduce the relevant set of rowids as efficiently as possible. If many indexes exist, some indexes might not reduce the rowid set substantially, but nevertheless introduce significant processing cost during query execution.
Adaptive plans can solve this problem by not using indexes that degrade performance. In this example, you issue the following star query, which joins the cars fact table with multiple dimension tables sample output included :. The following sample execution plan shows that the query generated no rows for the bitmap node in Step 12 and Step The query did not use the steps in the plan that begin with a dash -. The optimizer can use adaptive statistics when query predicates are too complex to rely on base table statistics alone. Dynamic Statistics.
Automatic Reoptimization. SQL Plan Directives. During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate an optimal execution plan. If the available statistics are insufficient, then the optimizer uses dynamic statistics to augment the statistics. One type of dynamic statistics is the information gathered by dynamic sampling. The optimizer can use dynamic statistics for table scans, index access, joins, and GROUP BY operations, thus improving the quality of optimizer decisions.
In automatic reoptimization , the optimizer changes a plan on subsequent executions after the initial execution.
- ApexSQL Plan.
- Clarkesworld, Issue 98 (December 2014).
- Tragedy Is Not Enough.
- Tips for SQL Database Tuning and Performance | Toptal.
Adaptive query plans are not feasible for all kinds of plan changes. For example, a query with an inefficient join order might perform suboptimally, but adaptive query plans do not support adapting the join order during execution. At the end of the first execution of a SQL statement, the optimizer uses the information gathered during execution to determine whether automatic reoptimization has a cost benefit. If execution information differs significantly from optimizer estimates, then the optimizer looks for a replacement plan on the next execution. The optimizer uses the information gathered during the previous execution to help determine an alternative plan.
The optimizer can reoptimize a query several times, each time gathering additional data and further improving the plan. A form of reoptimization known as statistics feedback formerly known as cardinality feedback automatically improves plans for repeated queries that have cardinality misestimates. The optimizer can estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates.
Imagine a world with only super-fast queries
The basic process of reoptimization using statistics feedback is as follows:. The optimizer may enable monitoring for statistics feedback for the shared SQL area in the following cases:. Predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates.
At the end of execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution.
If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution. If the query executes again, then the optimizer uses the corrected cardinality estimates instead of its usual estimates. Querying the plan in the cursor shows that the estimated rows E-Rows is far fewer than the actual rows A-Rows.
Querying the plan in the cursor shows that the optimizer used statistics feedback shown in the Note for the second execution, and also chose a different plan. In the preceding output, the estimated number of rows in Step 1 matches the actual number of rows.
How to Read Explain Plans
Another form of reoptimization is performance feedback. The optimizer chooses the degree of parallelism based on the estimated performance of the statement. Additional performance monitoring is enabled for all statements. The degree of parallelism computed based on the performance statistics for example, the CPU time gathered during the actual execution of the statement.
If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution statistics as feedback. This feedback helps better compute the degree of parallelism for subsequent executions. If the query executes again, then the optimizer uses the performance statistics gathered during the initial execution to better determine a degree of parallelism for the statement.
A SQL plan directive is additional information that the optimizer uses to generate a more optimal plan. For example, during query optimization, when deciding whether the table is a candidate for dynamic statistics, the database queries the statistics repository for directives on a table. If the query joins two tables that have a data skew in their join columns, a SQL plan directive can direct the optimizer to use dynamic statistics to obtain an accurate cardinality estimate.
The optimizer collects SQL plan directives on query expressions rather than at the statement level. In this way, the optimizer can apply directives to multiple SQL statements. SQL plan management is a mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans. This capability is central to the SQL plan management architecture.
In SQL plan management, the optimizer has the following main objectives:. Introduction to the Query Optimizer The query optimizer called simply the optimizer is built-in database software that determines the most efficient method for a SQL statement to access requested data. Purpose of the Query Optimizer The optimizer attempts to generate the most optimal execution plan for a SQL statement. Cost-Based Optimization Query optimization is the overall process of choosing the most efficient means of executing a SQL statement.
About Ahmad Yaseen
Note: The optimizer may not make the same decisions from one version of Oracle Database to the next. See Also: " Cost ". At first, they seemed expensive … but their tool solved a problem 3 engineers took a crack at over the course of 4 days. The cost of hours spent before trying EverSQL would have paid for many years of a subscription. Highly recommended!
Just tried out EverSQL , it's actually pretty useful and pretty surprised by the results. We reduced the database load significantly using EverSQL automatic query optimizer. Interesting tool EverSQL! It can generate optimal indexes for SQL queries automatically. We just solved some performance bottlnecks in the application using EverSQL.