Oracle Performance Tuning

Oracle Performance Tu Tuning ning Oracle Performance Tuning is very important part. It is not just the DBA responsibility ,it is also the responsibility of Oracle developers.Performance tuning should starts before Design and should be continuously tested. e need to have good !no"ledge of Oracle #oft"are "or$ing. %any times problems occurs as "e are not a"are of the "or$ing of the Oracle In this section,e are presenting the Oracle performance tuning articles "hich "ill help u in solving the problems &uic$ly Tuning Tools

Oracle Performance Tuning Tools' Oracle ()plain Plan,Auto*trace,t$prof+ e are presenting here her e All information about Oracle Performance Tuning Tools Tools li$e Oracle ()plain Plan,Auto*trace,t$prof. e "ill ta$e a deep dive into each of them Oracle Explain Plan *Oracle database internally creates a &uery e)ecution plan in order to fetch the desired data from the physical tables. The &uery e)ecution plan is nothing but a set of methods on ho" the database "ill access the data from the tables. This &uery e)ecution plan is crucial as different e)ecution plans "ill need different cost and time for the &uery e)ecution

*It sho"s the e)ecution path of a #- statement *indicates "hat tables it "ill read first and in "hat order  *indicates "hat inde)es it "ill use, or if ne" inde)es are needed *elps verify that Oracle "ill retrieve the data the "ay you e)pect it to How to find the Oracle Explain Plan? %ust have full access to a P-A/0TAB-( or create P-A/0TAB-( "ith utl)plan.s&l delivered by Oracle

Insert the follo"ing command at the top of your #- statement e)plain plan set statement0id 1 2)3 into plan0table for 

4 select lpad'2 2,56level+ 77 operation operations,options,object0name from plan0table "here statement0id 1 2)3 connect by prior id 1 parent0id and statement0id 1 2)3 start "ith id 1 8 and statement0id 1 2)3 order by id4 or  #-9 set linesi:e 8;5 #-9 #(-(O% TAB-('dbms0)plan.display+4 How to Read? *>ead innermost out, top?do"n

*@oin operations al"ays re&uire t"o sets. The order you read the sets is top do"n, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table. *One join is performed at a time, so you only need to consider t"o sets and their join operation at any one time. What to look for ?

*-oo$ for TAB-( A<<(## '=--+  *A/( #ead all ro"s from table 8 *Then access table 5 once for each ro" returned from table 8 *=astest ifE ro"s returned from table 8 are small access to table5 is ine)pensive. This means either a /I( loo$up or a #%A-- >ange #can. #erge $oin *Better than nested loops for joining a large number of ro"s

Hash $oin*o"s are produced from table 8 and are then sorted *>o"s are produced from table 5 and sorted by the same sort $ey as table 8 *Table *Ta ble 8 and 5 are /OT accessed concurrently *#orted ro"s from both sides are then merged together'joined+ %utotrace& %nother Oracle Performance Tunin Tuning g Tool' Tool' #imilar to ()plain Plan Provides plan F "ithout having to perform separate &uery from P-A/0TAB-( Provides statistics /oteE Does not support bind variables

#ynta) set autotrace G off 7 on 7 traceonly H e)plainJ statisticsJ  Autotrace #(T ATOT>A<( O== default. #(T ATOT>A<( ATOT>A<( O/ (CP-AI/ sho"s only the optimi:er e)ecution path #(T ATOT>A<( O/ #TATI#TI<# sho"s only e)ecution statistics #(T ATOT>A<( O/ includes both the plan and the statistics #(T ATOT>A<( T>A<(O/-K same as O/ but suppresses &uery output %utotrace*()ecution Plan produced by Autotrace contains the follo"ingE -ine number of each e)ecution step

The relationship number bet"een each step and its parent (ach step of the plan sho"s any database lin$s or parallel servers used *T!P>O= discussed ne)t "ill sho" ro" counts "hereas Autotrace does notL  AutotraceE ()ample Trace Tr ace (iles and T)PRO( Regular Trace
 Advantages Provides e)ecution path Provides ro" counts Produces smallest flat file Disadvantages egular Trace plus values supplied to the #- statement via the local variables 'i.e. binds+  Advantages Provides e)ecution path Provides ro" counts
Disadvantages  Again if run time is long and statistics statistics are lo" it "ill "ill be hard to tell "hy in this type of trace. Produces a bigger flat file that the regular trace because bind variable information has to be stored. Trace with Waits  'level N+ >egular Trace plus database operation timings that the #- "aited to have done in order to complete. =or e)ampleE dis$ access timings.  Advantages Provides e)ecution path Provides ro" counts
Disadvantages Trace file may easily ma) out due to all the information Oracle must "rite to the trace file and then only partial information is available in trace file. Bind variable information is not available Trace with *inds and Waits 'level 85+ >egular trace "ith the both the "ait and bind information.
 Advantages Provides e)ecution path Provides ro" counts O= is tool used to format trace files for reading #ynta) t$prof GtracefileH GoutputfileH e)plain1Guser?pass"dH J table1 J print1 J insert1 J sys1 J sort1 J T!P>O= sage Best Practices The follo"ing is the recommended usageE t$prof Gtrace fileH Goutput fileH e)plain1Gusernm?pass"dH print1Q sort1prsela,e)eela,fchela se e)plain1 to get Plan in the report se print1 to only report on the first Q statements se sort1 for sorting the statements "ith the longest elapsed times first ' "or$s "ith timed0statistics1true+ Some more command related to tracing 8. To trace any sid from outside sys.dbms0system.set0ev'sid, sys.dbms0sys tem.set0ev'sid, serialR, , , + ()amplesE #-9 e)ecute sys.dbms0system.set0ev'N, 58S, 8MU, 85, +4

5.athering stats for any object in APP# e)ec apps.fnd0stats.AT(>0TAB-(0#TAT#'2APP-#K#3,3=/D0>(/T0>((#T#3,8, M+4 ;. sing oradebug oradebug setospid oradebug close0trace oradebug setospid oradebug event 8MU trace name conte)t off4 oradebug event 8MU trace name conte)t forever, level M4 M.sing t$prof  This print 8 s&l only t$prof .trc elaps.prf sys1no e)plain1apps? sort1'prsela,e)eela,fchela+ print18 This print all the s&l t$prof .trc elaps.prf sys1no e)plain1apps?apps sort1prsela,e)eela,fchela

 Autotrace tility  Autotrace is beautiful tool provided by Oracle for getting the e)plain plan and e)ecution statistics. Kou need to $no" the &uery and its bind variable if any and "ith autotrace access, "e can get all the useful information about %utotrace -tilit. installation 8. cd O>A<-(0O%(J?rdbms?admin5. log into #-6Plus as #K#T(%;. >un Vutl)planM. >un <>(AT( PB-I< #K/O/K% P-A/0TAB-( =O> P-A/0TAB-(4W. >un >A/T A-- O/ P-A/0TAB-( TO PB-I<4 U. -og in to #-6Plus as #K# or as #K#DBAX. >un Vplustrce N. >un >A/T P-#T>A<( TO PB-I<4 %utotrace options #(T ATOT>A<( O==E /o ATOT>A<( report is generated. This is the default. #(T ATOT>A<( O/ (CP-AI/E The ATOT>A<( report sho"s only the optimi:er e)ecution path. #(T ATOT>A<( O/ #TATI#TI<#E The ATOT>A<( report sho"s only the #- statement e)ecution statistics. #(T ATOT>A<( O/E The ATOT>A<( report includes both the optimi:er e)ecution path and the #- statement e)ecution statistics. #(T ATOT>A<( T>A<(O/-KE This is li$e #(T ATOT>A<( O/, but it suppresses the printing of the user3s &uery output, if any. This is very useful for &ueries returning large ro"s,so "e dont need to scroll do"n that much #(T ATOT>A<( T>A<(O/-K #TATI#TI<#E This is li$e #(T ATOT>A<( T>A<(O/-K but it sho"s the statistics only and supress the e)plain plan output #(T ATOT>A<( T>A<(O/-K (CP-AI/E This is li$e #(T ATOT>A<( T>A<(O/-K but it sho"s the e)plain plan only and supress the statistics . This does not e)ecute the select statement ,just parse the statement and sho"s the e)plain. I/#(>T?PDAT( are e)ecuted and then e)plain plan sho"n -nderstanding %utotrace Output

 Autotrace sho"s t"o things a+ ()plain planE ()plain plan sho"s the plan for &uery and it sho"s e)actly ho" the &uery "ill be e)ecuted in Database.It "ill sho"s the cost ,cardinality and bytes for the each step in the e)plain plan b+ #tatisticsE -ots of statistics "ill be sho"n.#ome of the statistics are i+ >ecursive callsE /umber of s&l statement e)ecuted in order to e)ecute the statement ii+ DB bloc$ getsE The no of bloc$s read from buffer cache in current mode iii+
Oracle Performance Es&l0trace parameter and its impact Oracle Performance case study on s&l0trace being set improperly %y apps system "as generating lot of trace files and mount "as getting , resulting in I?O issues. "e cleaned up the trace files and but again it came bac$pon investigation "e found that some body has by mista$e set the init.ora parameter s&l0trace to true . Because of this every connection to the database "as generating trace file. Once "e set it =A-#(, trace file creation becomes lessor and it resolve the issue The s&l0trace setting to true also resulted in severe performance issue for the instance. #o appropiate care should be ta$en before setting this parameter. I "ill al"ays recommend to set this at session level alter session set s&l0trace1true4 If it is still needed to be set at system level then follo"ing should be ta$en care to minimi:e the performance impact • • •

%aintaining at least 5WY idle 0D%P0D(#T location #triping dis$ space over sufficient dis$s

 About sql_trace parameter  SQL_TRACE enables or disables the #- trace facility. #etting this parameter to true provides

information on tuning that you can use to improve performance. Kou can change the value using the DBMS_SYSTEM pac$age

o" to turn on the #- trace, 8MU event in Oracle database and trcsess, t$prof utility Tracing is a important part for Oracle database tuning. This "ill help capture all the problem statement , "ait event "hich are responsible for slo" running of the session. Oracle has provided has many &uic$est "ays to trace the local session, other user session and format the trace to ma$e readable.

-et us ta$e some loo$ at ho" to turn on #- trace, 8MU event in Oracle database and trcsess, t$prof utlity

#- Trace ,8MU event If you "ant to trace in local session,here are the steps to turn on #- trace

!ormal trace

alter session set s&l0trace 1 true4 Z To put trace on alter session set s&l0trace 1 false4 Z To put trace off  (ull le/el with wait e/ent %nd bind trace

alter session set events 1 28MU trace name conte)t forever, level 8534 To put tracing off  alter session set events 1 28MU trace name conte)t off34 Same as !ormal trace

e)ec DB%#0#(##IO/.set0s&l0trace's&l0trace 19 T>(+4 e)ec DB%#0#(##IO/.set0s&l0trace's&l0trace 19 =A-#(+4 If you "ant to trace in other running session,here are the steps !ormal trace

e)ecute dbms0system.set0s&l0trace0in0session '2sid3,3serial3,true+4 Z To put tracing on e)ecute dbms0system.set0s&l0trace0in0session '2sid3,3serial3,true+4 Z To put tracing off  (ull le/el with wait e/ent %nd bind trace

e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,85,33+4 To put trace off 

e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,,33+4 Kou must have seen using 85, in various statement above.They are tracing levels.Oracle Tracing has tracing levels .ere are the valid values



/o trace. -i$e s"itching s&l0trace off. The e&uivalent of regular s&l0trace

5 It Provides e)ecution path,ro" counts,smallest flat file M

The same as 5, but "ith the addition of bind variable values The same as 5, but "ith the addition of "ait events

N >egular Trace plus database operation timings that the #- "aited to have done in order to complete. =or e)ampleE dis$ access timings. The same as 5, but "ith both bind variable values and "ait events

85

>egular trace "ith the both the "ait and bind information.
There are other "ays to do the tracing also. ere are some of these 8+ O>AD(B This re&uires login as sysdba

oradebug setospid 8888 Z Debug session "ith the specified Oracle process id

oradebug setorapid 8888 Z Debug session "ith the specified O# process oradebug event 8MU trace name conte)t forever, level M4 oradebug event 8MU trace name conte)t off4 Z This disable the trace oradebug close0trace Z This closes the trace file Oradebug T>A<(=I-(0/A%(4 5+ ith Oracle 8g the #- tracing options have been e)tended using the DB%#0%O/ITO> pac$age (C(<T( dbms0monitor.session0trace0enable

hich is similar   A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level 534 (C(<T( dbms0monitor.session0trace0enable 'binds19true+4 hich is similar   A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level M34 (C(<T( dbms0monitor.session0trace0enable '"aits19true+4 hich is similar   A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level N34 (C(<T( dbms0monitor.session0trace0enable'2sid3,3serialR3+ hich is similar  e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,5,33+4 (C(<T( dbms0monitor.session0trace0enable '2sid3,3serialR3,binds19true+4 hich is similar  e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,M,33+4

(C(<T( dbms0monitor.session0trace0enable '2sid3,3serialR3,"aits19true+4 hich is similar  e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,N,33+4 There are many other functionality available in dbms0monitor. e can enable trace based on client identifier and multiple "ays How to identif. the Trace files

e can identify the trace files using the spid of the session. Also the trace file "ill contain sid,serialR pair at the start of the trace file. Belo" &uery can be used to find the local session trace file select c.value 77 2?3 77 d.instance0name 7730ora03 77 a.spid 77 2.trc3 trace from v\process a, v\session b, v\parameter c, v\instance d "here a.addr 1 b.paddr  and b.audsid 1 userenv'2sessionid3+ and c.name 1 2user0dump0dest3 ? There is another easy "ay to identify the trace file "hich is called trace identifier  alter session set tracefile0identifer13O>A<34 This is identifier  Example trace file

Trace file ?app?oracle?T(#T?diag?rdbms?test?T(#T?trace?T(#T0ora08;5W0ID(/.trc Oracle Database 88g (nterprise (dition >elease 88.5..M. F UMbit Production ith the Partitioning, >eal Application eal Application Testing options O>A<-(0O%( 1 ?app?oracle?T(#T?88.5.?db #ystem nameE

#unO#85

/ode nameE sun85>eleaseE W.8[ersionE eneric08MMMNN*88 %achineE sunMu Instance nameE T(#T >edo thread mounted by this instanceE 8 Oracle process numberE 55

ni) process pidE 8;5W, imageE oracleV #unO#85 666 58;*8*58 85E5NEWU.MU8 666 #(##IO/ IDE';NXW.8X;M8+ 58;*8*58 85E5NEWU.MU8666 <-I(/T IDE'+ 58;*8*58 85E5NEWU.MU8 666 #(>[I<( /A%(E'T(#T+ 58;*8*58 85E5NEWU.MU8 666 %OD-( /A%(E'#TA/DA>DVsunO#85'T/# [8*[;++ 58;*8*58 85E5NEWU.MU8 666 A#I/ I/ <>#O> RW len1;WU dep18 uid18X; oct1; lid18X; tim1NNUUNS88;UN58 hv15MUNXN;8N5 ad13McXeM;SN] s&lid13"m"sjyS$dS5f3#(-(O=I-(0OPTIO/0ID,  APP-I(#P0(/AB-(D0=-A , #(>0(/AB-(D0=-A, O>0(/AB-(D0=-A , #(>[(>0(/AB-(D0=-A, #(>[(>>(#P0(/AB-(D0=-A, I(>A><K0TKP(, #(>0<A/(AB-(0=-A =>O% =/D0P>O=I-(0OPTIO/# (>( P>O=I-(0OPTIO/0/A%( 1 EB8 A/D #TA>T0DAT(0A0B#I/(##0>OP0ID (C(< RWEc1,e18MXM,p1,cr1,cu1,mis1,r1,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88;N55M =(T< RWEc1,e1U8,p1,cr1;,cu1,mis1,r18,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88;NM5S

<-O#( RWEc1,e1W,dep18,type1;,tim1NNUUNS88;NW; 111111111111111111111PA>#I/ I/ <>#O> RM len15; dep18 uid18X; oct1; lid18X; tim1NNUUNS88;NU;M hv1;WXWWS5MW8 ad13;aeea;da] s&lid13WWdcXUXajydh;]  #(-(O=I-(0OPTIO/0[A-( =>O% =/D0P>O=I-(0OPTIO/0[A-(# (>( P>O=I-(0OPTIO/0ID 1 EBM A/D APP-IO=I-(0OPTIO/0[A-( I# /OT /-(/D O= #T%T BI/D# RME BindR oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1SU off1 $)sbbbfp1ffffffffXdUXXbUN bln155 avl1; flg1W value185M BindR8 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off15M $)sbbbfp1ffffffffXdUXXbN bln155 avl15 flg18 value1N BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUXXbSN bln155 avl1M flg18 value1W;;M BindR;

oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUXXbb bln155 avl18 flg18 value1 (C(< RMEc1,e1;XX,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;NSXN =(T< RMEc1,e15U,p1,cr1;,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SW <-O#( RMEc1,e15,dep18,type1;,tim1NNUUNS88;S88U 111111111111111111111 PA>#I/ I/ <>#O> RW len18S8 dep18 uid18X; oct1; lid18X; tim1NNUUNS88;S;N hv1;;;;N;W ad13;bedfeMN] s&lid13X&sXf)NS8SMu8]  #(-(O=I-(0OPTIO/0[A-( =>O% =/D0P>O=I-(0OPTIO/0[A-(# (>( P>O=I-(0OPTIO/0ID 1 EBM A/D APP-IO=I-(0OPTIO/0[A-( I# /OT /-(/D O= #T%T BI/D# RWE BindR oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1SU off1 $)sbbbfp1ffffffffXdUX;bXN bln155 avl1; flg1W value185M BindR8 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off15M $)sbbbfp1ffffffffXdUX;bS bln155 avl15 flg18 value1N

BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value188 BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RWEc1,e1;XX,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SU5M =(T< RWEc1,e15U,p1,cr1M,cu1,mis1,r18,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SUS5 <-O#( RWEc1,e1X,dep18,type1;,tim1NNUUNS88;SX;S111111111111111111111PA>#I/ I/ <>#O> RM len1;WU dep18 uid18X; oct1; lid18X; tim1NNUUNS88;SSW5 hv15MUNXN;8N5 ad13McXeM;SN] s&lid13"m"sjyS$dS5f3 #(-(O=I-(0OPTIO/0ID, APP-I(#P0(/AB-(D0=-A , #(>0(/AB-(D0=-A, O>0(/AB-(D0=-A , #(>[(>0(/AB-(D0=-A, #(>[(>>(#P0(/AB-(D0=-A, I(>A><K0TKP(, #(>0<A/(AB-(0=-A =>O% =/D0P>O=I-(0OPTIO/# (>( P>O=I-(0OPTIO/0/A%( 1 EB8 A/D #TA>T0DAT(0A
oacflg1; fl5185U8 frm18 csi1NX8 si:185N off1 $)sbbbfp1ffffffffXdUX;bWN bln185N avl15; flg1W value1P(>0#(<>ITK0P>O=I-(0ID (C(< RMEc1,e18UM,p1,cr1,cu1,mis1,r1,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88MSX =(T< RMEc1,e15N,p1,cr1;,cu1,mis1,r18,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88M8X8 <-O#( RMEc1,e15,dep18,type1;,tim1NNUUNS88M58N 111111111111111111111 PA>#I/ I/ <>#O> RW len15; dep18 uid18X; oct1; lid18X; tim1NNUUNS88M5S hv1;WXWWS5MW8 ad13;aeea;da] s&lid13WWdcXUXajydh;]  #(-(O=I-(0OPTIO/0[A-( =>O% =/D0P>O=I-(0OPTIO/0[A-(# (>( P>O=I-(0OPTIO/0ID 1 EBM A/D APP-IO=I-(0OPTIO/0[A-( I# /OT /-(/D O= #T%T BI/D# RWE BindR oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1SU off1 $)sbbbfp1ffffffffXdUX;bXN bln155 avl1; flg1W value158SS BindR8 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off15M $)sbbbfp1ffffffffXdUX;bS bln155 avl15 flg18 value1N

BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value1W;;M BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RWEc1,e1;5W,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88MWSS =(T< RWEc1,e18S,p1,cr1;,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88MUWS<-O#( RWEc1,e18,dep18,type1;,tim1NNUUNS88MX8 111111111111111111111 PA>#I/ I/ <>#O> RM len18S8 dep18 uid18X; oct1; lid18X; tim1NNUUNS88MNM; hv1;;;;N;W ad13;bedfeMN] s&lid13X&sXf)NS8SMu83#(-(O=I-(0OPTIO/0[A-( =>O% =/D0P>O=I-(0OPTIO/0[A-(# (>( P>O=I-(0OPTIO/0ID 1 EBM A/D  APP-IO=I-(0OPTIO/0[A-( I# /OT /-(/D O= #T%T BI/D# RME BindR oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1SU off1

$)sbbbfp1ffffffffXdUX;bXN bln155 avl1; flg1W value158SS BindR8 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off15M $)sbbbfp1ffffffffXdUX;bS bln155 avl15 flg18 value1N BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value188 BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RMEc1,e1;5M,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88M88W5=(T< RMEc1,e18X,p1,cr1M,cu1,mis1,r18,dep18,og18,plh15N5SXWU8,tim1NNUUNS88M85N <-O#( RMEc1,e1X,dep18,type1;,tim1NNUUNS88M85WU

t$prof utility The trace files obtained from above method is in ra" form "hich can be converted into more readable format using t$prof utility 'Transient !ernel P>O=ile utility+

t$prof  sageE t$prof tracefile outputfile e)plain1 J table1 J print1 J insert1 J sys1 J sort1 J table1schema.tablename se 2schema.tablename3 "ith 2e)plain13 option. e)plain1user?pass"ord print1integer

A<-( and issue (CP-AI/ P-A/.

-ist only the first 2integer3 #- statements.

aggregate1yes7no insert1filename -ist #- statements and data inside I/#(>T statements. sys1no

T!P>O= does not list #- statements run as user #K#.

record1filename >ecord non*recursive statements found in the trace file. "aits1yes7no sort1option

>ecord summary for any "ait events found in the trace file. #et of :ero or more of the follo"ing sort optionsE

prscnt number of times parse "as called prscpu cpu time parsing prsela elapsed time parsing

prsds$ number of dis$ reads during parseprs&ry number of

buffers for consistent read during parseprscu number of buffers for current read during parseprsmis number of misses in library cache during parse

e)ecnt number of e)ecute "as called e)ecpu cpu time spent e)ecuting e)eela elapsed time e)ecuting e)eds$ number of dis$ reads during e)ecute e)e&ry number of buffers for consistent read during e)ecute e)ecu number of buffers for current read during e)ecute e)ero" number of ro"s processed during e)ecute

e)emis number of library cache misses during e)ecute fchcnt number of times fetch "as called fchcpu cpu time spent fetching fchela elapsed time fetching fchds$ number of dis$ reads during fetch fch&ry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchro" number of ro"s fetched userid userid of user that parsed the cursor  Some examples t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela t$prof file.trc file.t)t sys1no t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela Example content of tkprof file

T!P>O=E >elease 88.5..M. F Production on Tue @an 8X 8ME85EM8 58;
1 number of times O
cpu

1 cpu time in seconds e)ecuting

elapsed 1 elapsed time in seconds e)ecuting dis$ &uery

1 number of physical reads of buffers from dis$ 1 number of buffers gotten for consistent read

current 1 number of buffers gotten in current mode 'usually for update+

ro"s

1 number of ro"s processed by the fetch or e)ecute call

66666666666666666666666666666666666666666666666666666666666666666666666666666666 #- IDE U"N58sggrtys) Plan ashE 5;5WXXUXXW#(-(
=>O% =/D0#(>0D(#!TOP0OB@(
(>( #(>0ID 1 Eb8 A/D APP-I(#PO/#IBI-ITK0ID 1 Eb; A/D TKP( 1 2=/O/% ^1 8 O>D(> BK #((/<( call

count

cpu

elapsed

dis$

&uery

current

ro"s

ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* Parse

8

()ecute

.

8

=etch

5

. .

.



.





.











M





8

ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* total

M

.

.



M



8

%isses in library cache during parseE  Optimi:er modeE A--0>O# Parsing user idE 8X; 'APP#+ /umber of plan statistics capturedE 8 >o"s '8st+ >o"s 'avg+ >o"s 'ma)+ >o" #ource Operation ZZZ* ZZZ* ZZZ* ZZZZZZZZZZZZZZZZZ 8

8

8 #O>T O>D(> BK 'cr1M pr1 p"1 time1 us cost1U si:e1M8 card18+

8

8

8
8

8

8

TAB-( A<<(## BK I/D(C >OID =/D0#(>0D(#!TOP0OB@(
p"1 time1 us cost1W si:e1M8 card18+ 8

8

8

I/D(C >A/( #0D(#!TOP0OB@(
time1 us cost1; si:e1 card1;+'object id ;;WSU+

>o"s

()ecution Plan

ZZ* ZZZZZZZZZZZZZZZZZ  #(-(O# 8 #O>T 'O>D(> BK+ 8


8

TAB-( A<<(## %OD(E A/A-K_(D 'BK I/D(C >OID+ O=

2=/D0#(>0D(#!TOP0OB@(
I/D(C %OD(E A/A-K_(D '>A/( #
2=/D0#(>0D(#!TOP0OB@(
Times %a). ait Total aited

ZZZZZZZZZZZZZ* aited ZZZ* ZZZZ #-6/et message to client

W

#-6/et message from client

. W

.

.

.66666666666666666666666666666666666666666666666666666666666666666666666666666666 #- IDE 5XUut5yXy"&u) Plan ashE ;NWU885W5N select object0name, icon0name from fnd0des$top0objects call

count

cpu

elapsed

dis$

&uery

current

ro"s

ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* Parse

8

()ecute =etch

8 ;

. . .

.



. .



 



 U

 

  MX

ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* total

W

.

.



U



MX

%isses in library cache during parseE  Optimi:er modeE A--0>O# Parsing user idE 8X; 'APP#+ /umber of plan statistics capturedE 8 >o"s '8st+ >o"s 'avg+ >o"s 'ma)+ >o" #ource Operation ZZZ* ZZZ* ZZZ* ZZZZZZZZZZZZZZZZZ MX

MX

MX TAB-( A<<(## =-- =/D0D(#!TOP0OB@(
cost15 si:e188XW card1MX+ >o"s

()ecution Plan

ZZ* ZZZZZZZZZZZZZZZZZ  #(-(O# MX TAB-( A<<(## %OD(E A/A-K_(D '=--+ O= 2=/D0D(#!TOP0OB@(
Times %a). ait

Total aitedZZZZZZZZZZZZZ* aited ZZZ* ZZZZ

#-6/et message to client #-6/et message from client

M

. M

.

. .

66666666666666666666666666666666666666666666666666666666666666666666666666666666

trcsess utlity hen using shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This ma$es it difficult to get a complete picture of the life cycle of a session. The trcsess utility consolidates trace output from selected trace files based on several criteria trcsess output1output0file0nameJ

session1session0idJ clientid1client0idJ service1service0nameJ action1action0nameJ module1module0nameJ trace0filesJ trcsess output1main.trc service1T(#T 6trc

 After the consolidate trace file had been generated you can e)ecute t$prof on it.

o" to run s&l tuning e)pert for particular s&l *d in the
D(<-A>( my0tas$0name [A><A>5';+4 B(I/ my0tas$0name E1 DB%#0#-T/(.<>(AT(0T/I/0TA#!' s&l0id 19 , scope 19 2((/#I[(3, time0limit 19 ;U, tas$0name 19 , description 19 +4 end4 ? Execute Tuning Task3

B(I/ DB%#0#-T/(.(C(<T(0T/I/0TA#!' tas$0name 19 2e)ample *83+4 end4 ? 4et the summar. of recommendation #(-((PO>T0T/I/0TA#!' 2e)ample *83+ from DA-4

How to accept the profile recommended b. the s0l tuning expert D(<-A>( my0s&lprofile0name [A><A>5';+4 B(I/ my0s&lprofile0name E1 DB%#0#-T/(.A<<(PT0#-0P>O=I-( ' tas$0name 193e)ample *8], name 19 2my0s&l0profile3+4 (/D4 ?

5 Performance Terms explained

hat is -ogical I?O and Physical I?O in Oracle Database %any times "e get confused "ith -ogical I?O and Physical I?O in Oracle Database. I am trying to clear all the doubt about "hat is -ogical I?O and Physical I?O in Oracle Database What is "ogical 67O It is the data bloc$ accessed from Buffer
• • • • • •

What is Ph.sical 67O • • •

• • • •

it is data bloc$ re&uested from Physical dis$s or Operation system It could be from Operation system cache Data bloc$s must be placed into memory in order to be accessed to satisfy the ac&uisition of ro"s for a &uery3s result set Its statistics in captured in physical reads statistics This stats is clearly visible as physical reads in Autotrace e)ecuted e can find this for statement from pr in ra" trace dat This stats is clearly visible as dis$s in t$prof output

89S+SST%T

se the follo"ing &uery 'or similar+ to get the information from this dictionary vie"E select name, value from v\sysstat "here name in '2consistent gets3,3db bloc$ gets3,3physical reads3+4 Which is better logical 67O and ph.sical 67O in terms of performance  A physical I?O is not good for performance of &ueries . henever a physical I?O ta$es place, Oracle tries to read the data bloc$ from the dis$ "hich "ill be slo". The goal hence is to avoid physical I?O as far as possible.  A -ogical I?O is considered better for performance '"hen compared to Physical I?O+ because the reads happen from the memory as the data bloc$ is pre*fetched from the dis$. #o no" Oracle does not need to go to dis$ to fetch bloc$s for your &uery results. But it is important to note that e)cess of -ogical reads 'I?O+ is also not good or recommended. There are many reasons for that 8+ a logical read might have resulted in a physical read to fetch the data bloc$ into the buffer cache. 5+ every time a bloc$ is read from the cache, a loc$ or latch is ac&uired on the cache and hence higher logical reads could mean that there are high chances of buffer cache contention. #o our goal should be to access least number of logical I?O for &ueries inorder to improve its performance

o" to find session generating lots of redo To find sessions generating lots of redo, you can use either of the follo"ing methods. Both methods e)amine the amount of undo generated. hen a transaction generates undo, it "ill automatically generate redo as "ell.how to find session generating lots of redo 8+ uery [\#(##0IO. This vie" contains the column B-OO% v\session s, v\sess0io i M (>( s.sid 1 i.sid W O>D(> BK W desc, 8, 5, ;, M4 >un the &uery multiple times and e)amine the delta bet"een each occurrence of B-OA< 5+ uery [\T>A/#A(< columns+.

The &uery you can use isE #-9 #(-(O% v\session s, v\transaction t M (>( s.taddr 1 t.addr  W O>D(> BK W desc, U desc, 8, 5, ;, M4 >un the &uery multiple times and e)amine the delta bet"een each occurrence of #(D0B-! and #(D0>(<. -arge deltas indicate high redo generation by the session. Kou use the first &uery "hen you need to chec$ for programs generating lots of  redo "hen these programs activate more than one transaction. The latter &uery can be used to find out "hich particular transactions are generating redo.

o" to calculate IOP# of an oracle database What is 6OPS IOP# 'Input?Output Operations Per #econd, pronounced eye*ops+ is a common performance measurement used to benchmar$ computer storage devices li$e hard dis$ drives 'DD+, solid state drives '##D+, and storage area net"or$s '#A/+ How to calculate 6OPS of an oracle database In Oracle database "e measure ho" much of such re&uests are generated by the database system. e have to chec$ "hether our hard"are are capable of processing the re&uest generated by the database system in order to maintain the performance.IOP# is calculated in Oracle database as the sum of Physical >ead Total IO >e&uests Per #ec and Physical rite Total IO >e&uests Per #ecThese values "ill be available in the table, dba0hist0sysmetric0summary

ere is a &uery on How to calculate 6OPS of an oracle database brea$ on report compute sum of [alue on report select %(T>I<0/A%(,avg'A[(>A(+ as [alue from dba0hist0sysmetric0summary "here %(T>I<0/A%( in '2Physical >ead Total IO >e&uests Per #ec3,3Physical rite Total IO >e&uests Per #ec3+ Some important terms related to 6OPS in metrics Physical read IO requests  F /umber of read re&uests for application activity 'mainly buffer

cache and direct load operation+ "hich read one or more database bloc$s per re&uest. This is a subset of physical read total IO re&uests statistic. Physical read total IO requests  F /umber of read re&uests "hich read one or more database

bloc$s for all instance activity including application, bac$up and recovery, and other utilities.

Physical write IO requests  F /umber of "rite re&uests for application activity 'mainly buffer

cache and direct load operation+ "hich "rote one or more database bloc$s per re&uest. Physical write total IO requests  F /umber of "rite re&uests "hich "rote one or more database

bloc$s from all instance activity including application activity, bac$up and recovery, and other utilities.  physical read total multi block requests – /umber of large read re&uests "hich read multi

database bloc$s for all instance activity   physical write total multi block requests  */umber of large "rite re&uests "hich "rite multi

database bloc$s for all instance activity   physical read total bytes *Total bytes read "hich read one or more database bloc$s for all

instance activity including application, bac$up and recovery, and other utilities.  physical write total bytes  FTotal bytes "rite "hich read one or more database bloc$s for all instance activity including application, bac$up and recovery, and other utilities. To calculate small readsE #mall >eads 1 Total >eads F -arge >eads #mall rites 1 Total rites F -arge rites These metrics can be used ta$en at t"o point of time can also be used to calculate IOP# for small read,"rire,large "rite,large read , total bytes per sec etc

hat is DB time and Average Active sessions, Active session in oracle In this section "e "ill be defining the terms line Database time, Action session and Average active session Database Time, or DB Time, is defined by Oracle as the total time by foreground sessions e)ecuting database calls. This includes
Performance -esson Ehat is a Bind [ariable hat is a Bind [ariableQ *It is a placeholder for a value *In P-?#- it is called a local or global variable. D(<-A>( l0empl0id D(PT.emp0idYTKP(4 B(I/ select first0name from D(PT "here id 1 l0empl0id4 (/D4 *in #-6Plus it is a variable variable a number  B(I/ Ea 1 84 dbms0output.put0line'a+4 (/D4* #&l "ithout binds #(-(O% users (>( user0id1NXWNXW4 #(-(O% users (>( user0id1NUSNU4  F #&l "ith binds #(-(O% users (>( user0id11Ea0user0id  F hen s&l statement "ith binds are used, It can be shared "ith mutiple user session and #oft parse is just good to e)ecute the statement. ard parsing each statement is &uite a pain and severely impact the performance of the oracle database.

o" to create ADD% tas$ and chec$ its report In this post, I "ill e)plain o" to create ADD% tas$ and chec$ its report e are using start snapshot as S  And end snapshots as SW B(I/ Z .create0tas$ ' advisor0name 19 2ADD%3, tas$0name 19 2S0SW0A>0#/AP#OT3, tas$0desc 19 2Advisor for snapshots S to SW.3+4

Z #et the start and end snapshots. DB%#0AD[I#O>.set0tas$0parameter ' tas$0name 19 2S0SW0A>0#/AP#OT3, parameter 19 2#TA>T0#/AP#OT3, value 19 S+4 DB%#0AD[I#O>.set0tas$0parameter ' tas$0name 19 2S0SW0A>0#/AP#OT3, parameter 19 2(/D0#/AP#OT3, value 19 SW+4 Z ()ecute the tas$. DB%#0AD[I#O>.e)ecute0tas$'tas$0name 19 2S0SW0A>0#/AP#OT3,+4 (/D4 ? Z Display the report. #(T -O/ 8 #(T PA(#I_( W #(-(.get0tas$0report'2S0SW0A>0#/AP#OT3+ A# report =>O% dual4 #(T PA(#I_( 5M >elated [ie"s The follo"ing vie"s can be used to display the ADD% output "ithout using (nterprise %anager or the (T0TA#!0>(PO>T functionE DBA0AD[I#O>0TA#!# F Basic information about e)isting tas$s. DBA0AD[I#O>0-O F #tatus information about e)isting tas$s. DBA0AD[I#O>0=I/DI/# F =indings identified for an e)isting tas$. DBA0AD[I#O>0>(ecommendations for the problems identified by an e)isting tas$.

Oracle A#'Active #ession istory+ seful &ueries %cti/e Session Histor.  'A#+ "as introduced in Oracle 8g. It samples the activity of each active database session every second. The data is held in a buffer in memory in the database.The design goal is to $eep about an hour 'your mileage "ill vary+. If a session is not active it "ill not be sampled. The in*memory buffer is e)posed via a vie" called v\active0session0history. hen an A> snapshot is ta$en, 8 ro" in 8 from the A# buffer is copied do"n into the A> repository. It can also be flushed to dis$ bet"een snapshots "hen the buffer reaches UUY full, so there is no missed data.The data is stored in >\0AK and it is e)posed via dba0hist0active0sess0history.it is enabled by default, but before you rush off to use it, be a"are that it is a licenced feature. Kou can disable ash by setting the underscore parameter 

alter system set “_ash_enable”=False; I "ant to emphasi:e that if the session is not active it "ill not be sampled. Kou can actually set a parameter 0ash0enable0all 1 T>( to force all sessions, including idle sessions, to be

sampledSome important points in this regard 8+Oracle has provided a pac$age called DB%#0APP-I
SELECT sql_id,COUNT(!,"OUN#(COUNT(!$SU%(COUNT(!! O&E"(!, '! CTLO)# F"O% *+a-ti+e_sessi.n_hist.ry /0E"E sam1le_time 2 S3S#)TE 4 5$'6 )N# sessi.n_ty1e = 78)C9:"OUN# :"OU 83 sql_id O"#E" 83 COUNT(! #ESC; SELECT sql_id,COUNT(!,"OUN#(COUNT(!$SU%(COUNT(!! O&E"(!, '! CTLO)# F"O% *+a-ti+e_sessi.n_hist.ry /0E"E sam1le_time 2 S3S#)TE 4 5$'6 )N# sessi.n_ty1e = 7FO"E:"OUN# :"OU 83 sql_id O"#E" 83 COUNT(! #ESC; ;+ To find out the wait e/ents for which this session

SELECT sam1le_time, e+ent, = ' M+ #ost acti/e session in last one hour  #(-(O/D''+, 5+ P
=>O% gv\active0session0history (>( sample0time 9 #K#DAT( F 8?5M  A/D session0type 1 2BAO/D3 >OP BK s&l0id O>D(> BK O/D''+, 5+ PO% gv\active0session0history (>( sample0time 9 #K#DAT( F 8?5M  A/D session0type 1 2=O>(>O/D3 >OP BK s&l0id O>D(> BK
W+ #ost 67O intensi/e s0l in last : hour  #(-(O% gv\active0session0history ash, gv\event0name evt (>( ash.sample0time 9 #K#DAT( F 8?5M  A/D ash.session0state 1 2AITI/3  A/D ash.event0id 1 evt.event0id  A/D evt."ait0class 1 2ser I?O3 >OP BK s&l0id O>D(> BK
U+"ocking information in last : min

col event for a55

col bloc$0type for a8N col objn for a8N col otype for a8 col fn for SS col sid for SSSS col bsid for SSSS col lm for SS col p; for SSSSS col bloc$n for SSSSS select to0char'sample0time,3E%I3+ st, substr'event,,5+ event, a.session0id sid, mod'a.p8,8U+ lm, a.p5, a.p;, nvl'o.object0name,ash.current0objR+ objn, substr'o.object0type,,8+ otype, <>>(/T0=I-(R fn, <>>(/T0B-O
all0objects o "here event li$e 2en&E TCY3 and o.object0id '`+1 a.<>>(/T0OB@R and sample0time 9 sysdate F M?'U65M+ Order by sample0time ?

X+ top s0ls spent more on cpu7wait7io select

ash.#-0ID , sum'decode'a.session0state,3O/
How To 4enerate %SH Report

Oracle Provide a standard script just li$e A> to analy:e the A#% data I "ill e)plain ho" to generate A#'Active #ession istory+ reports from #-P-#. The same can be done using (nterprise %anager also.

#cript -ocation is same as other standard script \O>A<-(0O%(?rdbms?admin? #-9 V\O>A<-(0O%(?rdbms?admin?ashrpt.s&l

eport Type  (nter 2html3 for an T%- report, or 2te)t3 for plain te)t Defaults to 2html3 (nter value for report0typeE html .. .. Defaults to *8W mins (nter value for begin0timeE 8?88?8W EE >eport begin time specifiedE 8?88?8W EE (nter value for durationE 8 >eport duration specifiedE 8 sing 88*@an*8W EE as report begin time sing 88*@an*8W E8E as report end time (nter value for report0nameE ()ample #ummary of All ser Input ZZZZZZZZ* =ormat E T%DB Id E NMNXMNMNM Inst num E 8 Begin time E 88*@an*8W EE (nd time E 88*@an*8W E8E #lot "idth E Default >eport targets E  >eport name E ()ample (nd of >eport ^?body9^?html9 >eport "ritten to ()ample

 All about A> ' Automatic or$load >epository+ What is %WR?

 Automatic or$load >epository 'A>+ is a collection of persistent system performance statistics o"ned by the #K# user. It resides in #K#AC tablespace. By default snapshot are generated once every Umin and maintained for X days.A> report are used to investigate performance and other issues. Script %/ailabe in 9OR%2"E;HO#E7rdbms7admin awrrpt.sql EDepending on the reasons for collecting the report, the default can be used, or for a more focused vie", a short 8*8W minute snapshot could be used.awrrpti.sql EDisplays statistics for a range of snapshot Ids on a specified database and instance. awrsqrpt.sql EDisplays statistics of a particular #- statement for a range of snapshot Ids. >un this report to inspect or debug the performance of a particular #- statement. awrsqrpi.sql  EDisplays statistics of a particular #- statement for a range of snapshot Ids on a specified #-. awrddrpt.sql E
-seful Operations on %WR

a+ o" to %odify the a"r snapshot settings. B(I/ DB%#0O>!-OAD0>(PO#ITO>K.modify0snapshot0settings' retention 19 58U, Z %inutes '58U 1 8W Days+. Z
b+ Dropping the A> snaps in rangeE B(I/ DB%#0O>!-OAD0>(PO#ITO>K.drop0snapshot0range' 'lo"0snap0id19M, igh0snap0id19N+4 (/D4 ?

c+ !-OAD0>(PO#ITO>K.create0snapshot'+4 (/D4 ? Workload Repositor. 8iews [\AK F Displays the active session history 'A#+ sampled every second. DBA0I#T0AK F Displays the history contents of the active session history. DBA0I#T0BA#(-I/( F Displays baseline information. DBA0I#T0DATABA#(0I/#TA/<( F Displays database environment information. DBA0I#T0#/AP#OT F Displays snapshot information. DBA0I#T0#-0P-A/ F Displays #- e)ecution plans. DBA0I#T0>0O- F Displays A> settings.

Oracle DBAE anganaly:e and system state dump %any times Oracle database get hangs due to loc$s or latching issue and /obody can login to the system. It becomes critical and And sually "e do shutdo"n abort to immediately resolve the issue. But it is good to ta$e system state dump?hanganaly:e at 8 min interval to analy:e the loc$ing or latching issue after"ards before doing shutdo"n abort so that "e can find the fi) for the problem and avoid that in future.Oracle #upport also usually as$ for these "hen the database hangs for some reason Here are the steps to take s.stem state dump

In >A< system this need to be e)ecuted on all the instance #ynta) for system state dumpE -ogon to s&lplus as sysdba #-9 oradebug setmypid #-9 oradebug unlimit #-9 oradebug dump systemstate 8 .. ait at least 8 min #-9 oradebug dump systemstate 8 .. ait at lease 8 min #-9 oradebug dump systemstate 8 #imilarly anganaly:e is a good utility to analysis. Here are the steps

#ynta) for hangaanaly:e for single instance -ogon to s&lplus as sysdba oradebug setmypid4 oradebug unlimit4 oradebug hanganaly:e ;4 #ynta) for hanganaly:e for >A< -ogon to s&lplus as sysdba oradebug setmypid4 oradebug unlimit4 oradebug setinst all oradebug *g def hanganaly:e ; #ometimes it is not possible to even login "ith s&lplus ? as sysdba  at the time of hang, In that situation "e can use belo" command to enter "ith 88g on"ards s&lplus *prelim ? as sysdba

Oracle ait (vents That (veryone #hould !no" Part *8 This is part I in series of Oracle ait (vents That (veryone #hould !no" ere are some of the
I am trying to e)plain fe" common Oracle "ait events ,there causes and resolutions en0ueue

The process is "aiting on an en&ueue 'a loc$ you can see in v\loc$+. This commonly occurs "hen one user is trying to update a ro" in a table that is currently being updated by another user. The bloc$ers can be find out by using follo"ing &uery select 6 from dba0"aiters librar. cache pin The process "ants to pin an object in memory in the library cache for e)amination, ensuring no other processes can update the object at the same time. This happens "hen you are compiling or parsing a P-?#- object or a vie". Avoid compiling P-?#- object or vie" at high usage time to avoid this "ait event

librar. cache load lock The process is "aiting for the opportunity to load an object or a piece of an object into the library cache. 'Only one process can load an object or a piece of an object at a time.+ latch free The process is "aiting for a latch held by another process. 'This "ait event does not apply to processes that are spinning "hile "aiting for a latch4 "hen a process is spinning, it is not "aiting.+.-atches are light"eight seriali:ation devices used to coordinate multiuser access to shared data structures, objects, and files. -atches are loc$s designed to be held for e)tremely short periods of time for e)ample, the time it ta$es to modify an in*memory data structure. They are used to protect certain memory structures, such as the database bloc$ buffer cache or the library cache in the shared pool. -atches are typically re&uested internally in a 2"illing to "ait3 mode. This means that if the latch is not available, the re&uesting session "ill sleep for a short period of time and retry the operation later. Other latches may be re&uested in an 2immediate3 mode, "hich is similar in concept to a #(-( PDAT( /OAIT, meaning that the process "ill go do something else, such as try to grab an e&uivalent sibling latch that may be free, rather than sit and "ait for this latch to become available. #ince many re&uestors may be "aiting for a latch at the same time, you may see some processes "aiting longer than others. -atches are assigned rather randomly, based on the luc$ of the dra", if you "ill. hichever session as$s for a latch right after it "as released "ill get it. There is no line of latch "aitersjust a mob of "aiters constantly retrying. buffer bus. waits The process "ants to access a data bloc$ that is currently not in memory, but another process has already issued an I?O re&uest to read the bloc$ into memory. 'The process is "aiting for the other process to finish bringing the bloc$ into memory.+. The hot bloc$s can be found using vie" v\bh

Oracle ait (vents That (veryone #hould !no" part*II This is part II in series of Oracle ait (vents That (veryone #hould !no" control file se0uential read The process is "aiting for bloc$s to be read from a control file. This happens generally • • • •

ma$ing a bac$up of the controlfiles sharing information 'bet"een instances+ from the controlfile reading other bloc$s from the controlfiles reading the header bloc$

If this is major "aiting event, it means control file location need to changed to faster dis$ location

control file parallel write The process has issued multiple I?O re&uests in parallel to "rite bloc$s to all control files, and is "aiting for all of the "rites to complete. log buffer space The process is "aiting for space to become available in the log buffer '#pace becomes available only after -> has "ritten the current contents of the log buffer to dis$.+ This typically happens "hen applications generate redo faster than -> can "rite it to dis$.

This can also happen, if the I?O to dis$ "here redo logs are located is slo" There should be no log buffer space "aits as such in the database.< process archives filled online redo logs. log file parallel write The process is "aiting for bloc$s to be "ritten to all online redo log members in one group. -> is typically the only process to see this "ait event. It "ill "ait until all bloc$s have been "ritten to all members. log file s.nc The process is "aiting for -> to finish flushing the log buffer to dis$. This occurs "hen a user commits a transaction. 'A transaction is not considered committed until all of the redo to recover the transaction has been successfully "ritten to dis$.+

 A slo" -> process can introduce log file sync "aits "hich ma$es the user to e)perience "ait times during commit or rollbac$. The log file parallel "rite and log file sync "ait events are interrelated and must be dealt simultaneously.

e must try to allocate the redo logs to high performance dis$'#olid state dis$+. Also "e should try to reduce the load on -> by reducing commits in the applications. The manual hotbac$up piece can also introduce stress in the system by generating lot of redo stuff,#o avoid that during pea$ time #ometimes -> is starving for can starve for , increasing 2log file sync3 "aits. After all, these system calls and I?O calls must use can also be paged out. This can lead to slo"er response from -> too.

Oracle ait (vents That (veryone #hould !no" Part *III -ets get going "ith the Third part db file scattered read The process has issued an I?O re&uest to read a series of contiguous bloc$s from a data file into the buffer cache, and is "aiting for the operation to complete. This typically happens during a full table scan or full inde) scan.

e should chec$ if the &uery should be using full table scan. %a$e sure optimi:er stats are p to date. se Partition pruning to reduce number of bloc$s visited If an &uery that has been running fine for a "hile suddenly cloc$s a lot of time on the db file scattered read event and there hasn3t been a code change, you might "ant to chec$ to see if one or more inde)es has been dropped or become unusable. db file se0uential read The process has issued an I?O re&uest to read one bloc$ from a data file into the buffer cache, and is "aiting for the operation to complete. This typically happens during an inde) loo$up or a fetch from a table by >OID "hen the re&uired data bloc$ is not already in memory. Do not be misled by the confusing name of this "ait eventL

e should be chec$ing if the right inde)es are being used. A "rong inde) can ma$e the &uery perform badly.%a$e sure optimi:er stats are p to date. db file parallel read The process has issued multiple I?O re&uests in parallel to read bloc$s from data files into memory, and is "aiting for all re&uests to complete. The documentation says this "ait event occurs only during recovery, but in fact it also occurs during regular activity "hen a process batches many single bloc$ I?O re&uests together and issues them in parallel. 'In spite of the

name, you "ill not see this "ait event during parallel &uery or parallel D%-. In those cases "ait events "ith PC in their names occur instead.+ db file parallel write The process, typically DB>, has issued multiple I?O re&uests in par allel to "rite dirty bloc$s from the buffer cache to dis$, and is "aiting for all re&uests to complete. direct path read< direct path write The process has issued asynchronous I?O re&uests that bypass the buffer cache, and is "aiting for them to complete. These "ait events typically involve sort segments.

#- statements "ith functions that re&uire sorts, such as O>D(> BK, >OP BK, /IO/, DI#TI/O--P, "rite sort runs to the temporary tablespace "hen the input si:e is larger than "or$area in PA %a$e sure the optimi:er stats are up to data and &uery is using the right driving table. D(> BK clause to avoid sort entirely. %a$e sure appropriate value PA0A>(AT(0TA>(T is set. If possible use /IO/ A-instead of /IO/. Shared pool latch

The shared pool latch is used to protect critical operations "hen allocating and freeing memory in the shared pool.
o" to find "hich sid is doing full table scan %any times the 1er?.rman-e .? the database
-.lAmn r. = sn@statisti-> and (name liHe 7Itable s-ans (sh.rt tables!I .r name liHe 7Itable s-ans (l.n* tables!I .r name liHe 7Itable s-an r.
Optimi=er 

/e" Optimi:er =eature "ith 88g !ew Optimi=er (eature with ::g

8+ Invisible inde)es ith 88g release,inde) can be made invisible. They "ill be maintained all the time and can be made visible any time alter inde) ! invisible4 Optimi:er "ill not use the inde) if it is invisible alter inde) ! visible If "ant one &uery to use invisible inde),"e can set this parameter in the session optimi:er0use0invisible0inde)1true Then the &uery in the session "ill use the invisible inde) 5+()tented stats on the table If "e use any function on the predicate in the &uery,the optimi:er is not able to get the selectivity properly.ith 88g "e can create e)tented stats on the column for the function .

e)ec dbms0stats.create0e)tented0stats'o"nname 19 2O/3,tab0name19 2TAB3 ,e)tension19 2func'col+3+4 Then optimi:er "ill be able to find the correct selectivity

o" to use Optimi:er hints What is Optimi=er? hen a &uery is submitted in Oracle server. optimi:er attempts to generate the best e)ecution plan for a #- statement. The best e)ecution plan is defined as the plan "ith the lo"est cost among all considered candidate plans. The cost computation accounts for factors of &uery e)ecution such as I?O,
The best method of e)ecution depends on myriad conditions including ho" the &uery is "ritten, the si:e of the data set, the layout of the data, and "hich access structures e)ist. The optimi:er determines the best plan for a #- statement by e)amining multiple access methods, such as full table scan or inde) scans, and different join methods such as nested loops and hash joins. What is a Optimi=er Hint  Q Optimi:er ints are clause that used in s&l &ueries to force the optimi:er to ta$e particular route li$e using inde),nested loop join.S.ntax to use hint

?6` ^ int name96? Some of commonl. used hint

#(-(O# 6? empid, last0name, sal =>O% emp4 #(-(#T0>O# 6? 6 =>O% emp4 #(-(#T050>O# 6? 6 =>O% emp4 #(-(#T0>O#'8+ 6? empid, last0name, sal =>O% emp4 ?6`I/D(C'Inde) name+6? ?6`I/D(C0@OI/'emp inde)8, inde)5+6? ?6`I/D(C0('table table+ #(0A#'table table+

[arious @oins %ethod in Oracle Performance is $ey for O-TP system and "e should have good information about @oins %ethod in Oracle to increase performance for s&l &ueries ere are the @oins %ethod in Oracle available in Oracle database

!ested "oops *=or each ro" in the first ro" source access all the ro"s from the second ro" source. *Best for O-TP type transactions *it "ill be =astest if ro"s returned from first table are small *The optimi:er first determine the driving table and designates it as the outer loop.This is the driving ro" source. It produces a set of ro"s for driving the join condition. The ro" source can be a table accessed using inde) scan or full table scan. The ro"s can be produced from any other operation too. =or e)ample the output from a /ested -oop @oin can be used as a ro" source. *The optimi:er designate other table as inner -oop.This is iterated for every ro" returned from the outer loop. This is an access operation on a table and ideally should be an inde) scan. *Operation performed by I//(> table is repeated for every ro" returned in OT(> table *If the optimi:er is choosing to use some other join method, you can use the #(0/-'A B+ hint, "here A and B are the aliases of the tables being joined.

Plan ZZZZZZZZZZZZZZZZ* #(-(D(>0-I/(#0A-TAB-( A<<(## BK I/D(C >OID O(0-I/(#0A-I/D(C >A/( #o"s returned even if inner loop does not have any ro"s meeting the criteria *nli$e the nested loop "hich might be driven from either of the tables, this is one "ay join a 1 b'`+ "ill al"ays go to a before b, this may result in more e)pensive plan 'possibly non*/-+ '`+ al"ays goes on deficient side Sort #erge $oin *>o"s are produced from first table and are then sorted *>o"s are produced from second table and sorted by the same sort $ey as first table *Table A and B are /OT accessed concurrently *#orted ro"s from both sides are then merged together'joined+ */o concept of driving table F order cannot affect outcome *=aster than /ested loops ifE ro"s produced from table 8 are large access to table 5 re&uires a large range scan for each ro" in table 8 *To instruct the optimi:er to use a sort merge join, apply the #(0%(>( hint. Kou might also need to give hints to force an access path. *There are situations "here it is better to override the optimi:e "ith the #(0%(>( hint.

()ecution Plan %(>( @OI/ #O>T @OI/ TAB-( A<<(## =-- O= O(0O>D(>0-I/(#0A-#O>T @OI/ TAB-( A<<(## =-- O= O(0(AD(># Hash $oin *#mallest table is passed over and a hashing algorithm is applied to each ro" to create a hash table in memory. *#econd table is passed over and the same hashing algorithm applied to chec$ for matches 'ie.  joins+ *=aster than sort merge joinE sort operation re&uired by sort merge join can be e)pensive if tables are large and not in any matching order  *Apply the #(0A# hint to instruct the optimi:er to use a hash join "hen joining t"o tables together. *(nsure that hash0area0si:e is large enough to hold the smaller table in memory. Other"ise, Oracle must "rite to the T(%P tablespace, slo"ing do"n the hash join

Plan ZZZZZZZZZZZZZZZZ* #(-(D(>0-I/(#0A-TAB-( A<<(## =-- O(0(AD(># 2artesian $oin*Are generally e)pensive as the result is the
Plan ZZZZZZZZZZZZZZZZ* #(-(
#O>T /I( %(>( @OI/ T(#IA/ /(#T(D -OOP# TAB-( A<<(## BK I/D(C >OID O(0O>D(>0-I/(#0A-I/D(C >A/( #D(>0-I/(#0A--0/8 TAB-( A<<(## BK I/D(C >OID O(0(AD(># I/D(C >A/( ##0/8 #O>T @OI/ I/D(C =A#T =-- ##0/8 Statistics

=ind inde)es and assigned columns for a table It is very common that you "ant to $no" ? see the available inde) on a table.  And also you sometime "ant to $no" the columns "hich are assigned to the inde)es. =ollo"ing vie"s "ould be helpful in these cases dba0ind0columns all0ind0columns set pagesi:e W verify off echo off  col table0name head 2Table /ame3 format a5 col inde)0name head 2Inde) /ame3 format a5W col column0name head 2
o" Table %onitoring and #TATI#TI<#0-([(- parameter are connected from 8g on"ards *efore Oracle :5g  *Automated collection of statistics for objects that had become stale "as controlled by the setting of the %O/ITO>I/ flag on table. Depending on the %O/ITO>I/ flag, the AT(>0#TAT#0@OB job collected AT(> (%PTK and AT(> #TA-( on the flagged objects. :5g onwards *The %O/ITO>I/ and /O%O/ITO>I/ $ey"ords are deprecated and "ill be ignored.Table*monitoring feature is no" controlled by the #TATI#TI<#0-([(- parameter.

hen #TATI#TI<#0-([(- is set to BA#I<, monitoring is disabled on the table. hen #TATI#TI<#0-([(- is set to TKPIT, PDAT(, and D(-(T( operations for the table since the last time statistics "ere gathered. This information on changes made is maintained in the #A and periodically 'about every 8W minutes+ the #%O/ flushes the data into the data dictionary tables. The data dictionary information is made visible through the vie"s DBA0TAB0%ODI=I0TAB0%ODI=I<(/T statistics preference. This can be set globally using DB%#0#TAT#.#(T0-OBA-0P>(=# or at the table level using DB%#0#TAT#.#(T0TAB-(0P>(=#. (.g. to modify and chec$ the staleness threshold for table # schemaE #-9 e)ec dbms0stats.set0table0prefs'null,3#<(/T3,5X+

Oracle TutorialE o" to chec$ #tale statistics #tats can become stale "hen the data in the table changes substantially.p to date statistics are important to generate good e)ecution plans o" Oracle decide if the stats has become stale #tats are considered stale "hen R'I/#(>T# ` PDAT(# ` D(-(T(#+ 91 8Y of /%0>O# from dba0tablesE Parameter setting re&uired to Trac$ the table changes Prior to Oracle 8g, automated collection of statistics for objects that had become stale "as controlled by the setting of the %O/ITO>I/ flag on table. Depending on the %O/ITO>I/ flag, the AT(>0#TAT#0@OB job collected AT(> (%PTK and AT(> #TA-( on the flagged objects. In 8g the %O/ITO>I/ and /O%O/ITO>I/ $ey"ords are deprecated and "ill be ignored.Table*monitoring feature is no" controlled by the #TATI#TI<#0-([(- parameter. hen #TATI#TI<#0-([(- is set to BA#I<, monitoring is disabled on the table. hen #TATI#TI<#0-([(- is set to TKPIT, PDAT(, and D(-(T( operations for the table since the last time statistics "ere gathered. This information on changes made is maintained in the #A and periodically 'about every 8W minutes+ the #%O/ flushes the data into the data dictionary tables. Kou can manually flush the information by calling dbms0stats.=-#0DATABA#(0%O/ITO>I/0I/=O'+. The data dictionary information is made visible through the vie"sE DBA0TAB0%ODI=I0TAB0%ODI=I[(>OTPT O/ #-9 D(<-A>( Obj-ist dbms0stats.ObjectTab4 B(I/ DB%#0#TAT#.AT(>0#<(%A0#TAT#'o"nname193#
=O> $ in Obj-ist.=I>#T..Obj-ist.-A#T -OOP dbms0output.put0line'Obj-ist'i+.o"nname 77 2.3 77 Obj-ist'$+.Obj/ame 77 2 2 77 Obj-ist'$+.ObjType 77 2 2 77 Obj-ist'$+.partname+4 (/D -OOP4 (/D4 ? The belo" s&l can also be used to find out insert,updates,deletes select u.TI%(#TA%P, t.last0analy:ed, u.table0name, u.inserts, u.updates, u.deletes, d.num0ro"s, decode'num0ro"s,,3Table #tats indicate /o >o"s3, nvl'TO0<A>'''.inserts`u.deletes`u.updates+?d.num0ro"s+ 6 8,3SSS.SS]+ ,3/ull [alue in #(>0TAB0%ODI=I0TAB0%ODI=I
#tarting "ith Oracle88g, the staleness threshold can be set using the #TA-(0P(><(/T statistics preference. This can be set globally using DB%#0#TAT#.#(T0-OBA-0P>(=# or at the table level using DB%#0#TAT#.#(T0TAB-(0P>(=#.

o" to find table "here statistics are loc$ed Kou can use the belo" &uery on o" to find table "here statistics are loc$ed select o"ner, table0name, stattype0loc$ed from dba0tab0statistics "here stattype0loc$ed is not null4

/o" once "e find out the objects,"e can use belo" &ueries to unloc$ them e)ec dbms0stats.unloc$0schema0stats'2schema0o"ner3+4 e)ec dbms0stats.unloc$0table0stats'2table0o"ner3,3table0name3+4 4eneral Performance topics

s&l performance tuning #&l statement are "ritten to retrive ?fetch data from the database. e "ant our s&l statement to running fast and providing the results in secs.  A poorly design s&l could slo" do"n the entire database operation and bring the entire operation to halt. It is much harder to "rite efficient #- than it is to "rite functionally correct #-. s&l performance tuning can signficantly improve a system3s health and performance. The $ey to tuning #- is to minimi:e the data it accesess to provide the result. e can minimi:e the data it accesses to provide the result through optimal search path.  A simple e)ample "ould be select 6 from dept "here emp18 /o" this &uery "ill have to search the entire table dept to find out the data "here emp18. #o it has to access the full table /o" if "e create the inde) to emp column, then it can just access the inde) and get the result. #o here it access the less data ere the general tips for s&l performance tuning 8+ =irst you must have all the re&uired tools for s&l tuning .Kou must have good information on tracing,formating the trace, e)plain plan,reading th e)plain plan. ood $no"legde about the various join method availabe in Oracle and ho" to use them efficently 8+ >ead less data and be I?O efficient. The more data you read for the s&l statement, the more latches it need to ac&uire and it slo"s do"n the performance. so it must be doing fe"er logical reads al"ays rite sensible s&l statement "here proper filters . Do chec$ about the number of ro"s in various tables involved and find out the best method to create the s&l statement 5+ se good inde)es BTree inde)es and Bitmap inde)es can be used to increased the performance of the &ueries if the data returned is less than 8Y. But "e must be careful "hile creating the inde) as it need to maintained for insert,update and delete operation also. #o creating an inde) create overhead over many things. #o "e must carefull e)amine the effect of creating the inde). ;+ Avoid s&l "hich disable the usage of inde)

#- that Disables Inde)es =unctions ' to0char'+, to0date'+, etc. + move the function to the constant?bind variable side Type . se /IO/ for &ueries "ith t"o clear e)ecution paths4 each returning a fairly small number of ro"s. Don3t use union for &ueries that are li$ely to return a large number of ro"s since all ro"s need to be sorted and most of them "ill be discarded. O> tends to disable the inde) U+ #e the accurate statistics on the table to get the optimal plan. X+ If you are using function on e)pression on the condition,chec$ if there is a function*based inde) on that column. If it is not present the inde) "ill not be used N+ se e)ists vs in and /on e)ists vs not in for correlated sub&ueries S+ Avoid Poor coding practices a+ Avoid cartesian join . %a$e sure all the tables re&uired in the &ueries are needed and are lin$ed to each other  b+ se Decode to avoid multiple trips to database c+ Try to avoid outer join d+ #ometimes decomoposing the logic into small parts ma$es the "or$ faster  8+ If trying to use the comple) vie",chec$ if the base tables can be used instead as the vie" tends to ma$e the performance bad 88+ se /IO/ A-- [s /IO/ if you $no"s that data retrieved "ill not have duplicate ro"s 85+ se hints to optimi:e the e)ecution plan. #ometimes hint can be used to change the e)ecution plan for the &uery so as to ta$e the most optimal path. #ome times bind pee$ing create a bad plan ,so in that case putting necessary hint to fi) the plan help in getting the good performance everytime The most common hints are ?6` -(ADI/ 'table alias+ 6? specifies table by "hich to start join ?6` =I>#T0>O# 6? very good for on*line screens F favours /(#T(D -OOP# ?6` I/D(C ' table alias.inde) name+ 6? specifies inde) you "ant to use. /oteE if inde) gets dropped and recreated and name changes then hint is no longer valid.

?6` #(0/- 'table alias8 table alias 5+6? as$s optimi:er to use the /ested -oop @oin for the t"o tables specified  Avoid unneccsary hint and use them "ith care

s&l performance tuning some &uestions Here are some of s0l performance tuning examination 0uestions

8+ follo"ing an A/A-K_( command, "hich of the follo"ing data dictionary vie"s "ill contain statistics on the number of ro"s in a table a dbms0ro"s b dba0table0ro"s c dbs0tables d dbs0statistics #olution 'c+ 5+ histograms are useful because they allo" the optimi:er to have a better idea of ho" the data in an inde)ed columns is a sorted b named c entered d distributed #olution 'd+ ;+ the optimi:er mode can be set at "hich of the follo"ing levels a instance b session c statement d all of the above #olution 'd+ M+ the ability to store predefined e)ecution plans so that the optimi:er $no"s in advance "hich e)ecution plan is best is called a stored outline b materiali:ed vie"s c hash cluster  d inde) organi:ed tables #olution 'a+ W+ "hich of the follo"ing types of inde)es stores the inde)ed columns values associated ro" id as binary string a b*tree inde) b reverse $ey inde)

c bitmap inde) d bit"ise inde) #olution 'c+ U+ "hich of the follo"ing factors "ould not ma$e a column a good candidate for b*tree inde) a the data in the column has lo" cardinality b the column fre&uently used in s&l statement "here clases c most &ueries on the table return only small portion of all ro" d none of the above #olution 'a+ X+ the process of preparing a statement for e)ecution is called a caching b hashing c parsing d none of the above #olution 'c+ N+ finding a statement already cached in the library cache is referred as a cache hit b cache miss c cache match d cache parse #olution 'a+ S+ to determine if a ne" statement is a match for an e)isting statement already in the shared pool, oracle compares each statement a result set b security c e)ecution plan d hashed value #olution 'd+ 8+ in order for t"o statements to result in a cache hit, "hich of the follo"ing must be true a the statements must use the same case F either upper , lo"er , or mi)ed b the statements must be issued against the same table c the statements must be on the same number of lines d all of the above must be true #olution 'd+ 88+ "hich dynamic data dictionary vie" contains information about the library cache hit ratio a v\ro"cache b v\librarycache c v\dictionarycache d all of the above