Tag: Explain Plan

Better diagnostics for SQL regressions in 19c and beyond #JoelKallmanDay

When diagnosing and correcting a performance regression for a SQL statement, it can often be challenging to find out what is happening during the execution and why your corrective measures are not working.

In today’s blog, I want to share several enhancements introduced in recent Oracle Database releases to help you better understand how to improve a SQL statement and understand why your corrective measures aren’t working as you hoped.

Enhanced Execution Plan

One of the most valuable tools at our disposal is the execution plan. Over the last several years, the Oracle Optimizer team has made a considerable effort to share even more insights and guidance on what might be impacting the performance of a SQL statement via the execution plan.

The Note section under the plan

The note section under the execution plan contains valuable information on what has happened during parse and execution. Over the last several releases, you will find a lot more information appearing in the note section, including details on the following features:

  • Dynamic Sampling – indicates a non-default value for the parameter OPTIMIZER_DYNAMIC_SAMPLING or that one or more objects referenced in the query are missing statistics.
  • Plan Stability Features – indicates that a store outline, SQL profile, SQL patch SQL directives, or SQL plan baseline influenced the plan chosen.
  • Adaptive plan – indicates that the plan shown through the Explain Plan command may be different from the plan used during execution as the Optimizer will use run-time statistics to make the final decision on what join types and aggregation methods to use. It is best to view the actual plan used via v$SQL_PLAN after the initial execution.
  • Statistics Feedback – indicates the plan was re-evaluated based on previous execution statistics, and a new plan was found.

Hint Usage Reporting in Oracle Database 19c

Starting in 19c, you will see a new hint usage report under the execution plan. The new report gives you a better understanding of what happened to each hint in a SQL statement.

Remember, the database doesn’t issue an error if a hint is invalid or not applicable for a SQL statement. The database will treat the hint as a comment, effectively ignoring it. Previously, the only way to find this information was via a 10053 trace file, and even then, it wasn’t very detailed.

The new hint report shows which hints were used and which were ignored and explains why hints weren’t used. The most common reasons for a hint not to be used are:

  • Syntax errors – the hint contains a typo or an invalid argument.
  • Unresolved hints – the hint contains an invalid argument or is not applicable to the SQL statement. For example, you request an index be used, but no index exists on the table.
  • Unused hints – the hint can’t be used in this specific scenario. For example, you requested a Hash Join for a non-equality join condition.
  • Conflicting hints – multiple hints provide conflicting directives.

In the example below, the hint NO_QURY_TRANSFORMATION was reported to contain a syntax error. The word query is misspelled in the hint, so the hint can’t be used.

---------------------------------------------------------------------------   
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|   
--------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT               |          |       |       |    47 (100)|    
|   1 |  HASH GROUP BY                 |          |   269 | 37929 |    47   (7)|    
|   2 |   HASH JOIN                    |          |   269 | 37929 |    46   (5)|    
|   3 |     TABLE ACCESS STORAGE FULL  | SALES    | 10000 | 90000 |     5   (0)|   
|*  4 |     TABLE ACCESS STORAGE FULL  | PRODUCTS | 43108 |  4841K|    40   (3)|    
--------------------------------------------------------------------------------    
Predicate Information (identified by operation id):                                            
---------------------------------------------------                                            
   4 - access("ITEM_1"="P"."PROD_ID")                                                          
   
Hint Report (identified by operation id / Query Block Name / Object Alias):                    
Total hints for statement: 1 (E - Syntax error (1))
--------------------------------------------------------------------------
 0 -  SEL$1                                                                                  
         E -  NO_QURY_TRANSFORMATION 
Note                                                                                           
-----
- dynamic statistics used: dynamic sampling (level=2)                                       
                                                             

In this second example, I provided two hints on how to access the employees table. One hint requested that the primary key index be used, and the other requested that the access leverage parallel execution.

SELECT /*+ index(e empno_pk_ind) parallel(e 8) */ e.empno, ename 
FROM    employees eWHERE   e.empno < 7700;

----------------------------------------------------------------------------
|Id  | Operation			     | Name	    | Rows | Bytes |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |	            |	   |	   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES    |	 8 |	80 |
|*  2 |   INDEX RANGE SCAN		     | EMPNO_PK_IND |	 8 |	   |
----------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
	 U -  parallel(e 8)

The parallel hint is not used, as I’ve supplied an invalid combination of hints. An index range scan can’t be parallelized unless the index is partitioned, which is not true in this example. Therefore, the Optimizer can not honor both hints.

Note Nigel Bayliss, the Optimizer product manager, has also blogged about Optimizer hint reporting and has shared details on some limitations regarding execution hints such as GATHER_OPTIMIZER_STATISTICS, APPEND, etc.

SQL Analysis Report in Oracle Database 23c

In Oracle Database 23c, the execution plan got another new section: a SQL Analysis Report. This handy addition helps you diagnose common problems that can cause suboptimal execution plans. For example, the new report will point out situations where you are:

  • Missing join conditions
  • Have a WHERE clause predicate that prevents an index from being used
  • Have a datatype mismatch in a WHERE clause predicate
  • Using a UNION instead of a UNION ALL
EXPLAIN PLAN FOR
SELECT * FROM addresses
 WHERE UPPER(state) = 'CA';
 
SELECT * FROM dbms_xplan.display();
 
Plan hash value: 3184888728
 
--------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   239 | 13384 |    61   (0)| 
|*  1 |  TABLE ACCESS FULL| ADDRESSES |   239 | 13384 |    61   (0)| 
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("state")='CA')
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-----------------------------------------------------------------
 
   1 -  SEL$1 / "ADDRESSES"@"SEL$1" 
-  The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "STATE"

Again, Nigel Bayliss has blogged about this in more detail on the official Optimizer blog.

Note that SQL Monitor active reports also contain SQL Analysis reports. You can find them under the new  SQL Analysis tab.

New Optimizer Dictionary Views

Along with enhancements to the execution plan in 23c, we added new data dictionary views to help identify what happens when we parse and execute a SQL statement.

Apps and users frequently change the value for one or more parameters that impact the Optimizer at a session level. As a DBA or performance engineer, you are often unaware of these logon triggers or ALTER SESSION commands. The first hint that the environment may have changed is when you see a plan change.

To check if a change in the optimizer environment caused the plan change, you can check the column OPTIMIZER_ENV_HASH_VALUE in V$SQL, V$SQLAREA, and DBA_HIST_SQLSTAT. However, this column doesn’t tell you what has changed.

Starting in 23c, you can query a new dictionary view, DBA_HIST_OPTIMIZER_ENV_DETAILS, to find out exactly what has changed in the optimizer environment when we parsed and executed a SQL statement.

Using the optimizer_env_hash_value for the original and new plan, you can query DBA_HIST_OPTIMIZER_ENV_DETAILS and get a list of the parameters settings for each scenario, which you can then compare to find the difference.

SELECT DISTINCT optimizer_env_hash_value
FROM  dba_hist_sqlstat
WHERE sql_id = 'bsvavk15n7cra'
ORDER BY 1;

OPTIMIZER_ENV_HASH_VALUE                                                        
------------------------                                                        
              1309615723                                                        
              2369923737 

SELECT name, value
FROM dba_hist_optimizer_env_details
WHERE optimizer_env_hash_value = 1309615723
ORDER BY 1;

NAME                                               VALUE                        
-------------------------------------------------- -------------------------    
BlockChain_ledger_infrastructure                   0                            
CLI_internal_cursor                                0                            
PMO_altidx_rebuild                                 0                            
_adaptive_window_consolidator_enabled              true                         
_add_stale_mv_to_dependency_list                   true  
   .
   .

You can get more information on how to leverage the DBA_HIST_OPTIMIZER_ENV_DETAILS view in MOS NOTE:2953121.1 – Examining the Optimizer Environment within Which a SQL Statement was Parsed in AWR.

Explain the Explain Plan: Join Order

 

Continuing my blog series on reading and interpreting Oracle execution plans, this week’s post covers Join Order.

So what is the Join Order?

The join order is the order in which the tables are joined together in a multi-table SQL statement. Ideally, a plan should start with the join that eliminates the most data to minimize the amount of data carried into the subsequent joins.

How is the Join Order Determined?

The join order is determined based on cost, which is strongly influenced by the cardinality estimates and available access paths. However, the Optimizer will also always adhere to some basic rules:

    • The Optimizer will always select a join that will produce at most 1 row as the initial join in the plan. For example, a join between two row sources that only have 1 row each. Like a primary key lookup or an index unique scan.
    • If a SQL statement uses an outer join, then the optimizer must obey the join order specified by the outer join. That is to say; the row preserving table must come after the other table in the predicate to ensure all of the additional rows that don’t satisfy the join condition can be added to the result set correctly. For example, with the Oracle syntax for outer joins, the table with the outer join operator must come after the other table in the predicate. Thus, in this example, the cites table must come before the countries table. 
       WHERE cities.country_id = countries.id(+);
    •  For SQL statements that reference a database view,  the Optimizer will attempt to do view merging, where the definition of the view is inserted into the rest of the SQL statement, and the entire expanded statement is optimized as a whole. However, there are a few cases where view merging isn’t possible. In these cases, the optimizer will join all of the tables in the view together before the resulting data set is joined to the tables outside the view.
    • When a subquery has been converted into an anti-join (NOT IN subquery) or semi-join (EXISTS subquery), the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash anti-joins and semi-joins can override this ordering condition under certain circumstances.

How to determine the Join Order in an execution plan

You can take several approaches to determine the Join Order in a plan, from looking at the indentation of the tables in the operation column to a depth-first search. To clearly explain how to identify the Join Order in an execution plan, I’ve created a short video demonstrating several approaches using real-world examples.

What if I don’t get the Join Order I want?

The leading cause of the wrong Join Order is typically a cardinality misestimate on the table or joins in the query or missing access methods.

Don’t forget this post is part of a series of posts on interpreting execution plans, covering how to generate plans, cardinality estimates, access methods, and join methods.

Explain the Explain Plan: Access Methods

At the end of last year, I began a blog series on reading and interpreting Oracle execution plans. In this week’s post, I will tackle the aspect of execution plans that I get the most questions about, Access Methods.

What are Oracle Access Paths or Methods?

Access Methods or Access Paths are the techniques used by Oracle to access the data needed to answer a query. Access Methods can be divided into two categories; data accessed via a table scan or index access. You can find the individual Access Methods chosen by the Optimizer in the Operations column of an Execution table.

How Many Access Paths are available to the Optimizer?

Oracle supports nine different Access Methods today, as shown in the image below.

When will the Optimizer choose each of these methods, and what can I do to influence that decision?

To clearly explain how each of the Access Methods works and when it will be chosen, I’ve created a short video.

What if I don’t get the Access Method I want?

If the Access Method you see in an execution plan is not what you expect, check the cardinality estimates for that object are correct, and the join order allows the access method you desire. Remember, Optimizer transformations (the rewriting of your query to open up additional access methods) can also greatly impact the Access Method.

Explaining the Explain Plan – How to Read and Interpret Execution Plans

 

Examining the different aspects of an execution plan, from cardinality estimates to parallel execution, and understanding what information you should glean from it can be overwhelming even for the most experienced DBA.

That’s why I’ve put together a series of short videos that will walk you through each aspect of the plan and explain what information you can find there and what to do if the plan isn’t what you were expecting.

What is an Execution Plan?

The series starts at the very beginning with a comprehensive overview of what an execution plan is and what information is displayed in each section. After all, you can’t learn to interpret what is happening in a plan, until you know what a plan actually is.

How to Generate an Execution Plan?

Although multiple different tools will display an Oracle Execution Plan for you, there really are only two ways to generate the plan. You can use the Explain Plan command, or you can view the execution plan of a SQL statement currently in the Cursor Cache using the dictionary view V$SQL_Plan. This session covers both techniques for you and provides insights into what additional information you can get the Optimizer to share with you when you generate a plan. It also explains why you don’t always get the same plan with each approach, as I discussed in an earlier post.

How to use DBMS_XPLAN to FORMAT an Execution Plan

The FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function is the best tool to show you detailed information about a what’s happened in an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.  I’ve also covered a lot of the content in this video in a previous post.

Part 2 of the series covers Cardinality Estimates and what you can do to improve them!

Part 3 of the series covers access Methods and what you can do if you don’t get the access method you were expecting.

Part 4 of the series covers Join Methods and when you can expect each one and what to do if you don’t get the join method you were expecting.

Remember you can always get more information on the Oracle Optimizer on the Optimizer team’s blog.

How to use Oracle Optimizer Hints – Part 4 of the Optimizer Workshop

From time to time, it may become necessary to influence the plan the Optimizer chooses via Optimizer hints. This session explains how Optimizer hints are interpreted, when and where they should be used.

Part 4 is called “Harnessing the power of optimizer hints”. Although I am not a strong supporter of adding hints to SQL statements for a whole host of reasons, from time to time, it may become necessary to influence the plan the Optimizer chooses. The most powerful way to alter the plan chosen is via Optimizer hints. But knowing when and how to use Optimizer hints correctly is somewhat of a dark art. This session explains how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.

Explain the Explain Plan – Part 3 of Optimizer Workshop

Examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan.

Part 3 of the workshop examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.

More information on displaying and reading execution plans can be found in my previous blog posts on DBMS_XPLAN.DISPLAY_CURSOR and using SQL Monitor. Or in the white paper Explain the Explain Plan.

How to read a Parallel Execution Plan in Oracle

The volume of data being stored in databases has grown exponentially in recent years. So too has the need to rapidly generate value or business insights from that data.

Parallel execution is the key to processing large volumes of diverse data quickly, as it subdivides complex tasks into a number of small tasks allowing multiple processes to accomplish a single complex task.

However, the use of parallelism can complicate the execution plan displayed. Oracle not only displays the operations needed to complete the SQL statement in the plan but all of the communication steps between the parallel server processes.

So, how should you go about interpreting a parallel execution plan?

In the video below, I give you a step by step guide on how to read parallel plans and what additional information you can glean from them!

%d bloggers like this: