Statspack Scripts: spauto.sql - Schedule a dbms_job to automate the collection of Statspack statistics. spcreate.sql - Installs the Statspack user, tables and package on a database (Run as SYS). spdrop.sql - Deinstall Statspack from database (Run as SYS). sppurge.sql - Deletes snapshots which fall between the begin and end range of Snapshot Id's specified. spuexp.par - Export parameter file i.e. exp userid=perfstat/perfstat parfile=spuexp.par. spreport.sql - Report on differences between values recorded in two snapshots. sptrunc.sql - Removes ALL data from Statspack tables. SQL> CONNECT / AS SYSDBA SQL> define default_tablespace='TOOLS' SQL> define temporary_tablespace='TEMP' SQL> define perfstat_password='my_perfstat_password' SQL> @?/rdbms/admin/spcreate The spcreate.sql install script automatically calls 3 other scripts needed: spcusr - creates the user and grants privileges spctab - creates the tables spcpkg - creates the package -- Drop Statspack -- Reverse of spcreate.sql -- -- @?/rdbms/admin/spdrop.sql Using Statspack (gathering data): To set up automatic collection of data every hour: cd $ORACLE_HOME/rdbms/admin sqlplus perfstat/@spauto.sql sqlplus perfstat -- -- Take a performance snapshot -- execute statspack.snap; or Using an anonymous PL/SQL block, begin perfstat.statspack.snap(i_snap_level => 6); end; / or SQL> variable snap number; SQL> begin :snap := statspack.snap; end; 2 / PL/SQL procedure successfully completed. SQL> print snap SNAP ---------- 12 Different snapshot levels determine data captured: Level = 0 General performance statistics (8i,9i) Level = 5 SQL Statements (default) (8i,9i) Level = 6 SQL Plans (9i) Level = 7 Segment statistics (9.2) Level = 10 Parent and Child latches (8i,9i) -- -- Get a list of snapshots -- column snap_time format a21 select snap_id,to_char(snap_time,'MON dd, yyyy hh24:mm:ss') snap_time from sp$snapshot; -- NOTE: To include important timing information set the init.ora parameter timed_statistics to true. Configuring Statspack: It is possible to change the amount of information gathered by the package, by specifying a different snapshot "level". Snapshot level and threshold information used by the package is stored in the sp$statspack_parameter table. Taking a snapshot, and specifying the new defaults to be saved to the database (using statspack.snap, and using the i_modify_parameter input variable). exec statspack.snap (i_snap_level=>5, i_modify_parameter=>'true'); Changing the defaults immediately without taking a snapshot, using the statspack.modify_statspack_parameter procedure. exec statspack.modify_statspack_parameter(i_snap_level=>5, i_buffer_gets_th=>10000, i_disk_reads_th=>1000); Note: This procedure changes the values permananently, but does not take a snapshot. If you would like to save session statistics for a particular session, it is possible to specify the session id in the call to Statspack. The statistics gathered for the session will include session statistics, session events and lock activity. The default behaviour is to not to gather session level statistics. exec statspack.snap(i_session_id=>3); Parameters that can be passed to the STATSPACK.SNAP and STATSPACK.MODIFY_STATSPACK_PARAMETER procedures are as follows: Table 21-1 Parameters for SNAP and MODIFY_STATSPACK_PARAMETER Procedures --------------------------------------------------------------------------------------------------------------------------- Parameter Name RangeOfValidValues DefaultValue Meaning --------------------------------------------------------------------------------------------------------------------------- i_snap_level 0, 5, 6, 7, 10 5 Snapshot level i_ucomment Text Blank Comment to be stored with snapshot i_executions_th Integer >=0 100 SQL threshold: number of times statement was executed i_disk_reads_th Integer >=0 1000 SQL threshold: number of disk reads the statement made i_parse_calls_th Integer >=0 1000 SQL threshold: number of parse calls the statement made i_buffer_gets_th Integer >=0 10000 SQL threshold: number of buffer gets the statement made i_sharable_mem_th Integer >=0 1048576 SQL threshold: amount of sharable memory i_version_count_th Integer >=0 20 SQL threshold: number of versions of a SQL statement i_seg_phy_reads_th Integer >=0 1000 Segment statistic threshold: number of physical reads on a segment i_seg_log_reads_th Integer >=0 10000 Segment statistic threshold: number of logical reads on a segment i_seg_buff_busy_th Integer >=0 100 Segment statistic threshold: number of buffer busy waits for a segment i_seg_rowlock_w_th Integer >=0 100 Segment statistic threshold: number of row lock waits for a segment i_seg_itl_waits_th Integer >=0 100 Segment statistic threshold: number of ITL waits for a segment i_seg_cr_bks_sd_th Integer >=0 1000 Segment statistic threshold: number of consistent reads blocks served by the instance for the segment (RAC) i_seg_cu_bks_sd_th Integer >=0 1000 Segment statistic threshold: number of current blocks served by the instance for the segment (RAC) i_session_id Valid SID from 0(no session) Session ID of the Oracle session for which to capture session granular V$SESSION statistics i_modify_parameter TRUE, FALSE FALSE Determines whether the parameters --------------------------------------------------------------------------------------------------------------------------- Running a Performance report: sqlplus perfstat -- -- Statspack Report -- -- You will be prompted for: -- a) The beginning snapshot Id -- b) The ending snapshot Id -- c) The name of the report text file to be created -- -- The report will then calculate and print ratios, -- increases etc. for all statistics between the two snapshot periods. -- Get a list of snapshots to report on: column snap_time format a21 select snap_id , to_char(snap_time,'MON dd, yyyy hh24:mm:ss') snap_time from sp$snapshot; SQL> connect perfstat/my_perfstat_password SQL> define begin_snap=1 SQL> define end_snap=2 SQL> define report_name=batch_run SQL> @?/rdbms/admin/spreport -------------------------------------------------------------------------- Context 0 Cache Sizes 0 Load Profile 0 Instance Efficiency 0 Timed/Wait Events (renamed now includes CPU time) 0 SQL (Buffer Gets/Disk Reads/Executions/Parses) 5 Instance Statistics 0 Tablespace and Datafile IO 0 Buffer Pool Statistics 0 Rollback Activity 0 Latch Statistics 0,10 Segment Statistics (introduced in 9.2) 7 Library Cache Statistics 0 SGA Pool Breakdown 0 Instance Parameters 0 -------------------------------------------------------------------------- To collect optimizer statistics on the PERFSTAT schema, use DBMS_STATS or DBMS_UTILITY, and specify the PERFSTAT user. For example: EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE); or EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT','COMPUTE'); -------------------------------------------------------------------------- To remove statistics collected by stats pack. SQL> CONNECT perfstat/my_perfstat_password SQL> DEFINE losnapid=1 SQL> DEFINE hisnapid=2 SQL> @?/rdbms/admin/sppurge -------------------------------------------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~ SAMPLE STATSPACK REPORT ~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~ STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ ORATOOLS 3059401611 oratools 1 9.2.0.1.0 NO DELPHI Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 1 31-Dec-03 16:03:24 11 6.0 End Snap: 3 31-Dec-03 17:18:13 11 10.5 Elapsed: 74.82 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 16M Std Block Size: 8K Shared Pool Size: 48M Log Buffer: 512K Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shared Pool Statistics Begin End Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- control file sequential read 684 9 42.70 control file parallel write 1,453 4 20.95 CPU time 3 14.37 db file sequential read 179 1 6.78 log file parallel write 2,596 1 6.43 ------------------------------------------------------------- Wait Events for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file sequential read 684 0 9 13 40.2 control file parallel write 1,453 0 4 3 85.5 db file sequential read 179 0 1 8 10.5 log file parallel write 2,596 2,455 1 1 152.7 latch free 67 0 1 14 3.9 db file scattered read 99 0 0 5 5.8 db file parallel write 256 128 0 1 15.1 BFILE get path object 81 0 0 0 4.8 log file sync 10 0 0 2 0.6 LGWR wait for redo copy 12 0 0 1 0.7 SQL*Net break/reset to clien 10 0 0 1 0.6 SQL*Net more data to client 4 0 0 1 0.2 SQL*Net message from client 154 0 4,922 31961 9.1 virtual circuit status 149 149 4,448 29853 8.8 wakeup time manager 142 142 4,350 30634 8.4 jobq slave wait 456 438 1,394 3056 26.8 SQL*Net more data from clien 24 0 0 0 1.4 SQL*Net message to client 154 0 0 0 9.1 ------------------------------------------------------------- Background Wait Events for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file sequential read 580 0 8 14 34.1 control file parallel write 1,453 0 4 3 85.5 log file parallel write 2,596 2,455 1 1 152.7 latch free 65 0 1 14 3.8 db file scattered read 99 0 0 5 5.8 db file parallel write 256 128 0 1 15.1 db file sequential read 60 0 0 2 3.5 LGWR wait for redo copy 12 0 0 1 0.7 rdbms ipc message 10,284 7,845 21,931 2132 604.9 smon timer 14 14 3,998 ###### 0.8 ------------------------------------------------------------- SQL ordered by Gets for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Buffer Gets Threshold: 10000 -> 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 % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 103,892 142 731.6 83.3 9.71 10.96 238087931 ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 35,865 5,112 7.0 28.7 3.29 3.22 3460529092 affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 11,800 1 11,800.0 9.5 1.61 14.67 471420503 1,756 871 2.0 1.4 0.01 0.26 2963598673 ext_date) and (next_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job 864 864 1.0 0.7 0.00 0.08 2964743345 ext_date < (sysdate+5/86400)) 769 1 769.0 0.6 0.02 0.34 1212505517 user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bit and(i.flags, 512) = 512) and (not((i.type# = 9) and bi tand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# 769 1 769.0 0.6 0.05 0.28 4218865043 sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) and o.obj#=t.obj# and o.owner# = u.user# 426 142 3.0 0.3 0.02 0.02 3048181047 426 142 3.0 0.3 0.03 0.05 3188984641 e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corr id, time_manager_info from SYSTEM.DEF$_AQCALL where time _manager_info <= :1 and state != :2 for update skip locked 426 142 3.0 0.3 0.01 0.04 3223513021 426 142 3.0 0.3 0.02 0.05 3719012396 e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corr id, time_manager_info from SYSTEM.DEF$_AQERROR where tim e_manager_info <= :1 and state != :2 for update skip locked 364 14 26.0 0.3 0.00 0.03 1714733582 re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 SQL ordered by Gets for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Buffer Gets Threshold: 10000 -> 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 % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 224 112 2.0 0.2 0.01 0.01 787810128 estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 142 142 1.0 0.1 0.06 0.06 615028291 msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr iority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protoco l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st 142 142 1.0 0.1 0.07 0.05 1030859119 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from RTR.WB_RT_NOTIFY_QUEUE_TAB where time_manager_info 142 142 1.0 0.1 0.04 0.05 1243459867 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_INQUEUE where time_manager_info <= :1 142 142 1.0 0.1 0.07 0.07 1273533510 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_IN_OAG_Q_TABLE where time_manager_info 142 142 1.0 0.1 0.08 0.05 1899253823 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, ------------------------------------------------------------- rows will be truncated SQL ordered by Reads for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 492 1 492.0 44.4 0.02 0.34 1212505517 user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bit and(i.flags, 512) = 512) and (not((i.type# = 9) and bi tand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# 491 1 491.0 44.3 0.05 0.28 4218865043 sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) and o.obj#=t.obj# and o.owner# = u.user# 264 1 264.0 23.8 1.61 14.67 471420503 3 142 0.0 0.3 9.71 10.96 238087931 ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 3 6 0.5 0.3 0.00 0.03 931956286 )from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co l#,0) order by grantee# 2 2 1.0 0.2 0.00 0.01 2385919346 e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh 2 1 2.0 0.2 0.00 0.02 2918884618 2 2 1.0 0.2 0.00 0.02 4049165760 1 2 0.5 0.1 0.00 0.01 1819073277 obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh ere d_obj#=:1 and p_obj#=obj#(+) order by order# 1 5,112 0.0 0.1 3.29 3.22 3460529092 affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 0 6 0.0 0.0 0.00 0.00 114078687 j#=:1 0 2 0.0 0.0 0.00 0.00 365454555 view$ where obj#=:1 0 14 0.0 0.0 0.00 0.00 398896841 :2 or (field1 = 0 and 'Y' = :3)) 0 142 0.0 0.0 0.06 0.06 615028291 msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr iority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protoco SQL ordered by Reads for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st 0 112 0.0 0.0 0.01 0.01 787810128 estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 0 142 0.0 0.0 0.07 0.05 1030859119 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from RTR.WB_RT_NOTIFY_QUEUE_TAB where time_manager_info 0 2 0.0 0.0 0.00 0.00 1159012319 synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc 0 14 0.0 0.0 0.00 0.03 1239889223 :1 0 142 0.0 0.0 0.04 0.05 1243459867 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_INQUEUE where time_manager_info <= :1 0 142 0.0 0.0 0.07 0.07 1273533510 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri ------------------------------------------------------------- rows will be truncated SQL ordered by Executions for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 5,112 5,112 1.0 0.00 0.00 3460529092 affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 871 7 0.0 0.00 0.00 2963598673 ext_date) and (next_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job 864 864 1.0 0.00 0.00 2964743345 ext_date < (sysdate+5/86400)) 142 5,112 36.0 0.07 0.08 238087931 ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 142 0 0.0 0.00 0.00 615028291 msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr iority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protoco l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st 142 0 0.0 0.00 0.00 1030859119 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from RTR.WB_RT_NOTIFY_QUEUE_TAB where time_manager_info 142 0 0.0 0.00 0.00 1243459867 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_INQUEUE where time_manager_info <= :1 142 0 0.0 0.00 0.00 1273533510 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_IN_OAG_Q_TABLE where time_manager_info 142 0 0.0 0.00 0.00 1899253823 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_OUTQUEUE where time_manager_info <= :1 142 0 0.0 0.00 0.00 2424344360 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1 SQL ordered by Executions for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 142 0 0.0 0.00 0.00 2780709284 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1 142 0 0.0 0.00 0.00 2917029344 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.WF_INBOUND_TABLE where time_manager_info < 142 0 0.0 0.00 0.00 2979542350 e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corr id, time_manager_info from SYS.AQ_EVENT_TABLE where time _manager_info <= :1 and state != :2 for update skip locked 142 142 1.0 0.00 0.00 3048181047 142 0 0.0 0.00 0.00 3085743083 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri ------------------------------------------------------------- rows will be truncated SQL ordered by Parse Calls for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 142 0 2.70 105459333 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_OS.QS_OS_ORDERS_PR_MQTAB where msgid = :1 142 142 2.70 238087931 ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 142 0 2.70 257130719 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_WS.QS_WS_ORDERS_MQTAB where msgid = :1 142 0 2.70 546288790 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_CBADM.QS_CBADM_ORDERS_MQTAB where msgid = :1 142 142 2.70 615028291 msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr iority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protoco l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st 142 142 2.70 1030859119 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from RTR.WB_RT_NOTIFY_QUEUE_TAB where time_manager_info 142 0 2.70 1207280434 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_ES.QS_ES_ORDERS_MQTAB where msgid = :1 142 142 2.70 1243459867 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_INQUEUE where time_manager_info <= :1 142 142 2.70 1273533510 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_IN_OAG_Q_TABLE where time_manager_info SQL ordered by Parse Calls for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 142 0 2.70 1444746843 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.WF_OUT where msgid = :1 142 0 2.70 1453960610 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.WF_DEFERRED where msgid = :1 142 142 2.70 1899253823 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from OWF_MGR.ECX_OUTQUEUE where time_manager_info <= :1 142 0 2.70 2364930759 _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ------------------------------------------------------------- Instance Activity Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 297 0.1 17.5 CPU used when call started 678 0.2 39.9 CR blocks created 142 0.0 8.4 DBWR buffers scanned 196 0.0 11.5 DBWR checkpoint buffers written 842 0.2 49.5 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 178 0.0 10.5 DBWR lru scans 4 0.0 0.2 DBWR make free requests 4 0.0 0.2 DBWR summed scan depth 196 0.0 11.5 DBWR transaction table writes 28 0.0 1.7 DBWR undo block writes 324 0.1 19.1 SQL*Net roundtrips to/from client 142 0.0 8.4 active txn count during cleanout 41 0.0 2.4 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 5,244 1.2 308.5 buffer is not pinned count 40,621 9.1 2,389.5 buffer is pinned count 20,097 4.5 1,182.2 bytes received via SQL*Net from c 139,806 31.1 8,223.9 bytes sent via SQL*Net to client 58,814 13.1 3,459.7 calls to get snapshot scn: kcmgss 46,321 10.3 2,724.8 calls to kcmgas 15,687 3.5 922.8 calls to kcmgcs 46 0.0 2.7 change write time 172 0.0 10.1 cleanout - number of ktugct calls 51 0.0 3.0 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 9 0.0 0.5 cluster key scan block gets 573 0.1 33.7 cluster key scans 411 0.1 24.2 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 7 0.0 0.4 commit cleanouts 15,584 3.5 916.7 commit cleanouts successfully com 15,577 3.5 916.3 commit txn count during cleanout 35 0.0 2.1 consistent changes 19,454 4.3 1,144.4 consistent gets 78,498 17.5 4,617.5 consistent gets - examination 40,956 9.1 2,409.2 cursor authentications 46 0.0 2.7 data blocks consistent reads - un 19,454 4.3 1,144.4 db block changes 65,805 14.7 3,870.9 db block gets 46,258 10.3 2,721.1 deferred (CURRENT) block cleanout 8,895 2.0 523.2 dirty buffers inspected 41 0.0 2.4 enqueue conversions 52 0.0 3.1 enqueue releases 34,608 7.7 2,035.8 enqueue requests 34,608 7.7 2,035.8 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 execute count 10,293 2.3 605.5 free buffer inspected 41 0.0 2.4 free buffer requested 6,851 1.5 403.0 hot buffers moved to head of LRU 107 0.0 6.3 immediate (CR) block cleanout app 9 0.0 0.5 immediate (CURRENT) block cleanou 51 0.0 3.0 Instance Activity Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ index fetch by key 16,613 3.7 977.2 index scans kdiixs1 15,301 3.4 900.1 leaf node 90-10 splits 6 0.0 0.4 leaf node splits 21 0.0 1.2 logons cumulative 32 0.0 1.9 messages received 2,602 0.6 153.1 messages sent 2,602 0.6 153.1 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 15,760 3.5 927.1 opened cursors cumulative 5,244 1.2 308.5 parse count (failures) 1 0.0 0.1 parse count (hard) 115 0.0 6.8 parse count (total) 5,252 1.2 308.9 parse time cpu 92 0.0 5.4 parse time elapsed 157 0.0 9.2 physical reads 1,109 0.3 65.2 physical reads direct 0 0.0 0.0 physical writes 901 0.2 53.0 physical writes direct 0 0.0 0.0 physical writes non checkpoint 800 0.2 47.1 pinned buffers inspected 0 0.0 0.0 prefetched blocks 837 0.2 49.2 process last non-idle time 8,583,245,260 1,912,061.8 ############ recovery blocks read 0 0.0 0.0 recursive calls 21,387 4.8 1,258.1 recursive cpu usage 183 0.0 10.8 redo blocks written 16,811 3.7 988.9 redo buffer allocation retries 0 0.0 0.0 redo entries 33,266 7.4 1,956.8 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 1 0.0 0.1 redo size 7,983,100 1,778.4 469,594.1 redo synch time 3 0.0 0.2 redo synch writes 10 0.0 0.6 redo wastage 385,520 85.9 22,677.7 redo write time 559 0.1 32.9 redo writer latching time 1 0.0 0.1 redo writes 2,596 0.6 152.7 rollback changes - undo records a 0 0.0 0.0 rollbacks only - consistent read 994 0.2 58.5 rows fetched via callback 15,743 3.5 926.1 session connect time 8,583,245,260 1,912,061.8 ############ session logical reads 124,756 27.8 7,338.6 session uga memory 504,432 112.4 29,672.5 session uga memory max 3,187,944 710.2 187,526.1 shared hash latch upgrades - no w 16,068 3.6 945.2 sorts (disk) 0 0.0 0.0 sorts (memory) 1,352 0.3 79.5 sorts (rows) 23,119 5.2 1,359.9 summed dirty queue length 59 0.0 3.5 switch current to new buffer 5,113 1.1 300.8 table fetch by rowid 17,164 3.8 1,009.7 table fetch continued row 0 0.0 0.0 table scan blocks gotten 7,302 1.6 429.5 table scan rows gotten 95,992 21.4 5,646.6 Instance Activity Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ table scans (long tables) 2 0.0 0.1 table scans (short tables) 5,878 1.3 345.8 transaction rollbacks 0 0.0 0.0 user calls 329 0.1 19.4 user commits 10 0.0 0.6 user rollbacks 7 0.0 0.4 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 298 0.1 17.5 write clones created in foregroun 0 0.0 0.0 ------------------------------------------------------------- rows will be truncated Tablespace IO Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ JUNK 120 0 10.7 1.0 379 0 0 0.0 UNDOTBS1 2 0 25.0 1.0 354 0 0 0.0 SYSTEM 186 0 5.2 5.5 168 0 0 0.0 USERS 0 0 0.0 2 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ JUNK E:\ORACLE\ORADATA\ORATOOLS\JUNK.ORA 45 0 10.7 1.0 154 0 0 E:\ORACLE\ORADATA\ORATOOLS\JUNK01.DBF 70 0 11.1 1.0 212 0 0 E:\ORACLE\ORADATA\ORATOOLS\JUNK02.DBF 2 0 5.0 1.0 4 0 0 E:\ORACLE\ORADATA\ORATOOLS\JUNK03.DBF 3 0 3.3 1.0 9 0 0 SYSTEM E:\ORACLE\ORADATA\ORATOOLS\SYSTEM01.DBF 186 0 5.2 5.5 168 0 0 UNDOTBS1 E:\ORACLE\ORADATA\ORATOOLS\UNDOTBS01.DBF 2 0 25.0 1.0 354 0 0 USERS E:\ORACLE\ORADATA\ORATOOLS\USERS01.DBF 0 0 2 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 2,000 99.1 121,481 1,136 903 0 0 0 ------------------------------------------------------------- rows will be truncated Instance Recovery Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- B 104 71 545 14463 14693 184320 14693 E 104 65 256 6271 5819 184320 5819 ------------------------------------------------------------- Buffer Pool Advisory for DB: ORATOOLS Instance: oratools End Snap: 3 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 4 .3 500 4.30 235,044 D 8 .5 1,000 2.10 114,650 D 12 .8 1,500 1.29 70,752 D 16 1.0 2,000 1.00 54,668 D 20 1.3 2,500 0.80 43,706 D 24 1.5 3,000 0.75 40,738 D 28 1.8 3,500 0.71 38,775 D 32 2.0 4,000 0.66 36,238 D 36 2.3 4,500 0.64 35,137 D 40 2.5 5,000 0.63 34,227 D 44 2.8 5,500 0.62 33,749 D 48 3.0 6,000 0.58 31,547 D 52 3.3 6,500 0.55 29,919 D 56 3.5 7,000 0.55 29,823 D 60 3.8 7,500 0.55 29,823 D 64 4.0 8,000 0.54 29,680 D 68 4.3 8,500 0.54 29,632 D 72 4.5 9,000 0.54 29,632 D 76 4.8 9,500 0.54 29,632 D 80 5.0 10,000 0.54 29,632 ------------------------------------------------------------- PGA Aggr Target Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 7 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 32 19 23.4 0.0 .0 .0 .0 1,638 E 32 20 21.0 0.0 .0 .0 .0 1,638 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 264 264 0 0 16K 32K 20 20 0 0 32K 64K 4 4 0 0 64K 128K 4 4 0 0 256K 512K 2 2 0 0 512K 1024K 4 4 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: ORATOOLS Instance: oratools End Snap: 3 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 16 0.5 137.6 7.0 95.0 2 24 0.8 137.6 0.0 100.0 0 32 1.0 137.6 0.0 100.0 0 38 1.2 137.6 0.0 100.0 0 45 1.4 137.6 0.0 100.0 0 51 1.6 137.6 0.0 100.0 0 58 1.8 137.6 0.0 100.0 0 64 2.0 137.6 0.0 100.0 0 96 3.0 137.6 0.0 100.0 0 128 4.0 137.6 0.0 100.0 0 192 6.0 137.6 0.0 100.0 0 256 8.0 137.6 0.0 100.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 0 16.0 0.00 0 0 0 0 1 2,608.0 0.00 168,554 0 0 0 2 2,587.0 0.00 165,358 0 0 0 3 2,587.0 0.00 165,508 1 0 0 4 2,608.0 0.00 168,388 0 0 0 5 2,587.0 0.00 166,474 0 0 0 6 2,653.0 0.00 319,554 0 0 0 7 2,588.0 0.00 168,108 2 0 1 8 2,632.0 0.00 426,544 1 0 1 9 2,607.0 0.00 168,798 0 0 0 10 2,611.0 0.00 168,328 2 0 1 ------------------------------------------------------------- Rollback Segment Storage for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 385,024 0 385,024 1 2,220,032 317,707 2,220,032 2 3,334,144 282,726 3,334,144 3 3,268,608 347,008 3,268,608 4 2,220,032 255,251 2,220,032 5 3,268,608 347,008 3,268,608 6 3,268,608 347,008 3,268,608 7 3,268,608 316,424 3,268,608 8 3,268,608 347,008 3,268,608 9 2,220,032 172,265 2,220,032 10 3,268,608 344,301 3,268,608 ------------------------------------------------------------- Undo Segment Summary for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU ---- -------------- ---------- -------- ---------- -------- ------ ------------- 1 273 1,710,861 4 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> ordered by Time desc Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU ------------ ------------ -------- ------- -------- ------- ------ ------------- 31-Dec 17:10 30 248,503 4 1 0 0 0/0/0/0/0/0 31-Dec 17:00 29 247,042 3 1 0 0 0/0/0/0/0/0 31-Dec 16:50 29 245,774 4 1 0 0 0/0/0/0/0/0 31-Dec 16:40 26 244,466 4 1 0 0 0/0/0/0/0/0 31-Dec 16:30 27 243,053 4 1 0 0 0/0/0/0/0/0 31-Dec 16:20 56 241,708 3 1 0 0 0/0/0/0/0/0 31-Dec 16:10 76 240,315 3 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Consistent RBA 2,596 0.0 0 0 FIB s.o chain latch 2 0.0 0 0 FOB s.o list latch 17 0.0 0 0 SQL memory manager latch 2 0.0 0 1,453 0.0 SQL memory manager worka 97,589 0.0 0 0 active checkpoint queue 1,586 0.0 0 0 archive control 2 0.0 0 0 cache buffer handles 550 0.0 0 0 cache buffers chains 372,269 0.0 0 2,859 0.0 cache buffers lru chain 8,897 0.0 0 5,388 0.0 channel handle pool latc 50 0.0 0 0 channel operations paren 3,037 0.0 0 0 checkpoint queue latch 80,913 0.0 0 616 0.0 child cursor hash table 2,127 0.0 0 0 dml lock allocation 42,057 0.0 0 0 dummy allocation 64 0.0 0 0 enqueue hash chains 69,256 0.0 0 0 enqueues 6,605 0.0 0 0 event group latch 25 0.0 0 0 file number translation 183 0.0 0 0 hash table column usage 33 0.0 0 527 0.0 job workq parent latch 0 0 14 0.0 job_queue_processes para 93 0.0 0 0 kmcptab latch 1 0.0 0 0 kmcpvec latch 0 0 1 0.0 ktm global data 14 0.0 0 0 kwqit: protect wakeup ti 142 0.0 0 0 lgwr LWN SCN 3,761 0.0 0 0 library cache 126,802 0.0 19.0 1 0 library cache load lock 126 0.0 0 0 library cache pin 53,452 0.0 0 0 library cache pin alloca 42,849 0.0 0 0 list of block allocation 32 0.0 0 0 messages 23,608 0.0 0 0 mostly latch-free SCN 3,762 0.0 0 0 multiblock read objects 286 0.0 0 0 ncodef allocation latch 71 0.0 0 0 object stats modificatio 2 0.0 0 0 post/wait queue 5,200 0.0 0 10 0.0 process allocation 25 0.0 0 25 0.0 process group creation 50 0.0 0 0 redo allocation 39,643 0.0 1.3 0 0 redo copy 1 0.0 0 33,294 0.0 redo writing 12,428 0.0 0 0 row cache enqueue latch 18,517 0.0 0 0 row cache objects 18,680 0.0 0 0 sequence cache 18 0.0 0 0 session allocation 23,881 0.0 0 0 session idle bit 871 0.0 0 0 session switching 71 0.0 0 0 Latch Activity for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ session timer 1,504 0.0 0 0 shared pool 61,268 0.0 0 0 sim partition latch 0 0 5 0.0 simulator hash latch 1,866 0.0 0 0 simulator lru latch 108 0.0 0 12 0.0 sort extent pool 85 0.0 0 0 transaction allocation 18 0.0 0 0 transaction branch alloc 71 0.0 0 0 undo global data 51,339 0.0 0 0 user lock 18 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ redo allocation 39,643 8 10 0/6/2/0/0 library cache 126,802 3 57 0/2/0/1/0 ------------------------------------------------------------- Latch Miss Sources for DB: ORATOOLS Instance: oratools Snaps: 1 -3 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- library cache kglic 0 55 0 library cache kgllkdl: child: cleanup 0 1 0 library cache kglobpn: child: 0 1 0 redo allocation kcrfwr 0 10 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_histogram_defs 265 42.3 0 0 329 dc_object_ids 452 0.0 0 0 1,291 dc_objects 2,613 0.2 0 0 1,690 dc_profiles 8 0.0 0 0 1 dc_rollback_segments 588 0.0 0 0 22 dc_segments 5,211 0.0 0 0 1,136 dc_tablespaces 20 0.0 0 0 21 dc_user_grants 35 0.0 0 0 23 dc_usernames 60 1.7 0 0 35 dc_users 154 0.0 0 0 41 ------------------------------------------------------------- Library Cache Activity for DB: ORATOOLS Instance: oratools Snaps: 1 -3 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 16 0.0 16 0.0 0 0 CLUSTER 2 0.0 3 0.0 0 0 INDEX 2,272 0.0 2,272 0.0 0 0 SQL AREA 5,202 1.7 15,846 1.5 7 0 TABLE/PROCEDURE 5,857 0.1 6,304 1.6 1 0 TRIGGER 24 0.0 24 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: ORATOOLS Instance: oratools End Snap: 3 -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 24 .5 24 5,653 17,118 1.0 540,068 32 .7 31 6,419 17,123 1.0 540,252 40 .8 37 7,728 17,123 1.0 540,255 48 1.0 37 7,728 17,123 1.0 540,255 56 1.2 37 7,728 17,123 1.0 540,255 64 1.3 37 7,728 17,123 1.0 540,255 72 1.5 37 7,728 17,123 1.0 540,255 80 1.7 37 7,728 17,123 1.0 540,255 88 1.8 37 7,728 17,123 1.0 540,255 96 2.0 37 7,728 17,123 1.0 540,255 ------------------------------------------------------------- SGA Memory Summary for DB: ORATOOLS Instance: oratools Snaps: 1 -3 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 16,777,216 Fixed Size 453,452 Redo Buffers 667,648 Variable Size 109,051,904 ---------------- sum 126,950,220 ------------------------------------------------------------- SGA breakdown difference for DB: ORATOOLS Instance: oratools Snaps: 1 -3 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 8,388,608 8,388,608 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared FileIdentificatonBlock 323,292 323,292 0.00 shared FileOpenBlock 695,504 695,504 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 3,777,348 3,837,964 1.60 shared KQR M PO 1,669,716 1,729,620 3.59 shared KQR S PO 396,076 396,332 0.06 shared KQR S SO 5,908 5,908 0.00 shared KSXR pending messages que 841,036 841,036 0.00 shared KSXR receive buffers 1,033,000 1,033,000 0.00 shared MTTR advisory 8,456 8,456 0.00 shared PL/SQL DIANA 7,272,420 7,272,420 0.00 shared PL/SQL MPCODE 3,519,544 3,556,288 1.04 shared PL/SQL PPCODE 142,496 142,496 0.00 shared PL/SQL SOURCE 22,888 22,888 0.00 shared PLS non-lib hp 2,068 2,068 0.00 shared XDB Schema Cac 4,956,344 4,956,344 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared errors 197,500 197,500 0.00 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 180 180 0.00 shared free memory 10,750,704 7,734,268 -28.06 shared joxs heap init 4,220 4,220 0.00 shared kgl simulator 988,364 1,034,960 4.71 shared library cache 7,631,636 8,136,032 6.61 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 6,173,592 6,173,592 0.00 shared parameters 40,836 47,928 17.37 shared sessions 410,720 410,720 0.00 shared sim memory hea 21,164 21,164 0.00 shared sql area 9,822,128 12,121,840 23.41 shared subheap 46,580 46,580 0.00 shared table definiti 7,504 8,624 14.93 shared trigger defini 6,632 6,632 0.00 shared trigger inform 1,256 1,256 0.00 shared trigger source 4,992 4,992 0.00 shared type object de 68,836 68,836 0.00 buffer_cache 16,777,216 16,777,216 0.00 fixed_sga 453,452 453,452 0.00 log_buffer 656,384 656,384 0.00 ------------------------------------------------------------- init.ora Parameters for DB: ORATOOLS Instance: oratools Snaps: 1 -3 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 audit_trail NONE background_dump_dest E:\oracle\admin\oraTools\bdump compatible 9.2.0.0.0 control_files E:\oracle\oradata\oraTools\CONTRO core_dump_dest E:\oracle\admin\oraTools\cdump db_block_size 8192 db_cache_size 16777216 db_create_file_dest db_create_online_log_dest_1 db_domain db_file_multiblock_read_count 32 db_name oraTools dispatchers (PROTOCOL=TCP) (SERVICE=oraToolsX enqueue_resources 3000 fast_start_mttr_target 300 hash_area_size 1048576 hash_join_enabled TRUE instance_name oraTools java_pool_size 33554432 job_queue_processes 10 large_pool_size 8388608 max_enabled_roles 50 open_cursors 300 optimizer_mode CHOOSE pga_aggregate_target 33554432 processes 150 query_rewrite_enabled TRUE remote_login_passwordfile EXCLUSIVE shared_pool_size 50331648 sort_area_size 1048576 star_transformation_enabled TRUE timed_statistics TRUE undo_management AUTO undo_retention 10800 undo_tablespace UNDOTBS1 user_dump_dest E:\oracle\admin\oraTools\udump ------------------------------------------------------------- End of Report