This article will cover all that you need to know about execution plans in order to do SQL tuning. As this is a vast topic (believe me), I will cover this in a series of articles.
Whenever I have a SQL which is running long, the first step I do is to obtain an execution plan. An Execution plan lists out the steps taken by Oracle to execute a SQL statement. It will give you details about ordering, access method, join methods, data operation involved, optimization, cardinalities. This is one torch which will show you the path 95% of times.
There are 3 main ways in which once can get an execution plan for a SQL. They are given below:
- Using “explain plan” statement.
- Using the oracle provided dynamic performance views.
- Enable Trace / Autotrace.
Explain Plan statement
The Explain Plan statement shows the execution plan chosen by oracle for SQL statements (SELECT, UPDATE, INSERT or DELETE).
- You should have a plan table which will hold the o/p of this statement. You can create one using the script $ORACLE_HOME/rdbms/admin/utlxplan.sql if you wish to create separate plan table for the resources, you can edit this and create multiple tables.
- You should be able to execute the SQL for which you are trying to obtain the explain plan. If your SQL contains views, then you must have sufficient privileges in the underlying tables and view. In many cases you may be able to execute a select on a view but not an explain plan. This is because your user id does not have the execute privileges on the underlying tables.
The syntax for this is shown below (ref: Oracle Document)
- Using all the above cases:
EXPLAIN PLAN SET STATEMENT_ID =’plan1’ INTO my_plan_table FOR SELECT employee_name, department, salary FROM employee WHERE salary > 10000;
- You may use the default plan table in Oracle; In that case you can do away with the INTO clause.
- SET STATEMENT_ID is requires when many people are using the same plan table. The statement ids will help you differentiate your plan from others.
In my case, I am the only guy working on tuning and I use
EXPLAIN PLAN FOR SELECT employee_name, department, salary FROM employee WHERE salary > 10000;
Displaying the plan
- To retrieve the plan from the plan table you can use a simple query like
Select * from plan_table where statement_id = ‘plan1’;
- You may also make use of the display function in the dbms_xplan package to display the plan. This will retrieve the latest plan in the plan table:
Select * from table(dbms_xplan.display);
- You may also construct a SQL to retrieve rows from your custom plan table. I use the one shown below:
SELECT ID, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' || options operation, object_name, NVL (BYTES, 0) BYTES, CASE WHEN cardinality > 1000000 THEN TO_CHAR (TRUNC (cardinality / 1000000))|| 'M' WHEN cardinality > 1000 THEN TO_CHAR (TRUNC (cardinality / 1000)) || 'K' ELSE cardinality || '' END no_of_rows, CASE WHEN COST > 1000000 THEN TO_CHAR (TRUNC (COST / 1000000)) || 'M' WHEN COST > 1000 THEN TO_CHAR (TRUNC (COST / 1000)) || 'K' ELSE COST || '' END COST FROM table_plan_8508 WHERE STATEMENT_ID = 'plan1' START WITH ID = 0 AND STATEMENT_ID = 'plan1' CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = 'plan1';
Here, table_plan_8508 is my custom plan table and plan1 is the statement_id of the execution plan I require. This query can also be used against performance views with appropriate modifications.
Using the oracle provided dynamic performance views
We can make use of the oracle views v$sql_plan and v$sql_plan_statistics to determine the execution plan. Of these, v$sql_plan is almost the same as the plan_table mentioned above. The only difference would be a few extra columns for cursor identification etc. v$sql_plan_statistics view gives us the execution stats (time, # of rows etc.) for each operation in the v$sql_plan. You can write a query joining these two views to retrieve the runtime execution plan of a query.
There is another view, v$sql_plan_statistics_all which gives all the combined information in the above two views. I’d rather query this view than write a complex query. Note that the initialization parameter statistics_level needs to be set to “ALL” OR the query hinted to collect the execution statistics so that the execution stats are gathered. Also note that they cause an overhead which cannot be ignored. Given below is a sample query which I use to get the plan from the dynamic performance views.
SELECT ID, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' || options operation, object_name, NVL (BYTES, 0) BYTES, CASE WHEN cardinality > 1000000 THEN TO_CHAR (TRUNC (cardinality / 1000000))|| 'M' WHEN cardinality > 1000 THEN TO_CHAR (TRUNC (cardinality / 1000)) || 'K' ELSE cardinality || '' END no_of_rows, CASE WHEN COST > 1000000 THEN TO_CHAR (TRUNC (COST / 1000000)) || 'M' WHEN COST > 1000 THEN TO_CHAR (TRUNC (COST / 1000)) || 'K' ELSE COST || '' END COST FROM v$sql_plan WHERE hash_value = &&hash_value START WITH ID = 0 AND hash_value = &&hash_value CONNECT BY PRIOR ID = parent_id AND hash_value = &&hash_value;
There are many types of traces. However, from the performance point of view, the most important ones are those associated with Event 10053and Event 10054.
This is the one I use extensively when any of the EBS programs are running long. This can also be used to capture what is going on in an SQL*Plus(or TOAD etc.) session. This is also may be the only officially oracle supported trace.
1) If you want to trace the current session, you may use:
ALTER SESSION SET events ‘10046 trace name context forever;
ALTER SESSION SET events ‘10046 trace name context off’;
2) If you want to trace a concurrent program session OR another session which already exists, then use:
The event 10053 trace actually traces the optimizer actions. Please note that this should be used as a last resort – basically because the data contained in the trace can be very difficult to comprehend in most of the cases. Have you ever wondered why the optimizer chose a particular index and not the other? Well this trace will give you an answer – at lease guide you to it.
The trace file will give you information like peaked values of the binds in the SQL, initialization parameter values, stored statistics, query blocks, single tables, sanity checks, join orders etc. To explain how to interpret this is beyond the scope of this article, I plan to cover it later in a series. Those in a hurry can refer chapter 12 of Jonathan Lewis’ book “Cost-Based Oracle Fundamentals”. It’s a great read.
ALTER SESSION SET events '10053 tracename context forever; ALTER SESSION SET events '10053 tracename context off';
The rest will come in part II – how to make use of the execution plan that you just obtained. Stay tuned.