Hi all,
Here are few scripts which allow you to query SQL execution plans and their execution statistics out from V$SQL_PLAN and V$SQL_PLAN_STATISTICS yourself.
Why would you want to do this as there’s the DBMS_XPLAN.DISPLAY_CURSOR() function in Oracle 10g?
Well, my scripts work also on Oracle 9.2. Also they give you better detail (and flexibility) compared to DBMS_XPLAN.DISPLAY_CURSOR.
Of course in 9i there’s the DBMS_XPLAN.DISPLAY function which you can use in conjuction with EXPLAIN PLAN FOR command, but that approach has problems. EXPLAIN PLAN treats all bind variables as varchar2 datatype, possibly causing the reported execution plan to be different from reality. Also, if you have a long running report from last night and you run explain plan today morning, then if optimizer statistics have changed meanwhile, the explain plan command might come up with a different execution plan again, which is different from reality.
So that’s why in 10g we have the DBMS_XPLAN.DISPLAY_CURSOR which goes directly to required library cache child cursor and extracts (unparses) the execution plan from there. The function uses V$SQL_PLAN% views as its data source. And guess what – these views are there in version 9.2 already! And thats’ where my scripts come in:
Here’s an example. Let’s set statistics_level=all so we get rowsource level execution stats for the cursor (note that this parameter makes your query consume much more CPU so it should only be used at session level for troubleshooting a specific performance issue):
SQL> alter session set statistics_level = all;
Session altered.
SQL> select count(*) from all_users;
COUNT(*)
----------
36
I know the hash value of this query, so lets report its execution plan, directly from library cache. This is the REAL execution plan inside that child cursor, not some estimate like EXPLAIN PLAN command gives:
SQL> @xm 3416239794 %
Ch Pr Op Objcect Optimizer Optim rows Optim bytes Optimizer
ld ed ID Operation Name Cost from step from step Mode
-- -- ---- ------------------------------------------------------- ------------------------------ --------- ---------- ----------- ----------
0 0 SELECT STATEMENT 14 ALL_ROWS
1 SORT AGGREGATE 1 15
A 2 HASH JOIN 14 36 540
A 3 HASH JOIN 9 36 432
4 TABLE ACCESS FULL TS$ 5 13 39
F 5 TABLE ACCESS FULL USER$ 3 36 324
6 TABLE ACCESS FULL TS$ 5 13 39
7 rows selected.
Ch Op
ld ID Predicate Information (identified by operation id):
-- ------ ----------------------------------------------------------------------------------------------------
0 2 - access("U"."TEMPTS#"="TTS"."TS#")
3 - access("U"."DATATS#"="DTS"."TS#")
5 - filter("U"."TYPE#"=1)
XM means eXplain from Memory :)
As we had enabled rowsource level execution stats collection, we can use another script to report the response time and resource consumption at execution plan line level (if you’re interested how Oracle achieves this, check the last part of this blog post)
SQL> @xmsh 3416239794 %
SQL hash value: 3416239794 Cursor address: 00000003A41E5290 | Statement first parsed at: 2009-05-26/23:02:16 | 200 seconds ago
Ch Pr Op Object ms spent in Estimated Real #rows Rowsource Consistent Current Physical Physical Optimizer
ld ed ID Operation Name operation output rows returned starts gets gets reads writes Cost
--- -- ---- ------------------------------------------------------- ------------------------------ ------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
0 0 SELECT STATEMENT 14
1 SORT AGGREGATE 2.49 1 1 1 42 0 0 0
A 2 HASH JOIN 2.43 36 36 1 42 0 0 0 14
A 3 HASH JOIN 1.87 36 36 1 24 0 0 0 9
4 TABLE ACCESS FULL TS$ .12 13 13 1 18 0 0 0 5
F 5 TABLE ACCESS FULL USER$ .06 36 36 1 6 0 0 0 3
6 TABLE ACCESS FULL TS$ .05 13 13 1 18 0 0 0 5
Ch Op
ld ID Predicate Information (identified by operation id):
--- ------ ----------------------------------------------------------------------------------------------------
0 2 - access("U"."TEMPTS#"="TTS"."TS#")
3 - access("U"."DATATS#"="DTS"."TS#")
5 - filter("U"."TYPE#"=1)
XMSH means eXplain from Memory with Statistics by Hash value (well it did make sense to me when I wrote the script, you can rename it to something else if you like :)
You may need to make your browser window pretty wide to see the full output to see all the columns reported. I normally run sqlplus in Windows cmd.exe and make the screen buffer wide, so the output doesn’t wrap.
If you want to read what these output columns mean, read my presentation about how SQL plan execution works.
As I mentioned, the above scripts run also on Oracle 9.2 (that’s why I wrote them years ago).
Also there’s one more script, xms.sql which doesn’t take any parameters and shows you the execution plan of the latest SQL executed in current session. It uses v$session.prev_hash_value for determining this. However on Oracle 9.2 the prev_hash_value seems to be incorrectly reset and my script would display you wrong SQL statements execution plan. In 10g onwards Oracle has fixed this problem though. In 9i you need to identify the hash value of the statement and use xmsh.sql instead.
In 10g the xms works ok:
SQL> select count(*) from all_tables;
COUNT(*)
----------
1739
1 row selected.
SQL> @xms
SQL hash value: 3565202713 Cursor address: 00000003A4311930 | Statement first parsed at: 2009-05-26/23:05:59 | 2 seconds ago
Ch Pr Op Object ms spent Estimated Real #rows Rowsource Consistent Current Physical Physical Optimizer
ld ed ID Operation Name in op. output rows returned starts gets gets reads writes Cost
--- -- ---- ---------------------------------------- ------------------------------ ------------ ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 SELECT STATEMENT 558
1 SORT AGGREGATE 418.63 1 1 1 4559 0 1 0
F 2 FILTER 416.78 1739 1 4559 0 1 0
A 3 HASH JOIN RIGHT OUTER 387.23 1750 1739 1 2568 0 0 0 558
4 TABLE ACCESS FULL USER$ .12 74 74 1 6 0 0 0 3
A 5 HASH JOIN OUTER 377.55 1750 1739 1 2562 0 0 0 555
6 NESTED LOOPS OUTER 225.00 1750 1739 1 1913 0 0 0 410
A 7 HASH JOIN 218.02 1750 1739 1 1819 0 0 0 410
8 TABLE ACCESS FULL USER$ .11 74 74 1 6 0 0 0 3
A 9 HASH JOIN 206.61 1750 1739 1 1813 0 0 0 406
A 10 HASH JOIN RIGHT OUTER 41.63 1750 1750 1 1164 0 0 0 260
11 TABLE ACCESS FULL SEG$ 5.28 5249 5249 1 156 0 0 0 35
A 12 HASH JOIN 17.97 1750 1750 1 1008 0 0 0 224
13 MERGE JOIN CARTESIAN 6.62 13 13 1 18 0 0 0 6
A 14 HASH JOIN 6.40 1 1 1 0 0 0 0 1
F 15 FIXED TABLE FULL X$KSPPI .95 1 1 1 0 0 0 0 0
16 FIXED TABLE FULL X$KSPPCV 1.45 1440 1440 1 0 0 0 0 0
17 BUFFER SORT .20 13 13 1 18 0 0 0 6
18 TABLE ACCESS FULL TS$ .09 13 13 1 18 0 0 0 5
F 19 TABLE ACCESS FULL TAB$ 3.55 1750 1750 1 990 0 0 0 218
F 20 TABLE ACCESS FULL OBJ$ 55.26 53718 53716 1 649 0 0 0 146
A 21 INDEX UNIQUE SCAN I_OBJ1 3.36 1 92 1739 94 0 0 0 0
22 TABLE ACCESS FULL OBJ$ 1.26 53728 53727 1 649 0 0 0 144
23 NESTED LOOPS 25.93 1 69 995 1991 0 1 0 2
A 24 INDEX RANGE SCAN I_OBJAUTH1 22.25 1 149 995 1991 0 1 0 2
F 25 FIXED TABLE FULL X$KZSRO .91 1 69 149 0 0 0 0 0
F 26 FIXED TABLE FULL X$KZSPR .05 14 1 1 0 0 0 0 0
Ch Op
ld ID Predicate Information (identified by operation id):
--- ------ ----------------------------------------------------------------------------------------------------
0 2 - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR IS NOT NULL OR IS NOT NULL))
3 - access("CX"."OWNER#"="CU"."USER#")
5 - access("T"."DATAOBJ#"="CX"."OBJ#")
7 - access("O"."OWNER#"="U"."USER#")
9 - access("O"."OBJ#"="T"."OBJ#")
10 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#"="S"."TS#")
12 - access("T"."TS#"="TS"."TS#")
14 - access("KSPPI"."INDX"="KSPPCV"."INDX")
15 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
19 - filter(BITAND("T"."PROPERTY",1)=0)
20 - filter(BITAND("O"."FLAGS",128)=0)
21 - access("T"."BOBJ#"="CO"."OBJ#")
24 - access("OA"."OBJ#"=:B1)
25 - filter("GRANTEE#"="KZSROROL")
26 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49)
OR (-"KZSPRPRV")=(-50)) AND "INST_ID"=USERENV('INSTANCE')))
I haven’t had a need to adjust the scripts to fix the problem what Jonathan Lewis has described earlier, but it should be easily doable (just need to make sure that the connect by recursive loop is done against a materialized resultset of v$sql_plan, not the view itself to avoid excessive latching).
The scripts are downloadable from here:
- xm.sql – explain from Memory, lookup by Hash value
- xmsh.sql – eXplain from Memory with plan line level Statistics, lookup by Hash value
- xmsi.sql – eXplain from Memory with plan line level Statistics, lookup by SQL_ID
- xms.sql – eXplain from Memory with plan line level Statistics (latest SQL in current session)
Update:
If you are on 10g and want to know how to convert SQL_ID’s to hash values for use with my XM scripts, read this:
