STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1363123452 svtlsdb 1 09-Sep-08 21:45 10.2.0.3.0 NO Host Name: win05 Num CPUs: 4 Phys Memory (MB): 0 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 18 09-Sep-08 22:21:11 29 310.9 End Snap: 19 09-Sep-08 22:57:01 27 576.6 Elapsed: 35.83 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 1,600M Std Block Size: 8K Shared Pool Size: 352M Log Buffer: 13,921K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 84,099.23 8,069.14 Logical reads: 10,478.15 1,005.36 Block changes: 539.79 51.79 Physical reads: 19.66 1.89 Physical writes: 192.53 18.47 User calls: 123.70 11.87 Parses: 135.84 13.03 Hard parses: 0.08 0.01 Sorts: 38.55 3.70 Logons: 2.02 0.19 Executes: 440.82 42.30 Transactions: 10.42 % Blocks changed per Read: 5.15 Recursive Call %: 87.91 Rollback per transaction %: 0.00 Rows per Sort: 137.27 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.92 In-memory Sort %: 99.87 Library Hit %: 99.87 Soft Parse %: 99.94 Execute to Parse %: 69.18 Latch Hit %: 99.98 Parse CPU to Parse Elapsd %: 97.35 % Non-Parse CPU: 96.97 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 51.31 55.09 % SQL with executions>1: 51.39 53.72 % Memory for SQL w/exec>1: 58.63 65.09 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 787 48.0 log file sync 23,006 239 10 14.6 log file parallel write 22,808 215 9 13.1 HS message to agent 152,347 214 1 13.0 db file sequential read 19,057 168 9 10.2 ------------------------------------------------------------- Host CPU (CPUs: 4) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 7.45 8.55 29.69 68.16 1.91 0.40 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 10.18 % of busy CPU for Instance: 10.37 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): .1 .1 SGA use (MB): 2,000.0 2,000.0 PGA use (MB): 455.1 783.4 % Host Mem used for SGA+PGA: 3629563.7 3911072.4 ------------------------------------------------------------- Time Model System Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 2,032.1 87.4 PL/SQL execution elapsed time 1,137.0 48.9 DB CPU 857.8 36.9 parse time elapsed 32.4 1.4 connection management call elapsed 25.0 1.1 hard parse elapsed time 4.3 .2 hard parse (sharing criteria) elaps 2.5 .1 sequence load elapsed time 0.2 .0 hard parse (bind mismatch) elapsed 0.1 .0 PL/SQL compilation elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 DB time 2,325.8 background elapsed time 355.6 background cpu time 14.9 ------------------------------------------------------------- Wait Events DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 --------------------------------- ------------ ------ ---------- ------ -------- log file sync 23,006 0 239 10 1.0 log file parallel write 22,808 0 215 9 1.0 HS message to agent 152,347 0 214 1 6.8 db file sequential read 19,057 0 168 9 0.9 control file parallel write 960 0 13 13 0.0 latch: row cache objects 7 0 1 115 0.0 control file single write 82 0 1 8 0.0 buffer busy waits 145 0 1 4 0.0 SQL*Net more data to client 55,135 0 1 0 2.5 enq: TX - index contention 56 0 0 7 0.0 log file switch completion 4 0 0 80 0.0 control file sequential read 732 0 0 0 0.0 latch: session allocation 26 0 0 5 0.0 buffer deadlock 2 100 0 68 0.0 Data file init write 33 0 0 4 0.0 latch free 38 0 0 3 0.0 latch: library cache 34 0 0 3 0.0 db file single write 4 0 0 12 0.0 log file single write 4 0 0 12 0.0 direct path read temp 8,511 0 0 0 0.4 latch: cache buffers chains 22 0 0 1 0.0 latch: shared pool 12 0 0 2 0.0 LGWR wait for redo copy 64 0 0 0 0.0 rdbms ipc reply 5 0 0 3 0.0 enq: TX - row lock contention 1 0 0 13 0.0 latch: enqueue hash chains 3 0 0 2 0.0 direct path read 380 0 0 0 0.0 log file sequential read 4 0 0 1 0.0 latch: library cache lock 4 0 0 0 0.0 latch: library cache pin 7 0 0 0 0.0 SQL*Net message from client 211,642 0 25,455 120 9.4 Streams AQ: qmn slave idle wait 77 0 2,106 27345 0.0 Streams AQ: qmn coordinator idle 154 50 2,106 13673 0.0 SQL*Net message to client 211,641 0 1 0 9.4 SQL*Net more data from client 10,876 0 0 0 0.5 ------------------------------------------------------------- Background Wait Events DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> %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 --------------------------------- ------------ ------ ---------- ------ -------- log file parallel write 22,808 0 215 9 1.0 control file parallel write 952 0 13 13 0.0 control file single write 78 0 1 8 0.0 log file single write 4 0 0 12 0.0 db file sequential read 5 0 0 9 0.0 events in waitclass Other 68 0 0 0 0.0 control file sequential read 336 0 0 0 0.0 log file sequential read 4 0 0 1 0.0 rdbms ipc message 29,195 23 18,200 623 1.3 Streams AQ: qmn slave idle wait 77 0 2,106 27345 0.0 Streams AQ: qmn coordinator idle 154 50 2,106 13673 0.0 smon timer 4,449 0 2,098 472 0.2 pmon timer 1,277 100 2,096 1641 0.1 ------------------------------------------------------------- Wait Event Histogram DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- Data file init write 33 36.4 57.6 3.0 3.0 HS message to agent 152K 89.5 1.9 2.0 1.2 3.7 .9 .7 LGWR wait for redo copy 64 96.9 1.6 1.6 SQL*Net more data to clien 55K 100.0 .0 buffer busy waits 145 75.2 4.8 5.5 1.4 3.4 6.2 3.4 buffer deadlock 2 50.0 50.0 control file parallel writ 960 8.2 79.4 9.9 2.5 control file sequential re 732 99.5 .1 .1 .1 .1 control file single write 82 89.0 3.7 6.1 1.2 db file sequential read 19K 6.1 3.5 18.8 46.9 18.4 3.8 2.5 db file single write 4 25.0 75.0 direct path read 380 100.0 direct path read temp 8511 100.0 direct path write 20 100.0 direct path write temp 89 100.0 enq: TX - index contention 56 8.9 12.5 12.5 41.1 16.1 7.1 1.8 enq: TX - row lock content 1 100.0 latch free 38 63.2 7.9 2.6 13.2 7.9 5.3 latch: cache buffers chain 22 90.9 9.1 latch: enqueue hash chains 3 66.7 33.3 latch: library cache 34 76.5 2.9 11.8 5.9 2.9 latch: library cache lock 4 75.0 25.0 latch: library cache pin 6 100.0 latch: redo allocation 1 100.0 latch: redo writing 1 100.0 latch: row cache objects 7 28.6 14.3 57.1 latch: session allocation 26 73.1 3.8 3.8 11.5 7.7 latch: shared pool 12 58.3 8.3 8.3 25.0 log file parallel write 22K 2.6 6.8 27.8 59.5 2.6 .7 log file sequential read 4 75.0 25.0 log file single write 4 25.0 50.0 25.0 log file switch completion 4 100.0 log file sync 23K .8 2.4 6.5 23.6 59.1 6.1 1.5 rdbms ipc reply 5 60.0 20.0 20.0 SQL*Net message from clien 211K 74.0 5.1 2.1 2.3 2.6 3.3 8.7 1.9 SQL*Net message to client 211K 100.0 .0 .0 .0 .0 SQL*Net more data from cli 10K 99.8 .1 .0 .0 .0 .0 Streams AQ: qmn coordinato 154 50.0 50.0 Streams AQ: qmn slave idle 77 100.0 pmon timer 1277 42.1 .5 .6 .4 .2 .1 .2 56.0 rdbms ipc message 29K 8.8 .8 1.5 4.2 7.8 12.1 50.7 14.0 smon timer 4449 .2 .1 .3 .6 1.1 2.2 84.9 10.5 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 858 -> Captured SQL accounts for 130.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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 130.93 121 1.08 15.3 286.43 829,669 3843149556 Module: JDBC Thin Client SELECT WRKPKG.COMPONENTID,WRKPKG.ITEMID,WRKPKG.PRIORITY,WRKPKG.P ROCESSITEMID,WRKPKG.PROCESSLASTCHG,WRKPKG.LASTACCUSERID,WRKPKG.L ASTACCTIME,WRKPKG.NOTIFYFLAG,WRKPKG.NOTIFYTIME,WRKPKG.SUSPENDFLA G,WRKPKG.RESUMETIME,WRKPKG.ASSIGNEDUSERID,WRKPKG.RTARGETITEMID,W RKPKG.RTARGETITEMTYPEID,WRKPKG.RTARGETCOMPID,WRKPKG.RTARGETCOMPT 82.20 4,315 0.02 9.6 162.02 999,986 1284099840 Module: JDBC Thin Client begin productLSPKG.productCREATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 77.52 2,161 0.04 9.0 147.10 987,260 3379448481 Module: JDBC Thin Client begin productLSPKG.productCREATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 62.41 3,237 0.02 7.3 81.09 3,861,550 749208749 Module: JDBC Thin Client begin productLSPKG.productGETWORK(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18); end; 56.96 7,079 0.01 6.6 643.06 171,390 1314781815 Module: JDBC Thin Client begin productLSPKG.productSEARCH(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13,:14,:15,:16); end; 52.57 7,097 0.01 6.1 103.44 513,302 1395421607 Module: JDBC Thin Client begin productLSPKG.productGETITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28 ,:29,:30,:31,:32,:33); end; 49.83 3,237 0.02 5.8 50.63 3,751,782 984517820 Module: JDBC Thin Client SELECT TMP.COMPONENTID, TMP.RTARGETITEMID, TMP.USERID FROM ( SELECT WRKPKG.COMPONENTID,WRKPKG.PRIORITY,WRKPKG.LASTACCTIME,WRK PKG.RTARGETITEMID,WRKPKG.RTARGETITEMTYPEID,WNORDER.WORKNODEORDER ,C.USERID FROM superuser.productUT00206001 WRKLIST INNER JOIN SVTLS ADM.productUT00207001 WNORDER ON WRKLIST.ITEMID = :x AND WNOR 46.14 25,202 0.00 5.4 46.53 254,855 2486774319 Module: JDBC Thin Client select C.ATTRIBUTEID ,C.ATTRIBUTEGROUP ,C.SEQUENCENUM ,C.ATTRFLA GS ,C.DEFAULTTYPE ,C.DEFAULTVALUE ,A.ATTRSQLTYPE ,A.ATTRLENGTH , A.ATTRSCALE from productSTATTRDEFS A ,productSTCOMPATTRS C where (C.COM PONENTTYPEID=:b0 and C.ATTRIBUTEID=A.ATTRIBUTEID) order by C.SEQ UENCENUM SQL ordered by CPU DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 858 -> Captured SQL accounts for 130.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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 38.95 722 0.05 4.5 48.49 3,088,542 377292000 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adv ertise_1.ITEMID, SVT_Advertise_1.COMPONENTID, SVT_Advertise_1.VE RSIONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPE ID FROM productUT01272001 SVT_Advertise_1, productUT01278001 productParts110 3_5 WHERE ((((SVT_Advertise_1.COMPONENTID = productParts1103_5.PAREN 28.62 1,802 0.02 3.3 33.06 3,067,706 2896381746 Module: JDBC Thin Client begin productLSPKG.productCHECKINITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); end; 27.63 3,722 0.01 3.2 27.81 3,283,080 10598977 Module: JDBC Thin Client select USERID into :b0 from productSTCHECKEDOUT where ITEMID=:b1 25.95 2,760 0.01 3.0 45.14 635,998 2754952449 Module: JDBC Thin Client begin productLSPKG.productITEMROUTE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14,:15,:16,:17,:18,:19); end; 25.77 1,801 0.01 3.0 25.93 3,033,062 3089785859 Module: JDBC Thin Client select USERID from productSTCHECKEDOUT where ITEMID=:b0 for update of USERID 20.28 721 0.03 2.4 30.51 1,461,408 2804088295 Module: JDBC Thin Client begin productLSPKG.productDELETEITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11, :12,:13,:14,:15); end; 20.12 718 0.03 2.3 76.15 474,841 958459823 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adj Report_1.ITEMID, SVT_AdjReport_1.COMPONENTID, SVT_AdjReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01262001 SVT_AdjReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Adjuster_3.ITEMID, SVT_Adjuster_3.COMPONENTID, SVT_Adjust 15.20 2,161 0.01 1.8 27.92 232,620 865340884 Module: JDBC Thin Client begin productLSPKG.productPREPCRTDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10, :11,:12,:13,:14,:15,:16,:17,:18,:19); end; 13.22 360 0.04 1.5 22.64 479,277 2909731019 Module: JDBC Thin Client begin productLSPKG.productUPDATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; SQL ordered by CPU DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 858 -> Captured SQL accounts for 130.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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 13.21 359 0.04 1.5 21.03 798,927 2669649165 Module: JDBC Thin Client begin productLSPKG.productDELETEDOC(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14,:15,:16,:17); end; 12.38 479 0.03 1.4 22.59 292,702 225579285 Module: JDBC Thin Client begin productLSPKG.productUPDATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 12.37 720 0.02 1.4 16.50 310,573 2223094780 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Ins Policy_1.ITEMID, SVT_InsPolicy_1.COMPONENTID, SVT_InsPolicy_1.VE RSIONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPE ID FROM productUT01256001 SVT_InsPolicy_1 WHERE (contains(SVT_InsPol icy_1.TIEREF, :"SYS_B_02", :"SYS_B_03") > :"SYS_B_04") AND (((( 11.99 2,160 0.01 1.4 20.15 122,308 2941834285 Module: JDBC Thin Client begin productLSPKG.productPREPCRTRSITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 10.31 363 0.03 1.2 17.44 433,498 3878041174 Module: JDBC Thin Client begin productLSPKG.productREINDEXITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); end; 9.92 718 0.01 1.2 23.41 237,468 1750001241 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Pol Report_1.ITEMID, SVT_PolReport_1.COMPONENTID, SVT_PolReport_1.VE ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 2,326 -> Captured SQL accounts for 103.2% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 643.06 7,079 0.09 27.6 56.96 35 1314781815 Module: JDBC Thin Client begin productLSPKG.productSEARCH(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13,:14,:15,:16); end; 286.43 121 2.37 12.3 130.93 21,961 3843149556 Module: JDBC Thin Client SELECT WRKPKG.COMPONENTID,WRKPKG.ITEMID,WRKPKG.PRIORITY,WRKPKG.P ROCESSITEMID,WRKPKG.PROCESSLASTCHG,WRKPKG.LASTACCUSERID,WRKPKG.L ASTACCTIME,WRKPKG.NOTIFYFLAG,WRKPKG.NOTIFYTIME,WRKPKG.SUSPENDFLA G,WRKPKG.RESUMETIME,WRKPKG.ASSIGNEDUSERID,WRKPKG.RTARGETITEMID,W RKPKG.RTARGETITEMTYPEID,WRKPKG.RTARGETCOMPID,WRKPKG.RTARGETCOMPT 162.02 4,315 0.04 7.0 82.20 949 1284099840 Module: JDBC Thin Client begin productLSPKG.productCREATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 147.10 2,161 0.07 6.3 77.52 986 3379448481 Module: JDBC Thin Client begin productLSPKG.productCREATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 103.44 7,097 0.01 4.4 52.57 124 1395421607 Module: JDBC Thin Client begin productLSPKG.productGETITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28 ,:29,:30,:31,:32,:33); end; 81.09 3,237 0.03 3.5 62.41 0 749208749 Module: JDBC Thin Client begin productLSPKG.productGETWORK(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18); end; 76.15 718 0.11 3.3 20.12 5,697 958459823 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adj Report_1.ITEMID, SVT_AdjReport_1.COMPONENTID, SVT_AdjReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01262001 SVT_AdjReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Adjuster_3.ITEMID, SVT_Adjuster_3.COMPONENTID, SVT_Adjust 50.63 3,237 0.02 2.2 49.83 0 984517820 Module: JDBC Thin Client SELECT TMP.COMPONENTID, TMP.RTARGETITEMID, TMP.USERID FROM ( SELECT WRKPKG.COMPONENTID,WRKPKG.PRIORITY,WRKPKG.LASTACCTIME,WRK PKG.RTARGETITEMID,WRKPKG.RTARGETITEMTYPEID,WNORDER.WORKNODEORDER ,C.USERID FROM superuser.productUT00206001 WRKLIST INNER JOIN SVTLS ADM.productUT00207001 WNORDER ON WRKLIST.ITEMID = :x AND WNOR SQL ordered by Elapsed DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 2,326 -> Captured SQL accounts for 103.2% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 48.49 722 0.07 2.1 38.95 40 377292000 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adv ertise_1.ITEMID, SVT_Advertise_1.COMPONENTID, SVT_Advertise_1.VE RSIONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPE ID FROM productUT01272001 SVT_Advertise_1, productUT01278001 productParts110 3_5 WHERE ((((SVT_Advertise_1.COMPONENTID = productParts1103_5.PAREN 46.53 25,202 0.00 2.0 46.14 0 2486774319 Module: JDBC Thin Client select C.ATTRIBUTEID ,C.ATTRIBUTEGROUP ,C.SEQUENCENUM ,C.ATTRFLA GS ,C.DEFAULTTYPE ,C.DEFAULTVALUE ,A.ATTRSQLTYPE ,A.ATTRLENGTH , A.ATTRSCALE from productSTATTRDEFS A ,productSTCOMPATTRS C where (C.COM PONENTTYPEID=:b0 and C.ATTRIBUTEID=A.ATTRIBUTEID) order by C.SEQ UENCENUM 45.14 2,760 0.02 1.9 25.95 36 2754952449 Module: JDBC Thin Client begin productLSPKG.productITEMROUTE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14,:15,:16,:17,:18,:19); end; 39.90 719 0.06 1.7 2.13 3,745 266761962 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Cus tomer_1.ITEMID, SVT_Customer_1.COMPONENTID, SVT_Customer_1.VERSI ONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPEID FROM productUT01247001 SVT_Customer_1 WHERE (EXISTS (SELECT DISTINCT SVT_CustJob_3.ITEMID, SVT_CustJob_3.COMPONENTID, SVT_CustJob_3. 33.06 1,802 0.02 1.4 28.62 0 2896381746 Module: JDBC Thin Client begin productLSPKG.productCHECKINITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); end; 30.51 721 0.04 1.3 20.28 0 2804088295 Module: JDBC Thin Client begin productLSPKG.productDELETEITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11, :12,:13,:14,:15); end; 27.92 2,161 0.01 1.2 15.20 241 865340884 Module: JDBC Thin Client begin productLSPKG.productPREPCRTDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10, :11,:12,:13,:14,:15,:16,:17,:18,:19); end; 27.81 3,722 0.01 1.2 27.63 0 10598977 Module: JDBC Thin Client select USERID into :b0 from productSTCHECKEDOUT where ITEMID=:b1 25.93 1,801 0.01 1.1 25.77 0 3089785859 Module: JDBC Thin Client SQL ordered by Elapsed DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 2,326 -> Captured SQL accounts for 103.2% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- select USERID from productSTCHECKEDOUT where ITEMID=:b0 for update of USERID 23.41 718 0.03 1.0 9.92 1,946 1750001241 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Pol Report_1.ITEMID, SVT_PolReport_1.COMPONENTID, SVT_PolReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01252001 SVT_PolReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Victim_3.ITEMID, SVT_Victim_3.COMPONENTID, SVT_Victim_3.V ------------------------------------------------------------- SQL ordered by Gets DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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: 22,528,029 -> Captured SQL accounts for 96.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 3,861,550 3,237 1,192.9 17.1 62.41 81.09 749208749 Module: JDBC Thin Client begin productLSPKG.productGETWORK(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18); end; 3,751,782 3,237 1,159.0 16.7 49.83 50.63 984517820 Module: JDBC Thin Client SELECT TMP.COMPONENTID, TMP.RTARGETITEMID, TMP.USERID FROM ( SELECT WRKPKG.COMPONENTID,WRKPKG.PRIORITY,WRKPKG.LASTACCTIME,WRK PKG.RTARGETITEMID,WRKPKG.RTARGETITEMTYPEID,WNORDER.WORKNODEORDER ,C.USERID FROM superuser.productUT00206001 WRKLIST INNER JOIN SVTLS ADM.productUT00207001 WNORDER ON WRKLIST.ITEMID = :x AND WNOR 3,283,080 3,722 882.1 14.6 27.63 27.81 10598977 Module: JDBC Thin Client select USERID into :b0 from productSTCHECKEDOUT where ITEMID=:b1 3,088,542 722 4,277.8 13.7 38.95 48.49 377292000 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adv ertise_1.ITEMID, SVT_Advertise_1.COMPONENTID, SVT_Advertise_1.VE RSIONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPE ID FROM productUT01272001 SVT_Advertise_1, productUT01278001 productParts110 3_5 WHERE ((((SVT_Advertise_1.COMPONENTID = productParts1103_5.PAREN 3,067,706 1,802 1,702.4 13.6 28.62 33.06 2896381746 Module: JDBC Thin Client begin productLSPKG.productCHECKINITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); end; 3,033,062 1,801 1,684.1 13.5 25.77 25.93 3089785859 Module: JDBC Thin Client select USERID from productSTCHECKEDOUT where ITEMID=:b0 for update of USERID 1,461,408 721 2,026.9 6.5 20.28 30.51 2804088295 Module: JDBC Thin Client begin productLSPKG.productDELETEITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11, :12,:13,:14,:15); end; 999,986 4,315 231.7 4.4 82.20 162.02 1284099840 Module: JDBC Thin Client begin productLSPKG.productCREATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 987,260 2,161 456.9 4.4 77.52 147.10 3379448481 Module: JDBC Thin Client begin productLSPKG.productCREATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; SQL ordered by Gets DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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: 22,528,029 -> Captured SQL accounts for 96.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 829,669 121 6,856.8 3.7 130.93 286.43 3843149556 Module: JDBC Thin Client SELECT WRKPKG.COMPONENTID,WRKPKG.ITEMID,WRKPKG.PRIORITY,WRKPKG.P ROCESSITEMID,WRKPKG.PROCESSLASTCHG,WRKPKG.LASTACCUSERID,WRKPKG.L ASTACCTIME,WRKPKG.NOTIFYFLAG,WRKPKG.NOTIFYTIME,WRKPKG.SUSPENDFLA G,WRKPKG.RESUMETIME,WRKPKG.ASSIGNEDUSERID,WRKPKG.RTARGETITEMID,W RKPKG.RTARGETITEMTYPEID,WRKPKG.RTARGETCOMPID,WRKPKG.RTARGETCOMPT 798,927 359 2,225.4 3.5 13.21 21.03 2669649165 Module: JDBC Thin Client begin productLSPKG.productDELETEDOC(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14,:15,:16,:17); end; 684,677 359 1,907.2 3.0 8.20 8.31 1996458938 Module: JDBC Thin Client SELECT A.COMPONENTID,A.ITEMID,A.VERSIONID,A.ACLCODE,A.SEMANTICTY PE,A.CREATETS,A.CREATEUSERID,A.LASTCHANGEDTS,A.LASTCHANGEDUSERID ,A.WORKNODENAME,A.WORKNODEDESC,A.WORKNODETYPE,A.MAXNODETIME,A.OV ERLOADLIMIT,A.OVERLOADDLLPATH,A.OVERLOADFUNC,A.ENTERDLLPATH,A.EN TERFUNC,A.LEAVEDLLPATH,A.LEAVEFUNC,A.productACTIONLISTCODE,A.productWORK 635,998 2,760 230.4 2.8 25.95 45.14 2754952449 Module: JDBC Thin Client begin productLSPKG.productITEMROUTE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14,:15,:16,:17,:18,:19); end; 513,302 7,097 72.3 2.3 52.57 103.44 1395421607 Module: JDBC Thin Client begin productLSPKG.productGETITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28 ,:29,:30,:31,:32,:33); end; 479,277 360 1,331.3 2.1 13.22 22.64 2909731019 Module: JDBC Thin Client begin productLSPKG.productUPDATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 474,841 718 661.3 2.1 20.12 76.15 958459823 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adj Report_1.ITEMID, SVT_AdjReport_1.COMPONENTID, SVT_AdjReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01262001 SVT_AdjReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Adjuster_3.ITEMID, SVT_Adjuster_3.COMPONENTID, SVT_Adjust 433,498 363 1,194.2 1.9 10.31 17.44 3878041174 Module: JDBC Thin Client begin productLSPKG.productREINDEXITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); end; SQL ordered by Gets DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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: 22,528,029 -> Captured SQL accounts for 96.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 341,671 360 949.1 1.5 4.49 6.53 3092885272 Module: JDBC Thin Client begin productLSPKG.productPREPUPDDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10, :11,:12,:13,:14,:15,:16,:17,:18,:19); end; 334,445 360 929.0 1.5 4.43 6.65 3188172405 Module: JDBC Thin Client begin productLSPKG.productPREPUPDRSITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 310,573 720 431.4 1.4 12.37 16.50 2223094780 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Ins Policy_1.ITEMID, SVT_InsPolicy_1.COMPONENTID, SVT_InsPolicy_1.VE RSIONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPE ID FROM productUT01256001 SVT_InsPolicy_1 WHERE (contains(SVT_InsPol icy_1.TIEREF, :"SYS_B_02", :"SYS_B_03") > :"SYS_B_04") AND (((( 292,702 479 611.1 1.3 12.38 22.59 225579285 Module: JDBC Thin Client begin productLSPKG.productUPDATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 283,106 722 392.1 1.3 5.82 7.39 3898929061 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Cam paign_1.ITEMID, SVT_Campaign_1.COMPONENTID, SVT_Campaign_1.VERSI ONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FR OM productUT01237001 SVT_Campaign_1 WHERE (contains(SVT_Campaign_1.A ------------------------------------------------------------- SQL ordered by Reads DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Disk Reads Threshold: 1000 Total Disk Reads: 42,268 -> Captured SQL accounts for 97.8% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 21,961 121 181.5 52.0 130.93 286.43 3843149556 Module: JDBC Thin Client SELECT WRKPKG.COMPONENTID,WRKPKG.ITEMID,WRKPKG.PRIORITY,WRKPKG.P ROCESSITEMID,WRKPKG.PROCESSLASTCHG,WRKPKG.LASTACCUSERID,WRKPKG.L ASTACCTIME,WRKPKG.NOTIFYFLAG,WRKPKG.NOTIFYTIME,WRKPKG.SUSPENDFLA G,WRKPKG.RESUMETIME,WRKPKG.ASSIGNEDUSERID,WRKPKG.RTARGETITEMID,W RKPKG.RTARGETITEMTYPEID,WRKPKG.RTARGETCOMPID,WRKPKG.RTARGETCOMPT 5,697 718 7.9 13.5 20.12 76.15 958459823 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Adj Report_1.ITEMID, SVT_AdjReport_1.COMPONENTID, SVT_AdjReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01262001 SVT_AdjReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Adjuster_3.ITEMID, SVT_Adjuster_3.COMPONENTID, SVT_Adjust 3,745 719 5.2 8.9 2.13 39.90 266761962 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Cus tomer_1.ITEMID, SVT_Customer_1.COMPONENTID, SVT_Customer_1.VERSI ONID, :"SYS_B_00" AS COMPONENTTYPEID, :"SYS_B_01" AS ITEMTYPEID FROM productUT01247001 SVT_Customer_1 WHERE (EXISTS (SELECT DISTINCT SVT_CustJob_3.ITEMID, SVT_CustJob_3.COMPONENTID, SVT_CustJob_3. 2,413 843 2.9 5.7 1.28 15.15 2724042207 Module: JDBC Thin Client SELECT LINKS.TARGETITEMID OTHERITEMID, ITEMS.ITEMTYPEI D, ITEMS.CHANGED, LINKS.LINKTYPE, LINKS.LINKITEMID, ITEM S.COMPONENTTYPEID, ITEMS.VERSIONID, ITEMS.COMPONENTID FROM SVT LSADM.productSTLINKS001001 LINKS, superuser.productSTITEMS001001 ITEM S, superuser.productSTITEMTYPEDEFS ITEMTYPES WHERE LINKS.SOURCEIT 1,946 718 2.7 4.6 9.92 23.41 1750001241 Module: JDBC Thin Client SELECT /*+ FIRST_ROWS (500) */ T.* FROM (SELECT DISTINCT SVT_Pol Report_1.ITEMID, SVT_PolReport_1.COMPONENTID, SVT_PolReport_1.VE RSIONID, :"SYS_B_0" AS COMPONENTTYPEID, :"SYS_B_1" AS ITEMTYPEID FROM productUT01252001 SVT_PolReport_1 WHERE (EXISTS (SELECT DISTIN CT SVT_Victim_3.ITEMID, SVT_Victim_3.COMPONENTID, SVT_Victim_3.V 986 2,161 0.5 2.3 77.52 147.10 3379448481 Module: JDBC Thin Client begin productLSPKG.productCREATEDOCPART(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19); end; 949 4,315 0.2 2.2 82.20 162.02 1284099840 Module: JDBC Thin Client begin productLSPKG.productCREATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 572 1 572.0 1.4 0.87 1.23 1909642268 Module: sqlplus@win05 (TNS V1-V3) SQL ordered by Reads DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Disk Reads Threshold: 1000 Total Disk Reads: 42,268 -> Captured SQL accounts for 97.8% 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 o.object_name, COUNT(:"SYS_B_0") number_of_blocks F ROM DBA_OBJECTS o, V$BH bh WHERE o.object_id = bh.objd AND o.owner != :"SYS_B_1" GROUP BY o.object_name ORDER BY count(:"SYS_B_2") desc 447 723 0.6 1.1 4.22 9.50 1353836047 Module: JDBC Thin Client begin productLSPKG.productGETDOCTOC(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13,:14); end; ------------------------------------------------------------- SQL ordered by Executions DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Executions Threshold: 100 Total Executions: 947,759 -> Captured SQL accounts for 99.6% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 99,672 99,672 1.0 0.00 0.00 1652415585 Module: JDBC Thin Client select COMPONENTTYPEID into :b0 from productSTCOMPVIEWDEFS where (C OMPONENTVIEWID=:b1 and ROWNUM<:"SYS_B_0") 45,004 45,004 1.0 0.00 0.00 332751908 Module: JDBC Thin Client select ACCESSMODULESTATUS into :b0 from productSTCOMPVIEWDEFS where COMPONENTVIEWID=:b1 41,559 41,559 1.0 0.00 0.00 3551714715 Module: JDBC Thin Client SELECT VIEWACCESSINFO, ACLSUBSELECT, TO_CHAR(LASTCHANGEDTS, :"SY S_B_0") FROM productSTVIEWACCESS WHERE COMPONENTVIEWID=:h1 AND SEGME NTID=:h2 27,726 27,726 1.0 0.00 0.00 1424268339 Module: JDBC Thin Client select PRIVS from productSTCOMPILEDACL where ((ACL=:b0 or ACL=(-:"S YS_B_0")) and UNUM=:b1) 26,406 26,406 1.0 0.00 0.00 2591812284 Module: JDBC Thin Client select count(*) into :b0 from productSTCOMPATTRS where COMPONENTTY PEID=:b1 25,202 401,542 15.9 0.00 0.00 2486774319 Module: JDBC Thin Client select C.ATTRIBUTEID ,C.ATTRIBUTEGROUP ,C.SEQUENCENUM ,C.ATTRFLA GS ,C.DEFAULTTYPE ,C.DEFAULTVALUE ,A.ATTRSQLTYPE ,A.ATTRLENGTH , A.ATTRSCALE from productSTATTRDEFS A ,productSTCOMPATTRS C where (C.COM PONENTTYPEID=:b0 and C.ATTRIBUTEID=A.ATTRIBUTEID) order by C.SEQ UENCENUM 20,407 20,407 1.0 0.00 0.00 544420177 Module: JDBC Thin Client select ITEMTYPECLASS ,ACLCODE ,ITEMLEVELACLFLAG ,VERSIONCONTROL ,VERSIONMAX ,DFLTPREFCHCOLLCODE ,DFLTXDOCLASSID ,AUTOLINKENABLE ,AUTOLINKSMS ,ITEMEVENTFLAG ,PROCESSNAME ,DFLTPRIORITY ,DFLTRETE NTIONUNIT ,DFLTITEMRETENTION ,ITEMTYPEFLAG into :b0,:b1,:b2,:b3, :b4,:b5,:b6,:b7,:b8,:b9,:b10:b11,:b12:b13,:b14,:b15,:b16 from I 19,177 15,261 0.8 0.00 0.00 3472917369 Module: JDBC Thin Client select :"SYS_B_0" into :b0 from productSTCOMPILEDPERM where (UNUM=: b1 and PRIVDEFCODE=:b2) 17,645 17,645 1.0 0.00 0.00 43449643 Module: JDBC Thin Client select TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),:"SYS_B_0") into :b 0 from DUAL SQL ordered by Executions DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Executions Threshold: 100 Total Executions: 947,759 -> Captured SQL accounts for 99.6% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 14,519 14,518 1.0 0.00 0.00 1905731735 Module: JDBC Thin Client select count(distinct SOURCEITEMTYPEID) into :b0 from productSTITEM AUTOLINK where TARGETITEMTYPEID=:b1 14,041 14,041 1.0 0.00 0.00 625920357 Module: JDBC Thin Client select B.ITEMTYPECLASS ,B.ITEMLEVELACLFLAG into :b0,:b1 from IC MSTITEMS001001 A ,productSTITEMTYPEDEFS B where (A.ITEMID=:b2 and A. ITEMTYPEID=B.ITEMTYPEID) 12,859 12,859 1.0 0.00 0.00 1534611058 Module: JDBC Thin Client select RMFLAGS into :b0 from productSTRESOURCEMGR where RMCODE=:b1 12,718 12,718 1.0 0.00 0.00 181728509 Module: JDBC Thin Client select count(SOURCEITEMTYPEID) into :b0 from productSTITEMAUTOLINK where SEQUENCENUM=:"SYS_B_0" 12,484 40,055 3.2 0.00 0.00 3053845722 Module: JDBC Thin Client select COMPONENTTYPEID ,PARENTCOMPTYPEID ,CARDINALITYMIN ,CARDIN ALITYMAX ,COMPTYPECLASS from productSTCOMPDEFS where ITEMTYPEID=:b0 order by COMPONENTTYPEID 11,888 11,888 1.0 0.00 0.00 3908166862 Module: JDBC Thin Client select UPPER(:b0) into :b0 from DUAL 10,475 10,475 1.0 0.00 0.00 2536606458 Module: JDBC Thin Client select :"SYS_B_0" into :b0 from productSTCOMPDEFS where COMPONENTTY PEID=:b1 9,479 9,479 1.0 0.00 0.00 3214843858 Module: JDBC Thin Client insert into productSTITEMS001001 (ITEMID,VERSIONID,COMPONENTID,SEGME NTID,ACLCODE,SEMANTICTYPE,ITEMTYPEID,COMPONENTTYPEID,CHANGED,INP ROGRESS,productMANAGEDRECORD) values (:b0,:b1,:b2,:b3,:b4,:b5,:b6,:b 7,SYS_EXTRACT_UTC(SYSTIMESTAMP),:b8,:"SYS_B_0") 9,478 9,478 1.0 0.00 0.00 3768895660 Module: JDBC Thin Client select ACLCODE into :b0 from productSTITEMS001001 where ITEMID=:b1 ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Parse Calls Threshold: 1000 Total Parse Calls: 292,063 -> Captured SQL accounts for 96.6% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 41,559 41,559 14.23 3551714715 Module: JDBC Thin Client SELECT VIEWACCESSINFO, ACLSUBSELECT, TO_CHAR(LASTCHANGEDTS, :"SY S_B_0") FROM productSTVIEWACCESS WHERE COMPONENTVIEWID=:h1 AND SEGME NTID=:h2 7,097 7,097 2.43 1395421607 Module: JDBC Thin Client begin productLSPKG.productGETITEM(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28 ,:29,:30,:31,:32,:33); end; 7,079 7,079 2.42 1314781815 Module: JDBC Thin Client begin productLSPKG.productSEARCH(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13,:14,:15,:16); end; 4,472 4,472 1.53 4117975755 Module: JDBC Thin Client begin productLSPKG.productCREATETEMPLOBS(:1,:2,:3,:4,:5,:6,:7,:8); end; 4,322 4,322 1.48 801255854 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERE NV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTU P_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance wher e INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME') 4,322 4,322 1.48 3425645771 select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT ', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE ', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failove r_delay, flags from service$ where name = :1 4,322 19,177 1.48 3472917369 Module: JDBC Thin Client select :"SYS_B_0" into :b0 from productSTCOMPILEDPERM where (UNUM=: b1 and PRIVDEFCODE=:b2) 4,322 4,989 1.48 3869134238 Module: JDBC Thin Client select LIBRARYSERVERID ,LANGUAGECODE ,SYSSEGMENTID ,SYSSEGMENTTH RESHLD ,ACLBINDINGLEVEL ,LIBRARYACLCODE ,PUBACCESSENABLED ,DFLTA CLCHOICE ,SMSCHOICE ,TRACELEVEL ,MAXUSERS ,MAXUSERACTION ,CURREN TUSERS ,MAXLOGONRETRY ,PASSWORDDURATION ,SYSADMINEVENTFLAG ,SYST EMFLAG ,SYSTEMFLAG2 ,DATABASETYPE ,MAXTXDURATION ,MAXRESULTSETSI 4,322 4,322 1.48 4166735688 select value$ from props$ where name = 'GLOBAL_DB_NAME' 4,321 7,801 1.48 261344901 Module: JDBC Thin Client SQL ordered by Parse Calls DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Parse Calls Threshold: 1000 Total Parse Calls: 292,063 -> Captured SQL accounts for 96.6% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- select ITEMLEVELACLFLAG into :b0 from productSTITEMTYPEDEFS where I TEMTYPEID=:b1 4,315 4,315 1.48 1284099840 Module: JDBC Thin Client begin productLSPKG.productCREATEITEMS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17,:18,:19); end; 3,608 3,608 1.24 2149605888 Module: JDBC Thin Client BEGIN :p := CTX_QUERY.PREFERENCE; :d := CTX_QUERY.CHK_TXNQRY_DIS BL_SWITCH();END; 3,608 3,608 1.24 3217853381 Module: JDBC Thin Client begin :1 := ctxsys.drvxmd.GetDocidCount(:2,:3);end; 3,237 3,237 1.11 749208749 Module: JDBC Thin Client begin productLSPKG.productGETWORK(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12 ,:13,:14,:15,:16,:17,:18); end; 3,237 3,237 1.11 984517820 Module: JDBC Thin Client SELECT TMP.COMPONENTID, TMP.RTARGETITEMID, TMP.USERID FROM ( SELECT WRKPKG.COMPONENTID,WRKPKG.PRIORITY,WRKPKG.LASTACCTIME,WRK PKG.RTARGETITEMID,WRKPKG.RTARGETITEMTYPEID,WNORDER.WORKNODEORDER ,C.USERID FROM superuser.productUT00206001 WRKLIST INNER JOIN SVTLS ADM.productUT00207001 WNORDER ON WRKLIST.ITEMID = :x AND WNOR 3,237 3,237 1.11 1020229657 Module: JDBC Thin Client SELECT A.COMPCLUSTERID, A.COMPONENTID,A.ITEMID,A.VERSIONID,A.PAR ENTCOMPID,A.COMPKEY,A.PARENTCOMPKEY,A.REQUIREDITID,A.QUANTITYNEE DED,B.KEYWORDNAME FROM productUT00205001 A, productSTNLSKEYWORDS B WHERE A.PARENTCOMPID = :"SYS_B_0" AND A.ITEMID = :"SYS_B_1" AND A.VER SIONID = :"SYS_B_2" AND A.REQUIREDITID = B.KEYWORDCODE AND B.KEY 3,237 3,237 1.11 1920148968 Module: JDBC Thin Client SELECT WP.COMPONENTID,WP.ITEMID,WP.PRIORITY,WP.PROCESSITEMID,WP. PROCESSLASTCHG,WP.LASTACCUSERID,WP.LASTACCTIME,WP.NOTIFYFLAG,WP. NOTIFYTIME,WP.SUSPENDFLAG,WP.RESUMETIME,WP.ASSIGNEDUSERID,WP.RTA RGETITEMID,WP.RTARGETITEMTYPEID,WP.RTARGETCOMPID,WP.RTARGETCOMPT YPEID,WP.RTARGETVERSIONID, PROC.PROCESSNAME, WN.WORKNODENAME,WP. 3,237 3,237 1.11 3986387230 Module: JDBC Thin Client SELECT CONTAINERDATANAME, CONTAINERDATAVAL FROM productUT00208001 WH ERE PARENTCOMPID = :"SYS_B_0" AND VERSIONID = :"SYS_B_1" SQL ordered by Parse Calls DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Parse Calls Threshold: 1000 Total Parse Calls: 292,063 -> Captured SQL accounts for 96.6% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Old Hash Value ---------------- ------------ ------- -------------- 2,158,386 359 0.6 2599824671 Module: JDBC Thin Client SELECT CHILD.COMPONENTID,CHILD.ITEMID,CHILD.VERSIONID,CHILD.PARE NTCOMPID,CHILD.FOLDERITID,CHILD.REQUIREDITID,CHILD.QUANTITYNEEDE D FROM superuser.productUT00203001 CHILD, productUT00202001 ROOT WHERE ( (CHILD.VERSIONID=:"SYS_B_000" AND CHILD.ITEMID IN (:"SYS_B_001", :"SYS_B_002",:"SYS_B_003",:"SYS_B_004",:"SYS_B_005",:"SYS_B_006" 1,263,062 119 0.3 3734219348 Module: JDBC Thin Client SELECT A.COMPONENTID,A.ITEMID,A.VERSIONID,A.ACLCODE,A.SEMANTICTY PE,A.CREATETS,A.CREATEUSERID,A.LASTCHANGEDTS,A.LASTCHANGEDUSERID ,A.WORKLISTNAME,A.WORKLISTDESC,A.SELECTIONORDER,A.SUSPENDEDFILTE R,A.NOTIFIEDFILTER,A.RETURNSIZE,A.ASSIGNEDFILTER,A.productMANAGEDREC ORD ,B.USERID, B.CREATED FROM superuser.productUT00206001 A LEFT OUT ------------------------------------------------------------- Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 78,710 36.6 3.5 CPU used when call started 78,619 36.6 3.5 CR blocks created 6,429 3.0 0.3 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DB time 258,533 120.3 11.5 DBWR checkpoint buffers written 25,580 11.9 1.1 DBWR checkpoints 2 0.0 0.0 DBWR transaction table writes 91 0.0 0.0 DBWR undo block writes 10,047 4.7 0.5 SMON posted for undo segment shri 3 0.0 0.0 SQL*Net roundtrips to/from client 194,292 90.4 8.7 SQL*Net roundtrips to/from dblink 56,557 26.3 2.5 active txn count during cleanout 8,759 4.1 0.4 application wait time 1 0.0 0.0 background checkpoints completed 2 0.0 0.0 background checkpoints started 2 0.0 0.0 background timeouts 6,868 3.2 0.3 branch node splits 7 0.0 0.0 buffer is not pinned count 5,079,172 2,362.4 226.7 buffer is pinned count 24,512,964 11,401.4 1,093.9 bytes received via SQL*Net from c 78,715,983 36,612.1 3,512.9 bytes received via SQL*Net from d 42,006,088 19,537.7 1,874.6 bytes sent via SQL*Net to client 197,842,257 92,019.7 8,829.1 bytes sent via SQL*Net to dblink 161,640,677 75,181.7 7,213.5 calls to get snapshot scn: kcmgss 1,840,655 856.1 82.1 calls to kcmgas 38,107 17.7 1.7 calls to kcmgcs 3,507 1.6 0.2 change write time 1,349 0.6 0.1 cleanout - number of ktugct calls 9,075 4.2 0.4 cleanouts and rollbacks - consist 3,788 1.8 0.2 cleanouts only - consistent read 357 0.2 0.0 cluster key scan block gets 4,785 2.2 0.2 cluster key scans 2,654 1.2 0.1 commit batch/immediate performed 49 0.0 0.0 commit batch/immediate requested 49 0.0 0.0 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 6 0.0 0.0 commit cleanout failures: callbac 221 0.1 0.0 commit cleanout failures: cannot 52 0.0 0.0 commit cleanouts 348,707 162.2 15.6 commit cleanouts successfully com 348,428 162.1 15.6 commit immediate performed 49 0.0 0.0 commit immediate requested 49 0.0 0.0 commit txn count during cleanout 7,969 3.7 0.4 concurrency wait time 197 0.1 0.0 consistent changes 83,347 38.8 3.7 consistent gets 20,796,415 9,672.8 928.1 consistent gets - examination 3,388,369 1,576.0 151.2 consistent gets direct 360 0.2 0.0 consistent gets from cache 20,796,055 9,672.6 928.1 cursor authentications 107 0.1 0.0 data blocks consistent reads - un 7,832 3.6 0.4 db block changes 1,160,538 539.8 51.8 db block gets 1,731,614 805.4 77.3 db block gets direct 0 0.0 0.0 Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ db block gets from cache 1,731,614 805.4 77.3 deferred (CURRENT) block cleanout 50,854 23.7 2.3 dirty buffers inspected 0 0.0 0.0 enqueue conversions 15,936 7.4 0.7 enqueue releases 188,915 87.9 8.4 enqueue requests 188,915 87.9 8.4 enqueue waits 56 0.0 0.0 exchange deadlocks 2 0.0 0.0 execute count 947,759 440.8 42.3 failed probes on index block recl 0 0.0 0.0 free buffer inspected 34,671 16.1 1.6 free buffer requested 67,222 31.3 3.0 heap block compress 1,541 0.7 0.1 hot buffers moved to head of LRU 41,608 19.4 1.9 immediate (CR) block cleanout app 4,145 1.9 0.2 immediate (CURRENT) block cleanou 52,826 24.6 2.4 index crx upgrade (found) 29 0.0 0.0 index crx upgrade (positioned) 647,377 301.1 28.9 index fast full scans (full) 13,476 6.3 0.6 index fetch by key 1,001,985 466.0 44.7 index scans kdiixs1 1,563,671 727.3 69.8 leaf node 90-10 splits 903 0.4 0.0 leaf node splits 1,462 0.7 0.1 lob reads 179,514 83.5 8.0 lob writes 16,178 7.5 0.7 lob writes unaligned 16,178 7.5 0.7 logons cumulative 4,334 2.0 0.2 messages received 35,373 16.5 1.6 messages sent 35,373 16.5 1.6 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 15,278,859 7,106.5 681.9 opened cursors cumulative 247,112 114.9 11.0 parse count (failures) 0 0.0 0.0 parse count (hard) 173 0.1 0.0 parse count (total) 292,063 135.8 13.0 parse time cpu 2,385 1.1 0.1 parse time elapsed 2,450 1.1 0.1 physical read IO requests 27,899 13.0 1.3 physical read bytes 346,259,456 161,050.9 15,452.5 physical read total IO requests 28,642 13.3 1.3 physical read total bytes 352,315,392 163,867.6 15,722.8 physical read total multi block r 2,383 1.1 0.1 physical reads 42,268 19.7 1.9 physical reads cache 19,008 8.8 0.9 physical reads cache prefetch 0 0.0 0.0 physical reads direct 23,260 10.8 1.0 physical reads direct (lob) 360 0.2 0.0 physical reads direct temporary t 22,880 10.6 1.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 30,635 14.3 1.4 physical write bytes 3,390,955,520 1,577,188.6 151,327.9 physical write total IO requests 55,406 25.8 2.5 physical write total bytes 3,620,331,008 1,683,874.9 161,564.2 physical write total multi block 42,108 19.6 1.9 physical writes 413,935 192.5 18.5 physical writes direct 388,355 180.6 17.3 Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical writes direct temporary 388,335 180.6 17.3 physical writes from cache 25,580 11.9 1.1 physical writes non checkpoint 401,473 186.7 17.9 pinned buffers inspected 2 0.0 0.0 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 6,846 3.2 0.3 process last non-idle time 1,521 0.7 0.1 recursive calls 1,934,400 899.7 86.3 recursive cpu usage 39,972 18.6 1.8 redo blocks written 375,739 174.8 16.8 redo buffer allocation retries 4 0.0 0.0 redo entries 584,331 271.8 26.1 redo log space requests 4 0.0 0.0 redo log space wait time 32 0.0 0.0 redo ordering marks 0 0.0 0.0 redo size 180,813,340 84,099.2 8,069.1 redo synch time 24,671 11.5 1.1 redo synch writes 38,125 17.7 1.7 redo wastage 5,598,208 2,603.8 249.8 redo write time 22,156 10.3 1.0 redo writer latching time 3 0.0 0.0 redo writes 22,808 10.6 1.0 rollback changes - undo records a 300 0.1 0.0 rollbacks only - consistent read 2,649 1.2 0.1 rows fetched via callback 882,033 410.3 39.4 session connect time 0 0.0 0.0 session cursor cache hits 206,359 96.0 9.2 session logical reads 22,528,029 10,478.2 1,005.4 session pga memory 295,225,024 137,314.0 13,175.0 session pga memory max 49,727,168 23,128.9 2,219.2 session uga memory 407,339,520 189,460.2 18,178.3 session uga memory max 5,188,196,104 2,413,114.5 231,533.2 shared hash latch upgrades - no w 698,732 325.0 31.2 shared hash latch upgrades - wait 36 0.0 0.0 sorts (disk) 104 0.1 0.0 sorts (memory) 82,783 38.5 3.7 sorts (rows) 11,377,695 5,292.0 507.8 sql area evicted 0 0.0 0.0 sql area purged 0 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 6,961 3.2 0.3 table fetch by rowid 9,622,287 4,475.5 429.4 table fetch continued row 2,022 0.9 0.1 table scan blocks gotten 1,328,050 617.7 59.3 table scan rows gotten 70,268,352 32,683.0 3,135.9 table scans (long tables) 0 0.0 0.0 table scans (short tables) 63,658 29.6 2.8 total number of times SMON posted 4,448 2.1 0.2 transaction rollbacks 49 0.0 0.0 undo change vector size 70,274,524 32,685.8 3,136.1 user I/O wait time 16,762 7.8 0.8 user calls 265,952 123.7 11.9 user commits 22,408 10.4 1.0 workarea executions - multipass 5 0.0 0.0 workarea executions - onepass 101 0.1 0.0 workarea executions - optimal 92,528 43.0 4.1 Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ write clones created in foregroun 10 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 29 27 opened cursors current 9,015 15,568 session cursor cache count 55,352 108,202 workarea memory allocated 3,227 1,997 ------------------------------------------------------------- Instance Activity Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 2 3.35 ------------------------------------------------------------- OS Statistics DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 841,252 IDLE_TIME 16,405 IOWAIT_TIME 3,420 NICE_TIME 265 SYS_TIME 584,621 USER_TIME 254,670 LOAD 9 PHYSICAL_MEMORY_BYTES 74,624 NUM_CPUS 4 NUM_CPU_SOCKETS 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SVTLFQ32 114,994 53 10.1 3.9 13,774 6 43 1.9 TEMP 8,514 4 0.0 2.7 18,086 8 0 0.0 SVTLSNDX 20,133 9 10.2 7.5 6,122 3 284 1.8 SYSTEM 6,631 3 5.8 1.4 365 0 0 0.0 SYSAUX 2,933 1 10.3 2.2 457 0 0 0.0 SVTVFQ04 2,762 1 3.9 3.7 284 0 3 0.0 SVTSFQ04 1,808 1 17.7 16.6 773 0 7 0.0 UNDOTBS1 50 0 1.2 1.0 1,759 1 219 4.6 SVTLSTXT 656 0 12.8 10.9 469 0 0 0.0 STATSPACK 592 0 15.4 3.3 166 0 0 0.0 SVTLNF32 193 0 3.6 3.0 209 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ STATSPACK /u03/svtlsdb/statspack_tbs.dbf 592 0 15.4 ### 3.3 166 0 0 SVTLFQ32 /u03/svtlsdb/svtlsdb_SVTLFQ32_01.dbf 114,994 53 10.1 ### 3.9 13,774 6 43 1.9 SVTLNF32 /u03/svtlsdb/svtlsdb_SVTLNF32_01.dbf 193 0 3.6 16 3.0 209 0 0 SVTLSNDX /u03/svtlsdb/svtlsdb_SVTLSNDX_01.dbf 20,133 9 10.2 64 7.5 6,122 3 284 1.8 SVTLSTXT /u03/svtlsdb/svtlsdb_SVTLSTXT_01.dbf 656 0 12.8 64 10.9 469 0 0 SVTSFQ04 /u03/svtlsdb/svtlsdb_SVTSFQ04_01.dbf 1,808 1 17.7 32 16.6 773 0 7 0.0 SVTVFQ04 /u03/svtlsdb/svtlsdb_SVTVFQ04_01.dbf 2,762 1 3.9 3.7 284 0 3 0.0 SYSAUX /u03/svtlsdb/svtlsdb_SYSAUX_01.dbf 2,933 1 10.3 16 2.2 457 0 0 SYSTEM /u03/svtlsdb/svtlsdb_SYSTEM_01.dbf 6,631 3 5.8 ### 1.4 365 0 0 TEMP /u03/svtlsdb/svtlsdb_TEMP_01.dbf 8,514 4 0.0 16 2.7 18,086 8 0 UNDOTBS1 /u03/svtlsdb/svtlsdb_UNDOTBS1_01.dbf 50 0 1.2 1.0 1,759 1 219 4.6 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->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 ------------ ------------ ------------ ------------ ------------ ------------ STATSPACK /u03/svtlsdb/statspack_tbs.dbf 47 8 18 6 8 15 SVTLFQ32 /u03/svtlsdb/svtlsdb_SVTLFQ32_01.dbf 1,659 3,449 8,349 3,051 670 454 SVTLNF32 /u03/svtlsdb/svtlsdb_SVTLNF32_01.dbf 6 2 7 6 0 0 SVTLSNDX /u03/svtlsdb/svtlsdb_SVTLSNDX_01.dbf 63 91 432 368 33 5 SVTLSTXT /u03/svtlsdb/svtlsdb_SVTLSTXT_01.dbf 5 8 9 13 0 1 SVTSFQ04 /u03/svtlsdb/svtlsdb_SVTSFQ04_01.dbf 27 18 99 40 7 0 SYSAUX /u03/svtlsdb/svtlsdb_SYSAUX_01.dbf 1 1 1 1 0 0 SYSTEM /u03/svtlsdb/svtlsdb_SYSTEM_01.dbf 26 5 20 15 2 7 TEMP /u03/svtlsdb/svtlsdb_TEMP_01.dbf 2 0 0 1 0 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 146K 100 22,531,221 19,050 25,580 0 0 145 K 37K 0 0 0 0 0 0 R 13K 100 2 0 0 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 25 3685 54884 331740 331740 339452 E 0 25 3620 54026 315827 331740 315827 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: SVTLSDB/svtlsdb End Snap: 19 -> 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 112 .1 14 17.7 11,829 86,160 ###### D 224 .2 28 8.0 5,337 36,914 589.9 D 336 .3 42 4.0 2,680 16,757 267.8 D 448 .4 55 2.6 1,719 9,469 151.3 D 560 .5 69 1.9 1,260 5,989 95.7 D 672 .6 83 1.5 1,018 4,148 66.3 D 784 .7 97 1.3 856 2,920 46.7 D 896 .8 111 1.1 763 2,216 35.4 D 1,008 .9 125 1.1 715 1,853 29.6 D 1,120 .9 139 1.0 690 1,661 26.5 D 1,184 1.0 146 1.0 668 1,494 23.9 D 1,232 1.0 152 1.0 659 1,427 22.8 D 1,344 1.1 166 1.0 636 1,252 20.0 D 1,456 1.2 180 0.9 614 1,085 17.3 D 1,568 1.3 194 0.9 604 1,009 16.1 D 1,680 1.4 208 0.9 602 992 15.9 D 1,792 1.5 222 0.9 602 992 15.9 D 1,904 1.6 236 0.9 602 992 15.9 D 2,016 1.7 249 0.9 602 992 15.9 D 2,128 1.8 263 0.9 602 992 15.9 D 2,240 1.9 277 0.9 602 992 15.9 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- undo header 74 1 8 data block 40 0 1 undo block 31 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 --------------- ---------------- ------------------------- 74.9 11,434 3,839 Warning: pga_aggregate_target was set too low for current workload, as this value was exceeded during this interval. Use the PGA Advisory view to help identify a different value for pga_aggregate_target. %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 500 101 455.1 3.2 .7 100.0 .0 102,400 E 500 31 783.4 2.0 .2 100.0 .0 3,128 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 83,212 83,212 0 0 64K 128K 43 43 0 0 128K 256K 1 1 0 0 256K 512K 1 1 0 0 512K 1024K 8,973 8,973 0 0 1M 2M 128 128 0 0 2M 4M 4 4 0 0 4M 8M 2 0 2 0 32M 64M 121 17 99 5 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: SVTLSDB/svtlsdb End Snap: 19 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 63 0.1 22,671.1 16,363.8 58.0 307 125 0.3 22,671.1 16,055.7 59.0 290 250 0.5 22,671.1 14,356.3 61.0 240 375 0.8 22,671.1 12,329.2 65.0 148 500 1.0 22,671.1 3,241.8 87.0 89 600 1.2 22,671.1 1,512.9 94.0 34 700 1.4 22,671.1 96.5 100.0 0 800 1.6 22,671.1 0.0 100.0 0 900 1.8 22,671.1 0.0 100.0 0 1,000 2.0 22,671.1 0.0 100.0 0 1,500 3.0 22,671.1 0.0 100.0 0 2,000 4.0 22,671.1 0.0 100.0 0 3,000 6.0 22,671.1 0.0 100.0 0 4,000 8.0 22,671.1 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 Avg Alloc Used Freeabl Alloc Category (MB) (MB) (MB) (MB) -------------------------------- -------- --------- --------- -------- -------- Hist Num Std Dev Max Max Procs Alloc Alloc Alloc or (MB) (MB) (MB) Allocs ------- ------- ------ ------ B -------- 457.0 394.0 25.8 15.2 19.0 54 84 30 Other 329.7 11.0 13.5 39 39 30 SQL 98.7 76.5 4.1 4.8 14 47 22 Freeable 27.1 .0 1.4 .9 3 19 PL/SQL 2.9 .5 .1 .1 0 0 30 E -------- 783.4 673.4 70.6 29.0 34.4 86 89 27 Other 538.6 19.9 23.3 58 58 27 SQL 171.3 135.7 8.2 8.6 21 48 19 Freeable 70.6 .0 3.7 2.9 7 19 PL/SQL 2.8 .4 .1 .1 0 0 27 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- B 16 ------------ 54.5 50.7 1.3 54.5 84.3 Other 39.0 39.0 39.0 SQL 13.9 9.6 13.9 46.9 Freeable 1.3 .0 1.3 PL/SQL .3 .0 .3 .3 29 ------------ 45.6 37.0 2.7 45.6 75.1 Other 34.1 34.1 34.1 SQL 8.6 6.8 8.6 41.5 Freeable 2.7 .0 2.7 PL/SQL .3 .0 .3 .3 19 ------------ 40.6 36.5 2.6 40.6 70.1 Other 28.1 28.1 28.1 SQL 9.6 7.5 9.6 42.4 Freeable 2.6 .0 2.6 PL/SQL .3 .0 .3 .3 17 ------------ 39.2 35.2 2.6 39.2 68.9 Other 27.1 27.1 27.1 SQL 9.2 7.3 9.2 42.1 Freeable 2.6 .0 2.6 PL/SQL .3 .0 .3 .3 21 ------------ 38.4 35.3 1.6 38.4 69.4 Other 27.3 27.3 27.3 SQL 9.2 7.3 9.2 42.2 Freeable 1.6 .0 1.6 PL/SQL .3 .0 .3 .3 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- B 22 ------------ 38.1 34.8 1.8 38.1 69.1 Other 26.9 26.9 26.9 SQL 9.2 7.2 9.2 42.1 Freeable 1.8 .0 1.8 PL/SQL .3 .0 .3 .3 23 ------------ 37.9 34.2 2.3 37.9 67.0 Other 26.4 26.4 26.4 SQL 9.0 7.1 9.0 41.6 Freeable 2.3 .0 2.3 PL/SQL .3 .0 .3 .3 25 ------------ 37.7 33.9 2.3 37.7 66.7 Other 26.2 26.2 26.2 SQL 8.9 7.0 8.9 41.5 Freeable 2.3 .0 2.3 PL/SQL .3 .0 .3 .3 24 ------------ 37.6 35.2 .9 37.6 66.7 Other 26.1 26.1 26.1 SQL 8.9 7.1 8.9 41.6 Freeable 2.3 .0 2.3 PL/SQL .3 .0 .3 .3 28 ------------ 37.4 33.5 2.5 37.4 66.8 Other 25.7 25.7 25.7 SQL 9.0 7.2 9.0 41.5 Freeable 2.4 .0 2.4 PL/SQL .3 .1 .3 .3 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- B 6 LGWR -------- 22.0 10.3 .1 22.0 22.5 Other 21.9 21.9 22.5 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 13 TNS V1-V3 --- 7.1 5.5 .3 7.1 7.6 Other 6.2 6.2 6.2 SQL .7 .4 .7 5.6 Freeable .2 .0 .2 PL/SQL .0 .0 .0 .0 11 MMON -------- 3.3 1.8 1.1 3.3 3.4 Other 2.0 2.0 2.0 Freeable 1.1 .0 1.1 SQL .2 .1 .2 1.1 PL/SQL .0 .0 .0 .0 18 ------------ 3.2 1.9 .9 3.2 4.1 Other 1.4 1.4 1.4 Freeable .9 .0 .9 SQL .8 .7 .8 2.2 PL/SQL .1 .0 .1 .1 26 ------------ 2.7 1.4 1.0 2.7 3.4 Other 1.0 1.0 1.0 Freeable 1.0 .0 1.0 SQL .6 .6 .6 2.2 PL/SQL .1 .0 .1 .1 8 SMON -------- 2.4 1.1 1.1 2.4 2.4 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- B 8 Other 1.1 1.1 1.1 Freeable 1.1 .0 1.1 SQL .2 .1 .2 .3 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.3 1.0 .1 1.3 1.9 Other 1.1 1.1 1.3 SQL .1 .1 .1 .5 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 27 ------------ 1.2 .8 .0 1.2 1.2 Other .9 .9 .9 SQL .2 .2 .2 .2 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.1 .5 .3 1.1 2.6 Other .8 .8 2.2 Freeable .3 .0 .3 SQL .0 .0 .0 .1 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.0 .4 .4 1.0 2.5 Other .7 .7 2.2 Freeable .4 .0 .4 PL/SQL .0 .0 .0 .0 E 16 ------------ 85.7 77.8 6.6 85.7 88.9 Other 57.6 57.6 57.6 SQL 21.2 15.5 21.2 48.1 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- E 16 Freeable 6.6 .0 6.6 PL/SQL .3 .0 .3 .3 29 ------------ 75.9 64.3 5.4 75.9 79.6 Other 54.4 54.4 54.4 SQL 15.9 12.8 15.9 42.7 Freeable 5.4 .0 5.4 PL/SQL .3 .0 .3 .3 22 ------------ 74.0 66.2 7.3 74.0 75.7 Other 50.1 50.1 50.1 SQL 16.4 13.1 16.4 43.4 Freeable 7.3 .0 7.3 PL/SQL .3 .0 .3 .3 19 ------------ 71.9 64.0 6.4 71.9 74.7 Other 48.2 48.2 48.2 SQL 17.0 13.5 17.0 43.7 Freeable 6.4 .0 6.4 PL/SQL .3 .0 .3 .3 21 ------------ 70.8 62.3 6.7 70.8 73.9 Other 47.4 47.4 47.4 SQL 16.5 13.2 16.5 43.4 Freeable 6.7 .0 6.7 PL/SQL .3 .0 .3 .3 17 ------------ 70.7 62.7 6.4 70.7 73.4 Other 47.4 47.4 47.4 SQL 16.6 13.3 16.6 43.3 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- E 17 Freeable 6.4 .0 6.4 PL/SQL .3 .0 .3 .3 24 ------------ 70.4 61.5 7.4 70.4 70.4 Other 46.4 46.4 46.4 SQL 16.4 13.1 16.4 42.2 Freeable 7.4 .0 7.4 PL/SQL .3 .0 .3 .3 23 ------------ 68.6 61.5 5.6 68.6 69.2 Other 46.4 46.4 46.4 SQL 16.3 13.0 16.3 42.3 Freeable 5.6 .0 5.6 PL/SQL .3 .0 .3 .3 28 ------------ 68.3 60.5 6.3 68.3 71.2 Other 45.7 45.7 45.7 SQL 16.1 12.9 16.1 42.7 Freeable 6.3 .0 6.3 PL/SQL .3 .0 .3 .3 25 ------------ 68.2 61.1 5.5 68.2 69.0 Other 46.2 46.2 46.2 SQL 16.2 13.0 16.2 42.1 Freeable 5.5 .0 5.5 PL/SQL .3 .0 .3 .3 6 LGWR -------- 22.0 10.3 .1 22.0 22.5 Other 21.9 21.9 22.5 Freeable .1 .0 .1 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- E 6 PL/SQL .0 .0 .0 .0 13 TNS V1-V3 --- 17.6 10.9 1.4 17.6 22.2 Other 15.3 15.3 15.3 Freeable 1.4 .0 1.4 SQL .9 .5 .9 7.2 PL/SQL .0 .0 .0 .0 11 MMON -------- 3.2 1.8 .9 3.2 3.4 Other 2.0 2.0 2.0 Freeable .9 .0 .9 SQL .2 .1 .2 1.1 PL/SQL .0 .0 .0 .0 26 ------------ 2.7 1.4 1.0 2.7 3.4 Other 1.0 1.0 1.0 Freeable 1.0 .0 1.0 SQL .6 .6 .6 2.2 PL/SQL .1 .0 .1 .1 30 ------------ 2.7 1.4 1.0 2.7 3.0 Other 1.0 1.0 1.0 Freeable 1.0 .0 1.0 SQL .6 .6 .6 1.8 PL/SQL .1 .0 .1 .1 8 SMON -------- 2.4 1.1 1.1 2.4 2.4 Freeable 1.1 .0 1.1 Other 1.0 1.0 1.0 SQL .2 .1 .2 .3 Top Process Memory (by component) DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl PId Category (MB) (MB) (MB) -------------------------------- ------ ------------- ------- ------- -------- Max Hist Max Alloc (MB) Alloc (MB) ---------- ---------- E 8 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.7 .5 .9 1.7 2.6 Freeable .9 .0 .9 Other .8 .8 1.7 SQL .0 .0 .0 .1 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.6 1.0 .4 1.6 1.9 Other 1.1 1.1 1.1 Freeable .4 .0 .4 SQL .1 .1 .1 .5 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.1 .4 .3 1.1 2.5 Other .8 .8 2.2 Freeable .3 .0 .3 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 (index contention) 55 55 0 55 0 7.27 TX-Transaction (row lock contention) 1 1 0 1 0 20.00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 11.2 27,745 1,809 5 44/44.2 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 09-Sep 22:55 2,740 6,857 1,802 5 44 0/0 0/0/0/0/0/0 09-Sep 22:45 2,757 6,822 1,803 5 44 0/0 0/0/0/0/0/0 09-Sep 22:35 2,847 7,069 1,809 5 44 0/0 0/0/0/0/0/0 09-Sep 22:25 2,863 6,997 1,806 5 44 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->"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 1,427 0.0 0 0 Consistent RBA 22,810 0.0 0.0 0 0 FIB s.o chain latch 16 0.0 0 0 FOB s.o list latch 10,710 0.0 0 0 JS mem alloc latch 8 0.0 0 0 JS queue access latch 8 0.0 0 0 JS queue state obj latch 12,870 0.0 0 0 KMG MMAN ready and start 716 0.0 0 0 KTF sga latch 7 0.0 0 717 0.0 KWQP Prop Status 1 0.0 0 0 MQL Tracking Latch 0 0 44 0.0 Memory Management Latch 0 0 716 0.0 OS process 13,002 0.0 0 0 OS process allocation 9,384 0.0 0 0 OS process: request allo 8,668 0.0 0 0 PL/SQL warning settings 66,094 0.0 0 0 SQL memory manager latch 1 0.0 0 711 0.0 SQL memory manager worka 151,796 0.0 0.0 0 0 Shared B-Tree 80 0.0 0 0 active checkpoint queue 13,251 0.0 0 0 active service list 29,874 0.0 0.3 0 1,277 0.0 cache buffer handles 6,466 0.0 0 0 cache buffers chains 45,319,459 0.0 0.0 0 62,719 0.0 cache buffers lru chain 98,886 0.0 0.0 0 74,699 0.0 channel handle pool latc 17,554 0.0 0.0 0 0 channel operations paren 67,464 0.0 0.0 0 0 checkpoint queue latch 359,340 0.0 0.0 0 25,253 0.0 client/application info 21,670 0.0 0 0 compile environment latc 67,971 0.0 0 0 dml lock allocation 245,670 0.0 0.0 0 0 dummy allocation 8,668 0.0 1.0 0 0 enqueue hash chains 393,761 0.0 0.1 0 81 0.0 enqueues 133,387 0.0 0.5 0 0 event group latch 4,334 0.0 0 0 file cache latch 113 0.0 0 0 fixed table rows for x$h 8,668 0.0 0.0 0 0 global KZLD latch for me 4,334 0.0 0 0 hash table column usage 252 0.0 0 79,018 0.0 hash table modification 41 0.0 0 0 job_queue_processes para 36 0.0 0 0 kks stats 232 0.0 0 0 ksuosstats global area 145 0.0 0 0 ktm global data 4,453 0.0 0 0 kwqbsn:qsga 80 0.0 0 0 lgwr LWN SCN 22,812 0.0 0.0 0 0 library cache 1,357,834 0.0 0.1 0 0 library cache load lock 644 0.0 0 0 library cache lock 494,945 0.0 0.1 0 0 library cache lock alloc 41,526 0.0 0 0 library cache pin 589,338 0.0 0.2 0 0 Latch Activity DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ library cache pin alloca 5,871 0.0 0 0 list of block allocation 2,103 0.0 0 0 loader state object free 744 0.0 0 0 message pool operations 8,888 0.0 0.0 0 0 messages 113,383 0.0 0.0 0 0 mostly latch-free SCN 22,826 0.1 0.0 0 0 multiblock read objects 52 0.0 0 0 ncodef allocation latch 60 0.0 0 0 object queue header heap 88,237 0.0 0 8,741 0.0 object queue header oper 366,573 0.0 0.0 0 0 object stats modificatio 16 0.0 0 0 parallel query alloc buf 284 0.0 0 0 parameter list 39,723 0.0 0.1 0 0 parameter table allocati 4,334 0.0 0 0 post/wait queue 57,671 0.0 0 23,008 0.0 process allocation 8,668 0.0 1.0 0 4,334 0.0 process group creation 8,668 0.0 0 0 qmn task queue latch 308 0.0 0 0 redo allocation 629,751 0.0 0.0 0 0 redo copy 0 0 584,120 0.0 redo writing 83,192 0.0 0.3 0 0 resmgr group change latc 4,334 0.0 0 0 resmgr:actses active lis 8,669 0.0 0 0 resmgr:actses change gro 4,334 0.0 0 0 resmgr:free threads list 8,668 0.0 0 0 resmgr:schema config 1 0.0 0 0 row cache objects 1,046,633 0.0 0.0 1 0 rules engine rule set st 100 0.0 0 0 sequence cache 13,719 0.0 0 0 session allocation 276,156 0.0 0.2 0 0 session idle bit 595,513 0.0 0.3 0 0 session state list latch 26,004 0.0 0 0 session switching 60 0.0 0 0 session timer 1,277 0.0 0 0 shared pool 155,303 0.0 0.3 0 0 shared pool sim alloc 23 0.0 0 0 shared pool simulator 20,963 0.0 0 0 simulator hash latch 1,739,847 0.0 0.3 0 0 simulator lru latch 1,345,935 0.1 0.0 0 391,294 0.0 sort extent pool 34,124 0.0 0.2 0 0 temp lob duration state 8,668 0.0 0 0 threshold alerts latch 138 0.0 0 0 transaction allocation 68 0.0 0 0 transaction branch alloc 60 0.0 0 0 undo global data 103,138 0.0 0.0 0 0 user lock 17,336 0.0 1.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 45,319,459 8,309 22 8,287 simulator lru latch 1,345,935 1,129 15 1,114 row cache objects 1,046,633 326 7 319 library cache 1,357,834 325 34 291 redo allocation 629,751 237 1 236 session allocation 276,156 105 26 79 shared pool 155,303 38 12 26 library cache pin 589,338 37 7 31 library cache lock 494,945 32 4 28 enqueue hash chains 393,761 23 3 20 enqueues 133,387 20 9 11 session idle bit 595,513 12 4 8 simulator hash latch 1,739,847 10 3 7 parameter list 39,723 7 1 6 sort extent pool 34,124 5 1 4 active service list 29,874 4 1 3 redo writing 83,192 3 1 2 dummy allocation 8,668 2 2 0 process allocation 8,668 1 1 0 user lock 17,336 1 1 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- active service list kswsgetso: get service obj 0 1 0 cache buffers chains kcbgtcr: kslbegin excl 0 46 47 cache buffers chains kcbgtcr: fast path 0 12 1 cache buffers chains kcbchg: kslbegin: bufs not 0 11 7 cache buffers chains kcbget: pin buffer 0 9 5 cache buffers chains kcbrls: kslbegin 0 4 17 cache buffers chains kcbchg: kslbegin: call CR 0 1 4 cost function kzulgt: find user res 0 1 1 dummy allocation ktcscnfy 0 2 1 enqueue hash chains ksqgtl3 0 2 1 enqueue hash chains ksqcmi: get hash chain lat 0 1 0 enqueues ksqgel: create enqueue 0 4 3 enqueues ksqdel 0 3 0 enqueues ksqies 0 2 12 library cache kglpndl: child: after proc 0 6 0 library cache kglobpn: child: 0 6 3 library cache kglpnp: child 0 6 15 library cache kglpndl: child: before pro 0 4 3 library cache kgldte: child 0 0 4 8 library cache kglhdgn: child: 0 3 5 library cache kglLockCursor 0 1 0 library cache kglic 0 1 0 library cache lock kgllkdl: child: no lock ha 0 3 0 library cache lock kgllkal: child: multiinsta 0 2 3 library cache lock kgllkdl: child: cleanup 0 2 1 library cache pin kglpnp: child 0 5 6 library cache pin kglpndl 0 2 1 parameter list kmmsvcl 0 1 1 process allocation ksudlp 0 1 1 redo allocation kcrfw_redo_gen 0 1 0 redo writing kcrfwcr 0 1 0 row cache objects kqreqd: reget 0 4 0 row cache objects kqrpre: find obj 0 3 7 session allocation ksucri 0 13 21 session allocation ksuxds: KSUSFCLC not set 0 8 0 session allocation ksuxds: not user session 0 2 3 session allocation ksudlc 0 2 1 session allocation ksucrp 0 1 1 session idle bit ksupuc: set busy 0 2 3 session idle bit ksuxds 0 1 0 session idle bit ksupuc: clear busy 0 1 3 shared pool kghalo 0 11 5 shared pool kghfre 0 1 1 simulator hash latch kcbsacc: lookup dba 0 3 2 simulator lru latch kcbs_simulate: simulate se 0 15 15 sort extent pool ktstcrtobj 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->"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 37 0.0 0 0 1 dc_global_oids 14,857 0.0 0 0 51 dc_histogram_data 2,435 1.0 0 0 856 dc_histogram_defs 3,422 10.8 0 0 4,283 dc_object_grants 18,110 0.0 0 0 51 dc_object_ids 15,782 0.1 0 0 1,428 dc_objects 21,820 0.6 0 0 1,365 dc_profiles 4,336 0.0 0 0 1 dc_rollback_segments 756 0.0 0 0 22 dc_segments 4,121 0.3 0 0 986 dc_sequences 357 0.0 0 357 9 dc_tablespace_quotas 27 11.1 0 21 6 dc_tablespaces 72,133 0.0 0 0 12 dc_usernames 27,096 0.0 0 0 24 dc_users 197,286 0.0 0 0 29 outstanding_alerts 48 0.0 0 0 11 ------------------------------------------------------------- Library Cache Activity DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 6 0.0 97,755 0.0 0 0 CLUSTER 6 0.0 12 0.0 0 0 SQL AREA 23,920 0.2 397,101 0.1 0 0 TABLE/PROCEDURE 7,980 1.3 184,239 0.2 3 0 ------------------------------------------------------------- Rule Sets DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc Rule * ----------------------------------- -------------------------------- No-SQL SQL Eval/sec Reloads/sec Eval % Eval % ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: SVTLSDB/svtlsdb End Snap: 19 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 160 .5 51 4,196 ####### 1.0 4,129 36.2 756,392 208 .6 85 6,613 ####### 1.0 114 1.0 756,742 256 .7 85 6,613 ####### 1.0 114 1.0 756,742 304 .9 85 6,613 ####### 1.0 114 1.0 756,742 352 1.0 85 6,613 ####### 1.0 114 1.0 756,742 400 1.1 85 6,613 ####### 1.0 114 1.0 756,742 448 1.3 85 6,613 ####### 1.0 114 1.0 756,742 496 1.4 85 6,613 ####### 1.0 114 1.0 756,742 544 1.5 85 6,613 ####### 1.0 114 1.0 756,742 592 1.7 85 6,613 ####### 1.0 114 1.0 756,742 640 1.8 85 6,613 ####### 1.0 114 1.0 756,742 688 2.0 85 6,613 ####### 1.0 114 1.0 756,742 736 2.1 85 6,613 ####### 1.0 114 1.0 756,742 ------------------------------------------------------------- SGA Target Advisory DB/Inst: SVTLSDB/svtlsdb End Snap: 19 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 1,000 .5 22,311 3.6 2,803,604 1,500 .8 7,980 1.3 896,488 2,000 1.0 6,258 1.0 667,477 2,500 1.3 5,990 1.0 631,700 3,000 1.5 5,992 1.0 631,700 3,500 1.8 5,994 1.0 631,700 4,000 2.0 5,998 1.0 631,700 ------------------------------------------------------------- SGA Memory Summary DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 1,677,721,600 Fixed Size 2,074,048 Redo Buffers 14,700,544 Variable Size 402,655,808 -------------------- -------------------- sum 2,097,152,000 ------------------------------------------------------------- SGA breakdown difference DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 -> 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 16.0 16.0 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 15.0 15.0 0.00 shared ASH buffers 8.0 8.0 0.00 shared CCursor 8.6 9.6 11.52 shared FileOpenBlock 7.1 7.1 0.00 shared KCB Table Scan Buffer 4.0 4.0 0.00 shared KGLS heap 20.3 20.8 2.24 shared KSFD SGA I/O b 4.0 4.0 0.00 shared PCursor 7.8 8.9 13.85 shared db_block_hash_buckets 11.3 11.3 0.00 shared free memory 171.4 158.1 -7.76 shared kglsim hash table bkts 4.0 4.0 0.00 shared library cache 12.8 15.3 19.84 shared row cache 7.2 7.2 0.00 shared sql area 40.9 47.9 16.92 buffer_cache 1,600.0 1,600.0 0.00 fixed_sga 2.0 2.0 0.00 log_buffer 14.0 14.0 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 23.89 25.41 5.99 Cursor to Parent ratio: 1.04 1.08 3.87 Total Cursors: 2,244 2,356 4.75 Total Parents: 2,158 2,178 .92 ------------------------------------------------------------- init.ora Parameters DB/Inst: SVTLSDB/svtlsdb Snaps: 18-19 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- background_dump_dest /home/oracle/admin/svtlsdb/bdump compatible 9.2.0.0.0 control_files /u02/oradata/svtlsdb/svtlsdb_cont core_dump_dest /home/oracle/admin/svtlsdb/cdump cursor_sharing SIMILAR cursor_space_for_time TRUE db_block_size 8192 db_cache_size 1056964608 db_domain cn.crazy.com db_files 1024 db_keep_cache_size 318767104 db_name svtlsdb db_recycle_cache_size 117440512 job_queue_processes 1 local_listener LISTENER_SVTLSDB max_dump_file_size 10240 open_cursors 3000 pga_aggregate_target 524288000 pre_page_sga TRUE processes 150 session_cached_cursors 3000 sga_max_size 2097152000 sga_target 2097152000 spfile /u02/oradata/svtlsdb/spfilesvtlsd timed_statistics TRUE undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest /home/oracle/admin/svtlsdb/udump ------------------------------------------------------------- End of Report ( 18-19-test.rep )