Probably a month ago, I read Greg Rahn's blog post about 11g's Real Time SQL monitoring. It contained a link to an earlier blog post about Troubleshooting Bad Execution Plans, which reminded me I have to investigate the display_cursor function a little more. Especially in combination with the gather_plan_statistics hint, you can get much more information than with a regular dbms_xplan.display. Although nothing beats the information of SQL*Trace/tkprof, this looks like the second best, in case access to the user_dump_dest on the database server is not easy.
The display function gets your information from the plan_table, whereas the display_cursor gets the information from the cursor cache. If you can get the v$sql_plan_statistics filled, then you can get actual row source information, instead of only predictions about what will likely happen. The easiest way to have plan statistics is by adding the almost undocumented hint gather_plan_statistics to your query. When testing however, I ran into this phenomenon:
rwijk@ORA11G> select /*+ gather_plan_statistics */
2 e.empno
3 , e.ename
4 , d.deptno
5 , d.dname
6 from emp e
7 , dept d
8 where e.deptno = d.deptno
9 and e.deptno = 10
10 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
3 rijen zijn geselecteerd.
rwijk@ORA11G> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
2 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rijen zijn geselecteerd.
Luckily I recognized the line BEGIN DBMS_OUTPUT.GET_LINES(...); END; from the tkprof output files. It is what SQL*Plus does after each statement: flushing the dbms_output buffer to the screen. To have the dbms_xplan.display_cursor show what I want, I started writing a little script against V$SQL to get the latest "real" SQL-statement, but then I realised I cannot possibly be the first to encounter this problem. Googling led me to this very useful blog entry by Jonathan Lewis. It contains almost everything you want to know about the display_cursor function. A simple "set serveroutput off" is enough to get the function working! Nevertheless, I finished the little script, in case I really need the serveroutput to be on. Here is the script which I called explain.sql:
set echo off
set verify off
set termout off
column sql_id new_val sql_id
column child_number new_val child_number
select max(sql_id) keep
(dense_rank last order by last_active_time) sql_id
, max(child_number) keep
(dense_rank last order by last_active_time) child_number
from v$sql
where upper(sql_text) like '%/*+ GATHER\_PLAN\_STATISTICS */%' escape '\'
/
set termout on
set feedback off
select * from table
(dbms_xplan.display_cursor('&sql_id',&child_number,'ALLSTATS LAST'))
/
set feedback on
set echo off
set verify on
It finds the cursor in v$sql containing the gather_plan_statistics hint, with the latest last_active_time.
An example of how to use this, with serveroutput left on:
rwijk@ORA11G> select /*+ gather_plan_statistics */
2 e.empno
3 , e.ename
4 , d.deptno
5 , d.dname
6 from emp e
7 , dept d
8 where e.deptno = d.deptno
9 and e.deptno = 10
10 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
3 rijen zijn geselecteerd.
rwijk@ORA11G> @explain
rwijk@ORA11G> set echo off
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID c5n0ffvxjy4xh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.empno , e.ename
, d.deptno , d.dname from emp e , dept d where e.deptno =
d.deptno and e.deptno = 10
Plan hash value: 615168685
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 5 | 3 |00:00:00.01 | 15 | 870K| 870K| 329K (0)|
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 5 | 3 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."DEPTNO"=10)
3 - filter("E"."DEPTNO"=10)
Next, I started comparing all format options - the third parameter - to find the ones I think are most useful. So first I have to understand what is the meaning of each column. It may be trivial to the designers of Oracle and some OakTable members, but some of the columns were certainly not trivial to me. Partly because the documentation is kind of brief about the display_cursor function and all its possibilities.
Id, Operation and Name will be quite obvious for everyone who has worked with explain plans before. But in short: Id is like adding the rownum column to a query: just a number starting with 1 to be able to uniquely identify each step. The operation tells us what is done at each step, for example a SORT GROUP BY or a FULL TABLE SCAN. The name is the database object, for example a table, view or index on which the operation is performed.
Starts
Starts: this is the number of times the particular step is executed. Most of the times it is 1, but in case of a nested loop you will likely see a higher number.
Note that it shows the actual number of times the operation has been performed. So it only shows up when plan statistics have been gathered by using the /*+ gather_plan_statistics */ hint or by setting the statistics_level parameter to all. When doing a regular explain plan with the dbms_xplan.display function you may have noticed that a nested loop always shows a 1 as the cardinality for the inner step. In the row source operation of tkprof however, you'd find the number of iterations in the "Rows" column. This difference is confusing to some - I think I've seen two threads in the past year on the OTN Forums about this. Now with the starts column it can be explained a little easier: tkprof will show N rows, and the plan shows N "Starts" and 1 rows.
E-Rows
This is the estimated number of rows. This is the number you would previously see in the Rows column after a "explain plan for
A-rows
This is the actual number of rows. This number is taken from the plan_statistics, so it only shows up when plan statistics have been gathered. In a tkprof file, this number corresponds to the Rows column in the "Row source operation" section. It is the real number of rows processed by this step. You can get valuable information when this A-rows number differs a lot from E-rows. It is the basis of Wolfgang Breitling's Tuning by Cardinality Feedback method.
IOSTATS
Including IOSTATS in the third argument "format", may result in getting the columns "Buffers" and "Reads" as part of the output. Those columns names were not totally clear to me. As a side note: why does Oracle use so many different words for the same phenomenon (locks/enqueues, db block gets/current gets, ...), is it to confuse non-native speakers or maybe there is a small difference after all? Anyway, here is what I did to find out:
rwijk@ORA11G> create table myobjects as select * from all_objects
2 /
Tabel is aangemaakt.
rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'myobjects')
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> alter system flush buffer_cache
2 /
Systeem is gewijzigd.
rwijk@ORA11G> alter system flush shared_pool
2 /
Systeem is gewijzigd.
rwijk@ORA11G> select count(*) from myobjects
2 /
COUNT(*)
----------
67542
1 rij is geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 ( null, null, 'BASIC IOSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from myobjects
Plan hash value: 3282413857
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| MYOBJECTS |
----------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
19 rijen zijn geselecteerd.
So the IOSTATS don't show up when plan statistics have not been gathered. So let's gather them. Note that I flush the buffer_cache to be able to see some physical reads.
rwijk@ORA11G> alter system flush buffer_cache
2 /
Systeem is gewijzigd.
rwijk@ORA11G> select /*+ gather_plan_statistics */ count(*) from myobjects
2 /
COUNT(*)
----------
67542
1 rij is geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 ( null, null, 'BASIC IOSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */ count(*) from myobjects
Plan hash value: 3282413857
----------------------------------------------------------------------------------
--------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
Reads |
----------------------------------------------------------------------------------
--------
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.34 | 1000 |
996 |
| 2 | TABLE ACCESS FULL| MYOBJECTS | 1 | 67542 |00:00:00.15 | 1000 |
996 |
----------------------------------------------------------------------------------
--------
13 rijen zijn geselecteerd.
Now the "Buffers" and "Reads" columns show up. They only show up when they have a value. In the next query there isn't a single logical nor physical I/O done:
rwijk@ORA11G> alter system flush buffer_cache
2 /
Systeem is gewijzigd.
rwijk@ORA11G> select /*+ gather_plan_statistics */ *
2 from dual
3 where 1=0
4 model
5 dimension by (0 i)
6 measures (1 x)
7 (x[1] = 2)
8 /
I X
---------- ----------
1 2
1 rij is geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 ( null, null, 'BASIC IOSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */ * from dual where 1=0 model
dimension by (0 i) measures (1 x) (x[1] = 2)
Plan hash value: 1604308172
----------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
----------------------------------------------------------------------
| 1 | SQL MODEL ORDERED FAST| | 1 | 1 |00:00:00.01 |
|* 2 | FILTER | | 1 | 0 |00:00:00.01 |
| 3 | FAST DUAL | | 0 | 0 |00:00:00.01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
20 rijen zijn geselecteerd.
And so the IOSTATS don't appear even though we gathered plan statistics. Now let's compare the numbers of the earlier query with the autotrace report:
rwijk@ORA11G> alter system flush buffer_cache
2 /
Systeem is gewijzigd.
rwijk@ORA11G> set autotrace on
rwijk@ORA11G> select count(*) from myobjects
2 /
COUNT(*)
----------
67542
1 rij is geselecteerd.
Uitvoeringspan
----------------------------------------------------------
Plan hash value: 3282413857
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 279 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MYOBJECTS | 67542 | 279 (1)| 00:00:04 |
------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1000 consistent gets
996 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rwijk@ORA11G> set autotrace off
So "Reads" is the number of physical reads performed, and "Buffers" at least comprises the consistent gets. Likely the current gets/db block gets are calculated as well. Let's find out to be sure:
rwijk@ORA11G> alter system flush buffer_cache
2 /
Systeem is gewijzigd.
rwijk@ORA11G> update /*+ gather_plan_statistics */ myobjects
2 set object_type = 'TABLE'
3 where object_type = 'VIEW'
4 /
4749 rijen zijn bijgewerkt.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 ( null, null, 'BASIC IOSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
update /*+ gather_plan_statistics */ myobjects set object_type =
'TABLE' where object_type = 'VIEW'
Plan hash value: 188869321
----------------------------------------------------------------------------------
--------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
Reads |
----------------------------------------------------------------------------------
--------
| 1 | UPDATE | MYOBJECTS | 1 | 0 |00:00:00.59 | 5899 |
1008 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS | 1 | 4749 |00:00:00.05 | 1000 |
996 |
----------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='VIEW')
19 rijen zijn geselecteerd.
rwijk@ORA11G> select substr(s.sql_text,1,80) sql_text
2 , last_cr_buffer_gets
3 , last_cu_buffer_gets
4 , last_disk_reads
5 from v$sql s
6 , v$sql_plan_statistics p
7 where s.address = p.address
8 and s.hash_value = p.hash_value
9 and s.child_number = p.child_number
10 and s.sql_text like 'update%myobjects%'
11 /
SQL_TEXT
--------------------------------------------------------------------------------
LAST_CR_BUFFER_GETS LAST_CU_BUFFER_GETS LAST_DISK_READS
------------------- ------------------- ---------------
update /*+ gather_plan_statistics */ myobjects set object_type = 'TABLE' whe
1000 4899 1008
update /*+ gather_plan_statistics */ myobjects set object_type = 'TABLE' whe
1000 0 996
2 rijen zijn geselecteerd.
So the "Buffers" is the the number of consistent gets plus the number of current gets (4899 + 1000 = 5899). The numbers are the same as the columns LAST_CR_BUFFER_GETS + LAST_CU_BUFFER_GETS and LAST_DISK_READS in the v$sql_plan_statistics view.
MEMSTATS
This displays statistics regarding the estimated sizes of the required PGA workareas to do a SORT, HASH JOIN, BITMAP MERGE or BITMAP CREATE actions. The three columns are "OMem", "1Mem" and "Used-Mem". Even if you specify MEMSTATS in the third argument, it doesn't guarantee that these columns will be displayed: there must be an operation involved in the query that used the PGA workarea memory. Whether the plan statistics are gathered or not, doesn't affect the contents of the memory columns. This can be seen in the following test:
rwijk@ORA11G> create table myobjects as select * from all_objects
2 /
Tabel is aangemaakt.
rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'myobjects')
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> select object_type
2 from myobjects
3 where rownum = 1
4 /
OBJECT_TYPE
-------------------
TABLE
1 rij is geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 (null,null,'BASIC MEMSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select object_type from myobjects where rownum = 1
Plan hash value: 848663153
----------------------------------------
| Id | Operation | Name |
----------------------------------------
|* 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS FULL| MYOBJECTS |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
24 rijen zijn geselecteerd.
So even though we specified 'MEMSTATS', they are not shown here because there isn't an operation that uses PGA workarea memory.
Now a query that uses a HASH GROUP BY, which uses PGA workarea memory, where plan_statistics are not being gathered:
rwijk@ORA11G> select object_type
2 , count(*)
3 from myobjects
4 group by object_type
5 /
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
INDEX PARTITION 128
CONSUMER GROUP 2
SEQUENCE 230
TABLE PARTITION 108
SCHEDULE 2
RULE 1
JAVA DATA 327
PROCEDURE 133
OPERATOR 57
WINDOW 9
PACKAGE 1268
PACKAGE BODY 1207
LIBRARY 179
PROGRAM 18
RULE SET 17
TYPE BODY 224
CONTEXT 7
JAVA RESOURCE 833
XML SCHEMA 49
TRIGGER 484
JOB CLASS 13
DIRECTORY 4
MATERIALIZED VIEW 1
TABLE 2551
INDEX 3208
SYNONYM 26670
VIEW 4749
FUNCTION 296
INDEXTYPE 11
WINDOW GROUP 4
JAVA CLASS 22103
CLUSTER 10
TYPE 2616
JOB 12
EVALUATION CONTEXT 11
36 rijen zijn geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 (null,null,'BASIC MEMSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select object_type , count(*) from myobjects group by
object_type
Plan hash value: 1577776164
-------------------------------------------------------------------
| Id | Operation | Name | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------
| 1 | HASH GROUP BY | | 798K| 798K| 2374K (0)|
| 2 | TABLE ACCESS FULL| MYOBJECTS | | | |
-------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
20 rijen zijn geselecteerd.
So even though plan statistics were not gathered, the memory statistics are shown.
Now repeat the same test with plan statistics gathered:
rwijk@ORA11G> select /*+ gather_plan_statistics */
2 object_type
3 , count(*)
4 from myobjects
5 group by object_type
6 /
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
INDEX PARTITION 128
CONSUMER GROUP 2
SEQUENCE 230
TABLE PARTITION 108
SCHEDULE 2
RULE 1
JAVA DATA 327
PROCEDURE 133
OPERATOR 57
WINDOW 9
PACKAGE 1268
PACKAGE BODY 1207
LIBRARY 179
PROGRAM 18
RULE SET 17
TYPE BODY 224
CONTEXT 7
JAVA RESOURCE 833
XML SCHEMA 49
TRIGGER 484
JOB CLASS 13
DIRECTORY 4
MATERIALIZED VIEW 1
TABLE 2551
INDEX 3208
SYNONYM 26670
VIEW 4749
FUNCTION 296
INDEXTYPE 11
WINDOW GROUP 4
JAVA CLASS 22103
CLUSTER 10
TYPE 2616
JOB 12
EVALUATION CONTEXT 11
36 rijen zijn geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor
4 (null,null,'BASIC MEMSTATS -ROWS LAST')
5 )
6 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */ object_type , count(*)
from myobjects group by object_type
Plan hash value: 1577776164
----------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Starts | A-Rows | A-Time | OMem | 1
Mem | Used-Mem |
----------------------------------------------------------------------------------
----------------
| 1 | HASH GROUP BY | | 1 | 36 |00:00:00.05 | 798K|
798K| 2374K (0)|
| 2 | TABLE ACCESS FULL| MYOBJECTS | 1 | 67543 |00:00:00.14 | |
| |
----------------------------------------------------------------------------------
----------------
14 rijen zijn geselecteerd.
Here we get the extra columns "Starts", "A-rows" and "A-time", because plan statistics are gathered. There is no way - that I know of - to suppress these three columns. The memory columns are shown as expected. Next question: what exactly is represented by each of the three columns. The next query shows the matching columns of v$sql_workarea:
rwijk@ORA11G> select substr(s.sql_text,1,49) sql_text
2 , to_char(w.estimated_optimal_size/1024) || 'K' "OMem"
3 , to_char(w.estimated_onepass_size/1024) || 'K' "1Mem"
4 , to_char(w.last_memory_used/1024) || 'K' "Used-Mem"
5 from v$sql s
6 , v$sql_workarea w
7 where s.address = w.address
8 and s.hash_value = w.hash_value
9 and s.child_number = w.child_number
10 and s.sql_text like '%from myobjects%'
11 /
SQL_TEXT OMem 1Mem Used-Mem
------------------------------------------------- ---------- ---------- ----------
select /*+ gather_plan_statistics */ objec 798K 798K 2374K
select object_type , count(*) from myobjec 798K 798K 2374K
2 rijen zijn geselecteerd.
So they are exactly the same as v$sql_workarea's estimated optimal size, estimated one-pass size and last memory used. From the documentation:
ESTIMATED_OPTIMAL_SIZE NUMBER Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions.
ESTIMATED_ONEPASS_SIZE NUMBER Estimated size (in KB) required by this work area to execute the operation in a single pass. Derived from either optimizer statistics or previous executions.
LAST_MEMORY_USED NUMBER Memory (in KB) used by this work area during the last execution of the cursor
If terms like optimal size and one-pass size are not familiar to you, then I highly recommend to read this article on SQL memory management. It is a tough read - I had to reread several parts to understand it - but it's worth the effort.
Extra sections below the plan
There are five documented sections: PREDICATE, PROJECTION, ALIAS, REMOTE and NOTE. But in a recent blog post of the Optimizer Development Group, they disclosed that you can use 'PEEKED_BINDS' for the format parameter, giving a section with the values of bind variables that are used. This is a lot easier than digging through the trace file looking for a "#BIND" and a "value=" clause.
UPDATE
An update to draw extra attention to the first comment. Jonathan Lewis knows some more options, and mentions them here on his Oracle Scratchpad. The ALL option is described quite well in the documentation, but OUTLINE and ADVANCED are not, and this is the first time I hear about them.
Here you can see that they work indeed. It's not that I don't believe Jonathan Lewis, but one has to "trust, but verify". Or as my former colleague Hans Driessen likes to say (in German) "Vertrauen ist gut, controlieren ist besser".
rwijk@ORA11G> alter session set create_stored_outlines = true
2 /
Sessie is gewijzigd.
rwijk@ORA11G> select /* my unique identifier */
2 e.ename
3 , d.dname
4 from emp e
5 , dept d
6 where e.deptno = d.deptno
7 and d.loc = 'DALLAS'
8 /
ENAME DNAME
---------- --------------
SMITH RESEARCH
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
5 rijen zijn geselecteerd.
rwijk@ORA11G> alter session set create_stored_outlines = false
2 /
Sessie is gewijzigd.
rwijk@ORA11G> column hash_value new_value hv
rwijk@ORA11G> column child_number new_value cn
rwijk@ORA11G> select hash_value
2 , child_number
3 from v$sql
4 where sql_text like 'select /* my unique identifier%'
5 /
HASH_VALUE CHILD_NUMBER
---------- ------------
3597778427 0
3597778427 1
2 rijen zijn geselecteerd.
rwijk@ORA11G> exec dbms_outln.create_outline(&hv,&cn)
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> column name new_value outline_name
rwijk@ORA11G> select name
2 , used
3 , sql_text
4 from user_outlines
5 /
NAME USED SQL_TEXT
------------------------------ ------ -------------------------------------
SYS_OUTLINE_08030623242857879 UNUSED select /* my unique identifier */
e.ename
, d.dname
from emp e
, dept d
where e.deptno = d.deptno
and d.loc = 'DALLAS'
1 rij is geselecteerd.
rwijk@ORA11G> set serveroutput off
rwijk@ORA11G> select /* my unique identifier */
2 e.ename
3 , d.dname
4 from emp e
5 , dept d
6 where e.deptno = d.deptno
7 and d.loc = 'DALLAS'
8 /
ENAME DNAME
---------- --------------
SMITH RESEARCH
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
5 rijen zijn geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor(null,null,'OUTLINE LAST')
4 )
5 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 8y0jxxzb73ggv, child number 1
-------------------------------------
select /* my unique identifier */ e.ename , d.dname from
emp e , dept d where e.deptno = d.deptno and d.loc = 'DALLAS'
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 5 | 145 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_HASH(@"SEL$1" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."LOC"='DALLAS')
39 rijen zijn geselecteerd.
rwijk@ORA11G> select /* my unique identifier */
2 e.ename
3 , d.dname
4 from emp e
5 , dept d
6 where e.deptno = d.deptno
7 and d.loc = 'DALLAS'
8 /
ENAME DNAME
---------- --------------
SMITH RESEARCH
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
5 rijen zijn geselecteerd.
rwijk@ORA11G> select *
2 from table
3 ( dbms_xplan.display_cursor(null,null,'ADVANCED LAST')
4 )
5 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 8y0jxxzb73ggv, child number 1
-------------------------------------
select /* my unique identifier */ e.ename , d.dname from
emp e , dept d where e.deptno = d.deptno and d.loc = 'DALLAS'
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 5 | 145 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / E@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_HASH(@"SEL$1" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."LOC"='DALLAS')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "D"."DNAME"[VARCHAR2,14], "E"."ENAME"[VARCHAR2,10]
2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]
3 - "E"."ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]
53 rijen zijn geselecteerd.
Thanks Jonathan for making the list more complete and thanks Jason for letting me know.
As requested http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/
ReplyDeleteby Jonathan Lewis:
‘All’: Prints the Query block/Object Alias section, the Predicate information, and the Column Projection information after the basic plan.
‘Advanced’: as for ‘All’, but also include the Outline information (the set of hints that will reproduce the plan).
‘Outline’: prints just the Outline information and the Predicate information after the basic plan.
Great job with the details on the other parts of the display_cursor statistics. Thanks!
ReplyDeleteI bookmarked this page and for the time being it will be my bible... thanks a lot for this excellent explanation
ReplyDeleteThank you for the Great Explanation ! This is what I was looking for.
ReplyDeleteI am on 11202 and am not able to make this work.
ReplyDeleteI am trying to get the plan for a DELETE with gather_plan_statistics hint. And I have tried the above. Also tried with serveroutput on and off both, but I just cant get the plan.
If I query v$sqlplan, the row with the sql_id is not there.
The server is free, nothing else executing on it. So what could be going wrong?
Thanks.
"If I query v$sqlplan, the row with the sql_id is not there."
ReplyDeleteThen you need to find out why the cursor doesn't get loaded in the library cache. If it's there, dbms_xplan.display_cursor will display it.
nice! thank you!!
ReplyDelete