STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ WSPROD 1599102205 wsprod 1 9.2.0.6.0 NO prod.worldsp ace.com Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 13 27-Jun-06 21:41:35 231 182.7 End Snap: 14 28-Jun-06 12:53:14 159 309.1 Elapsed: 911.65 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 3,120M Std Block Size: 8K Shared Pool Size: 736M Log Buffer: 195,313K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 97,028.10 44,213.10 Logical reads: 7,558.36 3,444.14 Block changes: 555.24 253.01 Physical reads: 23.09 10.52 Physical writes: 15.88 7.23 User calls: 316.54 144.24 Parses: 100.42 45.76 Hard parses: 2.52 1.15 Sorts: 10.29 4.69 Logons: 0.14 0.06 Executes: 135.79 61.88 Transactions: 2.19 % Blocks changed per Read: 7.35 Recursive Call %: 24.28 Rollback per transaction %: 4.09 Rows per Sort: 5.34 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.84 Redo NoWait %: 99.49 Buffer Hit %: 99.69 In-memory Sort %: 100.00 Library Hit %: 98.73 Soft Parse %: 97.49 Execute to Parse %: 26.05 Latch Hit %: 98.71 Parse CPU to Parse Elapsd %: 37.51 % Non-Parse CPU: 98.33 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 94.49 83.44 % SQL with executions>1: 8.21 16.50 % Memory for SQL w/exec>1: 13.85 32.83 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- db file parallel write 51,765 223,845 28.18 buffer busy waits 664,830 163,009 20.52 enqueue 47,901 96,573 12.16 log file sync 246,565 93,624 11.78 log file switch (checkpoint incomplete) 63,859 62,940 7.92 ------------------------------------------------------------- Wait Events for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 parallel write 51,765 0 223,845 4324 0.4 buffer busy waits 664,830 47,658 163,009 245 5.5 enqueue 47,901 26,064 96,573 2016 0.4 log file sync 246,565 44,859 93,624 380 2.1 log file switch (checkpoint 63,859 63,798 62,940 986 0.5 db file sequential read 295,017 0 52,759 179 2.5 log file parallel write 324,687 0 32,856 101 2.7 log file switch completion 18,365 17,861 17,822 970 0.2 SQL*Net message from dblink 154,752 0 12,135 78 1.3 control file parallel write 16,136 0 7,638 473 0.1 async disk IO 181,411 0 5,082 28 1.5 db file scattered read 75,960 0 2,032 27 0.6 direct path write (lob) 86 0 1,976 22980 0.0 direct path write 437 0 1,910 4370 0.0 row cache lock 143 140 413 2886 0.0 control file sequential read 180,207 0 352 2 1.5 library cache lock 54 53 156 2893 0.0 latch free 26,317 15,133 151 6 0.2 local write wait 134 131 130 968 0.0 log file sequential read 5,463 0 111 20 0.0 library cache load lock 26 8 36 1397 0.0 SQL*Net more data to client 988,205 0 30 0 8.2 process startup 568 0 15 26 0.0 SQL*Net more data to dblink 270,214 0 8 0 2.3 direct path read 420 0 7 17 0.0 direct path read (lob) 261 0 6 24 0.0 log file single write 74 0 6 85 0.0 SQL*Net break/reset to clien 3,536 0 6 2 0.0 db file single write 12 0 5 409 0.0 undo segment extension 11,384,553 ########## 3 0 94.8 switch logfile command 5 0 2 415 0.0 control file single write 5 0 1 221 0.0 LGWR wait for redo copy 5,579 6 1 0 0.0 library cache pin 102 0 0 5 0.0 SQL*Net message to dblink 154,752 0 0 0 1.3 SQL*Net more data from dblin 20 0 0 1 0.0 SQL*Net break/reset to dblin 36 0 0 0 0.0 buffer deadlock 118 118 0 0 0.0 kksfbc child completion 1 0 0 0 0.0 SQL*Net message from client 17,833,023 0 6,333,537 355 148.6 virtual circuit status 1,838 1,838 53,693 29213 0.0 wakeup time manager 1,492 1,491 43,390 29082 0.0 jobq slave wait 12,556 11,983 36,362 2896 0.1 SQL*Net more data from clien 838,119 0 39 0 7.0 SQL*Net message to client 17,832,965 0 9 0 148.6 ------------------------------------------------------------- Background Wait Events for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file parallel write 51,765 0 223,845 4324 0.4 log file parallel write 324,687 0 32,856 101 2.7 control file parallel write 15,755 0 7,604 483 0.1 buffer busy waits 7,296 7,235 7,139 979 0.1 async disk IO 10,217 0 1,953 191 0.1 direct path write 420 0 1,904 4534 0.0 enqueue 2,891 458 1,753 606 0.0 rdbms ipc reply 998 184 373 374 0.0 control file sequential read 170,628 0 322 2 1.4 db file scattered read 546 0 265 486 0.0 db file sequential read 680 0 167 246 0.0 log file sequential read 5,340 0 95 18 0.0 direct path read 420 0 7 17 0.0 log file single write 74 0 6 85 0.0 LGWR wait for redo copy 5,579 6 1 0 0.0 latch free 37 0 0 0 0.0 rdbms ipc message 286,026 49,810 389,732 1363 2.4 pmon timer 22,274 18,422 53,782 2415 0.2 smon timer 296 102 44,709 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 109,211,066 24,027 4,545.3 26.4 4716.27 4976.20 4121898293 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select service_obj_DB, service_obj_ID0, service_obj_TYPE, ser vice_obj_REV, item_no, name, item_total, due_t, account_obj_DB, account_obj_ID0, account_obj_TYPE, account_obj_REV, billinfo_obj _DB, billinfo_obj_ID0, billinfo_obj_TYPE, billinfo_obj_REV, ar_b illinfo_obj_DB, ar_billinfo_obj_ID0, ar_billinfo_obj_TYPE, ar_bi 79,353,642 10,672 7,435.7 19.2 3124.01 3518.87 803850538 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct poid_DB, poid_ID0, poid_TYPE, poid_REV from ac count_t where account_t.name = :1 43,903,912 1,501,778 29.2 10.6 888.70 ######### 2388095759 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) insert into SYSTEM.DEF$_AQCALL (q_name, msgid, corrid, priority , state, delay, expiration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid, retry_count, e xception_qschema, exception_queue, recipient_key, dequeue_msgi d, user_data) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, 39,717,265 5,340 7,437.7 9.6 3357.46 4255.40 2700627409 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct poid_DB, poid_ID0, poid_TYPE, poid_REV from ac count_t where ( ( account_t.read_access = :1 AND account_t.poi d_ID0 = :2 ) OR ( account_t.read_access = :3 AND account_t.bran d_obj_ID0 = :4 AND account_t.lineage LIKE :5 ) OR ( account_t.r ead_access = :6 AND account_t.brand_obj_ID0 = :7 ) OR ( account 19,912,787 18,512 1,075.7 4.8 127.79 4445.27 100427747 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) update bill_t set poid_rev = poid_rev + 1, mod_t = :mod_t, due_t =:due_t, bill_no=:bill_no where poid_id0 = :poid_id0 19,357,945 38,597 501.5 4.7 86.08 2592.16 441576714 UPDATE SYSTEM.DEF$_DESTINATION SET LAST_DELIVERED=:b1,LAST_ENQ_T ID=:b2 WHERE ROWID = :b3 15,514,718 21,187 732.3 3.8 119.48 3857.92 2224799193 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) update bill_t set poid_rev = poid_rev + 1, mod_t = :mod_t, accou nt_obj_DB=:account_obj_DB, account_obj_ID0=:account_obj_ID0, acc ount_obj_TYPE=:account_obj_TYPE, account_obj_REV=:account_obj_RE V, billinfo_obj_DB=:billinfo_obj_DB, billinfo_obj_ID0=:billinfo_ obj_ID0, billinfo_obj_TYPE=:billinfo_obj_TYPE, billinfo_obj_REV= 15,039,552 21,186 709.9 3.6 137.32 4308.15 3014338978 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) update billinfo_t set poid_rev = poid_rev + 1, mod_t = :mod_t, n ext_bill_t=:next_bill_t, future_bill_t=:future_bill_t, last_bill SQL ordered by Gets for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- _t=:last_bill_t, actual_last_bill_t=:actual_last_bill_t, actual_ last_bill_obj_DB=:actual_last_bill_obj_DB, actual_last_bill_obj_ ID0=:actual_last_bill_obj_ID0, actual_last_bill_obj_TYPE=:actual 9,891,415 40,151 246.4 2.4 242.46 8230.96 3034007233 delete from system.def$_aqcall where (enq_tid = :1) 8,768,083 38,597 227.2 2.1 83.28 7469.19 587869871 SELECT /*+ ORDERED USE_NL(P) */ aq.step_no, P.sname, P.oname, aq .chain_no, aq.user_data FROM system.def$_aqcall aq, system.repca t$_repprop P WHERE aq.enq_tid = :tid AND P.recipient_key = aq.r ecipient_key AND P.how = 1 AND P.dblink = :dest AND P.extension_ id = :extid ORDER BY aq.enq_tid, aq.step_no 4,612,485 53,348 86.5 1.1 37.87 40.14 421674144 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct account_t.lineage, account_t.brand_obj_DB, acc ount_t.brand_obj_ID0, account_t.brand_obj_TYPE, account_t.brand_ obj_REV, account_t.poid_DB, account_t.poid_ID0, account_t.poid_T YPE, account_t.poid_REV from account_t ,service_t where servic e_t.poid_ID0 = :1 AND account_t.poid_ID0 = service_t.account_ob 4,277,807 36 118,828.0 1.0 108.43 637.74 2790358827 INSERT INTO SYSTEM.DEF$_CALLDEST ( ENQ_TID,STEP_NO,DBLINK,SCHEMA _NAME,PACKAGE_NAME,CATCHUP ) SELECT /*+ ORDERED */:b1,D.CALLN O,D.DBLINK,C.SCHEMANAME,C.PACKAGENAME,P.EXTENSION_ID FROM DEFC ALLDEST D,DEFCALL C,SYSTEM.REPCAT$_REPPROP P WHERE D.DEFERRED_T RAN_ID = :b1 AND C.DEFERRED_TRAN_ID = :b1 AND C.DEFERRED_TRAN_ 2,037,390 36 56,594.2 0.5 45.68 54.94 2622626546 SELECT 1 FROM DEFTRANDEST WHERE DEFTRANDEST.DEFERRED_TRAN_ID = :b1 AND DEFTRANDEST.DBLINK = :b2 1,947,469 26,663 73.0 0.5 19.19 23.40 74079205 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct billinfo_t.poid_DB, billinfo_t.poid_ID0, billi nfo_t.poid_TYPE, billinfo_t.poid_REV, billinfo_t.business_profil e_obj_DB, billinfo_t.business_profile_obj_ID0, billinfo_t.busine ss_profile_obj_TYPE, billinfo_t.business_profile_obj_REV from bi llinfo_t ,bal_grp_t ,account_t where ( ( billinfo_t.account_ob 1,546,945 120,228 12.9 0.4 78.78 5282.53 3841387224 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) insert into au_account_phones_t ( phone, type, rec_id2, rec_id, obj_id0 ) values ( :phone, :type, :rec_id2, :rec_id, :obj_id0 ) 1,531,554 37,357 41.0 0.4 15.20 17.58 3962149041 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct lineage, brand_obj_DB, brand_obj_ID0, brand_ob j_TYPE, brand_obj_REV, poid_DB, poid_ID0, poid_TYPE, poid_REV fr SQL ordered by Gets for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- om account_t where account_t.poid_ID0 = :1 1,382,383 74,608 18.5 0.3 78.56 16715.87 3922307075 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update account_t set poid_rev = poid_rev + 1, mod_t = :mod_t w here poid_id0 = :poid_id0 1,240,156 53,030 23.4 0.3 49.93 5069.38 801087864 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update service_t set poid_rev = poid_rev + 1, mod_t = :mod_t, it em_poid_list=:item_poid_list, next_item_poid_list=:next_item_poi d_list where poid_id0 = :poid_id0 ------------------------------------------------------------- SQL ordered by Reads for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 216,319 3 72,106.3 17.1 71.48 443.33 2895987827 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."EVENT_T".* FROM "PIN". "EVENT_T" PARTITION ("P_R_07072006") 96,901 1,501,778 0.1 7.7 888.70 ######### 2388095759 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) insert into SYSTEM.DEF$_AQCALL (q_name, msgid, corrid, priority , state, delay, expiration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid, retry_count, e xception_qschema, exception_queue, recipient_key, dequeue_msgi d, user_data) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, 62,546 38,597 1.6 5.0 83.28 7469.19 587869871 SELECT /*+ ORDERED USE_NL(P) */ aq.step_no, P.sname, P.oname, aq .chain_no, aq.user_data FROM system.def$_aqcall aq, system.repca t$_repprop P WHERE aq.enq_tid = :tid AND P.recipient_key = aq.r ecipient_key AND P.how = 1 AND P.dblink = :dest AND P.extension_ id = :extid ORDER BY aq.enq_tid, aq.step_no 52,364 3 17,454.7 4.1 6.62 42.53 3140042682 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."INVOICE_FORMATS_BUF_T" .* FROM "PIN"."INVOICE_FORMATS_BUF_T" 44,780 3 14,926.7 3.5 11.76 185.45 2766379292 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."SCHEDULE_INPUT_FLIST_B UF".* FROM "PIN"."SCHEDULE_INPUT_FLIST_BUF" 44,664 3 14,888.0 3.5 13.29 65.08 3286389014 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_SERVICE_T".* FROM " PIN"."AU_SERVICE_T" 32,405 3 10,801.7 2.6 10.07 53.21 1308081855 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_ACCOUNT_T".* FROM " PIN"."AU_ACCOUNT_T" 26,644 3 8,881.3 2.1 10.82 82.55 556563478 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."EVENT_CUSTOMER_BILLINF O_T".* FROM "PIN"."EVENT_CUSTOMER_BILLINFO_T" PARTITION ("P_R_0 7072006") 25,707 3 8,569.0 2.0 11.71 95.78 2756998499 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."SERVICE_T".* FROM "PIN "."SERVICE_T" 24,562 3 8,187.3 1.9 7.78 105.33 1833687368 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."EVENT_T".* FROM "PIN". SQL ordered by Reads for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- "EVENT_T" PARTITION ("PARTITION_HISTORIC") 23,874 3 7,958.0 1.9 7.54 61.38 307349908 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."EVENT_T".* FROM "PIN". "EVENT_T" PARTITION ("P_R_06072006") 20,011 3 6,670.3 1.6 7.75 30.94 2612013378 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."BILLINFO_T".* FROM "PI N"."BILLINFO_T" 16,420 3 5,473.3 1.3 5.90 24.32 952801085 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_ACCOUNT_NAMEINFO_T" .* FROM "PIN"."AU_ACCOUNT_NAMEINFO_T" 15,442 3 5,147.3 1.2 7.47 22.34 1730380606 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_ACCOUNT_PHONES_T".* FROM "PIN"."AU_ACCOUNT_PHONES_T" 14,844 36 412.3 1.2 6.93 1183.54 3645256634 SELECT /*+ FIRST_ROWS INDEX(A, DEF$_TRANORDER) */A.ROWID RID,A .ENQ_TID,DECODE(A.RECIPIENT_KEY,0,'D','R') DESTINATION_LIST,A.CS CN FROM SYSTEM.DEF$_AQCALL A WHERE A.CSCN IS NOT NULL AND A. CSCN >= :b1 AND A.CSCN < :b2 AND (A.CSCN > :b1 OR (A.CSCN = : b1 AND (A.ENQ_TID > :b5 ))) AND ((A.RECIPIENT_KEY = 0 AND EXIS 14,518 3 4,839.3 1.1 5.04 17.48 760046638 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_ACCOUNT_PRODUCTS_T" .* FROM "PIN"."AU_ACCOUNT_PRODUCTS_T" 12,579 3 4,193.0 1.0 3.58 23.74 1393574394 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."AU_BAL_GRP_T".* FROM " PIN"."AU_BAL_GRP_T" 12,127 3 4,042.3 1.0 4.39 11.74 2579064776 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."ACCOUNT_NAMEINFO_T".* FROM "PIN"."ACCOUNT_NAMEINFO_T" 10,151 33,952 0.3 0.8 12.66 1203.13 13206605 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select event_t.poid_DB, event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV from event_t ,event_billing_product_t ,event_pr oduct_fee_cycle_t where event_t.account_obj_ID0 = :1 and ev ent_billing_product_t.node_location = :2 and event_t.poid_TYPE l ike :3 and event_t.end_t = :4 and event_product_fee_cycle_t.fla 9,963 3 3,321.0 0.8 5.23 19.69 2578583902 Module: exp@prod.worldspace.com (TNS V1-V3) SQL ordered by Reads for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."ACCOUNT_PHONES_T".* FR OM "PIN"."ACCOUNT_PHONES_T" 8,739 10,672 0.8 0.7 3124.01 3518.87 803850538 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct poid_DB, poid_ID0, poid_TYPE, poid_REV from ac count_t where account_t.name = :1 8,649 3 2,883.0 0.7 3.28 8.36 519346672 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."PAYINFO_T".* FROM "PIN "."PAYINFO_T" 8,551 3 2,850.3 0.7 2.82 13.94 4131091961 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."BAL_GRP_T".* FROM "PIN "."BAL_GRP_T" 8,526 3 2,842.0 0.7 3.51 14.80 491462007 Module: exp@prod.worldspace.com (TNS V1-V3) SELECT /*+NESTED_TABLE_GET_REFS+*/ "PIN"."PAYINFO_INV_T".* FROM "PIN"."PAYINFO_INV_T" 7,522 21,077 0.4 0.6 36.29 1176.78 4090767290 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) insert into event_billing_tax_cycles_t ( start_t, end_t, currenc y, bill_vat, bill_obj_DB, bill_obj_ID0, bill_obj_TYPE, bill_obj_ REV, rec_id, obj_id0 ) values ( :start_t, :end_t, :currency, :bi ------------------------------------------------------------- SQL ordered by Executions for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 1,501,778 1,501,909 1.0 0.00 0.08 2388095759 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) insert into SYSTEM.DEF$_AQCALL (q_name, msgid, corrid, priority , state, delay, expiration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_uid, enq_tid, retry_count, e xception_qschema, exception_queue, recipient_key, dequeue_msgi d, user_data) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, 273,576 174,763 0.6 0.00 0.00 2578817543 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, charged_from_t, charged_to_t, count, cycle_fee_en d_t, cycle_fee_start_t, unit from account_cycle_fees_t where obj _id0 = :1 and rec_id2 = :2 120,228 120,229 1.0 0.00 0.04 3841387224 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) insert into au_account_phones_t ( phone, type, rec_id2, rec_id, obj_id0 ) values ( :phone, :type, :rec_id2, :rec_id, :obj_id0 ) 104,745 0 0.0 0.00 0.00 4290459425 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select rec_id, created_t, cycle_end_t, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, descr, discount_ob j_DB, discount_obj_ID0, discount_obj_TYPE, discount_obj_REV, las t_modified_t, node_location, plan_obj_DB, plan_obj_ID0, plan_obj _TYPE, plan_obj_REV, purchase_end_t, purchase_start_t, quantity, 104,737 200,189 1.9 0.00 0.01 2796020384 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select rec_id, created_t, cycle_discount, cycle_disc_amt, cycle_ end_t, cycle_fee_amt, cycle_fee_flags, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, descr, flags, last _modified_t, mmc_type, node_location, plan_obj_DB, plan_obj_ID0, plan_obj_TYPE, plan_obj_REV, product_obj_DB, product_obj_ID0, p 74,608 74,610 1.0 0.00 0.22 3922307075 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update account_t set poid_rev = poid_rev + 1, mod_t = :mod_t w here poid_id0 = :poid_id0 73,876 73,875 1.0 0.00 0.00 4051557625 update SYSTEM.DEF$_AQCALL set dscn = :1, cscn = :2 where rowid = :3 73,381 0 0.0 0.00 0.00 4142380815 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) set transaction read write 68,544 0 0.0 0.00 0.00 3867936055 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) commit 64,350 0 0.0 0.00 0.00 2095203105 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) SQL ordered by Executions for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- select rec_id, account_obj_DB, account_obj_ID0, account_obj_TYPE , account_obj_REV, effective_t, item_poid_list, next_item_poid_l ist from service_transfer_list_t where obj_id0 = :1 63,931 63,926 1.0 0.00 0.00 4228202957 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, created_t, last_modified_t, cycle_discount, cycle _disc_amt, cycle_fee_amt, cycle_end_t, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, node_location, fla gs, cycle_fee_flags, plan_obj_DB, plan_obj_ID0, plan_obj_TYPE, p lan_obj_REV, product_obj_DB, product_obj_ID0, product_obj_TYPE, 63,930 63,926 1.0 0.00 0.00 4146166433 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select distinct account_t.poid_DB, account_t.poid_ID0, account _t.poid_TYPE, account_t.poid_REV from account_t ,account_product s_t where account_t.poid_ID0 = :1 and account_products_t.node _location = :2 and account_t.poid_id0 = account_products_t.obj_ id0 56,793 56,794 1.0 0.00 0.00 3944725134 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select poid_DB, poid_ID0, poid_TYPE, poid_REV from account_t whe re account_t.poid_id0 = :1 56,408 56,412 1.0 0.00 0.04 3437683357 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) update account_products_t set flags=:flags, cycle_fee_flags=:cyc le_fee_flags where obj_id0 = :obj_id0 and rec_id = :rec_id 55,211 55,211 1.0 0.00 0.01 2655379500 Module: SQL*Plus UPDATE SCHEDULE_T SET STATUS = 9999 WHERE POID_ID0 = :B1 54,643 0 0.0 0.00 0.00 281501090 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, event_type from product_rum_map_t where obj_id0 = :1 54,642 54,642 1.0 0.00 0.00 914570021 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, event_type, flags, incr_quantity, incr_unit, min_ quantity, min_unit, rate_plan_name, selector_obj_DB, selector_ob j_ID0, selector_obj_TYPE, selector_obj_REV, rounding_mode, rum_n ame, timezone_mode, tod_mode from product_usage_map_t where obj_ id0 = :1 53,348 53,348 1.0 0.00 0.00 421674144 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct account_t.lineage, account_t.brand_obj_DB, acc ount_t.brand_obj_ID0, account_t.brand_obj_TYPE, account_t.brand_ obj_REV, account_t.poid_DB, account_t.poid_ID0, account_t.poid_T YPE, account_t.poid_REV from account_t ,service_t where servic e_t.poid_ID0 = :1 AND account_t.poid_ID0 = service_t.account_ob SQL ordered by Executions for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 53,232 53,232 1.0 0.00 0.00 3669788131 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select poid_DB, poid_ID0, poid_TYPE, poid_REV, created_t, mod_t, read_access, write_access, account_obj_DB, account_obj_ID0, acc ount_obj_TYPE, account_obj_REV, descr, end_t, name, own_max, own _min, partial, permitted, priority, provisioning_tag, purchase_m ax, purchase_min, start_t, tax_supplier_id_DB, tax_supplier_id_I 53,030 53,030 1.0 0.00 0.10 801087864 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update service_t set poid_rev = poid_rev + 1, mod_t = :mod_t, it em_poid_list=:item_poid_list, next_item_poid_list=:next_item_poi d_list where poid_id0 = :poid_id0 ------------------------------------------------------------- SQL ordered by Parse Calls for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 273,492 273,576 4.98 2578817543 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, charged_from_t, charged_to_t, count, cycle_fee_en d_t, cycle_fee_start_t, unit from account_cycle_fees_t where obj _id0 = :1 and rec_id2 = :2 120,234 120,228 2.19 3841387224 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) insert into au_account_phones_t ( phone, type, rec_id2, rec_id, obj_id0 ) values ( :phone, :type, :rec_id2, :rec_id, :obj_id0 ) 104,749 104,745 1.91 4290459425 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select rec_id, created_t, cycle_end_t, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, descr, discount_ob j_DB, discount_obj_ID0, discount_obj_TYPE, discount_obj_REV, las t_modified_t, node_location, plan_obj_DB, plan_obj_ID0, plan_obj _TYPE, plan_obj_REV, purchase_end_t, purchase_start_t, quantity, 104,743 104,737 1.91 2796020384 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select rec_id, created_t, cycle_discount, cycle_disc_amt, cycle_ end_t, cycle_fee_amt, cycle_fee_flags, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, descr, flags, last _modified_t, mmc_type, node_location, plan_obj_DB, plan_obj_ID0, plan_obj_TYPE, plan_obj_REV, product_obj_DB, product_obj_ID0, p 74,614 74,608 1.36 3922307075 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update account_t set poid_rev = poid_rev + 1, mod_t = :mod_t w here poid_id0 = :poid_id0 73,855 73,876 1.34 4051557625 update SYSTEM.DEF$_AQCALL set dscn = :1, cscn = :2 where rowid = :3 73,380 73,381 1.34 4142380815 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) set transaction read write 68,532 68,544 1.25 3867936055 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) commit 64,346 64,350 1.17 2095203105 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select rec_id, account_obj_DB, account_obj_ID0, account_obj_TYPE , account_obj_REV, effective_t, item_poid_list, next_item_poid_l ist from service_transfer_list_t where obj_id0 = :1 63,931 63,930 1.16 4146166433 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select distinct account_t.poid_DB, account_t.poid_ID0, account _t.poid_TYPE, account_t.poid_REV from account_t ,account_product SQL ordered by Parse Calls for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- s_t where account_t.poid_ID0 = :1 and account_products_t.node _location = :2 and account_t.poid_id0 = account_products_t.obj_ id0 63,920 63,931 1.16 4228202957 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, created_t, last_modified_t, cycle_discount, cycle _disc_amt, cycle_fee_amt, cycle_end_t, cycle_start_t, deal_obj_D B, deal_obj_ID0, deal_obj_TYPE, deal_obj_REV, node_location, fla gs, cycle_fee_flags, plan_obj_DB, plan_obj_ID0, plan_obj_TYPE, p lan_obj_REV, product_obj_DB, product_obj_ID0, product_obj_TYPE, 56,796 56,793 1.03 3944725134 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select poid_DB, poid_ID0, poid_TYPE, poid_REV from account_t whe re account_t.poid_id0 = :1 56,415 56,408 1.03 3437683357 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) update account_products_t set flags=:flags, cycle_fee_flags=:cyc le_fee_flags where obj_id0 = :obj_id0 and rec_id = :rec_id 54,643 54,643 0.99 281501090 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, event_type from product_rum_map_t where obj_id0 = :1 54,643 54,642 0.99 914570021 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select rec_id, event_type, flags, incr_quantity, incr_unit, min_ quantity, min_unit, rate_plan_name, selector_obj_DB, selector_ob j_ID0, selector_obj_TYPE, selector_obj_REV, rounding_mode, rum_n ame, timezone_mode, tod_mode from product_usage_map_t where obj_ id0 = :1 53,348 53,348 0.97 421674144 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) select distinct account_t.lineage, account_t.brand_obj_DB, acc ount_t.brand_obj_ID0, account_t.brand_obj_TYPE, account_t.brand_ obj_REV, account_t.poid_DB, account_t.poid_ID0, account_t.poid_T YPE, account_t.poid_REV from account_t ,service_t where servic e_t.poid_ID0 = :1 AND account_t.poid_ID0 = service_t.account_ob 53,232 53,232 0.97 3669788131 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select poid_DB, poid_ID0, poid_TYPE, poid_REV, created_t, mod_t, read_access, write_access, account_obj_DB, account_obj_ID0, acc ount_obj_TYPE, account_obj_REV, descr, end_t, name, own_max, own _min, partial, permitted, priority, provisioning_tag, purchase_m ax, purchase_min, start_t, tax_supplier_id_DB, tax_supplier_id_I 53,030 53,030 0.97 801087864 Module: dm_oracle@oracle.worldspace.com (TNS V1-V3) update service_t set poid_rev = poid_rev + 1, mod_t = :mod_t, it SQL ordered by Parse Calls for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- em_poid_list=:item_poid_list, next_item_poid_list=:next_item_poi d_list where poid_id0 = :poid_id0 52,234 52,222 0.95 1629223684 insert into system.def$_lob (id, enq_tid, blob_col, clob_col, nc lob_col) values (:1, :2, empty_blob(), NULL, NULL) 52,233 52,222 0.95 1946413742 select blob_col from system.def$_lob where id = :1 for update 52,230 52,195 0.95 117317804 begin sys.dbms_lob.copy(:dlob, :slob, :amount, 1, :soffset); end ; 44,608 44,604 0.81 98688242 Module: dm_oracle@app1.worldspace.com (TNS V1-V3) select poid_DB, poid_ID0, poid_TYPE, poid_REV, account_obj_DB, a ------------------------------------------------------------- Instance Activity Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 1,740,818 31.8 14.5 CPU used when call started 1,730,065 31.6 14.4 CR blocks created 1,743,598 31.9 14.5 DBWR buffers scanned 675,918 12.4 5.6 DBWR checkpoint buffers written 809,006 14.8 6.7 DBWR checkpoints 38 0.0 0.0 DBWR free buffers found 599,602 11.0 5.0 DBWR lru scans 167 0.0 0.0 DBWR make free requests 167 0.0 0.0 DBWR revisited being-written buff 29,496 0.5 0.3 DBWR summed scan depth 675,918 12.4 5.6 DBWR transaction table writes 942 0.0 0.0 DBWR undo block writes 308,405 5.6 2.6 OS All other sleep time 6,203,679,900 113,414.9 51,680.1 OS Chars read and written 7,362,287,849 134,596.4 61,332.0 OS Data page fault sleep time 124 0.0 0.0 OS Input blocks 850,057 15.5 7.1 OS Involuntary context switches 2,404,061 44.0 20.0 OS Kernel page fault sleep time 1,019 0.0 0.0 OS Major page faults 471,272 8.6 3.9 OS Messages sent 3,813 0.1 0.0 OS Other system trap CPU time 623 0.0 0.0 OS Output blocks 3,168,211 57.9 26.4 OS Process heap size 3,900,325,888 71,305.3 32,491.9 OS Process stack size 891,822,080 16,304.2 7,429.4 OS Signals received 37 0.0 0.0 OS System call CPU time 258,941 4.7 2.2 OS System calls 281,761,559 5,151.1 2,347.2 OS Text page fault sleep time 657 0.0 0.0 OS User level CPU time 1,758,720 32.2 14.7 OS User lock wait sleep time 5,120,856,483 93,618.8 42,659.6 OS Voluntary context switches 27,269,613 498.5 227.2 OS Wait-cpu (latency) time 167,721 3.1 1.4 SQL*Net roundtrips to/from client 17,818,728 325.8 148.4 SQL*Net roundtrips to/from dblink 154,753 2.8 1.3 active txn count during cleanout 10,578,096 193.4 88.1 background checkpoints completed 35 0.0 0.0 background checkpoints started 37 0.0 0.0 background timeouts 73,198 1.3 0.6 branch node splits 39 0.0 0.0 buffer is not pinned count 269,629,348 4,929.3 2,246.2 buffer is pinned count 1,912,876,286 34,971.0 15,935.3 bytes received via SQL*Net from c 6,011,491,782 109,901.3 50,079.1 bytes received via SQL*Net from d 8,037,855 147.0 67.0 bytes sent via SQL*Net to client 10,543,421,596 192,753.5 87,832.6 bytes sent via SQL*Net to dblink 613,115,042 11,208.9 5,107.6 calls to get snapshot scn: kcmgss 26,113,173 477.4 217.5 calls to kcmgas 2,071,462 37.9 17.3 calls to kcmgcs 3,046,638 55.7 25.4 change write time 75,841 1.4 0.6 cleanout - number of ktugct calls 4,572,459 83.6 38.1 cleanouts and rollbacks - consist 1,630,237 29.8 13.6 cleanouts only - consistent read 10,649 0.2 0.1 cluster key scan block gets 932,574 17.1 7.8 cluster key scans 471,182 8.6 3.9 commit cleanout failures: buffer 2,842 0.1 0.0 Instance Activity Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ commit cleanout failures: callbac 1,652 0.0 0.0 commit cleanout failures: cannot 6,454 0.1 0.1 commit cleanouts 3,317,503 60.7 27.6 commit cleanouts successfully com 3,306,555 60.5 27.6 commit txn count during cleanout 374,147 6.8 3.1 consistent changes 24,341,533 445.0 202.8 consistent gets 316,192,212 5,780.6 2,634.1 consistent gets - examination 50,427,728 921.9 420.1 current blocks converted for CR 0 0.0 0.0 cursor authentications 3,122 0.1 0.0 data blocks consistent reads - un 24,326,502 444.7 202.7 db block changes 30,371,006 555.2 253.0 db block gets 97,242,984 1,777.8 810.1 deferred (CURRENT) block cleanout 4,642,912 84.9 38.7 dirty buffers inspected 36,389 0.7 0.3 enqueue conversions 193,654 3.5 1.6 enqueue deadlocks 0 0.0 0.0 enqueue releases 3,147,306 57.5 26.2 enqueue requests 3,147,542 57.5 26.2 enqueue timeouts 386 0.0 0.0 enqueue waits 18,915 0.4 0.2 exchange deadlocks 118 0.0 0.0 execute count 7,427,728 135.8 61.9 free buffer inspected 44,610 0.8 0.4 free buffer requested 3,355,965 61.4 28.0 hot buffers moved to head of LRU 535,017 9.8 4.5 immediate (CR) block cleanout app 1,640,886 30.0 13.7 immediate (CURRENT) block cleanou 257,595 4.7 2.2 index fast full scans (full) 73 0.0 0.0 index fetch by key 4,291,106 78.5 35.8 index scans kdiixs1 6,642,789 121.4 55.3 leaf node 90-10 splits 178 0.0 0.0 leaf node splits 24,998 0.5 0.2 logons cumulative 7,549 0.1 0.1 messages received 241,362 4.4 2.0 messages sent 241,369 4.4 2.0 no buffer to keep pinned count 1 0.0 0.0 no work - consistent read gets 257,036,055 4,699.1 2,141.3 opened cursors cumulative 605,959 11.1 5.1 parse count (failures) 1,457 0.0 0.0 parse count (hard) 137,658 2.5 1.2 parse count (total) 5,492,622 100.4 45.8 parse time cpu 29,084 0.5 0.2 parse time elapsed 77,532 1.4 0.7 physical reads 1,263,267 23.1 10.5 physical reads direct 420 0.0 0.0 physical reads direct (lob) 267 0.0 0.0 physical writes 868,431 15.9 7.2 physical writes direct 845 0.0 0.0 physical writes direct (lob) 88 0.0 0.0 physical writes non checkpoint 438,496 8.0 3.7 pinned buffers inspected 5 0.0 0.0 prefetched blocks 891,648 16.3 7.4 process last non-idle time 47,902 0.9 0.4 recovery blocks read 0 0.0 0.0 recursive calls 5,551,354 101.5 46.3 Instance Activity Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ recursive cpu usage 144,040 2.6 1.2 redo blocks written 10,796,334 197.4 89.9 redo buffer allocation retries 677 0.0 0.0 redo entries 16,192,254 296.0 134.9 redo log space requests 82,611 1.5 0.7 redo log space wait time 8,270,920 151.2 68.9 redo ordering marks 63 0.0 0.0 redo size 5,307,340,108 97,028.1 44,213.1 redo synch time 8,938,427 163.4 74.5 redo synch writes 197,913 3.6 1.7 redo wastage 46,739,192 854.5 389.4 redo write time 3,404,057 62.2 28.4 redo writer latching time 109 0.0 0.0 redo writes 162,343 3.0 1.4 rollback changes - undo records a 349,602 6.4 2.9 rollbacks only - consistent read 127,669 2.3 1.1 rows fetched via callback 7,340,882 134.2 61.2 session connect time 0 0.0 0.0 session logical reads 413,434,817 7,558.4 3,444.1 session pga memory 255,200,960 4,665.6 2,126.0 session pga memory max 318,872,656 5,829.6 2,656.4 session uga memory 1,743,851,376,216 31,880,863.9 ############ session uga memory max 1,458,626,616 26,666.4 12,151.2 shared hash latch upgrades - no w 6,522,347 119.2 54.3 shared hash latch upgrades - wait 5,284 0.1 0.0 sorts (memory) 563,045 10.3 4.7 sorts (rows) 3,005,138 54.9 25.0 summed dirty queue length 1,722,720 31.5 14.4 switch current to new buffer 80,448 1.5 0.7 table fetch by rowid 1,023,001,635 18,702.4 8,522.2 table fetch continued row 116,408 2.1 1.0 table scan blocks gotten 127,176,098 2,325.0 1,059.5 table scan rows gotten 3,178,191,422 58,103.3 26,476.1 table scans (long tables) 45 0.0 0.0 table scans (short tables) 412,960 7.6 3.4 transaction rollbacks 5,221 0.1 0.0 transaction tables consistent rea 5 0.0 0.0 transaction tables consistent rea 49 0.0 0.0 user calls 17,314,198 316.5 144.2 user commits 115,125 2.1 1.0 user rollbacks 4,915 0.1 0.0 workarea executions - optimal 791,887 14.5 6.6 write clones created in backgroun 68 0.0 0.0 write clones created in foregroun 64,293 1.2 0.5 ------------------------------------------------------------- Tablespace IO Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SYSTEM 196,582 4 234.1 1.0 309,604 6 543,901 272.0 UNDOTBS1 41 0 111.5 1.0 309,448 6 100,850 143.3 DATA 155,466 3 54.7 6.7 130,768 2 15,502 243.3 INDEXS 18,997 0 76.4 1.0 117,763 2 1,711 22.2 STATS 215 0 13.0 1.0 278 0 0 0.0 XDB 65 0 77.2 1.2 35 0 0 0.0 CWMLITE 35 0 3.7 1.0 35 0 0 0.0 DRSYS 35 0 4.0 1.0 35 0 0 0.0 INDX 35 0 138.6 1.0 35 0 0 0.0 ODM 35 0 89.7 1.0 35 0 0 0.0 TOOLS 35 0 4.3 1.0 35 0 0 0.0 USERS 35 0 16.6 1.0 35 0 0 0.0 TEMP 0 0 0.0 4 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ CWMLITE /oracle/oradata/wsprod/cwmlite01.dbf 35 0 3.7 1.0 35 0 0 DATA /oracle/oradata/wsprod/data01.dbf 155,466 3 54.7 6.7 130,768 2 15,502 243.3 DRSYS /oracle/oradata/wsprod/drsys01.dbf 35 0 4.0 1.0 35 0 0 INDEXS /oracle/oradata/wsprod/indexs01.dbf 18,997 0 76.4 1.0 117,763 2 1,711 22.2 INDX /oracle/oradata/wsprod/indx01.dbf 35 0 138.6 1.0 35 0 0 ODM /oracle/oradata/wsprod/odm01.dbf 35 0 89.7 1.0 35 0 0 STATS /oracle/oradata/wsprod/stats01.dbf 215 0 13.0 1.0 278 0 0 SYSTEM /oracle/oradata/wsprod/system01.dbf 196,582 4 234.1 1.0 309,604 6 543,901 272.0 TEMP /oracle/oradata/wsprod/temp01.dbf 0 0 4 0 0 TOOLS /oracle/oradata/wsprod/tools01.dbf 35 0 4.3 1.0 35 0 0 UNDOTBS1 /oracle/oradata/wsprod/undotbs01.dbf 41 0 111.5 1.0 309,448 6 100,850 143.3 USERS /oracle/oradata/wsprod/users01.dbf 35 0 16.6 1.0 35 0 0 XDB /oracle/oradata/wsprod/xdb01.dbf 65 0 77.2 1.2 35 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 387,075 99.7 413,297,252 1,262,551 748,072 0 0 664,868 ------------------------------------------------------------- Instance Recovery Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 300 341 21826 517796 276480 276480 521151 E 300 567 37968 625478 10583 276480 10583 ------------------------------------------------------------- Buffer Pool Advisory for DB: WSPROD Instance: wsprod End Snap: 14 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate (default block size first) Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 304 .1 37,715 7.25 36,198,163 D 608 .2 75,430 2.31 11,517,600 D 912 .3 113,145 1.62 8,094,853 D 1,216 .4 150,860 1.41 7,027,076 D 1,520 .5 188,575 1.29 6,436,899 D 1,824 .6 226,290 1.24 6,184,002 D 2,128 .7 264,005 1.20 6,017,078 D 2,432 .8 301,720 1.14 5,694,753 D 2,736 .9 339,435 1.07 5,359,329 D 3,040 1.0 377,150 1.02 5,079,815 D 3,120 1.0 387,075 1.00 4,996,177 D 3,344 1.1 414,865 0.93 4,635,149 D 3,648 1.2 452,580 0.83 4,143,424 D 3,952 1.3 490,295 0.73 3,657,312 D 4,256 1.4 528,010 0.62 3,112,281 D 4,560 1.5 565,725 0.55 2,771,047 D 4,864 1.6 603,440 0.49 2,449,875 D 5,168 1.7 641,155 0.45 2,264,142 D 5,472 1.8 678,870 0.42 2,102,367 D 5,776 1.9 716,585 0.34 1,700,737 D 6,080 1.9 754,300 0.24 1,191,440 ------------------------------------------------------------- Buffer wait Statistics for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 560,949 151,687 270 undo header 15,953 14,084 883 file header block 346 344 995 undo block 84,502 20 0 segment header 456 0 0 bitmap block 1 0 0 ------------------------------------------------------------- PGA Aggr Target Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 31,131 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 1,442 1,192 215.1 0.0 .0 .0 .0 73,828 E 1,442 1,212 159.0 0.0 .0 .0 .0 73,828 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 760,248 760,248 0 0 16K 32K 3,596 3,596 0 0 32K 64K 764 764 0 0 64K 128K 168 168 0 0 128K 256K 29 29 0 0 256K 512K 33 33 0 0 512K 1024K 26,497 26,497 0 0 1M 2M 4 4 0 0 2M 4M 27 27 0 0 4M 8M 13 13 0 0 8M 16M 18 18 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: WSPROD Instance: wsprod End Snap: 14 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 180 0.1 70,414.4 497.0 99.0 0 360 0.3 70,414.4 0.0 100.0 0 721 0.5 70,414.4 0.0 100.0 0 1,081 0.8 70,414.4 0.0 100.0 0 1,442 1.0 70,414.4 0.0 100.0 0 1,730 1.2 70,414.4 0.0 100.0 0 2,019 1.4 70,414.4 0.0 100.0 0 2,307 1.6 70,414.4 0.0 100.0 0 2,596 1.8 70,414.4 0.0 100.0 0 2,884 2.0 70,414.4 0.0 100.0 0 4,326 3.0 70,414.4 0.0 100.0 0 5,768 4.0 70,414.4 0.0 100.0 0 8,652 6.0 70,414.4 0.0 100.0 0 11,536 8.0 70,414.4 0.0 100.0 0 ------------------------------------------------------------- Enqueue activity for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> Enqueue stats gathered prior to 9i should not be compared with 9i data -> ordered by Wait Time desc, Waits desc Avg Wt Wait Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s) -- ------------ ------------ ----------- ----------- ------------- ------------ SQ 2,550 2,539 0 118 594,695.10 70,174 TX 204,908 204,905 34 18,681 1,375.21 25,690 CF 73,803 73,786 17 42 42,748.81 1,795 TC 5 5 0 1 1,242,127.00 1,242 HW 8,075 8,075 0 70 .33 0 US 11,867 11,867 0 1 1.00 0 ------------------------------------------------------------- Rollback Segment Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->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 367.0 0.00 4,910 0 0 0 1 15,439,129.0 0.00 115,432,920 145 18 56 2 48,533.0 0.01 56,818,376 82 10 17 3 52,338.0 0.02 88,337,374 114 16 43 4 91,218.0 0.01 168,971,308 143 6 72 5 62,843.0 0.00 97,217,974 126 13 54 6 1,680,975.0 0.00 148,549,074 140 12 93 7 55,046.0 0.01 98,020,966 122 6 59 8 55,161.0 0.01 111,660,160 120 10 79 9 71,797.0 0.00 144,433,040 137 7 67 10 16,000,400.0 0.00 96,379,604 125 12 59 11 54,984.0 0.00 100,006,842 124 9 59 12 49,391.0 0.00 75,171,322 98 4 33 13 53,699.0 0.00 99,592,394 106 8 59 14 45,302.0 0.00 46,500,200 -458 -43 -168 15 52,461.0 0.01 84,902,750 -535 -61 -230 ------------------------------------------------------------- Rollback Segment Storage for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->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 45,277,184 7,879,056 45,277,184 2 13,754,368 1,063,716 51,503,104 3 45,211,648 8,931,099 61,988,864 4 134,340,608 7,623,510 134,340,608 5 38,920,192 6,380,323 75,620,352 6 61,988,864 7,693,003 84,008,960 7 65,134,592 7,095,938 86,106,112 8 52,551,680 7,946,488 75,620,352 9 117,563,392 8,545,699 117,563,392 10 41,017,344 6,381,327 55,697,408 11 57,794,560 8,287,437 57,794,560 12 43,114,496 7,417,666 43,114,496 13 39,968,768 7,003,106 117,563,392 14 14,802,944 0 14,802,944 15 15,851,520 0 15,851,520 ------------------------------------------------------------- Undo Segment Summary for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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 311,574 ########## 34,198 14 0 0 0/0/128/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> 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-Jun 12:52 0 ######## 1,026 14 0 0 0/0/0/0/0/0 28-Jun 12:42 0 ######## 3 13 0 0 0/0/0/0/0/0 28-Jun 12:32 0 0 0 0 0 0 0/0/0/0/0/0 28-Jun 12:22 12,720 ######## 34,198 2 0 0 0/0/0/0/0/0 28-Jun 12:12 0 ######## 1,548 8 0 0 0/0/0/0/0/0 28-Jun 12:02 0 ######## 807 6 0 0 0/0/0/0/0/0 28-Jun 11:52 12,718 ######## 31,932 2 0 0 0/0/0/0/0/0 28-Jun 11:42 12,731 ######## 2,129 2 0 0 0/0/0/0/0/0 28-Jun 11:32 12,722 ######## 31,008 6 0 0 0/0/0/0/0/0 28-Jun 11:22 12,713 ######## 30,379 2 0 0 0/0/0/0/0/0 28-Jun 11:12 0 ######## 572 8 0 0 0/0/0/0/0/0 28-Jun 11:02 10,702 ######## 29,384 7 0 0 0/0/0/0/0/0 28-Jun 10:52 1,194 ######## 28,900 1 0 0 0/0/0/0/0/0 28-Jun 10:42 1,270 ######## 28,300 1 0 0 0/0/0/0/0/0 28-Jun 10:32 1,160 ######## 27,700 1 0 0 0/0/0/0/0/0 28-Jun 10:22 1,296 ######## 27,100 1 0 0 0/0/0/0/0/0 28-Jun 10:12 1,229 ######## 26,500 1 0 0 0/0/128/0/0/0 28-Jun 10:02 1,308 ######## 25,900 1 0 0 0/0/0/0/0/0 28-Jun 09:52 1,305 ######## 25,300 1 0 0 0/0/0/0/0/0 28-Jun 09:42 1,316 ######## 24,700 1 0 0 0/0/0/0/0/0 28-Jun 09:32 1,265 ######## 24,100 1 0 0 0/0/0/0/0/0 28-Jun 09:22 1,087 ######## 23,500 1 0 0 0/0/0/0/0/0 28-Jun 09:12 768 ######## 22,900 1 0 0 0/0/0/0/0/0 28-Jun 09:02 669 ######## 22,300 1 0 0 0/0/0/0/0/0 28-Jun 08:52 608 ######## 21,700 1 0 0 0/0/0/0/0/0 28-Jun 08:42 3,647 ######## 21,100 1 0 0 0/0/0/0/0/0 28-Jun 08:32 2,966 ######## 20,498 6 0 0 0/0/0/0/0/0 28-Jun 08:22 2,120 ######## 19,900 2 0 0 0/0/0/0/0/0 28-Jun 08:12 2,863 ######## 19,300 5 0 0 0/0/0/0/0/0 28-Jun 08:02 2,286 ######## 18,700 3 0 0 0/0/0/0/0/0 28-Jun 07:52 2,504 ######## 18,100 6 0 0 0/0/0/0/0/0 28-Jun 07:42 3,055 ######## 17,500 7 0 0 0/0/0/0/0/0 28-Jun 07:32 3,072 ######## 16,900 9 0 0 0/0/0/0/0/0 28-Jun 07:22 2,994 ######## 16,300 3 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->"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 162,384 0.0 0 0 FAL request queue 75 0.0 0 0 FIB s.o chain latch 1,084 0.0 0 0 FOB s.o list latch 7,087 0.0 0.0 0 0 SQL memory manager latch 1 0.0 0 15,181 0.0 SQL memory manager worka 1,141,460 0.0 0 0 X$KSFQP 23 0.0 0 0 active checkpoint queue 107,379 0.2 0.0 0 0 alert log latch 160 0.6 0.0 0 0 archive control 1,984 0.1 0.0 0 0 archive process latch 1,656 2.4 0.7 0 0 begin backup scn array 161 0.0 0 0 cache buffer handles 18,278 0.0 0 0 cache buffers chains 913,572,465 1.8 0.0 0 3,947,330 0.2 cache buffers lru chain 1,006,757 0.0 0.0 0 5,368,470 0.1 channel handle pool latc 25,132 0.2 0.0 0 0 channel operations paren 65,051 0.0 0.0 0 0 checkpoint queue latch 7,077,509 0.0 0.0 0 860,745 0.0 child cursor hash table 977,729 0.0 0.0 0 0 commit callback allocati 157,538 0.0 0.0 0 0 device information 36 0.0 0 0 dictionary lookup 16 0.0 0 0 dml lock allocation 2,014,831 0.2 0.0 0 0 dummy allocation 15,167 1.4 0.0 0 0 enqueue hash chains 6,544,067 0.1 0.0 0 0 enqueues 4,346,806 0.0 0.0 0 0 event group latch 7,142 0.0 0 0 global tx hash mapping 1,679,565 0.0 0 0 hash table column usage 758 0.0 0 26,704 0.0 hash table modification 1 0.0 0 0 job workq parent latch 0 0 652 1.1 job_queue_processes para 1,000 0.0 0 0 kmcptab latch 4 0.0 0 0 kmcpvec latch 0 0 4 0.0 ktm global data 674 0.0 0 0 kwqit: protect wakeup ti 1,503 0.0 0 0 lgwr LWN SCN 164,365 0.0 0.0 0 0 library cache 81,668,116 1.0 0.0 51 324,932 1.0 library cache load lock 10,576 0.0 0.0 0 0 library cache pin 46,051,440 0.1 0.0 0 0 library cache pin alloca 30,077,136 0.1 0.0 1 0 list of block allocation 188,654 0.0 0.0 0 0 loader state object free 1,288 0.2 0.0 0 0 longop free list parent 2,893 0.0 0 102 0.0 message pool operations 14 0.0 0 0 messages 950,205 0.2 0.0 0 0 mostly latch-free SCN 165,184 0.2 0.0 0 0 multiblock read objects 196,107 0.0 0.0 0 0 ncodef allocation latch 2,710 0.0 0 0 object stats modificatio 52 0.0 0 0 Latch Activity for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ post/wait queue 472,813 0.9 0.0 0 326,219 0.8 process allocation 7,142 0.0 0.0 0 7,140 0.0 process group creation 14,355 0.2 0.0 0 0 redo allocation 16,522,916 0.4 0.0 0 0 redo copy 0 0 16,199,824 0.3 redo writing 682,712 0.9 0.0 0 0 resumable state object 17 0.0 0 0 row cache enqueue latch 46,149,260 0.0 0.0 0 0 row cache objects 46,270,885 0.4 0.0 0 8,325 0.0 sequence cache 138,067 0.0 0.0 0 0 session allocation 982,276 0.2 0.0 0 0 session idle bit 35,070,038 0.0 0.0 0 0 session switching 2,710 0.0 0 0 session timer 20,351 0.0 0 0 shared pool 39,014,739 0.6 0.0 4 0 sim partition latch 0 0 4,082 0.0 simulator hash latch 21,831,987 0.0 0.1 0 0 simulator lru latch 20,350 0.0 0.0 0 354,587 1.5 sort extent pool 3,030 0.0 0 0 spilled msgs queues list 1,502 0.0 0 0 temp lob duration state 8 0.0 0 0 transaction allocation 526,354 0.7 0.0 0 0 transaction branch alloc 79,978 0.0 0 0 undo global data 44,282,969 0.0 0.0 0 1 0.0 user lock 26,177 0.3 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 913,572,465 16,002,451 16,270 0/0/0/0/0 library cache 81,668,116 797,885 4,540 793631/4023/ 203/28/0 shared pool 39,014,739 238,101 4,615 233727/4203/ 152/19/0 row cache objects 46,270,885 197,462 132 197330/132/0 /0/0 redo allocation 16,522,916 59,195 154 59041/154/0/ 0/0 library cache pin 46,051,440 48,435 172 48265/168/2/ 0/0 library cache pin allocati 30,077,136 22,809 176 22637/168/4/ 0/0 row cache enqueue latch 46,149,260 9,886 8 9878/8/0/0/0 undo global data 44,282,969 9,667 67 9600/67/0/0/ 0 enqueue hash chains 6,544,067 5,997 25 5972/25/0/0/ 0 redo writing 682,712 5,957 3 5954/3/0/0/0 dml lock allocation 2,014,831 4,250 3 4247/3/0/0/0 transaction allocation 526,354 3,467 1 3466/1/0/0/0 messages 950,205 2,243 1 2242/1/0/0/0 session idle bit 35,070,038 2,027 39 0/0/0/0/0 session allocation 982,276 1,942 66 1876/66/0/0/ 0 enqueues 4,346,806 1,477 11 1466/11/0/0/ 0 user lock 26,177 76 1 75/1/0/0/0 cache buffers lru chain 1,006,757 54 1 53/1/0/0/0 archive process latch 1,656 39 28 11/28/0/0/0 checkpoint queue latch 7,077,509 23 1 22/1/0/0/0 simulator hash latch 21,831,987 10 1 9/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- archive process latch kcrrpa 0 28 0 cache buffers chains kcbgtcr: kslbegin excl 0 5,616 5,431 cache buffers chains kcbchg: kslbegin: bufs not 0 3,241 2,795 cache buffers chains kcbzwb 0 3,219 2,701 cache buffers chains kcbgtcr: fast path 0 1,176 1,252 cache buffers chains kcbgcur: kslbegin 0 1,085 660 cache buffers chains kcbrls: kslbegin 0 966 1,418 cache buffers chains kcbchg: kslbegin: call CR 0 605 1,718 cache buffers chains kcbnlc 0 74 116 cache buffers chains kcbget: exchange rls 0 49 3 cache buffers chains kcbzgb: scan from tail. no 0 40 0 cache buffers chains kcbget: exchange 0 37 9 cache buffers chains kcbget: pin buffer 0 24 34 cache buffers chains kcbcge 0 10 46 cache buffers chains kcbzsc 0 4 0 cache buffers chains kcbnew 0 3 0 cache buffers chains kcbzcg 0 2 0 cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 2 1 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 0 cost function kzulgt: find user res 0 1 1 dml lock allocation ktaiam 0 3 1 enqueue hash chains ksqgtl3 0 15 14 enqueue hash chains ksqrcl 0 10 5 enqueues ksqdel 0 4 5 enqueues ksqies 0 4 5 enqueues ksqgel: create enqueue 0 3 1 library cache kgllkdl: child: cleanup 0 849 264 library cache kglupc: child 0 704 790 library cache kglpndl: child: before pro 0 397 337 library cache kglhdgn: child: 0 354 1,442 library cache kglpnc: child 0 261 1,243 library cache kglpndl: child: after proc 0 192 18 library cache kglobpn: child: 0 121 58 library cache kglpin: child: heap proces 0 113 16 library cache kglhdgc: child: 0 104 77 library cache kglic 0 39 5 library cache kglhdiv: child 0 28 0 library cache kglpin 0 28 14 library cache kgldte: child 0 0 19 40 library cache kgldti: 2child 0 10 6 library cache kglpnp: child 0 10 9 library cache kglget: child: KGLDSBRD 0 4 26 library cache kglobld 0 3 4 library cache kglidp: child 0 2 0 library cache kglget: child: KGLDSBYD 0 1 12 library cache kglprg: child 0 1 0 library cache pin kglpndl 0 57 11 library cache pin kglupc 0 55 51 library cache pin kglpnal: child: alloc spac 0 32 59 library cache pin kglpnc: child 0 27 49 library cache pin kglpnp: child 0 1 2 library cache pin alloca kgllkdl 0 88 69 library cache pin alloca kglpnal 0 87 107 Latch Miss Sources for DB: WSPROD Instance: wsprod Snaps: 13 -14 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- library cache pin alloca kglpndl 0 1 0 messages ksaamb: after wakeup 0 1 0 redo allocation kcrfwr 0 154 150 redo writing kcrfws: in loop 0 3 3 row cache enqueue latch kqreqd 0 6 0 row cache enqueue latch kqreqa 0 2 8 row cache objects kqrpfl: not dirty 0 84 5 row cache objects kqrpre: find obj 0 48 127 session allocation ksudlp 0 50 3 session allocation ksucri 0 15 8 session allocation ksuxds: not user session 0 1 1 session idle bit ksupuc: clear busy 0 23 13 session idle bit ksupuc: set busy 0 10 22 session idle bit ksuxds 0 8 6 shared pool kghalo 0 1,840 432 shared pool kghupr1 0 1,336 3,979 shared pool kghfrunp: alloc: wait 0 1,265 9 shared pool kghfrunp: clatch: nowait 0 891 0 shared pool kghfrunp: clatch: wait 0 408 177 shared pool kghalp 0 89 9 shared pool kghfre 0 74 182 shared pool kghfen: not perm alloc cla 0 7 2 shared pool kghfru 0 2 0 simulator hash latch kcbsacc: lookup dba 0 1 1 transaction allocation ktcxbr 0 1 1 undo global data ktudba: KSLBEGIN 0 60 61 undo global data ktudnx:child 0 5 5 undo global data ktubnd:child 0 1 1 undo global data ktusmupst: KSLBEGIN 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->"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_database_links 77,964 0.0 0 0 2 dc_files 7,812 0.0 0 0 12 dc_global_oids 212 0.0 0 0 129 dc_histogram_defs 333,725 5.2 0 0 20,800 dc_object_ids 13,884,246 0.0 0 9 2,218 dc_objects 160,180 1.3 0 492 3,412 dc_profiles 6,620 0.0 0 0 1 dc_rollback_segments 30,269 0.0 0 21 41 dc_segments 3,625,363 0.1 0 236 5,371 dc_sequences 3,136 0.1 0 3,136 3 dc_table_scns 1 100.0 0 0 0 dc_tablespace_quotas 241 0.4 0 241 4 dc_tablespaces 2,842,409 0.0 0 0 13 dc_user_grants 49,821 0.0 0 0 22 dc_usernames 18,295 0.0 0 0 16 dc_users 2,116,124 0.0 0 0 31 ------------------------------------------------------------- Library Cache Activity for DB: WSPROD Instance: wsprod Snaps: 13 -14 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 33,852 0.0 33,852 0.0 1 0 CLUSTER 1,227 0.6 1,042 1.3 0 0 INDEX 397,156 0.0 397,155 0.0 0 0 SQL AREA 5,383,195 2.5 18,630,788 1.5 5,345 79,158 TABLE/PROCEDURE 1,965,371 0.1 2,665,258 0.2 1,439 0 TRIGGER 93,008 0.0 93,008 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: WSPROD Instance: wsprod End Snap: 14 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 416 .6 411 65,743 1,372,464 1.0 76,457,786 496 .7 490 86,476 1,372,788 1.0 76,462,004 576 .8 569 107,180 1,373,233 1.0 76,466,650 656 .9 648 127,844 1,374,488 1.0 76,490,831 736 1.0 727 148,585 1,374,846 1.0 76,512,244 816 1.1 805 155,432 1,375,013 1.0 76,514,311 896 1.2 884 161,190 1,375,042 1.0 76,515,285 976 1.3 961 166,069 1,375,127 1.0 76,515,725 1,056 1.4 1,040 171,878 1,375,168 1.0 76,516,251 1,136 1.5 1,119 177,725 1,375,420 1.0 76,524,639 1,216 1.7 1,198 183,953 1,375,452 1.0 76,525,305 1,296 1.8 1,277 189,566 1,375,544 1.0 76,527,250 1,376 1.9 1,356 195,218 1,375,546 1.0 76,527,587 1,456 2.0 1,437 201,057 1,375,673 1.0 76,528,148 1,536 2.1 1,518 206,964 1,375,698 1.0 76,529,652 ------------------------------------------------------------- SGA Memory Summary for DB: WSPROD Instance: wsprod Snaps: 13 -14 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 3,271,557,120 Fixed Size 737,512 Redo Buffers 200,155,136 Variable Size 1,006,632,960 ---------------- sum 4,479,082,728 ------------------------------------------------------------- SGA breakdown difference for DB: WSPROD Instance: wsprod Snaps: 13 -14 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 117,440,512 117,440,512 0.00 large free memory 16,777,216 16,777,216 0.00 shared Checkpoint queue 4,106,240 4,106,240 0.00 shared FileOpenBlock 11,238,952 11,238,952 0.00 shared KGK heap 7,000 7,000 0.00 shared KGLS heap 4,669,200 6,057,240 29.73 shared KQR L PO 5,724,232 5,955,248 4.04 shared KQR M PO 13,052,608 13,536,624 3.71 shared KQR M SO 2,560 2,560 0.00 shared KQR S SO 5,632 5,632 0.00 shared KQR X PO 5,152 5,152 0.00 shared MTTR advisory 181,104 181,104 0.00 shared PL/SQL DIANA 1,369,456 2,802,640 104.65 shared PL/SQL MPCODE 2,665,776 2,734,520 2.58 shared PLS non-lib hp 2,088 2,088 0.00 shared VIRTUAL CIRCUITS 3,379,880 3,379,880 0.00 shared XDB Schema Cac 5,190,440 5,884,368 13.37 shared db_block_hash_buckets 13,598,992 13,598,992 0.00 shared dictionary cache 3,229,952 3,229,952 0.00 shared errors 106,968 91,328 -14.62 shared event statistics per sess 18,721,360 18,721,360 0.00 shared fixed allocation callback 584 584 0.00 shared free memory 48,108,192 144,442,072 200.24 shared joxs heap init 4,240 4,240 0.00 shared library cache 110,078,040 224,868,952 104.28 shared log_buffer 6,254,144 6,254,144 0.00 shared miscellaneous 65,186,312 80,035,264 22.78 shared parameters 43,208 40,192 -6.98 shared partitioning d 2,355,760 2,437,168 3.46 shared sessions 4,475,120 4,475,120 0.00 shared sim memory hea 3,179,552 3,179,552 0.00 shared sql area 542,314,184 311,792,624 -42.51 shared subheap 102,312 102,312 0.00 shared table definiti 40,008 52,904 32.23 shared transaction 2,998,336 2,998,336 0.00 shared trigger defini 1,904 7,240 280.25 shared trigger inform 1,880 1,880 0.00 shared trigger source 488 3,704 659.02 shared type object de 13,376 170,600 ####### buffer_cache 3,271,557,120 3,271,557,120 0.00 fixed_sga 737,512 737,512 0.00 log_buffer 200,132,608 200,132,608 0.00 ------------------------------------------------------------- init.ora Parameters for DB: WSPROD Instance: wsprod Snaps: 13 -14 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 audit_trail DB background_dump_dest /oracle/admin/wsprod/bdump compatible 9.2.0.0.0 control_files /oracle/oradata/wsprod/control01. core_dump_dest /oracle/admin/wsprod/cdump db_block_size 8192 db_cache_size 3271557120 db_domain db_file_multiblock_read_count 16 db_name wsprod db_writer_processes 8 dispatchers (PROTOCOL=TCP) (SERVICE=wsprodXDB fast_start_mttr_target 300 filesystemio_options asynch hash_join_enabled TRUE instance_name wsprod java_pool_size 117440512 job_queue_processes 512 large_pool_size 16777216 log_archive_dest_1 LOCATION=/oracle/oradata/wsprod/a log_archive_format %t_%s.dbf log_archive_max_processes 4 log_archive_start TRUE log_buffer 200000000 open_cursors 6000 pga_aggregate_target 1512000000 processes 1500 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE sessions 1655 sga_max_size 4479082728 shared_pool_size 771751936 sort_area_size 256000000 sql_trace TRUE star_transformation_enabled FALSE statistics_level ALL timed_statistics TRUE undo_management AUTO undo_retention 10800 undo_tablespace UNDOTBS1 user_dump_dest /oracle/admin/wsprod/udump ------------------------------------------------------------- End of Report