STATSPACK report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ---------------- ORAPERF 1029455018 oraperf 1 10.1.0.4.0 NO aixperfdb Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 742 12-Jan-06 12:00:01 65 1,221.9 End Snap: 743 12-Jan-06 13:00:01 67 1,212.0 Elapsed: 60.00 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 5,312M Std Block Size: 4K Shared Pool Size: 960M Log Buffer: 195,313K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 1,732,386.78 50,529.00 Logical reads: 29,519.13 860.99 Block changes: 10,273.57 299.65 Physical reads: 3,017.22 88.00 Physical writes: 1,270.28 37.05 User calls: 8,060.40 235.10 Parses: 665.93 19.42 Hard parses: 0.02 0.00 Sorts: 3.44 0.10 Logons: 0.04 0.00 Executes: 4,158.92 121.30 Transactions: 34.29 % Blocks changed per Read: 34.80 Recursive Call %: 9.58 Rollback per transaction %: 0.00 Rows per Sort: 14.64 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.95 Redo NoWait %: 100.00 Buffer Hit %: 89.78 In-memory Sort %: 100.00 Library Hit %: 100.00 Soft Parse %: 100.00 Execute to Parse %: 83.99 Latch Hit %: 99.82 Parse CPU to Parse Elapsd %: 94.39 % Non-Parse CPU: 97.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 54.67 54.97 % SQL with executions>1: 65.14 64.99 % Memory for SQL w/exec>1: 64.00 63.65 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time -------------------------------------------- ------------ ----------- --------- db file sequential read 10,859,046 101,584 85.72 CPU time 7,244 6.11 db file parallel write 570,740 6,955 5.87 log file sync 129,437 1,292 1.09 log file parallel write 239,969 1,039 .88 ------------------------------------------------------------- Wait Events DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> 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 sequential read 10,859,046 0 101,584 9 88.0 db file parallel write 570,740 0 6,955 12 4.6 log file sync 129,437 0 1,292 10 1.0 log file parallel write 239,969 0 1,039 4 1.9 SQL*Net break/reset to clien 627,422 0 169 0 5.1 class slave wait 12 12 59 4883 0.0 Queue Monitor Task Wait 12 0 49 4069 0.0 control file parallel write 1,563 0 39 25 0.0 enq: TX - index contention 7,793 0 19 2 0.1 control file sequential read 3,044 0 14 5 0.0 latch: cache buffers chains 5,031 4,973 12 2 0.0 buffer busy waits 51,636 0 10 0 0.4 log file switch completion 53 0 6 121 0.0 latch free 1,245 1,186 3 3 0.0 process startup 58 0 3 53 0.0 read by other session 318 0 3 8 0.0 latch: library cache 1,090 0 2 2 0.0 control file single write 124 0 1 9 0.0 rdbms ipc reply 1,018 0 1 1 0.0 db file scattered read 165 0 0 3 0.0 SQL*Net more data to client 3,896 0 0 0 0.0 LGWR wait for redo copy 5,052 1 0 0 0.0 latch: shared pool 221 0 0 1 0.0 latch: row cache objects 33 0 0 2 0.0 direct path write 68 0 0 1 0.0 latch: library cache pin 72 0 0 1 0.0 direct path read 76 0 0 0 0.0 enq: HW - contention 27 0 0 1 0.0 latch: cache buffers lru cha 44 0 0 0 0.0 latch: redo allocation 20 0 0 1 0.0 enq: SQ - contention 16 0 0 1 0.0 log file single write 2 0 0 5 0.0 log file sequential read 2 0 0 4 0.0 latch: redo writing 3 0 0 1 0.0 undo segment extension 1,784 1,784 0 0 0.0 buffer deadlock 1,253 1,248 0 0 0.0 enq: TX - allocate ITL entry 6 0 0 0 0.0 latch: enqueue hash chains 4 0 0 0 0.0 latch: session allocation 1 0 0 1 0.0 latch: checkpoint queue latc 1 0 0 0 0.0 SQL*Net message from client 14,715,386 0 22,818 2 119.2 queue messages 721 720 3,518 4879 0.0 virtual circuit status 120 120 3,494 29114 0.0 Queue Monitor Wait 119 119 3,470 29157 0.0 jobq slave wait 1,150 1,124 3,340 2905 0.0 wakeup time manager 23 9 3,147 ###### 0.0 SQL*Net message to client 14,715,384 0 27 0 119.2 SQL*Net more data from clien 206 0 5 24 0.0 Wait Events DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> 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 ---------------------------- ------------ ---------- ---------- ------ -------- ------------------------------------------------------------- Background Wait Events DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> 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 570,671 0 6,954 12 4.6 log file parallel write 239,973 0 1,039 4 1.9 Queue Monitor Task Wait 12 0 49 4069 0.0 control file parallel write 1,561 0 39 25 0.0 control file sequential read 632 0 5 7 0.0 process startup 58 0 3 53 0.0 db file sequential read 376 0 2 4 0.0 control file single write 124 0 1 9 0.0 rdbms ipc reply 1,018 0 1 1 0.0 db file scattered read 113 0 0 2 0.0 LGWR wait for redo copy 5,054 1 0 0 0.0 direct path write 63 0 0 1 0.0 latch: cache buffers chains 18 16 0 3 0.0 direct path read 76 0 0 0 0.0 latch: cache buffers lru cha 44 0 0 0 0.0 log file single write 2 0 0 5 0.0 log file sequential read 2 0 0 4 0.0 latch: redo allocation 13 0 0 1 0.0 buffer busy waits 36 0 0 0 0.0 latch: redo writing 3 0 0 1 0.0 latch: checkpoint queue latc 1 0 0 0 0.0 rdbms ipc message 137,436 16,156 43,979 320 1.1 smon timer 416 0 3,526 8476 0.0 Queue Monitor Wait 119 119 3,470 29157 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> ordered by event (idle events last) Event -------------------------------------------------- 0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ LGWR wait for redo copy 5,038 11 2 1 0 0 Queue Monitor Task Wait 2 0 0 0 0 10 SQL*Net break/reset to client 617,052 10,011 171 18 0 0 SQL*Net more data to client 3,896 0 0 0 0 0 buffer busy waits 51,148 231 82 118 24 32 buffer deadlock 1,253 0 0 0 0 0 class slave wait 0 0 0 0 0 12 control file parallel write 0 0 0 122 1,415 26 control file sequential read 642 938 1,269 190 5 0 control file single write 0 0 17 107 0 0 db file parallel write 162,723 78,586 82,797 120,379 75,987 50,255 db file scattered read 15 115 31 4 0 0 db file sequential read 88,189 1,873,445 5,750,511 2,073,441 639,681 430,277 direct path read 74 0 0 1 1 0 direct path write 65 0 0 1 2 0 enq: HW - contention 26 0 0 0 1 0 enq: SQ - contention 15 1 0 0 0 0 enq: TX - allocate ITL entry 6 0 0 0 0 0 enq: TX - index contention 6,251 526 411 306 150 131 latch free 891 11 0 341 0 1 latch: cache buffers chains 3,838 0 0 1,189 0 0 latch: cache buffers lru chain 41 3 0 0 0 0 latch: checkpoint queue latch 1 0 0 0 0 0 latch: enqueue hash chains 4 0 0 0 0 0 latch: library cache 640 350 30 56 14 0 latch: library cache pin 65 7 0 0 0 0 latch: redo allocation Wait Event Histogram DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> ordered by event (idle events last) Event -------------------------------------------------- 0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ 14 6 0 0 0 0 latch: redo writing 2 1 0 0 0 0 latch: row cache objects 17 11 5 0 0 0 latch: session allocation 1 0 0 0 0 0 latch: shared pool 151 70 0 0 0 0 log file parallel write 119,980 2,372 54,401 62,770 371 71 log file sequential read 0 1 1 0 0 0 log file single write 0 0 2 0 0 0 log file switch completion 0 1 0 8 1 43 log file sync 2,199 1,192 33,882 87,030 4,872 175 process startup 0 0 0 0 0 58 rdbms ipc reply 1,000 11 0 1 3 3 read by other session 55 77 117 41 17 11 undo segment extension 1,784 0 0 0 0 0 Queue Monitor Wait 0 0 0 0 0 119 SQL*Net message from client 11,229,236 3,195,889 122,219 96,872 54,212 9,991 SQL*Net message to client 14,694,900 36 1 1 0 0 SQL*Net more data from client 154 2 3 1 5 41 dispatcher timer 0 0 0 0 0 60 jobq slave wait 0 0 0 0 0 1,150 pmon timer 12 0 0 0 0 1,200 queue messages 0 0 0 0 0 721 rdbms ipc message 30,868 9,102 10,536 16,810 20,164 49,959 smon timer 0 0 0 1 1 414 virtual circuit status 0 0 0 0 0 120 wakeup time manager 1 0 0 0 7 15 ------------------------------------------------------------- SQL ordered by Gets DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 106,268,861 -> Captured SQL accounts for 101.4% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 12,874,134 1,255,078 10.3 12.1 2901.84 7898.37 2573386356 Module: pipe04ac INSERT INTO SEP_CONFLICT_REL(CONFLICT_ID, MATCH_ID, PARENT_ENTIT Y_ID, CHILD_ENTITY_ID, DEGREE_OF_SEP, LIKE_CONF, REL_CONF) VALUE S(:1,:2,:3,:4,:5,:6,:7) 9,455,818 965,325 9.8 8.9 2665.22 6258.69 3352792302 Module: pipe04ac INSERT INTO ER_DETAIL ( ER_ID, MATCHED_ENTITY_ID, MATCHED_TYPE, MATCHED_ACCT_ID, MATCHED_KEY_ID, INBOUND_KEY_ID, SCORE_ADJUS TMENT, MATCH_PRECISION, PRECISION_DESC ) VALUES ( :1, :2, :3, : 4, :5, :6, :7, :8, :9 ) 6,173,911 627,696 9.8 5.8 1039.13 2759.51 4101062403 Module: pipe02ac INSERT INTO SEP_CONFLICT(CONFLICT_ID, SEP_TEST_ID, INBOUND_ENTIT Y_ID, CONFLICT_RULE_ID, CONF_ENTITY1, CONF_ENTITY2, DEGREE_OF_SE P, CONFLICT_STAT, WEAK_LIKE_SCORE, WEAK_REL_SCORE, CONFLICT_DT) VALUES(:1, :2,:3,:4,:5,:6,:7,:"SYS_B_0",:8,:9,:10) 4,871,081 823,388 5.9 4.6 203.07 215.45 659324044 Module: pipe04ac INSERT INTO ER_ENTITY_STATE(ER_ID, ENTITY_ID, DSRC_ACCT_ID, MAX_ ADDR_ID, MAX_ATTR_ID, MAX_EMAIL_ID, MAX_NAME_ID, MAX_NUM_ID, MAX _ROLE_ID) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9) 4,624,691 823,390 5.6 4.4 1509.27 3853.53 1712256954 Module: pipe04ac SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1 3,926,702 823,423 4.8 3.7 2065.13 5118.39 2878966175 Module: pipe04ac SELECT MAX(ADDR_ID) FROM ADDRESS WHERE DSRC_ACCT_ID = :1 3,894,561 823,621 4.7 3.7 2035.52 5295.99 1507829111 Module: pipe04ac SELECT MAX(NAME_ID) FROM NAME WHERE DSRC_ACCT_ID = :1 3,867,887 823,404 4.7 3.6 2323.74 6215.38 728889134 Module: pipe04ac SELECT MAX(NUM_ID) FROM NUMS WHERE DSRC_ACCT_ID = :1 3,829,651 273,973 14.0 3.6 954.31 3602.34 1739503172 Module: pipe01ac INSERT INTO SEP_RELATIONS(RELATION_ID, ENTITY_ID, RELTO_ID, LIKE _CONF, REL_CONF, DISCLOSED, MATCH_ID) VALUES(:1, :2,:3,:4,:5,:"S YS_B_0",:6) 3,805,847 823,421 4.6 3.6 1460.62 4005.33 3193297599 Module: pipe04ac SQL ordered by Gets DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 106,268,861 -> Captured SQL accounts for 101.4% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT MAX(ATTR_ID) FROM ATTRIBUTE WHERE DSRC_ACCT_ID = :1 3,344,138 180,092 18.6 3.1 460.50 1169.78 444121366 Module: pipe03ac INSERT INTO NUMS(NUM_ID, DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, NUM _HASH, NUM_LOCATION, NUM_STAT, NUM_STAT_DT, NUM_TYPE_ID, NUM_VAL UE, QC_STAT, SYS_CREATE_DT, SYS_DELETE_DT, SYS_LSTUPD_DT, VALID_ FROM_DT, VALID_THRU_DT) VALUES(:1, :2, :3, :4, :5, NULL, NULL, N ULL, :6, :7, :8, :9, NULL, :10, NULL, NULL) 2,789,522 134,129 20.8 2.6 326.90 1801.53 3253887853 Module: pipe03ac INSERT INTO ADDRESS(ADDR_ID, ADDR1, ADDR2, ADDR3, ADDR_HASH, ADD R_LEFTOVRS, ADDR_STAT, ADDR_STAT_DT, ADDR_TYPE, CARRIER_ROUTE, C ITY, COMPANY_NAME, COUNTRY, COUNTRY_CODE, DLV_PROB, DPBC, DSRC_A CCT_ID, ENTITY_ID, HIST_STAT, HOUSEHOLD_ID, JOB_TITLE, LATITUDE, LONGITUDE, LOT_CODE, POSTAL_CODE, QC_STAT, STATE, STATE_CODE, S 2,176,872 313,450 6.9 2.0 101.54 101.73 3286148528 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co n# = cd.con# and cd.enabled = :1 and c.owner# = u.user# 2,110,418 114,331 18.5 2.0 435.75 1801.98 3078809437 Module: pipe02ab INSERT INTO SEP_ROLES(ROLE_ID, DISCLOSED, DSRC_ACCT_ID, ENTITY_I D, ROLE_CODE_ID, ROLE_STAT, SYS_DELETE_DT) VALUES(:1, :2, :3, :4 , :5, :6, NULL) 2,029,750 93,637 21.7 1.9 110.02 1678.59 2711563501 Module: pipe04ac INSERT INTO NAME(NAME_ID, CULTURE, DSRC_ACCT_ID, ENTITY_ID, FIRS T_NAME, HIST_STAT, LAST_NAME, LFN_HASH, LMN_HASH, MID_NAME, NAME _GEN, NAME_PFX, NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS _DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES( :1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, NULL, :10, 1,733,793 95,782 18.1 1.6 252.38 737.35 1848403279 Module: pipe03ac INSERT INTO ATTRIBUTE(ATTR_ID, ATTR_DT, ATTR_TYPE_ID, ATTR_VALUE , DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, QC_STAT, SYS_CREATE_DT, SY S_DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES (:1, NULL, :2, :3, :4, :5, :6, :7, :8, NULL, :9, NULL, NULL) 1,694,449 59,014 28.7 1.6 267.56 787.76 3965780448 Module: pipe03ac UPDATE DSRC_ACCT SET ENTITY_ID = :1 ,ACCT_LOADED = :"SYS_B _0" WHERE DSRC_ACCT_ID = :2 1,657,693 96,383 17.2 1.6 225.59 2828.23 2888989485 Module: pipe04ac SQL ordered by Gets DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 106,268,861 -> Captured SQL accounts for 101.4% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT ENTITY_ID,RELTO_ID,LIKE_CONF,REL_CONF FROM SEP_RELATIONS WHERE ENTITY_ID IN (:1) OR RELTO_ID IN (:2) 1,553,392 199,749 7.8 1.5 365.60 5676.90 3518280853 Module: pipe02ac SELECT ENTITY_ID, ADDR1, CITY, STATE, POSTAL_CODE, COUNTRY, ADDR _ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT FROM ADDRESS WHE RE ADDR_HASH = :1 AND SYS_DELETE_DT IS NULL 1,446,212 55,316 26.1 1.4 142.46 894.80 3991303586 Module: pipe05ac SELECT ENTITY_ID, NUM_HASH, NUM_TYPE_ID, DSRC_ACCT_ID, NUM_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE ENTIT Y_ID = :1 AND NUM_TYPE_ID IN (:2, :3, :4, :5, :6, :7) AND SYS_DE LETE_DT IS NULL 1,064,547 192,905 5.5 1.0 569.63 2955.64 2276167495 Module: pipe01ac SELECT ENTITY_ID, NUM_HASH, NUM_ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE NUM_HASH = :1 AND NUM_TYPE_ID = :2 AND SYS_DELETE_DT IS NULL ------------------------------------------------------------- SQL ordered by Reads DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,861,999 -> Captured SQL accounts for 100.0% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 731,722 199,749 3.7 6.7 365.60 5676.90 3518280853 Module: pipe02ac SELECT ENTITY_ID, ADDR1, CITY, STATE, POSTAL_CODE, COUNTRY, ADDR _ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT FROM ADDRESS WHE RE ADDR_HASH = :1 AND SYS_DELETE_DT IS NULL 687,747 965,325 0.7 6.3 2665.22 6258.69 3352792302 Module: pipe04ac INSERT INTO ER_DETAIL ( ER_ID, MATCHED_ENTITY_ID, MATCHED_TYPE, MATCHED_ACCT_ID, MATCHED_KEY_ID, INBOUND_KEY_ID, SCORE_ADJUS TMENT, MATCH_PRECISION, PRECISION_DESC ) VALUES ( :1, :2, :3, : 4, :5, :6, :7, :8, :9 ) 656,307 1,255,078 0.5 6.0 2901.84 7898.37 2573386356 Module: pipe04ac INSERT INTO SEP_CONFLICT_REL(CONFLICT_ID, MATCH_ID, PARENT_ENTIT Y_ID, CHILD_ENTITY_ID, DEGREE_OF_SEP, LIKE_CONF, REL_CONF) VALUE S(:1,:2,:3,:4,:5,:6,:7) 549,264 823,404 0.7 5.1 2323.74 6215.38 728889134 Module: pipe04ac SELECT MAX(NUM_ID) FROM NUMS WHERE DSRC_ACCT_ID = :1 468,772 823,621 0.6 4.3 2035.52 5295.99 1507829111 Module: pipe04ac SELECT MAX(NAME_ID) FROM NAME WHERE DSRC_ACCT_ID = :1 453,364 823,423 0.6 4.2 2065.13 5118.39 2878966175 Module: pipe04ac SELECT MAX(ADDR_ID) FROM ADDRESS WHERE DSRC_ACCT_ID = :1 346,727 96,383 3.6 3.2 225.59 2828.23 2888989485 Module: pipe04ac SELECT ENTITY_ID,RELTO_ID,LIKE_CONF,REL_CONF FROM SEP_RELATIONS WHERE ENTITY_ID IN (:1) OR RELTO_ID IN (:2) 338,833 823,390 0.4 3.1 1509.27 3853.53 1712256954 Module: pipe04ac SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1 324,794 823,421 0.4 3.0 1460.62 4005.33 3193297599 Module: pipe04ac SELECT MAX(ATTR_ID) FROM ATTRIBUTE WHERE DSRC_ACCT_ID = :1 323,930 192,905 1.7 3.0 569.63 2955.64 2276167495 Module: pipe01ac SELECT ENTITY_ID, NUM_HASH, NUM_ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE NUM_HASH = :1 AND NUM_TYPE_ID = :2 AND SYS_DELETE_DT IS NULL 313,710 273,973 1.1 2.9 954.31 3602.34 1739503172 Module: pipe01ac SQL ordered by Reads DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,861,999 -> Captured SQL accounts for 100.0% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- INSERT INTO SEP_RELATIONS(RELATION_ID, ENTITY_ID, RELTO_ID, LIKE _CONF, REL_CONF, DISCLOSED, MATCH_ID) VALUES(:1, :2,:3,:4,:5,:"S YS_B_0",:6) 235,272 627,696 0.4 2.2 1039.13 2759.51 4101062403 Module: pipe02ac INSERT INTO SEP_CONFLICT(CONFLICT_ID, SEP_TEST_ID, INBOUND_ENTIT Y_ID, CONFLICT_RULE_ID, CONF_ENTITY1, CONF_ENTITY2, DEGREE_OF_SE P, CONFLICT_STAT, WEAK_LIKE_SCORE, WEAK_REL_SCORE, CONFLICT_DT) VALUES(:1, :2,:3,:4,:5,:6,:7,:"SYS_B_0",:8,:9,:10) 172,903 63,849 2.7 1.6 112.18 1408.37 2870256135 Module: pipe05ac SELECT :"SYS_B_0", ADDR_HASH, DSRC_ACCT_ID, ADDR_ID, VALID_FROM _DT, VALID_THRU_DT, CITY, STATE, POSTAL_CODE, COUNTRY FROM ADDRE SS WHERE ENTITY_ID = :1 AND SYS_DELETE_DT IS NULL 166,486 114,341 1.5 1.5 358.70 1820.68 3882409638 Module: pipe01ac SELECT ENTITY_ID, DSRC_ACCT_ID, DOC_TYPE_CREATED, ACCT_LOADED FR OM DSRC_ACCT WHERE DSRC_ACCT = :1 AND DSRC_ID = :2 145,375 114,331 1.3 1.3 435.75 1801.98 3078809437 Module: pipe02ab INSERT INTO SEP_ROLES(ROLE_ID, DISCLOSED, DSRC_ACCT_ID, ENTITY_I D, ROLE_CODE_ID, ROLE_STAT, SYS_DELETE_DT) VALUES(:1, :2, :3, :4 , :5, :6, NULL) 139,935 93,637 1.5 1.3 110.02 1678.59 2711563501 Module: pipe04ac INSERT INTO NAME(NAME_ID, CULTURE, DSRC_ACCT_ID, ENTITY_ID, FIRS T_NAME, HIST_STAT, LAST_NAME, LFN_HASH, LMN_HASH, MID_NAME, NAME _GEN, NAME_PFX, NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS _DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES( :1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, NULL, :10, 135,521 70,850 1.9 1.2 200.69 1060.29 1836975286 Module: pipe04ac SELECT ENTITY_ID, LFN_HASH, LMN_HASH, LAST_NAME, FIRST_NAME, MID _NAME, DSRC_ACCT_ID, NAME_ID, NAME_GEN, CULTURE FROM NAME WHERE ENTITY_ID = :1 AND SYS_DELETE_DT IS NULL 123,718 134,129 0.9 1.1 326.90 1801.53 3253887853 Module: pipe03ac INSERT INTO ADDRESS(ADDR_ID, ADDR1, ADDR2, ADDR3, ADDR_HASH, ADD R_LEFTOVRS, ADDR_STAT, ADDR_STAT_DT, ADDR_TYPE, CARRIER_ROUTE, C ITY, COMPANY_NAME, COUNTRY, COUNTRY_CODE, DLV_PROB, DPBC, DSRC_A CCT_ID, ENTITY_ID, HIST_STAT, HOUSEHOLD_ID, JOB_TITLE, LATITUDE, LONGITUDE, LOT_CODE, POSTAL_CODE, QC_STAT, STATE, STATE_CODE, S 110,278 55,316 2.0 1.0 142.46 894.80 3991303586 Module: pipe05ac SQL ordered by Reads DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,861,999 -> Captured SQL accounts for 100.0% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT ENTITY_ID, NUM_HASH, NUM_TYPE_ID, DSRC_ACCT_ID, NUM_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE ENTIT Y_ID = :1 AND NUM_TYPE_ID IN (:2, :3, :4, :5, :6, :7) AND SYS_DE LETE_DT IS NULL ------------------------------------------------------------- SQL ordered by Executions DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Executions Threshold: 100 Total Executions: 14,972,101 -> Captured SQL accounts for 100.0% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 1,255,078 1,254,945 1.0 0.00 0.01 2573386356 Module: pipe04ac INSERT INTO SEP_CONFLICT_REL(CONFLICT_ID, MATCH_ID, PARENT_ENTIT Y_ID, CHILD_ENTITY_ID, DEGREE_OF_SEP, LIKE_CONF, REL_CONF) VALUE S(:1,:2,:3,:4,:5,:6,:7) 965,325 965,093 1.0 0.00 0.01 3352792302 Module: pipe04ac INSERT INTO ER_DETAIL ( ER_ID, MATCHED_ENTITY_ID, MATCHED_TYPE, MATCHED_ACCT_ID, MATCHED_KEY_ID, INBOUND_KEY_ID, SCORE_ADJUS TMENT, MATCH_PRECISION, PRECISION_DESC ) VALUES ( :1, :2, :3, : 4, :5, :6, :7, :8, :9 ) 823,621 823,727 1.0 0.00 0.01 1507829111 Module: pipe04ac SELECT MAX(NAME_ID) FROM NAME WHERE DSRC_ACCT_ID = :1 823,486 823,295 1.0 0.00 0.00 4007112669 Module: pipe04ac SELECT MAX(EMAIL_ADDR_ID) FROM EMAIL_ADDR WHERE DSRC_ACCT_ID = : 1 823,423 823,674 1.0 0.00 0.01 2878966175 Module: pipe04ac SELECT MAX(ADDR_ID) FROM ADDRESS WHERE DSRC_ACCT_ID = :1 823,421 823,715 1.0 0.00 0.00 3193297599 Module: pipe04ac SELECT MAX(ATTR_ID) FROM ATTRIBUTE WHERE DSRC_ACCT_ID = :1 823,404 823,710 1.0 0.00 0.01 728889134 Module: pipe04ac SELECT MAX(NUM_ID) FROM NUMS WHERE DSRC_ACCT_ID = :1 823,390 823,661 1.0 0.00 0.00 1712256954 Module: pipe04ac SELECT MAX(ROLE_ID) FROM SEP_ROLES WHERE DSRC_ACCT_ID = :1 823,388 823,451 1.0 0.00 0.00 659324044 Module: pipe04ac INSERT INTO ER_ENTITY_STATE(ER_ID, ENTITY_ID, DSRC_ACCT_ID, MAX_ ADDR_ID, MAX_ATTR_ID, MAX_EMAIL_ID, MAX_NAME_ID, MAX_NUM_ID, MAX _ROLE_ID) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9) 627,696 627,695 1.0 0.00 0.00 4101062403 Module: pipe02ac INSERT INTO SEP_CONFLICT(CONFLICT_ID, SEP_TEST_ID, INBOUND_ENTIT Y_ID, CONFLICT_RULE_ID, CONF_ENTITY1, CONF_ENTITY2, DEGREE_OF_SE P, CONFLICT_STAT, WEAK_LIKE_SCORE, WEAK_REL_SCORE, CONFLICT_DT) VALUES(:1, :2,:3,:4,:5,:6,:7,:"SYS_B_0",:8,:9,:10) 627,467 627,679 1.0 0.00 0.00 3494544863 SQL ordered by Executions DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Executions Threshold: 100 Total Executions: 14,972,101 -> Captured SQL accounts for 100.0% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- Module: pipe04ac SELECT SEQ_SEP_CONFLICT_ID.NextVal AS NewIdentity FROM DUAL 619,636 619,051 1.0 0.00 0.00 3424337786 Module: pipe04ac INSERT INTO ER_ENTITY_SCORE ( ER_ID, ER_ITERATION, MATCHED_ENTI TY_ID, ADDR_CNT, UNUM_CNT, ONUM_CNT, ATTR_CNT, EMAIL_CNT, APP ROX_CNT, PROXIMITY_CNT, NAME_CONF, LAS_CONF, DOB_CONF, UNUM_C ONF, ONUM_CLOSE_CNT, GEN_CONF, ATTR_CONF, LIKE_SCORE, REL_SCO RE, TOT_MATCH_CNT, MATCH_CONF, NUM_ADDRS, NUM_NAMES, NUM_NUMS, 313,450 258,275 0.8 0.00 0.00 3286148528 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co n# = cd.con# and cd.enabled = :1 and c.owner# = u.user# 274,006 274,022 1.0 0.00 0.00 855832451 Module: pipe01ac SELECT SEQ_SEP_RELATIONS_ID.NextVal AS NewIdentity FROM DUAL 273,973 273,969 1.0 0.00 0.01 1739503172 Module: pipe01ac INSERT INTO SEP_RELATIONS(RELATION_ID, ENTITY_ID, RELTO_ID, LIKE _CONF, REL_CONF, DISCLOSED, MATCH_ID) VALUES(:1, :2,:3,:4,:5,:"S YS_B_0",:6) 199,749 649,935 3.3 0.00 0.03 3518280853 Module: pipe02ac SELECT ENTITY_ID, ADDR1, CITY, STATE, POSTAL_CODE, COUNTRY, ADDR _ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT FROM ADDRESS WHE RE ADDR_HASH = :1 AND SYS_DELETE_DT IS NULL 192,905 266,117 1.4 0.00 0.02 2276167495 Module: pipe01ac SELECT ENTITY_ID, NUM_HASH, NUM_ID, DSRC_ACCT_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE NUM_HASH = :1 AND NUM_TYPE_ID = :2 AND SYS_DELETE_DT IS NULL 180,096 180,101 1.0 0.00 0.00 2970982364 Module: pipe03ac SELECT SEQ_NUMS_ID.NextVal AS NewIdentity FROM DUAL 180,092 85,402 0.5 0.00 0.01 444121366 Module: pipe03ac INSERT INTO NUMS(NUM_ID, DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, NUM _HASH, NUM_LOCATION, NUM_STAT, NUM_STAT_DT, NUM_TYPE_ID, NUM_VAL UE, QC_STAT, SYS_CREATE_DT, SYS_DELETE_DT, SYS_LSTUPD_DT, VALID_ FROM_DT, VALID_THRU_DT) VALUES(:1, :2, :3, :4, :5, NULL, NULL, N ULL, :6, :7, :8, :9, NULL, :10, NULL, NULL) 164,361 164,365 1.0 0.00 0.00 2982720875 Module: pipe03ac SELECT SEQ_ADDRESS_ID.NextVal AS NewIdentity FROM DUAL SQL ordered by Executions DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Executions Threshold: 100 Total Executions: 14,972,101 -> Captured SQL accounts for 100.0% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Parse Calls Threshold: 1000 Total Parse Calls: 2,397,337 -> Captured SQL accounts for 100.0% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 313,577 313,450 13.08 3286148528 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co n# = cd.con# and cd.enabled = :1 and c.owner# = u.user# 59,692 180,092 2.49 444121366 Module: pipe03ac INSERT INTO NUMS(NUM_ID, DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, NUM _HASH, NUM_LOCATION, NUM_STAT, NUM_STAT_DT, NUM_TYPE_ID, NUM_VAL UE, QC_STAT, SYS_CREATE_DT, SYS_DELETE_DT, SYS_LSTUPD_DT, VALID_ FROM_DT, VALID_THRU_DT) VALUES(:1, :2, :3, :4, :5, NULL, NULL, N ULL, :6, :7, :8, :9, NULL, :10, NULL, NULL) 55,315 55,308 2.31 2458412332 select o.name, u.name from obj$ o, user$ u where o.obj# = :1 an d o.owner# = u.user# 42,506 63,855 1.77 2736288501 Module: pipe05ac SELECT :"SYS_B_0", EMAIL_HASH, EMAIL_ADDR, DSRC_ACCT_ID, EMAIL_ ADDR_ID, VALID_FROM_DT, VALID_THRU_DT FROM EMAIL_ADDR WHERE ENTI TY_ID = :1 AND SYS_DELETE_DT IS NULL 42,504 63,849 1.77 2870256135 Module: pipe05ac SELECT :"SYS_B_0", ADDR_HASH, DSRC_ACCT_ID, ADDR_ID, VALID_FROM _DT, VALID_THRU_DT, CITY, STATE, POSTAL_CODE, COUNTRY FROM ADDRE SS WHERE ENTITY_ID = :1 AND SYS_DELETE_DT IS NULL 40,484 55,310 1.69 1107508677 Module: pipe05ac UPDATE DSRC_ACCT SET SRC_LSTUPD_US = NULL , ACCT_LOADED = :1 , S YS_LSTUPD_DT = :2 , SYS_DELETE_DT = NULL , SYS_DELETE_STAT = NUL L , DSRC_REF = :3 , DOC_TYPE_CREATED = :"SYS_B_0" WHERE DSRC_ACC T_ID = :4 40,484 55,316 1.69 3991303586 Module: pipe05ac SELECT ENTITY_ID, NUM_HASH, NUM_TYPE_ID, DSRC_ACCT_ID, NUM_ID, VALID_FROM_DT, VALID_THRU_DT, NUM_LOCATION FROM NUMS WHERE ENTIT Y_ID = :1 AND NUM_TYPE_ID IN (:2, :3, :4, :5, :6, :7) AND SYS_DE LETE_DT IS NULL 39,724 70,856 1.66 3337879300 Module: pipe04ac SELECT ENTITY_ID, ENTITY_TYPE_ID FROM ENTITY WHERE ENTIT Y_ID = :1 39,608 70,849 1.65 2508232119 Module: pipe04ac SELECT ENTITY_ID, ATTR_VALUE, ATTR_TYPE_ID, DSRC_ACCT_ID, ATTR_I D, VALID_FROM_DT, VALID_THRU_DT FROM ATTRIBUTE WHERE ENTITY_ID = :1 AND ATTR_TYPE_ID IN ( :2, :3 ) AND SYS_DELETE_DT IS NULL SQL ordered by Parse Calls DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Parse Calls Threshold: 1000 Total Parse Calls: 2,397,337 -> Captured SQL accounts for 100.0% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 39,527 55,303 1.65 1542044249 Module: pipe05ac SELECT ROLE_ID, SYS_DELETE_DT FROM SEP_ROLES WHERE DISCLOSED = : 1 AND DSRC_ACCT_ID = :2 AND ROLE_CODE_ID = :3 39,311 93,637 1.64 2711563501 Module: pipe04ac INSERT INTO NAME(NAME_ID, CULTURE, DSRC_ACCT_ID, ENTITY_ID, FIRS T_NAME, HIST_STAT, LAST_NAME, LFN_HASH, LMN_HASH, MID_NAME, NAME _GEN, NAME_PFX, NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS _DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES( :1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, NULL, :10, 39,164 273,973 1.63 1739503172 Module: pipe01ac INSERT INTO SEP_RELATIONS(RELATION_ID, ENTITY_ID, RELTO_ID, LIKE _CONF, REL_CONF, DISCLOSED, MATCH_ID) VALUES(:1, :2,:3,:4,:5,:"S YS_B_0",:6) 38,874 60,854 1.62 3347081731 Module: pipe05ac SELECT NAME_ID, FIRST_NAME, LAST_NAME, MID_NAME, NAME_GEN, CULTU RE, LFN_HASH, LMN_HASH, NAME_PFX, NAME_SFX, QC_STAT, VALID_FROM_ DT, VALID_THRU_DT, SYS_LSTUPD_DT, SYS_DELETE_DT FROM NAME WHERE DSRC_ACCT_ID = :1 AND ENTITY_ID = :2 AND NAME_TYPE = :3 AND HIST _STAT = :"SYS_B_0" 38,627 70,854 1.61 1070464841 Module: pipe04ac SELECT ROLE_CODE_ID, ENTITY_ID , DSRC_ACCT_ID FROM SEP_ROLES WHE RE ENTITY_ID = :1 AND SYS_DELETE_DT IS NULL 38,594 95,782 1.61 1848403279 Module: pipe03ac INSERT INTO ATTRIBUTE(ATTR_ID, ATTR_DT, ATTR_TYPE_ID, ATTR_VALUE , DSRC_ACCT_ID, ENTITY_ID, HIST_STAT, QC_STAT, SYS_CREATE_DT, SY S_DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES (:1, NULL, :2, :3, :4, :5, :6, :7, :8, NULL, :9, NULL, NULL) 38,426 50,018 1.60 3298398546 Module: pipe05ac UPDATE ADDRESS SET HIST_STAT = (SELECT MAX(HIST_STAT)+:"SYS_B_0" FROM ADDRESS WHERE ADDR_TYPE = :1 AND DSRC_ACCT_ID = :2 AND ENT ITY_ID = :3) WHERE ADDR_TYPE = :4 AND DSRC_ACCT_ID = :5 AND ENT ITY_ID = :6 AND HIST_STAT = :"SYS_B_1" 37,985 94,690 1.58 352454643 Module: pipe05ac SELECT NUM_ID, NUM_LOCATION, NUM_VALUE, NUM_HASH, NUM_STAT, NUM_ STAT_DT, QC_STAT, VALID_FROM_DT, VALID_THRU_DT, SYS_LSTUPD_DT, S YS_DELETE_DT FROM NUMS WHERE DSRC_ACCT_ID = :1 AND ENTITY_ID = : SQL ordered by Parse Calls DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Parse Calls Threshold: 1000 Total Parse Calls: 2,397,337 -> Captured SQL accounts for 100.0% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 2 AND NUM_TYPE_ID = :3 AND HIST_STAT = :"SYS_B_0" 37,916 70,850 1.58 1836975286 Module: pipe04ac SELECT ENTITY_ID, LFN_HASH, LMN_HASH, LAST_NAME, FIRST_NAME, MID _NAME, DSRC_ACCT_ID, NAME_ID, NAME_GEN, CULTURE FROM NAME WHERE ENTITY_ID = :1 AND SYS_DELETE_DT IS NULL 36,497 47,452 1.52 2635318180 Module: pipe05ac SELECT ATTR_ID, ATTR_VALUE, ATTR_DT, QC_STAT, VALID_FROM_DT, VAL ID_THRU_DT, SYS_LSTUPD_DT, SYS_DELETE_DT FROM ATTRIBUTE WHERE AT TR_TYPE_ID = :1 AND DSRC_ACCT_ID = :2 AND ENTITY_ID = :3 AND HIS T_STAT = :"SYS_B_0" ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> End Version Count Threshold: 20 Version Count Executions Old Hash Value -------- ------------ --------------- 25 5,637 3517442706 Module: pipe02ac INSERT INTO NAME(NAME_ID, CULTURE, DSRC_ACCT_ID, ENTITY_ID, FIRS T_NAME, HIST_STAT, LAST_NAME, LFN_HASH, LMN_HASH, MID_NAME, NAME _GEN, NAME_PFX, NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS _DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT) VALUES( :1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, :10, :11, ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 724,384 201.2 5.9 CPU used when call started 723,302 200.9 5.9 CR blocks created 357,945 99.4 2.9 Cached Commit SCN referenced 10,248 2.9 0.1 Commit SCN cached 2 0.0 0.0 DB time 11,933,328 3,314.8 96.7 DBWR checkpoint buffers written 984,203 273.4 8.0 DBWR checkpoints 1 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR transaction table writes 787 0.2 0.0 DBWR undo block writes 601,098 167.0 4.9 IMU CR rollbacks 261,068 72.5 2.1 IMU Flushes 54,615 15.2 0.4 IMU Redo allocation size 231,422,032 64,283.9 1,875.0 IMU bind flushes 0 0.0 0.0 IMU commits 295 0.1 0.0 IMU contention 32,774 9.1 0.3 IMU ktichg flush 7 0.0 0.0 IMU pool not allocated 69,399 19.3 0.6 IMU recursive-transaction flush 9 0.0 0.0 IMU undo allocation size 194,470,208 54,019.5 1,575.6 IMU- failed to get a private stra 6,256 1.7 0.1 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 SQL*Net roundtrips to/from client 14,715,966 4,087.8 119.2 active txn count during cleanout 3,120,243 866.7 25.3 application wait time 17,020 4.7 0.1 background checkpoints completed 1 0.0 0.0 background checkpoints started 1 0.0 0.0 background timeouts 18,564 5.2 0.2 branch node splits 1,098 0.3 0.0 buffer is not pinned count 14,842,582 4,122.9 120.3 buffer is pinned count 1,956,986 543.6 15.9 bytes received via SQL*Net from c 2,011,313,879 558,698.3 16,295.7 bytes sent via SQL*Net to client 1,741,634,031 483,787.2 14,110.8 calls to get snapshot scn: kcmgss 9,649,099 2,680.3 78.2 calls to kcmgas 1,186,881 329.7 9.6 calls to kcmgcs 246,690 68.5 2.0 change write time 36,862 10.2 0.3 cleanout - number of ktugct calls 708,863 196.9 5.7 cluster key scan block gets 323,691 89.9 2.6 cluster key scans 313,863 87.2 2.5 commit cleanout failures: block l 9 0.0 0.0 commit cleanout failures: buffer 136 0.0 0.0 commit cleanout failures: callbac 1,678 0.5 0.0 commit cleanout failures: cannot 26,955 7.5 0.2 commit cleanouts 6,837,591 1,899.3 55.4 commit cleanouts successfully com 6,808,813 1,891.3 55.2 commit txn count during cleanout 1,362,621 378.5 11.0 concurrency wait time 4,299 1.2 0.0 consistent changes 2,503,249 695.4 20.3 consistent gets 54,963,217 15,267.6 445.3 consistent gets - examination 34,180,478 9,494.6 276.9 consistent gets from cache 54,963,218 15,267.6 445.3 current blocks converted for CR 0 0.0 0.0 Instance Activity Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ cursor authentications 16 0.0 0.0 data blocks consistent reads - un 2,490,456 691.8 20.2 db block changes 36,984,866 10,273.6 299.7 db block gets 51,305,647 14,251.6 415.7 db block gets direct 520 0.1 0.0 db block gets from cache 51,305,127 14,251.4 415.7 deferred (CURRENT) block cleanout 247,898 68.9 2.0 dirty buffers inspected 2,651,729 736.6 21.5 enqueue conversions 1,199 0.3 0.0 enqueue releases 2,067,026 574.2 16.8 enqueue requests 2,066,980 574.2 16.8 enqueue timeouts 0 0.0 0.0 enqueue waits 7,766 2.2 0.1 exchange deadlocks 1,252 0.4 0.0 execute count 14,972,101 4,158.9 121.3 free buffer inspected 11,902,678 3,306.3 96.4 free buffer requested 12,124,688 3,368.0 98.2 heap block compress 63,225 17.6 0.5 hot buffers moved to head of LRU 2,834,078 787.2 23.0 immediate (CR) block cleanout app 377,862 105.0 3.1 immediate (CURRENT) block cleanou 406,139 112.8 3.3 index fast full scans (full) 0 0.0 0.0 index fetch by key 1,257,122 349.2 10.2 index scans kdiixs1 10,448,673 2,902.4 84.7 leaf node 90-10 splits 23,225 6.5 0.2 leaf node splits 133,795 37.2 1.1 logons cumulative 126 0.0 0.0 messages received 701,153 194.8 5.7 messages sent 701,152 194.8 5.7 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 9,988,508 2,774.6 80.9 opened cursors cumulative 2,236,765 621.3 18.1 parse count (failures) 12 0.0 0.0 parse count (hard) 84 0.0 0.0 parse count (total) 2,397,337 665.9 19.4 parse time cpu 21,728 6.0 0.2 parse time elapsed 23,020 6.4 0.2 physical read IO requests 10,859,918 3,016.6 88.0 physical reads 10,861,999 3,017.2 88.0 physical reads cache 10,861,923 3,017.2 88.0 physical reads cache prefetch 2,081 0.6 0.0 physical reads direct 76 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 3,140,981 872.5 25.5 physical writes 4,573,014 1,270.3 37.1 physical writes direct 578 0.2 0.0 physical writes direct (lob) 0 0.0 0.0 physical writes direct temporary 0 0.0 0.0 physical writes from cache 4,572,436 1,270.1 37.1 physical writes non checkpoint 4,160,768 1,155.8 33.7 pinned buffers inspected 148 0.0 0.0 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 3,605 1.0 0.0 recovery blocks read 0 0.0 0.0 Instance Activity Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ recursive calls 3,072,899 853.6 24.9 recursive cpu usage 27,512 7.6 0.2 redo blocks written 12,709,192 3,530.3 103.0 redo buffer allocation retries 80 0.0 0.0 redo entries 19,330,256 5,369.5 156.6 redo log space requests 102 0.0 0.0 redo log space wait time 670 0.2 0.0 redo ordering marks 560,598 155.7 4.5 redo size 6,236,592,404 1,732,386.8 50,529.0 redo synch time 132,721 36.9 1.1 redo synch writes 127,263 35.4 1.0 redo wastage 58,017,800 16,116.1 470.1 redo write time 106,560 29.6 0.9 redo writer latching time 20 0.0 0.0 redo writes 119,987 33.3 1.0 rollback changes - undo records a 385,253 107.0 3.1 rows fetched via callback 701,096 194.8 5.7 session connect time 0 0.0 0.0 session cursor cache hits 2,020,440 561.2 16.4 session logical reads 106,268,861 29,519.1 861.0 session pga memory 112,924,336 31,367.9 914.9 session pga memory max 129,308,336 35,919.0 1,047.7 session uga memory 292,062,600,456 81,128,500.1 2,366,297.2 session uga memory max 85,375,496 23,715.4 691.7 shared hash latch upgrades - no w 10,455,526 2,904.3 84.7 shared hash latch upgrades - wait 5,329 1.5 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 12,392 3.4 0.1 sorts (rows) 181,444 50.4 1.5 summed dirty queue length 14,120,114 3,922.3 114.4 switch current to new buffer 4,443 1.2 0.0 table fetch by rowid 6,460,447 1,794.6 52.3 table fetch continued row 471 0.1 0.0 table scan blocks gotten 7,266 2.0 0.1 table scan rows gotten 27,880 7.7 0.2 table scans (long tables) 0 0.0 0.0 table scans (short tables) 564 0.2 0.0 transaction rollbacks 4,168 1.2 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 undo change vector size 2,103,606,256 584,335.1 17,043.5 user I/O wait time 10,157,999 2,821.7 82.3 user calls 29,017,422 8,060.4 235.1 user commits 123,426 34.3 1.0 user rollbacks 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 6,913 1.9 0.1 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 225 0.1 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 65 67 opened cursors current 79,425 81,207 session cursor cache count 20,181 20,466 ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 1 1.00 ------------------------------------------------------------- Time Model System Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Total Time in Database calls 109390.2s (or 1.0939E+11us) Statistic Time (s) % of DB Time ----------------------------------- -------------------- ------------ DB CPU 26,158.8 23.9 DB time 109,390.2 PL/SQL compilation elapsed time 2.0 .0 PL/SQL execution elapsed time 3.4 .0 background cpu time 282.8 .3 background elapsed time 8,342.9 7.6 hard parse (bind mismatch) elapsed 0.0 .0 hard parse (sharing criteria) elaps 0.0 .0 hard parse elapsed time 2.6 .0 parse time elapsed 231.5 .2 sequence load elapsed time 0.8 .0 sql execute elapsed time 106,927.6 97.7 ------------------------------------------------------------- OS Statistics DB/Inst: ORAPERF/oraperf Snaps: 742-743 Statistic Total per Second ------------------------- ---------------------- ------------------ AVG_IN_BYTES 0 0 AVG_OUT_BYTES 4,425,728 1,229 IN_BYTES 0 0 OUT_BYTES 35,405,824 9,835 AVG_BUSY_TICKS 7,295,484 AVG_IDLE_TICKS 237,759 AVG_IOWAIT_TICKS 236,143 AVG_SYS_TICKS 27,604 AVG_USER_TICKS 94,535 BUSY_TICKS 58,364,722 IDLE_TICKS 1,902,931 IOWAIT_TICKS 1,889,999 SYS_TICKS 221,707 USER_TICKS 757,174 OS_CPU_WAIT_TIME 1,248,100 NUM_CPUS 8 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_NUMS_IX 1,366,118 379 11.2 1.0 184,058 51 565 1.4 TS_INSERT_ONLY_IX 782,116 217 8.7 1.0 713,476 198 6,478 0.2 TS_NAME_IX 1,148,245 319 11.3 1.0 214,547 60 512 0.8 TS_SEP_CONF_REL_IX 656,060 182 11.4 1.0 631,929 176 28,534 0.1 TS_ADDR_IX 882,459 245 11.8 1.0 327,606 91 349 0.6 TS_SEP_REL_IX 492,372 137 11.2 1.0 376,768 105 539 0.1 TS_ATTR_IX 758,118 211 12.2 1.0 62,811 17 221 1.9 TS_SEP_ROLE_IX 634,354 176 11.2 1.0 174,078 48 232 2.2 TS_NAME_DT 785,480 218 5.2 1.0 3,168 1 19 4.7 TS_ADDR_DT 629,400 175 5.6 1.0 59,419 17 56 0.4 TS_NUMS_DT 667,887 186 5.7 1.0 2,732 1 18 3.9 TS_ATTR_DT 556,819 155 5.3 1.0 2,071 1 30 4.0 TS_SEP_ROLE_DT 533,046 148 5.4 1.0 2,506 1 19 5.8 TS_DSRC_IX 231,210 64 13.2 1.0 124,267 35 568 5.1 TS_SEP_CONF_IX 234,579 65 10.7 1.0 104,773 29 8,885 0.1 TS_SEP_REL_DT 235,413 65 5.5 1.0 6,620 2 2 0.0 TS_ENTITY_IX 178,281 50 12.1 1.0 52,366 15 5 16.0 TS_DSRC_DT 65,633 18 5.3 1.0 65,761 18 9 1.1 UNDOTBS1 85 0 3.3 1.0 20,460 6 4,872 0.3 SYSAUX 6,040 2 3.2 1.1 484 0 0 0.0 TS_INSERT_ONLY_DT 573 0 6.1 1.0 4,946 1 12 0.0 CFG_DATA 716 0 5.0 1.0 815 0 7 0.0 TS_SEP_CONF_REL_DT 6 0 6.7 1.0 1,266 0 8 0.0 TS_ENTITY_DT 704 0 6.1 1.0 493 0 0 0.0 SYSTEM 857 0 3.3 3.0 224 0 0 0.0 TS_SEP_CONF_DT 6 0 15.0 1.0 801 0 2 0.0 Tablespace IO Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ PERFSTAT 624 0 0.0 1.0 129 0 0 0.0 CFG_IX 203 0 6.4 1.0 467 0 2 0.0 TEMP 6 0 3.3 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ CFG_DATA /dev/rlv5_cfg 716 0 5.0 32 1.0 815 0 7 0.0 CFG_IX /dev/rlv2_cfg_ix 203 0 6.4 16 1.0 467 0 2 0.0 PERFSTAT /opt/oracle/ora10g/oradata/oraperf/perfstat.dbf 624 0 0.0 1 1.0 129 0 0 SYSAUX /dev/rlv5_sysaux1 6,040 2 3.2 32 1.1 484 0 0 SYSTEM /dev/rlv2_sys1 857 0 3.3 32 3.0 224 0 0 TEMP /dev/rlv2_tmp1 6 0 3.3 8 1.0 0 0 0 TS_ADDR_DT /dev/rlv4_addr_d2 159,095 44 5.6 ### 1.0 14,889 4 0 /dev/rlv4_addr_d4 151,344 42 6.0 ### 1.0 14,347 4 2 0.0 /dev/rlv7_addr_d1 158,861 44 5.4 ### 1.0 15,047 4 54 0.4 /dev/rlv7_addr_d3 160,100 44 5.5 ### 1.0 15,136 4 0 TS_ADDR_IX /dev/rlv3_addr_ix1 270,510 75 13.8 ### 1.0 107,752 30 342 0.4 /dev/rlv3_addr_ix2 240,299 67 13.7 ### 1.0 98,213 27 3 10.0 /dev/rlv8_addr_ix3 201,854 56 9.1 ### 1.0 72,403 20 0 /dev/rlv8_addr_ix4 169,796 47 9.2 ### 1.0 49,238 14 4 7.5 TS_ATTR_DT /dev/rlv4_attr_d2 316,930 88 5.3 ### 1.0 1,304 0 19 3.2 /dev/rlv7_attr_d1 239,889 67 5.2 ### 1.0 767 0 11 5.5 TS_ATTR_IX /dev/rlv3_attr_ix2 476,574 132 14.1 ### 1.0 41,291 11 131 2.2 /dev/rlv8_attr_i1 281,544 78 9.1 ### 1.0 21,520 6 90 1.6 TS_DSRC_DT /dev/rlv4_dsrc_d2 31,280 9 5.4 ### 1.0 31,366 9 0 /dev/rlv7_dsrc_d1 34,353 10 5.3 ### 1.0 34,395 10 9 1.1 File IO Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ TS_DSRC_IX /dev/rlv3_dsrc_ix1 123,963 34 16.4 ### 1.0 65,953 18 263 9.3 /dev/rlv8_dsrc_i1 107,247 30 9.4 ### 1.0 58,314 16 305 1.5 TS_ENTITY_DT /dev/rlv4_ent_d2 702 0 6.1 32 1.0 480 0 0 /dev/rlv7_ent_d1 2 0 20.0 1.0 13 0 0 TS_ENTITY_IX /dev/rlv3_ent_ix2 89,125 25 14.0 ### 1.0 26,188 7 5 16.0 /dev/rlv8_ent_i1 89,156 25 10.3 ### 1.0 26,178 7 0 TS_INSERT_ONLY_DT /dev/rlv7_insert_d1 1 0 10.0 1.0 732 0 12 0.0 /dev/rlv7_insert_d2 563 0 6.1 32 1.0 1,551 0 0 /dev/rlv7_insert_d3 3 0 0.0 8 1.0 1,655 0 0 /dev/rlv7_insert_d4 5 0 4.0 8 1.0 949 0 0 /dev/rlv7_insert_d5 1 0 0.0 1.0 59 0 0 TS_INSERT_ONLY_IX /dev/rlv8_insert_i1 269,351 75 8.4 ### 1.0 257,997 72 1,302 0.2 /dev/rlv8_insert_i2 237,455 66 8.7 ### 1.0 226,778 63 8 7.5 /dev/rlv8_insert_i3 275,310 76 9.0 ### 1.0 228,701 64 5,168 0.2 TS_NAME_DT /dev/rlv4_name_d2 390,453 108 5.3 ### 1.0 1,828 1 10 5.0 /dev/rlv7_name_d1 395,027 110 5.2 ### 1.0 1,340 0 9 4.4 TS_NAME_IX /dev/rlv3_name_ix2 631,654 175 13.3 ### 1.0 120,134 33 491 0.7 /dev/rlv8_name_i1 516,591 143 8.8 ### 1.0 94,413 26 21 4.3 TS_NUMS_DT /dev/rlv4_naums_d2 324,772 90 5.5 ### 1.0 1,185 0 9 4.4 /dev/rlv7_nums_d1 343,115 95 5.8 ### 1.0 1,547 0 9 3.3 TS_NUMS_IX /dev/rlv3_nums_ix2 File IO Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ 738,695 205 13.2 ### 1.0 103,841 29 230 2.6 TS_NUMS_IX /dev/rlv8_nums_i1 627,423 174 8.8 ### 1.0 80,217 22 335 0.6 TS_SEP_CONF_DT /dev/rlv4_sep_cf_d2 2 0 20.0 1.0 683 0 0 /dev/rlv7_sep_cf_d1 4 0 12.5 8 1.0 118 0 2 0.0 TS_SEP_CONF_IX /dev/rlv3_sep_cf_ix2 123,696 34 12.9 ### 1.0 58,050 16 0 /dev/rlv8_sep_cf_i1 110,883 31 8.3 ### 1.0 46,723 13 8,885 0.1 TS_SEP_CONF_REL_DT /dev/rlv4_sep_cr_d2 4 0 5.0 8 1.0 142 0 0 /dev/rlv7_sep_cr_d1 2 0 10.0 1.0 1,124 0 8 0.0 TS_SEP_CONF_REL_IX /dev/rlv3_sep_cr_ix2 333,491 93 13.8 ### 1.0 321,183 89 249 0.2 /dev/rlv8_sep_cr_i1 322,569 90 8.9 ### 1.0 310,746 86 28,285 0.1 TS_SEP_REL_DT /dev/rlv4_sep_rl_d2 117,856 33 5.7 ### 1.0 3,266 1 2 0.0 /dev/rlv7_sel_rl_d1 117,557 33 5.3 ### 1.0 3,354 1 0 TS_SEP_REL_IX /dev/rlv3_sep_rl_ix2 238,665 66 14.5 ### 1.0 191,668 53 538 0.1 /dev/rlv8_sep_rl_i1 253,707 70 8.1 ### 1.0 185,100 51 1 10.0 TS_SEP_ROLE_DT /dev/rlv4_sep_ro_d2 257,412 72 5.6 ### 1.0 1,285 0 9 6.7 /dev/rlv7_sep_ro_d1 275,634 77 5.3 ### 1.0 1,221 0 10 5.0 TS_SEP_ROLE_IX /dev/rlv3_sep_ro_ix2 370,743 103 12.9 ### 1.0 95,362 26 31 11.3 /dev/rlv8_sep_ro_i1 263,611 73 8.8 ### 1.0 78,716 22 201 0.7 UNDOTBS1 /dev/rlv5_undo1 85 0 3.3 16 1.0 20,460 6 4,872 0.3 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ CFG_IX /dev/rlv2_cfg_ix 57 89 56 0 0 SYSAUX /dev/rlv5_sysaux1 4,259 1,072 647 17 0 SYSTEM /dev/rlv2_sys1 464 245 31 1 0 CFG_DATA /dev/rlv5_cfg 279 352 77 11 0 PERFSTAT /opt/oracle/ora10g/oradata/oraperf/perfstat.dbf 624 0 0 0 0 UNDOTBS1 /dev/rlv5_undo1 63 20 1 0 0 TS_ADDR_DT /dev/rlv4_addr_d2 36,752 110,398 11,471 364 88 /dev/rlv4_addr_d4 23,165 113,848 13,795 455 70 /dev/rlv7_addr_d1 39,417 109,965 9,134 264 77 /dev/rlv7_addr_d3 37,206 113,095 9,417 294 72 TS_ADDR_IX /dev/rlv3_addr_ix1 22,373 115,343 77,999 30,990 23,808 /dev/rlv3_addr_ix2 23,092 102,008 67,248 26,355 21,631 /dev/rlv8_addr_ix3 29,712 110,317 42,359 12,957 6,541 /dev/rlv8_addr_ix4 22,541 93,700 36,948 11,140 5,425 TS_ATTR_DT /dev/rlv4_attr_d2 91,867 200,858 23,438 675 125 /dev/rlv7_attr_d1 72,240 152,833 14,358 354 123 TS_ATTR_IX /dev/rlv8_attr_i1 44,287 151,748 58,428 17,884 9,235 /dev/rlv3_attr_ix2 31,047 202,578 145,274 56,297 41,433 TS_DSRC_DT /dev/rlv4_dsrc_d2 8,385 20,615 2,220 54 12 /dev/rlv7_dsrc_d1 10,431 21,846 2,009 40 36 TS_DSRC_IX /dev/rlv8_dsrc_i1 File Read Histogram Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ 18,361 55,164 22,338 7,380 4,040 TS_DSRC_IX /dev/rlv3_dsrc_ix1 5,342 51,685 36,191 15,106 15,612 TS_NAME_DT /dev/rlv4_name_d2 118,035 241,882 29,646 802 147 /dev/rlv7_name_d1 130,805 239,373 24,036 665 204 TS_NAME_IX /dev/rlv8_name_i1 101,653 259,634 106,529 32,634 16,130 /dev/rlv3_name_ix2 81,310 258,610 173,636 63,093 55,037 TS_NUMS_DT /dev/rlv7_nums_d1 61,464 255,617 25,034 852 190 /dev/rlv4_naums_d2 87,885 210,928 25,064 764 150 TS_NUMS_IX /dev/rlv8_nums_i1 129,527 308,931 129,403 40,191 19,452 /dev/rlv3_nums_ix2 101,677 297,264 200,436 75,554 63,883 TS_ENTITY_DT /dev/rlv4_ent_d2 152 480 65 3 0 TS_ENTITY_IX /dev/rlv8_ent_i1 6,013 51,104 21,630 6,890 3,538 /dev/rlv3_ent_ix2 11,199 35,178 24,255 10,067 8,446 TS_SEP_REL_DT /dev/rlv4_sep_rl_d2 27,084 80,903 9,449 301 110 /dev/rlv7_sel_rl_d1 34,554 75,020 7,690 222 83 TS_SEP_REL_IX /dev/rlv8_sep_rl_i1 51,413 132,297 51,351 13,268 5,379 /dev/rlv3_sep_rl_ix2 28,319 92,842 65,346 28,074 24,069 TS_SEP_CONF_DT /dev/rlv7_sep_cf_d1 1 1 0 0 0 TS_SEP_CONF_IX /dev/rlv8_sep_cf_i1 28,186 47,945 25,138 6,909 2,922 /dev/rlv3_sep_cf_ix2 19,549 43,129 37,358 13,680 9,931 TS_SEP_ROLE_DT /dev/rlv4_sep_ro_d2 59,588 176,289 20,798 699 128 File Read Histogram Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ TS_SEP_ROLE_DT /dev/rlv7_sep_ro_d1 81,240 175,305 18,569 523 94 TS_SEP_ROLE_IX /dev/rlv8_sep_ro_i1 47,927 134,909 55,911 17,265 7,655 /dev/rlv3_sep_ro_ix2 43,219 152,940 106,410 40,197 27,979 TS_INSERT_ONLY_DT /dev/rlv7_insert_d2 69 453 39 1 0 /dev/rlv7_insert_d3 1 1 0 0 0 /dev/rlv7_insert_d4 2 2 0 0 0 TS_INSERT_ONLY_IX /dev/rlv8_insert_i1 35,524 153,123 60,847 15,025 4,888 /dev/rlv8_insert_i2 28,703 135,533 53,635 14,511 5,071 /dev/rlv8_insert_i3 27,705 158,520 64,729 17,711 6,600 TS_SEP_CONF_REL_DT /dev/rlv4_sep_cr_d2 1 1 0 0 0 TS_SEP_CONF_REL_IX /dev/rlv8_sep_cr_i1 56,495 166,285 68,020 22,475 9,365 /dev/rlv3_sep_cr_ix2 37,451 135,873 94,013 36,069 30,096 TEMP /dev/rlv2_tmp1 0 6 0 0 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 1,282,516 90 106,215,938 10,861,235 4,572,488 0 0 51,954 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> 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 86 219931 1087692 6439083 9123840 6439083 E 0 86 218898 1088402 6439435 9123840 6439435 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: ORAPERF/oraperf End Snap: 743 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtime P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds --- -------- ----- ------------ ------ -------------- ------------ -------- D 528 .1 127 1.5 776,925 46,741 .0 D 1,056 .2 255 1.5 732,828 39,502 .0 D 1,584 .3 382 1.4 692,784 29,567 .0 D 2,112 .4 510 1.3 660,300 50,489 .0 D 2,640 .5 637 1.2 628,465 36,169 .0 D 3,168 .6 765 1.2 601,432 26,316 .0 D 3,696 .7 892 1.1 575,677 28,696 .0 D 4,224 .8 1,020 1.1 550,832 39,782 .0 D 4,752 .9 1,147 1.0 528,717 35,522 .0 D 5,280 1.0 1,275 1.0 506,552 30,785 .0 D 5,312 1.0 1,283 1.0 505,263 59,912 1.0 D 5,808 1.1 1,402 1.0 486,595 47,163 .0 D 6,336 1.2 1,530 0.9 468,425 39,183 .0 D 6,864 1.3 1,657 0.9 451,171 39,971 .0 D 7,392 1.4 1,785 0.9 434,857 49,746 .0 D 7,920 1.5 1,912 0.8 419,405 26,308 .0 D 8,448 1.6 2,040 0.8 404,662 51,108 .0 D 8,976 1.7 2,167 0.8 390,479 39,810 .0 D 9,504 1.8 2,295 0.7 377,019 35,423 .0 D 10,032 1.9 2,422 0.7 373,440 42,650 .0 D 10,560 2.0 2,550 0.7 372,338 32,101 .0 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 46,988 11 0 undo header 2,152 1 1 undo block 2,731 0 0 free list 84 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> 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 64 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 2,000 1,719 202.6 0.0 .0 .0 .0 102,400 E 2,000 1,717 206.0 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 6,840 6,840 0 0 128K 256K 24 24 0 0 256K 512K 3 3 0 0 512K 1024K 39 39 0 0 1M 2M 6 6 0 0 4M 8M 1 1 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: ORAPERF/oraperf End Snap: 743 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 250 0.1 35,261.4 18,887.6 65.0 0 500 0.3 35,261.4 18,621.5 65.0 0 1,000 0.5 35,261.4 18,445.3 66.0 0 1,500 0.8 35,261.4 18,119.2 66.0 0 2,000 1.0 35,261.4 17,413.0 67.0 0 2,400 1.2 35,261.4 16,353.4 68.0 0 2,800 1.4 35,261.4 16,353.4 68.0 0 3,200 1.6 35,261.4 16,353.4 68.0 0 3,600 1.8 35,261.4 16,353.4 68.0 0 4,000 2.0 35,261.4 16,353.4 68.0 0 6,000 3.0 35,261.4 16,353.4 68.0 0 8,000 4.0 35,261.4 16,353.4 68.0 0 12,000 6.0 35,261.4 16,353.4 68.0 0 16,000 8.0 35,261.4 16,353.4 68.0 0 ------------------------------------------------------------- Enqueue activity DB/Inst: ORAPERF/oraperf Snaps: 742-743 -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- TX-Transaction (index contention) 7,815 7,815 0 7,717 20 2.59 HW-Segment High Water Mark 65,464 65,463 0 27 0 1.00 SQ-Sequence Cache 699 699 0 16 0 .63 TX-Transaction (allocate ITL entry) 6 6 0 6 0 .17 ------------------------------------------------------------- Rollback Segment Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->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 417.0 0.00 0 0 0 0 1 24,521.0 0.00 29,683,872 42 1 4 2 28,215.0 0.02 34,709,020 49 7 16 3 26,383.0 0.01 30,200,406 44 5 9 4 32,099.0 0.00 32,856,536 40 2 3 5 33,751.0 0.00 32,779,082 40 3 6 6 33,255.0 0.00 32,173,202 39 3 4 7 423.0 0.00 0 0 0 0 8 23,784.0 0.00 23,116,626 28 2 3 9 34,261.0 0.01 33,757,542 40 2 3 10 33,774.0 0.00 33,704,336 41 3 6 11 31,512.0 0.00 31,004,944 37 2 2 12 33,994.0 0.00 34,082,358 41 4 9 13 34,308.0 0.00 33,949,776 41 2 7 14 34,077.0 0.00 34,017,420 41 4 10 15 33,419.0 0.00 33,699,408 41 3 3 16 33,350.0 0.00 33,276,278 40 2 2 17 32,359.0 0.00 31,605,500 39 2 3 18 24,073.0 0.00 23,669,606 29 3 6 19 32,477.0 0.00 32,227,412 39 4 7 20 36,650.0 0.01 34,000,552 41 5 6 21 34,233.0 0.01 33,863,460 43 5 5 22 24,043.0 0.00 28,972,050 41 2 2 23 24,097.0 0.00 30,284,680 44 5 13 24 28,501.0 0.00 34,773,154 50 8 22 25 24,636.0 0.00 30,628,052 42 3 5 26 29,986.0 0.00 37,563,814 54 11 28 27 30,116.0 0.02 36,015,410 53 11 29 28 29,052.0 0.01 35,971,478 52 11 30 29 29,660.0 0.00 35,826,458 50 11 30 30 29,399.0 0.02 36,347,794 51 11 29 31 28,772.0 0.00 35,613,696 50 10 29 32 27,639.0 0.00 34,467,092 48 10 25 33 24,884.0 0.00 29,104,736 41 4 4 34 23,649.0 0.00 29,281,712 41 2 2 35 27,568.0 0.00 34,427,956 49 7 16 36 24,479.0 0.00 30,372,936 43 3 5 37 29,788.0 0.02 37,099,678 53 12 36 38 29,085.0 0.01 35,396,558 50 11 30 39 29,188.0 0.00 35,762,020 51 10 24 40 31,311.0 0.02 36,176,800 51 9 26 41 29,806.0 0.01 36,804,540 53 12 33 42 28,951.0 0.00 35,342,984 50 8 21 43 26,959.0 0.00 32,976,614 48 8 20 44 23,579.0 0.00 29,600,152 42 2 6 45 24,796.0 0.00 29,938,534 44 3 3 46 28,758.0 0.00 35,294,850 50 9 19 47 24,919.0 0.02 30,468,882 43 4 8 48 29,827.0 0.00 36,968,072 52 10 26 49 28,765.0 0.00 36,184,400 50 12 30 50 28,674.0 0.00 35,521,392 50 10 27 51 29,813.0 0.00 36,744,932 53 10 26 Rollback Segment Stats DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->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 ------ -------------- ------- --------------- -------- -------- -------- 52 29,486.0 0.01 36,189,182 51 10 30 53 29,425.0 0.00 36,240,834 50 11 30 54 27,847.0 0.01 33,402,470 48 5 12 55 23,897.0 0.00 29,586,430 41 4 8 56 24,281.0 0.00 29,931,678 43 4 11 57 423.0 0.00 0 0 0 0 58 24,485.0 0.00 29,800,314 42 3 7 59 30,046.0 0.00 37,247,812 52 12 32 60 29,136.0 0.00 36,291,620 53 12 33 61 28,984.0 0.00 35,970,866 50 9 25 62 28,893.0 0.00 35,771,438 51 8 22 63 30,131.0 0.01 36,330,886 51 10 28 64 29,614.0 0.00 36,523,612 53 11 30 65 27,347.0 0.01 33,722,404 49 7 19 66 23,497.0 0.00 29,705,776 41 3 6 ------------------------------------------------------------- Rollback Segment Storage DB/Inst: ORAPERF/oraperf Snaps: 742-743 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 389,120 6,553 389,120 1 8,515,584 815,782 24,244,224 2 7,467,008 868,057 27,389,952 3 6,418,432 764,732 24,244,224 4 9,564,160 815,830 26,341,376 5 8,515,584 878,990 26,341,376 6 8,515,584 878,844 26,341,376 7 4,321,280 776,985 22,147,072 8 8,515,584 858,388 26,341,376 9 8,515,584 838,462 26,341,376 10 8,515,584 895,870 25,292,800 11 8,515,584 789,976 25,292,800 12 6,418,432 883,993 26,341,376 13 9,564,160 789,962 27,389,952 14 9,564,160 814,780 23,195,648 15 8,515,584 878,903 25,292,800 16 8,515,584 860,062 25,358,336 17 8,515,584 859,165 26,341,376 18 8,515,584 905,566 27,389,952 19 9,564,160 860,051 25,292,800 20 6,418,432 878,872 26,341,376 21 6,418,432 761,911 25,292,800 22 8,515,584 815,830 23,195,648 23 8,515,584 780,870 23,195,648 24 9,564,160 787,123 28,438,528 25 8,515,584 911,084 24,244,224 26 9,564,160 758,376 29,487,104 27 8,515,584 743,241 28,438,528 28 8,515,584 752,309 28,438,528 29 8,515,584 903,689 27,389,952 30 7,467,008 862,347 28,438,528 31 9,564,160 913,435 28,438,528 32 8,515,584 827,097 26,341,376 33 8,515,584 830,992 22,147,072 34 8,515,584 911,174 23,195,648 35 9,564,160 886,223 27,389,952 36 9,564,160 924,833 24,244,224 37 8,515,584 743,150 28,438,528 38 7,467,008 828,320 27,389,952 39 9,564,160 782,836 27,389,952 40 10,612,736 906,735 27,389,952 41 8,515,584 769,699 27,389,952 42 7,467,008 886,209 27,389,952 43 8,515,584 878,168 26,341,376 44 8,515,584 860,056 23,195,648 45 6,418,432 761,131 24,244,224 46 8,515,584 863,319 27,389,952 47 8,515,584 911,238 23,195,648 48 8,515,584