Oracle SQL Plan Execution: How it
really works
Tanel Põder
http://www.tanelpoder.com
Tanel Põder
What is an execution plan?
For Oracle server:
� Parsed, optimized and compiled SQL code kept inside library cache
For DBAs and developers:
� Text or graphical representation of SQL execution flow
Often known as explain plan
� To be correct in terms, explain plan is just a tool, command in
Oracle
� Explain plan outputs textual representation of execution plan into
plan table
� DBAs/developers report human readable output from plan table
Tanel Põder
One slide for getting execution plan
Starting from 9.2 the usual way is:
� explain plan for
� select * from table(dbms_xplan.display)
In 10g
� the autotrace also uses dbms_xplan
� set autotrace on
� or select * from table(dbms_xplan.display_cursor())
In 11g
� DBMS_SQLTUNE.REPORT_SQL_MONITOR
Other methods
� sql_trace / 10046 trace + tkprof utility
� v$sql_plan
� setting event 10132 at level 1
� 3rd party tools (which use explain plan anyway)
Avoid "explain plan for" approach if possible!!!
Explain plan for has problems:
1) It treats all bind variables as
VARCHAR2
2) It might not show you the real
exec plan used!
Use V$SQL_PLAN_STATISTICS /
dbms_xplan.display_cursor
instead!
Tanel Põder
Parse stages
Syntactic check
� Syntax, keywords, sanity
Semantic check
� Whether objects referenced exist, are accessible (by
permissions) and are usable
View merging
� Queries are written to reference base tables
� Can merge both stored views and inline views
Query transformation
� Transitivity, etc (example: if a=1 and a=b then b=1)
Optimization
Query execution plan (QEP) generation
Loading SQL and execution plan in library cache
s
o
f
t
p
a
r
s
e
h
a
r
d
p
a
r
s
e
Tanel Põder
SQL execution basics
EMPLOYEESDEPARTMENTS
SELECT
E.LAST_NAME,
D.DEPARTMENT_NAME
FROM
EMPLOYEES E,
DEPARTMENTS D
WHERE
E.DEPARTMENT_ID =
D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME =
'Sales'
AND E.SALARY > 2000;
Query
HASH JOINFILTER
FIL
TE
R
r
o
w
s
o
u
r
c
e
table
access
full
row
sourcetab
le
sc
an
ro
w
so
ur
ce
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS |
|* 3 | TABLE ACCESS FULL| EMPLOYEES |
------------------------------------------
Execution plan
SELECT
processor cursor
application
Tanel Põder
SQL execution basics - multitable joins
SELECT
E.LAST_NAME,
D.DEPARTMENT_NAME,
L.CITY
FROM
EMPLOYEES E,
DEPARTMENTS D,
LOCATIONS L
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME = 'Sales'
AND D.LOCATION_ID = L.LOCATION_IID
AND E.SALARY > 2000;
Multiple joins
EMPLOYEES
DEPARTMENTS
ro
w
so
ur
ce
HASH JOIN row
source
r
o
w
s
o
u
r
c
e
SELECT
processor cursor
app.
LOCATIONS
row
source
NL
JOIN
ro
w
so
ur
ce
Only two row sources can be
joined together at a time
Row sources pass their data
"up" the execution plan tree
The join order is determined
during optimization phase
Tanel Põder
SQL execution terminology
ACCESS PATH
� A means to access physical data in database storage
� From tables, indexes, external tables, database links
ROW SOURCE
� A virtual stream of rows
� Can come through access paths from tables, indexes
� Or from other child row sources
FILTER PREDICATE
� A property of row source - can discard rows based on defined
conditions - filter predicates
JOIN
� Filters and merges rows based on matching rows from child
rowsources. Matching is defined by join predicates
� Any join operator can join only two inputs
Tanel Põder
-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | NESTED LOOPS OUTER | |
|* 3 | HASH JOIN OUTER | |
| 4 | NESTED LOOPS OUTER | |
| 5 | NESTED LOOPS OUTER | |
|* 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | USER$ |
| 8 | NESTED LOOPS | |
|* 9 | HASH JOIN | |
| 10 | MERGE JOIN CARTESIAN | |
|* 11 | HASH JOIN | |
|* 12 | FIXED TABLE FULL | X$KSPPI |
| 13 | FIXED TABLE FULL | X$KSPPCV |
| 14 | BUFFER SORT | |
| 15 | TABLE ACCESS FULL | TS$ |
|* 16 | TABLE ACCESS FULL | TAB$ |
|* 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 18 | INDEX UNIQUE SCAN | I_OBJ1 |
| 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 20 | INDEX UNIQUE SCAN | I_OBJ1 |
| 21 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 22 | INDEX UNIQUE SCAN | I_OBJ1 |
| 23 | TABLE ACCESS FULL | USER$ |
| 24 | TABLE ACCESS CLUSTER | SEG$ |
|* 25 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# |
| 26 | NESTED LOOPS | |
|* 27 | INDEX RANGE SCAN | I_OBJAUTH1 |
|* 28 | FIXED TABLE FULL | X$KZSRO |
|* 29 | FIXED TABLE FULL | X$KZSPR |
-------------------------------------------------------------------------
Execution plan
structure
First rule for reading an execution plan
Parent operations get input only from their children
Tanel Põder
Second rule for reading an execution plan
Data access starts from the first line without children
-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | NESTED LOOPS OUTER | |
|* 3 | HASH JOIN OUTER | |
| 4 | NESTED LOOPS OUTER | |
| 5 | NESTED LOOPS OUTER | |
|* 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | USER$ |
| 8 | NESTED LOOPS | |
|* 9 | HASH JOIN | |
| 10 | MERGE JOIN CARTESIAN | |
|* 11 | HASH JOIN | |
|* 12 | FIXED TABLE FULL | X$KSPPI |
| 13 | FIXED TABLE FULL | X$KSPPCV |
| 14 | BUFFER SORT | |
| 15 | TABLE ACCESS FULL | TS$ |
|* 16 | TABLE ACCESS FULL | TAB$ |
|* 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 18 | INDEX UNIQUE SCAN | I_OBJ1 |
| 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 20 | INDEX UNIQUE SCAN | I_OBJ1 |
| 21 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 22 | INDEX UNIQUE SCAN | I_OBJ1 |
| 23 | TABLE ACCESS FULL | USER$ |
| 24 | TABLE ACCESS CLUSTER | SEG$ |
|* 25 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# |
| 26 | NESTED LOOPS | |
|* 27 | INDEX RANGE SCAN | I_OBJAUTH1 |
|* 28 | FIXED TABLE FULL | X$KZSRO |
|* 29 | FIXED TABLE FULL | X$KZSPR |
-------------------------------------------------------------------------
Execution plan
structure
First operation with no children
(leaf operation) accesses data
Tanel Põder
Cascading rowsources
Data access starts from the first line without children
-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | NESTED LOOPS OUTER | |
|* 3 | HASH JOIN OUTER | |
| 4 | NESTED LOOPS OUTER | |
| 5 | NESTED LOOPS OUTER | |
|* 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | USER$ |
| 8 | NESTED LOOPS | |
|* 9 | HASH JOIN | |
| 10 | MERGE JOIN CARTESIAN | |
|* 11 | HASH JOIN | |
|* 12 | FIXED TABLE FULL | X$KSPPI |
| 13 | FIXED TABLE FULL | X$KSPPCV |
| 14 | BUFFER SORT | |
| 15 | TABLE ACCESS FULL | TS$ |
|* 16 | TABLE ACCESS FULL | TAB$ |
|* 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 18 | INDEX UNIQUE SCAN | I_OBJ1 |
| 19 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 20 | INDEX UNIQUE SCAN | I_OBJ1 |
| 21 | TABLE ACCESS BY INDEX ROWID | OBJ$ |
|* 22 | INDEX UNIQUE SCAN | I_OBJ1 |
| 23 | TABLE ACCESS FULL | USER$ |
| 24 | TABLE ACCESS CLUSTER | SEG$ |
|* 25 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# |
| 26 | NESTED LOOPS | |
|* 27 | INDEX RANGE SCAN | I_OBJAUTH1 |
|* 28 | FIXED TABLE FULL | X$KZSRO |
|* 29 | FIXED TABLE FULL | X$KZSPR |
-------------------------------------------------------------------------
Execution plan
structure
USER$
X$KSPPI
HASH
JOIN
SELECT
processor cursor
app.
X$KSPPCV
NL
JOIN
ro
w
so
ur
ce
r
o
w
s
o
u
r
c
e
HASH
JOIN
ro
w
so
ur
ce
RS
HASH
JOIN
RS
RS
MERGE
CART.
JOIN
RS
TS$
TAB$
RS
row
source
OBJ$
row
source
Buffer
SORT
RS
RS
Tanel Põder
SQL execution plan recap
Execution plan lines are just Oracle kernel functions!
� In other words, each row source is a function
Data can only be accessed using access path functions
� Only access paths can access physical data
� Access paths process physical data, return row sources
Data processing starts from first line without children
� In other words the first leaf access path in execution plan
Row sources feed data to their parents
� Can be non-cascading, semi-cascading or cascading
A join operation can input only two row sources
� However, it is possible to combine result of more than 2 row
sources for some operations (not for joins though)
� Index combine, bitmap merging, filter, union all, for example
Tanel Põder
Troubleshooting: Reading DBMS_XPLAN execution plan profile
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 56bs32ukywdsq, child number 0
-------------------------------------
select count(*) from dba_tables
Plan hash value: 736297560
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-----------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.38 |
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 1690 | 1688 |00:00:00.37 |
| 3 | TABLE ACCESS FULL | USER$ | 1 | 68 | 68 |00:00:00.01 |
|* 4 | HASH JOIN OUTER | | 1 | 1690 | 1688 |00:00:00.37 |
|* 5 | HASH JOIN | | 1 | 1690 | 1688 |00:00:00.22 |
| 6 | TABLE ACCESS FULL | USER$ | 1 | 68 | 68 |00:00:00.01 |
|* 7 | HASH JOIN | | 1 | 1690 | 1688 |00:00:00.21 |
| 8 | NESTED LOOPS OUTER | | 1 | 1690 | 1691 |00:00:00.06 |
|* 9 | HASH JOIN RIGHT OUTER | | 1 | 1690 | 1691 |00:00:00.05 |
| 10 | TABLE ACCESS FULL | SEG$ | 1 | 5044 | 5041 |00:00:00.01 |
|* 11 | HASH JOIN | | 1 | 1690 | 1691 |00:00:00.02 |
| 12 | MERGE JOIN CARTESIAN| | 1 | 12 | 12 |00:00:00.01 |
|* 13 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 |
|* 14 | FIXED TABLE FULL | X$KSPPI | 1 | 1 | 1 |00:00:00.01 |
| 15 | FIXED TABLE FULL | X$KSPPCV | 1 | 100 | 1440 |00:00:00.01 |
| 16 | BUFFER SORT | | 1 | 12 | 12 |00:00:00.01 |
| 17 | TABLE ACCESS FULL | TS$ | 1 | 12 | 12 |00:00:00.01 |
|* 18 | TABLE ACCESS FULL | TAB$ | 1 | 1690 | 1691 |00:00:00.01 |
|* 19 | INDEX UNIQUE SCAN | I_OBJ1 | 1691 | 1 | 92 |00:00:00.01 |
|* 20 | TABLE ACCESS FULL | OBJ$ | 1 | 53517 | 53510 |00:00:00.05 |
| 21 | TABLE ACCESS FULL | OBJ$ | 1 | 53517 | 53514 |00:00:00.01 |
-----------------------------------------------------------------------------------------
Starts number of times the rowsource was initialized
E-rows CBO number estimated rows coming from rowsource
A-rows actual measured number of rows during last execution
A-time actual measured (and extrapolated) time spent inside a
rowsource function or under its children (cumulative)
Buffer number of buffer gets done within rowsource during last
execution
http://www.tanelpoder.com
Tanel Põder http://www.tanelpoder.com
Troubleshooting: Reading XMS/XMSH execution plan profile
SQL> @xms
SQL hash value: 2783852310 Cursor address: 00000003DCA9EF28 | Statement firs
Ch Pr Op Object ms spent Estimated Real #rows Op. ite-
ld ed ID Operation Name in op. output rows returned rations
--- -- ---- ---------------------------------------- ---------- ------------ ----------- ---------- ----------
0 0 SELECT STATEMENT
1 SORT AGGREGATE 374.36 1 1 1
A 2 HASH JOIN RIGHT OUTER 373.17 1690 1688 1
3 TABLE ACCESS FULL USER$ .13 68 68 1
A 4 HASH JOIN OUTER 366.53 1690 1688 1
A 5 HASH JOIN 222.34 1690 1688 1
6 TABLE ACCESS FULL USER$ .10 68 68 1
A 7 HASH JOIN 214.32 1690 1688 1
8 NESTED LOOPS OUTER 55.61 1690 1691 1
A 9 HASH JOIN RIGHT OUTER 48.82 1690 1691 1
10 TABLE ACCESS FULL SEG$ 5.08 5044 5041 1
A 11 HASH JOIN 23.67 1690 1691 1
12 MERGE JOIN CARTESIAN 7.00 12 12 1
A 13 HASH JOIN 6.70 1 1 1
F 14 FIXED TABLE FULL X$KSPPI .96 1 1 1
15 FIXED TABLE FULL X$KSPPCV 1.45 100 1440 1
16 BUFFER SORT .28 12 12 1
17 TABLE ACCESS FULL TS$ .20 12 12 1
F 18 TABLE ACCESS FULL TAB$ 6.56 1690 1691 1
A 19 INDEX UNIQUE SCAN I_OBJ1 3.40 1 92 1691
F 20 TABLE ACCESS FULL OBJ$ 53.54 53517 53510 1
21 TABLE ACCESS FULL OBJ$ .04 53517 53514 1
Ch Op
ld ID Predicate Information (identified by operation id):
--- ------ ---------------------------------------------------------------------------------------
0 2 - access("CX"."OWNER#"="CU"."USER#")
4 - access("T"."DATAOBJ#"="CX"."OBJ#")
5 - access("O"."OWNER#"="U"."USER#")
7 - access("O"."OBJ#"="T"."OBJ#")
9 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#"="S"."TS#")
ms spent in op. milliseconds spent in rowsource function
(cumulative)
Estimated rows CBO rowcount estimate
Real # rows Real measured rowcount from rowsource
Op. iterations Number of times the rowsource fetch was initialized
Logical reads Consistent buffer gets
Logical writes Current mode buffer gets (Note that some CUR gets
may not always be due writing...)
Physical reads Physial reads done by the rowsource function
Physical writes Physical writes done by the rowsource function
Optimizer cost Least significant thing for measuring the real
execution efficiency of a statement
Tanel PõderTanel Poder
Advanced Troubleshooting - Reading process stack
$ pstack 5855
#0 0x00c29402 in __kernel_vsyscall ()
#1 0x005509e4 in semtimedop () from /lib/libc.so.6
#2 0x0e5769b7 in sskgpwwait ()
#3 0x0e575946 in skgpwwait ()
#4 0x0e2c3adc in ksliwat ()
#5 0x0e2c3449 in kslwaitctx. ()
#6 0x0b007261 in kjusuc ()
#7 0x0c8a7961 in ksipgetctx ()
#8 0x0e2d4dec in ksqcmi ()
#9 0x0e2ce9b8 in ksqgtlctx ()
#10 0x0e2cd214 in ksqgelctx. ()
#11 0x08754afa in ktcwit1 ()
#12 0x0e39b2a8 in kdddgb ()
#13 0x08930c80 in kdddel ()
#14 0x0892af0f in kaudel ()
#15 0x08c3d21a in delrow ()
#16 0x08e6ce16 in qerdlFetch ()
#17 0x08c403c5 in delexe ()
#18 0x0e3c3fa9 in opiexe ()
#19 0x08b54500 in kpoal8 ()
#20 0x0e3be673 in opiodr ()
#21 0x0e53628a in ttcpip ()
#22 0x089a87ab in opitsk ()
#23 0x089aaa00 in opiino ()
#24 0x0e3be673 in opiodr ()
#25 0x089a4e76 in opidrv ()
#26 0x08c1626f in sou2o ()
#27 0x08539aeb in opimai_real ()
#28 0x08c19a42 in ssthrdmain ()
#29 0x08539a68 in main ()
Where to look up the meaning of Oracle
kernel function names?
1) Metalink:
175982.1 ORA-600 Lookup Error Categories
453521.1 ORA-04031 “KSFQ Buffers”
ksmlgpalloc
Search: "executable entry p
本文档为【Oracle_SQL_Plan_Execution】，请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑，
图片更改请在作品中右键图片并更换，文字修改请直接点击文字进行修改，也可以新增和删除文档中的内容。
该文档来自用户分享，如有侵权行为请发邮件ishare@vip.sina.com联系网站客服，我们会及时删除。
[版权声明] 本站所有资料为用户分享产生，若发现您的权利被侵害，请联系客服邮件isharekefu@iask.cn，我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权，请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传，仅限个人学习分享使用，禁止用于任何广告和商用目的。
浙公网安备 33021202002483