STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 3968631959 tlax 1 02-Jul-08 20:50 10.2.0.4.0 NO Host Name: TRANXFER_TX Num CPUs: 16 Phys Memory (MB): 8,186 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 21 03-Jul-08 15:10:12 111 25.3 End Snap: 31 03-Jul-08 19:10:12 107 27.2 Elapsed: 240.00 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 460M Std Block Size: 8K Shared Pool Size: 108M Log Buffer: 6,817K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 56,597.34 104,756.01 Logical reads: 278,284.19 515,076.14 Block changes: 454.33 840.91 Physical reads: 726.27 1,344.25 Physical writes: 55.99 103.62 User calls: 128.60 238.02 Parses: 76.56 141.71 Hard parses: 11.06 20.47 Sorts: 333.01 616.36 Logons: 0.08 0.15 Executes: 673.08 1,245.81 Transactions: 0.54 % Blocks changed per Read: 0.16 Recursive Call %: 95.69 Rollback per transaction %: 0.41 Rows per Sort: 3.38 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.74 In-memory Sort %: 100.00 Library Hit %: 93.40 Soft Parse %: 85.56 Execute to Parse %: 88.62 Latch Hit %: 99.78 Parse CPU to Parse Elapsd %: 78.16 % Non-Parse CPU: 99.16 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 78.47 80.24 % SQL with executions>1: 79.69 80.48 % Memory for SQL w/exec>1: 88.70 86.13 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 14,982 67.6 db file sequential read 7,032,937 6,508 1 29.4 db file scattered read 293,792 431 1 1.9 SQL*Net more data to client 70,696 82 1 .4 db file parallel write 61,483 43 1 .2 ------------------------------------------------------------- Host CPU (CPUs: 16) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 8.82 1.00 90.18 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 6.66 % of busy CPU for Instance: 67.88 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 8,186.2 8,186.2 SGA use (MB): 584.0 584.0 PGA use (MB): 176.4 177.5 % Host Mem used for SGA+PGA: 9.3 9.3 ------------------------------------------------------------- Time Model System Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 27,492.8 99.7 DB CPU 15,304.9 55.5 parse time elapsed 457.5 1.7 hard parse elapsed time 434.2 1.6 hard parse (sharing criteria) elaps 111.2 .4 PL/SQL execution elapsed time 61.4 .2 PL/SQL compilation elapsed time 37.8 .1 hard parse (bind mismatch) elapsed 23.6 .1 connection management call elapsed 11.7 .0 sequence load elapsed time 5.0 .0 repeated bind elapsed time 2.1 .0 failed parse elapsed time 0.0 .0 DB time 27,571.0 background elapsed time 202.1 background cpu time 49.9 ------------------------------------------------------------- Wait Events DB/Inst: TLAX/tlax Snaps: 21-31 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- db file sequential read 7,032,937 0 6,508 1 904.0 db file scattered read 293,792 0 431 1 37.8 SQL*Net more data to client 70,696 0 82 1 9.1 db file parallel write 61,483 0 43 1 7.9 enq: TX - row lock contention 15 47 27 1805 0.0 log file sequential read 897 0 25 28 0.1 control file sequential read 18,706 0 17 1 2.4 log file sync 12,317 0 10 1 1.6 log file parallel write 18,068 0 9 1 2.3 read by other session 6,666 0 8 1 0.9 control file parallel write 6,445 0 6 1 0.8 direct path read temp 4,019 0 4 1 0.5 cursor: pin S wait on X 206 98 2 11 0.0 library cache load lock 73 0 2 27 0.0 Log archive I/O 843 0 2 2 0.1 log file switch completion 49 0 2 35 0.0 latch: cache buffers chains 2,699 0 1 0 0.3 direct path read 776 0 1 1 0.1 os thread startup 90 0 1 6 0.0 direct path write temp 3,701 0 0 0 0.5 kksfbc child completion 4 100 0 56 0.0 SGA: MMAN sleep for component shr 174 4 0 1 0.0 latch: shared pool 711 0 0 0 0.1 db file parallel read 7 0 0 9 0.0 row cache lock 125 0 0 0 0.0 enq: TX - index contention 10 0 0 4 0.0 latch free 17 0 0 2 0.0 rdbms ipc reply 109 0 0 0 0.0 buffer busy waits 589 0 0 0 0.1 direct path write 806 0 0 0 0.1 log file single write 36 0 0 0 0.0 SQL*Net break/reset to client 12 0 0 0 0.0 latch: library cache 2 0 0 1 0.0 latch: row cache objects 6 0 0 0 0.0 SQL*Net message from client 1,503,500 0 1,031,942 686 193.3 Streams AQ: qmn slave idle wait 511 0 14,288 27961 0.1 Streams AQ: qmn coordinator idle 1,058 52 14,288 13505 0.1 jobq slave wait 1,916 95 5,634 2941 0.2 Streams AQ: waiting for time mana 3 100 4,551 ###### 0.0 SQL*Net more data from client 4,526 0 11 2 0.6 SQL*Net message to client 1,503,496 0 1 0 193.3 ------------------------------------------------------------- Background Wait Events DB/Inst: TLAX/tlax Snaps: 21-31 -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- db file parallel write 61,483 0 43 1 7.9 db file sequential read 7,098 0 25 4 0.9 log file sequential read 897 0 25 28 0.1 control file sequential read 14,254 0 16 1 1.8 log file parallel write 18,068 0 9 1 2.3 control file parallel write 6,445 0 6 1 0.8 Log archive I/O 843 0 2 2 0.1 db file scattered read 326 0 1 4 0.0 direct path read 774 0 1 1 0.1 os thread startup 90 0 1 6 0.0 SGA: MMAN sleep for component shr 174 4 0 1 0.0 events in waitclass Other 169 0 0 0 0.0 direct path write 774 0 0 0 0.1 log file single write 36 0 0 0 0.0 latch: shared pool 20 0 0 0 0.0 rdbms ipc message 71,006 72 168,711 2376 9.1 pmon timer 5,720 93 14,391 2516 0.7 Streams AQ: qmn slave idle wait 511 0 14,288 27961 0.1 Streams AQ: qmn coordinator idle 1,058 52 14,288 13505 0.1 smon timer 188 8 13,684 72786 0.0 Streams AQ: waiting for time mana 3 100 4,551 ###### 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: TLAX/tlax Snaps: 21-31 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- LGWR wait for redo copy 52 100.0 Log archive I/O 843 2.8 1.4 95.5 .2 SGA: MMAN sleep for compon 174 86.8 7.5 1.1 4.6 SQL*Net break/reset to cli 12 100.0 SQL*Net more data to clien 70K 99.4 .1 .0 .0 .0 .1 .4 .0 buffer busy waits 589 100.0 control file parallel writ 6445 99.6 .2 .1 .1 .0 .0 .0 control file sequential re 18K 94.5 .1 .5 2.9 1.2 .5 .3 cursor: pin S 7 100.0 cursor: pin S wait on X 206 7.8 1.5 8.3 13.6 68.9 db file parallel read 7 14.3 42.9 28.6 14.3 db file parallel write 61K 87.5 7.9 2.8 .9 .5 .3 .2 db file scattered read 293K 88.6 2.8 1.7 3.7 1.7 .9 .5 db file sequential read 7032K 88.8 1.2 2.4 6.2 .9 .4 .1 .0 direct path read 776 93.8 .6 1.2 1.9 .8 1.0 .6 direct path read temp 4019 93.2 .3 .9 2.9 1.7 .6 .5 direct path write 806 100.0 direct path write temp 3701 99.5 .1 .0 .1 .2 .0 .1 enq: TX - index contention 10 50.0 10.0 20.0 10.0 10.0 enq: TX - row lock content 15 6.7 33.3 60.0 kksfbc child completion 4 100.0 latch free 17 70.6 23.5 5.9 latch: In memory undo latc 12 100.0 latch: cache buffers chain 2345 99.5 .0 .1 .0 .0 .0 .2 latch: cache buffers lru c 5 100.0 latch: library cache 2 50.0 50.0 latch: object queue header 4 100.0 latch: row cache objects 6 100.0 latch: session allocation 1 100.0 latch: shared pool 674 98.8 .7 .1 .3 library cache load lock 73 75.3 11.0 1.4 2.7 1.4 1.4 6.8 log file parallel write 18K 96.1 .9 2.8 .2 .0 .0 .0 .0 log file sequential read 897 7.7 8.5 4.5 2.1 7.0 32.2 38.0 log file single write 36 100.0 log file switch completion 49 8.2 2.0 8.2 6.1 10.2 16.3 49.0 log file sync 12K 98.4 .8 .4 .1 .1 .0 .2 os thread startup 90 98.9 1.1 rdbms ipc reply 109 100.0 read by other session 6666 87.4 2.6 2.7 4.8 1.5 .8 .2 row cache lock 125 96.0 .8 .8 2.4 undo segment extension 197 100.0 SQL*Net message from clien 1503K 59.6 9.5 6.9 16.5 3.5 .8 2.3 1.0 SQL*Net message to client 1503K 100.0 SQL*Net more data from cli 4526 54.7 10.1 30.5 2.9 .7 .3 .8 Streams AQ: qmn coordinato 1058 48.3 .1 51.6 Streams AQ: qmn slave idle 511 .2 99.8 Streams AQ: waiting for ti 3 100.0 class slave wait 8 100.0 jobq slave wait 1916 .4 99.6 pmon timer 5720 15.9 .0 .0 .2 83.9 rdbms ipc message 70K 6.1 .2 .2 .3 .6 .8 39.5 52.3 Wait Event Histogram DB/Inst: TLAX/tlax Snaps: 21-31 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- smon timer 188 30.9 1.1 68.1 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: TLAX/tlax Snaps: 21-31 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 15,305 -> Captured SQL accounts for 74.0% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 5370.66 2,447 2.19 35.1 5448.82 1,432,427,114 3728436829 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.PROD_POINTS,0.00)) + (A.REWO RK_QTY * NVL(B.REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.PR OD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.LOSS_POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE_MAP B WHERE A.S HIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A.SHIFT = :B2 AND 4796.40 2,459 1.95 31.3 4869.77 1,449,823,945 1157711408 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.SUB_PROD_POINTS,0.00)) + (A. REWORK_QTY * NVL(B.SUB_REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.SUB_PROD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.SUB_LOSS_ POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE _MAP B WHERE A.SHIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A. 323.62 11,758 0.03 2.1 359.17 22,783,670 2028107841 Module: TouchApp.exe SELECT ENDING_BALANCE FROM S_TRM_INV_HIST_SHIFT_DATE WHERE SHIFT _DATE=( SELECT MAX(SHIFT_DATE) FROM S_TRM_INV_HIST_SHIFT_DATE WH ERE SHIFT_DATE < :B3 AND PROD_POINT_ID = :B2 AND PART_ID = :B1 ) AND PROD_POINT_ID = :B2 AND PART_ID = :B1 173.47 304,908 0.00 1.1 175.93 4,114,978 2495995244 Module: w3wp.exe SELECT MR.NAME MACH_NAME FROM S_TR_TRACKING_MACHINE TM, S_TRL_MA CHINE_RESOURCE MR WHERE MR.MACHINE_ID = TM.MACHINE_ID AND TM.TRA CKING_NUM = :B2 AND TM.SEQUENCE_NUM = :B1 ORDER BY MR.NAME ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: TLAX/tlax Snaps: 21-31 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 27,571 -> Captured SQL accounts for 44.8% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 5448.82 2,447 2.23 19.8 5370.66 10 3728436829 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.PROD_POINTS,0.00)) + (A.REWO RK_QTY * NVL(B.REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.PR OD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.LOSS_POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE_MAP B WHERE A.S HIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A.SHIFT = :B2 AND 4869.77 2,459 1.98 17.7 4796.40 10 1157711408 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.SUB_PROD_POINTS,0.00)) + (A. REWORK_QTY * NVL(B.SUB_REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.SUB_PROD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.SUB_LOSS_ POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE _MAP B WHERE A.SHIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A. 359.17 11,758 0.03 1.3 323.62 453 2028107841 Module: TouchApp.exe SELECT ENDING_BALANCE FROM S_TRM_INV_HIST_SHIFT_DATE WHERE SHIFT _DATE=( SELECT MAX(SHIFT_DATE) FROM S_TRM_INV_HIST_SHIFT_DATE WH ERE SHIFT_DATE < :B3 AND PROD_POINT_ID = :B2 AND PART_ID = :B1 ) AND PROD_POINT_ID = :B2 AND PART_ID = :B1 ------------------------------------------------------------- SQL ordered by Gets DB/Inst: TLAX/tlax Snaps: 21-31 -> 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: 4,007,292,380 -> Captured SQL accounts for 73.5% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 1,449,823,945 2,459 589,599.0 36.2 4796.40 4869.77 1157711408 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.SUB_PROD_POINTS,0.00)) + (A. REWORK_QTY * NVL(B.SUB_REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.SUB_PROD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.SUB_LOSS_ POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE _MAP B WHERE A.SHIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A. 1,432,427,114 2,447 585,380.9 35.7 5370.66 5448.82 3728436829 Module: TouchApp.exe SELECT NVL(SUM( (A.PROD_QTY * NVL(B.PROD_POINTS,0.00)) + (A.REWO RK_QTY * NVL(B.REWORK_POINTS,0.00)) + (A.PROD_DEF_QTY * NVL(B.PR OD_DEF_POINTS,0.00)) + (A.LOSS_QTY * NVL(B.LOSS_POINTS,0.00)) ), 0.00) FROM S_TR_TRACKING A, S_TRL_ASSEMBLY_LINE_MAP B WHERE A.S HIFT_DATE = :B4 AND A.PROCESS_OWNER = :B3 AND A.SHIFT = :B2 AND ------------------------------------------------------------- SQL ordered by Reads DB/Inst: TLAX/tlax Snaps: 21-31 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,458,234 -> Captured SQL accounts for 7.9% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 246,473 4 61,618.3 2.4 17.23 216.38 2211776223 UPDATE S_TR_TRACKING TRKMAIN SET REQUEST_ID = :B2 WHERE ( TRKMAI N.TRACKING_NUM, TRKMAIN.SEQUENCE_NUM ) IN ( SELECT TRK.TRACKING_ NUM, TRK.SEQUENCE_NUM FROM S_TR_TRACKING TRK, S_TR_INV_POINT_PRO C IPTPRC WHERE IPTPRC.INV_POINT_ID = :B8 AND IPTPRC.BACK_FLUSH_I ND = 1 AND ((TRUNC(TRK.COMPLETED) <= :B7 AND TRK.COMPLETED >= :B 196,322 4 49,080.5 1.9 12.67 168.67 3944904487 UPDATE S_TR_TRACKING TRKMAIN SET REQUEST_ID = :B2 WHERE ( TRKMAI N.TRACKING_NUM, TRKMAIN.SEQUENCE_NUM ) IN ( SELECT TRK.TRACKING_ NUM, TRK.SEQUENCE_NUM FROM S_TR_TRACKING TRK, S_TR_INV_POINT_PRO C IPTPRC WHERE IPTPRC.INV_POINT_ID = :B8 AND IPTPRC.BACK_FLUSH_I ND = 1 AND ((TRUNC(TRK.COMPLETED) <= :B7 AND TRK.COMPLETED >= :B 194,969 4 48,742.3 1.9 11.55 155.77 466413066 UPDATE S_TR_TRACKING TRKMAIN SET REQUEST_ID = :B2 WHERE (TRKMAIN .TRACKING_NUM, TRKMAIN.SEQUENCE_NUM) IN ( SELECT TRK.TRACKING_NU M, TRK.SEQUENCE_NUM FROM S_TR_TRACKING TRK, S_TR_INV_POINT_PROC IPTPRC WHERE IPTPRC.INV_POINT_ID = :B8 AND IPTPRC.BACK_FLUSH_IND = 1 AND ((TRUNC(TRK.COMPLETED) <= :B7 AND TRK.COMPLETED >= :B6 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: TLAX/tlax Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 9,692,390 -> Captured SQL accounts for 80.9% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 419,157 419,157 1.0 0.00 0.00 1709463772 Module: w3wp.exe SELECT COUNT( D.DESCRIPTION ) FROM S_TR_TRACKING_DEFECT TD, S_TR _DEFECTS D WHERE D.DEFECT_ID = TD.DEFECT_ID AND TD.TRACKING_NUM = :B2 AND TD.SEQUENCE_NUM = :B1 419,157 419,157 1.0 0.00 0.00 2217275344 Module: w3wp.exe SELECT COUNT( DISTINCT E.NAME ) FROM S_TR_TRACKING_EMPLOYEE TE, S_TRL_EMPLOYEE E WHERE E.EMPLOYEE_ID = TE.EMPLOYEE_ID AND TE.TRA CKING_NUM = :B3 AND TE.SEQUENCE_NUM = :B2 AND TE.EMP_TYPE_IND <> :B1 AND TE.EMPLOYEE_ID NOT IN ( SELECT X.EMPLOYEE_ID FROM S_TR_ TRACKING_EMPLOYEE X WHERE X.TRACKING_NUM = :B3 AND X.SEQUENCE_NU 419,157 0 0.0 0.00 0.00 3501848323 Module: w3wp.exe SELECT TO_CHAR( S.USL ) SIGMA_UPPER FROM S_TR_TRACKING_SIGMA TS, S_TR_SIGMA S WHERE S.SIGMA_ID = TS.SIGMA_ID AND TS.TRACKING_NUM = :B2 AND TS.SEQUENCE_NUM = :B1 ORDER BY S.PARAMETER 419,157 419,157 1.0 0.00 0.00 4069307366 Module: w3wp.exe SELECT COUNT( DL.DESCRIPTION ) FROM S_TR_TRACKING_DEFECT TD, S_T R_DEFECT_LOCATION DL WHERE DL.DEFECT_LOCATION_ID = TD.DEFECT_LOC ATION_ID AND TD.TRACKING_NUM = :B2 AND TD.SEQUENCE_NUM = :B1 419,157 419,157 1.0 0.00 0.00 4204479790 Module: w3wp.exe SELECT COUNT( S.PARAMETER ) FROM S_TR_TRACKING_SIGMA TS, S_TR_SI GMA S WHERE S.SIGMA_ID = TS.SIGMA_ID AND TS.TRACKING_NUM = :B2 A ND TS.SEQUENCE_NUM = :B1 419,089 122,306 0.3 0.00 0.00 457777763 Module: w3wp.exe SELECT DISTINCT E.NAME EMP_NAME FROM S_TR_TRACKING_EMPLOYEE TE, S_TRL_EMPLOYEE E WHERE E.EMPLOYEE_ID = TE.EMPLOYEE_ID AND E.PLAN T_CODE=TE.PLANT_CODE AND TE.TRACKING_NUM = :B3 AND TE.SEQUENCE_N UM = :B2 AND TE.EMP_TYPE_IND <> :B1 AND TE.EMPLOYEE_ID NOT IN ( SELECT X.EMPLOYEE_ID FROM S_TR_TRACKING_EMPLOYEE X WHERE X.TRACK 419,089 0 0.0 0.00 0.00 590945298 Module: w3wp.exe SELECT TS.VALUE SIGMA_VALUE FROM S_TR_TRACKING_SIGMA TS, S_TR_SI GMA S WHERE S.SIGMA_ID = TS.SIGMA_ID AND TS.TRACKING_NUM = :B2 A ND TS.SEQUENCE_NUM = :B1 ORDER BY S.PARAMETER 419,089 36,194 0.1 0.00 0.00 593029203 Module: w3wp.exe SELECT D.DESCRIPTION DEF_DESC FROM S_TR_TRACKING_DEFECT TD, S_TR _DEFECTS D WHERE D.DEFECT_ID = TD.DEFECT_ID AND TD.PLANT_CODE=D. PLANT_CODE AND TD.TRACKING_NUM = :B2 AND TD.SEQUENCE_NUM = :B1 O RDER BY TD.MAIN, TD.MAJOR_IND, TD.DEFECT_ID, TD.DEFECT_LOCATION_ SQL ordered by Executions DB/Inst: TLAX/tlax Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 9,692,390 -> Captured SQL accounts for 80.9% 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 ------------ --------------- ---------------- ----------- ---------- ---------- ID, TD.DEFECT_TYPE, TD.GOTO_PROCESS_ID 419,089 36,194 0.1 0.00 0.00 934346144 Module: w3wp.exe SELECT DL.DESCRIPTION LOC_DESC FROM S_TR_TRACKING_DEFECT TD, S_T R_DEFECT_LOCATION DL WHERE DL.DEFECT_LOCATION_ID = TD.DEFECT_LOC ATION_ID AND TD.PLANT_CODE=DL.PLANT_CODE AND TD.TRACKING_NUM = : B2 AND TD.SEQUENCE_NUM = :B1 ORDER BY TD.MAIN, TD.MAJOR_IND, TD. DEFECT_ID, TD.DEFECT_LOCATION_ID, TD.DEFECT_TYPE, TD.GOTO_PROCES 419,089 36,194 0.1 0.00 0.00 1841818310 Module: w3wp.exe SELECT DECODE( TD.MAJOR_IND, 1, 'x', ' ' ) MAJOR_IND FROM S_TR_T RACKING_DEFECT TD WHERE TD.TRACKING_NUM = :B2 AND TD.SEQUENCE_NU M = :B1 ORDER BY TD.MAIN, TD.MAJOR_IND, TD.DEFECT_ID, TD.DEFECT_ LOCATION_ID, TD.DEFECT_TYPE, TD.GOTO_PROCESS_ID 419,089 419,089 1.0 0.00 0.00 2011543165 Module: w3wp.exe SELECT T.PROCESS_OWNER FROM S_TR_TRACKING T WHERE T.TRACKING_NUM = :B2 AND T.SEQUENCE_NUM = :B1 419,089 0 0.0 0.00 0.00 2216827316 Module: w3wp.exe SELECT S.PARAMETER SIGMA_DESC FROM S_TR_TRACKING_SIGMA TS, S_TR_ SIGMA S WHERE S.SIGMA_ID = TS.SIGMA_ID AND TS.TRACKING_NUM = :B2 AND TS.SEQUENCE_NUM = :B1 ORDER BY S.PARAMETER 419,089 0 0.0 0.00 0.00 4158308323 Module: w3wp.exe SELECT TO_CHAR( S.LSL ) SIGMA_LOW FROM S_TR_TRACKING_SIGMA TS, S _TR_SIGMA S WHERE S.SIGMA_ID = TS.SIGMA_ID AND TS.TRACKING_NUM = :B2 AND TS.SEQUENCE_NUM = :B1 ORDER BY S.PARAMETER 304,908 266,283 0.9 0.00 0.00 2495995244 Module: w3wp.exe SELECT MR.NAME MACH_NAME FROM S_TR_TRACKING_MACHINE TM, S_TRL_MA CHINE_RESOURCE MR WHERE MR.MACHINE_ID = TM.MACHINE_ID AND TM.TRA CKING_NUM = :B2 AND TM.SEQUENCE_NUM = :B1 ORDER BY MR.NAME 304,908 304,908 1.0 0.00 0.00 4282806716 Module: w3wp.exe SELECT COUNT( MR.NAME ) FROM S_TR_TRACKING_MACHINE TM, S_TRL_MAC HINE_RESOURCE MR WHERE MR.MACHINE_ID = TM.MACHINE_ID AND TM.TRAC KING_NUM = :B2 AND TM.SEQUENCE_NUM = :B1 239,498 3,411,312 14.2 0.00 0.00 199663413 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 214,192 205,439 1.0 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim SQL ordered by Executions DB/Inst: TLAX/tlax Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 9,692,390 -> Captured SQL accounts for 80.9% 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 ------------ --------------- ---------------- ----------- ---------- ---------- estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 114,196 114,196 1.0 0.00 0.00 347991012 Module: w3wp.exe SELECT UPPER( PARAM_VALUE ) FROM S_TR_SYSTEM_PARAM WHERE UPPER( PARAM_NAME ) = 'TM3' AND PLANT_CODE=:B1 114,196 114,196 1.0 0.00 0.00 761970350 Module: w3wp.exe SELECT MR1.NAME, MR2.NAME, CP.MOLD_POSITION, CP.CAVITY_NO FROM S _TRM_CAST_PLAN CP, S_TRL_MACHINE_RESOURCE MR1, S_TRL_MACHINE_RES OURCE MR2 WHERE CP.BARCODE = :B1 AND MR1.MACHINE_ID = CP.BENCH_I ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: TLAX/tlax Snaps: 21-31 -> End Parse Calls Threshold: 1000 Total Parse Calls: 1,102,511 -> Captured SQL accounts for 22.2% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 29,895 239,498 2.71 199663413 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 29,273 49,821 2.66 3694268570 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i .property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i. distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samp lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo d(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( 29,206 50,109 2.65 839312984 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh 28,503 214,192 2.59 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 27,193 108,205 2.47 2889900621 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj# =:1 18,966 66,301 1.72 2703824309 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa ce=:3 and remoteowner is null and linkname is null and subname i s null 17,021 55,226 1.54 431456802 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1 12,749 12,749 1.16 658874621 Module: TouchApp.exe select SYSDATE from dual ------------------------------------------------------------- Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 1,498,168 104.0 192.6 CPU used when call started 1,451,542 100.8 186.6 CR blocks created 66,159 4.6 8.5 Cached Commit SCN referenced 1,077 0.1 0.1 Commit SCN cached 4 0.0 0.0 DB time 2,760,177 191.7 354.8 DBWR checkpoint buffers written 115,629 8.0 14.9 DBWR checkpoints 18 0.0 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR thread checkpoint buffers wr 521 0.0 0.1 DBWR transaction table writes 1,025 0.1 0.1 DBWR undo block writes 46,656 3.2 6.0 IMU CR rollbacks 1,039 0.1 0.1 IMU Flushes 5,071 0.4 0.7 IMU Redo allocation size 46,626,188 3,237.9 5,993.1 IMU commits 1,999 0.1 0.3 IMU contention 112 0.0 0.0 IMU ktichg flush 47 0.0 0.0 IMU pool not allocated 2,486 0.2 0.3 IMU recursive-transaction flush 9 0.0 0.0 IMU undo allocation size 48,601,696 3,375.1 6,247.0 IMU- failed to get a private stra 2,486 0.2 0.3 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 SMON posted for undo segment shri 9 0.0 0.0 SQL*Net roundtrips to/from client 1,500,604 104.2 192.9 SQL*Net roundtrips to/from dblink 0 0.0 0.0 active txn count during cleanout 26,110 1.8 3.4 application wait time 2,708 0.2 0.4 background checkpoints completed 18 0.0 0.0 background checkpoints started 18 0.0 0.0 background timeouts 51,157 3.6 6.6 branch node splits 12 0.0 0.0 buffer is not pinned count 3,493,115,425 242,577.5 448,986.6 buffer is pinned count 21,693,653,160 1,506,503.7 2,788,387.3 bytes received via SQL*Net from c 213,875,663 14,852.5 27,490.5 bytes received via SQL*Net from d 0 0.0 0.0 bytes sent via SQL*Net to client 507,839,251 35,266.6 65,275.0 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgss 10,812,471 750.9 1,389.8 calls to kcmgas 321,946 22.4 41.4 calls to kcmgcs 10,728 0.8 1.4 change write time 1,468 0.1 0.2 cleanout - number of ktugct calls 395,308 27.5 50.8 cleanouts and rollbacks - consist 16,440 1.1 2.1 cleanouts only - consistent read 290,661 20.2 37.4 cluster key scan block gets 677,034 47.0 87.0 cluster key scans 508,535 35.3 65.4 commit batch/immediate performed 53 0.0 0.0 commit batch/immediate requested 53 0.0 0.0 commit cleanout failures: block l 11,188 0.8 1.4 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 153 0.0 0.0 commit cleanout failures: cannot 22 0.0 0.0 commit cleanouts 304,917 21.2 39.2 commit cleanouts successfully com 293,554 20.4 37.7 Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ commit immediate performed 53 0.0 0.0 commit immediate requested 53 0.0 0.0 commit txn count during cleanout 380,999 26.5 49.0 concurrency wait time 589 0.0 0.1 consistent changes 430,821 29.9 55.4 consistent gets 4,000,577,437 277,817.9 514,213.0 consistent gets - examination 229,514,093 15,938.5 29,500.5 consistent gets direct 18 0.0 0.0 consistent gets from cache 4,000,577,417 277,817.9 514,213.0 current blocks converted for CR 939 0.1 0.1 cursor authentications 92,488 6.4 11.9 data blocks consistent reads - un 81,259 5.6 10.4 db block changes 6,542,318 454.3 840.9 db block gets 6,714,939 466.3 863.1 db block gets direct 77 0.0 0.0 db block gets from cache 6,714,862 466.3 863.1 deferred (CURRENT) block cleanout 132,143 9.2 17.0 dirty buffers inspected 499,478 34.7 64.2 enqueue conversions 8,021 0.6 1.0 enqueue releases 464,288 32.2 59.7 enqueue requests 464,343 32.3 59.7 enqueue timeouts 53 0.0 0.0 enqueue waits 18 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 9,692,390 673.1 1,245.8 failed probes on index block recl 2 0.0 0.0 frame signature mismatch 182 0.0 0.0 free buffer inspected 10,983,412 762.7 1,411.8 free buffer requested 10,679,824 741.7 1,372.7 heap block compress 200,277 13.9 25.7 hot buffers moved to head of LRU 1,263,294 87.7 162.4 immediate (CR) block cleanout app 307,101 21.3 39.5 immediate (CURRENT) block cleanou 122,947 8.5 15.8 index crx upgrade (found) 2 0.0 0.0 index crx upgrade (positioned) 2,302,018 159.9 295.9 index fast full scans (full) 14,618 1.0 1.9 index fetch by key 19,664,143 1,365.6 2,527.5 index scans kdiixs1 187,781,142 13,040.4 24,136.4 leaf node 90-10 splits 282 0.0 0.0 leaf node splits 3,172 0.2 0.4 lob reads 4,318 0.3 0.6 lob writes 583 0.0 0.1 lob writes unaligned 583 0.0 0.1 logons cumulative 1,175 0.1 0.2 messages received 80,884 5.6 10.4 messages sent 80,884 5.6 10.4 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 3,767,198,458 261,611.0 484,215.7 opened cursors cumulative 2,136,411 148.4 274.6 parse count (failures) 5 0.0 0.0 parse count (hard) 159,256 11.1 20.5 parse count (total) 1,102,511 76.6 141.7 parse time cpu 12,526 0.9 1.6 parse time elapsed 16,027 1.1 2.1 physical read IO requests 7,331,538 509.1 942.4 physical read bytes 85,673,852,928 5,949,573.1 ############ Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical read total IO requests 7,351,577 510.5 944.9 physical read total bytes 86,836,228,608 6,030,293.7 ############ physical read total multi block r 298,795 20.8 38.4 physical reads 10,458,234 726.3 1,344.3 physical reads cache 10,381,272 720.9 1,334.4 physical reads cache prefetch 3,054,847 212.1 392.7 physical reads direct 76,962 5.3 9.9 physical reads direct (lob) 2 0.0 0.0 physical reads direct temporary t 76,170 5.3 9.8 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 507,397 35.2 65.2 physical write bytes 6,604,390,400 458,638.2 848,893.4 physical write total IO requests 553,497 38.4 71.1 physical write total bytes 8,729,628,160 606,224.2 1,122,060.2 physical write total multi block 123,418 8.6 15.9 physical writes 806,200 56.0 103.6 physical writes direct 77,021 5.4 9.9 physical writes direct (lob) 25 0.0 0.0 physical writes direct temporary 76,170 5.3 9.8 physical writes from cache 729,179 50.6 93.7 physical writes non checkpoint 747,749 51.9 96.1 pinned buffers inspected 812 0.1 0.1 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 1 0.0 0.0 process last non-idle time 14,401 1.0 1.9 recovery blocks read 0 0.0 0.0 recursive aborts on index block r 8 0.0 0.0 recursive calls 41,088,390 2,853.4 5,281.3 recursive cpu usage 1,403,639 97.5 180.4 redo blocks read for recovery 0 0.0 0.0 redo blocks written 1,656,570 115.0 212.9 redo buffer allocation retries 45 0.0 0.0 redo entries 3,426,270 237.9 440.4 redo log space requests 52 0.0 0.0 redo log space wait time 173 0.0 0.0 redo ordering marks 42,638 3.0 5.5 redo size 815,001,724 56,597.3 104,756.0 redo subscn max counts 379,729 26.4 48.8 redo synch time 958 0.1 0.1 redo synch writes 11,674 0.8 1.5 redo wastage 4,881,980 339.0 627.5 redo write time 969 0.1 0.1 redo writes 18,068 1.3 2.3 rollback changes - undo records a 52,678 3.7 6.8 rollbacks only - consistent read 49,800 3.5 6.4 rows fetched via callback 15,519,151 1,077.7 1,994.8 session connect time 0 0.0 0.0 session cursor cache hits 1,446,014 100.4 185.9 session logical reads 4,007,292,380 278,284.2 515,076.1 session pga memory 106,769,924 7,414.6 13,723.6 session pga memory max 157,815,744 10,959.4 20,284.8 session uga memory 420,938,316,612 29,231,827.5 ############ session uga memory max 1,395,730,476 96,925.7 179,399.8 shared hash latch upgrades - no w 2,571,370 178.6 330.5 shared hash latch upgrades - wait 3 0.0 0.0 sorts (disk) 1 0.0 0.0 Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ sorts (memory) 4,795,315 333.0 616.4 sorts (rows) 16,195,044 1,124.7 2,081.6 sql area evicted 146,830 10.2 18.9 sql area purged 6 0.0 0.0 summed dirty queue length 3,448,551 239.5 443.3 switch current to new buffer 187,458 13.0 24.1 table fetch by rowid 12,494,382,994 867,665.5 1,605,961.8 table fetch continued row 64,004 4.4 8.2 table scan blocks gotten 22,431,875 1,557.8 2,883.3 table scan rows gotten 2,203,432,646 153,016.2 283,217.6 table scans (cache partitions) 0 0.0 0.0 table scans (long tables) 2,700 0.2 0.4 table scans (short tables) 539,472 37.5 69.3 total number of times SMON posted 173 0.0 0.0 transaction rollbacks 53 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 undo change vector size 320,538,584 22,259.6 41,200.3 user I/O wait time 695,257 48.3 89.4 user calls 1,851,778 128.6 238.0 user commits 7,748 0.5 1.0 user rollbacks 32 0.0 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 52 0.0 0.0 workarea executions - optimal 1,386,733 96.3 178.2 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 8 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 111 107 opened cursors current 2,813 2,907 session cursor cache count 94,252 110,242 workarea memory allocated 0 408 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 18 4.50 ------------------------------------------------------------- OS Statistics DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 2,261,948 IDLE_TIME 20,778,627 SYS_TIME 229,834 USER_TIME 2,032,114 VM_IN_BYTES 35,299,328 VM_OUT_BYTES 56,492,032 PHYSICAL_MEMORY_BYTES 8,583,802,880 NUM_CPUS 16 NUM_CPU_CORES 4 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ TRANSFER_TRKGTAB_TWO_TS 6,352,733 441 0.8 1.2 403,892 28 1,949 1.0 TRANSFER_LGINDEX_ONE_TS 388,745 27 1.6 1.0 10,815 1 6 0.0 TRANSFER_TRKGIDEX_ONE_TS 246,127 17 1.7 1.1 51,881 4 11 0.0 TRANSFER_LGTAB_ONE_TS 240,096 17 1.5 6.8 4,328 0 5,138 1.2 TRANSFER_USERS_TS 31,807 2 4.7 2.6 8,328 1 5 0.0 SYSTEM 28,234 2 5.0 1.1 630 0 2 5.0 TRANSFER_DATA_ONE_TS 23,418 2 1.7 11.1 973 0 7 0.0 TRANSFER_UNDO_ONE_TS 3,382 0 3.3 1.0 19,139 1 135 0.0 TRANSFER_TEMP_ONE_TS 5,256 0 25.0 14.8 5,232 0 0 0.0 TRANSFER_TMINDEX_ONE_TS 6,089 0 3.7 1.4 306 0 0 0.0 SYSAUX 3,071 0 4.1 1.0 894 0 0 0.0 TRANSFER_SMTAB_ONE_TS 1,382 0 7.7 2.5 276 0 2 0.0 PERFSTAT 335 0 7.8 1.2 345 0 0 0.0 TRANSFER_SMINDEX_ONE_TS 387 0 7.3 1.0 128 0 0 0.0 TRANSFER_ARCHINDEX_ONE_TS 54 0 42.6 1.0 54 0 0 0.0 TRANSFER_ARCHLGTAB_TWO_TS 36 0 43.3 1.0 36 0 0 0.0 TRANSFER_TOOLS_TS 36 0 41.4 1.0 36 0 0 0.0 TRANSFER_XLGINDEX_ONE_TS 40 0 21.8 1.3 20 0 0 0.0 WEBMIS_USERS_TS 38 0 23.4 1.0 18 0 0 0.0 WEBMIS_SMTAB_ONE_TS 34 0 25.3 1.0 18 0 0 0.0 TRANSFER_PLAN_TABLE_TS 18 0 43.3 1.0 18 0 0 0.0 TRANSFER_TEMP_TWO_TS 18 0 57.2 1.0 18 0 0 0.0 TRANSFER_EXT_USERS_TS 18 0 44.4 1.0 18 0 0 0.0 TRANSFER_TMTAB_ONE_TS 18 0 46.7 1.0 18 0 0 0.0 TRANSFER_ARCHUSER_TS 18 0 42.8 1.0 18 0 0 0.0 BW_USR_TS 18 0 35.0 1.0 18 0 0 0.0 Tablespace IO Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ TRANSFER_TRKGTAB_ONE_TS 18 0 50.0 1.0 18 0 0 0.0 BW_TEMP_ONE_TS 18 0 43.3 1.0 18 0 0 0.0 BW_SMTAB_ONE_TS 18 0 40.6 1.0 18 0 0 0.0 BW_SMINDEX_ONE_TS 18 0 42.2 1.0 18 0 0 0.0 BW_LGTAB_ONE_TS 18 0 40.6 1.0 18 0 0 0.0 WEBMIS_DATA_ONE_TS 18 0 45.0 1.0 18 0 0 0.0 WEBMIS_ROLLBACK_ONE_TS 18 0 50.0 1.0 18 0 0 0.0 WEBMIS_SMINDEX_ONE_TS 18 0 49.4 1.0 18 0 0 0.0 WEBMIS_TEMP_ONE_TS 18 0 43.9 1.0 18 0 0 0.0 WEBMIS_TEMP_TWO_TS 18 0 45.6 1.0 18 0 0 0.0 BW_LGINDEX_ONE_TS 18 0 40.0 1.0 18 0 0 0.0 TRANSFER_MDTAB_ONE_TS 18 0 37.8 1.0 18 0 0 0.0 TRANSFER_TEMP_TOUCH_ONE_TS 18 0 41.1 1.0 18 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ BW_LGINDEX_ONE_TS E:\ORADATA\TLAX\BWLGINDEX01.DBF 18 0 40.0 1.0 18 0 0 BW_LGTAB_ONE_TS E:\ORADATA\TLAX\BWDATALARGE01.DBF 18 0 40.6 1.0 18 0 0 BW_SMINDEX_ONE_TS E:\ORADATA\TLAX\BWSMINDEX01.DBF 18 0 42.2 1.0 18 0 0 BW_SMTAB_ONE_TS E:\ORADATA\TLAX\BWDATASMALL01.DBF 18 0 40.6 1.0 18 0 0 BW_TEMP_ONE_TS E:\ORADATA\TLAX\BWTEMPONE01.DBF 18 0 43.3 1.0 18 0 0 BW_USR_TS E:\ORADATA\TLAX\BWUSERS01.DBF 18 0 35.0 1.0 18 0 0 PERFSTAT E:\ORADATA\TLAX\PERFSTAT01.DBF 335 0 7.8 ### 1.2 345 0 0 SYSAUX E:\ORADATA\TLAX\TLAXSYSAUX01.DBF 3,071 0 4.1 64 1.0 894 0 0 SYSTEM E:\ORADATA\TLAX\TLAXSYSTEM01.DBF 28,234 2 5.0 ### 1.1 630 0 2 5.0 TRANSFER_ARCHINDEX_ONE_T E:\ORADATA\TLAX\TLAXARCHINDEX01.DBF 18 0 41.1 1.0 18 0 0 E:\ORADATA\TLAX\TLAXARCHINDEX02.DBF 18 0 46.1 1.0 18 0 0 E:\ORADATA\TLAX\TLAXARCHINDEX03.DBF 18 0 40.6 1.0 18 0 0 TRANSFER_ARCHLGTAB_TWO_T E:\ORADATA\TLAX\TLAXARCHLGTABTWO01.DBF 18 0 42.2 1.0 18 0 0 E:\ORADATA\TLAX\TLAXARCHLGTABTWO02.DBF 18 0 44.4 1.0 18 0 0 TRANSFER_ARCHUSER_TS E:\ORADATA\TLAX\TLAXARCHUSER01.DBF 18 0 42.8 1.0 18 0 0 TRANSFER_DATA_ONE_TS E:\ORADATA\TLAX\TLAXDATAONE01.DBF 23,418 2 1.7 ### 11.1 973 0 7 0.0 TRANSFER_EXT_USERS_TS E:\ORADATA\TLAX\TLAXEXTUSERS01.DBF 18 0 44.4 1.0 18 0 0 TRANSFER_LGINDEX_ONE_TS E:\ORADATA\TLAX\TLAXLGINDEXONE01.DBF 388,745 27 1.6 ### 1.0 10,815 1 6 0.0 File IO Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ TRANSFER_LGTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATALARGE01.DBF 240,096 17 1.5 ### 6.8 4,328 0 5,138 1.2 TRANSFER_MDTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATAMED01.DBF 18 0 37.8 1.0 18 0 0 TRANSFER_PLAN_TABLE_TS E:\ORADATA\TLAX\TLAXPLANTABLE01.DBF 18 0 43.3 1.0 18 0 0 TRANSFER_SMINDEX_ONE_TS E:\ORADATA\TLAX\TLAXSMINDEXONE01.DBF 387 0 7.3 ### 1.0 128 0 0 TRANSFER_SMTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATASMALL01.DBF 1,382 0 7.7 ### 2.5 276 0 2 0.0 TRANSFER_TEMP_ONE_TS E:\ORADATA\TLAX\TLAXTEMPONE01.DBF 563 0 2.8 ### 9.4 547 0 0 E:\ORADATA\TLAX\TLAXTEMPONE02.DBF 309 0 4.3 64 3.3 690 0 0 E:\ORADATA\TLAX\TLAXTEMPONE03.DBF 3,663 0 34.4 32 17.8 3,356 0 0 E:\ORADATA\TLAX\TLAXTEMPONE04.DBF 453 0 3.7 64 6.8 395 0 0 E:\ORADATA\TLAX\TLAXTEMPONE05.DBF 268 0 2.2 32 12.4 244 0 0 TRANSFER_TEMP_TOUCH_ONE_ E:\ORADATA\TLAX\TLAXTEMPTOUCH01.DBF 18 0 41.1 1.0 18 0 0 TRANSFER_TEMP_TWO_TS E:\ORADATA\TLAX\TLAXTEMPTWO01.DBF 18 0 57.2 1.0 18 0 0 TRANSFER_TMINDEX_ONE_TS E:\ORADATA\TLAX\TLAXTMINDEXONE01.DBF 6,089 0 3.7 ### 1.4 306 0 0 TRANSFER_TMTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATATM01.DBF 18 0 46.7 1.0 18 0 0 TRANSFER_TOOLS_TS E:\ORADATA\TLAX\.ORA 18 0 41.7 1.0 18 0 0 E:\ORADATA\TLAX\TLAXTOOLS01.DBF 18 0 41.1 1.0 18 0 0 TRANSFER_TRKGIDEX_ONE_TS E:\ORADATA\TLAX\TLAXTRKGIDEXONE01.DBF 246,127 17 1.7 ### 1.1 51,881 4 11 0.0 TRANSFER_TRKGTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATATRKG01.DBF 18 0 50.0 1.0 18 0 0 File IO Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ TRANSFER_TRKGTAB_TWO_TS E:\ORADATA\TLAX\TLAXDATATRKG02.DBF 6,352,733 441 0.8 ### 1.2 403,892 28 1,949 1.0 TRANSFER_UNDO_ONE_TS E:\ORADATA\TLAX\TLAXUNDOONE01.DBF 1,960 0 3.3 ### 1.0 10,244 1 98 0.0 E:\ORADATA\TLAX\TLAXUNDOONE02.DBF 1,422 0 3.3 ### 1.0 8,895 1 37 0.0 TRANSFER_USERS_TS E:\ORADATA\TLAX\TLAXUSERS01.DBF 31,807 2 4.7 ### 2.6 8,328 1 5 0.0 TRANSFER_XLGINDEX_ONE_TS E:\ORADATA\TLAX\TLAXXLGINDEXONE01.DBF 40 0 21.8 32 1.3 20 0 0 WEBMIS_DATA_ONE_TS E:\ORADATA\WBSC\WEBMISDATAONE01.DBF 18 0 45.0 1.0 18 0 0 WEBMIS_ROLLBACK_ONE_TS E:\ORADATA\WBSC\WEBMISROLLBACKONE01.DBF 18 0 50.0 1.0 18 0 0 WEBMIS_SMINDEX_ONE_TS E:\ORADATA\WBSC\WEBMISSMINDEXONE01.DBF 18 0 49.4 1.0 18 0 0 WEBMIS_SMTAB_ONE_TS E:\ORADATA\WBSC\WEBMISDATASMALL01.DBF 34 0 25.3 16 1.0 18 0 0 WEBMIS_TEMP_ONE_TS E:\ORADATA\WBSC\WEBMISTEMPONE01.DBF 18 0 43.9 1.0 18 0 0 WEBMIS_TEMP_TWO_TS E:\ORADATA\WBSC\WEBMISTEMPTWO01.DBF 18 0 45.6 1.0 18 0 0 WEBMIS_USERS_TS E:\ORADATA\WBSC\WEBMISUSERS01.DBF 38 0 23.4 ### 1.0 18 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ TRANSFER_DATA_ONE_TS E:\ORADATA\TLAX\TLAXDATAONE01.DBF 4,709 382 1,444 375 77 18 TRANSFER_LGINDEX_ONE_TS E:\ORADATA\TLAX\TLAXLGINDEXONE01.DBF 309,316 19,257 46,036 11,484 2,130 484 TRANSFER_TMINDEX_ONE_TS E:\ORADATA\TLAX\TLAXTMINDEXONE01.DBF 3,148 587 1,723 326 95 30 TRANSFER_XLGINDEX_ONE_TS E:\ORADATA\TLAX\TLAXXLGINDEXONE01.DBF 0 4 11 3 1 0 TRANSFER_TRKGTAB_TWO_TS E:\ORADATA\TLAX\TLAXDATATRKG02.DBF 5,717,017 125,238 325,797 37,528 18,564 2,411 PERFSTAT E:\ORADATA\TLAX\PERFSTAT01.DBF 80 70 111 33 22 2 TRANSFER_TRKGIDEX_ONE_TS E:\ORADATA\TLAX\TLAXTRKGIDEXONE01.DBF 193,496 13,145 28,413 6,272 1,725 697 WEBMIS_USERS_TS E:\ORADATA\WBSC\WEBMISUSERS01.DBF 0 4 13 2 0 1 TRANSFER_USERS_TS E:\ORADATA\TLAX\TLAXUSERS01.DBF 8,442 4,346 11,743 2,733 673 145 WEBMIS_SMTAB_ONE_TS E:\ORADATA\WBSC\WEBMISDATASMALL01.DBF 0 2 10 4 0 0 TRANSFER_SMTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATASMALL01.DBF 52 185 650 133 47 6 TRANSFER_UNDO_ONE_TS E:\ORADATA\TLAX\TLAXUNDOONE01.DBF 1,232 172 392 102 31 14 TRANSFER_SMINDEX_ONE_TS E:\ORADATA\TLAX\TLAXSMINDEXONE01.DBF 67 62 195 32 8 2 SYSTEM E:\ORADATA\TLAX\TLAXSYSTEM01.DBF 9,121 4,003 11,288 2,344 958 159 TRANSFER_UNDO_ONE_TS E:\ORADATA\TLAX\TLAXUNDOONE02.DBF 936 83 271 80 30 4 SYSAUX E:\ORADATA\TLAX\TLAXSYSAUX01.DBF 1,004 513 1,401 86 17 2 TRANSFER_LGTAB_ONE_TS E:\ORADATA\TLAX\TLAXDATALARGE01.DBF 81,185 3,235 8,353 2,464 474 173 TRANSFER_TEMP_ONE_TS E:\ORADATA\TLAX\TLAXTEMPONE02.DBF File Read Histogram Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ 78 33 87 17 3 1 TRANSFER_TEMP_ONE_TS E:\ORADATA\TLAX\TLAXTEMPONE04.DBF 99 27 89 18 3 1 E:\ORADATA\TLAX\TLAXTEMPONE01.DBF 51 28 92 17 2 1 E:\ORADATA\TLAX\TLAXTEMPONE03.DBF 22 4 36 11 2 0 E:\ORADATA\TLAX\TLAXTEMPONE05.DBF 5 11 18 2 4 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: TLAX/tlax Snaps: 21-31 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------- D 57K 103 -297,150,569 10,381,426 729,427 0 0 7,255 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> 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 17 9852 68913 184320 184320 214247 E 0 14 4921 64170 184320 184320 219050 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: TLAX/tlax End Snap: 31 -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, 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 44 .1 5 2.6 190,457 179,322 82.0 D 88 .2 11 2.4 180,654 168,622 77.1 D 132 .3 16 2.4 176,913 164,539 75.2 D 176 .4 22 2.3 173,941 161,295 73.7 D 220 .5 27 2.1 159,375 145,397 66.5 D 264 .6 33 1.5 114,765 96,707 44.2 D 308 .7 38 1.1 80,403 59,202 27.1 D 352 .8 44 1.0 77,934 56,508 25.8 D 396 .9 49 1.0 76,486 54,927 25.1 D 440 1.0 55 1.0 75,143 53,460 24.4 D 460 1.0 57 1.0 74,489 52,747 24.1 D 484 1.1 60 1.0 73,705 51,891 23.7 D 528 1.1 66 1.0 72,066 50,102 22.9 D 572 1.2 71 0.9 69,674 47,491 21.7 D 616 1.3 77 0.9 64,821 42,195 19.3 D 660 1.4 82 0.8 61,426 38,489 17.6 D 704 1.5 88 0.8 59,307 36,176 16.5 D 748 1.6 93 0.8 58,127 34,889 15.9 D 792 1.7 99 0.8 57,376 34,069 15.6 D 836 1.8 104 0.8 56,921 33,573 15.3 D 880 1.9 110 0.7 54,761 31,215 14.3 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 7,119 8 1 undo block 107 0 0 undo header 28 0 0 segment header 1 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> B: Begin snap E: End snap (rows identified 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 --------------- ---------------- ------------------------- 95.8 23,800 1,048 %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 194 85 176.4 0.0 .0 .0 .0 39,731 E 194 84 177.5 0.4 .2 100.0 .0 39,731 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: TLAX/tlax Snaps: 21-31 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 1,360,372 1,360,372 0 0 64K 128K 130 130 0 0 128K 256K 996 996 0 0 256K 512K 20 20 0 0 512K 1024K 24,475 24,475 0 0 1M 2M 319 319 0 0 2M 4M 257 257 0 0 4M 8M 32 32 0 0 8M 16M 50 10 40 0 16M 32M 19 16 3 0 32M 64M 3 0 3 0 64M 128M 6 0 6 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: TLAX/tlax End Snap: 31 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 24 0.1 129,437.0 17,433.4 88.0 801 49 0.3 129,437.0 17,165.4 88.0 742 97 0.5 129,437.0 13,917.4 90.0 414 146 0.8 129,437.0 7,739.9 94.0 7 194 1.0 129,437.0 5,740.6 96.0 6 233 1.2 129,437.0 2,192.8 98.0 2 272 1.4 129,437.0 2,047.4 98.0 1 310 1.6 129,437.0 2,047.4 98.0 1 349 1.8 129,437.0 2,047.4 98.0 1 388 2.0 129,437.0 1,894.2 99.0 1 582 3.0 129,437.0 1,379.3 99.0 0 776 4.0 129,437.0 1,379.3 99.0 0 1,164 6.0 129,437.0 1,379.3 99.0 0 1,552 8.0 129,437.0 1,379.3 99.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time Hist Max Alloc is the Historical Max Allocation for still-connected processes -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ ------ B -------- 177.0 101.5 .0 1.6 1.3 9 16 113 Other 164.2 1.5 1.3 9 16 113 SQL 10.1 2.7 .1 .0 0 14 103 PL/SQL 2.5 1.5 .0 .0 0 0 113 E -------- 177.8 102.4 .0 1.7 1.4 9 16 107 Other 164.1 1.5 1.4 9 16 107 SQL 10.2 2.8 .1 .1 1 14 97 PL/SQL 3.7 1.4 .0 .0 0 0 107 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 30 ARC0 -------- 9.1 4.2 .0 9.1 9.1 Other 9.1 9.1 9.1 PL/SQL .0 .0 .0 .0 31 ARC1 -------- 9.1 4.3 .0 9.1 9.1 Other 9.1 9.1 9.1 PL/SQL .0 .0 .0 .0 7 LGWR -------- 9.1 4.2 .0 9.1 16.1 Other 9.1 9.1 16.1 PL/SQL .0 .0 .0 .0 98 J001 -------- 3.2 2.2 .0 3.2 3.8 Other 3.1 3.1 3.1 SQL .2 .1 .2 1.6 PL/SQL .0 .0 .0 .0 14 SHAD -------- 2.2 1.5 .0 2.2 4.0 Other 1.7 1.7 1.7 SQL .1 .0 .1 2.4 PL/SQL .1 .0 .1 .1 59 SHAD -------- 2.2 1.0 .0 2.2 6.3 Other 2.0 2.0 2.0 SQL .1 .0 .1 4.4 PL/SQL .0 .0 .0 .0 69 SHAD -------- 2.1 1.4 .0 2.1 7.3 Other 2.0 2.0 2.0 SQL .1 .0 .1 5.4 PL/SQL .0 .0 .0 .0 84 SHAD -------- 2.1 1.1 .0 2.1 3.6 Other 1.9 1.9 1.9 SQL .1 .0 .1 2.4 PL/SQL .1 .0 .1 .1 65 SHAD -------- 2.0 1.2 .0 2.0 5.5 Other 1.9 1.9 1.9 SQL .1 .0 .1 4.2 PL/SQL .0 .0 .0 .0 93 SHAD -------- 2.0 1.0 .0 2.0 4.3 Other 1.9 1.9 1.9 SQL .1 .1 .1 2.4 PL/SQL .0 .0 .0 .1 82 SHAD -------- 2.0 1.0 .0 2.0 2.9 Other 1.9 1.9 1.9 SQL .1 .0 .1 1.3 PL/SQL .0 .0 .0 .1 39 SHAD -------- 2.0 .9 .0 2.0 3.5 Other 1.8 1.8 1.8 SQL .1 .0 .1 2.3 PL/SQL .0 .0 .0 .1 81 SHAD -------- 2.0 1.0 .0 2.0 2.7 Other 1.8 1.8 1.8 SQL .1 .0 .1 1.3 PL/SQL .1 .0 .1 .1 34 SHAD -------- 1.9 1.0 .0 1.9 3.9 Other 1.8 1.8 1.8 SQL .1 .0 .1 2.4 PL/SQL .0 .0 .0 .1 Top Process Memory (by component) DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 74 SHAD -------- 1.9 1.2 .0 1.9 16.0 Other 1.8 1.8 1.9 SQL .1 .1 .1 14.1 PL/SQL .0 .0 .0 .0 23 SHAD -------- 1.9 1.0 .0 1.9 3.8 Other 1.7 1.7 1.7 SQL .1 .0 .1 2.3 PL/SQL .1 .0 .1 .1 21 SHAD -------- 1.9 1.1 .0 1.9 4.2 Other 1.7 1.7 1.7 SQL .1 .0 .1 2.4 PL/SQL .1 .0 .1 .1 78 SHAD -------- 1.9 1.0 .0 1.9 3.0 Other 1.7 1.7 1.7 SQL .1 .0 .1 1.4 PL/SQL .0 .0 .0 .1 44 SHAD -------- 1.8 1.0 .0 1.8 3.9 Other 1.7 1.7 1.7 SQL .1 .0 .1 2.4 PL/SQL .0 .0 .0 .1 97 SHAD -------- 1.8 1.0 .0 1.8 3.8 Other 1.7 1.7 1.7 SQL .1 .0 .1 2.4 PL/SQL .0 .0 .0 .1 E 30 ARC0 -------- 9.1 4.2 .0 9.1 9.1 Other 9.1 9.1 9.1 PL/SQL .0 .0 .0 .0 31 ARC1 -------- 9.1 4.3 .0 9.1 9.1 Other 9.1 9.1 9.1 PL/SQL .0 .0 .0 .0 7 LGWR -------- 9.1 4.2 .0 9.1 16.1 Other 9.1 9.1 16.1 PL/SQL .0 .0 .0 .0 75 J001 -------- 3.2 2.1 .0 3.2 3.8 Other 3.1 3.1 3.1 SQL .2 .1 .2 1.6 PL/SQL .0 .0 .0 .0 56 J000 -------- 2.7 1.6 .0 2.7 3.5 Other 2.1 2.1 2.1 SQL .5 .5 .5 1.6 PL/SQL .0 .0 .0 .0 33 SHAD -------- 2.4 1.4 .0 2.4 7.2 Other 2.3 2.3 2.3 SQL .1 .0 .1 5.5 PL/SQL .0 .0 .0 .0 65 SHAD -------- 2.4 1.3 .0 2.4 5.5 Other 2.3 2.3 2.3 SQL .1 .0 .1 4.2 PL/SQL .0 .0 .0 .0 96 SHAD -------- 2.3 1.3 .0 2.3 6.1 Other 2.1 2.1 2.1 SQL .1 .0 .1 4.3 PL/SQL .0 .0 .0 .1 Top Process Memory (by component) DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 69 SHAD -------- 2.1 1.4 .0 2.1 8.7 Other 2.0 2.0 3.3 SQL .1 .0 .1 5.4 PL/SQL .0 .0 .0 .0 90 SHAD -------- 2.1 .8 .0 2.1 4.0 Other 2.0 2.0 2.0 SQL .1 .0 .1 2.9 PL/SQL .0 .0 .0 .0 59 SHAD -------- 2.1 1.1 .0 2.1 5.3 Other 1.9 1.9 1.9 SQL .1 .0 .1 3.6 PL/SQL .0 .0 .0 .0 14 SHAD -------- 2.1 1.2 .0 2.1 4.0 Other 1.9 1.9 1.9 SQL .1 .0 .1 2.4 PL/SQL .1 .0 .1 .1 78 SHAD -------- 2.1 1.0 .0 2.1 3.0 Other 1.9 1.9 1.9 SQL .1 .0 .1 1.4 PL/SQL .1 .0 .1 .1 104 SHAD -------- 2.0 1.1 .0 2.0 4.2 Other 1.9 1.9 1.9 SQL .2 .0 .2 2.4 PL/SQL .0 .0 .0 .1 22 SHAD -------- 2.0 1.1 .0 2.0 3.9 Other 1.8 1.8 1.8 SQL .2 .0 .2 2.4 PL/SQL .1 .0 .1 .1 81 SHAD -------- 2.0 1.0 .0 2.0 2.7 Other 1.8 1.8 1.8 SQL .1 .0 .1 1.3 PL/SQL .1 .0 .1 .1 19 SHAD -------- 1.9 1.0 .0 1.9 4.0 Other 1.8 1.8 1.8 SQL .1 .0 .1 2.4 PL/SQL .1 .0 .1 .1 20 SHAD -------- 1.9 1.3 .0 1.9 2.9 Other 1.7 1.7 1.7 SQL .1 .0 .1 1.4 PL/SQL .1 .0 .1 .1 52 SHAD -------- 1.9 1.0 .0 1.9 4.0 Other 1.8 1.8 1.8 SQL .1 .0 .1 2.8 PL/SQL .0 .0 .0 .0 100 SHAD -------- 1.9 1.0 .0 1.9 2.7 Other 1.7 1.7 1.7 SQL .1 .0 .1 1.3 PL/SQL .1 .0 .1 .1 ------------------------------------------------------------- Enqueue activity DB/Inst: TLAX/tlax Snaps: 21-31 -> only enqueues with waits are shown -> 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 (row lock contention) 8 8 0 8 27 3,386.75 TX-Transaction (index contention) 10 10 0 10 0 4.50 ------------------------------------------------------------- Undo Segment Summary DB/Inst: TLAX/tlax Snaps: 21-31 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out Of Space count -> Undo segment block stats: uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 1 42.9 24,146 638 6 923.7/110 0/0 0/0/0/0/0/0 0.1 ------------------------------------------------------------- Undo Segment Stats DB/Inst: TLAX/tlax Snaps: 21-31 -> Most recent 35 Undostat rows, ordered by End Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ----------- 03-Jul 19:10 1,703 699 88 4 1,074 0/0 0/0/0/0/0/0 03-Jul 19:00 150 405 0 3 1,075 0/0 0/0/0/0/0/0 03-Jul 18:50 4,542 460 40 3 1,066 0/0 0/0/0/0/0/0 03-Jul 18:40 2,929 803 138 4 1,082 0/0 0/0/0/0/0/0 03-Jul 18:30 954 683 127 4 1,088 0/0 0/0/0/0/0/0 03-Jul 18:20 3,699 1,559 44 5 1,085 0/0 0/0/0/0/0/0 03-Jul 18:10 1,836 1,405 106 4 1,099 0/0 0/0/0/0/0/0 03-Jul 18:00 309 610 125 4 1,100 0/0 0/0/0/0/0/0 03-Jul 17:50 2,544 1,200 88 4 1,091 0/0 0/0/0/0/0/0 03-Jul 17:40 1,710 1,035 63 5 1,013 0/0 0/0/0/0/0/0 03-Jul 17:30 352 930 622 3 1,012 0/0 0/0/0/0/0/0 03-Jul 17:20 2,721 1,320 319 6 1,006 0/0 0/0/0/0/0/0 03-Jul 17:10 2,121 1,113 216 4 1,010 0/0 0/0/0/0/0/0 03-Jul 17:00 382 920 0 3 1,012 0/0 0/0/0/0/0/0 03-Jul 16:50 2,862 1,222 638 4 1,004 0/0 0/0/0/0/0/0 03-Jul 16:40 1,761 1,290 165 5 1,010 0/0 0/0/0/0/0/0 03-Jul 16:30 316 932 172 3 1,009 0/0 0/0/0/0/0/0 03-Jul 16:20 3,992 1,364 50 5 1,001 0/0 0/0/0/0/0/0 03-Jul 16:10 300 1,019 147 4 1,012 0/0 0/0/0/0/0/0 03-Jul 16:00 319 867 0 3 1,005 0/0 0/0/0/0/0/0 03-Jul 15:50 2,413 735 129 4 997 0/0 0/0/0/0/0/0 03-Jul 15:40 2,193 1,767 82 6 931 0/0 0/0/0/0/0/0 03-Jul 15:30 338 766 112 3 932 0/0 0/0/0/0/0/0 03-Jul 15:20 2,423 1,042 0 4 924 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: TLAX/tlax Snaps: 21-31 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ ASM db client latch 9,721 0.0 0 0 AWR Alerted Metric Eleme 79,596 0.0 0 0 Consistent RBA 18,086 0.0 0 0 FAL request queue 324 0.0 0 0 FAL subheap alocation 324 0.0 0 0 FIB s.o chain latch 284 0.0 0 0 FOB s.o list latch 4,876 0.0 0 0 In memory undo latch 282,485 0.1 0.0 0 26,916 0.0 JS queue state obj latch 86,370 0.0 0 0 JS slv state obj latch 246 0.0 0 0 KMG MMAN ready and start 4,978 0.0 0 0 KMG resize request state 88 0.0 0 0 KTF sga latch 35 0.0 0 3,788 0.0 KWQMN job cache list lat 3 0.0 0 0 KWQP Prop Status 1 0.0 0 0 MQL Tracking Latch 0 0 283 0.0 Memory Management Latch 1,054 0.0 0 4,978 0.0 OS process 3,840 0.0 0 0 OS process allocation 7,355 0.0 0 0 OS process: request allo 2,196 0.0 0 0 PL/SQL warning settings 61,905 0.0 0.0 0 0 SGA IO buffer pool latch 293,722 0.0 0.0 0 293,722 0.0 SQL memory manager latch 236,705 0.0 0.2 0 4,796 0.0 SQL memory manager worka 18,681,545 0.0 0.0 0 0 Shared B-Tree 514 0.0 0 0 active checkpoint queue 72,804 0.2 0.0 0 0 active service list 30,403 0.0 0 5,719 0.0 archive control 295 0.0 0 0 archive process latch 5,482 0.0 0 0 begin backup scn array 32 0.0 0 0 buffer pool 319 0.0 0 0 cache buffer handles 510,547 0.0 0 0 cache buffers chains 7,812,840,132 0.2 0.0 1 16,840,202 0.0 cache buffers lru chain 1,964,996 0.1 0.0 0 32,268,346 0.0 cache table scan latch 0 0 293,726 0.0 channel handle pool latc 2,456 0.0 0 0 channel operations paren 72,839 0.0 0.0 0 0 checkpoint queue latch 2,725,528 0.0 0.0 0 730,981 0.0 client/application info 6,419 0.0 0 0 commit callback allocati 54 0.0 0 0 compile environment latc 1,175 0.0 0 0 dml lock allocation 142,786 0.0 0.0 0 0 dummy allocation 2,354 0.0 0 0 enqueue hash chains 936,465 0.0 0.0 0 111 0.0 enqueues 840,686 0.0 0.0 0 0 event group latch 1,096 0.0 0 0 file cache latch 1,463 0.0 0 0 global KZLD latch for me 987 0.0 0 0 global tx hash mapping 260 0.0 0 0 hash table column usage 3,519 0.2 0.0 0 7,793,129 0.0 Latch Activity DB/Inst: TLAX/tlax Snaps: 21-31 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ hash table modification 320 0.0 0 0 job workq parent latch 0 0 196 0.0 job_queue_processes para 338 0.0 0 0 kks stats 495,931 0.0 0.0 0 0 kokc descriptor allocati 96 0.0 0 0 ksuosstats global area 970 0.0 0 0 ktm global data 206 0.0 0 0 kwqbsn:qsga 514 0.0 0 0 lgwr LWN SCN 18,367 0.1 0.0 0 0 library cache 10,607,137 0.0 0.0 0 503,606 1.4 library cache load lock 522,283 0.0 0.0 0 124 0.0 library cache lock 6,341,852 0.0 0.0 0 0 library cache lock alloc 73,431 0.0 0 0 library cache pin 2,065,522 0.0 0.0 0 0 library cache pin alloca 64,287 0.0 0 0 list of block allocation 16,702 0.0 0 0 loader state object free 440 0.0 0 0 longop free list parent 14 0.0 0 14 0.0 message pool operations 368 0.3 0.0 0 0 messages 286,260 0.1 0.0 0 0 mostly latch-free SCN 18,424 0.2 0.0 0 0 multiblock read objects 829,258 0.0 0.0 0 0 ncodef allocation latch 626 0.0 0 0 object queue header heap 307,955 0.0 0 17,399 0.0 object queue header oper 23,881,568 0.0 0.0 0 10,429 0.0 object stats modificatio 273 0.0 0 0 parallel query alloc buf 1,764 0.0 0 0 parameter list 4,816 0.0 0 0 parameter table allocati 1,283 0.0 0 0 post/wait queue 18,185 0.0 0 12,394 0.0 process allocation 2,196 0.0 0 1,096 0.0 process group creation 2,196 0.0 0 0 qmn task queue latch 2,044 0.0 0 0 redo allocation 110,096 0.1 0.0 0 3,425,987 0.0 redo copy 0 0 3,426,131 0.0 redo writing 137,669 0.2 0.0 0 0 resmgr group change latc 1,086 0.0 0 0 resmgr:active threads 2,343 0.0 0 0 resmgr:actses change gro 1,085 0.0 0 0 resmgr:free threads list 2,338 0.0 0 0 resmgr:schema config 5 0.0 0 0 row cache objects 44,799,554 0.0 0.0 0 480,829 0.1 rules engine rule set st 100 0.0 0 0 sequence cache 20,288 0.0 0 0 session allocation 25,776,147 0.1 0.0 0 0 session idle bit 3,808,448 0.0 0.0 0 0 session state list latch 2,058 0.0 0 0 session switching 626 0.0 0 0 session timer 5,719 0.0 0 0 shared pool 10,818,473 0.1 0.1 0 0 Latch Activity DB/Inst: TLAX/tlax Snaps: 21-31 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ shared pool simulator 18,108,184 0.0 0.0 0 0 simulator hash latch 298,029,969 0.0 0.0 0 0 simulator lru latch 731,479 0.2 0.0 0 294,991,840 0.5 slave class 8 0.0 0 0 slave class create 32 12.5 1.0 0 0 sort extent pool 2,159 0.6 0.0 0 0 state object free list 8 0.0 0 0 statistics aggregation 1,344 0.0 0 0 temp lob duration state 41 0.0 0 0 temporary table state ob 88 0.0 0 0 threshold alerts latch 2,200 0.0 0 0 transaction allocation 2,217 0.0 0 0 transaction branch alloc 5,962 0.0 0 0 undo global data 581,101 0.0 0.0 0 0 user lock 4,206 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 7,812,840,132 18,388,542 2,699 18,386,133 session allocation 25,776,147 32,091 1 32,090 shared pool 10,818,473 12,163 708 11,491 row cache objects 44,799,554 8,518 6 8,512 cache buffers lru chain 1,964,996 2,357 5 2,352 simulator hash latch 298,029,969 966 2 964 library cache 10,607,137 916 2 914 object queue header operat 23,881,568 855 4 851 In memory undo latch 282,485 332 12 320 SQL memory manager latch 236,705 63 11 52 slave class create 32 4 4 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: TLAX/tlax Snaps: 21-31 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- In memory undo latch ktichg: child 0 66 3 In memory undo latch ktiFlush: child 0 3 0 SQL memory manager latch qesmmIQueryRefreshBound 0 11 11 cache buffers chains kcbgtcr: kslbegin excl 0 5,581 4,728 cache buffers chains kcbrls: kslbegin 0 1,659 2,572 cache buffers chains kcbgcur: kslbegin 0 229 3 cache buffers chains kcbchg: kslbegin: bufs not 0 194 171 cache buffers chains kcbzib: multi-block read: 0 63 0 cache buffers chains kcbget: pin buffer 0 31 0 cache buffers chains kcbzwb 0 29 7 cache buffers chains kcbgtcr: fast path 0 17 87 cache buffers chains kcbzgb: scan from tail. no 0 12 0 cache buffers chains kcbgtcr: kslbegin shared 0 6 1 cache buffers chains kcbbic1 0 5 0 cache buffers chains kcb_pre_apply: kcbhq61 0 1 157 cache buffers chains kcbchg: kslbegin: call CR 0 1 77 cache buffers chains kcbnew: new latch again 0 1 0 cache buffers chains kcbbxsv 0 1 0 cache buffers chains kcbgtcr 0 1 0 cache buffers chains kcbnlc 0 1 1 cache buffers lru chain kcbzgws 0 5 0 library cache kglati 0 1 0 library cache kglpndl: child: after proc 0 1 0 object queue header oper kcbo_switch_mq_bg 0 3 2 object queue header oper kcbo_switch_cq 0 1 0 object queue header oper kcbw_link_q 0 1 0 object queue header oper kcbw_unlink_q 0 1 1 row cache objects kqrpre: find obj 0 3 4 row cache objects kqreqd 0 1 0 row cache objects kqreqd: reget 0 1 0 session allocation ksuprc 0 1 0 shared pool kghalo 0 621 254 shared pool kghfrunp: alloc: wait 0 40 0 shared pool kghupr1 0 26 207 shared pool kghfre 0 8 43 shared pool kghalp 0 5 176 shared pool kghasp 0 4 29 shared pool kgh: quiesce extents 0 3 0 shared pool kghfrunp: clatch: wait 0 1 2 shared pool kgh: sim resz update 0 1 0 shared pool kgh_next_free 0 1 0 simulator hash latch kcbsacc: lookup dba 0 2 2 slave class create ksvcreate 0 4 0 ------------------------------------------------------------- Mutex Sleep DB/Inst: TLAX/tlax Snaps: 21-31 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Cursor Pin kkslce [KKSCHLPIN2] 147 1.5 Cursor Pin kksfbc [KKSCHLFSP2] 66 0.7 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: TLAX/tlax Snaps: 21-31 ->"Pct Misses" should be very low (< 2% in most cases) ->"Final Usage" is the number of cache entries being used in End Snapshot Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 253 67.2 0 8 0 dc_database_links 1,982 13.6 0 0 0 dc_global_oids 432 41.9 0 0 0 dc_histogram_data 1,497,997 16.0 0 0 278 dc_histogram_defs 1,376,322 24.1 0 0 391 dc_object_grants 170,188 22.6 0 0 15 dc_object_ids 6,281,124 1.3 0 0 216 dc_objects 478,951 25.0 0 1 174 dc_profiles 1,085 28.9 0 0 0 dc_rollback_segments 3,076 0.0 0 0 35 dc_segments 1,380,341 7.3 0 20 124 dc_sequences 5,963 25.3 0 5,963 0 dc_tablespace_quotas 6 50.0 0 6 0 dc_tablespaces 1,927,717 0.3 0 0 8 dc_usernames 314,966 0.9 0 0 3 dc_users 2,369,658 0.4 0 0 5 outstanding_alerts 960 97.5 0 0 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: TLAX/tlax Snaps: 21-31 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 419 39.1 34,484 7.6 2,419 0 CLUSTER 4,339 0.6 12,439 0.4 28 0 INDEX 154 57.8 409 24.4 11 0 SQL AREA 1,097,806 8.7 11,066,287 5.1 206,692 101,297 TABLE/PROCEDURE 454,629 2.0 1,423,041 18.0 164,348 0 TRIGGER 130 63.1 939 11.5 26 0 ------------------------------------------------------------- Rule Sets DB/Inst: TLAX/tlax Snaps: 21-31 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: TLAX/tlax End Snap: 31 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> 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 Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 84 .8 14 1,827 ####### 1.0 12,740 3.7 26,816,515 96 .9 25 2,658 ####### 1.0 8,143 2.3 26,901,857 108 1.0 36 3,605 ####### 1.0 3,484 1.0 26,981,164 120 1.1 47 4,581 ####### 1.0 1 .0 27,054,982 132 1.2 58 5,435 ####### 1.0 1 .0 27,121,791 144 1.3 69 6,196 ####### 1.1 1 .0 27,180,544 156 1.4 80 7,118 ####### 1.1 1 .0 27,231,214 168 1.6 91 7,899 ####### 1.1 1 .0 27,275,028 180 1.7 102 8,543 ####### 1.1 1 .0 27,311,462 192 1.8 113 9,353 ####### 1.2 1 .0 27,341,331 204 1.9 124 9,955 ####### 1.2 1 .0 27,365,863 216 2.0 135 10,665 ####### 1.2 1 .0 27,386,077 ------------------------------------------------------------- SGA Target Advisory DB/Inst: TLAX/tlax End Snap: 31 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 146 .3 360,301 1.6 190,378,115 292 .5 332,343 1.5 176,835,516 438 .8 225,325 1.0 80,354,738 584 1.0 218,762 1.0 74,450,790 730 1.3 208,043 1.0 64,787,077 876 1.5 200,627 .9 58,108,842 1,022 1.8 196,908 .9 54,743,666 1,168 2.0 196,908 .9 54,743,666 ------------------------------------------------------------- SGA Memory Summary DB/Inst: TLAX/tlax Snaps: 21-31 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 482,344,960 Fixed Size 1,298,160 Redo Buffers 7,090,176 Variable Size 121,635,088 -------------------- -------------------- sum 612,368,384 ------------------------------------------------------------- SGA breakdown difference DB/Inst: TLAX/tlax Snaps: 21-31 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- -------- java p free memory 4.0 4.0 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 3.0 3.0 0.00 shared ASH buffers 11.7 11.7 0.00 shared CCursor 4.0 4.9 22.20 shared Heap0: KGL 2.3 2.7 18.56 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGH: NO ACCESS 15.2 11.3 -25.59 shared KQR X PO 5.8 6.5 11.82 shared KSFD SGA I/O b 3.8 3.8 0.00 shared KTI-UNDO 2.4 2.4 0.00 shared PCursor 1.8 2.1 17.41 shared db_block_hash_buckets 1.5 1.5 0.00 shared dbwriter coalesce buffer 2.0 2.0 0.00 shared event statistics per sess 3.0 3.0 0.00 shared free memory 23.3 21.3 -8.24 shared kglsim hash table bkts 2.0 2.0 0.00 shared kglsim object batch 1.3 1.3 0.00 shared library cache 8.0 8.4 4.53 shared private strands 2.3 2.3 0.00 shared row cache 3.6 3.6 0.00 shared sessions 1.6 1.6 0.00 shared sql area 2.0 1.8 -10.18 shared trace buffer 2.2 2.2 0.00 buffer_cache 460.0 460.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 6.8 6.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: TLAX/tlax Snaps: 21-31 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 8.20 9.23 11.16 Cursor to Parent ratio: 2.43 2.43 -.05 Total Cursors: 935 1,010 7.43 Total Parents: 384 415 7.47 ------------------------------------------------------------- init.ora Parameters DB/Inst: TLAX/tlax Snaps: 21-31 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest E:\ORADBA\TRANSFER\TLAX\ADUMP background_dump_dest E:\ORADBA\TRANSFER\TLAX\BDUMP compatible 10.2.0.4.0 control_files E:\ORADATA\TLAX\TLAXCONTROL01.CTL core_dump_dest E:\ORADBA\TRANSFER\TLAX\CDUMP db_block_size 8192 db_file_multiblock_read_count 16 db_name TLAX job_queue_processes 100 log_archive_dest D:\ORACLE\TRANSFER\tlax\arch\ open_cursors 300 pga_aggregate_target 203423744 processes 200 remote_login_passwordfile EXCLUSIVE sessions 335 sga_target 612368384 undo_management AUTO undo_retention 500 undo_tablespace TRANSFER_undo_one_ts user_dump_dest E:\ORADBA\TRANSFER\TLAX\UDUMP ------------------------------------------------------------- End of Report ( E:\STATS_PACK_REPORT\FROM_SNAP_ID_21_TO_31.TXT )