STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ PULSEDB 2982994484 PULSEDB1 1 9.2.0.6.0 YES pulpdb001.hs a.co.uk Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 15660 19-Dec-06 16:17:34 111 ######### End Snap: 15662 19-Dec-06 16:42:20 93 ######### Elapsed: 24.77 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 784M Std Block Size: 8K Shared Pool Size: 256M Log Buffer: 5,120K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 244,838.93 9,359.47 Logical reads: 34,214.99 1,307.94 Block changes: 1,458.39 55.75 Physical reads: 1,255.17 47.98 Physical writes: 71.56 2.74 User calls: 125.46 4.80 Parses: 84.31 3.22 Hard parses: 0.64 0.02 Sorts: 91.90 3.51 Logons: 0.09 0.00 Executes: 756.61 28.92 Transactions: 26.16 % Blocks changed per Read: 4.26 Recursive Call %: 89.34 Rollback per transaction %: 0.26 Rows per Sort: 16.70 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.92 Redo NoWait %: 100.00 Buffer Hit %: 96.33 In-memory Sort %: 100.00 Library Hit %: 99.92 Soft Parse %: 99.24 Execute to Parse %: 88.86 Latch Hit %: 99.96 Parse CPU to Parse Elapsd %: 83.66 % Non-Parse CPU: 97.88 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 84.81 85.43 % SQL with executions>1: 89.28 87.60 % Memory for SQL w/exec>1: 97.65 97.28 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- db file sequential read 520,726 2,086 42.71 wait for unread message on broadcast channel 1,486 1,451 29.72 db file scattered read 211,523 494 10.12 CPU time 323 6.61 global cache cr request 526,900 197 4.04 ------------------------------------------------------------- Cluster Statistics for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Global Cache Service - Workload Characteristics ----------------------------------------------- Ave global cache get time (ms): 2.0 Ave global cache convert time (ms): 104.6 Ave build time for CR block (ms): 0.0 Ave flush time for CR block (ms): 0.1 Ave send time for CR block (ms): 0.1 Ave time to process CR block request (ms): 0.1 Ave receive time for CR block (ms): 0.7 Ave pin time for current block (ms): 0.1 Ave flush time for current block (ms): 0.0 Ave send time for current block (ms): 0.1 Ave time to process current block request (ms): 0.1 Ave receive time for current block (ms): 0.9 Global cache hit ratio: 4.4 Ratio of current block defers: 0.0 % of messages sent for buffer gets: 4.1 % of remote buffer gets: 0.4 Ratio of I/O for coherence: 1.1 Ratio of local vs remote work: 9.8 Ratio of fusion vs physical writes: 0.1 Global Enqueue Service Statistics --------------------------------- Ave global lock get time (ms): 0.1 Ave global lock convert time (ms): 0.0 Ratio of global lock gets vs global lock releases: 1.0 GCS and GES Messaging statistics -------------------------------- Ave message sent queue time (ms): 12.4 Ave message sent queue time on ksxp (ms): 2.0 Ave message received queue time (ms): 0.1 Ave GCS message process time (ms): 0.0 Ave GES message process time (ms): 0.0 % of direct sent messages: 24.7 % of indirect sent messages: 43.7 % of flow controlled messages: 31.7 ------------------------------------------------------------- GES Statistics for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 flow control messages received 106 0.1 0.0 flow control messages sent 0 0.0 0.0 gcs ast xid 0 0.0 0.0 gcs blocked converts 11,482 7.7 0.3 gcs blocked cr converts 12,980 8.7 0.3 gcs compatible basts 19 0.0 0.0 gcs compatible cr basts (global) 2,456 1.7 0.1 gcs compatible cr basts (local) 194,239 130.7 5.0 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs error msgs 0 0.0 0.0 gcs flush pi msgs 5,553 3.7 0.1 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 7,332 4.9 0.2 gcs immediate (null) converts 47,327 31.8 1.2 gcs immediate cr (compatible) con 230,324 155.0 5.9 gcs immediate cr (null) converts 1,560,815 1,050.3 40.2 gcs msgs process time(ms) 33,631 22.6 0.9 gcs msgs received 2,044,205 1,375.6 52.6 gcs out-of-order msgs 85 0.1 0.0 gcs pings refused 14,117 9.5 0.4 gcs queued converts 4 0.0 0.0 gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 0 0.0 0.0 gcs retry convert request 0 0.0 0.0 gcs side channel msgs actual 10,339 7.0 0.3 gcs side channel msgs logical 1,033,900 695.8 26.6 gcs write notification msgs 230 0.2 0.0 gcs write request msgs 4,931 3.3 0.1 gcs writes refused 33 0.0 0.0 ges msgs process time(ms) 3,576 2.4 0.1 ges msgs received 403,473 271.5 10.4 global posts dropped 0 0.0 0.0 global posts queue time 0 0.0 0.0 global posts queued 0 0.0 0.0 global posts requested 0 0.0 0.0 global posts sent 0 0.0 0.0 implicit batch messages received 160,645 108.1 4.1 implicit batch messages sent 152,567 102.7 3.9 lmd msg send time(ms) 1,855 1.2 0.0 lms(s) msg send time(ms) 2 0.0 0.0 messages flow controlled 746,701 502.5 19.2 messages received actual 1,296,968 872.8 33.4 messages received logical 2,447,682 1,647.2 63.0 messages sent directly 582,395 391.9 15.0 messages sent indirectly 1,029,932 693.1 26.5 msgs causing lmd to send msgs 207,458 139.6 5.3 msgs causing lms(s) to send msgs 13,734 9.2 0.4 msgs received queue time (ms) 125,222 84.3 3.2 msgs received queued 2,447,662 1,647.1 63.0 msgs sent queue time (ms) 12,776,594 8,598.0 328.7 msgs sent queue time on ksxp (ms) 2,346,237 1,578.9 60.4 msgs sent queued 1,030,148 693.2 26.5 msgs sent queued on ksxp 1,151,789 775.1 29.6 GES Statistics for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ process batch messages received 189,574 127.6 4.9 process batch messages sent 192,199 129.3 4.9 ------------------------------------------------------------- Wait Events for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 520,726 0 2,086 4 13.4 wait for unread message on b 1,486 1,486 1,451 977 0.0 db file scattered read 211,523 0 494 2 5.4 global cache cr request 526,900 104 197 0 13.6 log file parallel write 39,046 0 97 2 1.0 db file parallel read 20,527 0 69 3 0.5 LNS wait on SENDREQ 424 0 26 62 0.0 buffer busy waits 22,226 0 25 1 0.6 buffer busy global CR 16,847 0 17 1 0.4 global cache open x 37,232 1 17 0 1.0 log file sync 4,397 0 14 3 0.1 enqueue 37,892 0 11 0 1.0 global cache null to x 5,638 1 10 2 0.1 DFS lock handle 34,523 0 10 0 0.9 log file sequential read 403 0 9 23 0.0 latch free 1,518 867 4 3 0.0 library cache lock 13,393 0 4 0 0.3 global cache s to x 7,713 0 3 0 0.2 control file parallel write 755 0 3 4 0.0 control file sequential read 5,240 0 3 0 0.1 global cache open s 4,109 0 2 0 0.1 global cache busy 12 1 1 120 0.0 CGS wait for IPC msg 217,903 215,853 1 0 5.6 process startup 49 0 1 24 0.0 LNS wait on LGWR 123,588 0 1 0 3.2 log file switch completion 6 0 1 110 0.0 row cache lock 3,721 0 1 0 0.1 KJC: Wait for msg sends to c 15,369 0 0 0 0.4 LGWR wait on LNS 60 14 0 3 0.0 LGWR-LNS wait on channel 26 14 0 6 0.0 SQL*Net break/reset to clien 106 0 0 1 0.0 ksxr poll remote instances 7,320 3,737 0 0 0.2 ARCH wait on SENDREQ 31 0 0 2 0.0 global cache null to s 101 0 0 1 0.0 ARCH wait on ATTACH 2 0 0 29 0.0 slave TJ process wait 4 4 0 13 0.0 db file parallel write 4,118 0 0 0 0.1 SQL*Net more data to client 2,311 0 0 0 0.1 log file single write 8 0 0 1 0.0 library cache pin 16 0 0 0 0.0 buffer busy global cache 2 0 0 3 0.0 LGWR wait for redo copy 246 0 0 0 0.0 local write wait 1 0 0 2 0.0 async disk IO 768 0 0 0 0.0 direct path read (lob) 292 0 0 0 0.0 direct path read 44 0 0 0 0.0 direct path write 44 0 0 0 0.0 lock deadlock retry 5 4 0 0 0.0 ARCH wait on DETACH 2 0 0 0 0.0 Wait Events for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- buffer deadlock 2 2 0 0 0.0 SQL*Net message from client 184,064 0 75,750 412 4.7 jobq slave wait 1,963 1,793 5,501 2802 0.1 gcs remote message 836,005 30,072 2,833 3 21.5 ges remote message 290,626 22,632 1,442 5 7.5 wakeup time manager 55 55 1,398 25409 0.0 SQL*Net more data from clien 2,306 0 0 0 0.1 SQL*Net message to client 184,094 0 0 0 4.7 ------------------------------------------------------------- Background Wait Events for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- wait for unread message on b 1,486 1,486 1,451 977 0.0 log file parallel write 39,046 0 97 2 1.0 LNS wait on SENDREQ 424 0 26 62 0.0 enqueue 31,514 0 10 0 0.8 DFS lock handle 33,285 0 9 0 0.9 log file sequential read 403 0 9 23 0.0 control file parallel write 755 0 3 4 0.0 control file sequential read 5,116 0 3 0 0.1 CGS wait for IPC msg 217,903 215,853 1 0 5.6 LNS wait on LGWR 123,605 0 1 0 3.2 db file sequential read 95 0 0 2 0.0 LGWR wait on LNS 60 14 0 3 0.0 LGWR-LNS wait on channel 26 14 0 6 0.0 latch free 356 40 0 0 0.0 ksxr poll remote instances 7,318 3,737 0 0 0.2 ARCH wait on SENDREQ 31 0 0 2 0.0 ARCH wait on ATTACH 2 0 0 29 0.0 db file parallel write 4,118 0 0 0 0.1 global cache cr request 65 0 0 1 0.0 global cache open x 33 0 0 1 0.0 log file single write 8 0 0 1 0.0 library cache lock 13 0 0 0 0.0 LGWR wait for redo copy 246 0 0 0 0.0 global cache s to x 5 0 0 1 0.0 async disk IO 768 0 0 0 0.0 buffer busy waits 1 0 0 0 0.0 direct path read 44 0 0 0 0.0 direct path write 44 0 0 0 0.0 KJC: Wait for msg sends to c 1 0 0 0 0.0 ARCH wait on DETACH 2 0 0 0 0.0 rdbms ipc message 71,750 17,499 24,833 346 1.8 gcs remote message 836,057 30,071 2,833 3 21.5 pmon timer 497 497 1,449 2916 0.0 ges remote message 290,629 22,630 1,442 5 7.5 smon timer 465 0 1,429 3073 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 18,992,181 10 1,899,218.1 37.4 45.66 53.00 2351430522 Module: JDBC Thin Client BEGIN Fin_Manual_Alloc_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END; 18,893,866 10 1,889,386.6 37.2 44.47 46.61 650378899 Module: JDBC Thin Client SELECT * FROM COM_TEMP_RISK WHERE TRANSID = :B1 AND (LPOLDUESEQ IN (SELECT LPOLDUESEQ FROM COM_TEMP_DUE WHERE TRANSID = :B1 AND LPOLDUESEQ IN (SELECT LPOLDUESEQ FROM COM_TEMP_FE_POL_DUE WHERE TRANSID =:B1 ) OR LTRANREFNBR IN (SELECT LPOLDUESEQ FROM COM_TEM P_FE_POL_DUE WHERE TRANSID =:B1 ) OR LTRANREFNBR IN ( SELECT CTD 6,689,398 14 477,814.1 13.2 32.05 35.59 3246231460 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN begin wf_com_diary_management_pkg .WF_TRANSFER_DEFFERED_ITEM_PRC; end ; :mydate := next_date; IF b roken THEN :b := 1; ELSE :b := 0; END IF; END; 5,721,343 93 61,519.8 11.3 21.37 39.76 2584253257 Module: JDBC Thin Client SELECT 'Refund' type, (select STRCDDESC from com_p aram_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NR FNDRSNCD ) strnarration, TO_CHAR(fvh.dtvou,'DD/MM/YYYY') d tvou, fvh.dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_S YSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARAMCD = fvc.NPMTREFTYPE 5,301,310 3,038 1,745.0 10.4 20.99 21.29 1195405947 SELECT NAME FROM ALL_QUEUES WHERE NAME = :B2 AND OWNER = :B1 AND ((TRIM(ENQUEUE_ENABLED) = 'NO') OR (TRIM(DEQUEUE_ENABLED) = 'NO ')) 2,123,942 1 2,123,942.0 4.2 82.91 321.29 2017419167 INSERT INTO FIN_CLUBBING_LOGIC ( STRCLIENTCD , STRFIRSTNAME , ST RADDRLINE1 , STRADDRLINE2 , STRADDRLINE3 , STRCOUNTRYCD , STRSTA TECD , STRTOWNCD , STRPOSTALCD , STRBASEDOCNBR , STRCASHBANKCD , STRBANKBRANCHCD , STRBANKACCTNBR , STRBRANDCD , STRVOUTYPE , ST RVOUSERIES , LVOUNBR , NFISCALYEAR , NCLUB, DVOUAMNT ) SELECT CP 1,660,046 27 61,483.2 3.3 6.30 9.34 2239433510 Module: JDBC Thin Client SELECT 'Refund' type, (select STRCDDESC from com_p aram_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NR FNDRSNCD ) strnarration , TO_CHAR(cap.dtpay,'DD/MM/YYYY') dtvou, cap.dlocalcurramnt dvouamnt, (SELECT STRCD DESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARA 1,571,224 4,614 340.5 3.1 21.88 25.16 2756772217 UPDATE FIN_VOU_CSHBNK SET LPMTDTLSEQ = :B5 , NPMTREFSTATCD = 2, DTPMTREFSTAT = SYSDATE WHERE STRVOUTYPE = :B4 AND STRVOUSERIES = :B3 AND NFISCALYEAR = :B2 AND STRBRANDCD = :B1 AND LVOUNBR IN ( SQL ordered by Gets for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT LVOUNBR FROM FIN_CLUBBING_LOGIC WHERE STRCLIENTCD = :B18 AND NVL(STRFIRSTNAME,'*') = NVL(:B17 ,'*') AND NVL(STRADDRLINE1 1,093,680 1,519 720.0 2.2 4.48 4.18 1983252009 SELECT S.ROWID ROW_ID FROM WF_ITEM_ACTIVITY_STATUSES S WHERE S.D UE_DATE < SYSDATE AND S.ACTIVITY_STATUS IN ('ACTIVE','WAITING',' NOTIFIED', 'SUSPEND','DEFERRED') 906,795 56 16,192.8 1.8 47.36 307.17 2496791006 Module: JDBC Thin Client SELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FRO M DUAL 905,190 56 16,164.1 1.8 47.26 306.64 1284180873 Module: JDBC Thin Client SELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, P S_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.ST RALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NUL L UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PA H WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1 904,666 57 15,871.3 1.8 50.72 314.68 4012608250 SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD, DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_ QUEUE WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTA TCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE)) AND M OD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD 707,985 3 235,995.0 1.4 2.60 8.75 878760152 Module: JDBC Thin Client select cgm.strgroupname grpname, cgm1.strgroupname primegrpname , cca.straddrline1 grpaddr1, cca.straddrline2 grpaddr2, cca.st raddrline3 grpaddr3, cps.strcddesc grptype, cgm.strgroupid grpid, cgm1.strgroupid primegrpid, cca.strpostalcd postal cd, cca.strclientcd strclientcd,(SELECT strcddesc from com_par 666,518 78 8,545.1 1.3 35.13 217.93 2099693177 Module: JDBC Thin Client BEGIN Om_Chk_Prompt_Reqm_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END; 616,784 27 22,843.9 1.2 6.29 19.59 3557186462 Module: JDBC Thin Client SELECT NPMNTDTLID, NQRYSTATUS, LPOLDUESEQ, 'Premium' type, strpolnbr, Decode(npaymentterms,2,To_Char(dtduefrom,'dd/mm/yyyy') ,1,To_Char(dtdueto,'dd/mm/yyyy'),To_Char(dtdue,'dd/mm/yyyy')) dt due, To_Char(dtdue,'dd/mm/yyyy') realdtdue, To 553,661 9 61,517.9 1.1 2.19 2.17 2915859633 Module: JDBC Thin Client SQL ordered by Gets for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- SELECT * FROM ( SELECT 'Refund' type, (select STRC DDESC from com_param_user_m where IPARAMTYPECD = 6714 and strpar amcd = frodt.NRFNDRSNCD ) strnarration, TO_CHAR(fvh.dtvou ,'DD/MM/YYYY') dtvou, fvh.dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARAMCD = ------------------------------------------------------------- SQL ordered by Reads for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 550,167 56 9,824.4 29.5 47.36 307.17 2496791006 Module: JDBC Thin Client SELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FRO M DUAL 550,085 56 9,822.9 29.5 47.26 306.64 1284180873 Module: JDBC Thin Client SELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, P S_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.ST RALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NUL L UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PA H WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1 524,424 57 9,200.4 28.1 50.72 314.68 4012608250 SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD, DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_ QUEUE WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTA TCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE)) AND M OD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD 402,672 78 5,162.5 21.6 35.13 217.93 2099693177 Module: JDBC Thin Client BEGIN Om_Chk_Prompt_Reqm_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END; 270,319 1 270,319.0 14.5 82.91 321.29 2017419167 INSERT INTO FIN_CLUBBING_LOGIC ( STRCLIENTCD , STRFIRSTNAME , ST RADDRLINE1 , STRADDRLINE2 , STRADDRLINE3 , STRCOUNTRYCD , STRSTA TECD , STRTOWNCD , STRPOSTALCD , STRBASEDOCNBR , STRCASHBANKCD , STRBANKBRANCHCD , STRBANKACCTNBR , STRBRANDCD , STRVOUTYPE , ST RVOUSERIES , LVOUNBR , NFISCALYEAR , NCLUB, DVOUAMNT ) SELECT CP 158,563 16 9,910.2 8.5 13.87 96.82 1768391363 Module: JDBC Thin Client BEGIN Com_Queue_Insert_Prc (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,: 12,:13); END; 155,684 14 11,120.3 8.3 13.60 166.43 689586070 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN begin om_generate_output_pkg.om_r un_report_in_que_prc(3); end ; :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END; 146,700 14 10,478.6 7.9 13.01 147.49 1395092802 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN begin om_generate_output_pkg.om_r un_report_in_que_prc(2); end ; :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END; 111,819 14 7,987.1 6.0 12.05 171.84 4265822483 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN begin om_generate_output_pkg.om_r un_report_in_que_prc(0); end ; :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END; SQL ordered by Reads for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 110,264 14 7,876.0 5.9 13.39 166.19 206930920 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN begin om_generate_output_pkg.om_r un_report_in_que_prc(1); end ; :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END; 46,232 154 300.2 2.5 5.04 181.06 1937334617 Module: JDBC Thin Client BEGIN com_customer_search_prc(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11 ,:12,:13,:14,:15,:16,:17); END; 35,985 8,461 4.3 1.9 4.41 238.91 326303322 SELECT 1 FROM com_trans_int_hdr ctih,com_Trans_int_dtl ctid,com _acc_pay_appr capa,com_Acc_pay cap ,com_currency_m ccm WHERE cti h.ltransinthdrseqnbr = ctid.ltransinthdrseqnbr AND ctih.strclien tcd = :l AND ctih.nvouind = 2 AND ctih.nisvoupassed = 0 AND c tih.nrefind IN (2,4) AND ctid.lapprseqnbr = capa.laccpayapprseq 34,041 3 11,347.0 1.8 2.46 25.54 3555506540 Module: ? @pulprds002.hsa.co.uk (TNS V1-V3) SELECT dtduefrom, dtdueto, dtddrun, damnt FROM nb_temp_insert_report_dtl WHERE ltemprepseq =:P_r epseqno 26,028 16,847 1.5 1.4 10.56 176.30 2148339904 Module: SQL*Plus INSERT INTO FIN_VOU_HDR ( STRVOUTYPE, STRVOUSERIES, NFISCALYEAR, LVOUNBR, DTVOU, STRNARRATION, NFISCALMONTH, NVOUSTAT, STRCURRCD , DEXCHANGERATE, NBASEDOCTYPE, STRBASEDOCNBR, NREFIND, NREFTYPE, STRREFNBR, STRCLIENTCD, STRREFVOUTYPE, STRREFVOUSERIES, NREFFIS CALYEAR, LREFVOUNBR, NISPOSTED, NCLUBPMT, STRREASNCD, STROTHREFN 23,886 140 170.6 1.3 2.73 84.28 2016421978 Module: JDBC Thin Client SELECT a.ITEM_KEY LREMINDERID, ( SELECT text_value FROM wf_item _attribute_values b WHERE item_type='DIARY' AND b.ITEM_KEY=a.I TEM_KEY AND b.NAME='SUBJECT' ) STRREMINDER, To_Date((SELECT date_value FROM wf_item_attribute_values C WHERE item_type='DIA RY' AND c.ITEM_KEY=a.ITEM_KEY AND NAME='SCHEDULE_DATE') || ' ' 22,202 3 7,400.7 1.2 2.08 72.92 2761365050 Module: JDBC Thin Client INSERT INTO ps_clientcds SELECT * FROM ( SELECT Decode(cgm.str clientcd,NULL,ccm.strclientcd,NULL) strclientcd FROM com_clie nt_m ccm, com_group_m cgm, ( SELECT NULL from DUAL where :pi_st rpostalcd IS NULL ) grpcnt, DUAL WHERE ccm.strclientcd = cgm. strclientcd(+) AND ccm.strfirstname LIKE :pi_strfirstname AND 19,873 4 4,968.3 1.1 1.78 76.93 646603137 Module: JDBC Thin Client INSERT INTO ps_clientcds SELECT * FROM ( SELECT Decode(cgm.str clientcd,NULL,ccm.strclientcd,NULL) strclientcd FROM com_clie nt_m ccm, com_group_m cgm, ( SELECT NULL from DUAL where :pi_st SQL ordered by Reads for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- ------------------------------------------------------------- SQL ordered by Executions for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -156 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 47,325 47,325 1.0 0.00 0.00 328566932 Module: JDBC Thin Client SELECT DECODE ( :B3 , 1, DOPENRATE, 2, DHIGHRATE, 3, DLOWRATE, 4 , DCLOSERATE, 5, DLASTBIDRATE ), DBASEUNIT FROM FIN_EXCHNG_RATE_ M WHERE :B2 BETWEEN DTEFFFROM AND DTEFFTO AND UPPER ( STRCURRCD ) = UPPER ( :B1 ) 47,325 47,325 1.0 0.00 0.00 1764337026 Module: JDBC Thin Client SELECT NXCHGRATEBASIS FROM FIN_PREFERENCES WHERE STRBRANDCD = :B 1 40,265 0 0.0 0.00 0.00 1053795750 COMMIT 31,683 30,883 1.0 0.00 0.00 2318608989 SELECT NPARAMCD FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = :B2 AND UPPER ( STRPARAM1 ) = :B1 25,243 25,243 1.0 0.00 0.00 830034152 Module: SQL*Plus SELECT STRPRODLINE FROM PRD_KEY_INFO WHERE STRPRODCD = :B2 AND N PRODVER = :B1 21,930 21,930 1.0 0.00 0.00 1713915481 Module: SQL*Plus SELECT PKI.STRBRANDCD FROM COM_POL_PROD_DTL CPPD, PRD_KEY_INFO P KI WHERE CPPD.STRPRODCD = PKI.STRPRODCD AND CPPD.NPRODVER = PKI. NPRODVER AND CPPD.STRPOLNBR = :B1 AND ROWNUM = 1 17,485 17,485 1.0 0.00 0.00 1273663408 SELECT STRREQDQUERY, STRDTLQUERY1, STRDTLQUERY2, NISMANUALREMVL FROM SM_PROMPT_M WHERE LPROMPTID = :B1 17,485 17,485 1.0 0.00 0.00 3140707388 SELECT COUNT(*) FROM SM_PROMPT_ACTIVE WHERE LPROMPTID = :B2 AND STRCLIENTCD = :B1 16,922 16,922 1.0 0.00 0.00 2597680315 Module: JDBC Thin Client SELECT NPAYAPPRTYPE, STRPAYAPPRCHGTYPE FROM COM_ACC_PAY_APPR WHE RE LACCPAYAPPRSEQNBR = :B1 16,897 16,897 1.0 0.00 0.00 137164473 Module: SQL*Plus SELECT FIN_VOUAPPR_SEQ.NEXTVAL FROM DUAL 16,896 16,896 1.0 0.00 0.00 1857148722 Module: SQL*Plus INSERT INTO FIN_VOU_APPR_DTL ( LVOUAPPRSEQNBR, STRVOUTYPE, STRVO USERIES, NFISCALYEAR, LVOUNBR, DFCAMNT, DLOCALCURRAMNT, NISDRORC R, NAPPRSEQTYPE, LAPPRSEQNBR, DTCREATED, STRCREATEDBY, DTUPDATED , STRUPDATEDBY, DEXCHANGERATE, STRBRANDCD, NPOSTKEY, NPRODVER, S TRPRODCD, STRPRODLINE, NDUETYPE ) VALUES ( :B24 , :B23 , :B22 , SQL ordered by Executions for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -156 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 16,896 16,896 1.0 0.00 0.00 3063723326 Module: SQL*Plus INSERT INTO FIN_VOU_CUST ( STRVOUTYPE, STRVOUSERIES, NFISCALYEAR , LVOUNBR, STRBRANDCD, DEXCHANGERATE, DFCAMNT, DLOCALCURRAMNT, N ISDRORCR, NPOSTKEY, DTCREATED, STRCREATEDBY, DTUPDATED, STRUPDAT EDBY, STRPRODCD, STRPRODLINE, NDUETYPE, NAPPRSEQTYPE, LAPPRSEQNB R ) VALUES ( :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , ( ROUND ( 16,854 16,854 1.0 0.00 0.00 372357728 Module: SQL*Plus SELECT NFISCALYEAR, NFISCALMONTH FROM FIN_FINANCIAL_YEAR WHERE : B2 BETWEEN DTSTART AND DTEND AND NFISCALMONTH != 0 AND NVL ( NST ATCD, 0 ) IN ( 0, 2 ) AND STRBRANDCD = :B1 16,847 16,847 1.0 0.00 0.01 2148339904 Module: SQL*Plus INSERT INTO FIN_VOU_HDR ( STRVOUTYPE, STRVOUSERIES, NFISCALYEAR, LVOUNBR, DTVOU, STRNARRATION, NFISCALMONTH, NVOUSTAT, STRCURRCD , DEXCHANGERATE, NBASEDOCTYPE, STRBASEDOCNBR, NREFIND, NREFTYPE, STRREFNBR, STRCLIENTCD, STRREFVOUTYPE, STRREFVOUSERIES, NREFFIS CALYEAR, LREFVOUNBR, NISPOSTED, NCLUBPMT, STRREASNCD, STROTHREFN 16,847 16,847 1.0 0.00 0.00 2416298380 Module: SQL*Plus SELECT COUNT ( * ) FROM FIN_DOC_SERIES_M WHERE STRVOUTYPE = :B2 AND STRVOUSERIES = :B1 16,847 16,847 1.0 0.00 0.00 3739083495 Module: SQL*Plus UPDATE fin_vs_no_d SET llastvounbr = NVL(llastvounbr,0) + 1 WH ERE strvoutype = :prm_strvoutype AND strvouseries = :prm_str vouseries AND nfiscalyear = decode(:prm_strvoutype, 'IN',9999 ,:prm_fiscal_year) AND Nvl ( strbrandcd, '-' ) = Dec ode ( :prm_strvoutype, 'IN', '-', 16,788 16,788 1.0 0.00 0.00 2216569138 SELECT SUBSTR ( STRCDDESC, 1, 3 ) FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = :B1 AND NPARAMCD = 5 16,748 16,742 1.0 0.00 0.00 1125133621 Module: SQL*Plus SELECT NPOSTKEY FROM FIN_POST_KEY WHERE NTABLEID = :B2 AND NPOST PROCESSID = :B1 15,324 6,863 0.4 0.00 0.00 3959132706 Module: JDBC Thin Client SELECT DISTINCT LSETNBR FROM FIN_VOU_CSHBNK_PV_AUD WHERE STRVOUT YPE = :B8 AND STRVOUSERIES = :B7 AND NFISCALYEAR = :B6 AND STRBR ANDCD = :B5 AND LVOUNBR = :B4 AND LINWARDNBR = :B3 AND STRCASHBA NKCD = :B2 AND NPMTREFTYPE = :B1 14,747 14,747 1.0 0.00 0.00 1802494505 Module: ? @pulprds002.hsa.co.uk (TNS V1-V3) SQL ordered by Executions for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -156 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- SELECT TRUNC ( DTLOGICAL ) FROM COM_LOGICAL_DATE 10,610 10,610 1.0 0.00 0.00 1202416549 SELECT NPARAMCD FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 701 5 10,198 10,198 1.0 0.00 0.00 1023339635 Module: JDBC Thin Client SELECT NBASEDOCTYPE FROM COM_ACC_PAY WHERE LACCPAYSEQNBR = :B1 8,657 8,657 1.0 0.00 0.00 1012893102 SELECT 1 FROM COM_CLIENT_M WHERE STRCLIENTCD = :B1 8,548 8,548 1.0 0.00 0.00 2194671270 Module: JDBC Thin Client SELECT 1 FROM COM_EVENT_M WHERE LEVENTSEQ = :B1 8,548 17,270 2.0 0.00 0.00 4167100748 Module: JDBC Thin Client SELECT LPROMPTID FROM SM_PROMPT_M WHERE STREVENTID LIKE '%~' || :B1 || '~%' 8,536 8,536 1.0 0.00 0.00 1105116824 Module: JDBC Thin Client SELECT STRREFNBR ,NTRANSTYPE ,NPAYTYPE FROM COM_ACC_PAY WHERE LA CCPAYSEQNBR = :B1 ------------------------------------------------------------- SQL ordered by Parse Calls for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 16,847 16,847 13.45 3739083495 Module: SQL*Plus UPDATE fin_vs_no_d SET llastvounbr = NVL(llastvounbr,0) + 1 WH ERE strvoutype = :prm_strvoutype AND strvouseries = :prm_str vouseries AND nfiscalyear = decode(:prm_strvoutype, 'IN',9999 ,:prm_fiscal_year) AND Nvl ( strbrandcd, '-' ) = Dec ode ( :prm_strvoutype, 'IN', '-', 8,461 8,461 6.75 326303322 SELECT 1 FROM com_trans_int_hdr ctih,com_Trans_int_dtl ctid,com _acc_pay_appr capa,com_Acc_pay cap ,com_currency_m ccm WHERE cti h.ltransinthdrseqnbr = ctid.ltransinthdrseqnbr AND ctih.strclien tcd = :l AND ctih.nvouind = 2 AND ctih.nisvoupassed = 0 AND c tih.nrefind IN (2,4) AND ctid.lapprseqnbr = capa.laccpayapprseq 8,461 8,461 6.75 3376334763 SELECT 1 FROM com_acc_pay CAP,com_currency_m ccm WHERE npaytype = 11 AND cap.strcurrcd = ccm.strcurrcd AND laccpayseqnbr IN (SEL ECT laccpayseqnbr FROM com_acc_pay_appr WHERE laccpayapprseqnbr IN ( SELECT lapprseqnbr FROM com_trans_int_dtl WHERE napprseqtyp e = 2 AND ltransinthdrseqnbr IN (SELECT ltransinthdrseqnbr FROM 6,464 6,464 5.16 1401579238 select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from id l_sb4$ where obj#=:1 and version<=:2 and (part=0 or part=2) an d piece#=0 3,126 3,126 2.50 3716207873 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=: 1 2,191 2,190 1.75 4054391816 SELECT INSTR(:1,'~', :2, 1) FROM DUAL 2,020 1,010 1.61 4264821501 Module: CMImport.exe begin WF_OUTBOUND_DOC_UPD_PRC(:V00001,:V00002,:V00003,:V00004,:V 00005); end; 2,001 2,001 1.60 1156476881 SELECT SUBSTR(:1,:2,:3) FROM DUAL 1,690 1,690 1.35 2569213986 Module: JDBC Thin Client SELECT spa.LPROMPTHISTSEQ,spm.LPROMPTID,spm.STRPROMPTDESC, spa.S TRPROMPTPARAGRAPH, spm.NPROMPTIND,spm.NISMANUALREMVL,spm.NCA PTRSNFORREMVL FROM SM_PROMPT_M spm,SM_PROMPT_ACTIVE spa WHER E spm.LPROMPTID = spa.LPROMPTID AND Trunc(Sysdate) BETWEEN Nv l(DTEFFFROM,Trunc(Sysdate)) AND Nvl(DTEFFTO,Trunc(Sysdate)) A 792 792 0.63 1998777504 Module: JDBC Thin Client SELECT COUNT(1) CNT FROM COM_POL_CLIENT_LNK WHERE STRPOLNBR = SQL ordered by Parse Calls for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- :1 AND STRCLIENTCD = :2 699 699 0.56 3129725218 Module: JDBC Thin Client SELECT STRFIRSTNAME, STRMIDDLENA ME, STRLASTNAME, STRPREFNAME, STRTITLECD, NGENDERCD, DTBIRTH, NMARITALSTATCD, N NOADDRESS, NNOADDRRSN, NPREFERC 659 40,265 0.53 1053795750 COMMIT 643 643 0.51 2364374604 Module: JDBC Thin Client SELECT LADDRSEQ, STRCLIENTCD, STRADDRLINE1, STRADDRLINE2, STRADDRLINE3, STRTOWN, STRSTATECD, STRPOSTALCD, STRCOUNTRYCD, STRAD DRTYPECD FROM COM_CLIENT_ADDRESS WHE 584 584 0.47 796314907 Module: JDBC Thin Client SELECT DVOUAMNT FROM FIN_VOU_HDR FVH, COM_POL_DUE CPD WHERE CPD.STRVOUTYPE = FVH.STRVOUTYPE AND CPD.STRVOUSERIES = FVH .STRVOUSERIES AND CPD.NFISCALYEAR = FVH.NFISCALYEAR A ND CPD.STRBRANDCD = FVH.STRBRANDCD AND CPD.LVOUNBR = F VH.LVOUNBR AND CPD.STRPOLNBR = FVH.STRBASEDOCNBR AND 517 517 0.41 3179265942 Module: JDBC Thin Client SELECT cpm.strpolnbr,npolstatcd, strbrandcd, nsaccd, CASE WHEN (npolstatcd = 12 AND STRRETNFOLLOWUPKEY IS NOT NULL) TH EN strbrandcd || '-' || cpm.strpolnbr || '-' || 'Suspe nd' || '-' || cpsm2.strcddesc ELSE strbrandcd || '-' | | cpm.strpolnbr || '-' || cpsm1.strcddesc || '-' || cpsm2.strcdd 409 409 0.33 2370008668 select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from id l_sb4$ where obj#=:1 and version<=:2 and part=1 and piece#=0 325 325 0.26 533594675 SELECT SUBSTR( :"SYS_B_00",INSTR( :"SYS_B_01",:"SYS_B_02",INSTR( :"SYS_B_03", :"SYS_B_04" ))+:"SYS_B_05",INSTR( :"SYS_B_06",:"SY S_B_07",INSTR( :"SYS_B_08", :"SYS_B_09" ))-INSTR( :"SYS_B_10",:" SYS_B_11",INSTR( :"SYS_B_12", :"SYS_B_13" ))-:"SYS_B_14") FROM d ual 306 306 0.24 1784446816 Module: JDBC Thin Client Select CCC.NCONTPRIORPTYNO NCONTPRIORPTYNO, CCC.STRCO NTACTTYPECD STRCONTACTTYPECD, CCC.STRCONTACTNBR STRCONTACTN BR, CCC.NEXDIRECTORY NEXDIRECTORY, CCC.STRCONTTIMES ST RCONTTIMES, CCC.NSOURCETYPE NSOURCETYPE, CCC.LCLIENTCONT SQL ordered by Parse Calls for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- ------------------------------------------------------------- SQL ordered by Sharable Memory for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 6,821,088 42 1.8 1412250683 Module: JDBC Thin Client select ccb.strbankacctnbr, ccb.strbankbranchcd, ccb.stracctholde rname from ps_alt_contact_bank_history pah,com_client_bank_acct s ccb where pah.lseqno = ccb.lclientbankacctseq and pah.nseqtype = :"SYS_B_0" and pah.stralttranshdrnbr like :"SYS_B_1" 3,583,491 202 0.9 3495796091 Module: JDBC Thin Client INSERT INTO COM_ERROR_HISTORY ( LERRHISTSEQ, LTRANID, NSUBTRANID , STRERRORNBR, STRERRORDESC, NEXCEPTIONTYPE, NOVERRIDDEN, STRUPD ATEDBY, STRREMARKS, DTCREATED, STRCREATEDBY ) VALUES ( COM_ERROR _HISTORY_SEQ.NEXTVAL, :B10 , DECODE(:B9 ,'3',0,:B8 ), :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , SYSDATE, :B1 ) 1,857,724 6 0.5 1389029292 Module: JDBC Thin Client SELECT straddrline1, straddrline2, straddrline3, strtown, strsta tecd, strpostalcd, strcountrycd FROM com_client_address cca , ps_alt_contact_bank_history pacb WHERE cca.laddrseq = pacb.lseq no AND pacb.nseqtype = :"SYS_B_0" AND stralttranshdrnbr LIKE :"S YS_B_1" ------------------------------------------------------------- SQL ordered by Version Count for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 - -> End Version Count Threshold: 20 Version Count Executions Hash Value -------- ------------ ------------ 183 202 3495796091 Module: JDBC Thin Client INSERT INTO COM_ERROR_HISTORY ( LERRHISTSEQ, LTRANID, NSUBTRANID , STRERRORNBR, STRERRORDESC, NEXCEPTIONTYPE, NOVERRIDDEN, STRUPD ATEDBY, STRREMARKS, DTCREATED, STRCREATEDBY ) VALUES ( COM_ERROR _HISTORY_SEQ.NEXTVAL, :B10 , DECODE(:B9 ,'3',0,:B8 ), :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , SYSDATE, :B1 ) 119 42 1412250683 Module: JDBC Thin Client select ccb.strbankacctnbr, ccb.strbankbranchcd, ccb.stracctholde rname from ps_alt_contact_bank_history pah,com_client_bank_acct s ccb where pah.lseqno = ccb.lclientbankacctseq and pah.nseqtype = :"SYS_B_0" and pah.stralttranshdrnbr like :"SYS_B_1" 37 6 1389029292 Module: JDBC Thin Client SELECT straddrline1, straddrline2, straddrline3, strtown, strsta tecd, strpostalcd, strcountrycd FROM com_client_address cca , ps_alt_contact_bank_history pacb WHERE cca.laddrseq = pacb.lseq no AND pacb.nseqtype = :"SYS_B_0" AND stralttranshdrnbr LIKE :"S YS_B_1" ------------------------------------------------------------- Instance Activity Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 32,299 21.7 0.8 CPU used when call started 23,851 16.1 0.6 CR blocks created 17,644 11.9 0.5 Cached Commit SCN referenced 50,166 33.8 1.3 Commit SCN cached 105 0.1 0.0 DBWR buffers scanned 544,345 366.3 14.0 DBWR checkpoint buffers written 16,143 10.9 0.4 DBWR checkpoints 2 0.0 0.0 DBWR cross instance writes 0 0.0 0.0 DBWR free buffers found 457,107 307.6 11.8 DBWR fusion writes 5,978 4.0 0.2 DBWR lru scans 1,514 1.0 0.0 DBWR make free requests 1,533 1.0 0.0 DBWR revisited being-written buff 1 0.0 0.0 DBWR summed scan depth 544,345 366.3 14.0 DBWR transaction table writes 301 0.2 0.0 DBWR undo block writes 19,300 13.0 0.5 Parallel operations downgraded to 0 0.0 0.0 RowCR - row contention 0 0.0 0.0 RowCR attempts 25,815 17.4 0.7 RowCR hits 25,812 17.4 0.7 SQL*Net roundtrips to/from client 183,986 123.8 4.7 SQL*Net roundtrips to/from dblink 0 0.0 0.0 active txn count during cleanout 16,551 11.1 0.4 background checkpoints completed 2 0.0 0.0 background checkpoints started 2 0.0 0.0 background timeouts 3,808 2.6 0.1 branch node splits 13 0.0 0.0 buffer is not pinned count 44,964,678 30,258.9 1,156.7 buffer is pinned count 31,973,895 21,516.8 822.5 bytes received via SQL*Net from c 27,416,545 18,449.9 705.3 bytes received via SQL*Net from d 0 0.0 0.0 bytes sent via SQL*Net to client 36,448,769 24,528.1 937.6 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgss 1,920,319 1,292.3 49.4 calls to kcmgas 134,591 90.6 3.5 calls to kcmgcs 46,880 31.6 1.2 change write time 871 0.6 0.0 cleanout - number of ktugct calls 32,572 21.9 0.8 cleanouts and rollbacks - consist 8,264 5.6 0.2 cleanouts only - consistent read 6,034 4.1 0.2 cluster key scan block gets 2,169 1.5 0.1 cluster key scans 979 0.7 0.0 commit cleanout failures: block l 2,149 1.5 0.1 commit cleanout failures: buffer 3 0.0 0.0 commit cleanout failures: callbac 181 0.1 0.0 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 684,846 460.9 17.6 commit cleanouts successfully com 682,513 459.3 17.6 commit txn count during cleanout 138,934 93.5 3.6 consistent changes 52,953 35.6 1.4 consistent gets 47,733,685 32,122.3 1,227.9 consistent gets - examination 4,404,172 2,963.8 113.3 current blocks converted for CR 9 0.0 0.0 cursor authentications 95 0.1 0.0 data blocks consistent reads - un 51,844 34.9 1.3 Instance Activity Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ db block changes 2,167,170 1,458.4 55.8 db block gets 3,110,080 2,092.9 80.0 deferred (CURRENT) block cleanout 274,698 184.9 7.1 dirty buffers inspected 5,269 3.6 0.1 enqueue conversions 51,083 34.4 1.3 enqueue deadlocks 0 0.0 0.0 enqueue releases 506,317 340.7 13.0 enqueue requests 506,358 340.8 13.0 enqueue timeouts 8 0.0 0.0 enqueue waits 20,724 14.0 0.5 exchange deadlocks 2 0.0 0.0 execute count 1,124,319 756.6 28.9 free buffer inspected 10,132 6.8 0.3 free buffer requested 2,133,144 1,435.5 54.9 gcs messages sent 2,065,716 1,390.1 53.1 ges messages sent 290,820 195.7 7.5 global cache blocks lost 0 0.0 0.0 global cache convert time 187,200 126.0 4.8 global cache converts 17,895 12.0 0.5 global cache cr block build time 17 0.0 0.0 global cache cr block flush time 104 0.1 0.0 global cache cr block receive tim 805 0.5 0.0 global cache cr block send time 65 0.0 0.0 global cache cr blocks received 11,803 7.9 0.3 global cache cr blocks served 12,874 8.7 0.3 global cache current block flush 98 0.1 0.0 global cache current block pin ti 996 0.7 0.0 global cache current block receiv 18,266 12.3 0.5 global cache current block send t 1,153 0.8 0.0 global cache current blocks recei 197,561 133.0 5.1 global cache current blocks serve 190,812 128.4 4.9 global cache defers 174 0.1 0.0 global cache freelist waits 0 0.0 0.0 global cache get time 400,927 269.8 10.3 global cache gets 2,032,271 1,367.6 52.3 global cache prepare failures 1 0.0 0.0 global cache skip prepare failure 6,440 4.3 0.2 global lock async converts 2,991 2.0 0.1 global lock async gets 382 0.3 0.0 global lock convert time 39 0.0 0.0 global lock get time 3,146 2.1 0.1 global lock releases 574,135 386.4 14.8 global lock sync converts 54,251 36.5 1.4 global lock sync gets 591,961 398.4 15.2 hot buffers moved to head of LRU 200,151 134.7 5.2 immediate (CR) block cleanout app 14,303 9.6 0.4 immediate (CURRENT) block cleanou 31,765 21.4 0.8 index fast full scans (full) 120 0.1 0.0 index fetch by key 1,209,651 814.0 31.1 index scans kdiixs1 4,248,979 2,859.3 109.3 leaf node 90-10 splits 362 0.2 0.0 leaf node splits 3,843 2.6 0.1 logons cumulative 139 0.1 0.0 messages received 53,400 35.9 1.4 messages sent 53,401 35.9 1.4 no buffer to keep pinned count 0 0.0 0.0 Instance Activity Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ no work - consistent read gets 41,644,340 28,024.5 1,071.3 opened cursors cumulative 124,569 83.8 3.2 parse count (failures) 676 0.5 0.0 parse count (hard) 946 0.6 0.0 parse count (total) 125,287 84.3 3.2 parse time cpu 686 0.5 0.0 parse time elapsed 820 0.6 0.0 physical reads 1,865,190 1,255.2 48.0 physical reads direct 44 0.0 0.0 physical reads direct (lob) 292 0.2 0.0 physical writes 106,345 71.6 2.7 physical writes direct 44 0.0 0.0 physical writes direct (lob) 0 0.0 0.0 physical writes non checkpoint 104,676 70.4 2.7 pinned buffers inspected 3,874 2.6 0.1 prefetch clients - default 0 0.0 0.0 prefetched blocks 1,247,538 839.5 32.1 prefetched blocks aged out before 149 0.1 0.0 process last non-idle time 1,489 1.0 0.0 recursive calls 1,561,852 1,051.0 40.2 recursive cpu usage 23,478 15.8 0.6 redo blocks written 757,862 510.0 19.5 redo buffer allocation retries 4 0.0 0.0 redo entries 1,120,264 753.9 28.8 redo log space requests 7 0.0 0.0 redo log space wait time 68 0.1 0.0 redo ordering marks 19,708 13.3 0.5 redo size 363,830,656 244,838.9 9,359.5 redo synch time 1,480 1.0 0.0 redo synch writes 4,517 3.0 0.1 redo wastage 11,967,968 8,053.8 307.9 redo write time 10,129 6.8 0.3 redo writer latching time 2 0.0 0.0 redo writes 39,047 26.3 1.0 remote instance undo block writes 0 0.0 0.0 rollback changes - undo records a 1,664 1.1 0.0 rollbacks only - consistent read 9,633 6.5 0.3 rows fetched via callback 821,987 553.2 21.2 session connect time 0 0.0 0.0 session logical reads 50,843,469 34,215.0 1,307.9 session pga memory 47,752,296 32,134.8 1,228.4 session pga memory max 7,963,908 5,359.3 204.9 session uga memory 300,636,709,804 202,312,725.3 7,733,818.1 session uga memory max 100,659,040 67,738.3 2,589.4 shared hash latch upgrades - no w 1,055,704 710.4 27.2 shared hash latch upgrades - wait 5 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 136,568 91.9 3.5 sorts (rows) 2,280,415 1,534.6 58.7 summed dirty queue length 93,387 62.8 2.4 switch current to new buffer 16,553 11.1 0.4 table fetch by rowid 24,530,951 16,508.0 631.1 table fetch continued row 23,081 15.5 0.6 table scan blocks gotten 23,335,780 15,703.8 600.3 table scan rows gotten 129,526,667 87,164.7 3,332.1 table scans (long tables) 132 0.1 0.0 Instance Activity Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 278,639 187.5 7.2 transaction lock background get t 0 0.0 0.0 transaction lock background gets 0 0.0 0.0 transaction lock foreground reque 0 0.0 0.0 transaction lock foreground wait 0 0.0 0.0 transaction rollbacks 29 0.0 0.0 transaction tables consistent rea 1 0.0 0.0 transaction tables consistent rea 141 0.1 0.0 user calls 186,428 125.5 4.8 user commits 38,770 26.1 1.0 user rollbacks 103 0.1 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 166,257 111.9 4.3 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 41 0.0 0.0 ------------------------------------------------------------- Tablespace IO Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ HSATBL 669,797 451 2.7 2.4 10,027 7 39,062 1.1 HSAINDX 151,978 102 5.3 1.1 67,155 45 7 4.3 TOOLS 37,235 25 3.4 1.5 9,005 6 0 0.0 UNDOTBS1 203 0 3.6 1.0 19,551 13 6 0.0 SYSTEM 756 1 2.8 1.0 95 0 0 0.0 TEMP 4 0 5.0 1.0 14 0 0 0.0 UNDOTBS2 4 0 2.5 1.0 2 0 0 0.0 USERS 2 0 5.0 1.0 2 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ HSAINDX /ora_data1/oradata/PULSEDB/hsaindx04.dbf 28,279 19 5.4 1.1 12,439 8 0 /ora_data1/oradata/PULSEDB/hsaindx05.dbf 38,508 26 5.5 1.1 22,343 15 6 3.3 /ora_data2/oradata/PULSEDB/hsaindx01.dbf 29,017 20 5.1 1.1 11,032 7 0 /ora_data2/oradata/PULSEDB/hsaindx02.dbf 27,494 19 5.1 1.1 10,168 7 0 /ora_data2/oradata/PULSEDB/hsaindx03.dbf 28,680 19 5.1 1.2 11,173 8 1 10.0 HSATBL /ora_data1/oradata/PULSEDB/hsatbl01.dbf 36,839 25 3.9 2.6 347 0 921 1.1 /ora_data1/oradata/PULSEDB/hsatbl02.dbf 38,038 26 3.7 2.8 390 0 1,284 0.7 /ora_data1/oradata/PULSEDB/hsatbl03.dbf 40,670 27 3.4 2.0 360 0 1,560 1.0 /ora_data1/oradata/PULSEDB/hsatbl04.dbf 35,485 24 3.9 2.1 402 0 1,177 0.9 /ora_data1/oradata/PULSEDB/hsatbl05.dbf 36,485 25 3.5 2.0 495 0 1,316 0.9 /ora_data1/oradata/PULSEDB/hsatbl06.dbf 38,524 26 3.6 1.9 418 0 1,138 0.9 /ora_data1/oradata/PULSEDB/hsatbl07.dbf 136,418 92 1.9 2.7 729 0 11,329 1.2 /ora_data1/oradata/PULSEDB/hsatbl08.dbf 117,899 79 2.0 2.5 1,323 1 8,256 1.2 /ora_data1/oradata/PULSEDB/hsatbl09.dbf 139,175 94 1.9 2.6 1,252 1 10,704 1.2 /ora_data1/oradata/PULSEDB/hsatbl10.dbf 50,264 34 4.0 2.0 4,311 3 1,377 0.9 SYSTEM /ora_sys/oradata/PULSEDB/system01.dbf 586 0 2.8 1.0 84 0 0 /ora_sys/oradata/PULSEDB/system02.dbf 170 0 2.6 1.0 11 0 0 TEMP /u02/oradata/PULSEDB/temp02.dbf 4 0 5.0 1.0 14 0 0 TOOLS /ora_temp/oradata/PULSEDB/tools01.dbf 19,591 13 3.4 1.5 7,309 5 0 /ora_temp/oradata/PULSEDB/tools02.dbf 17,644 12 3.3 1.6 1,696 1 0 UNDOTBS1 /ora_undo/oradata/PULSEDB/undotbs01.dbf 203 0 3.6 1.0 19,551 13 6 0.0 UNDOTBS2 /ora_undo/oradata/PULSEDB/undotbs2_01.dbf 4 0 2.5 1.0 2 0 0 USERS /ora_data1/oradata/PULSEDB/users01.dbf File IO Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ 2 0 5.0 1.0 2 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 93,552 96.3 50,469,595 1,844,422 107,131 0 0 22,226 K 1,949 94.3 373,746 21,234 256 0 0 0 ------------------------------------------------------------- Instance Recovery Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> 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 390 77 12434 190472 190076 368640 190076 E 390 87 9785 262297 368640 368640 823171 ------------------------------------------------------------- Buffer Pool Advisory for DB: PULSEDB Instance: PULSEDB1 End Snap: 15662 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate (default block size first) Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 80 .1 9,745 2.17 2,679,852,458 D 160 .2 19,490 1.81 2,236,656,930 D 240 .3 29,235 1.58 1,952,702,664 D 320 .4 38,980 1.36 1,676,253,061 D 400 .5 48,725 1.22 1,512,431,123 D 480 .6 58,470 1.15 1,419,449,647 D 560 .7 68,215 1.09 1,351,542,369 D 640 .8 77,960 1.05 1,299,446,783 D 720 .9 87,705 1.02 1,257,852,789 D 768 1.0 93,552 1.00 1,236,673,772 D 800 1.0 97,450 0.99 1,223,851,950 D 880 1.1 107,195 0.97 1,194,470,261 D 960 1.3 116,940 0.94 1,168,472,817 D 1,040 1.4 126,685 0.93 1,145,493,516 D 1,120 1.5 136,430 0.91 1,125,251,693 D 1,200 1.6 146,175 0.90 1,107,268,047 D 1,280 1.7 155,920 0.88 1,091,095,233 D 1,360 1.8 165,665 0.87 1,075,892,911 D 1,440 1.9 175,410 0.86 1,061,753,457 D 1,520 2.0 185,155 0.85 1,048,539,810 D 1,600 2.1 194,900 0.84 1,033,499,995 K 16 1.0 1,949 1.00 53,974,240 K 32 2.0 3,898 0.90 48,360,039 K 48 3.0 5,847 0.79 42,724,382 K 64 4.0 7,796 0.69 37,061,005 K 80 5.0 9,745 0.57 30,923,628 K 96 6.0 11,694 0.45 24,552,140 K 112 7.0 13,643 0.34 18,339,682 K 128 8.0 15,592 0.24 13,012,824 K 144 9.0 17,541 0.17 9,179,465 K 160 10.0 19,490 0.13 6,775,975 K 176 11.0 21,439 0.10 5,377,249 K 192 12.0 23,388 0.09 4,646,939 K 208 13.0 25,337 0.08 4,303,572 K 224 14.0 27,286 0.08 4,065,421 K 240 15.0 29,235 0.07 3,855,636 K 256 16.0 31,184 0.07 3,659,136 K 272 17.0 33,133 0.06 3,475,525 K 288 18.0 35,082 0.06 3,292,561 K 304 19.0 37,031 0.06 3,111,743 K 320 20.0 38,980 0.05 2,526,249 ------------------------------------------------------------- Buffer wait Statistics for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 39,067 44 1 segment header 2 0 5 undo header 6 0 0 ------------------------------------------------------------- PGA Aggr Target Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 2,169 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 512 313 223.6 6.6 2.9 100.0 .0 26,214 E 512 328 198.8 7.4 3.7 100.0 .0 26,214 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -156 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 164,743 164,743 0 0 16K 32K 269 269 0 0 32K 64K 41 41 0 0 64K 128K 278 278 0 0 128K 256K 9 9 0 0 256K 512K 12 12 0 0 512K 1024K 635 635 0 0 1M 2M 10 10 0 0 2M 4M 8 8 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: PULSEDB Instance: PULSEDB1 End Snap: 15662 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 64 0.1 3,028,743.4 1,408,237.3 68.0 75,589 128 0.3 3,028,743.4 1,012,484.1 75.0 51,217 256 0.5 3,028,743.4 516,144.9 85.0 210 384 0.8 3,028,743.4 463,783.0 87.0 0 512 1.0 3,028,743.4 417,756.2 88.0 0 614 1.2 3,028,743.4 163,067.4 95.0 0 717 1.4 3,028,743.4 160,920.7 95.0 0 819 1.6 3,028,743.4 159,218.3 95.0 0 922 1.8 3,028,743.4 153,763.1 95.0 0 1,024 2.0 3,028,743.4 151,042.6 95.0 0 1,536 3.0 3,028,743.4 143,024.4 95.0 0 2,048 4.0 3,028,743.4 134,198.9 96.0 0 3,072 6.0 3,028,743.4 134,198.9 96.0 0 4,096 8.0 3,028,743.4 134,198.9 96.0 0 ------------------------------------------------------------- Enqueue activity for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> Enqueue stats gathered prior to 9i should not be compared with 9i data -> ordered by Wait Time desc, Waits desc Avg Wt Wait Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s) -- ------------ ------------ ----------- ----------- ------------- ------------ US 112,530 112,530 0 16,143 .57 9 DV 6,464 6,464 0 3,087 .51 2 CF 3,337 3,336 1 577 1.37 1 TA 465 465 0 457 .55 0 TT 242 242 0 236 .61 0 JQ 80 73 7 72 1.44 0 FB 251 251 0 110 .57 0 TX 48,437 48,437 0 17 1.41 0 HW 248 248 0 20 .55 0 WL 2 2 0 2 1.00 0 TM 379,205 379,205 0 1 1.00 0 RO 1 1 0 1 .00 0 SQ 2,886 2,886 0 1 .00 0 ------------------------------------------------------------- Rollback Segment Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 0 467.0 0.00 0 0 0 0 1 1,613.0 0.00 1,172,578 2 0 0 2 1,556.0 0.00 1,031,690 1 0 1 3 1,411.0 0.00 947,384 1 0 1 4 1,550.0 0.00 1,022,768 2 0 1 5 1,733.0 0.06 1,243,054 1 0 1 6 1,752.0 0.00 1,503,148 2 0 0 7 1,501.0 0.00 960,752 1 0 0 8 1,979.0 0.00 2,689,842 4 0 2 9 1,595.0 0.00 1,103,324 1 0 1 10 1,766.0 0.00 1,308,388 2 0 0 27 1,532.0 0.00 978,672 1 0 0 28 1,424.0 0.00 978,268 1 0 1 30 1,767.0 0.00 1,305,694 1 0 1 31 1,563.0 0.00 1,051,640 1 0 1 32 1,524.0 0.00 1,039,700 1 0 0 33 1,411.0 0.00 870,660 1 0 0 34 1,566.0 0.00 1,096,912 1 0 0 37 1,504.0 0.00 964,684 1 0 0 38 1,455.0 0.00 1,124,444 1 0 0 43 1,411.0 0.00 981,158 1 0 0 44 7,116.0 0.00 16,334,998 19 0 19 46 1,585.0 0.00 1,091,048 1 0 1 47 1,544.0 0.00 1,020,138 1 0 0 48 1,401.0 0.00 951,610 1 0 1 49 1,618.0 0.00 1,232,092 1 0 1 50 1,813.0 0.00 1,393,868 1 0 0 51 1,579.0 0.00 1,056,432 1 0 1 52 1,537.0 0.00 1,032,858 3 0 1 53 1,401.0 0.00 853,812 1 0 1 54 1,560.0 0.00 1,092,066 1 0 1 55 1,688.0 0.00 1,117,466 1 0 0 56 1,529.0 0.00 952,194 1 0 0 57 1,513.0 0.00 974,896 1 0 0 58 1,405.0 0.00 927,274 1 0 0 59 1,570.0 0.00 1,044,806 1 0 0 60 1,724.0 0.00 1,330,862 2 0 0 61 1,594.0 0.00 1,172,612 3 0 1 62 1,528.0 0.00 984,276 1 0 0 63 1,393.0 0.00 919,742 1 0 0 64 1,597.0 0.00 1,045,280 1 0 0 65 1,733.0 0.00 1,185,410 2 0 1 66 1,601.0 0.00 1,118,928 1 0 1 67 1,548.0 0.00 1,045,342 1 0 0 68 1,387.0 0.00 936,546 1 0 0 77 1,502.0 0.00 968,174 1 0 0 78 1,464.0 0.07 1,100,524 1 0 1 79 1,521.0 0.00 931,666 1 0 0 80 1,739.0 0.00 1,230,466 1 0 0 81 1,546.0 0.00 1,034,050 1 0 0 82 1,510.0 0.00 990,522 1 0 0 83 1,355.0 0.00 807,812 1 0 0 Rollback Segment Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 84 1,599.0 0.00 1,108,834 1 0 1 85 1,751.0 0.00 1,251,140 1 0 0 86 1,571.0 0.00 1,065,654 1 0 0 87 467.0 0.00 0 0 0 0 197 467.0 0.00 0 0 0 0 198 1,412.0 0.00 963,102 1 0 0 199 1,584.0 0.00 1,105,464 1 0 0 200 1,807.0 0.00 1,370,804 1 0 1 201 1,556.0 0.00 1,043,710 1 0 0 202 4,684.0 0.00 8,708,400 10 0 7 203 1,424.0 0.00 994,146 1 0 0 204 1,597.0 0.00 1,101,472 1 0 0 205 467.0 0.00 0 0 0 0 206 1,594.0 0.00 1,331,882 1 0 1 207 1,516.0 0.00 975,862 1 0 1 208 1,419.0 0.00 946,262 1 0 0 209 1,641.0 0.00 1,205,506 1 0 1 210 1,723.0 0.00 1,214,200 1 0 1 211 1,610.0 0.00 1,139,984 2 0 0 212 1,580.0 0.00 1,168,186 1 0 0 213 1,378.0 0.00 849,436 1 0 0 214 1,590.0 0.00 1,139,670 2 0 0 215 1,705.0 0.00 1,186,886 1 0 0 216 1,567.0 0.00 1,059,766 1 0 0 217 1,541.0 0.00 1,031,602 1 0 0 218 1,442.0 0.00 1,074,994 2 0 0 219 1,626.0 0.00 1,172,928 1 0 0 220 1,780.0 0.00 1,336,106 2 0 2 221 1,646.0 0.00 1,232,414 2 0 1 222 1,492.0 0.00 953,720 1 0 1 223 1,432.0 0.00 965,690 3 0 0 224 1,614.0 0.00 1,118,484 2 0 2 225 1,788.0 0.00 1,372,736 1 0 1 226 1,619.0 0.00 1,202,424 1 0 1 227 1,478.0 0.00 976,514 1 0 0 228 1,449.0 0.00 979,892 1 0 0 229 1,548.0 0.13 1,030,000 1 0 0 230 1,777.0 0.00 1,345,566 2 0 1 231 1,655.0 0.00 1,253,046 1 0 0 232 1,993.0 0.05 4,748,810 5 0 5 233 1,964.0 0.00 2,637,346 3 0 3 234 1,524.0 0.00 960,674 1 0 0 235 1,755.0 0.00 1,257,780 2 0 1 236 1,555.0 0.00 994,682 1 0 1 237 1,522.0 0.00 1,060,426 1 0 1 238 1,420.0 0.00 959,260 1 0 1 239 1,614.0 0.00 1,097,350 1 0 0 240 1,773.0 0.00 1,270,822 2 0 0 241 1,571.0 0.00 1,159,886 1 0 0 242 1,517.0 0.00 928,512 1 0 1 ------------------------------------------------------------- Rollback Segment Storage for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -1566 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 385,024 6,553 385,024 1 12,705,792 1,056,051 626,122,752 2 17,948,672 1,766,928 75,620,352 3 9,560,064 1,050,157 66,183,168 4 29,483,008 6,805,486 4,245,872,640 5 11,657,216 1,029,484 131,260,416 6 10,608,640 1,011,316 1,879,171,072 7 10,608,640 8,724,014 114,417,664 8 13,754,368 1,050,169 151,117,824 9 10,608,640 1,199,773 92,397,568 10 11,657,216 1,062,902 107,208,704 27 9,560,064 4,450,155 117,563,392 28 11,657,216 1,184,071 42,131,456 30 10,608,640 1,360,772 42,065,920 31 49,405,952 14,288,600 49,405,952 32 9,560,064 1,042,348 84,008,960 33 9,560,064 1,000,018 838,983,680 34 11,657,216 1,912,577 122,806,272 37 12,705,792 1,178,736 125,952,000 38 8,511,488 874,522 788,652,032 43 9,560,064 1,039,698 24,240,128 44 44,163,072 19,439,858 50,454,528 46 12,705,792 1,228,713 109,174,784 47 11,657,216 2,151,722 60,940,288 48 10,608,640 983,380 63,037,440 49 10,608,640 999,801 64,086,016 50 11,657,216 1,212,725 48,357,376 51 21,094,400 1,482,028 36,823,040 52 9,560,064 900,959 34,725,888 53 7,462,912 1,012,945 53,600,256 54 10,608,640 1,057,381 58,843,136 55 11,657,216 981,551 52,551,680 56 9,560,064 1,009,620 54,648,832 57 10,608,640 1,167,007 56,745,984 58 9,560,064 1,051,640 36,823,040 59 10,608,640 1,014,628 181,526,528 60 13,754,368 1,182,639 34,725,888 61 15,851,520 1,110,867 276,946,944 62 12,705,792 1,130,434 58,843,136 63 8,511,488 1,074,342 67,231,744 64 8,511,488 1,060,765 41,017,344 65 13,754,368 1,282,925 41,017,344 66 12,705,792 938,983 721,543,168 67 10,608,640 1,116,756 27,385,856 68 11,657,216 1,066,165 55,762,944 77 9,560,064 1,060,944 89,251,840 78 17,948,672 3,240,585 23,191,552 79 14,802,944 1,301,923 30,531,584 80 10,608,640 1,016,093 28,434,432 81 9,560,064 1,038,428 38,920,192 82 11,657,216 1,018,560 24,305,664 83 13,754,368 1,244,143 25,288,704 84 9,560,064 7,578,270 56,745,984 85 11,657,216 2,825,437 36,823,040 86 9,560,064 1,010,603 235,003,904 Rollback Segment Storage for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -1566 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 87 6,414,336 1,292,490 38,920,192 197 6,414,336 819,151 39,968,768 198 8,511,488 983,214 48,422,912 199 9,560,064 1,009,320 32,628,736 200 10,608,640 5,094,727 49,405,952 201 11,657,216 1,021,713 27,385,856 202 17,948,672 3,394,828 84,008,960 203 9,560,064 875,673 36,823,040 204 13,754,368 1,115,845 427,941,888 205 4,317,184 1,530,112 31,580,160 206 11,657,216 1,031,047 109,174,784 207 9,560,064 1,028,465 34,725,888 208 10,608,640 1,121,718 29,483,008 209 10,608,640 2,521,002 49,405,952 210 11,657,216 1,016,104 58,843,136 211 9,560,064 1,053,272 26,337,280 212 8,511,488 983,225 44,163,072 213 8,511,488 1,068,981 31,580,160 214 10,608,640 1,063,744 33,677,312 215 12,705,792 1,003,384 49,471,488 216 10,608,640 987,415 26,337,280 217 13,754,368 1,150,120 55,697,408 218 9,560,064 1,016,289 26,337,280 219 12,705,792 2,589,060 75,620,352 220 11,657,216 1,543,842 25,288,704 221 34,725,888 9,648,013 34,725,888 222 10,608,640 1,135,992 29,483,008 223 8,577,024 779,214 41,017,344 224 17,948,672 1,936,750 84,008,960 225 10,608,640 987,219 28,434,432 226 10,608,640 1,028,280 296,869,888 227 9,560,064 924,710 47,308,800 228 9,560,064 1,763,287 31,580,160 229 11,657,216 1,095,511 117,563,392 230 18,997,248 2,032,088 35,774,464 231 10,608,640 1,398,412 31,580,160 232 22,142,976 2,219,669 46,260,224 233 12,705,792 1,153,385 761,389,056 234 14,802,944 1,097,842 57,794,560 235 15,851,520 1,344,013 47,308,800 236 10,608,640 1,061,930 289,529,856 237 11,657,216 1,310,531 28,434,432 238 20,045,824 3,455,274 20,045,824 239 11,657,216 1,264,109 32,628,736 240 12,705,792 1,063,333 39,968,768 241 12,705,792 1,136,398 31,580,160 242 9,560,064 1,084,130 28,434,432 ------------------------------------------------------------- Undo Segment Summary for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU ---- -------------- ---------- -------- ---------- -------- ------ ------------- 1 21,177 ########## 629 5 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> ordered by Time desc Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU ------------ ------------ -------- ------- -------- ------- ------ ------------- 19-Dec 16:41 3,589 ######## 629 4 0 0 0/0/0/0/0/0 19-Dec 16:31 10,317 ######## 478 5 0 0 0/0/0/0/0/0 19-Dec 16:21 7,271 ######## 428 4 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Consistent RBA 39,054 0.0 0.0 0 0 FAL request queue 4 0.0 0 0 FIB s.o chain latch 28 0.0 0 0 FOB s.o list latch 2,012 0.0 0 0 KCL bast context freelis 15,394 0.0 0 0 KCL freelist parent latc 5,845,972 0.2 0.0 0 0 KCL gc element parent la 9,219,155 0.1 0.0 0 1,770,296 0.8 KCL name table parent la 2,102,228 0.0 0.0 0 1,728,233 0.0 KJC message pool free li 665,004 0.1 0.0 0 186,713 0.0 KJCT flow control latch 2,307,687 0.1 0.0 0 234,772 0.0 LGWR NS Write 269,444 0.0 0.0 0 0 SQL memory manager latch 2 0.0 0 494 0.0 SQL memory manager worka 86,064 0.0 0 0 active checkpoint queue 10,533 0.1 0.0 0 0 alert log latch 16 0.0 0 0 archive control 66 0.0 0 0 archive process latch 72 0.0 0 0 cache buffer handles 101,691 0.0 0.0 0 0 cache buffers chains 106,167,816 0.0 0.1 0 5,933,306 0.0 cache buffers lru chain 219,632 0.1 0.0 0 4,432,182 0.3 channel handle pool latc 494 0.0 0 0 channel operations paren 539,838 0.0 0.0 0 0 checkpoint queue latch 655,855 0.0 0.0 0 111,824 0.0 child cursor hash table 7,001 0.0 0 0 commit callback allocati 655 0.0 0 0 dictionary lookup 1 0.0 0 0 dml lock allocation 657,220 0.0 0.0 0 0 dummy allocation 296 0.0 0 0 enqueue hash chains 2,111,098 0.0 0.0 0 0 enqueues 281,902 0.0 0.0 0 0 event group latch 69 0.0 0 0 gcs opaque info freelist 414,092 0.2 0.0 0 0 gcs resource freelist 3,203,686 0.3 0.0 0 0 gcs resource hash 6,546,257 0.0 0.0 0 52 0.0 gcs resource scan list 104 0.0 0 0 gcs shadows freelist 1,996,305 0.2 0.0 0 0 ges caches resource list 590,288 0.5 0.0 0 527,377 0.8 ges deadlock list 52,224 0.0 0.0 0 4 0.0 ges domain table 995,338 0.0 0.0 0 0 ges enqueue table freeli 1,481,121 0.0 0.0 0 0 ges group parent 996,109 0.0 0.0 0 0 ges group table 1,098,283 0.0 0.0 0 0 ges process hash list 149,047 0.0 0 0 ges process parent latch 4,617,118 0.0 0.0 0 0 ges process table freeli 156 0.0 0 0 ges resource hash list 2,977,931 0.0 0.5 0 327,815 0.0 ges resource scan list 18 0.0 0 0 ges resource table freel 985,889 0.0 0.0 0 0 ges timeout list 8,624 0.0 0 849 0.0 hash table column usage 158 0.0 0 38,119 0.0 Latch Activity for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ job workq parent latch 6 100.0 0.0 0 142 41.5 job_queue_processes para 81 0.0 0 0 ksxp tid allocation 1,033,526 0.0 0.0 0 0 ktm global data 465 0.0 0 0 kwqit: protect wakeup ti 64 0.0 0 0 lgwr LWN SCN 39,102 0.0 0.0 0 0 library cache 4,328,158 0.2 0.0 0 554 4.0 library cache load lock 328 0.0 0 0 library cache pin 3,396,011 0.0 0.0 0 0 library cache pin alloca 688,439 0.0 0.0 0 0 list of block allocation 20,349 0.0 0 0 loader state object free 84 0.0 0 0 longop free list parent 30 0.0 0 31 0.0 message pool operations 247,770 0.0 0 0 messages 209,699 0.0 0.0 0 0 mostly latch-free SCN 39,228 0.1 0.0 0 0 multiblock read objects 766,184 0.1 0.0 0 0 name-service memory obje 9,366 0.0 0 0 name-service namespace b 17,656 0.0 0 0 name-service pending que 4,366 0.0 0 0 name-service request que 20,853 0.0 0 0 ncodef allocation latch 23 0.0 0 0 object stats modificatio 2 0.0 0 0 post/wait queue 20,487 0.2 0.0 0 4,983 0.0 process allocation 13,042 0.0 0.0 0 68 1.5 process group creation 156 0.0 0 0 redo allocation 1,198,253 0.0 0.0 0 0 redo copy 0 0 1,120,274 0.0 redo writing 128,713 0.0 0.0 0 0 row cache enqueue latch 1,014,386 0.0 0.0 0 0 row cache objects 1,057,302 0.0 0.0 0 39 0.0 sequence cache 277,039 0.0 0.0 0 0 session allocation 378,228 0.0 0.0 0 0 session idle bit 401,716 0.0 0.0 0 0 session switching 23 0.0 0 0 session timer 498 0.0 0 0 shared pool 2,103,314 0.1 0.0 0 0 sim partition latch 0 0 474 0.0 simulator hash latch 3,041,961 0.0 0.0 0 0 simulator lru latch 51,801 0.0 0.1 0 147,277 0.0 sort extent pool 1,428 0.0 0 0 spilled msgs queues list 56 0.0 0 0 temp lob duration state 5 0.0 0 0 temporary table state ob 13 0.0 0 0 transaction allocation 591 0.0 0 0 transaction branch alloc 23 0.0 0 0 undo global data 363,650 0.0 0.0 0 0 user lock 250 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ KCL gc element parent latc 9,219,155 13,209 35 13176/31/2/0 /0 KCL freelist parent latch 5,845,972 12,723 30 12696/24/3/0 /0 cache buffers chains 106,167,816 10,961 992 0/0/0/0/0 gcs resource freelist 3,203,686 9,186 10 9176/10/0/0/ 0 library cache 4,328,158 6,505 128 6377/128/0/0 /0 gcs shadows freelist 1,996,305 4,474 2 4472/2/0/0/0 ges caches resource lists 590,288 2,868 1 2867/1/0/0/0 shared pool 2,103,314 2,345 37 2310/33/2/0/ 0 gcs resource hash 6,546,257 1,677 15 1662/15/0/0/ 0 KJCT flow control latch 2,307,687 1,204 4 1200/4/0/0/0 gcs opaque info freelist 414,092 684 2 682/2/0/0/0 ges resource hash list 2,977,931 536 248 288/248/0/0/ 0 multiblock read objects 766,184 409 4 405/4/0/0/0 KCL name table parent latc 2,102,228 288 4 285/2/1/0/0 row cache objects 1,057,302 282 1 281/1/0/0/0 cache buffers lru chain 219,632 246 2 244/2/0/0/0 ksxp tid allocation 1,033,526 144 1 143/1/0/0/0 session allocation 378,228 53 1 52/1/0/0/0 simulator lru latch 51,801 10 1 9/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- KCL freelist parent latc kclfget 0 15 9 KCL freelist parent latc kclfrem 0 15 12 KCL gc element parent la kclfget 0 13 0 KCL gc element parent la kclpdc_2 0 6 0 KCL gc element parent la kclulb 0 5 11 KCL gc element parent la KCLUNLNK 0 4 6 KCL gc element parent la kclnloc 0 4 1 KCL gc element parent la kcllkop 0 1 0 KCL gc element parent la kclwrt 0 1 0 KCL gc element parent la kclrwrite 0 1 1 KCL name table parent la kclnfnd 0 4 4 KJCT flow control latch kjcts_sedeqv: dequeue a ve 0 3 0 KJCT flow control latch kjctcsnd: send normal mess 0 1 3 cache buffers chains kcbgtcr: kslbegin excl 0 698 664 cache buffers chains kcbzgb: scan from tail. no 0 61 0 cache buffers chains kcbrls: kslbegin 0 59 175 cache buffers chains kcbzib: multi-block read: 0 39 0 cache buffers chains kcbchg: kslbegin: bufs not 0 33 10 cache buffers chains kcbget: pin buffer 0 19 4 cache buffers chains kcbgtcr: fast path 0 18 23 cache buffers chains kcbgtcr: kslbegin shared 0 12 2 cache buffers chains kcbgtcr 0 11 0 cache buffers chains kcbgcur: kslbegin 0 9 8 cache buffers chains kcbnew 0 7 0 cache buffers chains kclpdcl 0 7 52 cache buffers chains kcbzwb 0 6 10 cache buffers chains kclbla 0 4 0 cache buffers chains kcbzib: finish free bufs 0 3 20 cache buffers chains kcbzsc 0 3 0 cache buffers chains kclpred 0 2 0 cache buffers chains kclgrantlk_1 0 1 2 cache buffers lru chain kcbzgb: multiple sets nowa 6,797 2 0 gcs opaque info freelist kjbrcvt save opaque info 0 1 0 gcs opaque info freelist kjbrgrant free opaque info 0 1 2 gcs resource freelist kjbralc add resource 0 8 7 gcs resource freelist kjbrfr free resource 0 2 3 gcs resource hash kclpdc 0 5 10 gcs resource hash kjbcropen 0 5 3 gcs resource hash kclgrantlk_1 0 2 0 gcs resource hash kjbassume 0 2 1 gcs resource hash kjbmpopen 0 1 1 gcs shadows freelist kjblalc add lock 0 2 2 ges resource hash list kjakcai: search for resp b 0 129 1 ges resource hash list kjlmfnd: search for lockp 0 118 0 ges resource hash list kjrmas1: lookup master nod 0 1 0 ges resource table freel kjrm2c: move resource to c 0 1 1 ksxp tid allocation ksxptidcre 0 1 1 library cache kglic 0 39 0 library cache kgllkdl: child: cleanup 0 33 0 library cache kglpndl: child: before pro 0 14 9 library cache kglpnc: child 0 9 48 library cache kglupc: child 0 9 42 library cache kglpin: child: heap proces 0 4 0 Latch Miss Sources for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- library cache kglhdgc: child: 0 3 1 library cache kglpnp: child 0 3 3 library cache kglobpn: child: 0 3 2 library cache kgldte: child 0 0 1 3 library cache kglpndl: child: after proc 0 1 0 library cache kglpin 0 1 0 library cache kglhdgn: child: 0 1 15 library cache kglget: child: KGLDSBRD 0 1 3 multiblock read objects kcbzib: mbr get 0 2 0 multiblock read objects kcbzib: normal mbr free 0 2 4 row cache objects kqrpfl: not dirty 0 1 0 session allocation ksufap: active sessions 0 1 0 shared pool kghupr1 0 14 36 shared pool kghalo 0 13 1 shared pool kghfrunp: alloc: wait 0 10 0 shared pool kghfrunp: clatch: nowait 0 5 0 shared pool kghfrunp: clatch: wait 0 1 0 simulator lru latch kcbs_simulate: simulate se 0 1 1 ------------------------------------------------------------- Dictionary Cache Stats for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_global_oids 11,463 0.0 0 0 37 dc_histogram_defs 4,016 3.8 0 3 3,414 dc_object_ids 28,403 0.0 0 0 1,231 dc_objects 2,592 0.5 0 1 2,090 dc_profiles 88 0.0 0 0 1 dc_rollback_segments 230,932 0.0 0 0 244 dc_segments 9,004 0.0 0 10 1,585 dc_sequences 3,146 5.8 0 3,146 30 dc_tablespace_quotas 38 5.3 0 11 7 dc_tablespaces 10,741 0.0 0 0 8 dc_user_grants 195 0.0 0 0 31 dc_usernames 873 0.0 0 0 23 dc_users 204,663 0.0 0 0 38 ------------------------------------------------------------- GES GES GES Cache Requests Conflicts Releases ------------------------- ------------ ------------ ------------ dc_global_oids 0 0 0 dc_histogram_defs 160 0 15 dc_object_ids 4 0 2 dc_objects 16 0 2 dc_profiles 0 0 0 dc_rollback_segments 0 0 0 dc_segments 22 0 6 dc_sequences 6,290 190 1 dc_tablespace_quotas 23 0 0 dc_tablespaces 0 0 0 dc_user_grants 0 0 0 dc_usernames 0 0 0 dc_users 0 0 0 ------------------------------------------------------------- Library Cache Activity for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 2,048 0.0 2,050 0.0 0 0 CLUSTER 52 0.0 59 0.0 0 0 INDEX 448 0.0 448 0.0 0 0 SQL AREA 20,170 0.2 1,499,034 0.1 816 8 TABLE/PROCEDURE 58,624 0.0 196,350 0.1 115 0 TRIGGER 1,294 0.0 1,294 0.5 7 0 ------------------------------------------------------------- GES Lock GES Pin GES Pin GES Inval GES Invali- Namespace Requests Requests Releases Requests dations --------------- ------------ ------------ ------------ ----------- ----------- BODY 1 0 0 0 0 CLUSTER 38 0 0 0 0 INDEX 448 0 0 0 0 SQL AREA 0 0 0 0 0 TABLE/PROCEDURE 25,414 19 67 6 0 TRIGGER 0 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: PULSEDB Instance: PULSEDB1 End Snap: 15662 -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 128 .5 129 20,695 5,208,690 1.0 610,400,332 160 .6 160 24,299 5,209,194 1.0 610,480,278 192 .8 191 28,600 5,209,539 1.0 610,548,612 224 .9 222 31,382 5,209,783 1.0 610,604,551 256 1.0 253 35,083 5,209,907 1.0 610,634,946 288 1.1 285 38,420 5,209,983 1.0 610,656,499 320 1.3 316 44,253 5,210,050 1.0 610,678,266 352 1.4 347 47,400 5,210,099 1.0 610,694,036 384 1.5 378 50,404 5,210,151 1.0 610,712,976 416 1.6 409 53,545 5,210,199 1.0 610,735,270 448 1.8 440 57,565 5,210,235 1.0 610,749,787 480 1.9 471 60,942 5,210,262 1.0 610,759,822 512 2.0 503 65,005 5,210,293 1.0 610,772,222 ------------------------------------------------------------- SGA Memory Summary for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 822,083,584 Fixed Size 453,708 Redo Buffers 5,386,240 Variable Size 1,006,632,960 ---------------- sum 1,834,556,492 ------------------------------------------------------------- SGA breakdown difference for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -1566 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 79,314,944 79,314,944 0.00 java memory in use 4,571,136 4,571,136 0.00 large free memory 536,870,912 536,870,912 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 4,220,168 4,397,176 4.19 shared KQR M PO 3,800,108 3,800,108 0.00 shared KQR S PO 604,732 604,732 0.00 shared KQR S SO 8,708 8,708 0.00 shared KQR X PO 36,488 36,488 0.00 shared MTTR advisory 167,960 167,960 0.00 shared PL/SQL DIANA 5,387,784 5,771,048 7.11 shared PL/SQL MPCODE 10,175,720 10,192,040 0.16 shared PL/SQL PPCODE 675,732 675,732 0.00 shared PLS non-lib hp 2,068 2,068 0.00 shared dictionary cache 1,614,976 1,614,976 0.00 shared errors 145,988 145,988 0.00 shared event statistics per sess 22,624,000 22,624,000 0.00 shared fixed allocation callback 280 280 0.00 shared free memory 58,612,616 56,213,544 -4.09 shared gcs resources 9,312,868 9,312,868 0.00 shared gcs shadows 6,791,524 6,791,524 0.00 shared ges enqueues 13,507,060 13,507,060 0.00 shared joxlod: in ehe 286,160 286,160 0.00 shared joxlod: in phe 2,672 2,672 0.00 shared joxlod: init s 856 856 0.00 shared joxs heap init 292 292 0.00 shared library cache 53,877,160 54,049,364 0.32 shared miscellaneous 88,464,284 88,159,772 -0.34 shared parameters 341,776 342,348 0.17 shared rule set evalu 36,056 36,056 0.00 shared sim memory hea 524,412 524,412 0.00 shared sql area 104,481,456 106,442,044 1.88 shared subscription : 796 796 0.00 shared table definiti 6,684 8,284 23.94 shared temporary tabl 29,800 9,572 -67.88 shared trigger defini 41,508 53,764 29.53 shared trigger inform 1,236 1,236 0.00 shared trigger source 1,776 1,776 0.00 shared type object de 88,556 88,556 0.00 buffer_cache 822,083,584 822,083,584 0.00 fixed_sga 453,708 453,708 0.00 log_buffer 5,374,976 5,374,976 0.00 ------------------------------------------------------------- Resource Limit Stats for DB: PULSEDB Instance: PULSEDB1 End Snap: 15662 -> only rows with Current or Maximum Utilization > 80% of Limit are shown -> ordered by resource name Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- gcs_resources 78,929 98,805 105051 105051 ------------------------------------------------------------- init.ora Parameters for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 archive_lag_target 1800 background_dump_dest /app/oracle/admin/PULSEDB/bdump/ cluster_database TRUE cluster_database_instances 2 compatible 9.2.0.0.0 control_files /ora_undo/oradata/PULSEDB/control core_dump_dest /app/oracle/admin/PULSEDB/cdump cursor_sharing SIMILAR db_block_size 8192 db_cache_advice ON db_cache_size 805306368 db_domain hsa.co.uk db_file_multiblock_read_count 16 db_keep_cache_size 16777216 db_name PULSEDB db_writer_processes 4 enqueue_resources 3000 event 10511 trace name context forever, fast_start_mttr_target 1800 fast_start_parallel_rollback HIGH hash_join_enabled TRUE instance_name PULSEDB1 instance_number 1 java_pool_size 83886080 job_queue_processes 20 large_pool_size 536870912 local_listener LISTENER_PULSEDB1 log_archive_dest log_archive_dest_1 LOCATION=/ora_data2/arch/PULSEDB log_archive_dest_2 SERVICE=PULSEDG LGWR ASYNC=20480 log_archive_dest_3 log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE log_archive_dest_state_3 DEFER log_archive_format arch%t_%s.log log_archive_max_processes 8 log_archive_start TRUE log_buffer 5242880 open_cursors 5000 optimizer_index_caching 0 optimizer_index_cost_adj 80 parallel_automatic_tuning FALSE parallel_max_servers 0 parallel_min_percent 0 parallel_min_servers 0 pga_aggregate_target 536870912 processes 1500 query_rewrite_enabled TRUE query_rewrite_integrity TRUSTED remote_listener LISTENER_PULSEDB2 remote_login_passwordfile EXCLUSIVE sessions 2000 shared_pool_size 268435456 sort_area_size 0 init.ora Parameters for DB: PULSEDB Instance: PULSEDB1 Snaps: 15660 -15662 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- spfile /ora_sys/oradata/PULSEDB/spfilePU star_transformation_enabled FALSE thread 1 timed_statistics TRUE undo_management AUTO undo_retention 32400 undo_tablespace UNDOTBS1 user_dump_dest /app/oracle/admin/PULSEDB/udump ------------------------------------------------------------- End of Report