Download - Use Explain
-
7/23/2019 Use Explain
1/56
1
Use EXPLAIN PLAN andTKPROF To Tune Your
ApplicationsRoger Schrag
Database Specialists, Inc.
www.dbspecialists.com
-
7/23/2019 Use Explain
2/56
2
Todays Session
EXPLAIN PLAN overview
TKPROF overview
Why??? Reading execution !an"
Reading TKPROF reort"
-
7/23/2019 Use Explain
3/56
3
!ite Paper
Twenty one age" o# detai!" I can$t o""i%!y
cover in a one hour re"entation&
Lot" o# "a'!e code( execution !an"( and
TKPROF reort" that you wi!! "ee are ro%a%!ynot reada%!e when I ut the' u on PowerPoint
"!ide")%ut they are reada%!e in the white aer*
+own!oad, www*d%"ecia!i"t"*co'-re"entation"
-
7/23/2019 Use Explain
4/56
4
E"ecution Plans and
EXPLAIN PLAN An execution planis a list of steps that Oracle will
follow in order to execute a SQL statement. achstep is one of a finite number of basic operations
!nown to the database ser"er. "en the mostcomplex SQL statement can be bro!en down into aseries of basic operations.
#$LAI% $LA%is a statement that allows &ou to
ha"e Oracle generate the execution plan for an&SQL statement without actuall& executing it. 'ouwill be able to examine the execution plan b&(uer&ing the plan table.
-
7/23/2019 Use Explain
5/56
5
T!e Plan Ta#le
A!an ta%!eho!d" execution !an" generated
%y the EXPLAIN PLAN "tate'ent*
The typical name for a plan tableis plan_table, but you may use any
name you wish.
Create the plan table by runningutlxplan.sql, located in
$ORACLE_HOME/rdbms/admin.
-
7/23/2019 Use Explain
6/56
6
I$portant %olu$ns
in t!e Plan Ta#le"tate'ent.id /ni0ue identi#ier #or each execution !an
ti'e"ta' When the execution !an wa" generated
oeration The oeration er#or'ed in one "te o# the execution!an( "uch a" 1ta%!e acce""2
otion"Additiona! in#or'ation a%out the oeration( "uch a" 1%yindex ROWI+2
o%3ect.na'e Na'e o# ta%!e( index( view( etc* acce""ed
oti'i4er Oti'i4er goa! u"ed when creating execution !anid 5te nu'%er in execution !an
arent.id 5te nu'%er o# arent "te
-
7/23/2019 Use Explain
7/56
7
EXPLAIN PLANPrere&uisites
IN5ERT rivi!ege on a !an ta%!e
A!! nece""ary rivi!ege" to execute the
"tate'ent %eing ex!ained 5ELE6T rivi!ege" on under!ying ta%!e" o#
view"( i# the "tate'ent %eing ex!ained
invo!ve" view"
-
7/23/2019 Use Explain
8/56
8
EXPLAIN PLAN Synta"
EXPLAIN PLAN
[SET STATEMENT_ID = ]
[INTO &
-
7/23/2019 Use Explain
9/56
9
'ueryin( an E"ecution
Plan )ro$ t!e Plan Ta#le )se a *O%%*+ ' clause to trace the hierarch&
)se L$AD function to indent rows, ma!ing the
hierarch& easier to follow
$ut statement-id in /R clause to retrie"e onl&
one execution plan at a time
Sample script on next slide shows the most
important information
'ou can also tr& utlxpls.s(l or utlxplp.s(l in
0ORA*L-/O12rdbms2admin
-
7/23/2019 Use Explain
10/56
10
A Si$ple 'uery to *isplay
E"ecution PlansSET 'E$I#( O##
A))EPT st"t_i* )+A$ P$OMPT ,Enter stte"ent_i*- ,)OL i* #O$MAT ...)OL prent_i* #O$MAT ... +EADIN/ ,PA$ENT,)OL opertion #O$MAT 01 T$2N)ATE)OL o!3e4t_n"e #O$MAT 05SELE)T i*6 prent_i*6 LPAD 78 86 LE'EL 9 :; opertion 8 8 options opertion6 o!3e4t_n"e
#$OM pln_t!le+E$E stte"ent_i* = 8st"t_i*8STA$T IT+ i* = 5
AND stte"ent_i* = 8st"t_i*8)ONNE)T ?( P$IO$ i* = prent_i*
AND stte"ent_i* = 8st"t_i*8&
-
7/23/2019 Use Explain
11/56
11
A Sa$ple E"ecution PlanS%L> EXPLAIN PLAN SET stte"ent_i* = 8*e"o8 #O$ @ SELE)T 4usto"er_n"e6 4usto"er_nu"!er6 !inBoi4e_nu"!er60 !inBoi4e_tCpe6 !inBoi4e_*te6 !totl_"ount6 4line_nu"!er6 4prt_nu"!er6 4quntitC6 4unit_4ost
1 #$OM 4usto"ers 6 inBoi4es !6 inBoi4e_ite"s 4 +E$E 4inBoi4e_i* = -!: F AND 4line_nu"!er = -!@ G AND !inBoi4e_i* = 4inBoi4e_i* . AND 4usto"er_i* = !4usto"er_i*&EHpline*S%L> eHplinsqlEnter stte"ent_i*- *e"o ID PA$ENT OPE$ATION O?JE)T_NAME9999 999999 99999999999999999999999999999999999 99999999999999999 5 SELE)T STATEMENT : 5 NESTED LOOPS
@ : NESTED LOOPS 0 @ TA?LE A))ESS ?( INDEX $OID IN'OI)E_ITEMS 0 INDEX 2NI%2E S)AN IN'OI)E_ITEMS_PK 1 @ TA?LE A))ESS ?( INDEX $OID IN'OI)ES 1 INDEX 2NI%2E S)AN IN'OI)ES_PK F : TA?LE A))ESS ?( INDEX $OID )2STOME$S G F INDEX 2NI%2E S)AN )2STOME$S_PK
-
7/23/2019 Use Explain
12/56
12
Ot!er ays to
+ie, E"ecution Plans The autotrace #eature in 57L8P!u"
Per#or'ance tuning too!"
6hec9 out TOA+ #ro' 7ue"t 5o#tware at,
htt,--www*toad"o#t*co'-down!d*ht'!
SET A2TOT$A)E O##ONT$A)EONL( [EXPLAIN] [STATISTI)S]
-
7/23/2019 Use Explain
13/56
13
Sa$ple Autotrace
Output in S'L-PlusEHe4ution Pln9999999999999999999999999999999999999999999999999999999999 5 SELE)T STATEMENT Opti"ier=)+OOSE 7)ost= )r*=: ?Ctes=0.; : 5 NESTED LOOPS 7)ost= )r*=: ?Ctes=0.; @ : NESTED LOOPS 7)ost=0 )r*=: ?Ctes=@F;
0 @ TA?LE A))ESS 7?( INDEX $OID; O# 8IN'OI)E_ITEMS8 7)ost =@ )r*=: ?Ctes=:1; 0 INDEX 72NI%2E S)AN; O# 8IN'OI)E_ITEMS_PK8 72NI%2E; 7 )ost=: )r*=@; 1 @ TA?LE A))ESS 7?( INDEX $OID; O# 8IN'OI)ES8 7)ost=: ) r*=@ ?Ctes=@; 1 INDEX 72NI%2E S)AN; O# 8IN'OI)ES_PK8 72NI%2E; F : TA?LE A))ESS 7?( INDEX $OID; O# 8)2STOME$S8 7)ost=: )r *=:55 ?Ctes=:@55; G F INDEX 72NI%2E S)AN; O# 8)2STOME$S_PK8 72NI%2E;
-
7/23/2019 Use Explain
14/56
14
Sa$ple Plan *isplay inTOA*
-
7/23/2019 Use Explain
15/56
15
EXPLAIN PLAN
Li$itations +he #$LAI% $LA% statement pro"ides a good faithestimate of the execution plan that Oracle would use.
+he real plan that gets used ma& differ from what
#$LAI% $LA% tells &ou for man& reasons3
: Optimi4er stats, cursor sharing,, bind "ariable pee!ing,
d&namic instance parameters ma!e plans less stable.
: #$LAI% $LA% does not pee! at bind "ariables.
: #$LAI% $LA% does not chec! the librar& cache to see if
the statement has alread& been parsed.
#$LAI% $LA% does not wor! for some (ueries3
O$A9@@.51- 4nnot 44ess ros ro" non9neste* t!le ite"
-
7/23/2019 Use Explain
16/56
16
+ie,in( Actual E"ecution
Plans +he "0s(l "iew shows statements in the librar& cache. /ere &ou can
find the address, hash "alue, and child number for a statement of
interest.
+he "0s(l-plan "iew shows the actual execution plan for each
statement, gi"en its address, hash "alue, and child number. +he
columns are similar to the plan table.
+he "0s(l-plan-statistics "iew shows actual statistics 5rows, buffer
gets, elapsed time, etc.6 for each operation of the execution plan.
+he "0s(l-plan and "0s(l-plan-statistics "iews are a"ailable starting
in Oracle 7i. "0s(l-plan-statistics is not populated b& default.
-
7/23/2019 Use Explain
17/56
17
A Si$ple 'uery to *isplay
Actual E"ecution PlansSET 'E$I#( O##)OL i* #O$MAT ...)OL prent_i* #O$MAT ... +EADIN/ ,PA$ENT,)OL opertion #O$MAT 01 T$2N)ATE
)OL o!3e4t_n"e #O$MAT 05SELE)T i*6 prent_i*6 LPAD 78 86 LE'EL 9 :;
opertion 8 8 options opertion6 o!3e4t_n"e#$OM 7 SELE)T i*6 prent_i*6 opertion6 options6 o!3e4t_n"e #$OM Bsql_pln
+E$E **ress = 8**ress8 AND s_Blue = s_Blue AND 4il*_nu"!er = 4il*_nu"!er ;STA$T IT+ i* = 5)ONNE)T ?( P$IO$ i* = prent_i*&
-
7/23/2019 Use Explain
18/56
18
Trace Files and TKPROF +he Oracle ser"er process managing a database session
writes a "erbosetrace filewhen SQL trace is enabled for the
session.
+8$RO9is a utilit& pro"ided b& Oracle that formats SQL
trace files into "er& helpful and readable reports. +8$RO9 isinstalled automaticall& when the database ser"er software is
installed. 'ou in"o!e +8$RO9 from the operating s&stem
command line: there is no graphical interface for +8$RO9.
Starting in Oracle 7i +8$RO9 can read extended SQL tracefiles and report on wait e"ents statistics.
-
7/23/2019 Use Explain
19/56
19
Ena#lin( S'L Trace
At the instance le"el3
sql_tr4e = true
ti"e*_sttisti4s = true (optional)
In &our own session3ALTE$ SESSION SET sql_tr4e = T$2E&
ALTE$ SESSION SET ti"e*_sttisti4s = T$2E&(optional)
In another session3
S(S*!"s_sCste"set_sql_tr4e_in_session
76 6 T$2E;
-
7/23/2019 Use Explain
20/56
20
Findin( t!e Trace File
Loo! in the user dump destination. On O9Acompliant s&stems this will be0ORA*L-AS2admin20ORA*L-SID2udump
*hec! timestamps and file contents to seewhich trace file is &ours
If non;DAs need access to trace files, add_tr4e_iles_pu!li4 = trueto the
parameter file to a"oid permissions problemson )nix platforms
)se a dedicated ser"er connection whentracing, if possible.
-
7/23/2019 Use Explain
21/56
21
For$attin( a Trace File
,it! TKPROFIn"o!e +8$RO9 from the operatings&stem prompt li!e this3
tRpro [eHplin=
-
7/23/2019 Use Explain
22/56
22
TKPROF %o$$and.line
Ar(u$ents
trace file The SQL trace file to be formattedoutput file The formatted output to be writte b! T"#$%&
e'plai( )ataba*e lo+i to be u*ed if !ou wat the output to
iclude e'ecutio pla*
*!*( %mit ,recur*i-e SQL. performed b! the S/S u*er*ort( Li*t traced SQL *tatemet i the output file i a
*pecific order
tRpro [eHplin=
-
7/23/2019 Use Explain
23/56
23
TKPROF Sa$ple Output
SELE)T 4usto"er_n"e6 4usto"er_nu"!er6 !inBoi4e_nu"!er6 !inBoi4e_tCpe6 !inBoi4e_*te6 !totl_"ount6 4line_nu"!er6 4prt_nu"!er6 4quntitC6 4unit_4ost#$OM 4usto"ers 6 inBoi4es !6 inBoi4e_ite"s 4
+E$E 4inBoi4e_i* = -!:AND 4line_nu"!er = -!@AND !inBoi4e_i* = 4inBoi4e_i*
AND 4usto"er_i* = !4usto"er_i*
4ll 4ount 4pu elpse* *isR querC 4urrent ros9999999 999999 99999999 9999999999 9999999999 9999999999 9999999999 9999999999Prse : 551 55@ 5 5 5 5EHe4ute : 555 555 5 5 5 5#et4 @ 555 555 G G 5 :9999999 999999 99999999 9999999999 9999999999 9999999999 9999999999 9999999999
totl 551 55@ G G 5 :
Misses in li!rrC 44e *uring prse- :Opti"ier gol- )+OOSEPrsing user i*- 0 7$S)+$A/;
-
7/23/2019 Use Explain
24/56
24
!y Loo/ at E"ecutionPlans
and TKPROF Reports0
$roacti"el& tune applications that are in de"elopment
Reacti"el& tune production s&stems that are experiencing
performance problems
stimate resource needs or feasibilit& of ad;hoc (ueries
Quantif& resource needs for specific applications
+hese tools are critical to the application tuningprocess, and tuning at the application le"el is necessar&for high performance s&stems. ith #$LAI% $LA%,"0s(l-plan, and +8$RO9, &ou can3
-
7/23/2019 Use Explain
25/56
25
Readin( E"ecution Plans
-
7/23/2019 Use Explain
26/56
26
E"ecution Plan E"a$ple12
SELE)T 4usto"er_i*6 4usto"er_n"e#$OM 4usto"ers
+E$E 2PPE$ 74usto"er_n"e; LIKE 8A)MEU8O$DE$ ?( 4usto"er_n"e&
OPE$ATION O?JE)T_NAME999999999999999999999999999999 99999999999999SELE)T STATEMENTSO$T O$DE$ ?(
TA?LE A))ESS #2LL )2STOME$S
-
7/23/2019 Use Explain
27/56
27
E"ecution PlanOperations
TA?LE A))ESS #2LL
$erform a full table scan of the indicated table and retrie"e
all rows that meet criteria from the /R clause.Input3no subordinate operations.Output3the necessar& columnsfrom the rows meeting all criteria.
SO$T O$DE$ ?(
Sort the input rows for the purpose of satisf&ing an ORDR' clause.Input3the rows to be sorted.Output3the rows insorted order.
-
7/23/2019 Use Explain
28/56
28
E"ecution Plan E"a$ple13
SELE)T 4usto"er_n"e6 !inBoi4e_nu"!er6!inBoi4e_*te
#$OM 4usto"ers 6 inBoi4es !+E$E !inBoi4e_*te > T$2N) 7S(SDATE 9 :;
AND 4usto"er_i* = !4usto"er_i*&
OPE$ATION O?JE)T_NAME999999999999999999999999999999 99999999999999SELE)T STATEMENT
NESTED LOOPS TA?LE A))ESS ?( INDEX $OID IN'OI)ES INDEX $AN/E S)AN IN'OI)ES_DATE TA?LE A))ESS ?( INDEX $OID )2STOME$S INDEX 2NI%2E S)AN )2STOME$S_PK
-
7/23/2019 Use Explain
29/56
29
E"ecution PlanOperations
INDEX 2NI%2E S)AN
Loo! up a complete !e& in a uni(ue index.Input3usuall&no subordinate operations. 58e& "alues t&picall& come
from the original (uer& or a parent operation.6Output34ero or one ROIDs from the index.
INDEX $AN/E S)AN
Loo! up a !e& in a non;uni(ue index, or an incomplete!e& in a uni(ue index.Input3usuall& no subordinateoperations.Output34ero or more ROIDs from the index.
-
7/23/2019 Use Explain
30/56
30
E"ecution PlanOperations
TA?LE A))ESS ?( INDEX $OID
Loo! up rows in a table b& their ROIDs.Input3a list ofROIDs to loo! up.Output3the necessar& columns
from the rows with the gi"en ROIDs.
NESTED LOOPS
$erform a >oin between two sets of row data using the
nested loops algorithm.Inputs3two separate sets of rowdata.Output3the results of the >oin.
9or each row Oracle reads from the first input, theoperations that ma!e up the second input are executed
once and matching rows generate output.
-
7/23/2019 Use Explain
31/56
31
E"ecution Plan E"a$ple14
SELE)T 4usto"er_n"e6)O2NT 7DISTIN)T !inBoi4e_i*; open_inBs6
)O2NT 74inBoi4e_i*; open_inB_ite"s#$OM 4usto"ers 6 inBoi4es !6 inBoi4e_ite"s 4+E$E !inBoi4e_sttus = 8OPEN8AND 4usto"er_i* = !4usto"er_i*
AND 4inBoi4e_i* 7V; = !inBoi4e_i*/$O2P ?( 4usto"er_n"e&
OPE$ATION O?JE)T_NAME99999999999999999999999999999999 9999999999999999SELE)T STATEMENTSO$T /$O2P ?(
NESTED LOOPS O2TE$ +AS+ JOIN TA?LE A))ESS ?( INDEX $OID IN'OI)ES INDEX $AN/E S)AN IN'OI)ES_STAT2S TA?LE A))ESS #2LL )2STOME$S INDEX $AN/E S)AN IN'OI)E_ITEMS_PK
-
7/23/2019 Use Explain
32/56
32
E"ecution PlanOperations
+AS+ JOIN
$erform a >oin between two sets of row data usingthe hash >oin algorithm.Inputs3two separate sets of
row data.Output3the results of the >oin.
Oracle reads all rows from the second input andbuilds a hash structure, before reading each rowfrom the first input one at a time. 9or each row fromthe first input, the hash structure is probed andmatching rows generate output.
-
7/23/2019 Use Explain
33/56
33
E"ecution PlanOperations
NESTED LOOPS O2TE$
Same as the %S+D LOO$S operation, except
that an outer >oin is performed.
SO$T /$O2P ?(
Same as the SOR+ ORDR ' operation, except
that the rows are sorted and grouped to satisf& a?RO)$ ' clause.
-
7/23/2019 Use Explain
34/56
34
E"ecution Plan E"a$ple15
SELE)T 4usto"er_n"e#$OM 4usto"ers
+E$E EXISTS7SELE)T :
#$OM inBoi4es_Bie !
+E$E !4usto"er_i* = 4usto"er_i* AND nu"!er_o_lines > :55;O$DE$ ?( 4usto"er_n"e&
)$EATE O$ $EPLA)E 'IE inBoi4es_Bie AS
SELE)T inBoi4e_i*6 4usto"er_i*6)O2NT7W; nu"!er_o_lines
#$OM inBoi4es 6 inBoi4e_ite"s !+E$E !inBoi4e_i* = inBoi4e_i*/$O2P ?( inBoi4e_i*6 4usto"er_i*&
-
7/23/2019 Use Explain
35/56
35
E"ecution Plan E"a$ple15
6continued7OPE$ATION O?JE)T_NAME99999999999999999999999999999999999 9999999999999SELE)T STATEMENT
SO$T O$DE$ ?( #ILTE$ TA?LE A))ESS #2LL )2STOME$S 'IE IN'OI)ES_'IE #ILTE$ SO$T /$O2P ?( NESTED LOOPS TA?LE A))ESS ?( INDEX $OID IN'OI)ES INDEX $AN/E S)AN IN'S_)2ST_ID INDEX $AN/E S)AN IN'_ITEMS_PK
-
7/23/2019 Use Explain
36/56
36
E"ecution PlanOperations
#ILTE$
Read a set of row data and discard some rows basedon "arious criteria. +o determine the criteria,
operations from a second input ma& need to beperformed.Input3rows to be examined and,sometimes, an additional subordinate operation thatmust be performed for each row from the first input in
order to e"aluate criteria.Output3the rows from thefirst input that met the criteria.
-
7/23/2019 Use Explain
37/56
37
E"ecution PlanOperations
'IE
uild a ph&sical representation of a database
"iew or subset of a database "iew. Input3set ofrow data.Output3set of row data thatimplements the "iew or subset of the "iew.
-
7/23/2019 Use Explain
38/56
38
Notes on E"ecution Plan
Operations +he optimi4er rewrites sub(ueries as >oins and
merges them into the main (uer& whene"er possible.
If a sub(uer& is completel& independent of the main
(uer& and cannot be merged into the main (uer&, theoptimi4er ma& treat the sub(uer& as a separatestatement and lea"e it out of the execution plan for themain (uer&.
+he optimi4er expands "iew definitions and mergesthem into the main (uer& where"er possible. A @Ioperation will onl& appear in an execution plan whenthe "iew definition could not be merged.
-
7/23/2019 Use Explain
39/56
39
E"ecution Plan E"a$ple18
SELE)T WV $2LE W 4ust_n"e6 !4ont4t_n"e#$OM 4usto"ers 6 4ont4tssles4"e4o" !
+E$E 2PPE$7!4ont4t_n"e; = 2PPE$74ust_n"e;&
EHe4ution Pln
999999999999999999999999999999999999999999999999 5 SELE)T STATEMENT Opti"ier=+INT- $2LE : 5 ME$/E JOIN @ : SO$T 7JOIN; 0 @ $EMOTEW SALESA)ME)OM : SO$T 7JOIN; 1 TA?LE A))ESS 7#2LL; O# 8)2STOME$S8
0 SE$IAL_#$OM_$EMOTE SELE)T ,)ONTA)T_NAME,#$OM ,)ONTA)TS, ,?
-
7/23/2019 Use Explain
40/56
40
E"ecution Plan
Operations$EMOTE
Submit a SQL statement to a remote database "ia Oracle
%et.Input3t&picall& no subordinate operations.Output3the results of the (uer& from the remote database. %otethat the database lin! used to access the remotedatabase and the actual SQL submitted to the remotedatabase will be accessible from the execution plan.
-
7/23/2019 Use Explain
41/56
41
E"ecution Plan
OperationsSO$T JOIN
Same as the SOR+ ?RO)$ ' operation, except thatthe input is sorted b& the >oin column or columns inpreparation for a >oin using the merge >oin algorithm.
-
7/23/2019 Use Explain
42/56
42
E"ecution Plan
OperationsME$/E JOIN
$erform a >oin between two sets of row data using
the merge >oin algorithm.Inputs3two separate setsof row data.Output3the results of the >oin.
Oracle reads rows from both inputs in analternating fashion and merges together matching
rows in order to generate output. +he two inputsare assumed to be sorted on the >oin column orcolumns.
-
7/23/2019 Use Explain
43/56
43
Su$$ary o) Operations
- TA?LE A))ESS #2LL
- TA?LE A))ESS ?( INDEX $OID
-
INDEX 2NI%2E S)AN- INDEX $AN/E S)AN
-NESTED LOOPS
-NESTED LOOPS O2TE$
- +AS+ JOIN
-ME$/E JOIN
-#ILTE$
-'IE
- $EMOTE
- SO$T O$DE$ ?(
- SO$T /$O2P ?(
-
SO$T JOIN
e ha"e not co"ered all of the execution plan operations,but we ha"e co"ered some of the most common ones3
-
7/23/2019 Use Explain
44/56
44
Ele$ents o) a TKPROFReport
Report heading
: +8$RO9 "ersion, date run, sort option, trace file
One entr& for each distinct SQL statement in trace file
: Listing of SQL statement
: O*I call statistics3 count of parse, execute, and fetchcalls, rows processed, and time and I2O used
: $arse information3 parsing user, recursi"e depth,librar& cache misses, and optimi4er mode
: Row source operation listing
: xecution plan listing 5optional6
: ait e"ent listing 5optional6
El ) TKPROF
-
7/23/2019 Use Explain
45/56
45
Ele$ents o) a TKPROFReport
6continued7 Report Summar&
: O*I call statistics totals
: *ounts of how man& statements were found in thetrace file, how man& were distinct, and how man&were explained in the report.
-
7/23/2019 Use Explain
46/56
46
Sa$ple TKPROF Report
9eadin(TKP$O#- $elese G::5 9 Pro*u4tion on e* Aug . :.-5-0 @555
74; )opCrigt :... Or4le )orportion All rigts reserBe*
Tr4e ile- eH"pletr4Sort options- *eultWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW4ount = nu"!er o ti"es O)I pro4e*ure s eHe4ute*4pu = 4pu ti"e in se4on*s eHe4utingelpse* = elpse* ti"e in se4on*s eHe4uting*isR = nu"!er o pCsi4l re*s o !uers ro" *isR
querC = nu"!er o !uers gotten or 4onsistent re*4urrent = nu"!er o !uers gotten in 4urrent "o*e 7usullC or up*te;ros = nu"!er o ros pro4esse* !C te et4 or eHe4ute 4ll
-
7/23/2019 Use Explain
47/56
47
Sa$ple O%I %all Statistics
SELE)T t!le_n"e#$OM user_t!lesO$DE$ ?( t!le_n"e
4ll 4ount 4pu elpse* *isR querC 4urrent ros9999999 999999 99999999 9999999999 999999999 999999999 999999999 999999999
Prse : 55: 55@ 5 5 5 5EHe4ute : 555 555 5 5 5 5#et4 : 51. 5.. 5 0000 5 :.9999999 999999 99999999 9999999999 999999999 999999999 999999999 999999999totl : 55 :5: 5 0000 5 :.
Misses in li!rrC 44e *uring prse- :Opti"ier gol- )+OOSEPrsing user i*- $S)+$A/ [re4ursiBe *ept- 5]
!at t!e TKPROF Sa$ple
-
7/23/2019 Use Explain
48/56
48
!at t!e TKPROF Sa$pleon t!e Pre:ious Slide
Tells Us +he application called on Oracle to parse this statementonce while SQL trace was enabled.
+he parse too! .B *$) seconds, .C elapsed seconds.
%o dis! I2Os or buffer gets too! place during the parse,suggesting that no misses in the dictionar& cache.
Oracle was called on to execute this statement once.
+he execution too! under .B *$) seconds.
%o dis! I2Os or buffer gets too! place during the execution.5Queries often defer the wor! to the fetch phase.6
Oracle was called on B times to perform a fetch, and atotal of B7 rows were returned.
-
7/23/2019 Use Explain
49/56
49
!at t!e TKPROF Sa$ple
Tells Us 6continued7 9etching too! .E7 *$) seconds, .77 elapsed seconds.
9etching re(uired FF,GFF buffer gets in consistent mode,but no ph&sical reads were re(uired.
+he statement was not in the librar& cache 5shared pool6when the parse call came in.
+he cost;based optimi4er and a goal of
-
7/23/2019 Use Explain
50/56
50
Sa$ple Ro, Source
Operation Listin($os $o Sour4e Opertion9999999 999999999999999999999999999999999999999999999999999 :. SO$T O$DE$ ?( :. NESTED LOOPS :.1 NESTED LOOPS O2TE$ :.1 NESTED LOOPS O2TE$ :.1 NESTED LOOPS ::: TA?LE A))ESS ?( INDEX $OID O?J ::: INDEX $AN/E S)AN 7o!3e4t i* 0; ::00. TA?LE A))ESS )L2STE$ TA? :@1 INDEX 2NI%2E S)AN 7o!3e4t i* 0; 00 INDEX 2NI%2E S)AN 7o!3e4t i* 00;
:.0 TA?LE A))ESS )L2STE$ SE/ 0GF INDEX 2NI%2E S)AN 7o!3e4t i* .; :. TA?LE A))ESS )L2STE$ TS 0GG INDEX 2NI%2E S)AN 7o!3e4t i* F;
-
7/23/2019 Use Explain
51/56
51
Sa$ple E"ecution Plan
Listin($os EHe4ution Pln9999999 999999999999999999999999999999999999999999999999999 5 SELE)T STATEMENT /OAL- )+OOSE :. SO$T 7O$DE$ ?(; :. NESTED LOOPS :.1 NESTED LOOPS 7O2TE$; :.1 NESTED LOOPS 7O2TE$; :.1 NESTED LOOPS ::: TA?LE A))ESS 7?( INDEX $OID; O# 8O?J8 ::: INDEX 7$AN/E S)AN; O# 8I_O?J@8 72NI%2E; ::00. TA?LE A))ESS 7)L2STE$; O# 8TA?8 :@1 INDEX 72NI%2E S)AN; O# 8I_O?JQ8 7NON92NI%2E; 00 INDEX 72NI%2E S)AN; O# 8I_O?J:8 72NI%2E;
:.0 TA?LE A))ESS 7)L2STE$; O# 8SE/8 0GF INDEX 72NI%2E S)AN; O# 8I_#ILEQ_?LO)KQ8 7NON92NI%2E; :. TA?LE A))ESS 7)L2STE$; O# 8TS8 0GG INDEX 72NI%2E S)AN; O# 8I_TSQ8 7NON92NI%2E;
-
7/23/2019 Use Explain
52/56
52
Notes A#out TKPROF
E"ecution Plan Listin(s xecution plans are onl& included in +8$RO9 reports if
the explainH parameter is specified when +8$RO9 isin"o!ed
+8$RO9 will create and drop its own plan table if onedoes not alread& exist
+he row counts on each step are actualsnot estimates.+his can be "er& helpful when troubleshooting (ueries
that perform poorl&. hen +8$RO9 runs the #$LAI% $LA% statement for a
(uer&, a different execution plan could be returned thanwas actuall& used in the traced session.
-
7/23/2019 Use Explain
53/56
53
TKPROF Reports;
-
7/23/2019 Use Explain
54/56
54
rappin( Up
)se #$LAI% $LA%, (ueries against "0s(l-plan, theautotrace facilit& in SQLK$lus, or ?)I tools to "iewexecution plans.
)se +8$RO9 to format SQL trace files for human
readabilit&. xecution plans and +8$RO9 reports gi"e the DA
and application de"eloper a wealth of information thatcan be used to ma!e applications efficient andperform well.
+he catch3 &ou need to !now how to interpretexecution plans and +8$RO9 reports in order to getan& benefit from them. 'ou also ought to !now whento use #$LAI% $LA% "ersus when to (uer&"0s(l-plan.
-
7/23/2019 Use Explain
55/56
55
Resources
+own!oad thi" "!ide "how( the acco'anying
white aer( and 'any other u"e#u! re"entation"
at,
www*d%"ecia!i"t"*co'-re"entation"
-
7/23/2019 Use Explain
56/56
56
%ontact In)or$ationRoger Schrag
+ata%a"e 5ecia!i"t"( Inc*
;5an Franci"co( 6A @>
Te!, >B-;>>CB
E'ai!, r"chragDd%"ecia!i"t"*co'
We%, www*d%"ecia!i"t"*co'