ࡱ> >n( / 0DTimes New Roman@b@bLb0pbpb 0DArialNew Roman@b@bLb0pbpb 0" DComic Sans MSn@b@bLb0pbpb 0B0DCourier NewSn@b@bLb0pbpb 01@DCourier (W1)n@b@bLb0pbpb 0PDCourier(W1)n@b@bLb0pbpb 0@  @@``  @n?" dd@  @@`` ldw H     !#%')+ 79-F    &(*.102468:H    ; #%'BC+ T=? ay4nC@g4:d:d|b 0tbfh Tppp@  <4!d!d@b@b ? %O =aSQL Tuning for DBAs0^Tim Gorman Principal Evergreen Database Technologies, Inc. http://www.evergreen-database.com |_  %!:Agenda<The issue: identifying performance problems fixing the problem making sure that everyone knows that it has been fixed What seems to be the problem? Poor performance, but the machine doesn t seem busy. Or... Poor performance and the machine is melting down. Or& Perhaps a bit of both?Z k km r It could be:unsuitable data model for business requirements poorly tuned SQL statements I/O contention or saturation poorly tuned Oracle instance memory contention, latches network problems, failing peripherals` It could be:unsuitable data model for business requirements poorly tuned SQL statements I/O contention or saturation poorly tuned Oracle instance memory contention, latches network problems, failing peripherals60{ J...95% of the time, it s  bad SQL...more  bang for the buck comes from tuning SQL if the SQL is inefficient, then what is the purpose of tuning anything else? understand the difference between accommodating a problem and fixing a problem adding more CPUs, memory, or faster disk accomodates the problem, it doesn t fix it sometimes, you cannot fix a problem and you must accommodate it, but it s important to know that/MO/M"  )  $ c,Tune SQL first, then consider other areas...unsuitable data model for business requirements poorly tuned SQL statements I/O contention or saturation poorly tuned Oracle instance memory contention, latches network problems, failing peripherals@0 {0{0So how do I do it?start with O/S utilities to get an idea about the nature of the problem: CONTENTION (i.e. locking, blocking, etc) not very busy, but performance is terrible SATURATION (i.e. resource-hogs) extremely busy, with terrible performance both? attend to saturation first, then deal with any remaining contention later often saturation causes contention Use V$SQLAREA to find resource-hogs (saturation) Use SESSION WAIT views to find contentionI)+ *J#[I)+ * %     g O/S utilitiesUnix topcpu to find the heaviest consumers of CPU if you don t have such a utility, use: ps -eaf | sort -n +3 | tail iostat examine disk I/O utilization vmstat, sar examine CPU, memory, swap/page statistics Windows NT Task ManagerM * M * tMB3 V$SQLAREAReal-time view into the SQL Area cache of the Shared Pool SQL_TEXT 1st 1000 characters of the SQL statement full text can be retrieved from V$SQLTEXT BUFFER_GETS logical reads; reads attempted from the buffer cache DISK_READS physical reads; reads not satisfied in buffer cache SORTS USERS_EXECUTING # of times statement was executed LOADS # of times statement loaded into Shared Pool: S 6 5"-: R 6 "-62Detecting expensive SQL statements using V$SQLAREA33statements doing lots of I/O, look in v$sqlarea for disk_reads buffer_gets derived  load factor ((disk_reads * 100) + buffer_gets) ideal for creating a top 10 list SELECT SQL_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA WHERE DISK_READS > 100,000 AND BUFFER_GETS > 1,000,000 ORDER BY  load factor formula (above) DESC5-#!5-# (* EXPLAIN PLANPerforms the PARSE phase of SQL execution ONLY SQL statement is not actually executed useful with long-running statements Access method information is saved to a  plan table Getting report from  plan table requires a query fully documented in chapter 21 of Oracle8 Server Tuning manual Guy Harrison s Oracle SQL: High-Performance Tuning also an excellent reference Useful only for determining the access method provides no information on whether access method is good/Kg.9/Kg"$. (# i*EXPLAIN PLAN (cont d)ythat which is indented most is executed first that which is indented to the same depth is executed in top-to-bottom orderSQL*Plus and SET AUTOTRACEIf using SQL*Plus 3.3 or above, use AUTOTRACE SET AUTOTRACE ON first displays feedback from SQL statement next displays the EXPLAIN PLAN output requires INSERT access into a table named PLAN_TABLE next provides run-time statistics from V$SESSTAT view must be granted role PLUSTRACE Parse, execute, and fetch performed long-running statements can be bothersome Provides both access method and empirical statistics enough info to determine whether SQL is efficient or not.Q56$*59.Q56$*59#Comparing access methodsNEXPLAIN PLAN only displays what access method will be used; does not prove whether the method is efficient or not it can justify your preconceptions what you think is best may not actually be the best thing to do. Beware! AUTOTRACE displays the access method as well as run-time statistics which can be used for comparison it can verify whether one access method is better than the other when comparative test cases are performed logical reads (i.e. sum of consistent gets and db block gets) is the best single metric to use for comparison best indicator of the amount of work performed s#Je/s#Bee  2 nG SQL TracingOracle  server process outputs a record of all SQL statements received from the  client process to an operating system file (i.e.  raw trace file) SQL statement text run-time statistics for parsing, execution, and fetching wait events bind variable values$mm % SQL Tracing !&TKPROF performance summary "c SQL Tracing: enabling/disablingglobally for an entire database instance  init.ora parameter SQL_TRACE = TRUE (default: FALSE) for one session for itself ALTER SESSION SET SQL_TRACE TRUE|FALSE ALTER SESSION SET EVENTS  10046 trace name context forever, level NN ALTER SESSION SET EVENTS  10046 trace name context off DBMS_SESSION.SET_SQL_TRACE(true | false) level NN is either 4, 8, or 12 level 4 gives WAIT EVENT information level 8 gives  bind variable values level 12 is combination of levels 4 and 8)7m8Ht)7i8/t,*#uRSQL Tracing: enabling/disabling (cont d)**"for one session from another session DBMS_SYSTEM package SET_SQL_TRACE_IN_SESSION (SID, SERIAL#, true | false)  sid and  serial# come from V$SESSION RDBMS 7.2 and below: oradbx (UNIX) and orambx (VMS) debug operating-system-PID event 10046 trace name context forever, level NN RDBMS 7.3 and above: Server Manager oradebug setospid operating-system-PID oradebug event 10046 trace name context forever, level NN operating-system-PID : Oracle dedicated server process (i.e.  shadow process) level NN is either 4, 8, or 12Z%6(5L%an%(5/%7Abp8 w $K.SQL Tracing:  raw fileWAIT events and BIND VARIABLE values can be viewed using a text editor very cryptic text used as input for the TKPROF report formatter  raw file can be EXTREMELY useful for debugging logging of WAIT events (level 4) augments info in V$SESSION_WAIT bind variable values (level 8) can be used to debug applications Spend the time becoming familiar with it, because SQL Trace is the key to everything!fY_VF_%JBSQL Tracing:  raw file (excerpt) &M Using TKPROFProgram to summarize and format  raw SQL trace information first have to find  raw SQL trace file located in USER_DUMP_DEST directory file is usually named  $ORACLE_SID_ora_nnnnn.trc, where nnnnn is the Unix operating system PID of the Oracle  server process (I.e.  PROD_ora_12345.trc )  _TRACE_FILES_PUBLIC = TRUE so everyone can read these files! TKPROF ignores information from levels 4, 8, & 12 Get syntax information by entering tkprof at the UNIX command-line.dvd;  UN)i,TKPROF syntax (cont d) *Psorting TKPROF outputsort by any criteria, to have worst SQL statements  percolate to the top of the report options for logical reads (exeqry,fchqry,execu,fchcu) are the best, most portable metric for determining the efficiency of a SQL statement... ...but be sure to factor counts into consideration! elapsed and CPU times are good secondary data too dependent on system issues to be considered independently always generate EXPLAIN PLAN output provides insight into access methods4.>$% =$%PsC+R TKPROF output ,S,TKPROF output (cont d) -T,TKPROF output (cont d) .U,TKPROF output (cont d) kV$SESSION_WAITReal-time view of the SESSION WAIT interface SID foreign key to V$SESSION view (i.e. client-process information) EVENT the event that the session is waiting on there are hundreds of WAIT events listed in V$EVENT_NAME view & Oracle8 Server Reference P1TEXT, P1 P2TEXT, P2 P3TEXT, P3 descriptive text and values to further describe event tersely documented in V$EVENT_NAME view more completely documented in Oracle8 Server Reference Anjo Kolk s paper available to document Oracle7 wait events-@K7!<-?K!0G< 0l"V$SESSION_EVENT and V$SYSTEM_EVENT##3V$SESSION_EVENT has cumulative totals of wait event info for connected sessions V$SYSTEM_EVENT has cumulative totals of wait event info for entire database instance since startup EVENT TOTAL_WAITS TOTAL_TIMEOUTS TOTAL_TIME AVERAGE_TIME Last 2 columns not populated if parameter TIMED_STATISTICS set to FALSEZ9H)?+9HmSome wait eventsqdb file scattered read db file sequential read latch free free buffer waits log file sync write complete waitsrr$YnV$SESSION_WAIT example script oV$SESSION_WAIT example outputBecome familiar with the contents of Appendix A of the Oracle8 Server Reference manual to understand what these events could mean Thorough understanding of Oracle process architecture, enqueues, latches, and the Shared Pool is useful6% )p$Cumulative session SESSION WAIT infoV$SESSION_WAIT real-time, not cumulative good for identifying problems as they are occurring V$SESSION_EVENT contains cumulative statistics for each active session in the database information exists only for active sessions good for identifying problems as they are occurring V$SYSTEM_EVENT contains cumulative statistics since the instance was started summarized for each wait event across the instance good for a 50,000ft view of the situation N  4R ?/,SummaryMost of the time,  bad SQL statements are the problem query V$SQLAREA, sort by DISK_READS & BUFFER_GETS TRIAGE: find the 2-3 worst statements, fix them REPEAT: once a week - do NOT do it once and then stop! Once resource-hogs are found, empirically determine the best access method don t guess, use test cases -- try it out use SQL Trace/TKPROF when comparing demonstrate proof using test cases in SQL*Plus Identify bottlenecks using SESSION WAIT views but first fix the resource hogs7K}. 7Y?8}  04 DocumentationOracle SQL: High-performance Tuning Guy Harrison (Prentice Hall) ISBN 0135142311 I ve scanned it -- it s quite good! Oracle Performance Tuning Mark Gurry and Peter Corrigan (O Reilly & Assoc) ISBN 1565922377 I scanned chapters on SQL tuning (ch 6-10?); looks OK What do you care what other people think? Richard P. Feynman (autobiographical) 2nd half of book on space shuttle Challenger investigation shows value of test cases to convince skeptics$-$A6*b/$-$A6I  b  4H}fInformation on the Web?Oracle Support MetaLink http://support.oracle.com/metalink/ Oracle TechNet http://technet.oracle.com/ OraPub white paper site http://www.orapub.com (follow links for papers) every paper should be downloaded and devoured! My site http://www.evergreen-database.com under construction - papers and tools to be posted soon!$0/[$ (/"'b  /245789:< @ADEFPQR S!T"U#X&Y'Z([)\*]+^,d.j/q0r1s2t3Pb  ` ̙33` 3` 3333f` 999MMM` f` f3` 3>?" dU@$|?" xd @  dd`8 n?" dZ(@   @@``PR    @ ` ` p>> v(     `vgֳgֳ ?`` v =*    `tvgֳgֳ ?`  v c!http://www.evergreen-database.com&"!   `vgֳgֳ ?`  v ?*pB  Hp?x   Zvgֳgֳ ?HH| v ? Slide Title  #  Tvgֳgֳ ? v ;Body Text Second Level Third Level Fourth Level Fifth Level     <  TT vgֳgֳ?Q ?*H  0޽h? ? ( PowerPoint 0 g_@( (   Tt7jJjJ ?G   8 [*   T7jJjJ ?   8 ]*   Z47jJjJ ?`G  8 [*   Z7jJjJ ?`  8 ]*   T7UU?oyW  Page *Z ###55FFp  01 ?  8#  TT7gֳgֳ ? 6 8 ;Body Text Second Level Third Level Fourth Level Fifth Level     <H  0sTh? ? a(f 6.0 (     T7jJjJ ?G   8 A*   T7jJjJ ?   8 C*   ZT7jJjJ ?`G  8 A*   Z7jJjJ ?`  8 C*   T7UU?oyW  fPage *Z ###55FFH  0sTh? ? a( % :2P(     f8gֳgֳ ? H 8    f8gֳgֳ ? @@ 8 " d`8H  0޽h ? (   % $p(  P`    f8gֳgֳ ? HH|  8    fT8gֳgֳ ?`  8 H  0޽h ? (  % $((   ( (  f8gֳgֳ ? HH|  8  (  ft8gֳgֳ ? 8 H ( 0޽h ? (L  |t( x | | s 8gֳgֳ ? HH|  8  | s 8gֳgֳ ? 8 p" | H?H | 0޽h ? (  % $,(  , ,  f8gֳgֳ ? HH|    ,  f8gֳgֳ ? PP   H , 0޽h ? ( . %  ( $@   # l8gֳgֳ ? HP|     # l8gֳgֳ ?  pB  H? p2  H? H  0޽h ? (  % $@(  @ @  fYgֳgֳ ? HH|    @  ftYgֳgֳ ?`   H @ 0޽h ? (   ( x l  C [ HH|   l  C \0   H  0޽h ? (  % $0D(  D D  f]gֳgֳ ? HH|    D  f]gֳgֳ ?  H D 0޽h ? (  % $PH(  H H  f_gֳgֳ ? HH|    H  f4`gֳgֳ ? 4  H H 0޽h ? (  % $p`( hv0xev0d ` `  fbgֳgֳ ? HH|    `  ftbgֳgֳ ?   H ` 0޽h ? ( / % ^V( x   # lcgֳgֳ ? HH|     Tcgֳgֳ?   4select c.acct_id, c.name from customer c, product p where c.acct_id = p.acct_id and p.tel_no like  303624% ;qqZ  Tދgֳgֳ?v  c1generates the following output from EXPLAIN PLAN:22w  Tߋgֳgֳ? ^ SELECT STATEMENT NESTED LOOPS TABLE ACCESS (BY ROWID) PRODUCT INDEX (RANGE SCAN) PRODUCT_IX2 TABLE ACCESS (BY ROWID) CUSTOMER INDEX (RANGE SCAN) CUSTOMER_PK  # ldߋgֳgֳ ?`0  H  0޽h ? (  % $h(  h h  f$gֳgֳ ? HH|    h  fgֳgֳ ?0   H h 0޽h ? (  % $(     fgֳgֳ ? HH|      f$gֳgֳ ?`   H  0޽h ? (  % $(     fgֳgֳ ? HH|      fDgֳgֳ ?P$L  H  0޽h ? (  %   # (     fgֳgֳ ? HH|   ^2  61?`$  Tdgֳgֳ?p 7SQL*Plus, Forms, Reports, PRO*C, OCI, PowerBuilder, etc88& ^2  61?@ $  T$gֳgֳ?  rBOracle server or  shadow process""pB  H?< pB  H?<   Tgֳgֳ?-  U SQL stmts   Tgֳgֳ?]  GData and status codesvB  N?    Zgֳgֳ1?  l  9NETWORK^2  61? <h^2  61? <^2  61?0 < ^2  61? <H vB  N)?    Tgֳgֳ? V   Etrace output stream  Tgֳgֳ?IB0 Z( raw SQL trace file  TDgֳgֳ? zP  ?database server  Tgֳgֳ? z  ?database clientH  0޽h ? (   % 3 + 0(  Tl    fԀgֳgֳ ? HH|   ^2  61?$  ^2  61? < ^2  61?d  ^2  61? |   Tgֳgֳ?I00  Z( raw SQL trace file^2  61?$  ^2  61? < ^2  61?d  ^2  61? |   TTgֳgֳ?I0  DTKPROF report file^2  61?ttL   Tgֳgֳ? /  >TKPROF programpB  H)?P 0@P pB  H)?P P   Tԃgֳgֳ?   ?database serverH  0޽h ? (  % $P( cv0xev0d    fgֳgֳ ? HH|      fgֳgֳ ?P   H  0޽h ? (   % $p(     f4gֳgֳ ? Hh|      fgֳgֳ ?<l   H  0޽h ? ( ! % $(     ftgֳgֳ ? HH|      fԉgֳgֳ ?P`,   H  0޽h ? ( " % ( Fg    fgֳgֳ ? HH|      TTgֳgֳ?]+ ===================== PARSING IN CURSOR #16 len=478 dep=2 uid=45 oct=3 SELECT OI.ORGANIZATION_ID FROM HR_ORGANIZATION_INFORM ATION OI,FND_ORACLE_USERID UI WHERE UI.ORACLE_USERNAME = :b1 AND TO_CHAR(UI.ORACLE_ID) = OI.ORG_INFORMATION1 AND OI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND EXISTS (SELECT OI2.ORGANIZATION_ID FROM HR_ORGANIZATION _INFORMATION OI2 WHERE OI2.ORGANIZATION_ID = OI.ORGANIZAT ION_ID AND OI2.ORG_INFORMATION_CONTEXT = 'CLASS' AND OI2. ORG_INFORMATION1 = 'OPERATING_UNIT' AND OI2.ORG_INFORMA TION2 = 'Y' ) END OF STMT PARSE #16:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3 EXEC #16:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3 WAIT #16: nam='db file sequential read' ela= 0 p1=1 p2=290 p3=1 WAIT #16: nam='db file sequential read' ela= 1 p1=6 p2=17447 p3=1 WAIT #16: nam='db file sequential read' ela= 0 p1=6 p2=17734 p3=1 FETCH #16:c=1,e=1,p=3,cr=3,cu=0,mis=0,r=1,dep=1,og=3+   -H  0޽h ? ( # % $(     ftgֳgֳ ? HH|      fgֳgֳ ?   H  0޽h ? ( & % 5-(     fTgֳgֳ ? HH|     Ngֳgֳ?0d MRecommended TKPROF call syntax: tkprof trc-filename output-filename \ sort=exeqry,fchqry,execu,fchcu \ explain=un/pw print=20 For example: tkprof ora_12345.trc /tmp/x.tkp \ sort=exeqry,fchqry,execu,fchcu \ explain=scott/tiger print=20!&I"@  + "@:! "   H  0޽h ? ( ' % $( pu >}0    ftgֳgֳ ? HH|      fgֳgֳ ?<Pl   H  0޽h ? ( ( % yq0 (     fgֳgֳ ? HH|     Tgֳgֳ?x  TKPROF: Release 7.1.6.2.0 - Production on Wed Dec 4 19:58:59 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Trace file: test_ora_3228.trc Sort options: exeqry fchqry ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** bH  0޽h ? (w ) %  P(     fgֳgֳ ? HH|     Tgֳgֳ?  %SELECT OI.ORGANIZATION_ID FROM HR_ORGANIZATION_INFORMATION OI,FND_ORACLE_USERID UI WHERE UI.ORACLE_USERNAME = :b1 AND TO_CHAR(UI.ORACLE_ID) = OI.ORG_INFORMATION1 AND OI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND EXISTS (SELECT OI2.ORGANIZATION_ID FROM HR_ORGANIZATION_INFORMATION OI2 WHERE OI2.ORGANIZATION_ID = OI.ORGANIZATION_ID AND OI2.ORG_INFORMATION_CONTEXT = 'CLASS' AND OI2.ORG_INFORMATION1 = 'OPERATING_UNIT' AND OI2.ORG_INFORMATION2 = 'Y' ) H  0޽h ? ( * % yqp ( sP >}0    fgֳgֳ ? HH|     T4gֳgֳ? v  7call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 3117 0.83 0.92 0 0 0 3113 Fetch 3117 7.03 9.88 40 55902 0 3105 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6235 7.86 10.80 40 55902 0 6218 88  H  0޽h ? ( + % G?(     fgֳgֳ ? HH|     TTgֳgֳ? Y'Misses in library cache during parse: 0 Optimizer hint: RULE Parsing user id: 45 (APPS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT HINT: RULE 5 FILTER 5 NESTED LOOPS 14 TABLE ACCESS (BY ROWID) OF 'FND_ORACLE_USERID' 14 INDEX (UNIQUE SCAN) OF 'FND_ORACLE_USERID_U2' (UNIQUE) 3 TABLE ACCESS (BY ROWID) OF 'HR_ORGANIZATION_INFORMATION' 4 INDEX (RANGE SCAN) OF 'HR_ORGANIZATION_INFORMATIO_FK1' (NON-UNIQUE) 1 TABLE ACCESS (BY ROWID) OF 'HR_ORGANIZATION_INFORMATION' 1 AND-EQUAL 2 INDEX (RANGE SCAN) OF 'HR_ORGANIZATION_INFORMATIO_FK2' (NON-UNIQUE) 3 INDEX (RANGE SCAN) OF 'HR_ORGANIZATION_INFORMATIO_FK1' (NON-UNIQUE) ((H  0޽h ? ( 0 % 0(( $@   # l gֳgֳ ? HH|     # lt gֳgֳ ?   H  0޽h ? (  $(  r  S T  H|   r  S  P`  H  0޽h ? (} 1 % (    # ltgֳgֳ ? HH|     # lgֳgֳ ?0     N4gֳgֳ?   c= full-table scan = indexed scan = latch contention (i.e. db block contention, LRU chains, etc) = waiting on DBWR to clear dirty blocks = LGWR writing due to COMMIT or ROLLBACK = waiting on DBWR to finish writing :0n {V9H  0޽h ? (" 2 % N(    # lgֳgֳ ? HH|   N  NTgֳgֳ? (SQL> col event format a20 truncate SQL> col p1 format a12 truncate SQL> col p2 format a12 truncate SQL> col p3 format a12 truncate SQL> SQL> select event, 2 p1text || '=' || p1 p1, 3 p2text || '=' || p2 p2, 4 p3text || '=' || p3 p3 5 from v$session_wait 6 where event not like  % timer 7 and event not like  rdbms ipc message 8 and event not like  SQL*Net % ; "b /H  0޽h ? ( 3 % sk (    # lgֳgֳ ? HH|   G  Ttgֳgֳ?4 3 SID EVENT P1 P2 P3 ------ -------------------- ------------ ------------ --------- 127 latch free addr=4597239 latch=30 =0 201 db file scattered re file=73 block=75444 blocks=16 352 db file sequential r file=111 block=9291 blocks=1.40>=6   `gֳgֳ ? P`   H  0޽h ? (  @$(  r  S  H|   r  S h   H  0޽h ? (  % $P(     f4gֳgֳ ? H@|      fgֳgֳ ?``   H  0޽h ? ( , % $p( t     fgֳgֳ ? H@|      fgֳgֳ ?0`   H  0޽h ? (   (  l  C 7 H@|  8 l  C 47D`  8 H  0޽h ? (D 0 `(     H1 ?   8   f8gֳgֳ ? 6  8  H  0sTh ? a(b 0 "d(  d d  H1 ?    d  f4cD[D[ ? =   < '';;OOH d 0sTh ? a(b 0 "l( jTj  l l  H1 ?    l  fdD[D[ ? =   < '';;OOH l 0sTh ? a(b# 0 "( ʤ     H1 ?      fD[D[ ? =   < '';;OOH  0sTh ? a(b% 0 " ( hv0xev0|d8    H1 ?      ftD[D[ ? =   < '';;OOH  0sTh ? a(b& 0 "@( `90    H1 ?      f4D[D[ ? =   < '';;OOH  0sTh ? a(b, 0 "`( g    H1 ?      fTD[D[ ? =   < '';;OOH  0sTh ? a(: 0 x( Hи` R  3    8~  C 8 6  8  H  0sTh ? a(r 0 x( }иHy R  3    8~  C 48 6  8  H  0sTh ? a( 0 x( и} R  3    ~  C 48 6    H  0sTh ? a(0 0 x ( 0и  R   3    ~   C T[ 6    H   0sTh ? a(3 0 x@( Z1 R  3    ~  C t_ 6    H  0sTh ? a(6 0 x`( `и^ R  3    ~  C a 6    H  0sTh ? a(G 0 x<( tиߧ <R < 3    ~ < C  6    H < 0sTh ? a(c 0 x`@( иH4 @R @ 3    ~ @ C Ԇ 6    H @ 0sTh ? a(u 0 xD( и DR D 3    ~ D C  6    H D 0sTh ? a(K 0 xH(  HR H 3    ~ H C  6    H H 0sTh ? a(J 0 xL( и@ LR L 3    ~ L C  6    H L 0sTh ? a(M 0 xP( и PR P 3    ~ P C  6    H P 0sTh ? a(i 0 x\( ,и \R \ 3    ~ \ C  6    H \ 0sTh ? a(P 0 x `(  и, `R ` 3    ~ ` C 4 6    H ` 0sTh ? a(R 0 x@d( ,Xt0xev0 dR d 3    ~ d C T 6    H d 0sTh ? a(S 0 x`h(  hR h 3    ~ h C t 6    H h 0sTh ? a(T 0 xl( `и lR l 3    ~ l C  6    H l 0sTh ? a(U 0 xp( и` pR p 3    ~ p C  6    H p 0sTh ? a(4 0 xt(  tR t 3    ~ t C 4 6    H t 0sTh ? a( 0 (  X  C      S X 6    H  0sTh ? a(n 0 .&(     N1 ?     # lߋD[D[ ? =   < '';;OOH  0sTh ? a( 0 (  X  C      S   6    H  0sTh ? a( 0 (  X  C      S  6    H  0sTh ? a( 0 (  X  C      S  6    H  0sTh ? a( 0 0(  X  C      S T 6    H  0sTh ? a(rp`xFn͈ ]A0]͛] wǯWn%E)[q224 X7@, "%<j'@ 2)*D0,.R0P`235r7:9;Xp<>Z@"BCEzG`c ѓBIf iK$ M`O4QSpTtb Oh+'0   @ L X dpxSQL Tuning for DBAstuningi Tim Gormanfim im im  Tim Gormanf23 Microsoft PowerPoint 4.0i@_@Fg/s@R@ M ⟾ GD oM  ?& &&#TNPPp0D v & TNPP &&TNPP    - "-- !-- "-&G& - &Gy& BComic Sans MS- .92 A!http://www.evergreen-database.com     .&{-(- $||--&-- -- "Arial0- . 2  1.--yH-- Times New Roman- .2 SQL Tuning for $.''   . . 2 gDBAs.+..--ay`-- BComic Sans MS- .2  Tim Gorman .BComic Sans MS- .2  Principal  .BComic Sans MS- .?2 .%Evergreen Database Technologies, Inc.  .BComic Sans MS- .92 [!http://www.evergreen-database.com         .--"System-&TNPP & ՜.+,D՜.+,<   $,4 < Letter Paper (8.5x11 in)e&Evergreen Database Technologies, Inc.InxV%  ,Times New RomanArialComic Sans MS Courier New Courier (W1)Courier PowerPointSQL Tuning for DBAsAgenda It could be: It could be:&...95% of the time, its bad SQL...-Tune SQL first, then consider other areas...So how do I do it?O/S utilities V$SQLAREA3Detecting expensive SQL statements using V$SQLAREA EXPLAIN PLANEXPLAIN PLAN (contd)SQL*Plus and SET AUTOTRACEComparing access methods SQL Tracing SQL TracingTKPROF performance summary!SQL Tracing: enabling/disabling*SQL Tracing: enabling/disabling (contd)SQL Tracing: raw file"SQL Tracing: raw file (excerpt) Using TKPROFTKPROF syntax (contd)sorting TKPROF outputTKPROF outputTKPROF output (contd)TKPROF output (contd)TKPROF output (contd)V$SESSION_WAIT#V$SESSION_EVENT and V$SYSTEM_EVENTSome wait eventsV$SESSION_WAIT example scriptV$SESSION_WAIT example output%Cumulative session SESSION WAIT infoSummaryDocumentationInformation on the Web  Fonts UsedDesign Template Slide Titles% 6> _PID_GUIDAN{1359BFA0-0BA3-11D3-B919-00104BF09D00}"_TV bTim Gorman  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root EntrydO)Current UserSummaryInformation(PowerPoint Document(xVDocumentSummaryInformation8eh@P@