STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ AFSYS 2894357245 afsys 1 9.2.0.1.0 NO SN-ORACLE Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 1 28-Sep-09 09:24:53 64 18.2 End Snap: 6 28-Sep-09 09:53:10 73 19.1 Elapsed: 28.28 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 504M Std Block Size: 8K Shared Pool Size: 400M Log Buffer: 512K Shared Pool Statistics Begin End Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 2,829 93.07 db file scattered read 56,672 99 3.24 db file sequential read 55,663 74 2.45 control file parallel write 548 13 .44 db file parallel write 246 9 .30 ------------------------------------------------------------- Wait Events for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 ---------------------------- ------------ ---------- ---------- ------ -------- db file scattered read 56,672 0 99 2 206.1 db file sequential read 55,663 0 74 1 202.4 control file parallel write 548 0 13 24 2.0 db file parallel write 246 122 9 37 0.9 log file parallel write 420 340 6 14 1.5 log file sync 143 1 4 27 0.5 control file sequential read 256 0 3 10 0.9 latch free 52 49 1 21 0.2 inactive session 1 1 1 1029 0.0 direct path read 104 0 1 5 0.4 direct path write 4 0 0 19 0.0 SQL*Net break/reset to clien 80 0 0 1 0.3 SQL*Net more data to client 196 0 0 0 0.7 LGWR wait for redo copy 6 0 0 0 0.0 SQL*Net message from client 126,097 0 37,375 296 458.5 virtual circuit status 57 57 1,706 29934 0.2 wakeup time manager 54 54 1,648 30510 0.2 SQL*Net message to client 126,103 0 0 0 458.6 SQL*Net more data from clien 13 0 0 0 0.0 ------------------------------------------------------------- rows will be truncated Background Wait Events for DB: AFSYS Instance: afsys Snaps: 1 -6 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file parallel write 548 0 13 24 2.0 db file parallel write 246 122 9 37 0.9 log file parallel write 420 340 6 14 1.5 db file sequential read 18 0 0 12 0.1 db file scattered read 8 0 0 18 0.0 LGWR wait for redo copy 6 0 0 0 0.0 rdbms ipc message 2,699 2,407 7,660 2838 9.8 smon timer 5 5 1,367 ###### 0.0 ------------------------------------------------------------- rows will be truncated SQL ordered by Gets for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 1,017,453 323 3,150.0 0.2 25.91 27.88 2796848209 E PRE_GLT_COMPANY = :b1 AND PRE_GLT_CODE BETWEEN :b2 AND :b2 A ND PRE_GLT_GL_DATE BETWEEN TO_DATE(:b4 || ' 00:00:00' ,'DD/MM/YY YY HH24:MI:SS') AND TO_DATE(:b5 || ' 23:59:00' ,'DD/MM/YYYY HH24 :MI:SS') - 1 410,225 3 136,741.7 0.1 1.91 3.18 2081425600 tablespace_name, count(*) || ' tables' objects from sys.dba_t ables group by substr(owner,1,20), substr(tablespace_name,1, 32) union select substr(owner,1,20) owner, substr(tablespac e_name,1,32) tablespace_name, count(*) || ' indexes' object 410,092 3 136,697.3 0.1 1.81 1.80 2477790232 wner,1,20) owner, count(*) || ' tables' objects from sys.dba_t ables group by substr(tablespace_name,1,32), substr(owner,1, 20) union select substr(tablespace_name,1,32) tablespace_name, substr(owner,1,20) owner, count(*) || ' indexes' objects f 247,080 11 22,461.8 0.0 1.41 1.79 219796622 BLC_CONTTYPE,BLC_SIZE,BLC_NETWT,BLC_TAREWT,BLC_CARGO_WEIGHT,BLC_ GROSSWT,BLC_VOLUME,BLC_SEAL,BLC_SERVICE_TYPE,BLC_COMMODITY,BLC_P ACKAGE_TYPE,BLC_PACKAGES,BLC_OWNER_STATUS,BLC_REEFER,BLC_MIN_TEM P,BLC_MAX_TEMP,BLC_REEFER_OPERATION,BLC_VENTILATION,BLC_UNNO,BLC 246,587 11 22,417.0 0.0 1.38 1.52 287634203 :b9 AND cs_code = :b8 AND cs_mode = 'A' AND (: b7 IS NULL OR :b7 IS NOT NULL AND cs_commodit y_group = :b7) AND cs_origin_name = :b6 AND cs_load_port = :b5 AND cs_discharge_port = :b4 AND cs_final_name 244,045 20 12,202.3 0.0 1.19 1.23 3419983518 INER=:3,BLC_CONTTYPE=:4,BLC_SIZE=:5,BLC_NETWT=:6,BLC_TAREWT=:7,B LC_CARGO_WEIGHT=:8,BLC_GROSSWT=:9,BLC_VOLUME=:10,BLC_SEAL=:11,BL C_SERVICE_TYPE=:12,BLC_COMMODITY=:13,BLC_PACKAGE_TYPE=:14,BLC_PA CKAGES=:15,BLC_OWNER_STATUS=:16,BLC_REEFER=:17,BLC_MIN_TEMP=:18, 243,223 20 12,161.2 0.0 1.16 1.16 385183868 :b49 - :b48, cs_reefer_teus = cs_reefer_teus + :b47 - : b46, cs_dv_20_qty = cs_dv_20_qty + :b45 - :b44, cs_dv_40_qty = cs_dv_40_qty + :b43 - :b42, cs_ot_20_qt y = cs_ot_20_qty + :b41 - :b40, cs_ot_40_qty = cs_ot_4 240,570 9 26,730.0 0.0 7.63 57.11 3404103359 JOBA_PARENT_SERIAL,JOBA_SERIAL,JOBA_ACTIVITY,JOBA_BASIS,JOBA_RAT E,JOBA_QUANTITY,JOBA_CURRENCY,JOBA_EX_RATE,JOBA_F_QUOTE,JOBA_F_I NVOICE,JOBA_F_PROVISION,JOBA_F_COST,JOBA_L_QUOTE,JOBA_L_INVOICE, JOBA_L_PROVISION,JOBA_L_COST,JOBA_STATUS,JOBA_ACT_S_DATE,JOBA_AC 240,410 9 26,712.2 0.0 5.86 44.30 1879553644 E=:2,JOBA_PARENT_SERIAL=:3,JOBA_SERIAL=:4,JOBA_ACTIVITY=:5,JOBA_ SQL ordered by Gets for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- BASIS=:6,JOBA_RATE=:7,JOBA_QUANTITY=:8,JOBA_CURRENCY=:9,JOBA_EX_ RATE=:10,JOBA_F_QUOTE=:11,JOBA_F_INVOICE=:12,JOBA_F_PROVISION=:1 3,JOBA_F_COST=:14,JOBA_L_QUOTE=:15,JOBA_L_INVOICE=:16,JOBA_L_PRO 232,567 2 116,283.5 0.0 2.81 3.21 2729534277 ANY = :b1 AND (:b2 IS NULL OR ART_CODE IN ( :b2 )) AND TO_DAT E(TO_CHAR(ART_DOC_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') < TO_DATE(:b4 ,'DD/MM/YYYY') 223,080 25 8,923.2 0.0 6.92 44.54 1923005389 etails WHERE joba_reference = :b1 AND joba_invoice IS NULL AND joba_receipt IS NULL AND joba_system IS NULL AND joba_drcr IS NULL AND joba_l_invoice > 0 AND joba_flex_3 IS NULL 223,080 26 8,580.0 0.0 5.45 44.69 3906541344 ERE joba_reference = :b1 AND joba_invoice IS NOT NU LL AND joba_flex_3 IS NULL 223,080 26 8,580.0 0.0 6.70 40.99 4105350001 s WHERE joba_reference = :b1 AND (joba_invoice IS NOT NULL OR joba_receipt IS NOT NULL OR joba_system IS NOT NULL OR joba_drcr IS NOT NULL) AND job a_l_invoice > 0 AND joba_flex_3 IS NULL 198,626 2 99,313.0 0.0 1.02 1.01 117738335 RVICE_TYPE=:3,BK_TYPE=:4,BK_NUMBER=:5,BK_CMS_CODE=:6,BK_CUSTOMER ------------------------------------------------------------- rows will be truncated SQL ordered by Reads for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 93,894 9 10,432.7 20.4 5.86 44.30 1879553644 E=:2,JOBA_PARENT_SERIAL=:3,JOBA_SERIAL=:4,JOBA_ACTIVITY=:5,JOBA_ BASIS=:6,JOBA_RATE=:7,JOBA_QUANTITY=:8,JOBA_CURRENCY=:9,JOBA_EX_ RATE=:10,JOBA_F_QUOTE=:11,JOBA_F_INVOICE=:12,JOBA_F_PROVISION=:1 3,JOBA_F_COST=:14,JOBA_L_QUOTE=:15,JOBA_L_INVOICE=:16,JOBA_L_PRO 91,374 9 10,152.7 19.9 7.63 57.11 3404103359 JOBA_PARENT_SERIAL,JOBA_SERIAL,JOBA_ACTIVITY,JOBA_BASIS,JOBA_RAT E,JOBA_QUANTITY,JOBA_CURRENCY,JOBA_EX_RATE,JOBA_F_QUOTE,JOBA_F_I NVOICE,JOBA_F_PROVISION,JOBA_F_COST,JOBA_L_QUOTE,JOBA_L_INVOICE, JOBA_L_PROVISION,JOBA_L_COST,JOBA_STATUS,JOBA_ACT_S_DATE,JOBA_AC 88,433 25 3,537.3 19.2 6.92 44.54 1923005389 etails WHERE joba_reference = :b1 AND joba_invoice IS NULL AND joba_receipt IS NULL AND joba_system IS NULL AND joba_drcr IS NULL AND joba_l_invoice > 0 AND joba_flex_3 IS NULL 88,196 26 3,392.2 19.2 5.45 44.69 3906541344 ERE joba_reference = :b1 AND joba_invoice IS NOT NU LL AND joba_flex_3 IS NULL 88,143 26 3,390.1 19.2 6.70 40.99 4105350001 s WHERE joba_reference = :b1 AND (joba_invoice IS NOT NULL OR joba_receipt IS NOT NULL OR joba_system IS NOT NULL OR joba_drcr IS NOT NULL) AND job a_l_invoice > 0 AND joba_flex_3 IS NULL 68,153 7 9,736.1 14.8 5.22 25.26 4071009103 E=:b2 WHERE JOBA_COMPANY = :b3 AND JOBA_REFERENCE = :b4 AND JO BA_PARENT_SERIAL = :b5 AND JOBA_SERIAL = :b6 50,202 2 25,101.0 10.9 3.20 11.10 1192600723 ERIAL,BLC_SERVICE_TYPE,BLC_GROSSWT,BLC_TAREWT,BLC_NETWT,BLC_CARG O_WEIGHT,BLC_VOLUME,BLC_SEAL,BLC_REEFER,BLC_REEFER_OPERATION,BLC _VENTILATION,BLC_MIN_TEMP,BLC_MAX_TEMP,BLC_UNNO,BLC_CLASS,BLC_PA GENO,BLC_FLASH,BLC_TEMPRATURE,BLC_TECHNICAL,BLC_DESCRIPTION,BLC_ 25,809 1 25,809.0 5.6 5.73 37.31 2062829640 ERIAL,BLC_SERVICE_TYPE,BLC_GROSSWT,BLC_TAREWT,BLC_NETWT,BLC_CARG O_WEIGHT,BLC_VOLUME,BLC_SEAL,BLC_REEFER,BLC_REEFER_OPERATION,BLC _VENTILATION,BLC_MIN_TEMP,BLC_MAX_TEMP,BLC_UNNO,BLC_CLASS,BLC_PA GENO,BLC_FLASH,BLC_TEMPRATURE,BLC_TECHNICAL,BLC_DESCRIPTION,BLC_ 25,107 1 25,107.0 5.5 1.19 4.00 419764681 ERIAL,BLC_SERVICE_TYPE,BLC_GROSSWT,BLC_TAREWT,BLC_NETWT,BLC_CARG O_WEIGHT,BLC_VOLUME,BLC_SEAL,BLC_REEFER,BLC_REEFER_OPERATION,BLC _VENTILATION,BLC_MIN_TEMP,BLC_MAX_TEMP,BLC_UNNO,BLC_CLASS,BLC_PA GENO,BLC_FLASH,BLC_TEMPRATURE,BLC_TECHNICAL,BLC_DESCRIPTION,BLC_ 22,341 23 971.3 4.9 1.16 7.22 2984015833 = :b1 11,372 1 11,372.0 2.5 0.63 3.96 750853795 SQL ordered by Reads for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- A_COMPANY = :b2 AND JOBA_REFERENCE = :b3 AND JOBA_JOB_NUMBER = TO_CHAR(:b3) 11,319 11 1,029.0 2.5 0.59 2.58 1363539192 AND EQP_TYPE = :b2 9,470 6 1,578.3 2.1 0.39 2.67 1619173295 :b1 AND TMP_DOC_NUMBER = :b2 6,021 3 2,007.0 1.3 0.20 1.17 1968403797 = :b1 AND ARM_M_TYPE = :b2 AND ARM_M_NUMBER = :b3 5,251 4 1,312.8 1.1 0.20 1.09 1294107908 ORE_DETAILS WHERE SUB_RCTM_COMPANY = :b1 AND SUB_RCTM_DOC_TYPE = :b2 AND SUB_RCTM_DOC_NUMBER = :b3 5,249 4 1,312.3 1.1 0.17 1.19 648639163 ub_rctm_doc_type, sub_rctm_doc_number sub_rctm_doc_number, sub _rctm_currency sub_rctm_currency, sub_rctm_local_amount sub_rct m_local_amount, sub_rctm_cheque_number sub_rctm_cheque_number, sub_rctm_cheque_date sub_rctm_cheque_date , sub_rctm_branch sub 5,073 4 1,268.3 1.1 0.17 0.96 1428338505 ERE SUB_RCTM_COMPANY = :b1 AND SUB_RCTM_DOC_TYPE = :b2 AND SUB _RCTM_DOC_NUMBER = :b3 4,786 2 2,393.0 1.0 0.22 1.17 2294628130 ------------------------------------------------------------- rows will be truncated SQL ordered by Executions for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 2,010 2,010 1.0 0.00 0.00 2977853401 1,905 1,862 1.0 0.00 0.00 1629546654 :b1 1,474 1,474 1.0 0.00 0.00 4178409212 UE = 'P' AND BPL_REFNO = :b1 1,331 1,331 1.0 0.00 0.00 1077890418 INERS WHERE BLC_REFNO = :b1 AND BLC_SIZE = '40' 1,331 1,331 1.0 0.00 0.00 2615247551 = :b1 1,286 1,286 1.0 0.00 0.00 499133564 ERE BLC_REFNO = :b1 AND BLC_SIZE = '20' 1,188 1,188 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 1,180 1,180 1.0 0.00 0.00 1726400165 _CONTINUE,BPL_USER ) VALUES ( :b1,:b2,:b3,'C',:b4 ) 540 1,260 2.3 0.00 0.00 502510949 privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 540 540 1.0 0.00 0.00 1498920852 nect by grantee#=prior privilege# and privilege#>0 start with (g rantee#=:2 or grantee#=1) and privilege#>0 group by privilege# 430 430 1.0 0.00 0.00 3783998994 Y = :b1 331 0 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 331 331 1.0 0.00 0.00 2964743345 ext_date < (sysdate+5/86400)) 325 325 1.0 0.00 0.00 716336170 _CONTINUE,BPL_USER ) VALUES ( :b1,:b2,:b3,'A',:b4 ) 323 238 0.7 0.01 0.01 1321598810 NY = :b1 AND PH_DOC_NUMBER = :b2 323 323 1.0 0.00 0.00 2259059842 NY = :b1 AND GBAL_ACCOUNT = :b2 AND GBAL_YEAR = :b3 AND GBAL_ PERIOD = :b4 SQL ordered by Executions for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 323 323 1.0 0.08 0.09 2796848209 E PRE_GLT_COMPANY = :b1 AND PRE_GLT_CODE BETWEEN :b2 AND :b2 A ND PRE_GLT_GL_DATE BETWEEN TO_DATE(:b4 || ' 00:00:00' ,'DD/MM/YY YY HH24:MI:SS') AND TO_DATE(:b5 || ' 23:59:00' ,'DD/MM/YYYY HH24 :MI:SS') - 1 317 0 0.0 0.00 0.00 2921015019 TION,BLM_CONS_WEIGHT,BLM_CONS_VOLUME,BLM_UNNO,BLM_CLASS,BLM_PAGE NO,BLM_FLASH,BLM_MIN_TEMP,BLM_MAX_TEMP FROM ID_PACKAGE_MASTER, ID_BL_CONSIGNMENT WHERE BLM_REFNO = :b1 AND BLM_FLEX_1 = :b2 AND PACKAGE_CODE = BLM_PACKAGE_TYPE ORDER BY BLM_SERIAL 297 0 0.0 0.00 0.00 3397948939 231 231 1.0 0.00 0.00 3779223129 = :b1 AND BPL_CONTINUE = 'C' 213 213 1.0 0.00 0.00 2182723903 203 203 1.0 0.00 0.00 1341041388 opening + :b2 , gbal_f_opening = gbal_f_opening + :b1 WHERE gbal_company = :b5 AND gbal_account = :b4 AND gbal_year = :b3 AND gbal_period 203 203 1.0 0.00 0.00 1460170298 AND gbal_account = :b2 AND gbal_year = :b1 AND g bal_period = :b4 178 211 1.2 0.00 0.00 2085632044 177 134 0.8 0.00 0.00 584350288 169 169 1.0 0.00 0.00 1002381539 AL_SIZE_4, AL_SIZE_5, AL_SIZE_6, AL_SI ------------------------------------------------------------- rows will be truncated SQL ordered by Parse Calls for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 540 540 6.58 502510949 privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 540 540 6.58 1498920852 nect by grantee#=prior privilege# and privilege#>0 start with (g rantee#=:2 or grantee#=1) and privilege#>0 group by privilege# 297 297 3.62 3397948939 168 168 2.05 2091761008 147 147 1.79 3382968066 GE_COMPANY = :b1 AND VOYAGE_LINE = :b2 AND VOYAGE_VESSEL = :b3 AND VOYAGE_VOYAGE = :b4 AND VOYAGE_PORT = :b5 140 430 1.71 3783998994 Y = :b1 132 132 1.61 2917844719 Y_CODE = :b1 128 155 1.56 2735266159 127 127 1.55 578842274 ID_BL_NOTIFY WHERE BLN_REFNO = :b1 127 127 1.55 3310576611 L_REFNO = :b2 AND BL_NUMBER = :b3 115 115 1.40 1470906206 102 102 1.24 3348238861 95 95 1.16 3298889420 R WHERE VOYAGE_COMPANY = :1 AND VOYAGE_LINE = :2 AND VOYAGE_VESS EL = :3 AND VOYAGE_STATUS = 'Y' ORDER BY VOYAGE_ETA DESC 92 177 1.12 584350288 67 1,905 0.82 1629546654 :b1 66 66 0.80 1604408535 CA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CH ARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MO N-YY' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' 66 66 0.80 3013728279 privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0 60 60 0.73 1566515982 LNT_NOTES NOT LIKE 'REMINDER NO%' SQL ordered by Parse Calls for DB: AFSYS Instance: afsys Snaps: 1 -6 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 55 55 0.67 728028834 :b1 AND PRH_BOOK_NUMBER = :b2 54 0 0.66 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 54 54 0.66 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 54 0 0.66 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 54 0 0.66 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 54 54 0.66 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 ------------------------------------------------------------- rows will be truncated rows will be truncated Instance Activity Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 282,886 166.7 1,028.7 CPU used when call started 283,163 166.9 1,029.7 CR blocks created 88 0.1 0.3 DBWR buffers scanned 16,182 9.5 58.8 DBWR checkpoint buffers written 656 0.4 2.4 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 14,826 8.7 53.9 DBWR lru scans 4 0.0 0.0 DBWR make free requests 4 0.0 0.0 DBWR summed scan depth 16,182 9.5 58.8 DBWR transaction table writes 10 0.0 0.0 DBWR undo block writes 454 0.3 1.7 SQL*Net roundtrips to/from client 125,999 74.3 458.2 active txn count during cleanout 183 0.1 0.7 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 1,981 1.2 7.2 branch node splits 4 0.0 0.0 buffer is not pinned count 578,194,759 340,715.8 2,102,526.4 buffer is pinned count 1,487,482,758 876,536.7 5,409,028.2 bytes received via SQL*Net from c 12,216,079 7,198.6 44,422.1 bytes sent via SQL*Net to client 52,397,580 30,876.6 190,536.7 calls to get snapshot scn: kcmgss 61,885 36.5 225.0 calls to kcmgas 4,071 2.4 14.8 calls to kcmgcs 149 0.1 0.5 change write time 12 0.0 0.0 cleanout - number of ktugct calls 238 0.1 0.9 cleanouts and rollbacks - consist 15 0.0 0.1 cleanouts only - consistent read 21 0.0 0.1 cluster key scan block gets 274,674 161.9 998.8 cluster key scans 162,931 96.0 592.5 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: callbac 6 0.0 0.0 commit cleanouts 4,995 2.9 18.2 commit cleanouts successfully com 4,989 2.9 18.1 commit txn count during cleanout 132 0.1 0.5 consistent changes 2,658 1.6 9.7 consistent gets 586,214,499 345,441.7 2,131,689.1 consistent gets - examination 966,566 569.6 3,514.8 cursor authentications 178 0.1 0.7 data blocks consistent reads - un 2,658 1.6 9.7 db block changes 31,017 18.3 112.8 db block gets 25,098 14.8 91.3 deferred (CURRENT) block cleanout 2,995 1.8 10.9 dirty buffers inspected 550 0.3 2.0 enqueue conversions 294 0.2 1.1 enqueue releases 10,753 6.3 39.1 enqueue requests 10,757 6.3 39.1 execute count 27,440 16.2 99.8 free buffer inspected 595 0.4 2.2 free buffer requested 461,362 271.9 1,677.7 hot buffers moved to head of LRU 28,048 16.5 102.0 immediate (CR) block cleanout app 36 0.0 0.1 immediate (CURRENT) block cleanou 183 0.1 0.7 index fetch by key 867,300 511.1 3,153.8 index scans kdiixs1 246,943 145.5 898.0 Instance Activity Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ leaf node 90-10 splits 8 0.0 0.0 leaf node splits 73 0.0 0.3 logons cumulative 66 0.0 0.2 messages received 427 0.3 1.6 messages sent 428 0.3 1.6 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 584,996,614 344,724.0 2,127,260.4 opened cursors cumulative 7,709 4.5 28.0 parse count (failures) 9 0.0 0.0 parse count (hard) 283 0.2 1.0 parse count (total) 8,202 4.8 29.8 parse time cpu 102 0.1 0.4 parse time elapsed 186 0.1 0.7 physical reads 459,988 271.1 1,672.7 physical reads direct 719 0.4 2.6 physical writes 33,941 20.0 123.4 physical writes direct 31,395 18.5 114.2 physical writes non checkpoint 33,902 20.0 123.3 pinned buffers inspected 0 0.0 0.0 prefetched blocks 346,931 204.4 1,261.6 prefetched blocks aged out before 5 0.0 0.0 process last non-idle time 82,771,683,186 48,775,299.5 ############ recursive calls 35,989 21.2 130.9 recursive cpu usage 2,789 1.6 10.1 redo blocks written 12,848 7.6 46.7 redo buffer allocation retries 0 0.0 0.0 redo entries 15,910 9.4 57.9 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo size 6,333,104 3,731.9 23,029.5 redo synch time 366 0.2 1.3 redo synch writes 142 0.1 0.5 redo wastage 114,028 67.2 414.7 redo write time 635 0.4 2.3 redo writes 420 0.3 1.5 rollback changes - undo records a 16 0.0 0.1 rollbacks only - consistent read 289 0.2 1.1 rows fetched via callback 276,718 163.1 1,006.3 session connect time 82,771,683,186 48,775,299.5 ############ session logical reads 586,239,644 345,456.5 2,131,780.5 session uga memory max 36,305,688 21,394.0 132,020.7 shared hash latch upgrades - no w 247,533 145.9 900.1 shared hash latch upgrades - wait 0 0.0 0.0 sorts (disk) 1 0.0 0.0 sorts (memory) 12,722 7.5 46.3 sorts (rows) 2,138,512 1,260.2 7,776.4 summed dirty queue length 7,158 4.2 26.0 switch current to new buffer 1,308 0.8 4.8 table fetch by rowid 1,031,620,303 607,908.3 3,751,346.6 table fetch continued row 12 0.0 0.0 table scan blocks gotten 1,064,555 627.3 3,871.1 table scan rows gotten 45,910,403 27,053.9 166,946.9 table scans (long tables) 168 0.1 0.6 table scans (short tables) 11,486 6.8 41.8 transaction rollbacks 4 0.0 0.0 user calls 126,001 74.3 458.2 Instance Activity Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ user commits 130 0.1 0.5 user rollbacks 145 0.1 0.5 workarea executions - onepass 1 0.0 0.0 workarea executions - optimal 10,298 6.1 37.5 write clones created in foregroun 1 0.0 0.0 ------------------------------------------------------------- rows will be truncated Tablespace IO Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ AFSYS_SPACE 110,747 65 1.5 4.1 489 0 0 0.0 SYSTEM 1,505 1 1.7 1.2 512 0 0 0.0 TEMP 104 0 7.4 6.9 1,219 1 0 0.0 USERS 76 0 13.2 1.1 1,079 1 0 0.0 UNDOTBS1 5 0 6.0 1.0 466 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ AFSYS_SPACE E:\ORACLE\ORADATA\AFSYS\AFSYS_SPACE01.DBF 40,713 24 1.8 5.4 264 0 0 E:\ORACLE\ORADATA\AFSYS\AFSYS_SPACE02.DBF 33,505 20 1.4 3.4 138 0 0 E:\ORACLE\ORADATA\AFSYS\AFSYS_SPACE03.DBF 36,529 22 1.4 3.5 87 0 0 SYSTEM E:\ORACLE\ORADATA\AFSYS\SYSTEM01.DBF 1,505 1 1.7 1.2 512 0 0 TEMP E:\ORACLE\ORADATA\AFSYS\TEMP01.DBF 104 0 7.4 6.9 1,219 1 0 UNDOTBS1 E:\ORACLE\ORADATA\AFSYS\UNDOTBS01.DBF 5 0 6.0 1.0 466 0 0 USERS E:\ORACLE\ORADATA\AFSYS\USERS01.DBF 76 0 13.2 1.1 1,079 1 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 63,063 100.0############ 459,257 2,546 0 0 0 ------------------------------------------------------------- rows will be truncated Instance Recovery Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 0 0 24439 24153 184320 24153 E 0 0 14668 14220 184320 14220 ------------------------------------------------------------- Buffer Pool Advisory for DB: AFSYS Instance: afsys End Snap: 6 -> 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 48 .1 6,006 31.81 67,731,113 D 96 .2 12,012 3.19 6,784,760 D 144 .3 18,018 1.63 3,470,522 D 192 .4 24,024 1.42 3,018,827 D 240 .5 30,030 1.30 2,769,571 D 288 .6 36,036 1.24 2,630,527 D 336 .7 42,042 1.19 2,529,046 D 384 .8 48,048 1.14 2,435,220 D 432 .9 54,054 1.13 2,396,418 D 480 1.0 60,060 1.04 2,224,382 D 504 1.0 63,063 1.00 2,129,069 D 528 1.0 66,066 0.99 2,099,217 D 576 1.1 72,072 0.94 2,000,186 D 624 1.2 78,078 0.68 1,450,535 D 672 1.3 84,084 0.60 1,286,292 D 720 1.4 90,090 0.58 1,225,679 D 768 1.5 96,096 0.56 1,198,746 D 816 1.6 102,102 0.56 1,183,214 D 864 1.7 108,108 0.54 1,142,484 D 912 1.8 114,114 0.53 1,133,919 D 960 1.9 120,120 0.49 1,050,614 ------------------------------------------------------------- PGA Aggr Target Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 --------------- ---------------- ------------------------- 63.3 460 267 %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 200 164 38.0 4.6 12.1 100.0 .0 10,240 E 200 164 34.9 0.0 .0 100.0 .0 10,240 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: AFSYS Instance: afsys Snaps: 1 -6 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 9,954 9,954 0 0 16K 32K 168 168 0 0 32K 64K 17 17 0 0 64K 128K 15 15 0 0 128K 256K 80 80 0 0 256K 512K 2 2 0 0 512K 1024K 11 11 0 0 1M 2M 40 40 0 0 2M 4M 10 10 0 0 256M 512M 1 0 1 0 ------------------------------------------------------------- PGA Memory Advisory for DB: AFSYS Instance: afsys End Snap: 6 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 25 0.1 1,152.8 2,531.5 31.0 1 50 0.3 1,152.8 2,513.2 31.0 0 100 0.5 1,152.8 1,434.4 45.0 0 150 0.8 1,152.8 626.6 65.0 0 200 1.0 1,152.8 626.6 65.0 0 240 1.2 1,152.8 626.6 65.0 0 280 1.4 1,152.8 626.6 65.0 0 320 1.6 1,152.8 626.6 65.0 0 360 1.8 1,152.8 626.6 65.0 0 400 2.0 1,152.8 626.6 65.0 0 600 3.0 1,152.8 626.6 65.0 0 800 4.0 1,152.8 626.6 65.0 0 1,200 6.0 1,152.8 626.6 65.0 0 1,600 8.0 1,152.8 626.6 65.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 ->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 10.0 0.00 0 0 0 0 1 862.0 0.00 261,886 0 0 0 2 678.0 0.00 87,202 0 0 0 3 852.0 0.00 319,876 1 0 0 4 283.0 0.00 17,574 0 0 0 5 1,001.0 0.00 297,190 0 0 0 6 843.0 0.00 367,544 1 0 0 7 156.0 0.00 30,594 0 0 0 8 528.0 0.00 244,928 0 0 0 9 884.0 0.00 385,776 1 0 0 10 710.0 0.00 107,464 0 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: AFSYS Instance: afsys Snaps: 1 -6 ->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 96,648 4,317,184 2 2,220,032 167,104 3,268,608 3 2,220,032 260,636 3,268,608 4 2,220,032 185,372 5,365,760 5 2,220,032 96,648 3,268,608 6 2,220,032 191,840 7,462,912 7 2,220,032 11,714 2,285,568 8 2,220,032 167,104 5,431,296 9 2,220,032 191,840 3,268,608 10 2,220,032 96,648 3,268,608 ------------------------------------------------------------- Undo Segment Summary for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 376 258,154 2,770 5 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 -> 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 ------------ ------------ -------- ------- -------- ------- ------ ------------- 28-Sep 09:52 97 86,976 2,770 3 0 0 0/0/0/0/0/0 28-Sep 09:42 76 86,043 13 3 0 0 0/0/0/0/0/0 28-Sep 09:32 203 85,135 254 5 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: AFSYS Instance: afsys Snaps: 1 -6 ->"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 421 0.0 0 0 FOB s.o list latch 54 0.0 0 0 SQL memory manager latch 5 0.0 0 548 0.0 SQL memory manager worka 45,154 0.0 0 0 active checkpoint queue 671 0.0 0 0 archive control 3 0.0 0 0 cache buffer handles 703 0.0 0 0 cache buffers chains 1,172,064,374 0.0 0.0 0 891,125 0.0 cache buffers lru chain 1,005,761 0.0 0.0 0 2,331 0.0 channel handle pool latc 25 0.0 0 0 channel operations paren 1,141 0.0 0 0 checkpoint queue latch 36,640 0.0 0 1,210 0.0 child cursor hash table 4,851 0.0 0 0 dml lock allocation 12,764 0.0 0 0 dummy allocation 123 0.0 0 0 enqueue hash chains 21,786 0.0 0 0 enqueues 4,176 0.0 0 0 event group latch 15 0.0 0 0 file number translation 2,243 0.0 0 0 hash table column usage 23 0.0 0 418 0.0 job_queue_processes para 28 0.0 0 0 ktm global data 5 0.0 0 0 kwqit: protect wakeup ti 54 0.0 0 0 lgwr LWN SCN 699 0.0 0 0 library cache 217,660 0.0 0.1 0 0 library cache load lock 234 0.0 0 0 library cache pin 109,323 0.0 0 0 library cache pin alloca 45,688 0.0 0 0 list of block allocation 117 0.0 0 0 loader state object free 4 0.0 0 0 longop free list parent 1 0.0 0 1 0.0 messages 6,077 0.1 0.0 0 0 mostly latch-free SCN 699 0.0 0 0 multiblock read objects 200,340 0.0 0.0 0 0 ncodef allocation latch 28 0.0 0 0 object stats modificatio 37 0.0 0 0 post/wait queue 843 0.0 0 143 0.0 process allocation 15 0.0 0 15 0.0 process group creation 25 0.0 0 0 redo allocation 17,023 0.0 0 0 redo copy 0 0 15,909 0.0 redo writing 3,036 0.0 0 0 row cache enqueue latch 18,901 0.0 0 0 row cache objects 20,079 0.0 0 0 sequence cache 318 0.0 0 0 session allocation 6,442 0.0 0 0 session idle bit 252,711 0.0 0.0 0 0 session switching 199 0.0 0 0 session timer 586 0.0 0 0 shared pool 79,753 0.0 0.0 0 0 Latch Activity for DB: AFSYS Instance: afsys Snaps: 1 -6 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ simulator hash latch 37,002,910 0.0 0.0 0 0 simulator lru latch 35,448 0.0 0.0 0 287,430 0.0 sort extent pool 290 0.0 0 0 trace latch 6 0.0 0 0 transaction allocation 140 0.0 0 0 transaction branch alloc 28 0.0 0 0 undo global data 11,165 0.0 0 0 user lock 246 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: AFSYS Instance: afsys Snaps: 1 -6 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 1,172,064,374 2,975 49 0/0/0/0/0 cache buffers lru chain 1,005,761 107 2 105/2/0/0/0 library cache 217,660 12 1 11/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: AFSYS Instance: afsys Snaps: 1 -6 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbgtcr: kslbegin excl 0 28 45 cache buffers chains kcbrls: kslbegin 0 13 1 cache buffers chains kcbbxsv 0 2 0 cache buffers chains kcbzib: multi-block read: 0 2 0 cache buffers chains kcbzgb: scan from tail. no 0 2 0 cache buffers chains kcbgtcr: fast path 0 2 1 cache buffers lru chain kcbbiop: lru scan 0 1 0 cache buffers lru chain kcbbxsv: move to being wri 0 1 0 library cache kglic 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: AFSYS Instance: afsys Snaps: 1 -6 ->"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 948 0.0 0 0 507 dc_object_ids 1,742 1.3 0 0 898 dc_objects 1,054 3.4 0 0 1,105 dc_profiles 66 0.0 0 0 1 dc_rollback_segments 210 0.0 0 0 22 dc_segments 2,726 0.8 0 0 803 dc_sequences 9 22.2 0 9 14 dc_tablespace_quotas 2 50.0 0 2 2 dc_tablespaces 34 0.0 0 0 13 dc_user_grants 980 0.1 0 0 20 dc_usernames 777 0.1 0 0 35 dc_users 2,024 0.0 0 0 30 ------------------------------------------------------------- Library Cache Activity for DB: AFSYS Instance: afsys Snaps: 1 -6 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 167 0.0 167 0.0 0 0 CLUSTER 45 0.0 20 0.0 0 0 INDEX 371 0.0 371 0.0 0 0 SQL AREA 7,738 2.9 47,380 1.2 7 0 TABLE/PROCEDURE 3,881 1.0 6,302 2.3 0 0 TRIGGER 446 0.2 446 0.2 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: AFSYS Instance: afsys End Snap: 6 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 200 .5 41 8,264 1,932 1.0 224,718 240 .6 41 8,264 1,932 1.0 224,718 280 .7 41 8,264 1,932 1.0 224,718 320 .8 41 8,264 1,932 1.0 224,718 360 .9 41 8,264 1,932 1.0 224,718 400 1.0 41 8,264 1,932 1.0 224,718 440 1.1 41 8,264 1,932 1.0 224,718 480 1.2 41 8,264 1,932 1.0 224,718 520 1.3 41 8,264 1,932 1.0 224,718 560 1.4 41 8,264 1,932 1.0 224,718 600 1.5 41 8,264 1,932 1.0 224,718 640 1.6 41 8,264 1,932 1.0 224,718 680 1.7 41 8,264 1,932 1.0 224,718 720 1.8 41 8,264 1,932 1.0 224,718 760 1.9 41 8,264 1,932 1.0 224,718 800 2.0 41 8,264 1,932 1.0 224,718 ------------------------------------------------------------- SGA Memory Summary for DB: AFSYS Instance: afsys Snaps: 1 -6 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 528,482,304 Fixed Size 455,620 Redo Buffers 667,648 Variable Size 629,145,600 ---------------- sum 1,158,751,172 ------------------------------------------------------------- SGA breakdown difference for DB: AFSYS Instance: afsys Snaps: 1 -6 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 109,051,904 109,051,904 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 282,304 282,304 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 9,474,788 9,938,684 4.90 shared KQR M PO 1,283,672 1,314,392 2.39 shared KQR S PO 293,396 299,284 2.01 shared KQR S SO 4,864 5,120 5.26 shared KSXR large reply queue 166,104 166,104 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 PL/SQL DIANA 3,331,228 3,410,748 2.39 shared PL/SQL MPCODE 443,164 452,516 2.11 shared PLS non-lib hp 2,068 2,068 0.00 shared VIRTUAL CIRCUITS 265,160 265,160 0.00 shared character set object 279,708 279,708 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared enqueue 171,860 171,860 0.00 shared errors 1,872 1,872 0.00 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 228 228 0.00 shared free memory 372,506,136 365,336,184 -1.92 shared joxs heap init 4,220 4,220 0.00 shared kgl simulator 1,109,524 1,206,952 8.78 shared ksm_file2sga region 148,652 148,652 0.00 shared library cache 9,986,456 11,325,240 13.41 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 6,940,196 7,223,188 4.08 shared parameters 9,396 10,440 11.11 shared processes 144,000 144,000 0.00 shared sessions 410,720 410,720 0.00 shared sim memory hea 283,796 283,796 0.00 shared sql area 19,410,220 24,267,792 25.03 shared table definiti 18,980 20,436 7.67 shared trigger defini 73,600 74,644 1.42 shared trigger inform 1,140 1,140 0.00 shared trigger source 1,408 1,408 0.00 buffer_cache 528,482,304 528,482,304 0.00 fixed_sga 455,620 455,620 0.00 log_buffer 656,384 656,384 0.00 ------------------------------------------------------------- init.ora Parameters for DB: AFSYS Instance: afsys Snaps: 1 -6 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 background_dump_dest E:\oracle\admin\afsys\bdump compatible 9.2.0.0.0 control_files E:\oracle\oradata\afsys\CONTROL01 core_dump_dest E:\oracle\admin\afsys\cdump db_block_size 8192 db_cache_size 528482304 db_domain db_file_multiblock_read_count 16 db_name afsys dispatchers (PROTOCOL=TCP) (SERVICE=afsysXDB) fast_start_mttr_target 0 hash_join_enabled TRUE instance_name afsys java_pool_size 33554432 job_queue_processes 10 large_pool_size 109051904 open_cursors 300 pga_aggregate_target 209715200 processes 150 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE sga_max_size 1158751172 shared_pool_size 419430400 sort_area_size 524288 star_transformation_enabled FALSE timed_statistics TRUE undo_management AUTO undo_retention 10800 undo_tablespace UNDOTBS1 user_dump_dest E:\oracle\admin\afsys\udump ------------------------------------------------------------- End of Report