In the last post, we looked at the DBMS_XPLAN package and the DISPLAY function. Now we will start to look at my much preferred DISPLAY_CURSOR function.
DBMS_XPLAN.DISPLAY_CURSOR –
The DISPLAY_CURSOR function has been around since 10g. It is a table function that displays the execution plans for any cursor that is currently in the cursor cache. There are 3 parameters (as of 12c):
DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, cursor_child_no IN NUMBER DEFAULT 0, format IN VARCHAR2 DEFAULT 'TYPICAL' );
The SQL_ID and CURSOR_CHILD_NO are available in system views like gv$sql. If null is passed for sql_id, it will use the last cursor executed by the session. If null is passed for the cursor_child_no, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The format parameter controls the level of details displayed. If null is passed for the format, the value of TYPICAL is used. We will get more into what is available to be displayed by the format options later. The typical call to DISPLAY_CURSOR looks like:
select *
from table(dbms_xplan.display_cursor('b810y0qrnpgw5',0,'ADVANCED ALLSTATS'));
To me, the major benefit of DISPLAY_CURSOR is that it allows you to display additional statistics, including actual counts, as well as I/O, memory, and timing stats. Other benefits is that it can be run for any cursor in the cursor cache, and that it will show you the execution plan that was actually used (as opposed to the guess you get with EXPLAIN PLAN). The downside is that the cursor must be in the cursor cache, so the SQL must have already been executed. Note, however, that currently running queries are available to the DISPLAY_CURSOR function.
Additional Statistics
As mentioned previously, DISPLAY_CURSOR allows you to view additional statistics, that can be very helpful in diagnosing issues. Some of these statistics require that parameters get set prior to the SQL running to tell Oracle that you want to collect the statistics during execution. This is done by:
To gather I/O and “Actuals” Statistics:
- Set parameter statistics_level = ALL
- This can be done at the session level (alter session set statistics_level = ALL)
- OR you can use the hint gather_plan_statistics (select /*+ gather_plan_statistics */ …) during execution
To gather Memory Management Statistics:
- Set parameter pga_aggregate_target to non-zero value
- This is typically already done, as this is the default as of 10g
Note: There is a slight overhead in gathering the I/O statistics, so the statistics_level = ALL setting isn’t something you would typically want on at a system level on a production database.
Output
There are several options for format, which can be found in the documentation: http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_xplan.htm#i998364. There is also an undocumented option ADVANCED that includes the OUTLINE data section, making for a total of 7 sections of output. To produce the output below I used format = ‘ADVANCED ALLSTATS’, so I could show everything available:
– Information about SQL Statement:
Includes the SQL_ID, Child Number, and SQL that was executed. This is pretty much just for reference when looking back at the output.
SQL_ID b810y0qrnpgw5, child number 0 ------------------------------------- select /*+ cm2_test1 gather_plan_statistics */ at1.owner, at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner, at2.table_name, at2.num_rows from a_tables at1 inner join a_constraints ac1 on ac1.owner = at1.owner and ac1.table_name = at1.table_name inner join a_constraints ac2 on ac2.owner = ac1.r_owner and ac2.constraint_name = ac1.r_constraint_name inner join a_tables at2 on at2.owner = ac2.owner and at2.table_name = ac2.table_name where ac1.constraint_type = 'R'
Note: You can see from the statement that I put in the comment gather_plan_statistics to instruct Oracle to collect I/O statistics and “actuals” for this query. The cm2_test1 part is just a comment to help me find the query in the cursor cache.
– Execution Plan:
This shows the path that was taken to complete the SQL statement. The columns shown in this section depend on the value passed into the FORMAT parameter. We will look more closely at this output shortly.
Plan hash value: 3592798741 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 62 (100)| | 1059 |00:00:44.23 | 32M| | | | | 1 | NESTED LOOPS | | 1 | | | | | 1059 |00:00:44.23 | 32M| | | | | 2 | NESTED LOOPS | | 1 | 1 | 154 | 62 (0)| 00:00:01 | 99M|00:02:15.27 | 21M| | | | | 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 105 | 60 (0)| 00:00:01 | 25M|00:00:13.03 | 3480 | | | | | 4 | NESTED LOOPS | | 1 | | | | | 9951 |00:00:00.05 | 3385 | | | | | 5 | NESTED LOOPS | | 1 | 1 | 75 | 31 (0)| 00:00:01 | 9951 |00:00:00.03 | 2285 | | | | | 6 | TABLE ACCESS FULL | A_TABLES | 1 | 1 | 30 | 29 (0)| 00:00:01 | 2573 |00:00:00.01 | 96 | | | | |* 7 | INDEX RANGE SCAN | A_CONS_NUK2 | 2573 | 1 | | 1 (0)| 00:00:01 | 9951 |00:00:00.01 | 2189 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS | 9951 | 1 | 45 | 2 (0)| 00:00:01 | 9951 |00:00:00.01 | 1100 | | | | | 9 | BUFFER SORT | | 9951 | 1 | 30 | 58 (0)| 00:00:01 | 25M|00:00:07.41 | 95 | 178K| 178K| 1/0/0| | 10 | TABLE ACCESS FULL | A_TABLES | 1 | 1 | 30 | 29 (0)| 00:00:01 | 2573 |00:00:00.01 | 95 | | | | |* 11 | INDEX RANGE SCAN | A_CONS_NUK2 | 25M| 1 | | 1 (0)| 00:00:01 | 99M|00:01:30.82 | 21M| | | | |* 12 | TABLE ACCESS BY INDEX ROWID | A_CONSTRAINTS | 99M| 1 | 49 | 2 (0)| 00:00:01 | 1059 |00:00:52.50 | 10M| | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
– Query Blocks
This section is useful when you are wanting to hint specific query blocks from one location (for example when adding hints through OBIEE, where you can only add hints to the very outer level, but may want to hint a block nested several layers deep). The number in the beginning of each line corresponds to the ID in the execution plan table, letting you know which alias matches with which block/object.
Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$EE94F965 6 - SEL$EE94F965 / AT2@SEL$3 7 - SEL$EE94F965 / AC2@SEL$2 8 - SEL$EE94F965 / AC2@SEL$2 10 - SEL$EE94F965 / AT1@SEL$1 11 - SEL$EE94F965 / AC1@SEL$1 12 - SEL$EE94F965 / AC1@SEL$1
Note: In this example, there was no subqueries, so all objects have the same Query Block identifier
– Outline
This section shows all the hints that would be needed to ensure the query executes in exactly the same fashion again. This is typically used with stored outlines and SQL plan baselines.
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$EE94F965")
MERGE(@"SEL$9E43CB6E")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$9E43CB6E")
MERGE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$EE94F965" "AT2"@"SEL$3")
INDEX(@"SEL$EE94F965" "AC2"@"SEL$2" ("A_CONSTRAINTS"."OWNER" "A_CONSTRAINTS"."TABLE_NAME"))
FULL(@"SEL$EE94F965" "AT1"@"SEL$1")
INDEX(@"SEL$EE94F965" "AC1"@"SEL$1" ("A_CONSTRAINTS"."OWNER" "A_CONSTRAINTS"."TABLE_NAME"))
LEADING(@"SEL$EE94F965" "AT2"@"SEL$3" "AC2"@"SEL$2" "AT1"@"SEL$1" "AC1"@"SEL$1")
USE_NL(@"SEL$EE94F965" "AC2"@"SEL$2")
NLJ_BATCHING(@"SEL$EE94F965" "AC2"@"SEL$2")
USE_MERGE_CARTESIAN(@"SEL$EE94F965" "AT1"@"SEL$1")
USE_NL(@"SEL$EE94F965" "AC1"@"SEL$1")
NLJ_BATCHING(@"SEL$EE94F965" "AC1"@"SEL$1")
END_OUTLINE_DATA
*/
Note: You can see here how the Query Block / Object Aliases from the previous section can be referenced in hints
– Predicates
The predicate information shows the filters that get applied when accessing the data. This section is helpful to catch transformations that may have been done to the query by Oracle, including implicit datatype conversion. We will discuss this in more detail later as well. Again, the number in the beginning of each line corresponds to the ID in the execution plan table, letting you know at exactly which steps each predicate is applied.
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
12 - filter(("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND "AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND
"AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME"))
There are 2 types of predicates:
– Access = Only matching rows are retrieved
– Filter = All rows are retrieved, only matching rows are kept
This is an important distinction when understanding buffer counts
– Column Projection
This section shows each field and its corresponding data type that is returned from each step in the execution plan. Once again, the number in the beginning of each line corresponds to the ID in the execution plan table
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30],
"AT1"."NUM_ROWS"[NUMBER,22], "AC1"."CONSTRAINT_NAME"[VARCHAR2,30]
2 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30],
"AC2"."CONSTRAINT_NAME"[VARCHAR2,30], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22], "AC1".ROWID[ROWID,10]
3 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30],
"AC2"."CONSTRAINT_NAME"[VARCHAR2,30], "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
4 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2"."OWNER"[VARCHAR2,30],
"AC2"."CONSTRAINT_NAME"[VARCHAR2,30]
5 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22], "AC2".ROWID[ROWID,10], "AC2"."OWNER"[VARCHAR2,30]
6 - "AT2"."OWNER"[VARCHAR2,30], "AT2"."TABLE_NAME"[VARCHAR2,30], "AT2"."NUM_ROWS"[NUMBER,22]
7 - "AC2".ROWID[ROWID,10], "AC2"."OWNER"[VARCHAR2,30]
8 - "AC2"."CONSTRAINT_NAME"[VARCHAR2,30]
9 - (#keys=0) "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
10 - "AT1"."OWNER"[VARCHAR2,30], "AT1"."TABLE_NAME"[VARCHAR2,30], "AT1"."NUM_ROWS"[NUMBER,22]
11 - "AC1".ROWID[ROWID,10]
12 - "AC1"."CONSTRAINT_NAME"[VARCHAR2,30]
– Notes
Oracle displays any relevant information about what it may have done. Examples are dynamic sampling, star transformations, SQL Plan Management, and even comments that you are trying to display information in DISPLAY_CURSOR that wasn’t collected due to the proper parameters not being set.
Note ----- - cardinality feedback used for this statement
Note: The demo didn’t actually produce any notes. I got the above note from a different query, just to include an example of what the section might look like.
The FORMAT parameter that I normally use is IOSTATS, which just displays the columns and sections that I use most often:
SQL_ID b810y0qrnpgw5, child number 0
-------------------------------------
select /*+ cm2_test1 gather_plan_statistics */ at1.owner,
at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner,
at2.table_name, at2.num_rows from a_tables at1 inner join
a_constraints ac1 on ac1.owner = at1.owner and
ac1.table_name = at1.table_name inner join a_constraints ac2
on ac2.owner = ac1.r_owner and ac2.constraint_name =
ac1.r_constraint_name inner join a_tables at2 on at2.owner
= ac2.owner and at2.table_name = ac2.table_name where
ac1.constraint_type = 'R'
Plan hash value: 3592798741
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1059 |00:00:44.23 | 32M|
| 1 | NESTED LOOPS | | 1 | | 1059 |00:00:44.23 | 32M|
| 2 | NESTED LOOPS | | 1 | 1 | 99M|00:02:15.27 | 21M|
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 25M|00:00:13.03 | 3480 |
| 4 | NESTED LOOPS | | 1 | | 9951 |00:00:00.05 | 3385 |
| 5 | NESTED LOOPS | | 1 | 1 | 9951 |00:00:00.03 | 2285 |
| 6 | TABLE ACCESS FULL | A_TABLES | 1 | 1 | 2573 |00:00:00.01 | 96 |
|* 7 | INDEX RANGE SCAN | A_CONS_NUK2 | 2573 | 1 | 9951 |00:00:00.01 | 2189 |
| 8 | TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS | 9951 | 1 | 9951 |00:00:00.01 | 1100 |
| 9 | BUFFER SORT | | 9951 | 1 | 25M|00:00:07.41 | 95 |
| 10 | TABLE ACCESS FULL | A_TABLES | 1 | 1 | 2573 |00:00:00.01 | 95 |
|* 11 | INDEX RANGE SCAN | A_CONS_NUK2 | 25M| 1 | 99M|00:01:30.82 | 21M|
|* 12 | TABLE ACCESS BY INDEX ROWID | A_CONSTRAINTS | 99M| 1 | 1059 |00:00:52.50 | 10M|
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
12 - filter(("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND
"AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND
"AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME"))
In the next post, we will take a look at the execution plan in detail.