ࡱ> FCDE( "-  @,http://www.oraperf.com&9#parsecpu";#reccpu&=#othercpu?#cpuA #wait*C#latch free$E#enqueue0G#log file sync4I #buffer deadlock>K*#write complete waits8M$#buffer busy waits2Qwww.oraperf.com/ 00DTimes New Roman2v 0( 0DArialNew Roman2v 0( 0" DTahomaew Roman2v 0( 0"0DCourier Newman2v 0( 01c(.  @n?" dd@  @@``_@,WHOOSH.WAV.WAV 10103RIFFWAVEfmt ++data~~~~~~~~~~~~~~~~~~~~~~~~~~~|||~~~~~zvtvxz|~zvrnlrv||vtrpptz~|xvtv|~~zxvvvz|~xrlhntzzvrrpprrx~|j[QU_|bICCYn[ICY~zlnh]_r|]SUSSjz|x__l~v_]drnUb|nfd_]d]5=rrnj]jz~lMldrx[[_f|YQfWK_xh[zx[CIjzxxdSQz|_fI9WӹM;QnvK;OUx~xj]]YS~ɵlM?plM;CGpٵ[)Kh|% %;xtKQS]ɖbGr|lSnvz~nfS[nëx;Az=+AYݻ|=/1r_ ?|潄W5/Czãf)/pvbMQr|O=OtjdhjlG?G_ɊK3לdQCMppW;1SŷAMz/#?bx͖SAM[hvhYp~~zrvt[]ptWOUtxxd]v~vvxzd_xzh_nrSQltbdp~_lrQ_l][tf]W]~hWfp|fWhnhbU[hzlSCWbYbnxpdhjvjM9Ot]GQ[dpp~hhhdpx~xh]Yntnrp]dlvjQMb|vdjpzxzztljr||_[r~~jb_j|z|xpp~~v]Wh||zx|x|zzndhpxr]Yfntzjl|z||~vppnrzphrxz|~|xtrjjtv~~vz|z||xrrv~xrprtz|||vzxvx|xtx~~~~~~~zxxxx|zxz~~~rrz~|xz~~|vtx|zxz|~~~||zx|||~~zz|zz~~~~|~~||~~||zz|~||z~~~~zxzz|~~~~|~~~|~||~~~~~~~~~~~~~|||~~~~~~~~~~~~~||~~||~||~~~~|||~~~~||||~~~~~~||||~~~~~~~~~~~|~~~~|zz~~~~~~~||||~~||~~~~~|||~~~~~|||~|~|~~~~|z|~~~~~~~|||~~~~~~~~||~~~~~~~~ f^H(*<  ;      &-)l485(679:    #$% 3./02<"'*g+,1      A@ A1  8c8c     ?1 d0u0@Ty2 NP'p<'p@A)BCD|E?@845, g4CdCdv 0ppp@ <4!d!d` 0L3<4BdBd` 0L3R g4PdPdv 0Dp@ ppuʚ;2Nʚ;<4dddd{ 0:2___PPT9/ 0? O =tyFMANAGING PERFORMANCE IN ORACLE APPLICATIONS RELEASE 11 USING STATSPACK  Introduction Managing performance in any database is an iterative, proactive and reactive exercise DBA perspective on performance tuning User calls  Why is it so slow? Where do you look first? How do you know if it is worth pursuing? What s changed recently? What can help?V&{6  & [nT IntroductionLots of big expensive tools around Need something cheap, easy, and effective STATSPACK is a package included with the Oracle8i RDBMS Not installed automatically Not well understood Not designed specifically for Ora Apps R11 It is the focus our discussion todayj#Z*Z8Z[Z%Z#*8[%aKAgendaTotal Performance Management Typical Oracle Apps R11 environment Choosing STATSPACK Installing and gotchas Reporting Adjusting the package for Oracle Apps R11 Improving the package$cJTotal Performance ManagementbKnow your environment. Based on a description, can you identify the components that are probably involved? Have a Baseline You can t determine what s  abnormal until you know what s  normal And you have to PROVE it with factual data! By retaining & evaluating consistent metrics Trends can be identifiedTE,-A  E,- dNTotal Performance ManagementHave a Baseline (cont d) Keep history Start now! Even if you don t evaluate your data daily Beyond the database Use the same time intervals for Network & O/S metrics; allows for easier correlation Keep in mind the end-user s experience.Z Z Z+ZZUZ(Z  + U(.The Typical R11 environmentSeveral areas of complexity exist: 3 Tiered architecture Browser-based end-user interface. Don t forget the Jinitiator! Application Server which includes the Forms and Web servers. Database Server Concurrent Managers#ZZ"ZZNZZ#"N /Choosing STATSPACK"What is right about STATSPACK? Cheap & as in FREE& Portable across all Oracle platforms Written entirely in SQL and PL/SQL Easy to install and use & and de-install and disable, then re-enable, if you wish& Safe Oracle Support uses it to process TARs logged on tuning problemsZZ Z%Z#ZZ:ZFZ %# : F =(Using STATSPACKEvolved from venerable BSTAT/ESTAT BSTAT/ESTAT has been around *forever* Simply displays delta (changes) of statistics in V$ views between two snapshots Report in file  report.txt STATSPACK does the same as BSTAT/ESTAT Takes snapshots of statistics in V$ views But stores the information in tables Report is much more informative than  report.txt But curiously as difficult to use as  report.txt !#&P'3#1  ' I$3>)Using STATSPACKEverything resides within an account / schema named PERFSTAT Snapshots are scheduled and run using the DBMS_JOB package By default, takes a snapshot every hour & configurable (of course!)& Unlike BSTAT/ESTAT, also captures info from V$SQLAREA Vital tuning information!vx(6= 1(6 ?*Using STATSPACK STATSPACK produces only one report But it is much more useable and comprehensive than BSTAT/ESTAT s report.txt Contains some interpretations and advice Oracle9i contains an extra detailed report for drilling down on the history of a single SQL statement & that s all folks!#L)f# 3 ' *@+Installing STATSPACKUse either the v8.1.7 or v9.0.1 versions of STATSPACK Regardless of version RDBMS you are using& & shouldn t bother with the v8.1.6 version of STATSPACK& No worries! Each new version contains an upgrade script from the previous version& Files in $ORACLE_HOME/rdbms/admin Named  stats*.* in v8.1.6 version& Named  sp*.* in v8.1.7 and v9.0.1 versions& 6Z+Z8ZTZ"ZQZ6+8*   " "QhOInstalling STATSPACK Read online documentation spdoc.txt first! - Overview of STATSPACK - Detailed installation instructions - Gathering data manually or through automated tools - Running a report interactively or via batch - Setting thresholds - Purging data CONNECT INTERNAL in SQL*PlusJ+ A,Installing STATSPACKQRun SQL*Plus script spcreate.sql Calls spcusr.sql to create PERFSTAT Assigns DEFAULT and TEMP tablespaces Prompts for tablespace names Good time to think about sizing performs GRANTS create session, alter session create table, create procedure, create sequence create public synonym, drop public synonym Adds new X$ tables to SYS schema!Z$Z%Z=ZZzZ!Z    %=  z  !  iPInstalling STATSPACKRun SQL*Plus script spcreate.sql (cont d) Re-connects as PERFSTAT to continue Calls spctab.sql to create tables, sequences, synonyms NOTE: indexes are created in DEFAULT tablespace Might have to re-create them in a more desirable tablespace Calls spcpkg.sql to create package STATSPACK Creates public synonyms for package*$71<-$  $  '1  < $B-Installing STATSPACKhCheck spooled output files for errors $ grep  i err spc*.lis If you have any errors& Correct them De-install and start over again CONNECT INTERNAL in SQL*Plus Run SQL*Plus script spdrop.sql Calls scripts spdtab.sql and spdusr.sql Check spooled output files spd*.lis for errors! Then repeat installation steps&-<X&-1             C.Installing STATSPACKAutomatic job submission CONNECT PERFSTAT in SQL*Plus Run SQL*Plus script spauto.sql Configures STATSPACK.SNAP once an hour Edit the script if you d like a different frequency Purging data CONNECT PERFSTAT in SQL*Plus Run SQL*Plus script sppurge.sql Purges a range of snap-ids Gathering hourly, purging every 3 days retains ~300Mb in source database.Z<Z'Z4Z Z=ZeZ1 '4 1  JD/ Special tasksClearing out the STATSPACK tables CONNECT PERFSTAT in SQL*Plus Run SQL*Plus script sptrunc.sql Exporting the PERFSTAT schema Perhaps to send it to Oracle Support? Use parameter file spuexp.par $ exp parfile=spuexp.par Password:"=D#"1 9 "   E0Reporting from STATSPACKCONNECT PERFSTAT in SQL*Plus Run SQL*Plus script spreport.sql Will either prompt interactively for parameter values & OR& Can be run in batch mode when parameter values are supplied as SQL*Plus substitution variablesn>6_1  )KF1Reporting from STATSPACKSQL> connect perfstat/perfstat Connected. SQL> @spreport DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 2618106428 PRD1 1 prd1 Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment ------------ ------------ ----- ----------------- ----- prd1 PRD1 1 11 May 2001 12:07 5 2 11 May 2001 12:08 5ZG2Reporting from STATSPACKSpecify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1 Begin Snapshot Id specified: 1 Enter value for end_snap: 2 End Snapshot Id specified: 2 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_1_2 To use this name, hit to continue, otherwise enter an alternative. Enter value for report_name: Using the report name sp_1_2$ZH3Reporting from STATSPACKRunning from batch mode: SQL> connect perfstat Enter password: Connected SQL> define begin_snap=1 SQL> define end_snap=2 SQL> define report_name=sp_1_2 SQL> @spreportT I4Reporting from STATSPACK"Cache Sizes ~~~~~~~~~~~ db_block_buffers: 40960000 log_buffer: 1048576 db_block_size: 8192 shared_pool_size: 104857600 Load Profile Per Second Per Transaction --------------- --------------- Redo size: 54,378.06 23,189.73 Logical reads: 128,947.97 54,990.36 Block changes: 239.97 102.33 Physical reads: 2,384.38 1,016.83 Physical writes: 25.07 10.69 User calls: 182.21 77.71#Z#v\Reporting from STATSPACKParses: 29.49 12.58 Hard parses: 0.35 0.15 Sorts: 37.63 16.05 Logons: 0.22 0.09 Executes: 180.99 77.18 Transactions: 2.34 % Blocks changed per Read: 0.19 Recursive Call %: 57.54 Rollback per transaction %: 5.65 Rows per Sort: 68.35kQReporting from STATSPACK!Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 99.98 Redo NoWait %: 100.00 Buffer Hit %: 98.15 In-memory Sort %: 99.97 Library Hit %: 99.89 Soft Parse %: 98.80 Execute to Parse %: 83.71 Latch Hit %: 99.98 Parse CPU to Parse Elapsed %: 77.98 % Non-Parse CPU: 100.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 74.94 71.95 % SQL with executions>1: 80.60 80.55 % Memory for SQL w/exec>1: 83.33 83.55"Z"lRReporting from STATSPACKTop 5 Wait Events Event Waits Time (cs) Wt Time -------------------- ------------ ------------ ------- enqueue 24,345 7,194,663 49.50 db file sequential r 40,848,049 3,490,281 24.01 db file scattered re 3,175,741 2,320,214 15.96 SQL*Net message from 277,128 968,395 6.66 latch free 164,340 155,845 1.07mSReporting from STATSPACKz Buffer Gets Execs Gets/Exec % Total Hash Value -------------- ------- ------------ ------- ----------- 1,537,435,479 90 17,082,616.4 33.2 1394818125 select a.report_id, decode(a.batch_id,null, b.batch_id, a.batch_id) rbatch_id, c.NAME, c.NUMBER, a.NAME, b.BANK_NAME, c.ACCOUNT_NAME,a.RECEIPT_NUMBER, aps. DUE_DATE, decode(acr.selected_remittance_batch_id,null {zJ5 YAPP ReportsYet Another Performance Profiler Available from http://www.oraperf.com Unofficial website run by members of Oracle Server Technologies division Upload either BSTAT/ESTAT report.txt file or STATSPACK report file Returns an HTML page containing an amazing response-time analysis report!'I 'I I!00Fw] YAPP Reports5Organized from overview to hyperlinked detail sections Header (version info, time span of report, etc) Response-time breakout CPU Time or time spent processing SQL Parse, recursive, and other CPU Time breakouts Wait Time or time not spent processing SQL Initialization parameter settings Tuning advise summary7ZGZ&Z/Z+Z8Z G/   " 8WB YAPP ReportsGeneral Information The following comments were generated while processing file C:\Temp\sp_5349_5350.lst: Disclaimer: Use information at own risk ! All timing information is in 1/100 sec, unless stated otherwise. The timing period in this report is too long to get any useful tuning advise. End Buffer Gets Threshold: 100000 Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total 10021160010 exceed 100 End Executions Threshold: 1000 only latches with sleeps are shown ordered by name, sleeps desc NoWait Waiter Please be advised that running STATSNAP on releases before Oracle8i can give problems. Please be advised that Oracle8 version 8.0.6 is the terminal release for Oracle8. You are on an older release. Uploaded 167060 bytes in 5.10 secondsrZZW M8 YAPP Reports x^ YAPP Reports y_ YAPP Reports z` YAPP Reports L7Customizing STATPACKIHow should STATSPACK be altered for Ora Apps R11/R11i? Capture OraApps Concurrent Manager info Display MODULE (from V$SESSION and V$SQLAREA) in reports For OraApps and other applications using the DBMS_APPLICATION_INFO package Increase thresholds when data gathering filter out huge amounts of unnecessary data Consume less space7ZaZKZ(Z?Z7?K  ? oVCapture ConcMgr datamTable FND_CONCURRENT_REQUESTS in Ora Apps foundation schema APPLSYS Contains valuable elapsed time information for batch jobs and reports Only elapsed time information from interactive forms is missing in Ora Apps Leaving a gap in the record of the total end-user experience It is a standard best-practice in Ora Apps administration to purge this table periodicallyDZZ=Z[ZD   =<pWCapture ConcMgr data-Customization Suggestion Replicate data to the back-end reporting and analysis database Retain ConcMgr job statistics for several business cycles Allowing analysis of ConcMgr activity Allows frequent purging of FND_CONCURRENT_REQUESTS table in Ora Apps foundation schema Optimizing ConcMgr performance?:&W  :&W qXDisplay MODULE in reportssStandard v8.1.6, v8.17, and v9.0.1 STATSPACK script spreport.sql does not include value of column MODULE (i.e. Ora Apps executable name) Helps in associating a poorly-performing SQL statement to a form, report, or program Metalink note #153502.1 fixes v8.1.7 spreport.sql Metalink note #153503.1 fixes v8.1.6 statsrep.sql Metalink note #153505.1 fixes v9.0.1 sprepins.sqlZUZ2ZdZ4 IU% %     &       rYIncrease thresholds=When capturing SQL statement tuning information from V$SQLAREA STATSPACK installed by default has very low statistics thresholds for selecting SQL statements Gathers info about rather unimportant SQL statements Advisable to set them higher for Ora Apps Reduces the amount of storage consumed by STATSPACK dramatically?_5*A?(    4   sZIncrease thresholds+Metalink note #153507.1 contains good suggested thresholds But does not suggest a good method for setting the parameter Instead of adding new thresholds as parameters to STATSPACK.SNAP procedure call Update the table STATS$STATSPACK_PARAMETER with the threshold values to make the settings permanenth;=Pd;=PZ       tUCustomizing STATSPACKHow can STATSPACK be improved? Automated ANALYZE of PERFSTAT Automated purge of PERFSTAT tables More data warehouse-like architecture Replicate data to a decision-support database More reporting capabilities More reports Ability to perform ad-hoc queriesg./F     N9Automated ANALYZEdJust like anything else, the PERFSTAT schema should be analyzed periodically To optimize the performance of report processing and other queries in PERFSTAT Performance of STATSPACK.SNAP procedure not affected Only SELECTs from V$ views and INSERTs into PERFSTAT schema Customization Suggestion: Run DBMS_STATS.GATHER_SCHEMA_STATS using the DBMS_JOB systemsMZOZ5Z<ZZ>Z7  O5<  >O:Automated purgeNSQL*Plus script sppurge.sql cannot be run from automated DBMS_JOB system Must run from an external job-scheduling facility UNIX cron, NT/2000 at, CA AutoSys or Unicenter, etc& Customization Suggestion: Rewrite the sppurge.sql script as a PL/SQL stored procedure Called from DBMS_JOB once per dayI25<" .2    $"VAMore DW-like architecture=Reporting/analysis systems (DWs) are usually separated from operational systems STATSPACK data should be replicated to another system for reporting / analysis Customization Suggestion: Leave standard STATSPACK on database being monitored Build custom replication package to customized STATSPACK on a back-end databasetPO5PPO5>   XCMore DW-like architectureCustomization Suggestion (cont d) Purge data in PERFSTAT schema in front-end database (the one being monitored) to minimize the impact of storage Allow PERFSTAT schema in back-end database to retain more data for long-term analysis (i.e. trending, summaries, anomalies) Best to retain performance data across numerous distinct business cyclesb"I"! _[IYDMore DW-like architectureRCustomization Suggestion (cont d) Store data from multiple front-end databases in a single back-end database Standard STATSPACK labels all data by DBID and INSTANCE_NUMBER anyway The standard STATSPACK installation in the front-end database is not altered in any way Except by frequent data purges"KFX"  F+ $ P;More reporting capabilitiesXStandard STATSPACK only captures snapshots of cumulative statistics So each row has the current total value Script spreport.sql has logic to calculate deltas or changes in value between two snapshots But this type of data prevents most reporting and analysis Cannot summarize Cannot analyze across instance restarts All statistics reset to zeroDZZ;Z9ZZ! / ;9 ZEMore reporting capabilities2Customization Suggestion In the back-end (reporting and analysis) database, add and populate new columns for holding delta values between snapshots For example: ALTER TABLE STATS$SYSSTAT ADD (VALUE_INC NUMBER); Does not affect standard STATPACK installation in front-end database that is being monitored{ 5]M  5  2 ![FMore reporting capabilitiesAvailability of delta data values allows simple queries across all snapshots: select trunc(s.snap_time), sum(f.phyrds_inc) from stats$snapshot s, stats$filestatxs f where s.snap_id = f.snap_id group by trunc(s.snap_time)@OZZ9 Conclusion~Cannot improve that which you don t measure! STATSPACK is a cheap tool for gathering measurement metrics Still needs some improvements but even out-of-the-box it is an excellent start! Use the YAPP report from www.oraperf.com Better than the standard report High-Performance Tuning with STATSPACK by Donald K. Burleson-Z<ZZ2Z*Z Z=Z <      * &Q!0u[Q&A /<`egjPB ` ___f3f` ___3f` 999MMM>?" dd@,|?" dd@   " @ `"  n?" dd@   @@``@n?" dd@  @@``PR    @ ` ` p>>     ( $ pF  q0a  q 0a2  C BUCENG HZI8Qf3? جU,جU,8`T,8`T q0a2  C BUCENG HZIDQD? U8U8D`T8D`T 2  C BUCENG HZIEQ[? U9U9E`T9E`T dn"   G/*d6?@33@@@sf 4  Zugֳgֳ ?  T Click to edit Master title style! !:  Txgֳgֳ ?0  RClick to edit Master text styles Second level Third level Fourth level Fifth level!     S    `x}gֳgֳ ?`  Z*    `gֳgֳ ?   \*    ` gֳgֳ ?   \*H  0޽h? ??0 ___f3f Fireball   k c @  ( p0p0 pF  0    0 2  C BUCENG HZI8Qf3? جU,جU,8`T,8`T 0 2  C BUCENG HZIDQD? U8U8D`T8D`T 9Y 2  C BUCENG HZIEQ[? U9U9E`T9E`T "   G/*d6?@33@@@sf   Z(gֳgֳ ?`  T Click to edit Master title style! !  TH+gֳgֳ ?0 `    W#Click to edit Master subtitle style$ $    `gֳgֳ ?`  Z*    `,1gֳgֳ ?   \*    `l5gֳgֳ ?   \*H  0޽h? ??0 ___f3f*0 ` (    <\ ??P    `*  ^   S  ?  "   < ?? @  RClick to edit Master text styles Second level Third level Fourth level Fifth level!     S   <̗ ??    b*(    B ??`P   `*     B| ??`   b*( B  s *޽h ? ̙33 aYP(    <b ??P    OTim Gorman & Mary Crystal    <q ??    `*(   Bu ??`P   |FMANAGING PERFORMANCE IN ORACLE APPLICATIONS RELEASE 11 USING STATSPACKG G   By ??`   `*( B  s *޽h ? ̙33 A 0 /(  r  S p?@`  }   Z?gֳgֳ ? @` Rocky Mountain Oracle Users Group 20 February, 2002 Tim Gorman - SageLogix, Inc. Mary Crystal - Echostar Communications Corporation R5  'B  s *޽h ?    p *(  r   S Lj   x   c $Ȉ?  B  s *޽h ?   d$(  dr d S     r d S 0  H d 0޽h ? ___f3f  (  l  C Έ   l  C x0  H  0޽h ? ___f3f   p$(  pr p S    r p S h  B p s *޽h ?   (  l  C ا   l  C `   H  0޽h ? ___f3f(   P(  r  S .     S X/0<$ 0  H  0޽h ?    $( H# r  S 85   r  S 5   H  0޽h ? ___̙3f   $(   r   S    r   S T?   H   0޽h ? ___̙3f   $(  r  S D   r  S @   H  0޽h ? ___̙3f  0$(  r  S 0E   r  S D&0  H  0޽h ? ___̙3f  @ $(   r   S  ,   r   S ,P  H   0޽h ? ___f3f  `(  l  C    l  C 0  H  0޽h ? ___f3f  p($(  (r ( S    r ( S P  H ( 0޽h ? ___f3f  (  l  C 8   l  C   H  0޽h ? ___f3f  0$(  0r 0 S X   r 0 S    H 0 0޽h ? ___f3f  4$(  4r 4 S    r 4 S HP  H 4 0޽h ? ___f3f  8$(  8r 8 S    r 8 S d  H 8 0޽h ? ___f3f  <$(  <r < S    r < S 0  H < 0޽h ? ___̙3f  D$(  Dr D S X   r D S    H D 0޽h ? ___̙3f  L$(  Lr L S HŮ   r L S Ʈ  H L 0޽h ? ___̙3f  P$(  Pr P S ,Ю   r P S (ۮ0  H P 0޽h ? ___̙3f  T$(  Tr T S    r T S `P0  H T 0޽h ? ___̙3f   $(  r  S    r  S \@`  H  0޽h ? ___̙3f  0$(  $l $ C    l $ C @ `P  H $ 0޽h ? ___̙3f  @,( -@ ,l , C    l , C L  H , 0޽h ? ___̙3f  P4(  4l 4 C    l 4 C D` P  H 4 0޽h ? ___̙3f  `X$(  Xr X S +   r X S -0  H X 0޽h ? ___̙3f  p0(  x  c $   x  c $0  H  0޽h ? ___̙3f  $(  r  S T2   r  S 3`PP  H  0޽h ? ___̙3fq0  !00Pkl/(  lr l S 8O    l  `1??z l # l,Q1?? G@ G Response Time   l # l$1?? 1 ,F  kl   +@   il r@  @ :l @ !l  `1?? @ 9l Z ?? @@  @ l  @ #l 3 r61??  @ H Percentage"   =l Z ??  @@  @ @l @ $l 3 ra1?? @ I Per Execute"   ?l Z ?? @@  @ Bl @ %l 3 rX1?? @ K Per User Call"  Al Z ?? @@  @ Dl @ &l 3 r\ 1?? @ NPer Transactions" Cl Z ?? @@ @` Fl@` 'l 3 r1??@` ? Response Time El Z ??@`@ @` Hl@` (l 3 r1??@` :20985219   Gl Z ??@`@ @ ` Jl@ ` )l 3 rp1??@ ` 9100.00% Il Z ??@ `@  @` Ll @` *l 3 rts1?? @` 62.92 Kl Z ?? @`@ @` Nl@` +l 3 rv1??@` 64.81 Ml Z ??@`@ @` Pl@` ,l 3 rdz1??@` 8544.72 Ol Z ??@`@ ` Rl` -l 3 r'1??` jCPU Time  ?!0 Ql Z ??`@ ` Tl` .l 3 r1??` :20367938   Sl Z ??`@ `  Vl`  /l 3 rl1??`  897.06% Ul Z ??` @  ` Xl ` 0l 3 r1?? ` 62.83 Wl Z ?? `@ ` Zl` 1l 3 r 1??` 64.67 Yl Z ??`@ ` \l` 2l 3 rԏ1??` 8528.69 [l Z ??`@  ^l 3l 3 rH1?? k Wait Time  A!0  ]l Z ??@  `l 4l 3 r1?? 8617281 _l Z ??@   bl  5l 3 r1??  72.94% al Z ?? @   dl  6l 3 rH1??  60.09 cl Z ?? @  fl 7l 3 r1?? 60.14 el Z ??@  hl 8l 3 r1?? 716.02 gl Z ?? jl T ??H l 0޽h ? ___̙3f?=  <<ef<(  r  S       `1??22  # l1??0@4P BCPU Time   # l1??Rr  7F E c  p6@  B a Br@  @ & @   `1?? @ % Z ?? @@  @ ( @  3 r1?? @ BTime" ' Z ?? @@   @ *  @   3 rl1??  @ H Percentage"   ) Z ??  @@  @ , @   3 rĿ1?? @ I Per Execute"   + Z ?? @@  @ . @   3 r1?? @ K Per User Call"  - Z ?? @@  B@ 0 B@   3 r|1?? B@ MPer Transaction" / Z ?? B@@ @` 2@`   3 r\1??@` 7Total 1 Z ??@`@ @` 4@`  3 r$1??@` :20367938   3 Z ??@`@ @ ` 6@ `  3 r@1??@ ` 9100.00% 5 Z ??@ `@  @` 8 @`  3 r1?? @` 62.83 7 Z ?? @`@ @` :@`  3 r81??@` 64.67 9 Z ??@`@ @B` <@B`  3 r1??@B` 8528.69 ; Z ??@B`@ ` >`  3 r1??` k Parse CPU  9!0  = Z ??`@ ` @`  3 r1??` 746637 ? Z ??`@ `  B`   3 r1??`  70.23% A Z ??` @  ` D `  3 r(1?? ` 60.01 C Z ?? `@ ` F`  3 r1??` 60.01 E Z ??`@ `B H`B  3 r1??`B 61.21 G Z ??`B@  J  3 r1?? o Recursive CPU;!0  I Z ??@  L  3 rH1?? 91184109 K Z ??@   N   3 r1??  75.81% M Z ?? @   P   3 r81??  60.16 O Z ?? @  R  3 r 1?? 60.27 Q Z ??@ B TB  3 r(1??B 730.74 S Z ??B@  V  3 r1?? k Other CPU  =!0  U Z ??@  X   3 r1?? :19137192   W Z ??@   Z  ! 3 r1??  893.96% Y Z ?? @   \  " 3 r1??  62.66 [ Z ?? @  ^ # 3 rx!1?? 64.39 ] Z ??@ B `B $ 3 r%1??B 8496.75 _ Z ??B b T ??E d  `1??   e  `1??   f # lp)1??   ; H  0޽h ? ___̙3fQ  nQfQP(  r  S +      `1??P  # l01??    C Wait Time    # l51??P 1 NF    z`M@      @  @ 2 @  3 r91?? @ CEvent" 1 Z ?? @@  e @ 4 e @  3 r=1?? e @ BTime" 3 Z ?? e @@ e ) @ 6e ) @   3 r@ :v >@   3 rXK1??v >@ K Per User Call"  9 Z ??v >@@ > @ <> @   3 rP1??> @ MPer Transaction" ; Z ??> @@ @` >@`   3 r\T1??@` j latch free C!0  = Z ??@`@ @e ` @@e `  3 rX1??@e ` 8612795 ? Z ??@e `@ e @) ` Be @) `  3 r,\1??e @) ` 899.27% A Z ??e @) `@ ) @v` D) @v`  3 r_1??) @v` 60.09 C Z ??) @v`@ v@>` Fv@>`  3 rc1??v@>` 60.14 E Z ??v@>`@ >@` H>@`  3 rf1??>@` 715.91 G Z ??>@`@ ` J`  3 rk1??` genqueueE!0 I Z ??`@ `e  L`e   3 rm1??`e  5153 K Z ??`e @ e `)  Ne `)   3 rq1??e `)  70.02% M Z ??e `) @ ) `v P) `v  3 rDu1??) `v 60.00 O Z ??) `v@ v`> Rv`>  3 ry1??v`> 60.00 Q Z ??v`>@ >` T>`  3 r|1??>` 60.00 S Z ??>`@  V  3 r1?? m log file syncG!0  U Z ??@ e  Xe   3 r1??e  5117 W Z ??e @ e )  Ze )   3 r1??e )  70.02% Y Z ??e ) @ ) v \) v  3 rh1??) v 60.00 [ Z ??) v@ v> ^v>  3 r1??v> 60.00 ] Z ??v>@ > `>  3 rX1??> 60.00 _ Z ??>@  b  3 rĖ1?? obuffer deadlockI!0 a Z ??@ e  de    3 r1??e  437 c Z ??e @ e )  fe )  ! 3 r\1??e )  70.01% e Z ??e ) @ ) v h) v " 3 r1??) v 60.00 g Z ??) v@ v> jv> # 3 r1??v> 60.00 i Z ??v>@ > l> $ 3 rL1??> 60.00 k Z ??>@  n % 3 r1?? twrite complete waitsK!0 m Z ??@ e  pe  & 3 rH1??e  35 o Z ??e @ e )  re )  ' 3 r1??e )  70.00% q Z ??e ) @ ) v t) v ( 3 r1??) v 60.00 s Z ??) v@ v> vv> ) 3 rX1??v> 60.00 u Z ??v>@ > x> * 3 r1??> 60.00 w Z ??>@   z  + 3 r1??  qbuffer busy waitsM!0 y Z ?? @ e  |e  , 3 r1??e  35 { Z ??e @ e )  ~e )  - 3 r1??e )  70.00% } Z ??e ) @ ) v  ) v  . 3 r1??) v  60.00  Z ??) v @ v>  v>  / 3 r 1??v>  60.00  Z ??v> @ >  >  0 3 r1??>  60.00  Z ??>   T ?? H  0޽h ? ___̙3f  }+0(     `1??'    `1??e!F ?>,i - @P@ J3,^ +J3,^@ Jj Jj  3 r1??Jj G MaxGain %"    Z ??Jj@ Jj Jj  3 rH1??Jj BWhat"  Z ??Jj@ J3,j J3,j   3 r1??J3,j DDetail"  Z ??J3,j@ j8 j8   3 r1??j8 33  Z ??j8@ j8 j8   3 rl1??j8 ^.Reduce the number of buffer gets or executions//  Z ??j8@ j3,8 j3,8-   3 r@1??j3,8 SCheck SQL statement "SELECT /*+ choose */DS.SHIPMENT_ID SHIPMENT_ID,DPA_SHIPPING_PRO" (hash value 370972535). Check SQL statement "SELECT /*+ choose */DS.SHIPMENT_ID,DS.DELIVERY_ID,DT.DEPARTURE_ID FROM DPA_SHIPMENTS DS,DPA_TRUCKS DT WHERE DS.TRUCK_ID = DT" (hash value 73620072). Check SQL statement "SELECT ROW_ID,HEADER_ID,CUSTOMER_NAME,TT  Z ??j3,8@ 8X  8X   3 rp1??8X 32  Z ??8X@ 8X "8X   3 r1??8X bTune the cache buffers chain*  ! Z ??8X@ 83,X $83,X  3 r(1??83,X X(No detailed information is available yet)) # Z ??83,X@ X^ &X^  3 r 1??X^ 31 % Z ??X^@ X^ (X^   3 r1??X^ bReduce data block contention*   ' Z ??X^@ X3,^ *X3,^"  3 rd1??X3,^ xHCheck the objects that are inserted into that they have enough freelistsII ) Z ??X3,^ , Z ???>,i 0  fgֳgֳ ?   H  0޽h ? ___̙3f  h$(  hr h S `   r h S    H h 0޽h ? ___f3f  l0(  lx l c $d   x l c $ P  H l 0޽h ? ___f3f  p0(  px p c $/   x p c $L0P  H p 0޽h ? ___f3f  t0(  tx t c $A   x t c $\BP  H t 0޽h ? ___f3f  x0(  xx x c $P   x x c $Q  H x 0޽h ? ___f3f   |0(  |x | c $'   x | c $dS0  H | 0޽h ? ___f3f  0h0(  hx h c $,F   x h c $\HP  H h 0޽h ? ___f3f  @t$(  tr t S c   r t S cP  H t 0޽h ? ___f3f   x$(  xr x S <    r x S    H x 0޽h ? ___f3f  P0(  x  c $c   x  c $hdP  H  0޽h ? ___̙3f  `$(  r  S n   r  S o   H  0޽h ? ___̙3f  p$(  r  S }   r  S t~`P  H  0޽h ? ___̙3f  |$(  |r | S 8   r | S   H | 0޽h ? ___̙3f  $(  r  S    r  S |  H  0޽h ? ___̙3f  $(  r  S P   r  S ܉0  H  0޽h ? ___̙3f   L*(  Lr L S    x L c $\?  B L s *޽h ? ___̙3fj  (  r  S h   H  0޽h ? ___f3f0 P(  X  C       S ԯ  @    H  0޽h ? ̙330 x(  R  3     ~  C   @    H  0޽h ? ̙330 x(   R  3     ~