8/13/2019 200503 Plans

1/20

NYOUG Spring Meeting 2005

Interpreting Execution Plans

Tanel Pderttp!""integrid#in$o

8/13/2019 200503 Plans

2/20

Introduction

Name: Tanel PderOccupation: Independent consultantCompany: integrid.infoOracle experience: 8 years as DB

Oracle Certified !asterOa"Ta#le Net$or" !em#er%!% Oracle &ser 'roup director

T(is presentation is a#out:&nderstanding execution plansT(is presentation is not a#out:

'enerating execution plansTuning )ueries

8/13/2019 200503 Plans

3/20

%at is an execution plan&

*or Oracle ser+er:Parsed, optimi-ed and compiled /0 code "eptinside li#rary cac(e

*or DBs and de+elopers:Text or grap(ical representation of /0

execution process flo$Often "no$n as explain plan

To #e correct in terms, explain plan is 1ust atool, command in Oracle

%xplain plan outputs textual representation ofexecution plan into plan ta#leDBs2de+elopers report (uman reada#le outputfrom plan ta#le

8/13/2019 200503 Plans

4/20

One slide $or getting execution plan

tarting from 3.4 t(e recommended $ay isexplain plan into plan ta#leselect 5 from ta#le6d#ms7xplan.display

Ot(er met(odss)l7trace29;< trace = t"prof

+>s)l7plane+ent 99?4 6at le+el 9?rd party tools 6$(ic( use explain plan any$ay

set termout off

store set tmp/env_&_connect_identifier..sql replace

save tmp/explain_&_connect_identifier..sql replace0 explain plan for

runset termout on

select * from table(dbms_xplan.display);

@@tmp/env_&_CONNECT_IDENTIFIER..sqlget tmp/explain_&_CONNECT_IDENTIFIER..sqlset termout on

dbms_xplan demo

8/13/2019 200503 Plans

5/20

S'( state)ent li$ec*cle

Parsetatement c(ec"ed, optimi-ed, cac(ed

Bind%xecute

tatement executed

D!0,DD0,DC0 are executed immediatelyelects aren@t neccessarily A exception is sorting

*etc(ill start actually retrie+ing result ro$s

o$ many at a time depends on arraysi-ee#ind, executee#ind, execute...

8/13/2019 200503 Plans

6/20

Parse stages

yntactic c(ec"yntax, "ey$ords, sanity

emantic c(ec"(et(er o#1ects referenced exist, areaccessi#le 6#y permissions and are usa#le

Eie$ merging/ueries are $ritten to reference #ase ta#lesCan merge #ot( stored +ie$s and inline +ie$s

/uery transformation

Transiti+ity, etcOptimi-ation/uery execution plan 6/%P generationtoring /0 and execution plan in cac(e

hard

parse

soft

pars

e

8/13/2019 200503 Plans

7/20

+eading execution plan

/0 ro$source execution starts from t(etop rig(tmost operationo$ sources are generated from ta#lesNext stages in plan execution use

ro$sources from pre+ious operationsOnly t$o ro$ sources can #e 1oinedtoget(er at a timeF

o$e+er, some operations are cascading, t(ust(e $(ole 1oin doesn@t (a+e to #e done to passresults on for furt(er operationsNested loop is cascadingas( 1oin is semiAcascadingortAmerge 1oin is not cascading

8/13/2019 200503 Plans

8/20

Si)ple $ull ta,le scan

*ull ta#le scan scans all t(e ro$s in t(e ta#lell ta#le #loc"s are scanned up to t(e !%+en if all ro$s (a+e #een deleted from ta#leOracle uses multi#loc" reads $(ere it can!ost efficient $ay $(en )uerying ma1ority of ro$s

SQL> select * from emp;

PLAN_TABLE_OUTPUT----------------------------------------------------------------------------

Plan hash value: 4080710170

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

8/13/2019 200503 Plans

9/20

-ull ta,le scan .it a $ilter

Filteroperation t(ro$s a$ay nonAmatc(ing ro$sBy definition, not t(e most efficient operation*ilter conditions can #e seen in predicate section

SQL> select * from emp where ename = 'KING';

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------

Plan hash value: 4080710170

--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |

|* 1| TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ENAME"='KING')

8/13/2019 200503 Plans

10/20

Si)ple /tree index1ta,le access

Index tree is $al"ed from root to leafGey +alues and OIDs are gotten from indexTa#le ro$s are gotten using OIDsAccessoperation fetc(es only matc(ing ro$s

SQL> select * from emp where empno = 10;

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)|| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)|

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)|

---------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

2 - access("EMPNO"=10)

accespath.sql

8/13/2019 200503 Plans

11/20

So)e ter)inolog*

ccess pat( A a means to read data from ta#les,indexes 6also external ta#les*ilter A an operation for t(ro$ing out nonAmatc(ing ro$s 6or computing aggregates

SQL> select * from emp

2 where empno > 7000

3 and ename like 'KING%';

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)|

|* 1| TABLE ACCESS BY INDEX ROWID| EMP | 1 | 27 | 3 (0)|

|* 2| INDEX RANGE SCAN | PK_EMP | 9 | | 2 (0)|

---------------------------------------------------------------------------Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------

1 - filter("ENAME" LIKE 'KING%')

2 - access("EMPNO">7000)

8/13/2019 200503 Plans

12/20

Index $ast $ull scan

Doesn@t necessarily return "eys in orders $(ole index segment is 1ust scanned as Oraclefinds #loc"s on dis" 6in contrast to tree $al"ing!ulti#loc" reads are useds indexes don@t usually contain all columns t(at

ta#les do, ** is more efficient if re)uired columnsare indexed&sed mainly for aggregate functions, min2a+g2sum,etcOptimi-er must "no$ t(at all ta#le ro$s arerepresented in indexF 6null +alues and count example

SQL> select min(empno), max(empno) from emp;

---------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

---------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 25 (0)|| 1 | SORT AGGREGATE | | 1 | 5 | |

| 2 | INDEX FAST FULL SCAN| PK_EMP | 54121 | 264K| 25 (0)|

---------------------------------------------------------------------

8/13/2019 200503 Plans

13/20

Nested loop oin

Nested loop 1oinead data from outer ro$ source 6upper onePro#e for a matc( in inner ro$ source

SQL> select d.dname, d.loc, e.empno, e.ename 2 from emp e, dept d 3 where e.deptno = d.deptno

4 and d.dname = 'SALES' 5 and e.ename like 'K%';

------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 4 || 1 | NESTED LOOPS | | 1 | 37 | 4 ||* 2 | TABLE ACCESS FULL | EMP | 1 | 17 | 3 |

|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 ||* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | |------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------- 2 - filter("E"."DEPTNO" IS NOT NULL AND "E"."ENAME" LIKE 'K%') 3 - filter("D"."DNAME"='SALES')

4 - access("E"."DEPTNO"="D"."DEPTNO")

8/13/2019 200503 Plans

14/20

3as 4oin

Only for e)ui1oins and nonAe)ui1oinsBuilds an array $it( (as(ed "ey +alues from smallerro$ sourcecans t(e #igger ro$ source, #uilds and compares(as(ed "ey +alues on t(e fly, returns matc(ing ones

SQL> select d.dname, d.loc, e.empno, e.ename

2 from emp e, dept d 3 where e.deptno = d.deptno 4 and d.dname = 'SALES' 5 and e.ename between 'A%' and 'M%';

----------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 9 (12)|

|* 1 | HASH JOIN | | 1 | 37 | 9 (12)||* 2 | TABLE ACCESS FULL| DEPT | 1 | 20 | 2 (0)||* 3 | TABLE ACCESS FULL| EMP | 4 | 68 | 6 (0)|

----------------------------------------------------------------Predicate Information (identified by operation id):

--------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("D"."DNAME"='SALES')

3 - filter("E"."DEPTNO" IS NOT NULL AND "E"."ENAME"='A%')

8/13/2019 200503 Plans

15/20

Sort )erge oin

e)uires #ot( ro$sources to #e sorted%it(er #y a sort operationOr sorted #y access pat( 6index range and full scan

Cannot return any ro$s #efore #ot( ro$sourcesare sorted 6nonAcascading

N0 and as( 1oin s(ould #e preferredSQL> select /*+ USE_MERGE(d,e) */ d.dname, d.loc, e.empno, e.ename 2 from emp e, dept d

3 where e.deptno = d.deptno 4 and d.dname = 'SALES' 5 and e.ename between 'A%' and 'X%'

6 order by e.deptno;

-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1245 | 46065 | 64 (10)|| 1 | MERGE JOIN | | 1245 | 46065 | 64 (10)|

|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)|

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|

|* 4 | SORT JOIN | | 3735 | 63495 | 62 (10)|

|* 5 | TABLE ACCESS FULL | EMP | 3735 | 63495 | 61 (9)|-----------------------------------------------------------------------------

8/13/2019 200503 Plans

16/20

ie. )erging

Optimi-er merges su#)ueries, inline and stored+ie$s and runs )ueries directly on #ase ta#les

Not al$ays possi#le t(oug(SQL> create or replace view employees 2 as

3 select e.empno, e.ename, d.dname 4 from emp e, dept d

5 where e.deptno = d.deptno;

SQL> select * from employees

2 where ename = 'KING';

------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 7 | 210 | 5 (20)||* 1 | HASH JOIN | | 7 | 210 | 5 (20)|| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)|

|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 119 | 2 (0)|

|* 4 | INDEX RANGE SCAN | EMP_ENAME| 8 | | 1 (0)|

------------------------------------------------------------------------------

8/13/2019 200503 Plans

17/20

Su,6uer* unnesting

u#)ueries can #e unnested, con+erted to antiAand semi1oins

SQL> select * from employees e 2 where exists (

3 select ename from bonus b

4 where e.ename = b.ename 5 );--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 5

| 1 | NESTED LOOPS | | 1 | 37 | 5| 2 | NESTED LOOPS | | 1 | 24 | 4| 3 | SORT UNIQUE | | 1 | 7 | 2

| 4 | TABLE ACCESS FULL | BONUS | 1 | 7 | 2|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 17 | 1

|* 6 | INDEX RANGE SCAN | EMP_ENAME | 37 | | 1| 7 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1|* 8 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0--------------------------------------------------------------------------

Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("E"."DEPTNO" IS NOT NULL)

6 - access("E"."ENAME"="B"."ENAME")

8 - access("E"."DEPTNO"="D"."DEPTNO")

8/13/2019 200503 Plans

18/20

Multile7el oins

Top rig(tmost operation is executed firstT(en t(e one6s #elo$ it at same indentation le+elT(en 1ump one le+el #ac" in indentation and continueCascading operators allo$ space efficient 1oining

SQL> select e.empno, e.ename, e.dname, b.comm 2 from employees e, bonus b 3 where e.ename = b.ename;

--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (

--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 50 | 4| 1 | NESTED LOOPS | | 1 | 50 | 4

| 2 | NESTED LOOPS | | 1 | 37 | 3| 3 | TABLE ACCESS FULL | BONUS | 1 | 20 | 2

|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 17 | 1|* 5 | INDEX RANGE SCAN | EMP_ENAME | 37 | | 1

| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0--------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

4 - filter("E"."DEPTNO" IS NOT NULL)

5 - access("E"."ENAME"="B"."ENAME") 7 - access("E"."DEPTNO"="D"."DEPTNO")

8/13/2019 200503 Plans

19/20

8onclusion

To gain understanding, experimenting is needed'at(er statistics&se explain plan2d#ms7xplanun t(e statement and gat(er execution statistics

0ogical IOs, CP& usage, sorts etc from E>/0, E>%IONE>/07P0N7TTITICH700, CBO29J? trace

&sual pro#lemsNot accurate enoug( statisticsBad /0optimi-er7index7cac(ing, optimi-er7index7cost7ad1

7complex7+ie$7merging, 7unnest7su#)uerysort7area7si-e, (as(7area7si-eOptimi-er #ugs

!etalin" note: KInterpreting explain plansL

8/13/2019 200503 Plans

20/20

'uestions&

Tan9 *ou:

Tanel Pdertanel;integrid#in$ottp!""integrid#in$o