WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ DPAMPL 3038483284 dpampl 1 10.2.0.1.0 NO INMUMBA01 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 13290 20-Apr-11 08:31:04 52 1.9 End Snap: 13297 20-Apr-11 15:30:11 61 8.0 Elapsed: 419.12 (mins) DB Time: 155.28 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 824M 816M Std Block Size: 8K Shared Pool Size: 164M 172M Log Buffer: 4,160K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 10,405.16 18,162.19 Logical reads: 687.92 1,200.77 Block changes: 98.63 172.17 Physical reads: 0.22 0.39 Physical writes: 1.23 2.15 User calls: 14.85 25.91 Parses: 33.64 58.72 Hard parses: 1.39 2.43 Sorts: 4.71 8.21 Logons: 0.19 0.32 Executes: 49.21 85.90 Transactions: 0.57 % Blocks changed per Read: 14.34 Recursive Call %: 91.63 Rollback per transaction %: 5.40 Rows per Sort: 814.77 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.97 In-memory Sort %: 100.00 Library Hit %: 97.39 Soft Parse %: 95.87 Execute to Parse %: 31.64 Latch Hit %: 99.64 Parse CPU to Parse Elapsd %: 64.22 % Non-Parse CPU: 75.71 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 97.62 95.83 % SQL with executions>1: 79.69 77.86 % Memory for SQL w/exec>1: 60.32 79.45 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- log file sync 15,472 8,039 520 86.3 Commit LGWR-LNS wait on channel 185,072 2,756 15 29.6 Other LGWR wait on LNS 6,776 2,749 406 29.5 Network LNS wait on SENDREQ 6,767 2,748 406 29.5 Network ARCH wait on SENDREQ 481 641 1333 6.9 Network ------------------------------------------------------------- Time Model Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total time in database user-calls (DB Time): 9316.6s -> Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time 7,596.7 81.5 DB CPU 523.6 5.6 Java execution elapsed time 317.7 3.4 parse time elapsed 250.4 2.7 hard parse elapsed time 211.3 2.3 connection management call elapsed time 100.0 1.1 PL/SQL execution elapsed time 63.3 .7 sequence load elapsed time 55.3 .6 hard parse (sharing criteria) elapsed time 30.2 .3 PL/SQL compilation elapsed time 8.8 .1 hard parse (bind mismatch) elapsed time 1.1 .0 failed parse elapsed time 0.3 .0 repeated bind elapsed time 0.0 .0 DB time 9,316.6 N/A background elapsed time 7,309.7 N/A background cpu time 41.1 N/A ------------------------------------------------------------- Wait Class DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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 Avg %Time Total Wait wait Waits Wait Class Waits -outs Time (s) (ms) /txn -------------------- ---------------- ------ ---------------- ------- --------- Commit 15,472 42.2 8,039 520 1.1 Network 453,747 .0 6,144 14 31.5 Other 185,572 95.2 2,957 16 12.9 System I/O 66,420 .0 652 10 4.6 Configuration 451 87.4 421 933 0.0 Application 229 10.5 77 334 0.0 User I/O 4,880 .0 56 11 0.3 Concurrency 443 2.0 28 64 0.0 ------------------------------------------------------------- Wait Events DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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 %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- log file sync 15,472 42.2 8,039 520 1.1 LGWR-LNS wait on channel 185,072 95.4 2,756 15 12.8 LGWR wait on LNS 6,776 .1 2,749 406 0.5 LNS wait on SENDREQ 6,767 .0 2,748 406 0.5 ARCH wait on SENDREQ 481 .0 641 1333 0.0 log buffer space 412 92.0 396 961 0.0 control file parallel write 8,562 .0 261 30 0.6 log file parallel write 10,931 .0 251 23 0.8 Streams AQ: qmn coordinator 38 100.0 190 4992 0.0 db file parallel write 9,550 .0 124 13 0.7 local write wait 620 .0 42 68 0.0 enq: RO - fast object reuse 194 4.1 41 209 0.0 enq: UL - contention 25 64.0 36 1437 0.0 row cache lock 10 90.0 27 2718 0.0 log file switch completion 35 34.3 25 712 0.0 db file sequential read 3,626 .0 13 3 0.3 Log archive I/O 390 .0 9 23 0.0 kksfbc child completion 163 94.5 9 54 0.0 control file sequential read 36,619 .0 5 0 2.5 ARCH wait for netserver star 1 100.0 4 3992 0.0 log file sequential read 348 .0 2 7 0.0 enq: CF - contention 5 .0 2 407 0.0 ARCH wait on ATTACH 3 .0 1 224 0.0 os thread startup 65 .0 1 9 0.0 library cache pin 218 .0 0 2 0.0 db file scattered read 138 .0 0 3 0.0 direct path write temp 48 .0 0 6 0.0 SQL*Net message to client 379,961 .0 0 0 26.4 log file single write 20 .0 0 13 0.0 direct path write 168 .0 0 1 0.0 direct path read 231 .0 0 1 0.0 SGA: allocation forcing comp 19 21.1 0 8 0.0 SQL*Net more data to client 3,847 .0 0 0 0.3 LNS wait on ATTACH 1 .0 0 146 0.0 LNS wait on LGWR 55,070 .0 0 0 3.8 direct path read temp 48 .0 0 2 0.0 latch free 9 .0 0 5 0.0 SQL*Net more data from clien 836 .0 0 0 0.1 buffer exterminate 1 .0 0 25 0.0 ARCH wait on DETACH 3 .0 0 6 0.0 reliable message 186 .0 0 0 0.0 library cache load lock 15 .0 0 1 0.0 latch: shared pool 29 .0 0 0 0.0 rdbms ipc reply 32 .0 0 0 0.0 read by other session 1 .0 0 1 0.0 LGWR wait for redo copy 47 .0 0 0 0.0 buffer busy waits 11 .0 0 0 0.0 latch: cache buffers chains 18 .0 0 0 0.0 SQL*Net break/reset to clien 10 .0 0 0 0.0 latch: library cache 4 .0 0 0 0.0 cursor: mutex X 73 .0 0 0 0.0 undo segment extension 4 75.0 0 0 0.0 LNS wait on DETACH 1 .0 0 0 0.0 SQL*Net message from client 379,953 .0 147,147 387 26.4 Streams AQ: qmn slave idle w 24,339 .4 115,249 4735 1.7 virtual circuit status 838 100.0 25,042 29883 0.1 Streams AQ: qmn coordinator 9,747 46.3 24,942 2559 0.7 Streams AQ: waiting for time 397 62.5 23,871 60129 0.0 pipe get 409 87.5 1,709 4179 0.0 jobq slave wait 284 99.6 849 2990 0.0 Wait Events DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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 %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- class slave wait 49 100.0 245 4991 0.0 SGA: MMAN sleep for componen 334 87.1 5 14 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- events in waitclass Other 185,187 95.3 2,948 16 12.9 LGWR wait on LNS 6,776 .1 2,749 406 0.5 ARCH wait on SENDREQ 481 .0 641 1333 0.0 control file parallel write 8,562 .0 261 30 0.6 log file parallel write 10,931 .0 251 23 0.8 db file parallel write 9,550 .0 124 13 0.7 log buffer space 67 88.1 62 924 0.0 Log archive I/O 390 .0 9 23 0.0 log file switch completion 5 80.0 4 900 0.0 control file sequential read 30,127 .0 4 0 2.1 ARCH wait for netserver star 1 100.0 4 3992 0.0 log file sequential read 348 .0 2 7 0.0 db file sequential read 790 .0 1 2 0.1 ARCH wait on ATTACH 3 .0 1 224 0.0 os thread startup 65 .0 1 9 0.0 log file single write 20 .0 0 13 0.0 direct path write 35 .0 0 5 0.0 ARCH wait on DETACH 3 .0 0 6 0.0 direct path read 35 .0 0 0 0.0 rdbms ipc message 59,220 128.1 918,632 15512 4.1 pmon timer 8,733 100.0 25,134 2878 0.6 Streams AQ: qmn coordinator 9,747 46.3 24,942 2559 0.7 smon timer 727 2.1 24,358 33505 0.1 Streams AQ: waiting for time 397 62.5 23,871 60129 0.0 SGA: MMAN sleep for componen 334 87.1 5 14 0.0 ------------------------------------------------------------- Operating System Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME 16,634 AVG_IDLE_TIME 2,496,666 AVG_SYS_TIME #################### AVG_USER_TIME 8,731 BUSY_TIME 138,990 IDLE_TIME 19,979,474 SYS_TIME 64,184 USER_TIME 74,806 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 174,223,360 VM_OUT_BYTES 301,989,888 PHYSICAL_MEMORY_BYTES 8,579,002,368 NUM_CPUS 8 NUM_CPU_CORES 2 ------------------------------------------------------------- Service Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- dpampl 8,962.8 390.1 2,761 ########## SYS$USERS 353.8 133.5 1,777 428,508 SYS$BACKGROUND 0.0 0.0 1,026 248,130 dpamplXDB 0.0 0.0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Wait Class info for services in the Service Statistics section. -> Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network -> Time Waited (Wt Time) in centisecond (100th of a second) Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- dpampl 1391 1150 376 2770 0 0 370659 47 SYS$USERS 2246 4182 1 0 0 0 216 0 SYS$BACKGROUND 1243 262 95397 58 0 0 69103 614361 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- 5,699 127 2,105 2.7 61.2 6ybtgzh74j70z Module: QUANTISV4.exe begin p_user_alerts; end; 247 111 38 6.5 2.6 2p6p6c9b3j0pk BEGIN LMS_PKG_OL_PROCESS_HANDLER.LMS_PR_ORDER_HANDLER(:1,:2,:3); END; 208 1 13 16.0 2.2 a2t1yua71v2qw Module: QUANTISV4.exe begin LMS.LMS_PKG_OL_PROCESS_HANDLER.P_LMS_EVALUATE_REQUEST(p_request_id=>:p_req uest_id, p_user_code=>:p_user_code, p_machine_id=>:p_machine_id, p_response_data =>:p_response_data, p_error_message=>:p_error_message, p_final_action=>:p_final_ action); end; 54 0 26 2.1 0.6 9qyw36pt68nsc Module: Quantis.Utilities.Service.exe Select ut_seq_process_log.nextval from dual 42 40 735 0.1 0.4 9czd590q2180z Module: QUANTISV4.exe SELECT DS_VALUEDATE FROM V_QRY_DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUN D_ID=:B1 AND DS_REVERSAL_FLAG='N' AND DS_INPUT_DATE=(SELECT TO_CHAR(MAX(TO_DATE( DS_INPUT_DATE,'DD-MON-YYYY HH:MI:SS AM')),'DD-MON-YYYY HH:MI:SS AM') FROM V_QRY_ DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUND_ID=:B1 AND DS_REVERSAL_FLAG=' 39 2 22 1.8 0.4 g9hqcfw3vqa5f Module: QUANTISV4.exe begin PK_DBMS_ALERTS.P_RECIEVEALERTS(P_AA_APP_TYPE=>:P_AA_APP_TYPE, P_ACTION=>:P _ACTION); end; 38 0 1 37.9 0.4 02gqpdxa1b5rd Module: Quantis.Utilities.Service.exe begin UT_PKG_PROCESS_EXECUTIONS.P_UPLOAD_MD_MIBOR_BCHK_EXCN; end; 32 0 646 0.0 0.3 f5ab5n6gpy6qf Module: QUANTISV4.exe DELETE FROM USER_ALERTS 29 0 1 28.6 0.3 3hqf9m6n8yjhg Module: Quantis.Utilities.Service.exe DELETE FROM UT_GT_STAGING STAGE WHERE STAGE.C IS NULL 28 0 3,584 0.0 0.3 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache= :7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 127 5,699 2,105 0.06 61.2 6ybtgzh74j70z Module: QUANTISV4.exe begin p_user_alerts; end; 111 247 38 2.91 2.6 2p6p6c9b3j0pk BEGIN LMS_PKG_OL_PROCESS_HANDLER.LMS_PR_ORDER_HANDLER(:1,:2,:3); END; 40 42 735 0.05 0.4 9czd590q2180z Module: QUANTISV4.exe SELECT DS_VALUEDATE FROM V_QRY_DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUN D_ID=:B1 AND DS_REVERSAL_FLAG='N' AND DS_INPUT_DATE=(SELECT TO_CHAR(MAX(TO_DATE( DS_INPUT_DATE,'DD-MON-YYYY HH:MI:SS AM')),'DD-MON-YYYY HH:MI:SS AM') FROM V_QRY_ DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUND_ID=:B1 AND DS_REVERSAL_FLAG=' 16 21 84 0.19 0.2 3g8y4xy9txrrj Module: Quantis.Utilities.Service.exe begin UT_PKG_PROCESS_EXECUTIONS.P_DOWNLOAD_CITY_DEALSLIPFILE(P_LOGSYSCODE=>:P_LO GSYSCODE, P_FROMDATE=>:P_FROMDATE, P_TODATE=>:P_TODATE); end; 16 16 8,168 0.00 0.2 5a58p4u5f75aj Module: QUANTISV4.exe begin P_GET_ISM_QUANTITY(:V00001,:V00002,:V00003,:V00004,:V00005,:V00006); end; 11 14 2,008 0.01 0.2 59b2sa6wzmbby Module: QUANTISV4.exe begin p_ca_alerts; end; 8 8 8 0.98 0.1 5sfyp6xna66mk Module: QUANTISV4.exe SELECT FD_VALUEDATE FROM V_QRY_FD_DEALSUMMARY WHERE FD_SECURITY_CODE=:B2 AND FD_ FUND_ID= :B1 AND FD_REVERSAL_FLAG='N' AND FD_INPUT_DATE=(SELECT TO_CHAR(MAX(TO_D ATE(FD_INPUT_DATE,'DD-MON-YYYY HH:MI:SS AM')),'DD-MON-YYYY HH:MI:SS AM') FROM V_ QRY_FD_DEALSUMMARY WHERE FD_SECURITY_CODE=:B2 AND FD_FUND_ID= :B1 AND FD_REVERSA 6 6 1 6.42 0.1 44zg1b5bfsawf Module: QUANTISV4.exe begin P_DEALSUMMARY_DML(:V00001,20110328105243,:V00003,:V00004,50000000,500000,9 8.2735,98.2735,49136750,0,49136750,1,0,0,:V00015,:V00016,100000003,:V00018,20110 420135908,:V00020,:V00021,:V00022,20031229174755,:V00024,2011201200120,1,8.55,:V 00028,:V00029,:V00030,:V00031,:V00032,:V00033,:V00034,0,:V00036,:V00037,0,1,:V00 6 6 1 6.33 0.1 bgr0c464syusj Module: QUANTISV4.exe begin P_DEALSUMMARY_SETT_AUTH(2011201200124,100000006,:V00003,20100826130658,500 0,0.005,:V00007,:V00008,:V00009,:V00010,:V00011,:V00012,:V00013,:V00014,:V00015, 0,:V00017,:V00018,0,:V00020,:V00021,:V00022,:V00023,:V00024,:V00025,:V00026,:V00 027,:V00028,:V00029,1,5,1,:V00033,104,:V00035,:V00036,:V00037); end; 6 6 1 6.10 0.1 d6urcfrct1qyq Module: QUANTISV4.exe begin P_DEALSUMMARY_DML(:V00001,20110328105243,:V00003,:V00004,100000000,1000000 ,98.2735,98.2735,98273500,0,98273500,20100826130658,5000,0.005,:V00015,:V00016,1 00000006,:V00018,20110420135908,:V00020,:V00021,:V00022,20031229174755,:V00024,: V00025,:V00026,8.55,:V00028,:V00029,:V00030,:V00031,:V00032,:V00033,:V00034,0,:V 1 208 13 0.07 2.2 a2t1yua71v2qw Module: QUANTISV4.exe begin LMS.LMS_PKG_OL_PROCESS_HANDLER.P_LMS_EVALUATE_REQUEST(p_request_id=>:p_req uest_id, p_user_code=>:p_user_code, p_machine_id=>:p_machine_id, p_response_data =>:p_response_data, p_error_message=>:p_error_message, p_final_action=>:p_final_ SQL ordered by CPU Time DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- action); end; ------------------------------------------------------------- SQL ordered by Gets DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 17,299,487 -> Captured SQL account for 40.9% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 10,024,144 2,105 4,762.1 57.9 126.79 5699.37 6ybtgzh74j70z Module: QUANTISV4.exe begin p_user_alerts; end; 648,551 7 92,650.1 3.7 3.02 5.19 ffmsr2ajvsr8g Module: QUANTISV4.exE select distinct "Fund Name",c.DS_DEALNO "Order No./ Deal No." ,"Limit Name", "Limit Nature","Limit Category" "Limit Type","Limit Computed For" "Limit Cate gory", "Limit Computed For Value" "Entity Name","Limit Corpus", Roun d("Corpus Value",2) "Limit Set", Round("Measure Value",2) "Limit Utilize 535,834 2,008 266.8 3.1 11.32 14.18 59b2sa6wzmbby Module: QUANTISV4.exe begin p_ca_alerts; end; 518,883 8,168 63.5 3.0 15.78 15.97 5a58p4u5f75aj Module: QUANTISV4.exe begin P_GET_ISM_QUANTITY(:V00001,:V00002,:V00003,:V00004,:V00005,:V00006); end; 470,256 1,848 254.5 2.7 2.29 2.29 80j5m3rtb3zhc Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Access Control','For Approval',COUNT(*), NULL FR OM (SELECT DISTINCT CATEGORY, CATEGORY_ID FROM ACCESS_CONTROL WHERE ACC_RECORD_S TATUS=1 AND ACC_APPROVAL_FLAG=1) 423,508 1,853 228.6 2.4 2.57 7.77 gqt7fctga7y1b Module: QUANTISV4.exe DELETE FROM USER_ALERTS WHERE USA_COUNT<=0 405,573 2,134 190.1 2.3 4.60 4.60 gzm2xcd8dw328 Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Equity - Execution','For Compliance App roval',COUNT(1),'FRMLIMITBREACHAUTHORIZE' FROM EQ_ORDEREXECUTION WHERE EQ_OE_EXE CUTION_LOT_NO NOT IN (SELECT EQ_DS_EXECUTION_LOT_NO FROM EQ_DEALSUMMARY WHERE EQ _DS_EXECUTION_LOT_NO IS NOT NULL) AND EQ_OE_SPLIT_FLAG='N' AND EQ_OE_CANCELLED_Y 398,789 38 10,494.4 2.3 110.70 246.63 2p6p6c9b3j0pk BEGIN LMS_PKG_OL_PROCESS_HANDLER.LMS_PR_ORDER_HANDLER(:1,:2,:3); END; 353,386 2,133 165.7 2.0 5.80 7.77 2vd51j33ndxr8 Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades-Equity','For Confirmation',COUNT(1), :B1 FROM EQ_ORDERBOOK WHERE TRUNC(SYSDATE) BETWEEN EQ_OS_ORDER_STDATE AND NVL(EQ_OS_ ORDER_ENDDATE,TRUNC(SYSDATE)) AND EQ_OS_CANCELLED_Y_N = 'N' AND EQ_OS_RECORD_STA TUS = 1 AND EQ_OS_APPROVAL_FLAG = 2 AND EQ_OS_ORDERNO IN (SELECT EQ_OE_ORDERNO F 318,780 2,187 145.8 1.8 5.08 5.14 7r451gn5uw1rf Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Equity Orders ','For Execution',COUNT(1),:B1 FRO M EQ_ORDERBOOK WHERE TRUNC(SYSDATE) BETWEEN EQ_OS_ORDER_STDATE AND NVL(EQ_OS_ORD ER_ENDDATE,TRUNC(SYSDATE)) AND EQ_OS_CANCELLED_Y_N = 'N' AND EQ_OS_RECORD_STATUS = 1 AND EQ_OS_APPROVAL_FLAG = 2 AND EQ_OS_ORDERNO NOT IN ( SELECT EQ_OE_ORDERNO 289,845 735 394.3 1.7 39.52 41.68 9czd590q2180z Module: QUANTISV4.exe SELECT DS_VALUEDATE FROM V_QRY_DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUN D_ID=:B1 AND DS_REVERSAL_FLAG='N' AND DS_INPUT_DATE=(SELECT TO_CHAR(MAX(TO_DATE( DS_INPUT_DATE,'DD-MON-YYYY HH:MI:SS AM')),'DD-MON-YYYY HH:MI:SS AM') FROM V_QRY_ DEALSUMMARY WHERE DS_SECURITY_CODE=:B2 AND DS_FUND_ID=:B1 AND DS_REVERSAL_FLAG=' SQL ordered by Gets DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 17,299,487 -> Captured SQL account for 40.9% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 283,430 1,111 255.1 1.6 1.06 1.06 4txgca6gf23ug Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Access Control','Rejected',COUNT(*), NULL FROM ( SELECT DISTINCT CATEGORY, CATEGORY_ID FROM ACCESS_CONTROL WHERE ACC_RECORD_STATU S=1 AND ACC_APPROVAL_FLAG=3) 273,405 2,134 128.1 1.6 4.42 4.42 16umx2yvx1syw Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Securities','For Acknowledgement(2)',CO UNT(*), 'FRMDEBTACKT' FROM DEALSUMMARY WHERE DS_RECORD_STATUS=1 AND DS_REVERSAL_ FLAG = 'N' AND DS_APPROVAL_FLAG=5 AND PUT_OPTION_EXERCISE_FLAG = 'N' AND DS_COMP LIANCE_FLAG='Y'AND PARENT_SCHEME IS NULL AND DS_ACKNOWLEDGE_FLAG='Y' AND (DS_ACK 237,159 1,849 128.3 1.4 2.57 2.62 3908781u5sh3p Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Securities','For Settlement',COUNT(*), 'FRMDEBTSETTLEMENT' FROM DEALSUMMARY WHERE DS_RECORD_STATUS=1 AND DS_REVERSAL_FL AG = 'N' AND DS_APPROVAL_FLAG=5 AND PUT_OPTION_EXERCISE_FLAG = 'N' AND PARENT_SC HEME IS NULL AND DS_ACKNOWLEDGE_FLAG='Y' AND DS_COMPLIANCE_FLAG='Y' AND DS_ACKNO 236,982 1,851 128.0 1.4 2.63 2.63 c8gx3tw2251n7 Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Securities ','For Compliance Approval', COUNT(*), 'FRMLIMITBREACHAUTHORIZE' FROM DEALSUMMARY WHERE DS_RECORD_STATUS=1 AN D DS_REVERSAL_FLAG = 'N' AND DS_APPROVAL_FLAG=5 AND PARENT_SCHEME IS NULL AND DS _COMPLIANCE_FLAG='P' AND (DS_ACKNOWLEDGE_FLAG='N' OR DS_ACKNOWLEDGE_FLAG_2='N') 236,865 1,852 127.9 1.4 2.63 6.40 333aqp038n4h2 Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Securities ','Rejected by Compliance',C OUNT(*), 'FRMDEBTTRADES' FROM DEALSUMMARY WHERE DS_RECORD_STATUS=1 AND DS_REVERS AL_FLAG = 'N' AND DS_APPROVAL_FLAG=5 AND PARENT_SCHEME IS NULL AND DS_COMPLIANCE _FLAG='N' AND (DS_ACKNOWLEDGE_FLAG='N' OR DS_ACKNOWLEDGE_FLAG_2='N') 236,491 1,850 127.8 1.4 2.80 2.80 66ntwpq8j8vwf Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Securities','For Acknowledgement(1)',CO UNT(*), 'FRMDEBTACKT' FROM DEALSUMMARY WHERE DS_RECORD_STATUS=1 AND DS_REVERSAL_ FLAG = 'N' AND DS_APPROVAL_FLAG=5 AND PUT_OPTION_EXERCISE_FLAG = 'N' AND DS_COMP LIANCE_FLAG='Y'AND PARENT_SCHEME IS NULL AND (DS_ACKNOWLEDGE_FLAG='N' OR DS_ACKN 190,726 84 2,270.5 1.1 15.96 20.95 3g8y4xy9txrrj Module: Quantis.Utilities.Service.exe begin UT_PKG_PROCESS_EXECUTIONS.P_DOWNLOAD_CITY_DEALSLIPFILE(P_LOGSYSCODE=>:P_LO GSYSCODE, P_FROMDATE=>:P_FROMDATE, P_TODATE=>:P_TODATE); end; 186,520 1,457 128.0 1.1 1.63 1.63 5y85am1csv7kn Module: QUANTISV4.exe INSERT INTO USER_ALERTS SELECT 'Trades - Equity - Execution','Rejected by Compli ance',COUNT(1),:B1 FROM EQ_ORDEREXECUTION WHERE EQ_OE_EXECUTION_LOT_NO NOT IN (S ELECT EQ_DS_EXECUTION_LOT_NO FROM EQ_DEALSUMMARY WHERE EQ_DS_EXECUTION_LOT_NO IS NOT NULL) AND EQ_OE_SPLIT_FLAG='N' AND EQ_OE_CANCELLED_Y_N='N' AND EQ_OE_COMPLI ------------------------------------------------------------- SQL ordered by Reads DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total Disk Reads: 5,564 -> Captured SQL account for 9.9% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 1,515 38 39.9 27.2 110.70 246.63 2p6p6c9b3j0pk BEGIN LMS_PKG_OL_PROCESS_HANDLER.LMS_PR_ORDER_HANDLER(:1,:2,:3); END; 360 7 51.4 6.5 3.02 5.19 ffmsr2ajvsr8g Module: QUANTISV4.exE select distinct "Fund Name",c.DS_DEALNO "Order No./ Deal No." ,"Limit Name", "Limit Nature","Limit Category" "Limit Type","Limit Computed For" "Limit Cate gory", "Limit Computed For Value" "Entity Name","Limit Corpus", Roun d("Corpus Value",2) "Limit Set", Round("Measure Value",2) "Limit Utilize 82 1,362 0.1 1.5 0.35 0.43 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 23 14 1.6 0.4 1.34 5.72 8bdgnb4ravy55 Module: QUANTISV4.exe begin P_DEALSLIP_REPORT_VALUEDATE(:V00001,:V00002,:V00003,:V00004,:V00005,:V0000 6,:V00007); end; 22 1 22.0 0.4 0.30 27.00 f8ff0y5gk3c96 Module: Quantis.Utilities.Service.exe begin UT_PKG_BUSINESS_LOGIC.P_UPLOAD_MD_MIBOR_BENCHMARK; end; 21 2,105 0.0 0.4 126.79 5699.37 6ybtgzh74j70z Module: QUANTISV4.exe begin p_user_alerts; end; 21 2 10.5 0.4 0.14 0.62 d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 16 1 16.0 0.3 0.39 0.46 2dz1jc0vuwyfc Module: QUANTISV4.exe begin P_Dailydeals(:V00001,104); end; 13 199 0.1 0.2 0.04 0.09 3m8smr0v7v1m6 INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p 1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13) 13 7 1.9 0.2 0.58 1.10 cyqas1jyhzmgz Module: QUANTISV4.exe begin P_Eq_Orderbook_Dml(P_EQ_OS_ORDERNO=>:P_EQ_OS_ORDERNO, P_EQ_OS_FUND_TYPE=>: P_EQ_OS_FUND_TYPE, P_EQ_OS_FUND_CODE=>:P_EQ_OS_FUND_CODE, P_EQ_OS_TRANSTYPE=>:P_ EQ_OS_TRANSTYPE, P_EQ_OS_ISM_CODE=>:P_EQ_OS_ISM_CODE, P_EQ_OS_ORDER_DATE=>:P_EQ_ OS_ORDER_DATE, P_EQ_OS_ORDER_NATURE=>:P_EQ_OS_ORDER_NATURE, P_EQ_OS_QUANTITY_ORD ------------------------------------------------------------- SQL ordered by Executions DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total Executions: 1,237,528 -> Captured SQL account for 26.4% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 67,211 67,214 1.0 0.00 0.00 ftj9uawt4wwzb select condition from cdef$ where rowid=:1 38,582 38,583 1.0 0.00 0.00 1crmd8ubnq1th Module: QUANTISV4.exe SELECT SUM(FD_QUANTITY) FROM FD_DEALSUMMARY WHERE FD_SECURITY_CODE =:B3 AND FD_F UND_ID =:B2 AND FD_TRANSTYPE ='SELL' AND FD_TRADEDATE <=:B1 AND FD_RECORD_STATUS =1 AND FD_APPROVAL_FLAG =2 AND FD_REVERSAL_FLAG='N' 20,764 16,089 0.8 0.00 0.00 dmfp3aj9kz7nh Module: Quantis.Utilities.Service.exe SELECT PARAMETER_VALUE FROM QUANTIS_SYSTEM_SETTINGS WHERE PARAMETER_ID = :B1 AND ENABLED_Y_N='Y' 12,846 12,821 1.0 0.00 0.00 3c1kubcdjnppq update sys.col_usage$ set equality_preds = equality_preds + decode(bitan d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag ,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0 ,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), 11,276 11,276 1.0 0.00 0.00 bznhz5mfj04m6 Module: QUANTISV4.exe SELECT SYSDATE currentdate FROM DUAL 9,765 119,832 12.3 0.00 0.00 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 8,168 8,168 1.0 0.00 0.00 5a58p4u5f75aj Module: QUANTISV4.exe begin P_GET_ISM_QUANTITY(:V00001,:V00002,:V00003,:V00004,:V00005,:V00006); end; 7,280 7,280 1.0 0.00 0.00 3pqhmvzbhu8y4 Module: QUANTISV4.exe SELECT SUM(DECODE(DS_TRANSTYPE,'BUY',DS_QUANTITY,'SELL',-DS_QUANTITY)) FROM DEAL SUMMARY WHERE DS_RECORD_STATUS = 1 AND DS_REVERSAL_FLAG = 'N' AND DS_APPROVAL_FL AG = 2 AND DS_SECURITY_CODE = :B3 AND DS_FUND_ID = :B2 AND DS_TRADEDATE <=:B1 4,611 9,764 2.1 0.00 0.00 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege# >0 4,532 4,532 1.0 0.00 0.00 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total Parse Calls: 845,954 -> Captured SQL account for 28.9% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 67,213 67,211 7.95 ftj9uawt4wwzb select condition from cdef$ where rowid=:1 16,336 8,168 1.93 5a58p4u5f75aj Module: QUANTISV4.exe begin P_GET_ISM_QUANTITY(:V00001,:V00002,:V00003,:V00004,:V00005,:V00006); end; 11,276 11,276 1.33 bznhz5mfj04m6 Module: QUANTISV4.exe SELECT SYSDATE currentdate FROM DUAL 9,765 9,765 1.15 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 4,596 4,611 0.54 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege# >0 4,532 4,531 0.54 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_ NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE _NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME') 4,532 4,532 0.54 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' 4,532 4,532 0.54 5ur69atw3vfhj 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, failover_delay, flags from service$ where name = :1 4,368 2,105 0.52 6ybtgzh74j70z Module: QUANTISV4.exe begin p_user_alerts; end; 4,084 2,008 0.48 59b2sa6wzmbby Module: QUANTISV4.exe begin p_ca_alerts; end; ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Instance Activity Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 54,058 2.2 3.8 CPU used when call started 53,532 2.1 3.7 CR blocks created 897 0.0 0.1 Cached Commit SCN referenced 739 0.0 0.1 Commit SCN cached 3 0.0 0.0 DB time 1,187,690 47.2 82.4 DBWR checkpoint buffers written 28,356 1.1 2.0 DBWR checkpoints 191 0.0 0.0 DBWR object drop buffers written 750 0.0 0.1 DBWR revisited being-written buf 42 0.0 0.0 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 0 0.0 0.0 DBWR transaction table writes 823 0.0 0.1 DBWR undo block writes 18,935 0.8 1.3 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 0 0.0 0.0 IMU CR rollbacks 436 0.0 0.0 IMU Flushes 7,432 0.3 0.5 IMU Redo allocation size 5,071,268 201.7 352.0 IMU commits 5,739 0.2 0.4 IMU contention 20 0.0 0.0 IMU ktichg flush 342 0.0 0.0 IMU pool not allocated 1,200 0.1 0.1 IMU recursive-transaction flush 1 0.0 0.0 IMU undo allocation size 20,613,304 819.7 1,430.8 IMU- failed to get a private str 1,200 0.1 0.1 Misses for writing mapping 0 0.0 0.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 Parallel operations not downgrad 0 0.0 0.0 SMON posted for undo segment shr 11 0.0 0.0 SQL*Net roundtrips to/from clien 366,192 14.6 25.4 active txn count during cleanout 5,000 0.2 0.4 application wait time 7,655 0.3 0.5 auto extends on undo tablespace 0 0.0 0.0 background checkpoints completed 5 0.0 0.0 background checkpoints started 5 0.0 0.0 background timeouts 97,416 3.9 6.8 branch node splits 0 0.0 0.0 buffer is not pinned count 3,703,462 147.3 257.1 buffer is pinned count 3,662,807 145.7 254.2 bytes received via SQL*Net from 39,124,487 1,555.8 2,715.7 bytes sent via SQL*Net to client 53,925,274 2,144.4 3,743.0 calls to get snapshot scn: kcmgs 2,694,707 107.2 187.0 calls to kcmgas 53,261 2.1 3.7 calls to kcmgcs 12,557 0.5 0.9 change write time 40,188 1.6 2.8 cleanout - number of ktugct call 12,241 0.5 0.9 cleanouts and rollbacks - consis 244 0.0 0.0 cleanouts only - consistent read 7,162 0.3 0.5 cluster key scan block gets 87,978 3.5 6.1 cluster key scans 24,972 1.0 1.7 commit batch/immediate performed 80 0.0 0.0 commit batch/immediate requested 80 0.0 0.0 commit cleanout failures: block 9,561 0.4 0.7 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callba 49 0.0 0.0 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 40,295 1.6 2.8 commit cleanouts successfully co 30,685 1.2 2.1 Instance Activity Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit immediate performed 80 0.0 0.0 commit immediate requested 80 0.0 0.0 commit txn count during cleanout 9,683 0.4 0.7 concurrency wait time 2,826 0.1 0.2 consistent changes 1,088,394 43.3 75.6 consistent gets 15,892,725 632.0 1,103.1 consistent gets - examination 955,138 38.0 66.3 consistent gets direct 237 0.0 0.0 consistent gets from cache 15,892,488 632.0 1,103.1 cursor authentications 20,424 0.8 1.4 data blocks consistent reads - u 1,157 0.1 0.1 db block changes 2,480,386 98.6 172.2 db block gets 1,406,762 55.9 97.6 db block gets direct 66 0.0 0.0 db block gets from cache 1,406,696 55.9 97.6 deferred (CURRENT) block cleanou 18,906 0.8 1.3 dirty buffers inspected 0 0.0 0.0 enqueue conversions 14,482 0.6 1.0 enqueue releases 317,752 12.6 22.1 enqueue requests 317,834 12.6 22.1 enqueue timeouts 47 0.0 0.0 enqueue waits 211 0.0 0.0 execute count 1,237,528 49.2 85.9 frame signature mismatch 0 0.0 0.0 free buffer inspected 6,472 0.3 0.5 free buffer requested 35,393 1.4 2.5 global undo segment hints helped 0 0.0 0.0 heap block compress 32,579 1.3 2.3 hot buffers moved to head of LRU 5,413 0.2 0.4 immediate (CR) block cleanout ap 7,406 0.3 0.5 immediate (CURRENT) block cleano 2,443 0.1 0.2 index fast full scans (full) 10,880 0.4 0.8 index fetch by key 1,089,815 43.3 75.6 index scans kdiixs1 915,541 36.4 63.6 java call heap collected bytes 1,044,912 41.6 72.5 java call heap collected count 21,138 0.8 1.5 java call heap gc count 18 0.0 0.0 java call heap live object count 0 0.0 0.0 java call heap live object count 0 0.0 0.0 java call heap live size 0 0.0 0.0 java call heap live size max 0 0.0 0.0 java call heap object count 7,101 0.3 0.5 java call heap object count max 8,657 0.3 0.6 java call heap total size 394,240 15.7 27.4 java call heap total size max 1,971,200 78.4 136.8 java call heap used size 342,576 13.6 23.8 java call heap used size max 398,648 15.9 27.7 leaf node 90-10 splits 100 0.0 0.0 leaf node splits 242 0.0 0.0 lob reads 1,713 0.1 0.1 lob writes 365 0.0 0.0 lob writes unaligned 365 0.0 0.0 logons cumulative 4,666 0.2 0.3 messages received 31,369 1.3 2.2 messages sent 31,369 1.3 2.2 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 12,235,029 486.5 849.2 opened cursors cumulative 837,808 33.3 58.2 parse count (failures) 107 0.0 0.0 parse count (hard) 34,976 1.4 2.4 Instance Activity Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- parse count (total) 845,954 33.6 58.7 parse time cpu 12,719 0.5 0.9 parse time elapsed 19,806 0.8 1.4 physical read IO requests 3,994 0.2 0.3 physical read bytes 45,580,288 1,812.5 3,163.8 physical read total IO requests 41,215 1.6 2.9 physical read total bytes 942,777,856 37,490.1 65,438.9 physical read total multi block 571 0.0 0.0 physical reads 5,564 0.2 0.4 physical reads cache 4,572 0.2 0.3 physical reads cache prefetch 898 0.0 0.1 physical reads direct 992 0.0 0.1 physical reads direct (lob) 196 0.0 0.0 physical reads direct temporary 720 0.0 0.1 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 9,701 0.4 0.7 physical write bytes 253,919,232 10,097.2 17,624.7 physical write total IO requests 59,567 2.4 4.1 physical write total bytes 1,470,666,240 58,481.9 102,080.0 physical write total multi block 22,007 0.9 1.5 physical writes 30,996 1.2 2.2 physical writes direct 821 0.0 0.1 physical writes direct (lob) 42 0.0 0.0 physical writes direct temporary 720 0.0 0.1 physical writes from cache 30,175 1.2 2.1 physical writes non checkpoint 9,344 0.4 0.7 pinned buffers inspected 17 0.0 0.0 prefetch clients - default 0 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 28 0.0 0.0 process last non-idle time 20,408 0.8 1.4 recursive calls 4,086,725 162.5 283.7 recursive cpu usage 48,481 1.9 3.4 redo blocks written 535,250 21.3 37.2 redo buffer allocation retries 516 0.0 0.0 redo entries 1,222,450 48.6 84.9 redo log space requests 35 0.0 0.0 redo log space wait time 2,493 0.1 0.2 redo ordering marks 5,138 0.2 0.4 redo size 261,662,636 10,405.2 18,162.2 redo synch time 804,169 32.0 55.8 redo synch writes 62,994 2.5 4.4 redo wastage 2,941,244 117.0 204.2 redo write time 24,624 1.0 1.7 redo writer latching time 0 0.0 0.0 redo writes 10,931 0.4 0.8 rollback changes - undo records 1,094 0.0 0.1 rollbacks only - consistent read 523 0.0 0.0 rows fetched via callback 337,561 13.4 23.4 session connect time 0 0.0 0.0 session cursor cache hits 213,728 8.5 14.8 session logical reads 17,299,487 687.9 1,200.8 session pga memory 44,477,476 1,768.7 3,087.2 session pga memory max 220,441,636 8,766.0 15,301.0 session uga memory 68,850,786,768 2,737,889.8 4,778,981.5 session uga memory max 1,973,495,504 78,477.1 136,981.7 shared hash latch upgrades - no 743,315 29.6 51.6 shared hash latch upgrades - wai 2,614 0.1 0.2 sorts (memory) 118,340 4.7 8.2 sorts (rows) 96,420,302 3,834.2 6,692.6 Instance Activity Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- sql area purged 159 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 21,847 0.9 1.5 table fetch by rowid 2,423,544 96.4 168.2 table fetch continued row 11,137 0.4 0.8 table scan blocks gotten 10,120,787 402.5 702.5 table scan rows gotten 300,211,208 11,938.1 20,837.9 table scans (long tables) 0 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 535,649 21.3 37.2 total number of times SMON poste 712 0.0 0.1 transaction rollbacks 80 0.0 0.0 undo change vector size 145,026,988 5,767.1 10,066.4 user I/O wait time 5,594 0.2 0.4 user calls 373,315 14.9 25.9 user commits 13,629 0.5 1.0 user rollbacks 778 0.0 0.1 workarea executions - onepass 4 0.0 0.0 workarea executions - optimal 75,520 3.0 5.2 write clones created in backgrou 620 0.0 0.0 write clones created in foregrou 26 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: DPAMPL/dpampl Snaps: 13290 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 10,655,613 10,682,971 opened cursors current 101 490 logons current 52 61 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: DPAMPL/dpampl Snaps: 13290- -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 5 .72 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ USERS 1,927 0 3.3 1.1 2,898 0 1 0.0 SYSAUX 355 0 5.3 1.0 3,055 0 0 0.0 SYSTEM 1,523 0 2.7 1.4 1,254 0 7 0.0 UNDOTBS1 22 0 5.9 1.0 2,257 0 4 0.0 INDEX_TBS 83 0 3.6 2.0 144 0 0 0.0 TEMP 70 0 3.3 10.7 83 0 0 0.0 EXAMPLE 7 0 18.6 1.0 5 0 0 0.0 PERFSTAT 7 0 11.4 1.0 5 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ EXAMPLE E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\EXAMPLE01.DB 7 0 18.6 1.0 5 0 0 0.0 INDEX_TBS E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\INDEX_TBS.DB 83 0 3.6 2.0 144 0 0 0.0 PERFSTAT E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\PERFSTAT.DBF 7 0 11.4 1.0 5 0 0 0.0 SYSAUX E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\SYSAUX01.DBF 355 0 5.3 1.0 3,055 0 0 0.0 SYSTEM E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\SYSTEM01.DBF 1,523 0 2.7 1.4 1,254 0 7 0.0 TEMP E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\TEMP01.DBF 70 0 3.3 10.7 83 0 0 N/A UNDOTBS1 E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\UNDOTBS01.DB 22 0 5.9 1.0 2,257 0 4 0.0 USERS E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\USERS01.DBF 1,927 0 3.3 1.1 2,898 0 1 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 101,526 100 17,289,747 4,572 30,175 0 0 12 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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 19 305 1447 1833 184320 1833 N/A E 0 20 670 8522 36974 184320 36974 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 80 .1 9,980 3.8 10,794,740 D 160 .2 19,960 3.2 9,082,841 D 240 .3 29,940 2.7 7,777,716 D 320 .4 39,920 2.4 6,731,863 D 400 .5 49,900 2.1 5,868,043 D 480 .6 59,880 1.8 5,126,305 D 560 .7 69,860 1.6 4,474,881 D 640 .8 79,840 1.4 3,900,226 D 720 .9 89,820 1.2 3,387,897 D 800 1.0 99,800 1.0 2,928,523 D 816 1.0 101,796 1.0 2,846,628 D 880 1.1 109,780 0.9 2,519,034 D 960 1.2 119,760 0.8 2,152,385 D 1,040 1.3 129,740 0.6 1,820,338 D 1,120 1.4 139,720 0.5 1,520,884 D 1,200 1.5 149,700 0.4 1,256,505 D 1,280 1.6 159,680 0.4 1,026,721 D 1,360 1.7 169,660 0.3 842,737 D 1,440 1.8 179,640 0.2 692,520 D 1,520 1.9 189,620 0.2 574,259 D 1,600 2.0 199,600 0.2 457,582 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 99.8 9,569 23 ------------------------------------------------------------- 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 Aggr Target Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> 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 %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 600 38 1,075.3 0.0 .0 .0 .0 102,400 E 600 38 1,103.7 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 62,391 62,391 0 0 64K 128K 70 70 0 0 128K 256K 38 38 0 0 256K 512K 28 28 0 0 512K 1024K 12,959 12,959 0 0 1M 2M 12 12 0 0 4M 8M 16 12 4 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 75 0.1 951,616.8 15,809.6 98.0 2,272 150 0.3 951,616.8 15,809.6 98.0 2,272 300 0.5 951,616.8 8,548.0 99.0 2,054 450 0.8 951,616.8 8,496.8 99.0 1,999 600 1.0 951,616.8 2,177.2 100.0 152 720 1.2 951,616.8 2,167.2 100.0 0 840 1.4 951,616.8 2,167.2 100.0 0 960 1.6 951,616.8 2,167.2 100.0 0 1,080 1.8 951,616.8 2,167.2 100.0 0 1,200 2.0 951,616.8 2,167.2 100.0 0 1,800 3.0 951,616.8 2,167.2 100.0 0 2,400 4.0 951,616.8 2,167.2 100.0 0 3,600 6.0 951,616.8 2,167.2 100.0 0 4,800 8.0 951,616.8 2,167.2 100.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 152 .9 21 2,789 ####### 1.0 35,722 1.0 ########### 172 1.0 38 4,915 ####### 1.0 35,364 1.0 ########### 192 1.1 57 6,516 ####### 1.0 35,011 1.0 ########### 212 1.2 76 7,718 ####### 1.0 34,665 1.0 ########### 232 1.3 95 9,135 ####### 1.0 34,325 1.0 ########### 252 1.5 114 10,622 ####### 1.0 33,991 1.0 ########### 272 1.6 133 11,735 ####### 1.0 33,663 1.0 ########### 292 1.7 152 13,318 ####### 1.0 33,339 .9 ########### 312 1.8 171 14,915 ####### 1.0 33,019 .9 ########### 332 1.9 190 17,072 ####### 1.0 32,702 .9 ########### 352 2.0 208 18,356 ####### 1.0 32,389 .9 ########### ------------------------------------------------------------- SGA Target Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 512 0.5 716,248 6,731,991 768 0.8 709,485 4,474,899 1,024 1.0 704,483 2,846,628 1,280 1.3 700,538 2,152,335 1,536 1.5 698,213 1,256,502 1,792 1.8 698,213 842,602 2,048 2.0 698,213 457,453 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 4 0.5 0 0 0 0 8 1.0 0 0 0 0 12 1.5 0 0 0 0 16 2.0 0 0 0 0 20 2.5 0 0 0 0 24 3.0 0 0 0 0 28 3.5 0 0 0 0 32 4.0 0 0 0 0 36 4.5 0 0 0 0 40 5.0 0 0 0 0 44 5.5 0 0 0 0 48 6.0 0 0 0 0 52 6.5 0 0 0 0 56 7.0 0 0 0 0 60 7.5 0 0 0 0 64 8.0 0 0 0 0 68 8.5 0 0 0 0 72 9.0 0 0 0 0 76 9.5 0 0 0 0 80 10.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: DPAMPL/dpampl Snap: 13297 Est LC Est LC Est LC Est LC Java JP 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 16 1.0 4 537 21 1.0 35,364 1.0 3,020 20 1.3 7 616 21 1.0 35,364 1.0 3,020 24 1.5 9 670 21 1.0 35,364 1.0 3,020 28 1.8 9 670 21 1.0 35,364 1.0 3,020 32 2.0 9 670 21 1.0 35,364 1.0 3,020 ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 8 0 0 undo header 4 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 1,674 1,674 0 186 40 217.65 UL-User-defined 133 122 11 20 36 1,798.35 CF-Controlfile Transaction 17,267 17,265 0 5 2 406.20 ------------------------------------------------------------- Undo Segment Summary DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 1 18.6 24,165 1,851 7 15/43.033 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Most recent 35 Undostat rows, ordered by 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 20-Apr 15:26 487 894 690 6 27 0/0 0/0/0/0/0/0 20-Apr 15:16 423 596 78 6 16 0/0 0/0/0/0/0/0 20-Apr 15:06 429 252 0 6 15 0/0 0/0/0/0/0/0 20-Apr 14:56 424 267 0 5 15 0/0 0/0/0/0/0/0 20-Apr 14:46 342 213 1,371 6 38 0/0 0/0/0/0/0/0 20-Apr 14:36 487 656 1,673 6 39 0/0 0/0/0/0/0/0 20-Apr 14:26 484 963 1,061 6 29 0/0 0/0/0/0/0/0 20-Apr 14:16 671 795 452 7 19 0/0 0/0/0/0/0/0 20-Apr 14:06 723 812 1,851 6 42 0/0 0/0/0/0/0/0 20-Apr 13:56 415 241 1,242 6 32 0/0 0/0/0/0/0/0 20-Apr 13:46 412 497 629 5 22 0/0 0/0/0/0/0/0 20-Apr 13:36 531 879 89 7 15 0/0 0/0/0/0/0/0 20-Apr 13:26 429 253 1,572 6 38 0/0 0/0/0/0/0/0 20-Apr 13:16 449 356 961 2 28 0/0 0/0/0/0/0/0 20-Apr 13:06 432 381 1,394 4 35 0/0 0/0/0/0/0/0 20-Apr 12:56 451 657 783 4 25 0/0 0/0/0/0/0/0 20-Apr 12:46 422 520 1,709 4 41 0/0 0/0/0/0/0/0 20-Apr 12:36 518 423 1,098 3 31 0/0 0/0/0/0/0/0 20-Apr 12:26 433 257 1,792 4 43 0/0 0/0/0/0/0/0 20-Apr 12:16 462 492 1,801 5 43 0/0 0/0/0/0/0/0 20-Apr 12:06 416 251 1,189 3 33 0/0 0/0/0/0/0/0 20-Apr 11:56 469 760 747 4 26 0/0 0/0/0/0/0/0 20-Apr 11:46 470 705 137 4 15 0/0 0/0/0/0/0/0 20-Apr 11:36 524 752 711 4 26 0/0 0/0/0/0/0/0 20-Apr 11:26 659 1,181 186 5 17 0/0 0/0/0/0/0/0 20-Apr 11:16 407 291 0 6 15 0/0 0/0/0/0/0/0 20-Apr 11:06 445 330 0 6 15 0/0 0/0/0/0/0/0 20-Apr 10:56 400 232 0 6 15 0/0 0/0/0/0/0/0 20-Apr 10:46 374 556 0 6 15 0/0 0/0/0/0/0/0 20-Apr 10:36 474 317 0 6 15 0/0 0/0/0/0/0/0 20-Apr 10:26 411 469 1,554 6 41 0/0 0/0/0/0/0/0 20-Apr 10:16 788 271 945 5 31 0/0 0/0/0/0/0/0 20-Apr 10:06 412 982 333 4 21 0/0 0/0/0/0/0/0 20-Apr 09:56 465 1,072 919 4 30 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ AWR Alerted Metric Eleme 121,512 0.0 N/A 0 0 N/A Consistent RBA 10,936 0.0 N/A 0 0 N/A FAL request queue 2,868 0.0 N/A 0 0 N/A FAL subheap alocation 2,867 0.0 N/A 0 0 N/A FIB s.o chain latch 312 0.0 N/A 0 0 N/A FOB s.o list latch 1,283 0.2 0.0 0 0 N/A In memory undo latch 84,483 0.0 0.0 0 24,909 0.0 JOX SGA heap latch 404 0.0 N/A 0 0 N/A JS mem alloc latch 45 0.0 N/A 0 0 N/A JS queue access latch 45 0.0 N/A 0 0 N/A JS queue state obj latch 181,926 0.0 N/A 0 0 N/A JS slv state obj latch 123 0.0 N/A 0 0 N/A KMG MMAN ready and start 11,338 0.0 N/A 0 0 N/A KMG resize request state 56 0.0 N/A 0 0 N/A KTF sga latch 59 0.0 N/A 0 7,203 0.0 KWQMN job cache list lat 876 0.0 N/A 0 0 N/A KWQP Prop Status 2,819 0.0 N/A 0 0 N/A LGWR NS Write 2,863 0.2 0.0 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 492 0.0 Memory Management Latch 3,415 0.0 N/A 0 11,323 0.0 Memory Queue 2,805 0.0 N/A 0 0 N/A OS process 14,313 0.0 N/A 0 0 N/A OS process allocation 17,934 0.1 0.0 0 0 N/A OS process: request allo 9,291 0.0 N/A 0 0 N/A PL/SQL warning settings 40,369 0.0 0.0 0 0 N/A SGA IO buffer pool latch 138 0.0 N/A 0 138 0.0 SQL memory manager latch 7 0.0 N/A 0 8,322 0.0 SQL memory manager worka 814,289 0.0 0.0 0 0 N/A Shared B-Tree 17,819 1.3 0.0 0 0 N/A active checkpoint queue 28,648 0.2 0.0 0 0 N/A active service list 78,365 0.1 0.0 0 8,733 0.0 alert log latch 38 0.0 N/A 0 0 N/A archive control 1,327 0.0 N/A 0 0 N/A archive process latch 11,186 0.1 0.0 0 0 N/A begin backup scn array 49 0.0 N/A 0 0 N/A bq:time manger info latc 903 0.0 N/A 0 0 N/A buffer pool 96 0.0 N/A 0 0 N/A bufq statistics 2,826 0.0 N/A 0 0 N/A cache buffer handles 35,958 0.0 N/A 0 0 N/A cache buffers chains 37,655,221 0.5 0.0 0 126,689 0.1 cache buffers lru chain 153,176 0.2 0.0 0 37,653 0.4 cache table scan latch 0 N/A N/A 0 138 0.0 channel handle pool latc 10,424 0.0 0.0 0 0 N/A channel operations paren 573,157 0.0 0.0 0 0 N/A checkpoint queue latch 756,159 0.0 0.0 0 32,629 0.0 client/application info 23,092 0.0 N/A 0 0 N/A commit callback allocati 1,202 0.0 N/A 0 0 N/A compile environment latc 77,373 0.2 0.0 0 0 N/A dictionary lookup 224 0.0 N/A 0 0 N/A dml lock allocation 72,192 1.3 0.0 0 0 N/A dummy allocation 9,323 0.2 0.0 0 0 N/A enqueue hash chains 650,409 0.1 0.0 0 808 0.0 enqueue sob latch 32 0.0 N/A 0 0 N/A enqueues 591,701 0.1 0.0 0 0 N/A event group latch 4,650 0.0 N/A 0 0 N/A file cache latch 746 0.0 N/A 0 0 N/A global KZLD latch for me 4,579 0.0 N/A 0 0 N/A hash table column usage 6,051 0.0 0.0 0 3,181,646 0.0 hash table modification 740 0.0 N/A 0 0 N/A internal temp table obje 9 0.0 N/A 0 0 N/A Latch Activity DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ job workq parent latch 0 N/A N/A 0 32 0.0 job_queue_processes para 436 0.0 N/A 0 0 N/A kks stats 48,871 0.0 0.0 0 0 N/A kmcptab latch 1 0.0 N/A 0 0 N/A kmcpvec latch 0 N/A N/A 0 1 0.0 ksuosstats global area 1,691 0.0 N/A 0 0 N/A ktm global data 749 0.0 N/A 0 0 N/A kwqbsn:qsga 14,108 1.1 0.0 0 0 N/A lgwr LWN SCN 16,011 0.0 0.0 0 0 N/A library cache 6,557,882 0.5 0.0 0 62,779 0.0 library cache load lock 18,664 0.0 N/A 0 745 0.0 library cache lock 3,060,718 0.4 0.0 0 0 N/A library cache lock alloc 33,042 0.0 0.0 0 0 N/A library cache pin 3,484,339 0.2 0.0 0 5,016 0.0 library cache pin alloca 13,780 0.0 N/A 0 0 N/A list of block allocation 4,617 0.2 0.0 0 0 N/A loader state object free 820 0.0 N/A 0 0 N/A logminer context allocat 7 0.0 N/A 0 0 N/A message pool operations 125,714 0.0 0.0 0 0 N/A messages 361,456 0.5 0.0 0 0 N/A mostly latch-free SCN 16,095 0.4 0.0 0 0 N/A multiblock read objects 358 0.0 N/A 0 0 N/A ncodef allocation latch 416 0.0 N/A 0 0 N/A object queue header heap 1,921 0.0 N/A 0 1,791 0.0 object queue header oper 223,092 0.0 0.0 0 0 N/A object stats modificatio 1,196 0.2 0.0 0 0 N/A parallel query alloc buf 3,328 0.0 N/A 0 0 N/A parameter list 40,937 0.0 0.0 0 0 N/A parameter table allocati 4,723 0.8 0.0 0 0 N/A post/wait queue 24,318 0.8 0.0 0 15,787 1.1 process allocation 9,291 0.0 N/A 0 4,650 0.0 process group creation 9,291 0.1 0.0 0 0 N/A qmn state object latch 70 0.0 N/A 0 0 N/A qmn task queue latch 42,802 12.9 0.0 0 0 N/A redo allocation 93,751 0.3 0.0 0 1,222,068 0.3 redo copy 0 N/A N/A 0 1,222,668 0.0 redo writing 79,822 0.0 0.0 0 0 N/A resmgr group change latc 4,642 0.0 0.0 0 0 N/A resmgr:actses active lis 9,230 0.0 N/A 0 0 N/A resmgr:actses change gro 4,601 0.0 N/A 0 0 N/A resmgr:free threads list 9,223 0.3 0.0 0 0 N/A resmgr:schema config 7 0.0 N/A 0 0 N/A row cache objects 8,761,734 0.1 0.0 0 1,084 0.0 rules engine aggregate s 175 0.0 N/A 0 0 N/A rules engine rule set st 1,050 0.0 N/A 0 0 N/A sequence cache 23,874 0.0 0.0 0 0 N/A session allocation 1,853,321 0.1 0.0 0 0 N/A session idle bit 819,337 0.0 0.0 0 0 N/A session state list latch 912 0.0 N/A 0 0 N/A session switching 416 0.0 N/A 0 0 N/A session timer 8,733 0.0 N/A 0 0 N/A shared pool 2,068,113 0.1 0.0 0 0 N/A simulator hash latch 1,067,292 0.0 0.0 0 0 N/A simulator lru latch 1,066,585 0.8 0.0 0 1,598 0.0 slave class 49 0.0 N/A 0 0 N/A slave class create 199 3.5 1.3 0 0 N/A sort extent pool 4,623 0.2 0.0 0 0 N/A state object free list 14 0.0 N/A 0 0 N/A statistics aggregation 980 0.0 N/A 0 0 N/A temp lob duration state 77 0.0 N/A 0 0 N/A Latch Activity DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ temporary table state ob 332 0.0 N/A 0 0 N/A threshold alerts latch 2,171 0.0 N/A 0 0 N/A transaction allocation 5,021 0.1 0.0 0 0 N/A transaction branch alloc 416 0.0 N/A 0 0 N/A undo global data 129,600 0.0 0.0 0 0 N/A user lock 18,892 0.4 0.0 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- cache buffers chains 37,655,221 180,695 18 180,678 0 0 0 library cache 6,557,882 34,880 4 34,876 0 0 0 shared pool 2,068,113 1,167 29 1,138 0 0 0 slave class create 199 7 9 1 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- active service list ksws_event: ksws event 0 12 12 cache buffers chains kcbgtcr: kslbegin excl 0 17 25 cache buffers chains kcbgtcr: fast path 0 16 7 cache buffers chains kcbrls: kslbegin 0 8 9 cache buffers chains kcbzgb: scan from tail. no 0 1 0 library cache kglpin: child: heap proces 0 1 0 session idle bit ksupuc: set busy 0 1 1 shared pool kghalo 0 19 9 shared pool kghupr1 0 6 15 shared pool kghfrunp: alloc: cursor du 0 2 0 shared pool kghfrunp: clatch: nowait 0 2 0 shared pool kgh: sim resz update 0 1 0 shared pool kghalp 0 1 0 shared pool kghfrunp: clatch: wait 0 1 0 slave class create ksvcreate 0 8 0 slave class create ksvrdp1 0 1 9 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total Logical Reads: 17,299,487 -> Captured Segments account for 78.5% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- QUANTISV4 USERS DEALSUMMARY TABLE 1,408,192 8.14 QUANTISV4 USERS FD_DEALSUMMARY TABLE 1,312,848 7.59 QUANTISV4 USERS EQ_ORDEREXECUTION TABLE 1,309,024 7.57 QUANTISV4 USERS EQ_DEALSUMMARY TABLE 1,122,960 6.49 QUANTISV4 USERS ACCESS_CONTROL TABLE 1,120,656 6.48 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Total Physical Reads: 5,564 -> Captured Segments account for 22.6% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- LMS SYSTEM UK_CLTLEVEL_TRAN_LIM INDEX 647 11.63 SYS SYSTEM IDL_UB1$ TABLE 219 3.94 SYS SYSAUX SYS_IOT_TOP_8797 INDEX 38 .68 SYS SYSAUX WRH$_ACTIVE_SESSION_ 3284_13282 TABLE 24 .43 QUANTISV4 USERS PK_BCM_MARKET_PRICE INDEX 20 .36 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot Row Tablespace Subobject Obj. Lock % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- QUANTISV4 USERS IDX_PK_PL_LOG_SYS INDEX 84 50.91 LMS USERS XPKLMS_PROCESS_LOG INDEX 39 23.64 LMS USERS XPKLMS_PACKET_DETAIL INDEX 36 21.82 LMS USERS XPKLMS_ENTITY_INSTRU INDEX 3 1.82 LMS USERS XPKLMS_LOV_DATA INDEX 2 1.21 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> % of Capture shows % of Buffer Busy Waits for each top segment compared -> with total Buffer Busy Waits for all segments captured by the Snapshot Buffer Tablespace Subobject Obj. Busy % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM DBMS_ALERT_INFO TABLE 7 100.00 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> "Pct Misses" should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 446 0.0 0 N/A 14 1 dc_files 21 33.3 0 N/A 0 7 dc_global_oids 1,012 0.0 0 N/A 0 25 dc_histogram_data 576,201 0.1 0 N/A 0 3,879 dc_histogram_defs 594,242 0.3 0 N/A 0 7,642 dc_object_grants 1,496 1.3 0 N/A 0 489 dc_object_ids 357,275 0.1 0 N/A 0 1,238 dc_objects 93,440 0.2 0 N/A 271 2,596 dc_profiles 4,611 0.0 0 N/A 0 1 dc_rollback_segments 5,089 0.0 0 N/A 0 22 dc_segments 144,748 0.0 0 N/A 257 2,530 dc_sequences 4,235 0.1 0 N/A 4,235 32 dc_tablespace_quotas 45 0.0 0 N/A 45 3 dc_tablespaces 643,504 0.0 0 N/A 0 8 dc_usernames 12,375 0.0 0 N/A 0 10 dc_users 935,955 0.0 0 N/A 0 68 outstanding_alerts 958 9.1 0 N/A 175 18 ------------------------------------------------------------- Library Cache Activity DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 1,046 0.9 5,642 5.1 279 0 CLUSTER 704 0.0 2,271 0.4 9 0 INDEX 326 0.3 688 14.2 97 0 JAVA DATA 5 0.0 0 N/A 0 0 JAVA RESOURCE 5 0.0 15 46.7 5 0 PIPE 156 16.7 186 14.0 0 0 SQL AREA 29,884 22.6 1,468,333 2.6 23,488 1,759 TABLE/PROCEDURE 30,238 1.1 358,824 2.5 5,433 0 TRIGGER 557 2.5 1,641 4.4 59 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> 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 -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B Other 1,075.1 N/A 19.9 19.5 110 110 54 54 SQL .6 .3 .0 .0 0 2 15 10 PL/SQL .2 .0 .0 .0 0 0 52 52 E Other 1,095.6 N/A 17.4 19.1 110 110 63 63 JAVA 6.3 4.0 2.1 .0 2 4 3 3 SQL 1.2 .4 .0 .0 0 44 24 19 PL/SQL .7 .2 .0 .0 0 0 61 61 ------------------------------------------------------------- SGA Memory Summary DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 864,026,624 855,638,016 Fixed Size 1,335,044 Redo Buffers 4,259,840 Variable Size 204,120,316 212,508,924 ------------------- sum 1,073,741,824 ------------------------------------------------------------- SGA breakdown difference DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> ordered by Pool, Name -> N/A 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 free memory 3.0 3.0 -0.77 java joxlod exec hp 12.6 12.6 0.18 java joxs heap .4 .4 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 3.0 3.0 0.00 shared ASH buffers 15.5 15.5 0.00 shared CCursor 9.4 8.9 -5.50 shared CURSOR STATS 1.4 N/A -100.00 shared Heap0: KGL 1.8 N/A -100.00 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGH: NO ACCESS 1.9 1.9 0.00 shared KGLS heap 7.7 8.0 3.23 shared KQR M PO 5.9 5.7 -3.27 shared KQR M SO 3.5 1.7 -52.43 shared KSFD SGA I/O b 3.8 3.8 0.00 shared PCursor 6.7 6.2 -7.15 shared XDB Schema Cac 4.3 4.3 0.00 shared dbwriter coalesce buffer 2.0 2.0 0.00 shared event statistics per sess 1.5 1.5 0.00 shared free memory 3.9 7.2 83.71 shared kglsim hash table bkts 2.0 2.0 0.00 shared kglsim heap 2.5 2.5 0.00 shared kglsim object batch 3.8 3.8 0.00 shared library cache 17.9 19.5 8.88 shared row cache 3.6 3.6 0.00 shared sql area 8.6 15.3 77.56 shared trace buffer 1.5 1.5 0.00 stream KGH: NO ACCESS 4.0 4.0 0.00 stream free memory 8.0 8.0 0.00 buffer_cache 824.0 816.0 -0.97 fixed_sga 1.3 1.3 0.00 log_buffer 4.1 4.1 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Streams processes ordered by CPU usage -> CPU and I/O Time in micro seconds Session Type CPU Time User I/O Time Sys I/O Time ------------------------- -------------- -------------- -------------- QMON Slaves 355,351 442,303 0 QMON Coordinator 261,403 0 0 ------------------------------------------------------------- Streams Capture DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> The Spill Rate should be very close to zero -> The Diff in Percentage Spilled should be very close to zero or negative Incoming Outgoing Spilled Diff Queue Schema and Name per second per second per second Pct Spilled --------------------- ----------- ----------- ----------- ------------ SYS.KUPC$S_1_20091110 0 0 0 0 ------------------------------------------------------------- Buffered Subscribers DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> All Subscribers should have a zero spill rate Incoming Outgoing Spilled Subscriber Name per second per second per second -------------------- ----------- ----------- ----------- KUPC$A_1_20091110111 0 0 0 ------------------------------------------------------------- Rule Set DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 -> Rule Sets ordered by Evaluations Fast SQL CPU Elapsed Ruleset Name Evals Evals Execs Time Time ------------------------- -------- -------- -------- -------- -------- SYS.ALERT_QUE_R 175 0 0 0 0 ------------------------------------------------------------- Resource Limit Stats DB/Inst: DPAMPL/dpampl Snap: 13297 -> 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 ------------------------------ ------------ ------------ ---------- ---------- sessions 101 138 170 170 ------------------------------------------------------------- init.ora Parameters DB/Inst: DPAMPL/dpampl Snaps: 13290-13297 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _kgl_large_heap_warning_thres 8388608 audit_file_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\DP background_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\DP compatible 10.2.0.1.0 control_files E:\ORACLE\PRODUCT\10.2.0\ORADATA\ core_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\DP db_block_size 8192 db_domain db_file_multiblock_read_count 16 db_name dpampl db_recovery_file_dest G:\oracle\product\10.2.0/flash_re db_recovery_file_dest_size 37580963840 db_unique_name DPAMPL dispatchers (PROTOCOL=TCP) (SERVICE=dpamplXDB fal_client DPAMPL fal_server PMSDR job_queue_processes 10 log_archive_config DG_CONFIG=(DPAMPL,PMSDR) log_archive_dest_1 location=F:\Archivefile VALID_FOR log_archive_dest_2 SERVICE=PMSDR LGWR SYNC AFFIRM VA log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE log_archive_max_processes 30 open_cursors 300 pga_aggregate_target 629145600 processes 150 remote_login_passwordfile EXCLUSIVE sga_max_size 1073741824 sga_target 1073741824 standby_file_management AUTO undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\DP ------------------------------------------------------------- End of Report