sql plan enhancements in 10g
http://www.oracle-developer.net/display.php?id=316
Since the days of Version 7, Oracle has been exposing more (and more detailed) diagnostic information to its users. Oracle was originally instrumented to assist the kernel developers with their own performance tuning efforts, but it was soon realised how beneficial it would be to have this information available to all. For several years, Oracle has exposed its diagnostic data through dynamic views, trace files, packages, events and other sources. In addition to making more information available with every release, Oracle has also improved the quality and depth of its instrumentation.
In this article, we will examine new features of 10g related to SQL plan diagnostics. These will be of use to Oracle developers and DBAs alike. We will focus on the new data that Oracle exposes on execution plans and their resource usage and see how to access it.
dbms_xplan enhancements
The DBMS_XPLAN package was introduced in Oracle 9i Release Two (9.2). With this package, Oracle "internalised" its SQL to format the execution plans created via the EXPLAIN PLAN syntax and also added some increasingly critical extensions to the information it provided (such as predicate information).
With the release of Oracle 10g, this package has been extended to provide, among other things, formatted execution plans and workload statistics for actual SQL cursors (i.e. those that have executed or are running). We'll be taking a detailed look at these later, but we'll start by taking a look at the more general extensions to DBMS_XPLAN. Note that article assumes that readers are familiar with the 9i version of DBMS_XPLAN and its usage.
column projections
One of the benefits of all versions of DBMS_XPLAN is that it gives us much more information than we have ever had before. In particular, the presence of predicate information (access and filter conditions) made SQL tuning much easier in certain cases. For example, query merging, implicit datatype casting in joins and excessive function calls all just "flew off the page" when using DBMS_XPLAN for an explain plan. We could also begin to see the workings of the CBO to some degree with techniques such as transitive closure appearing in the report.
In 10g, Oracle has provided even more information in the explain plan report, including column-level projections and named query blocks. In the following example, we'll explain a simple join between EMP and DEPT and then ask DBMS_XPLAN.DISPLAY to provide all the information it can about the potential execution plan.
SQL> EXPLAIN PLAN set statement_id = 'X1'
2 FOR
3 SELECT d.deptno
4 , d.dname
5 , COUNT(*) AS count_employees
6 , SUM(e.sal) AS sum_salaries
7 FROM dept d
8 , emp e
9 WHERE d.deptno = e.deptno
10 GROUP BY
11 d.deptno
12 , d.dname;
Explained.
SQL> SELECT plan_table_output
2 FROM TABLE( DBMS_XPLAN.DISPLAY('PLAN_TABLE','X1','ALL') );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2970111170
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 240 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY | | 12 | 240 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
6 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], COUNT(*)[22],
SUM("E"."SAL")[22]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "E"."SAL"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "E"."DEPTNO"[NUMBER,22], "E"."SAL"[NUMBER,22]
6 - "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
44 rows selected.
We can see that Oracle has provided the columns to be projected at each stage of the execution plan. This information can be extremely useful for debugging complex statements that are joining incorrectly or inefficiently. This information can be so useful that debugging certain statements would be next to impossible otherwise. By way of an example, I was sent a SQL statement with a MODEL clause and asked to determine why it was ignoring the ITERATE UNTIL clause unless the measure used in the clause was also included in the outermost SELECT list. Thirty seconds later, the column projection section provided the answer. The measure column wasn't required in the resultset, so its projection was removed all the way through to the bottom of the nested in-line views (almost certainly a bug, but almost undetectable without the column projections).
query blocks
We can see in the previous example that the report includes a section on "query blocks". This can be quite useful when we have deeply nested or complex SQL statements with many inline views/subqueries or repeated visits to tables as it helps to align the steps of the plan with the respective part of the statement. Oracle 10g now allows us to explicitly name these blocks via the QB_NAME hint. In addition to the clarity query blocks add to execution plans, they can also be referenced in hints.
In the following example, we'll include a couple of query blocks and explicitly name them. We'll then review the execution plan.
SQL> EXPLAIN PLAN SET statement_id = 'qb_name'
2 FOR
3 SELECT /*+ QB_NAME(outer) */
4 e.ename
5 , e.sal
6 FROM (
7 SELECT /*+ QB_NAME(inline_view) */
8 *
9 FROM emp e
10 WHERE e.sal > 300
11 AND e.deptno IN ( SELECT /*+ QB_NAME(dept_subquery) */ d.deptno
12 FROM dept d
13 WHERE d.dname IN ('SALES','ACCOUNTING') )
14 ) e;
Explained.
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','qb_name','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 182 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 7 | 182 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 26 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$B9DAFA34
2 - SEL$B9DAFA34 / D@DEPT_SUBQUERY
3 - SEL$B9DAFA34 / D@DEPT_SUBQUERY
5 - SEL$B9DAFA34 / E@INLINE_VIEW
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES')
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL">300)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."ENAME"[VARCHAR2,10], "E"."SAL"[NUMBER,22]
2 - "D"."DEPTNO"[NUMBER,22]
3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
4 - "E"."DEPTNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."SAL"[NUMBER,22]
5 - "E"."ENAME"[VARCHAR2,10], "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
42 rows selected.
We can see that the various steps in the plan can be cross-referenced to the respective named query block (as can the column projections). As mentioned previously, query blocks can also be used to direct specific hints. In this respect, they are similar to global hints (where hints are referenced against tables or aliases within views) but vary slightly on syntax. The following example shows how we might do this.
SQL> EXPLAIN PLAN SET statement_id = 'qb_hints'
2 FOR
3 SELECT /*+ QB_NAME(outer) FULL(@dept_subquery d) */
4 e.ename
5 , e.sal
6 FROM (
7 SELECT /*+ QB_NAME(inline_view) */
8 *
9 FROM emp e
10 WHERE e.sal > 300
11 AND e.deptno IN ( SELECT /*+ QB_NAME(dept_subquery) */ d.deptno
12 FROM dept d
13 WHERE d.dname IN ('SALES','ACCOUNTING') )
14 ) e;
Explained.
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','qb_hints'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 182 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 7 | 182 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 2 | 26 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES')
3 - filter("E"."SAL">300)
17 rows selected.
This is a simple example but notice the syntax. We reference the query block preceded by the "@" symbol. The target of the hint is the table or alias within that query block. It is important to note that the hint could have been used in any of the three query blocks in the example SQL, using the same syntax. Query block hints can even be directed from within the query block itself. The same applies to views. Just as our example above allowed the outer query block to reference the subquery block, queries can also reference blocks within views, even if nested.
Finally for query blocks, note that view merging or subquery unnesting might affect the results we see, whereby query blocks can "disappear" completely. This means that our named query block will be "transformed out" of the query and a system-generated query block name used in its place. We can see examples of system-generated query block and alias names in our first detailed explain plan above. The implication of this, of course, is that some hints against named query blocks might not work as we expect them to because the named block doesn't really exist.
We do have a couple of options, however. First, we can identify the system-generated query block/alias names using a detailed explain plan and then use these identifiers in our hints. Jonathan Lewis has a good discussion of this technique here. Alternatively, Oracle provides a NO_QUERY_TRANSFORMATION hint to avoid the transformations that caused the named query blocks to "disappear". However, this is quite a drastic approach as it means that Oracle will optimise the query exactly how we write it. The cost of losing the transformation might far outweigh the benefit of the hints we needed to apply to the disappearing query block in the first place.
viewing actual execution plans with dbms_xplan
As most readers will be aware, DBMS_XPLAN in 9i only provided theoretical execution plans (i.e. via EXPLAIN PLAN). Also in 9i, Oracle introduced two new dynamic views (V$SQL_PLAN and V$SQL_PLAN_STATISTICS) that provided execution statistics on actual queries run against the database. Despite the introduction of these views, there was no supported method in 9i for retrieving and formatting the actual execution plans other than SQL Trace and TKProf (although Tom Kyte devised an innovative method to "trick" DBMS_XPLAN into thinking V$SQL_PLAN was PLAN_TABLE). In 10g, however, this changed with the introduction of the DBMS_XPLAN.DISPLAY_CURSOR pipelined function.
At its most simple, the DISPLAY_CURSOR function largely provides the same information as the DISPLAY function, including the new features described earlier. However, it provides the actual estimates and execution plan that Oracle used to optimise and execute the query. This uses the information exposed via the V$SQL, V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL views, for which we require SELECT privileges (either directly or via a role).
The specification for DISPLAY_CURSOR is as follows.
SQL> desc DBMS_XPLAN
...snip...
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
...snip...
Given the above, the following is an example of how we can use DISPLAY_CURSOR.
SQL> set serveroutput off
SQL> SELECT d.dname
2 , SUM(e.sal) AS sum_sal
3 FROM dept d
4 , emp e
5 WHERE d.deptno = e.deptno
6 GROUP BY
7 d.dname;
DNAME SUM_SAL
-------------- ----------
ACCOUNTING 7450
OPERATIONS 1300
RESEARCH 10875
SALES 9400
4 rows selected.
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 00chu48sqmz9p, child number 0
-------------------------------------
SELECT d.dname , SUM(e.sal) AS sum_sal FROM dept d , emp e WHERE
d.deptno = e.deptno GROUP BY d.dname
Plan hash value: 2970111170
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT GROUP BY | | 4 | 80 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note that we called the DISPLAY_CURSOR function with no parameters at all. In this case, Oracle defaults to the last cursor executed in our session. In the example, it was our EMP-DEPT query, but only because we turned serveroutput off (else sqlplus would have silently executed a call to DBMS_OUTPUT.GET_LINES and we would now have this as most recently executed cursor).
identifying specific sql cursors
From the DBMS_XPLAN specification, we can see that the leading parameter of DISPLAY_CURSOR is a SQL_ID. SQL_ID is a new feature of 10g and appears in many V$ views as a means to identify a specific SQL statement. SQL_ID has a one-to-one relationship with the text of a SQL statement in the shared pool but it doesn't quite have a one-to-one relationship with the execution of that SQL (i.e. the cursor). There might be multiple child cursors for a single SQL_ID (for example, the same SQL statement executed under different optimizer modes). For this reason, DISPLAY_CURSOR accepts a child number (default 0) to identify the correct cursor. Incidentally, the execution plan used for a single SQL_ID and CHILD_CURSOR might change over time and this will be represented by a new PLAN_HASH_VALUE in V$SQL_PLAN.
Going back to our previous example, we saw that DISPLAY_CURSOR defaults to the last cursor if we don't provide one. New in 10g, V$SESSION displays SQL_ID/SQL_CHILD_NUMBER and PREV_SQL_ID/PREV_CHILD_NUMBER, which in the very least enables us to answer a popular forum question "how do I identify my last SQL statement?"!
Of course, we are more likely to want to examine the execution plan used for a statement that is running in another session or completed some time earlier (for example, a batch job that took considerably longer than usual to run). In these circumstances, we will need to search the shared pool for the target statement to get the SQL_ID (and in some cases the CHILD_NUMBER). A simple way to do this is using V$SQL. In 10g, we have either the SQL_TEXT column (first 1,000 characters of a SQL statement) or SQL_FULLTEXT (new) which is a CLOB of the actual SQL statement in its original format. If we are searching for a statement using some token from early in the SQL (i.e in the first 1,000 characters), then SQL_TEXT will be faster. Else we have no option but to use the SQL_FULLTEXT as our search target.
A useful technique is to comment every SQL statement we write. In the following example, we will search for the SQL_ID of the EMP-DEPT query we ran earlier to find its SQL_ID. We will then lookup DISPLAY_CURSOR, but this time, request ALL information available.
SQL> SELECT sql_id
2 , child_number
3 , sql_text
4 FROM v$sql
5 WHERE LOWER(sql_text) LIKE 'select d.dname%'
6 AND LOWER(sql_text) NOT LIKE '%ignore%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------
00chu48sqmz9p 0 SELECT d.dname , SUM(e.sal) AS sum_sal FROM dept d ,
emp e WHERE d.deptno = e.deptno GROUP BY d.dn
ame
1 row selected.
In this case, we found just one statement. We can use the SQL_ID and CHILD_NUMBER to lookup its plan as follows.
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('00chu48sqmz9p',0,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
----------
SQL_ID 00chu48sqmz9p, child number 0
-------------------------------------
SELECT d.dname , SUM(e.sal) AS sum_sal FROM dept d , emp e WHERE
d.deptno = e.deptno GROUP BY d.dname
Plan hash value: 2970111170
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT GROUP BY | | 4 | 80 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
6 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "D"."DNAME"[VARCHAR2,14], SUM("E"."SAL")[22]
2 - "D"."DNAME"[VARCHAR2,14], "E"."SAL"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "E"."DEPTNO"[NUMBER,22], "E"."SAL"[NUMBER,22]
6 - "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
49 rows selected.
As an aside, if we combine the use of a bind variable for SQL_ID with SELECT * from the DISPLAY_CURSOR function, we are likely to encounter ORA-22905: cannot access rows from a non-nested table item. In the event of this, there are two solutions. The first is to reference the single PLAN_TABLE_OUTPUT column rather than *. The second is to cast the function to its return type using TABLE(CAST(... AS sys.dbms_xplan_type_table)).
runtime statistics
For several versions of Oracle, the V$SQL dynamic view has provided statistics for cursors (such as executions, elapsed time, fetches). However useful these statistics might be, they are at cursor level and might be the total for many executions.
With Oracle 10g, we can dig deeper into the performance characteristics of a particular execution of a cursor using the DISPLAY_CURSOR function. In addition to providing the execution plan of a given cursor the function can also provide us with runtime statistics, either in total or for the last execution of the SQL. The statistics for a cursor are derived from the data exposed via the V$SQL_PLAN_STATISTICS_ALL view.
There are two benefits to this new instrumentation. Firstly, it provides standard performance metrics to assist with tuning (much like TKProf). Secondly, it enables us to compare actual workloads against the estimates that Oracle generated when parsing the SQL.
It should be stated at this point, however, that to retrieve this information requires the STATISTICS_LEVEL parameter to be set to ALL. It is generally advised that this is not used as a system-wide default value, hence in many respects it is little more useful than SQL Trace and TKProf (i.e. intervention is required to switch it on and off).
In the following example, we'll create three tables, T1, T2 and T3. T3 will contain significantly fewer rows than T1 and T2. We'll gather statistics and then run a three-table join with a hint to force a sub-optimal join order. Using runtime statistics from DBMS_XPLAN.DISPLAY_CURSOR, we'll debug our poorly-performing SQL. First we'll create the tables.
SQL> CREATE TABLE t1 AS SELECT * FROM all_objects;
Table created.
SQL> CREATE TABLE t2 AS SELECT * FROM all_objects;
Table created.
SQL> CREATE TABLE t3 AS SELECT * FROM all_objects WHERE ROWNUM <= 100; Table created. SQL> CREATE INDEX it3 ON t3 (object_id);
Index created.
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
3 DBMS_STATS.GATHER_TABLE_STATS(user,'T2');
4 DBMS_STATS.GATHER_TABLE_STATS(user,'T3',cascade=>TRUE);
5 END;
6 /
PL/SQL procedure successfully completed.
Now we'll set STATISTICS_LEVEL to ALL and run a SQL statement against our three tables.
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;
Session altered.
SQL> SELECT /*+ ORDERED */
2 t1.object_type
3 , COUNT(*) AS object_count
4 FROM t1
5 , t2
6 , t3
7 WHERE t1.object_id = t2.object_id
8 AND t2.object_id = t3.object_id
9 GROUP BY
10 t1.object_type;
OBJECT_TYPE OBJECT_COUNT
------------------- ------------
CLUSTER 5
INDEX 35
TABLE 60
3 rows selected.
Now we'll examine the runtime statistics of this cursor. We'll use the 'RUNSTATS_LAST' parameter value to indicate that we wish to view the statistics for this execution of the cursor only (a cumulative 'RUNSTATS_TOT' is also available).
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 5rrm60t57x14g, child number 0
-------------------------------------
SELECT /*+ ORDERED */ t1.object_type , COUNT(*) AS object_count FROM t1
, t2 , t3 WHERE t1.object_id = t2.object_id AND t2.object_id =
t3.object_id GROUP BY t1.object_type
Plan hash value: 176539994
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time |
-----------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY | | 27 | 3 | 1344 | 700 | 196 |00:00:03.42 |
| 2 | NESTED LOOPS | | 100 | 100 | 1344 | 700 | 196 |00:00:03.42 |
|* 3 | HASH JOIN | | 48447 | 48447 | 1342 | 700 | 196 |00:00:02.48 |
| 4 | TABLE ACCESS FULL| T1 | 48447 | 48447 | 671 | 424 | 0 |00:00:00.41 |
| 5 | TABLE ACCESS FULL| T2 | 48448 | 48448 | 671 | 80 | 0 |00:00:00.47 |
|* 6 | INDEX RANGE SCAN | IT3 | 1 | 100 | 2 | 0 | 0 |00:00:00.54 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$1 / T2@SEL$1
6 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
33 rows selected.
Compared to what we've seen from DBMS_XPLAN so far, the plan format above looks unfamiliar. This time, of course, we've asked for runtime statistics. Oracle provides six high-level statistics as follows:
- E-Rows: estimated rowcounts flowing through each plan step;
- A-Rows: the actual rowcounts flowing through each plan step;
- Buffers: reads from the buffer cache (LIO);
- Reads: physical reads (PIO);
- Writes: physical writes (e.g. to temp); and
- A-Time: elapsed time of each plan step.
Returning to our "poorly performing" query, we can see that our statistics are accurate (E-Rows are equivalent to A-Rows). Note that the statistics for nested loops will be mismatched on first glance. This is because while the A-Rows shows the total number of rows to pass through that step, E-Rows, as in EXPLAIN PLAN, will show the number of rows expected for each execution of that step (i.e. index-lookup). What we can also see is that too much work was performed by this cursor. The two large tables were joined before the smaller table was accessed. We can easily spot a tuning opportunity here; that is, eliminate as much data as early as possible. We can now remove our inefficient ORDERED hint and re-run the example.
SQL> SELECT t1.object_type
2 , COUNT(*) AS object_count
3 FROM t1
4 , t2
5 , t3
6 WHERE t1.object_id = t2.object_id
7 AND t2.object_id = t3.object_id
8 GROUP BY
9 t1.object_type;
OBJECT_TYPE OBJECT_COUNT
------------------- ------------
CLUSTER 5
INDEX 35
TABLE 60
3 rows selected.
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID g1fbshfta4ywy, child number 0
-------------------------------------
SELECT t1.object_type , COUNT(*) AS object_count FROM t1 , t2 , t3
WHERE t1.object_id = t2.object_id AND t2.object_id = t3.object_id GROUP BY
t1.object_type
Plan hash value: 2072740069
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time |
-----------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY | | 27 | 3 | 1343 | 838 | 0 |00:00:01.62 |
|* 2 | HASH JOIN | | 100 | 100 | 1343 | 838 | 0 |00:00:01.62 |
|* 3 | HASH JOIN | | 100 | 100 | 672 | 237 | 0 |00:00:00.79 |
| 4 | INDEX FULL SCAN | IT3 | 100 | 100 | 1 | 0 | 0 |00:00:00.01 |
| 5 | TABLE ACCESS FULL| T2 | 48448 | 48448 | 671 | 237 | 0 |00:00:00.31 |
| 6 | TABLE ACCESS FULL | T1 | 48447 | 48447 | 671 | 601 | 0 |00:00:00.35 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T3@SEL$1
5 - SEL$1 / T2@SEL$1
6 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")
33 rows selected.
This time, we can see that our joins have had to deal with much less data and the statistics and elapsed runtime both reflect this.
retrieving plans for historic cursors
On very busy systems (particularly large batch systems), it is common for some SQL cursors to be aged out of the shared pool reasonably quickly. A new feature of Oracle 10g is the Automatic Workload Repository (AWR) which supports a number of the new advisors as well as the successor to Statspack. The AWR contains historical snapshots of a range of performance metrics (see the long list of DBA_HIST% views) and this includes the plans and text for aged SQL cursors that are no longer in the shared pool.
The DBMS_XPLAN package displays cut-down explain plan information for historic cursors via the new DISPLAY_AWR function. The function's usage is similar to that of DISPLAY_CURSOR, though it does not output any runtime statistics. It can be quite useful for comparing current cursor plans with their predecessors, for example, when a SQL statement suddenly takes much longer to run than usual.
We are not going to look at DISPLAY_AWR in this article as its usage is similar to the DISPLAY and DISPLAY_CURSOR functions. This can be an exercise for the reader. The SQL text can be found in DBA_HIST_SQLTEXT and the plans in DBA_HIST_SQL_PLAN. Note, however, that there might be multiple plans for a SQL_ID in AWR. In this case the PLAN_HASH_VALUE will be different and DISPLAY_AWR will output all plans in succession.
investigating bind variables
To continue with its general theme of exposing as much diagnostic information as possible, Oracle 10g now enables us to identify bind variables related to a SQL cursor. We can investigate these via the new V$SQL_BIND_CAPTURE view which displays bind variable metadata and values for a SQL cursor (though values are only captured for bind variables appears in a WHERE or HAVING clause). This is a dramatic improvement over the v$SQL_BIND_DATA view provided in 9i.
In the following example, we'll run a SQL statement with two bind variables and then examine the V$SQL_BIND_CAPTURE view to see what information we can determine from it.
SQL> var p_sal NUMBER;
SQL> var p_dno NUMBER;
SQL> BEGIN
2 :p_sal := 2000;
3 :p_dno := 20;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT SUM(sal)
2 FROM emp
3 WHERE deptno = :p_dno
4 AND sal > :p_sal;
SUM(SAL)
----------
8975
1 row selected.
We can now retrieve the SQL_ID for this statement using V$SESSION as previously described (in very active systems it is possible that we would also need the child number). The documentation suggests joining V$SQL to V$SQL_BIND_CAPTURE using HASH_VALUE and CHILD_ADDRESS (due to the possibility of having multiple child cursors for a single SQL_ID or HASH_VALUE), but SQL_ID works equally well on its own for this example as we have no child cursors to consider.
SQL> SELECT prev_sql_id
2 FROM v$session
3 WHERE audsid = SYS_CONTEXT('userenv','sessionid');
PREV_SQL_ID
-------------
5anj7td9xqhr3
1 row selected.
Using this SQL_ID, we can now investigate the bind variables used during the optimisation of this SQL statement.
SQL> SELECT name
2 , position
3 , datatype_string
4 , was_captured
5 , value_string
6 FROM v$sql_bind_capture
7 WHERE sql_id = '5anj7td9xqhr3';
NAME POSITION DATATYPE_STRING WAS_CAPTURED VALUE_STRING
-------- ---------- --------------- ------------ ------------
:P_DNO 1 NUMBER YES 20
:P_SAL 2 NUMBER YES 2000
2 rows selected.
Rather usefully, we can see the actual values we supplied to the SQL statement. But what if we re-run the statement with a new set of inputs? We'll repeat the example with new bind variables as follows.
SQL> BEGIN
2 :p_sal := 500;
3 :p_dno := 30;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT SUM(sal)
2 FROM emp
3 WHERE deptno = :p_dno
4 AND sal > :p_sal;
SUM(SAL)
----------
9400
1 row selected.
SQL> SELECT prev_sql_id
2 FROM v$session
3 WHERE audsid = SYS_CONTEXT('userenv','sessionid');
PREV_SQL_ID
-------------
5anj7td9xqhr3
1 row selected.
SQL> SELECT name
2 , position
3 , datatype_string
4 , was_captured
5 , value_string
6 FROM v$sql_bind_capture
7 WHERE sql_id = '5anj7td9xqhr3';
NAME POSITION DATATYPE_STRING WAS_CAPTURED VALUE_STRING
-------- ---------- --------------- ------------ ------------
:P_DNO 1 NUMBER YES 20
:P_SAL 2 NUMBER YES 2000
2 rows selected.
We can see that the new values were not captured. It appears that Oracle will capture the binds whenever a plan is optimised (including re-parses following cursor invalidations). It will not, therefore, show us every set of variables ever supplied to a statement, but these are not necessarily the critical inputs if we are investigating a wayward plan.
further reading
For further details on DBMS_XPLAN, read the comments in the package specification (in an IDE of choice) and also the package reference. For information on the new V$% and DBA_HIST% performance views, see the Oracle Reference manual. Finally, the Performance Tuning Guide has a wealth of information on investigative techniques and how to use the various diagnostic tools described in this article.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, December 2004 (updated November 2007)
No comments:
Post a Comment