WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ DESPO 854431149 DESPO 1 10.2.0.4.0 NO fsdespo02 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 27321 01-Feb-11 20:00:32 178 21.9 End Snap: 27322 01-Feb-11 20:15:39 161 22.3 Elapsed: 15.12 (mins) DB Time: 81.59 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 1,296M 1,360M Std Block Size: 8K Shared Pool Size: 1,360M 1,296M Log Buffer: 14,368K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 245,618.69 3,071.95 Logical reads: 39,680.23 496.28 Block changes: 956.49 11.96 Physical reads: 5,302.89 66.32 Physical writes: 109.80 1.37 User calls: 2,787.62 34.86 Parses: 735.90 9.20 Hard parses: 24.77 0.31 Sorts: 276.69 3.46 Logons: 0.21 0.00 Executes: 2,135.67 26.71 Transactions: 79.96 % Blocks changed per Read: 2.41 Recursive Call %: 63.22 Rollback per transaction %: 0.68 Rows per Sort: 990.89 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 86.66 In-memory Sort %: 100.00 Library Hit %: 97.64 Soft Parse %: 96.63 Execute to Parse %: 65.54 Latch Hit %: 99.86 Parse CPU to Parse Elapsd %: 73.67 % Non-Parse CPU: 97.64 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 30.80 25.54 % SQL with executions>1: 70.16 89.95 % Memory for SQL w/exec>1: 69.41 91.23 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- db file sequential read 918,240 2,393 3 48.9 User I/O CPU time 2,004 40.9 db file scattered read 620,109 160 0 3.3 User I/O ARCH wait on SENDREQ 274 60 220 1.2 Network pipe put 84 52 622 1.1 Concurrenc ------------------------------------------------------------- Time Model Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total time in database user-calls (DB Time): 4895.4s -> 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 4,822.0 98.5 DB CPU 2,004.2 40.9 PL/SQL execution elapsed time 437.2 8.9 parse time elapsed 102.9 2.1 hard parse elapsed time 85.4 1.7 PL/SQL compilation elapsed time 7.8 .2 hard parse (sharing criteria) elapsed time 1.9 .0 hard parse (bind mismatch) elapsed time 1.8 .0 sequence load elapsed time 1.2 .0 connection management call elapsed time 1.0 .0 repeated bind elapsed time 0.2 .0 failed parse elapsed time 0.0 .0 DB time 4,895.4 N/A background elapsed time 167.5 N/A background cpu time 20.6 N/A ------------------------------------------------------------- Wait Class DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 -------------------- ---------------- ------ ---------------- ------- --------- User I/O 1,544,400 .0 2,573 2 21.3 Network 2,651,752 .0 70 0 36.6 Concurrency 2,530 9.4 56 22 0.0 System I/O 106,732 .0 49 0 1.5 Commit 29,823 .0 16 1 0.4 Other 736 4.1 2 2 0.0 Configuration 13 .0 0 6 0.0 TRXOication 985 .0 0 0 0.0 ------------------------------------------------------------- Wait Events DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---------------------------- -------------- ------ ----------- ------- --------- db file sequential read 918,240 .0 2,393 3 12.7 db file scattered read 620,109 .0 160 0 8.6 ARCH wait on SENDREQ 274 .0 60 220 0.0 pipe put 84 15.5 52 622 0.0 log file parallel write 75,769 .0 35 0 1.0 log file sync 29,823 .0 16 1 0.4 db file parallel read 565 .0 16 28 0.0 control file sequential read 29,235 .0 7 0 0.4 SQL*Net more data to client 352,265 .0 6 0 4.9 read by other session 3,725 .0 5 1 0.1 Log archive I/O 542 .0 3 6 0.0 SQL*Net more data from clien 17,772 .0 2 0 0.2 log file sequential read 295 .0 2 8 0.0 cursor: pin S wait on X 234 96.6 2 9 0.0 SQL*Net message to client 2,281,441 .0 1 0 31.5 kksfbc child completion 19 94.7 1 45 0.0 control file parallel write 879 .0 1 1 0.0 latch: shared pool 1,096 .0 1 1 0.0 library cache load lock 129 .0 1 4 0.0 os thread startup 3 .0 0 147 0.0 buffer exterminate 2 .0 0 167 0.0 latch free 120 .0 0 2 0.0 latch: library cache 289 .0 0 1 0.0 rdbms ipc reply 110 .0 0 2 0.0 latch: row cache objects 189 .0 0 1 0.0 LGWR wait for redo copy 392 2.3 0 0 0.0 latch: cache buffers lru cha 15 .0 0 5 0.0 log file switch completion 7 .0 0 11 0.0 enq: TX - index contention 14 .0 0 5 0.0 SQL*Net break/reset to clien 964 .0 0 0 0.0 row cache lock 168 .0 0 0 0.0 latch: cache buffers chains 130 .0 0 0 0.0 buffer busy waits 112 .0 0 0 0.0 latch: session allocation 41 .0 0 0 0.0 latch: checkpoint queue latc 2 .0 0 5 0.0 latch: library cache lock 36 .0 0 0 0.0 latch: redo allocation 11 .0 0 0 0.0 enq: TX - row lock contentio 21 .0 0 0 0.0 log file single write 12 .0 0 0 0.0 latch: object queue header o 15 .0 0 0 0.0 log buffer space 5 .0 0 0 0.0 latch: In memory undo latch 32 .0 0 0 0.0 direct path read 911 .0 0 0 0.0 latch: redo writing 1 .0 0 1 0.0 direct path write 850 .0 0 0 0.0 latch: library cache pin 8 .0 0 0 0.0 latch: enqueue hash chains 4 .0 0 0 0.0 latch: messages 1 .0 0 0 0.0 cursor: mutex S 5 .0 0 0 0.0 buffer deadlock 3 100.0 0 0 0.0 cursor: pin S 1 .0 0 0 0.0 library cache pin 1 .0 0 0 0.0 SQL*Net message from client 2,281,462 .0 90,588 40 31.5 Streams AQ: qmn slave idle w 41 .0 902 22009 0.0 Streams AQ: qmn coordinator 96 34.4 902 9400 0.0 jobq slave wait 299 94.0 844 2823 0.0 SGA: MMAN sleep for componen 1,697 99.5 16 9 0.0 class slave wait 1 .0 0 1 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- ARCH wait on SENDREQ 274 .0 60 220 0.0 log file parallel write 75,772 .0 35 0 1.0 Log archive I/O 542 .0 3 6 0.0 log file sequential read 295 .0 2 8 0.0 db file sequential read 914 .0 1 2 0.0 control file parallel write 879 .0 1 1 0.0 os thread startup 3 .0 0 147 0.0 events in waitclass Other 423 2.1 0 0 0.0 control file sequential read 3,799 .0 0 0 0.1 latch: shared pool 14 .0 0 0 0.0 log file single write 12 .0 0 0 0.0 log buffer space 5 .0 0 0 0.0 latch: redo writing 1 .0 0 1 0.0 direct path read 240 .0 0 0 0.0 latch: library cache 2 .0 0 0 0.0 direct path write 159 .0 0 0 0.0 rdbms ipc message 92,227 3.5 13,376 145 1.3 Streams AQ: qmn slave idle w 41 .0 902 22009 0.0 Streams AQ: qmn coordinator 96 34.4 902 9400 0.0 pmon timer 303 100.0 888 2929 0.0 smon timer 12 8.3 858 71476 0.0 SGA: MMAN sleep for componen 1,697 99.5 16 9 0.0 ------------------------------------------------------------- Operating System Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME 30,277 AVG_IDLE_TIME 59,839 AVG_IOWAIT_TIME 28,734 AVG_SYS_TIME 3,723 AVG_USER_TIME 26,523 BUSY_TIME 181,796 IDLE_TIME 359,189 IOWAIT_TIME 172,548 SYS_TIME 22,488 USER_TIME 159,308 LOAD 0 OS_CPU_WAIT_TIME 112,386,766,100 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 0 VM_OUT_BYTES 0 PHYSICAL_MEMORY_BYTES 21,438,169,088 NUM_CPUS 6 NUM_CPU_SOCKETS 6 ------------------------------------------------------------- Service Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- SYS$USERS 4,893.8 2,003.6 4,804,219 ########## DESPO 0.0 0.0 0 0 SYS$BACKGROUND 0.0 0.0 3,329 83,559 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 1540977 256321 2493 5587 0 0 2650488 914 DESPO 0 0 0 0 0 0 0 0 SYS$BACKGROUND 3497 941 29 45 0 0 274 6024 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---------- ---------- ------------ ---------- ------- ------------- 874 40 0 N/A 17.8 fdxajxdfrckcj Module: SQL*Plus begin p_rr_form_totals; end; 770 17 0 N/A 15.7 0k000ypcq35pk Module: SQL*Plus INSERT INTO TEDV_BD.VCTR_SIMP_REQS_DAILY2X SELECT TRXO_YEAR, TO_CHAR (TRUNC (SUB MIT_DATE, 'MM'), 'MM') AS MM, DECODE (SUBSTR (CLAIM_FROM, 1, 1), '', 'W', SUBST R (CLAIM_FROM, 1, 1) ), COUNT (*) AS CNT FROM VCTR_REG_FORM A, VCTR_SIMP_REQS B WHERE A.USER_ID = B.USER_ID AND (ACCT_STATUS IS NULL OR ACCT_STATUS <> 'N') AND 287 252 2,493 0.1 5.9 fh93maz5utuxu Module: w2cm.exe select b.LUMBER_NAME_1, b.sim_txmx_code, b.ls_type_code, a.rank, a.notes, b.AL PHA_NAME, c.report_type, c.active_form, (select jd_cas from (select sim_txmx_co de,jd_cas,rownum from el_trx where (trnx_type='A1' or trnx_type = 'A0') order by trnx_year desc) where rownum = 1 and sim_txmx_code = a.sim_txmx_code) as JD_CAS 253 7 895 0.3 5.2 4hx5tfhbdmjya Module: w2cm.exe Select VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE_3, PIECE_4, PIECE_5 , PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PIECE_12, PIECE_13, PI ECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PIECE_20, PIECE_21_UP, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MO 76 7 3,502 0.0 1.5 f050n5s84mr1f Module: w2cm.exe SELECT ls.LUMBER_NAME_1, ls.alpha_name, ls.LS_TYPE_CODE, caf.sim_txmx_code, ca f.trnx_year, caf.vspc_pgm_term_code, caf.trnx_type, caf.trnx_status, lsa.trnx_de adline, caf.CREATE_DATE, caf.PROCESS_DATE, caf.trnx_ver, caf.last_mod_timestamp, lsa.trnx_name, ltm.vspc_pgm_term_descp, lsp.report_type, elmaddr.address1, lsad 50 12 1 50.1 1.0 94jczr1u0a51x Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2011, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 011 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 48 10 1 47.9 1.0 d66c63882pagx Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2010, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 010 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 44 1 156 0.3 0.9 anr0v8cpj830z Module: w2cm.exe Select VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE_3, PIECE_4, PIECE_5 , PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PIECE_12, PIECE_13, PI ECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PIECE_20, PIECE_21_UP, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MO 40 1 894 0.0 0.8 9z2zfffahkjnn Module: w2cm.exe SELECT ls.madeup_name_1 NAME, ls.sim_txmx_code, caf.trnx_year, caf.vspc _pgm_term_code, caf.trnx_type, la.generate_rr_enddt, lsp.report_type, c.s ervice, (SELECT count(*) FROM rept_req_input_dat rrid, el_trx la, vspc_pg m_term t3 WHERE caf.user_id = rrid.candidate_sequence AND caf.sim_txmx SQL ordered by Elapsed Time DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---------- ---------- ------------ ---------- ------- ------------- 35 34 3,587 0.0 0.7 gyfcf8m4bkpyw Module: w2cm.exe SELECT sim_txmx_code, trnx_year, vspc_pgm_term_code, trnx_type, lor_required, lo r_accepted, lor_recommended, eval_required, eval_accepted from el_trx_LOR_EVAL ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---------- ---------- ------------ ----------- ------- ------------- 252 287 2,493 0.10 5.9 fh93maz5utuxu Module: w2cm.exe select b.LUMBER_NAME_1, b.sim_txmx_code, b.ls_type_code, a.rank, a.notes, b.AL PHA_NAME, c.report_type, c.active_form, (select jd_cas from (select sim_txmx_co de,jd_cas,rownum from el_trx where (trnx_type='A1' or trnx_type = 'A0') order by trnx_year desc) where rownum = 1 and sim_txmx_code = a.sim_txmx_code) as JD_CAS 40 874 0 N/A 17.8 fdxajxdfrckcj Module: SQL*Plus begin p_rr_form_totals; end; 34 35 3,587 0.01 0.7 gyfcf8m4bkpyw Module: w2cm.exe SELECT sim_txmx_code, trnx_year, vspc_pgm_term_code, trnx_type, lor_required, lo r_accepted, lor_recommended, eval_required, eval_accepted from el_trx_LOR_EVAL 17 770 0 N/A 15.7 0k000ypcq35pk Module: SQL*Plus INSERT INTO TEDV_BD.VCTR_SIMP_REQS_DAILY2X SELECT TRXO_YEAR, TO_CHAR (TRUNC (SUB MIT_DATE, 'MM'), 'MM') AS MM, DECODE (SUBSTR (CLAIM_FROM, 1, 1), '', 'W', SUBST R (CLAIM_FROM, 1, 1) ), COUNT (*) AS CNT FROM VCTR_REG_FORM A, VCTR_SIMP_REQS B WHERE A.USER_ID = B.USER_ID AND (ACCT_STATUS IS NULL OR ACCT_STATUS <> 'N') AND 13 15 117 0.11 0.3 35zqk8k8a9pbz Module: dllcl2t.exe select null, owner, decode(package_name,NULL,object_name,package_name||'.'||obje ct_name), decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))), decode(da ta_type,'REF CURSOR',3,decode(in_out,'IN',1,'IN/OUT',2,'OUT',decode(argument_nam e,null,5,4),0)), decode(data_type, 'CHAR', 1, 'DATE', 11, 'FLOAT', 8, 'LONG', -1 12 14 157 0.08 0.3 1uku7gksj822n Module: w2cm.exe select b.LUMBER_NAME_1, b.sim_txmx_code, b.ls_type_code, a.rank, a.notes, b.AL PHA_NAME, c.report_type, c.active_form, (select jd_cas from (select sim_txmx_co de,jd_cas,rownum from el_trx where (trnx_type='A1' or trnx_type = 'A0') order by trnx_year desc) where rownum = 1 and sim_txmx_code = a.sim_txmx_code) as JD_CAS 12 50 1 11.70 1.0 94jczr1u0a51x Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2011, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 011 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 10 48 1 9.89 1.0 d66c63882pagx Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2010, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 010 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 10 11 271 0.04 0.2 0u2t3r5kkb7wy Module: dllcl2t.exe select PRINT_DATE,elem_SCH_CODE,PRINTED_FLAG,PROCESS_PC from sim_sch_print_volume s where printed_flag is null and process_pc is null order by print_date,sim_sch _code 9 11 18,460 0.00 0.2 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket SQL ordered by CPU Time DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---------- ---------- ------------ ----------- ------- ------------- 7 76 3,502 0.00 1.5 f050n5s84mr1f Module: w2cm.exe SELECT ls.LUMBER_NAME_1, ls.alpha_name, ls.LS_TYPE_CODE, caf.sim_txmx_code, ca f.trnx_year, caf.vspc_pgm_term_code, caf.trnx_type, caf.trnx_status, lsa.trnx_de adline, caf.CREATE_DATE, caf.PROCESS_DATE, caf.trnx_ver, caf.last_mod_timestamp, lsa.trnx_name, ltm.vspc_pgm_term_descp, lsp.report_type, elmaddr.address1, lsad 7 253 895 0.01 5.2 4hx5tfhbdmjya Module: w2cm.exe Select VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE_3, PIECE_4, PIECE_5 , PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PIECE_12, PIECE_13, PI ECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PIECE_20, PIECE_21_UP, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MO ------------------------------------------------------------- SQL ordered by Gets DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 35,989,213 -> Captured SQL account for 32.8% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 5,387,136 2,493 2,160.9 15.0 252.45 286.70 fh93maz5utuxu Module: w2cm.exe select b.LUMBER_NAME_1, b.sim_txmx_code, b.ls_type_code, a.rank, a.notes, b.AL PHA_NAME, c.report_type, c.active_form, (select jd_cas from (select sim_txmx_co de,jd_cas,rownum from el_trx where (trnx_type='A1' or trnx_type = 'A0') order by trnx_year desc) where rownum = 1 and sim_txmx_code = a.sim_txmx_code) as JD_CAS 825,683 272,254 3.0 2.3 5.64 5.64 9zjvws607knmw Module: dllcl2t.exe SELECT DISTINCT(ETH_CODE) FROM VCTR_COLOR2B WHERE VCTR_SEQ=:B1 700,435 3,502 200.0 1.9 7.41 75.50 f050n5s84mr1f Module: w2cm.exe SELECT ls.LUMBER_NAME_1, ls.alpha_name, ls.LS_TYPE_CODE, caf.sim_txmx_code, ca f.trnx_year, caf.vspc_pgm_term_code, caf.trnx_type, caf.trnx_status, lsa.trnx_de adline, caf.CREATE_DATE, caf.PROCESS_DATE, caf.trnx_ver, caf.last_mod_timestamp, lsa.trnx_name, ltm.vspc_pgm_term_descp, lsp.report_type, elmaddr.address1, lsad 681,758 0 N/A 1.9 40.28 873.62 fdxajxdfrckcj Module: SQL*Plus begin p_rr_form_totals; end; 562,229 271 2,074.6 1.6 9.69 10.69 0u2t3r5kkb7wy Module: dllcl2t.exe select PRINT_DATE,elem_SCH_CODE,PRINTED_FLAG,PROCESS_PC from sim_sch_print_volume s where printed_flag is null and process_pc is null order by print_date,sim_sch _code 514,859 73,531 7.0 1.4 7.50 7.50 5y41skz7pa18m Module: RealtimeEventsManager.exe select evs.event_number from evp_status evs where evs.event_status=:V00001 and(e vs.event_id in (select evq.event_id from event_que evq where evq.event_type=:V00 002 and evq.event_subtype=:V00003 and evq.ENABLED=:V00004)) order by evs.event_n umber asc 492,746 0 N/A 1.4 16.53 770.17 0k000ypcq35pk Module: SQL*Plus INSERT INTO TEDV_BD.VCTR_SIMP_REQS_DAILY2X SELECT TRXO_YEAR, TO_CHAR (TRUNC (SUB MIT_DATE, 'MM'), 'MM') AS MM, DECODE (SUBSTR (CLAIM_FROM, 1, 1), '', 'W', SUBST R (CLAIM_FROM, 1, 1) ), COUNT (*) AS CNT FROM VCTR_REG_FORM A, VCTR_SIMP_REQS B WHERE A.USER_ID = B.USER_ID AND (ACCT_STATUS IS NULL OR ACCT_STATUS <> 'N') AND 464,186 1,481 313.4 1.3 4.15 17.05 4qms9h399jwu9 Module: w2cm.exe SELECT ls.madeup_name_1, caf.sim_txmx_code, caf.vspc_pgm_term_code, lpt.vspc_p gm_term_descp, caf.trnx_year, caf.trnx_type, CASE WHEN instr(la le.lor_option_code,'C') > 0 Then 0 Else nvl(lale.eval_accepted, 0) END AS num_accepted, (SELECT COUNT (*) FROM cand_eval_schoo 319,158 3,587 89.0 0.9 34.32 35.43 gyfcf8m4bkpyw Module: w2cm.exe SELECT sim_txmx_code, trnx_year, vspc_pgm_term_code, trnx_type, lor_required, lo r_accepted, lor_recommended, eval_required, eval_accepted from el_trx_LOR_EVAL 270,468 157 1,722.7 0.8 12.47 14.05 1uku7gksj822n Module: w2cm.exe select b.LUMBER_NAME_1, b.sim_txmx_code, b.ls_type_code, a.rank, a.notes, b.AL PHA_NAME, c.report_type, c.active_form, (select jd_cas from (select sim_txmx_co de,jd_cas,rownum from el_trx where (trnx_type='A1' or trnx_type = 'A0') order by trnx_year desc) where rownum = 1 and sim_txmx_code = a.sim_txmx_code) as JD_CAS SQL ordered by Gets DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 35,989,213 -> Captured SQL account for 32.8% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- ------------------------------------------------------------- SQL ordered by Reads DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Disk Reads: 4,809,619 -> Captured SQL account for 7.9% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 436,300 0 N/A 9.1 40.28 873.62 fdxajxdfrckcj Module: SQL*Plus begin p_rr_form_totals; end; 279,483 0 N/A 5.8 16.53 770.17 0k000ypcq35pk Module: SQL*Plus INSERT INTO TEDV_BD.VCTR_SIMP_REQS_DAILY2X SELECT TRXO_YEAR, TO_CHAR (TRUNC (SUB MIT_DATE, 'MM'), 'MM') AS MM, DECODE (SUBSTR (CLAIM_FROM, 1, 1), '', 'W', SUBST R (CLAIM_FROM, 1, 1) ), COUNT (*) AS CNT FROM VCTR_REG_FORM A, VCTR_SIMP_REQS B WHERE A.USER_ID = B.USER_ID AND (ACCT_STATUS IS NULL OR ACCT_STATUS <> 'N') AND 95,305 1 95,305.0 2.0 11.70 50.10 94jczr1u0a51x Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2011, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 011 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 66,226 895 74.0 1.4 6.61 253.29 4hx5tfhbdmjya Module: w2cm.exe Select VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE_3, PIECE_4, PIECE_5 , PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PIECE_12, PIECE_13, PI ECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PIECE_20, PIECE_21_UP, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MO 50,612 1 50,612.0 1.1 9.89 47.89 d66c63882pagx Module: SQL*Plus INSERT INTO RR_YEAR_TOTAL_organ_EAPPS (elem_tabl_CODE, TRXO_YEAR, TOTAL_EAPP_COU NT) SELECT A.elem_tabl_CODE, 2010, COUNT (DISTINCT (A.VCTRIDATE_SEQUENCE)) "cntea pps" FROM REPT_REQ_INPUT_DAT A, VCTR_SIMP_REQS B WHERE A.symbio_CLASS_YEAR = 2 010 AND A.symbio_CLASS_YEAR = B.TRXO_YEAR AND A.VCTRIDATE_SEQUENCE = B.USER_ID 23,852 12 1,987.7 0.5 7.96 8.13 g0p3h46qwcqz2 Module: w2cm.exe Select EVALUATION_SEQ, EVALUATION_CLAIM_SEQ, EVAL_RESPONSE_ID, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From VCTR_EVAL_RESPONSE Where evaluation_seq = 310018720016229636 and evaluation_sub mit_seq = 1 Order By EVALUATION_SEQ asc, EVALUATION_CLAIM_SEQ asc, EVAL_RESPONS 19,763 3,502 5.6 0.4 7.41 75.50 f050n5s84mr1f Module: w2cm.exe SELECT ls.LUMBER_NAME_1, ls.alpha_name, ls.LS_TYPE_CODE, caf.sim_txmx_code, ca f.trnx_year, caf.vspc_pgm_term_code, caf.trnx_type, caf.trnx_status, lsa.trnx_de adline, caf.CREATE_DATE, caf.PROCESS_DATE, caf.trnx_ver, caf.last_mod_timestamp, lsa.trnx_name, ltm.vspc_pgm_term_descp, lsp.report_type, elmaddr.address1, lsad 11,247 156 72.1 0.2 1.29 43.91 anr0v8cpj830z Module: w2cm.exe Select VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE_3, PIECE_4, PIECE_5 , PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PIECE_12, PIECE_13, PI ECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PIECE_20, PIECE_21_UP, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MO 10,411 4,868 2.1 0.2 4.16 32.34 0nw7y3ynkby9s Module: w2cm.exe Select USER_ID, elem_tabl_CODE, TRXO_YEAR, vspc_PGM_TERM_CODE, DOC_SRNO, TRXO_TYP E, DOC_DATA, TMPL_ID, DOC_TITLE, DOC_STATUS, CONVERTED_DATA, CREATED_USERID, CRE ATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From CA ND_TRXO_DOC Where user_id = :var0 Order By DOC_SRNO SQL ordered by Reads DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Disk Reads: 4,809,619 -> Captured SQL account for 7.9% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 9,831 10 983.1 0.2 6.47 6.71 5dyjghu47gju3 Module: w2cm.exe Select EVALUATION_SEQ, EVALUATION_CLAIM_SEQ, EVAL_RESPONSE_ID, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From VCTR_EVAL_RESPONSE Where evaluation_seq = 311357520016245426 and evaluation_sub mit_seq = 1 Order By EVALUATION_SEQ asc, EVALUATION_CLAIM_SEQ asc, EVAL_RESPONS ------------------------------------------------------------- SQL ordered by Executions DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Executions: 1,937,008 -> Captured SQL account for 30.3% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 272,254 308,489 1.1 0.00 0.00 9zjvws607knmw Module: dllcl2t.exe SELECT DISTINCT(ETH_CODE) FROM VCTR_COLOR2B WHERE VCTR_SEQ=:B1 73,531 63 0.0 0.00 0.00 5y41skz7pa18m Module: RealtimeEventsManager.exe select evs.event_number from evp_status evs where evs.event_status=:V00001 and(e vs.event_id in (select evq.event_id from event_que evq where evq.event_type=:V00 002 and evq.event_subtype=:V00003 and evq.ENABLED=:V00004)) order by evs.event_n umber asc 18,460 333,685 18.1 0.00 0.00 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 14,411 12,409 0.9 0.00 0.00 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 14,294 16,229 1.1 0.00 0.00 53saa2zkr6wc3 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 10,897 10,897 1.0 0.00 0.00 0j6vrrmy6cn26 Module: w2cm.exe Select C.VCTR_SEQ, C.ACT_HIST, C.AM_BAR_ASSOC, C.BATCH_NUM, C.BIRTH_DATE, C.CANA DIAN_NONE, C.CANAD_CITIZEN, C.VCTR_AGE, decode(cffn.first_name,'',c.cand_first_n ame,cffn.first_name) as VCTR_FIRST_NAME, C.VCTR_FULL_NAME, C.VCTR_LAST_NAME, C.C AND_MID_INIT, C.VCTR_PREV_NAME, C.VCTR_REF_RESP, C.VCTR_REG_DATE, C.VCTR_REG_NUM 9,164 307 0.0 0.00 0.00 9g2wybkwswwcn Module: w2cm.exe Select VCTR_SEQ, ORDER_SEQ, AMOUNT, BATCH_NUM, idelem_NUM, idelem_TYPE, idelem_EXP, idelem_AUTH, idelem_ORIGID, INTERNAL_ERROR, STATUS, TAXES, CREATED_USERID, CREATE D_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From ONLIN E_HEADER Where VCTR_SEQ = :var0 Order By VCTR_SEQ asc, ORDER_SEQ asc 8,941 5,706 0.6 0.00 0.00 f5xn24bzhpsvh Module: w2cm.exe Select VCTR_SEQ, ORDER_SEQ, PUB_ABBREV, QUANTITY, TEST_DATE, PREV_DATE, TEST_CTR _CODE, PREV_CTR_CODE, UGRAD, HOLD_HRS, REG_COMPLETE, LATE_FEE, WAIVER, CANADIAN_ CTR, ZIPCODE, FEE, FEE_DATE, SIGNATURE, SIGNATURE_DATE, CLAIM_FROM, ONLINE_FEE, E_SIGNATURE, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMES 8,547 7,803 0.9 0.00 0.00 gadacwtd043xg Module: w2cm.exe Select VCTR_SEQ, organ_CODE, TYPE, NAME, GEO_AREA, LOCATION, CITY, MISC, ATTEND ED_FROM, ATTENDED_TO, DEGREE_DATE, DEGREE_CODE, DEGREE, MAJOR_CODE, MAJOR, MAJOR 2_CODE, MAJOR2, tbl2_LEVEL, FIN_OBL, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD _USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From ONLINE_organ Where VCTR_S 7,778 7,724 1.0 0.00 0.00 dppa0grwggpwp Module: w2cm.exe Select USER_ID, PASSWORD, VCTR_SEQ, PWD_ACCESS_QUESTION, PWD_ACCESS_ANSWER, FAMI LY_NAME, BIRTH_CITY, idelem_NUM, idelem_TYPE, idelem_EXP, idelem_HOLDER, LOCKED, ORD ER_SEQ, LOGGED_IN, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_ TIMESTAMP, LAST_MOD_SESSIONID From ONLINE_USER Where USER_ID = :var0 Order By U ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Parse Calls: 667,447 -> Captured SQL account for 50.8% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 147,062 73,531 22.03 5y41skz7pa18m Module: RealtimeEventsManager.exe select evs.event_number from evp_status evs where evs.event_status=:V00001 and(e vs.event_id in (select evq.event_id from event_que evq where evq.event_type=:V00 002 and evq.event_subtype=:V00003 and evq.ENABLED=:V00004)) order by evs.event_n umber asc 10,897 10,897 1.63 0j6vrrmy6cn26 Module: w2cm.exe Select C.VCTR_SEQ, C.ACT_HIST, C.AM_BAR_ASSOC, C.BATCH_NUM, C.BIRTH_DATE, C.CANA DIAN_NONE, C.CANAD_CITIZEN, C.VCTR_AGE, decode(cffn.first_name,'',c.cand_first_n ame,cffn.first_name) as VCTR_FIRST_NAME, C.VCTR_FULL_NAME, C.VCTR_LAST_NAME, C.C AND_MID_INIT, C.VCTR_PREV_NAME, C.VCTR_REF_RESP, C.VCTR_REG_DATE, C.VCTR_REG_NUM 9,164 9,164 1.37 9g2wybkwswwcn Module: w2cm.exe Select VCTR_SEQ, ORDER_SEQ, AMOUNT, BATCH_NUM, idelem_NUM, idelem_TYPE, idelem_EXP, idelem_AUTH, idelem_ORIGID, INTERNAL_ERROR, STATUS, TAXES, CREATED_USERID, CREATE D_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From ONLIN E_HEADER Where VCTR_SEQ = :var0 Order By VCTR_SEQ asc, ORDER_SEQ asc 8,941 8,941 1.34 f5xn24bzhpsvh Module: w2cm.exe Select VCTR_SEQ, ORDER_SEQ, PUB_ABBREV, QUANTITY, TEST_DATE, PREV_DATE, TEST_CTR _CODE, PREV_CTR_CODE, UGRAD, HOLD_HRS, REG_COMPLETE, LATE_FEE, WAIVER, CANADIAN_ CTR, ZIPCODE, FEE, FEE_DATE, SIGNATURE, SIGNATURE_DATE, CLAIM_FROM, ONLINE_FEE, E_SIGNATURE, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_TIMES 8,547 8,547 1.28 gadacwtd043xg Module: w2cm.exe Select VCTR_SEQ, organ_CODE, TYPE, NAME, GEO_AREA, LOCATION, CITY, MISC, ATTEND ED_FROM, ATTENDED_TO, DEGREE_DATE, DEGREE_CODE, DEGREE, MAJOR_CODE, MAJOR, MAJOR 2_CODE, MAJOR2, tbl2_LEVEL, FIN_OBL, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD _USERID, LAST_MOD_TIMESTAMP, LAST_MOD_SESSIONID From ONLINE_organ Where VCTR_S 7,778 7,778 1.17 dppa0grwggpwp Module: w2cm.exe Select USER_ID, PASSWORD, VCTR_SEQ, PWD_ACCESS_QUESTION, PWD_ACCESS_ANSWER, FAMI LY_NAME, BIRTH_CITY, idelem_NUM, idelem_TYPE, idelem_EXP, idelem_HOLDER, LOCKED, ORD ER_SEQ, LOGGED_IN, CREATED_USERID, CREATED_TIMESTAMP, LAST_MOD_USERID, LAST_MOD_ TIMESTAMP, LAST_MOD_SESSIONID From ONLINE_USER Where USER_ID = :var0 Order By U 7,645 7,645 1.15 9167vkgrntq2x Module: w2cm.exe Select USER_ID, elem_tabl_CODE, TRXO_YEAR, vspc_PGM_TERM_CODE, TRXO_TYPE, TRXO_DA TA, CREATE_DATE, CLAIM_DATE, TRXO_STATUS, CLAIM_FROM, PROCESS_DATE, PRINT_DATE , EXTRACT_FLAG, TRXO_FEE, FEE_DATE, TRXO_SIGNATURE, SIGNATURE_DATE, ONLINE_FEE, E_SIGNATURE, TRXO_VER, EXTRACT_AP_FLAG, EXTRACT_AR_FLAG, FEE_WAIVER, TIME_REPORT 7,200 7,200 1.08 gacuwun1w5y2t Module: w2cm.exe SELECT a.tbl2_kp_seq, a.tbl2_CODE, a.SUFFIX_TEXT, a.FIRST_NAME, a.LAST_NAME, a.M IDDLE_INITIAL, a.PREFIX_TEXT, a.EMAIL_ADDRESS, a.JOB_TITLE_TEXT,a.SUPRV_ACT_UPD , a.URL, a.SUPPRESSING, b.phone_type_code, b.area_code , b.phone_no, nvl(b.extn, 0) as extn, c.tbl2_kp_attr_type_code, d.name as tbl2_name FROM tbl2_KEY_PERSON a 5,828 5,828 0.87 47r1y8yn34jmj select default$ from col$ where rowid=:1 5,212 2,606 0.78 3b1hj0kjppsgv SQL ordered by Parse Calls DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Parse Calls: 667,447 -> Captured SQL account for 50.8% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- Module: eTBLDaemon.exe Select xx.* from (select /*+ index(VCTR_SIMP_REQS XIF8VCTR_SIMP_REQS) */ * from cand_trnx_form where package_status=:V00001 order by last_mod_timestamp asc) xx where rownum < :V00002 ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- 1,860,214 30 0.14 aykvshm7zsabd select size_for_estimate, size_factor * 100 f, estd_physical_read_time, estd_physical_reads from v$db_cache_advice where id = '3' ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 904 2,433 7qvtbbhny16tu Module: w2cm.exe INSERT INTO VCTR_HISTORY ( VCTR_SEQ, TXN_DATE, TXN_TIME, PIECE_1, PIECE_2, PIECE _3, PIECE_4, PIECE_5, PIECE_6, PIECE_7, PIECE_8, PIECE_9, PIECE_10, PIECE_11, PI ECE_12, PIECE_13, PIECE_14, PIECE_15, PIECE_16, PIECE_17, PIECE_18, PIECE_19, PI ECE_20, PIECE_21_UP ) VALUES ( :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , 232 1,828 75sfn99m29nug Module: w2cm.exe begin P_VCTR_SIMP_REQS_VBI(IO_LAST_MOD_TIMESTAMP=>:IO_LAST_MOD_TIMESTAMP, I_MODE =>:I_MODE, IO_USER_ID=>:IO_USER_ID, IO_elem_tabl_CODE=>:IO_elem_tabl_CODE, IO_TRXO _YEAR=>:IO_TRXO_YEAR, IO_vspc_PGM_TERM_CODE=>:IO_vspc_PGM_TERM_CODE, IO_TRXO_TYP E=>:IO_TRXO_TYPE, I_TRXO_DATA=>:I_TRXO_DATA, I_CREATE_DATE=>:I_CREATE_DATE, I_SU 204 584 6xvc0ah110asp Module: aspnet_wp.exe begin P_VCTR_HISTORY_INS_C(:V00001,:V00002,:V00003,:V00004,:V00005,:V00006,:V000 07,:V00008,:V00009,:V00010,:V00011,:V00012,:V00013,:V00014,:V00015,:V00016,:V000 17,:V00018,:V00019,:V00020,:V00021,:V00022,:V00023,:V00024); end; 182 1,886 5cfba2d7mj550 Module: w2cm.exe begin P_ONLINE_HISTORY_INS_C(IO_VCTR_SEQ=>:IO_VCTR_SEQ, IO_TXN_DATE=>:IO_TXN_DAT E, IO_TXN_TIME=>:IO_TXN_TIME, I_SESSION_ID=>:I_SESSION_ID, I_ORDER_SEQ=>:I_ORDER _SEQ, I_TXN_CODE=>:I_TXN_CODE, I_USER_ID=>:I_USER_ID, I_MISC_1=>:I_MISC_1, I_MIS C_2=>:I_MISC_2, I_MISC_3=>:I_MISC_3); end; 49 1,692 8z540rsvan97g Module: w2cm.exe UPDATE VCTR_SIMP_REQS SET USER_ID = :B6 , elem_tabl_CODE = :B5 , TRXO_YEAR = :B4 , vspc_PGM_TERM_CODE = :B3 , TRXO_TYPE = :B2 , TRXO_DATA = :B31 , CREATE_DATE = :B30 , CLAIM_DATE = :B29 , TRXO_STATUS = :B28 , CLAIM_FROM = :B27 , PROCESS_DA TE = :B26 , PRINT_DATE = :B25 , EXTRACT_FLAG = :B24 , TRXO_FEE = :B23 , FEE_DATE 31 174 3vpuz0g5vnukd Module: Candserv.exe BEGIN vspc_26.RELEASE_LOCK(:1); END; 30 7,159 04xtrk7uyhknh select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an d linkname is null and subname is null 26 4,724 2ym6hhaq30r73 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) fro m seg$ where ts#=:1 and file#=:2 and block#=:3 ------------------------------------------------------------- Instance Activity Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 147,410 162.5 2.0 CPU used when call started 107,440 118.5 1.5 CR blocks created 1,954 2.2 0.0 Cached Commit SCN referenced 11,730 12.9 0.2 Commit SCN cached 1 0.0 0.0 DB time 424,428 468.0 5.9 DBWR checkpoint buffers written 6,135 6.8 0.1 DBWR checkpoints 3 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR revisited being-written buf 0 0.0 0.0 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 1,705 1.9 0.0 DBWR transaction table writes 43 0.1 0.0 DBWR undo block writes 8,112 8.9 0.1 IMU CR rollbacks 511 0.6 0.0 IMU Flushes 808 0.9 0.0 IMU Redo allocation size 17,086,904 18,839.3 235.6 IMU commits 64,448 71.1 0.9 IMU contention 164 0.2 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 6,614 7.3 0.1 IMU recursive-transaction flush 5 0.0 0.0 IMU undo allocation size 234,270,728 258,297.3 3,230.5 IMU- failed to get a private str 6,614 7.3 0.1 Misses for writing mapping 0 0.0 0.0 SMON posted for dropping temp se 0 0.0 0.0 SMON posted for undo segment rec 0 0.0 0.0 SMON posted for undo segment shr 1 0.0 0.0 SQL*Net roundtrips to/from clien 2,281,120 2,515.1 31.5 SQL*Net roundtrips to/from dblin 0 0.0 0.0 active txn count during cleanout 2,811 3.1 0.0 trnxication wait time 6 0.0 0.0 background checkpoints completed 2 0.0 0.0 background checkpoints started 3 0.0 0.0 background timeouts 2,926 3.2 0.0 branch node splits 3 0.0 0.0 buffer is not pinned count 13,179,867 14,531.6 181.8 buffer is pinned count 12,571,571 13,860.9 173.4 bytes received via SQL*Net from 296,285,793 326,672.6 4,085.7 bytes received via SQL*Net from 0 0.0 0.0 bytes sent via SQL*Net to client 1,298,130,426 1,431,265.3 17,900.8 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgs 2,382,618 2,627.0 32.9 calls to kcmgas 101,143 111.5 1.4 calls to kcmgcs 2,456 2.7 0.0 change write time 409 0.5 0.0 cleanout - number of ktugct call 7,966 8.8 0.1 cleanouts and rollbacks - consis 453 0.5 0.0 cleanouts only - consistent read 3,347 3.7 0.1 cluster key scan block gets 642,310 708.2 8.9 cluster key scans 55,957 61.7 0.8 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 19 0.0 0.0 commit batch/immediate requested 19 0.0 0.0 commit cleanout failures: block 68 0.1 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callba 4,551 5.0 0.1 commit cleanout failures: cannot 174 0.2 0.0 commit cleanouts 262,338 289.2 3.6 Instance Activity Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit cleanouts successfully co 257,545 284.0 3.6 commit immediate performed 19 0.0 0.0 commit immediate requested 19 0.0 0.0 commit nowait performed 0 0.0 0.0 commit nowait requested 0 0.0 0.0 commit txn count during cleanout 38,528 42.5 0.5 commit wait/nowait performed 0 0.0 0.0 commit wait/nowait requested 0 0.0 0.0 concurrency wait time 5,647 6.2 0.1 consistent changes 37,352 41.2 0.5 consistent gets 34,933,854 38,516.6 481.7 consistent gets - examination 13,486,723 14,869.9 186.0 consistent gets direct 0 0.0 0.0 consistent gets from cache 34,933,854 38,516.6 481.7 current blocks converted for CR 91 0.1 0.0 cursor authentications 8,953 9.9 0.1 data blocks consistent reads - u 4,857 5.4 0.1 db block changes 867,519 956.5 12.0 db block gets 1,055,335 1,163.6 14.6 db block gets direct 8,262 9.1 0.1 db block gets from cache 1,047,073 1,154.5 14.4 deferred (CURRENT) block cleanou 120,837 133.2 1.7 dirty buffers inspected 60,048 66.2 0.8 doubling up with imu segment 0 0.0 0.0 enqueue conversions 4,060 4.5 0.1 enqueue releases 255,716 281.9 3.5 enqueue requests 255,717 281.9 3.5 enqueue timeouts 7 0.0 0.0 enqueue waits 34 0.0 0.0 exchange deadlocks 3 0.0 0.0 execute count 1,937,008 2,135.7 26.7 failed probes on index block rec 8 0.0 0.0 frame signature mismatch 0 0.0 0.0 free buffer inspected 4,842,468 5,339.1 66.8 free buffer requested 4,822,013 5,316.6 66.5 heap block compress 8,591 9.5 0.1 hot buffers moved to head of LRU 307,904 339.5 4.3 immediate (CR) block cleanout ap 3,800 4.2 0.1 immediate (CURRENT) block cleano 14,032 15.5 0.2 index crx upgrade (found) 6 0.0 0.0 index crx upgrade (positioned) 282,280 311.2 3.9 index fast full scans (full) 75 0.1 0.0 index fetch by key 4,481,023 4,940.6 61.8 index scans kdiixs1 1,867,980 2,059.6 25.8 leaf node 90-10 splits 49 0.1 0.0 leaf node splits 822 0.9 0.0 lob reads 819 0.9 0.0 lob writes 94 0.1 0.0 lob writes unaligned 94 0.1 0.0 logons cumulative 194 0.2 0.0 messages received 96,391 106.3 1.3 messages sent 96,391 106.3 1.3 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 20,560,715 22,669.4 283.5 opened cursors cumulative 614,497 677.5 8.5 parse count (failures) 26 0.0 0.0 parse count (hard) 22,463 24.8 0.3 parse count (total) 667,447 735.9 9.2 parse time cpu 4,738 5.2 0.1 parse time elapsed 6,431 7.1 0.1 Instance Activity Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical read IO requests 1,547,475 1,706.2 21.3 physical read bytes 39,400,464,384 43,441,333.8 543,319.8 physical read total IO requests 1,580,349 1,742.4 21.8 physical read total bytes 41,629,357,056 45,898,819.3 574,055.5 physical read total multi block 623,300 687.2 8.6 physical reads 4,809,619 5,302.9 66.3 physical reads cache 4,799,314 5,291.5 66.2 physical reads cache prefetch 3,262,540 3,597.1 45.0 physical reads direct 10,305 11.4 0.1 physical reads direct (lob) 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 66,600 73.4 0.9 physical write bytes 815,792,128 899,458.9 11,249.5 physical write total IO requests 221,396 244.1 3.1 physical write total bytes 1,622,069,248 1,788,426.9 22,367.8 physical write total multi block 111,090 122.5 1.5 physical writes 99,584 109.8 1.4 physical writes direct 18,487 20.4 0.3 physical writes direct (lob) 11 0.0 0.0 physical writes from cache 81,097 89.4 1.1 physical writes non checkpoint 96,576 106.5 1.3 pinned buffers inspected 2,835 3.1 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 0 0.0 0.0 process last non-idle time 905 1.0 0.0 recursive calls 4,345,130 4,790.8 59.9 recursive cpu usage 49,106 54.1 0.7 redo blocks written 246,378 271.7 3.4 redo buffer allocation retries 6 0.0 0.0 redo entries 190,893 210.5 2.6 redo log space requests 7 0.0 0.0 redo log space wait time 7 0.0 0.0 redo ordering marks 3,429 3.8 0.1 redo size 222,771,488 245,618.7 3,072.0 redo subscn max counts 9,421 10.4 0.1 redo synch time 1,672 1.8 0.0 redo synch writes 30,327 33.4 0.4 redo wastage 25,577,480 28,200.7 352.7 redo write time 3,491 3.9 0.1 redo writer latching time 10 0.0 0.0 redo writes 75,838 83.6 1.1 rollback changes - undo records 53 0.1 0.0 rollbacks only - consistent read 1,446 1.6 0.0 rows fetched via callback 3,677,432 4,054.6 50.7 session connect time 0 0.0 0.0 session cursor cache hits 299,108 329.8 4.1 session logical reads 35,989,213 39,680.2 496.3 session pga memory max 600,662,280 662,265.6 8,282.9 session uga memory 85,818,233,272 94,619,659.4 1,183,406.0 session uga memory max 438,999,760 484,023.1 6,053.7 shared hash latch upgrades - no 547,092 603.2 7.5 shared hash latch upgrades - wai 33 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 250,953 276.7 3.5 sorts (rows) 248,667,751 274,170.9 3,429.1 sql area evicted 22,182 24.5 0.3 sql area purged 26 0.0 0.0 summed dirty queue length 115,271 127.1 1.6 switch current to new buffer 11,090 12.2 0.2 table fetch by rowid 9,990,371 11,015.0 137.8 Instance Activity Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- table fetch continued row 434,919 479.5 6.0 table scan blocks gotten 13,989,600 15,424.4 192.9 table scan rows gotten 1,044,412,832 1,151,526.7 14,402.1 table scans (cache partitions) 0 0.0 0.0 table scans (direct read) 0 0.0 0.0 table scans (long tables) 498 0.6 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 154,303 170.1 2.1 total number of times SMON poste 11 0.0 0.0 transaction rollbacks 19 0.0 0.0 transaction tables consistent re 14 0.0 0.0 transaction tables consistent re 31,853 35.1 0.4 undo change vector size 56,611,264 62,417.3 780.7 user I/O wait time 257,323 283.7 3.6 user calls 2,528,320 2,787.6 34.9 user commits 72,024 79.4 1.0 user rollbacks 494 0.5 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 2 0.0 0.0 workarea executions - optimal 159,149 175.5 2.2 write clones created in backgrou 0 0.0 0.0 write clones created in foregrou 72 0.1 0.0 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: DESPO/DESPO Snaps: 27321-273 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 14,749,058 14,752,415 opened cursors current 3,896 3,592 workarea memory allocated 315,448 244,430 logons current 178 161 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: DESPO/DESPO Snaps: 27321-2732 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 3 11.91 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_DATA 1,176,908 1,298 1.4 3.2 5,768 6 853 1.2 TS_INDEX 239,871 264 3.0 1.5 56,760 63 1,322 2.9 TS_LOR_DATA 92,998 103 0.6 6.7 135 0 1,606 0.2 SYSTEM 15,836 17 2.3 1.4 92 0 35 6.0 TS_LOR_IDX 5,981 7 4.2 1.0 307 0 0 0.0 UNDOTBS 4,002 4 1.9 1.0 632 1 15 0.7 TS_DW 2,728 3 3.4 1.0 1,782 2 1 10.0 TS_LLM 3,431 4 1.7 4.6 17 0 0 0.0 SYSAUX 2,952 3 2.3 1.0 388 0 2 0.0 TEMP_B 675 1 4.4 14.9 673 1 0 0.0 TS_LLM_IDX 1,024 1 3.6 1.0 34 0 0 0.0 PERFSTAT 382 0 0.2 1.0 23 0 0 0.0 QUEST_IDX 163 0 8.7 1.3 26 0 0 0.0 QUEST_DATA 137 0 3.9 1.3 20 0 0 0.0 TOOLS 16 0 1.9 1.0 2 0 0 0.0 TS_GUIDE 10 0 5.0 1.1 2 0 0 0.0 TEMP 6 0 0.0 1.0 4 0 0 0.0 TS_GUIDE_IDX 5 0 4.0 1.0 2 0 0 0.0 USERS 2 0 0.0 1.0 2 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ PERFSTAT /elprod/data/DESPO/data/perfstat01.dbf 382 0 0.2 1.0 23 0 0 0.0 QUEST_DATA /elprod/data/DESPO/data/QUEST_DATA1.dbf 137 0 3.9 1.3 20 0 0 0.0 QUEST_IDX /elprod/data/DESPO/idx/QUEST_IDX1.dbf 163 0 8.7 1.3 26 0 0 0.0 SYSAUX /elprod/data/DESPO/data/sysaux01.dbf 2,348 3 2.3 1.0 150 0 0 0.0 SYSAUX /elprod/data/DESPO/data/sysaux02.dbf 604 1 2.1 1.0 238 0 2 0.0 SYSTEM /elprod/data/DESPO/system/system01.dbf 15,836 17 2.3 1.4 92 0 35 6.0 TEMP /elprod/data/DESPO/rbs/tmp001.dbf 4 0 0.0 1.0 2 0 0 0.0 TEMP /elprod/data/DESPO/temp/temp01.dbf 2 0 0.0 1.0 2 0 0 0.0 TEMP_B /elprod/data/DESPO/data/temp_b01.dbf 675 1 4.4 14.9 673 1 0 0.0 TOOLS /elprod/data/DESPO/data/tools01.dbf 16 0 1.9 1.0 2 0 0 0.0 TS_DATA /elprod/data/DESPO/data/data01.dbf 32,455 36 1.1 3.8 585 1 55 3.6 TS_DATA /elprod/data/DESPO/data/data02.dbf 29,685 33 1.0 3.8 92 0 61 1.3 TS_DATA /elprod/data/DESPO/data/data03.dbf 29,370 32 1.0 3.9 105 0 47 2.3 TS_DATA /elprod/data/DESPO/data/data04.dbf 31,664 35 0.9 4.2 436 0 42 2.6 TS_DATA /elprod/data/DESPO/data/data05.dbf 28,088 31 0.9 5.2 237 0 24 9.6 TS_DATA /elprod/data/DESPO/data/data06.dbf 385,331 425 0.1 4.2 63 0 16 0.6 TS_DATA /elprod/data/DESPO/data/data07.dbf 15,233 17 2.3 3.3 201 0 16 1.3 TS_DATA /elprod/data/DESPO/data/data08.dbf 17,812 20 2.8 1.8 96 0 7 1.4 TS_DATA /elprod/data/DESPO/data/data09.dbf 11,242 12 3.3 2.3 66 0 8 0.0 TS_DATA /elprod/data/DESPO/data/data10.dbf 10,158 11 3.0 2.0 56 0 7 0.0 TS_DATA /elprod/data/DESPO/data/data11.dbf 13,617 15 1.9 4.4 26 0 9 0.0 TS_DATA /elprod/data/DESPO/data/data12.dbf 13,474 15 1.6 4.6 15 0 9 1.1 TS_DATA /elprod/data/DESPO/data/data13.dbf 11,348 13 1.0 5.0 40 0 8 0.0 TS_DATA /elprod/data/DESPO/data/data14.dbf 10,979 12 0.9 5.3 13 0 7 1.4 TS_DATA /elprod/data/DESPO/data/data15.dbf 12,348 14 1.0 5.1 15 0 15 0.0 TS_DATA /elprod/data/DESPO/data/data16.dbf 25,009 28 1.7 2.8 50 0 6 0.0 TS_DATA /elprod/data/DESPO/data/data17.dbf 31,522 35 2.1 1.3 95 0 2 5.0 TS_DATA /elprod/data/DESPO/data/data18.dbf 14,050 15 1.2 4.5 45 0 14 0.7 TS_DATA /elprod/data/DESPO/data/data19.dbf 12,962 14 1.2 4.7 42 0 16 1.9 TS_DATA /elprod/data/DESPO/data/data20.dbf 20,560 23 1.4 5.1 58 0 29 0.0 File IO Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_DATA /elprod/data/DESPO/data/data21.dbf 22,040 24 1.6 4.9 72 0 25 0.4 TS_DATA /elprod/data/DESPO/data/data22.dbf 19,670 22 2.5 3.6 104 0 27 0.0 TS_DATA /elprod/data/DESPO/data/data23.dbf 35,847 40 2.5 2.4 95 0 34 0.0 TS_DATA /elprod/data/DESPO/data/data24.dbf 75,117 83 1.5 1.2 99 0 31 0.6 TS_DATA /elprod/data/DESPO/data/data25.dbf 28,888 32 3.6 1.9 147 0 40 0.3 TS_DATA /elprod/data/DESPO/data/data26.dbf 27,273 30 4.0 1.4 197 0 33 0.3 TS_DATA /elprod/data/DESPO/data/data27.dbf 33,042 36 3.8 1.4 333 0 66 0.6 TS_DATA /elprod/data/DESPO/data/data28.dbf 46,600 51 3.1 1.2 603 1 106 0.5 TS_DATA /elprod/data/DESPO/data/data29.dbf 43,389 48 2.9 1.2 716 1 82 0.4 TS_DATA /elprod/data/DESPO/data/data30.dbf 27,302 30 3.0 1.1 386 0 1 0.0 TS_DATA /elprod/data/DESPO/data/data31.dbf 24,059 27 2.7 1.1 544 1 7 0.0 TS_DATA /elprod/data/DESPO/idx/index12.dbf 36,774 41 1.6 2.3 136 0 3 0.0 TS_DW /elprod/data/DESPO/data/datadw01.dbf 1,618 2 3.4 1.0 1,045 1 0 0.0 TS_DW /elprod/data/DESPO/data/datadw02.dbf 1,110 1 3.3 1.0 737 1 1 10.0 TS_GUIDE /elprod/data/DESPO/data/guidedata01.dbf 10 0 5.0 1.1 2 0 0 0.0 TS_GUIDE_IDX /elprod/data/DESPO/idx/guideidx01.dbf 5 0 4.0 1.0 2 0 0 0.0 TS_INDEX /elprod/data/DESPO/idx/index01.dbf 12,733 14 2.6 1.5 2,591 3 60 1.8 TS_INDEX /elprod/data/DESPO/idx/index02.dbf 9,875 11 2.7 1.6 1,665 2 30 2.0 TS_INDEX /elprod/data/DESPO/idx/index03.dbf 12,952 14 2.6 1.5 2,975 3 14 1.4 TS_INDEX /elprod/data/DESPO/idx/index04.dbf 11,974 13 2.5 1.5 2,297 3 34 3.5 TS_INDEX /elprod/data/DESPO/idx/index05.dbf 11,960 13 2.4 1.4 1,926 2 53 2.3 TS_INDEX /elprod/data/DESPO/idx/index06.dbf 5,863 6 2.7 1.8 623 1 26 4.2 TS_INDEX /elprod/data/DESPO/idx/index07.dbf 5,549 6 2.4 1.7 485 1 22 2.3 TS_INDEX /elprod/data/DESPO/idx/index08.dbf 4,408 5 2.4 1.6 379 0 34 2.4 TS_INDEX /elprod/data/DESPO/idx/index09.dbf 6,029 7 2.8 1.8 1,309 1 40 2.8 TS_INDEX /elprod/data/DESPO/idx/index10.dbf 7,020 8 2.8 1.8 1,801 2 34 3.8 TS_INDEX /elprod/data/DESPO/idx/index11.dbf 7,157 8 2.9 1.7 1,764 2 24 3.3 TS_INDEX /elprod/data/DESPO/idx/index13.dbf 7,170 8 2.4 1.8 1,117 1 31 1.9 TS_INDEX /elprod/data/DESPO/idx/index14.dbf 8,038 9 2.9 1.8 1,826 2 59 1.5 TS_INDEX /elprod/data/DESPO/idx/index15.dbf 5,899 7 3.3 1.7 865 1 19 1.1 File IO Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ TS_INDEX /elprod/data/DESPO/idx/index16.dbf 5,101 6 3.2 1.8 213 0 28 3.6 TS_INDEX /elprod/data/DESPO/idx/index17.dbf 5,933 7 3.3 1.7 672 1 50 4.6 TS_INDEX /elprod/data/DESPO/idx/index18.dbf 10,378 11 3.5 1.6 2,400 3 43 3.3 TS_INDEX /elprod/data/DESPO/idx/index19.dbf 8,503 9 3.1 1.7 1,288 1 76 3.3 TS_INDEX /elprod/data/DESPO/idx/index20.dbf 11,145 12 3.7 1.5 2,489 3 68 3.7 TS_INDEX /elprod/data/DESPO/idx/index21.dbf 14,894 16 3.5 1.4 4,013 4 108 3.6 TS_INDEX /elprod/data/DESPO/idx/index22.dbf 18,347 20 3.5 1.2 5,725 6 119 2.7 TS_INDEX /elprod/data/DESPO/idx/index23.dbf 17,316 19 3.2 1.2 5,952 7 156 2.4 TS_INDEX /elprod/data/DESPO/idx/index24.dbf 14,248 16 3.3 1.2 5,180 6 116 3.7 TS_INDEX /elprod/data/DESPO/idx/index25.dbf 11,845 13 3.1 1.1 5,031 6 33 5.2 TS_INDEX /elprod/data/DESPO/idx/index26.dbf 5,534 6 3.5 1.0 2,174 2 45 0.2 TS_LLM /elprod/data/DESPO/data/llmdata01.dbf 3,431 4 1.7 4.6 17 0 0 0.0 TS_LLM_IDX /elprod/data/DESPO/idx/llmidx01.dbf 1,024 1 3.6 1.0 34 0 0 0.0 TS_LOR_DATA /elprod/data/DESPO/data/lor_data01.dbf 92,998 103 0.6 6.7 135 0 1,606 0.2 TS_LOR_IDX /elprod/data/DESPO/idx/lor_idx01.dbf 3,353 4 4.1 1.0 151 0 0 0.0 TS_LOR_IDX /elprod/data/DESPO/idx/lor_idx02.dbf 2,628 3 4.3 1.0 156 0 0 0.0 UNDOTBS /elprod/data/DESPO/data/undotbs01.dbf 950 1 2.4 1.0 209 0 13 0.8 UNDOTBS /elprod/data/DESPO/data/undotbs02.dbf 1,215 1 2.1 1.0 170 0 0 0.0 UNDOTBS /elprod/data/DESPO/data/undotbs03.dbf 1,837 2 1.6 1.0 253 0 2 0.0 USERS /elprod/data/DESPO/data/users01.dbf 2 0 0.0 1.0 2 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 164,764 87 35,955,126 4,792,199 81,055 0 0 3,841 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 21 1726 32177 100000 276480 176625 100000 E 0 27 3558 52370 100000 276480 332486 100000 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: DESPO/DESPO Snap: 27322 -> 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 128 .1 15,832 1.3 10,702,170,810 D 256 .2 31,664 1.2 10,454,206,003 D 384 .3 47,496 1.2 10,208,273,841 D 512 .4 63,328 1.2 9,965,563,151 D 640 .5 79,160 1.1 9,726,778,434 D 768 .6 94,992 1.1 9,492,671,994 D 896 .7 110,824 1.1 9,264,125,642 D 1,024 .8 126,656 1.1 9,053,383,632 D 1,152 .8 142,488 1.0 8,845,894,499 D 1,280 .9 158,320 1.0 8,641,522,365 D 1,360 1.0 168,215 1.0 8,515,588,989 D 1,408 1.0 174,152 1.0 8,440,028,959 D 1,536 1.1 189,984 1.0 8,241,093,596 D 1,664 1.2 205,816 0.9 8,044,334,367 D 1,792 1.3 221,648 0.9 7,849,281,377 D 1,920 1.4 237,480 0.9 7,656,409,335 D 2,048 1.5 253,312 0.9 7,463,563,373 D 2,176 1.6 269,144 0.9 7,270,723,373 D 2,304 1.7 284,976 0.8 7,077,883,335 D 2,432 1.8 300,808 0.8 6,885,043,468 D 2,560 1.9 316,640 0.8 6,692,202,127 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 --------------- ------------------ -------------------------- 98.7 12,946 173 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 1,024 577 787.4 78.4 10.0 100.0 .0 104,850 E 1,024 594 688.4 9.7 1.4 100.0 .0 104,850 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 114,731 114,731 0 0 64K 128K 587 587 0 0 128K 256K 41,465 41,465 0 0 256K 512K 42 42 0 0 512K 1024K 1,586 1,586 0 0 1M 2M 709 709 0 0 2M 4M 10 10 0 0 4M 8M 14 14 0 0 8M 16M 25 25 0 0 16M 32M 12 12 0 0 32M 64M 4 4 0 0 64M 128M 4 2 2 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: DESPO/DESPO Snap: 27322 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 128 0.1 35,750,958.0 1,989,069.7 95.0 270,028 256 0.3 35,750,958.0 1,875,388.7 95.0 246,508 512 0.5 35,750,958.0 114,755.6 100.0 13,531 768 0.8 35,750,958.0 27,178.6 100.0 0 1,024 1.0 35,750,958.0 26,470.7 100.0 0 1,229 1.2 35,750,958.0 15,909.0 100.0 0 1,434 1.4 35,750,958.0 15,909.0 100.0 0 1,638 1.6 35,750,958.0 15,909.0 100.0 0 1,843 1.8 35,750,958.0 15,909.0 100.0 0 2,048 2.0 35,750,958.0 15,909.0 100.0 0 3,072 3.0 35,750,958.0 15,909.0 100.0 0 4,096 4.0 35,750,958.0 15,909.0 100.0 0 6,144 6.0 35,750,958.0 15,909.0 100.0 0 8,192 8.0 35,750,958.0 15,909.0 100.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: DESPO/DESPO Snap: 27322 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 288 .2 164 10,037 ####### .8 ####### 41.3 ########### 432 .3 304 16,389 ####### .9 ####### 35.5 ########### 576 .4 444 23,083 ####### .9 ####### 29.7 ########### 720 .6 584 29,260 ####### .9 ####### 23.9 ########### 864 .7 725 35,976 ####### .9 ####### 18.2 ########### 1,008 .8 868 41,908 ####### 1.0 ####### 12.4 ########### 1,152 .9 1,011 48,220 ####### 1.0 ####### 6.7 ########### 1,296 1.0 1,154 55,204 ####### 1.0 ####### 1.0 ########### 1,440 1.1 1,297 60,763 ####### 1.0 1 .0 ########### 1,584 1.2 1,440 67,717 ####### 1.0 1 .0 ########### 1,728 1.3 1,583 74,742 ####### 1.1 1 .0 ########### 1,872 1.4 1,726 81,922 ####### 1.1 1 .0 ########### 2,016 1.6 1,869 88,256 ####### 1.1 1 .0 ########### 2,160 1.7 2,012 94,397 ####### 1.1 1 .0 ########### 2,304 1.8 2,155 100,885 ####### 1.2 1 .0 ########### 2,448 1.9 2,298 106,891 ####### 1.2 1 .0 ########### 2,592 2.0 2,441 113,476 ####### 1.2 1 .0 ########### ------------------------------------------------------------- SGA Target Advisory DB/Inst: DESPO/DESPO Snap: 27322 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 768 0.3 27,607,936 11,058,255,429 1,536 0.5 17,310,603 11,058,255,429 2,304 0.8 11,969,697 9,809,028,514 3,072 1.0 8,197,860 8,515,520,891 3,840 1.3 4,976,102 7,513,244,082 4,608 1.5 4,714,590 6,915,454,516 5,376 1.8 4,714,590 6,915,454,516 6,144 2.0 4,714,590 6,915,454,516 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: DESPO/DESPO Snap: 27322 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 16 0.5 0 0 0 0 32 1.0 0 0 0 0 48 1.5 0 0 0 0 64 2.0 0 0 0 0 80 2.5 0 0 0 0 96 3.0 0 0 0 0 112 3.5 0 0 0 0 128 4.0 0 0 0 0 144 4.5 0 0 0 0 160 5.0 0 0 0 0 176 5.5 0 0 0 0 192 6.0 0 0 0 0 208 6.5 0 0 0 0 224 7.0 0 0 0 0 240 7.5 0 0 0 0 256 8.0 0 0 0 0 272 8.5 0 0 0 0 288 9.0 0 0 0 0 304 9.5 0 0 0 0 320 10.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: DESPO/DESPO Snap: 27322 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 3,823 5 1 bitmap index block 3 0 3 segment header 1 0 10 undo header 12 0 0 undo block 2 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- TX-Transaction (index contention) 96 96 0 13 0 5.31 TX-Transaction (row lock contention) 21 21 0 21 0 .14 ------------------------------------------------------------- Undo Segment Summary DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ---- ---------- --------------- -------- -------- --------- ----- -------------- 21 12.3 109,757 1,037 7 30/32.35 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 01-Feb 20:23 2,862 22,261 1,037 6 32 0/0 0/0/0/0/0/0 01-Feb 20:13 5,678 56,794 423 7 30 0/0 0/0/0/0/0/0 01-Feb 20:03 3,758 30,702 37 7 30 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ ASM db client latch 790 0.0 N/A 0 0 N/A AWR Alerted Metric Eleme 7,993 0.0 N/A 0 0 N/A Consistent RBA 75,774 0.0 N/A 0 0 N/A FAL request queue 51 0.0 N/A 0 0 N/A FAL subheap alocation 48 0.0 N/A 0 0 N/A FIB s.o chain latch 70 0.0 N/A 0 0 N/A FOB s.o list latch 8,645 0.0 N/A 0 0 N/A In memory undo latch 476,219 0.0 0.7 0 77,552 0.0 JS queue state obj latch 5,460 0.0 N/A 0 0 N/A JS slv state obj latch 3 0.0 N/A 0 0 N/A KMG MMAN ready and start 1,996 0.0 N/A 0 0 N/A KMG resize request state 14 0.0 N/A 0 0 N/A KTF sga latch 2 0.0 N/A 0 304 0.0 KWQP Prop Status 2 0.0 N/A 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 18 0.0 Memory Management Latch 1,837 0.0 N/A 0 1,996 0.0 OS process 588 0.0 N/A 0 0 N/A OS process allocation 679 0.0 N/A 0 0 N/A OS process: request allo 365 0.0 N/A 0 0 N/A PL/SQL warning settings 32,741 0.0 N/A 0 0 N/A SGA IO buffer pool latch 619,213 0.1 0.0 0 619,212 0.1 SQL memory manager latch 2 0.0 N/A 0 302 0.0 SQL memory manager worka 725,756 0.0 0.0 0 0 N/A Shared B-Tree 36 0.0 N/A 0 0 N/A active checkpoint queue 18,882 0.0 N/A 0 0 N/A active service list 2,369 0.0 N/A 0 302 0.0 archive control 412 0.0 N/A 0 0 N/A archive process latch 389 0.3 1.0 0 0 N/A begin backup scn array 390 0.0 N/A 0 0 N/A buffer pool 783 0.0 N/A 0 0 N/A cache buffer handles 58,149 0.0 0.0 0 0 N/A cache buffers chains 67,136,086 0.0 0.0 0 9,883,174 0.0 cache buffers lru chain 238,168 0.6 0.0 0 12,316,616 0.5 cache table scan latch 0 N/A N/A 0 608,473 1.9 channel handle pool latc 385 0.0 N/A 0 0 N/A channel operations paren 5,674 0.0 N/A 0 0 N/A checkpoint queue latch 339,260 0.0 0.2 0 103,719 0.0 client/trnxication info 1,234 0.0 N/A 0 0 N/A commit callback allocati 26 0.0 N/A 0 0 N/A compile environment latc 194 0.0 N/A 0 0 N/A dictionary lookup 7 0.0 N/A 0 0 N/A dml lock allocation 259,949 0.0 0.1 0 0 N/A dummy allocation 404 0.0 N/A 0 0 N/A enqueue hash chains 515,085 0.0 0.1 0 73 0.0 enqueues 250,594 0.0 0.1 0 0 N/A event group latch 175 0.0 N/A 0 0 N/A file cache latch 59 0.0 N/A 0 0 N/A global KZLD latch for me 171 0.0 N/A 0 0 N/A global tx hash mapping 12,625 0.0 N/A 0 0 N/A hash table column usage 587 0.0 N/A 0 531,152 0.0 hash table modification 16 0.0 N/A 0 0 N/A internal temp table obje 6 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 38 0.0 job_queue_processes para 35 0.0 N/A 0 0 N/A kks stats 108,019 0.0 0.2 0 0 N/A kokc descriptor allocati 24 0.0 N/A 0 0 N/A ksuosstats global area 63 0.0 N/A 0 0 N/A ktm global data 14 0.0 N/A 0 0 N/A kwqbsn:qsga 35 0.0 N/A 0 0 N/A lgwr LWN SCN 75,913 0.0 0.0 0 0 N/A Latch Activity DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ library cache 4,558,071 0.1 0.1 0 81,719 121.5 library cache load lock 31,370 0.0 0.0 0 63 0.0 library cache lock 2,779,392 0.0 0.1 0 0 N/A library cache lock alloc 11,354 0.0 N/A 0 0 N/A library cache pin 1,297,212 0.0 0.1 0 6 0.0 library cache pin alloca 7,736 0.0 N/A 0 0 N/A list of block allocation 715 0.0 N/A 0 0 N/A loader state object free 154 0.0 N/A 0 0 N/A logminer context allocat 1 0.0 N/A 0 0 N/A longop free list parent 8 0.0 N/A 0 8 0.0 message pool operations 20 0.0 N/A 0 0 N/A messages 304,585 0.0 0.1 0 0 N/A mostly latch-free SCN 76,375 0.6 0.0 0 0 N/A multiblock read objects 1,467,831 0.0 0.0 0 0 N/A ncodef allocation latch 15 0.0 N/A 0 0 N/A object queue header heap 5,472 0.0 N/A 0 5,129 0.0 object queue header oper 9,859,343 0.0 0.0 0 22,011 0.0 object stats modificatio 147 0.0 N/A 0 0 N/A parallel query alloc buf 112 0.0 N/A 0 0 N/A parameter list 16 0.0 N/A 0 0 N/A parameter table allocati 284 0.0 N/A 0 0 N/A post/wait queue 47,137 0.0 0.0 0 29,847 0.0 process allocation 365 0.0 N/A 0 175 0.0 process group creation 365 0.0 N/A 0 0 N/A qmn task queue latch 206 1.9 0.3 0 0 N/A redo allocation 376,741 0.0 0.2 0 190,800 0.0 redo copy 0 N/A N/A 0 190,820 0.2 redo writing 246,857 0.0 0.1 0 0 N/A resmgr group change latc 248 0.0 N/A 0 0 N/A resmgr:active threads 401 0.0 N/A 0 0 N/A resmgr:actses change gro 190 0.0 N/A 0 0 N/A resmgr:free threads list 400 0.0 N/A 0 0 N/A resmgr:schema config 1 0.0 N/A 0 0 N/A row cache objects 8,621,222 1.3 0.0 0 18,132 0.4 rules engine rule set st 100 0.0 N/A 0 0 N/A sequence cache 8,647 0.0 N/A 0 0 N/A session allocation 1,646,491 0.2 0.0 0 0 N/A session idle bit 5,211,594 0.0 0.1 0 0 N/A session state list latch 484 0.0 N/A 0 0 N/A session switching 15 0.0 N/A 0 0 N/A session timer 302 0.0 N/A 0 0 N/A shared pool 3,095,886 0.2 0.2 1 0 N/A shared pool simulator 3,036,252 0.0 0.1 0 0 N/A simulator hash latch 3,415,408 0.0 0.0 0 0 N/A simulator lru latch 81,566 0.1 0.0 0 2,527,935 0.2 slave class 2 0.0 N/A 0 0 N/A slave class create 8 12.5 1.0 0 0 N/A sort extent pool 140 0.0 N/A 0 0 N/A state object free list 2 0.0 N/A 0 0 N/A statistics aggregation 336 0.0 N/A 0 0 N/A temp lob duration state 2 0.0 N/A 0 0 N/A temporary table state ob 2 0.0 N/A 0 0 N/A threshold alerts latch 51 0.0 N/A 0 0 N/A transaction allocation 129,235 0.0 N/A 0 0 N/A transaction branch alloc 12,348 0.0 N/A 0 0 N/A undo global data 444,494 0.0 0.0 0 0 N/A user lock 837 0.0 N/A 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- row cache objects 8,621,222 113,451 191 113,272 0 0 0 cache buffers chains 67,136,086 25,140 130 25,019 0 0 0 shared pool 3,095,886 7,247 1,105 6,208 0 0 0 library cache 4,558,071 4,789 293 4,506 0 0 0 session allocation 1,646,491 3,998 41 3,957 0 0 0 cache buffers lru chain 238,168 1,328 15 1,313 0 0 0 multiblock read objects 1,467,831 720 20 700 0 0 0 SGA IO buffer pool latch 619,213 655 23 632 0 0 0 session idle bit 5,211,594 579 35 544 0 0 0 library cache lock 2,779,392 464 36 428 0 0 0 object queue header operation 9,859,343 456 15 441 0 0 0 mostly latch-free SCN 76,375 431 13 418 0 0 0 shared pool simulator 3,036,252 126 10 117 0 0 0 simulator lru latch 81,566 105 4 101 0 0 0 simulator hash latch 3,415,408 95 3 92 0 0 0 library cache pin 1,297,212 89 8 81 0 0 0 redo allocation 376,741 58 11 47 0 0 0 dml lock allocation 259,949 49 4 45 0 0 0 In memory undo latch 476,219 48 32 16 0 0 0 enqueue hash chains 515,085 40 4 36 0 0 0 enqueues 250,594 39 4 35 0 0 0 messages 304,585 18 1 17 0 0 0 redo writing 246,857 11 1 10 0 0 0 checkpoint queue latch 339,260 10 2 8 0 0 0 kks stats 108,019 6 1 5 0 0 0 qmn task queue latch 206 4 1 3 0 0 0 archive process latch 389 1 1 0 0 0 0 slave class create 8 1 1 0 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- In memory undo latch ktiFlush: child 0 19 5 In memory undo latch kticmt: child 0 13 5 In memory undo latch ktichg: child 0 3 13 SGA IO buffer pool latch ksfd_alloc_sgabuffer 0 16 0 SGA IO buffer pool latch ksfd_free_sgabuffer 0 7 23 archive process latch kcrrsarc 0 1 0 cache buffers chains kcbgtcr: kslbegin excl 0 138 137 cache buffers chains kcbgtcr: fast path 0 26 14 cache buffers chains kcbrls: kslbegin 0 25 39 cache buffers chains kcbgcur: kslbegin 0 16 6 cache buffers chains kcbchg: kslbegin: bufs not 0 11 9 cache buffers chains kcbget: pin buffer 0 9 8 cache buffers chains kcbzwb 0 9 9 cache buffers chains kcbzib: multi-block read: 0 4 0 cache buffers chains kcbgtcr: kslbegin shared 0 3 1 cache buffers chains kcbget: exchange rls 0 2 0 cache buffers chains kcbget: exchange 0 1 1 cache buffers chains kcbzgb: scan from tail. no 0 1 0 cache buffers lru chain kcbzgws 0 14 3 cache buffers lru chain kcbzar: KSLNBEGIN 0 1 0 checkpoint queue latch kcbklbc: Link buffer into 0 2 0 dml lock allocation ktaiam 0 3 3 dml lock allocation ktaidm 0 1 1 enqueue hash chains ksqgtl3 0 2 3 enqueue hash chains ksqrcl 0 2 1 enqueues ksqdel 0 2 0 enqueues ksqgel: create enqueue 0 1 0 enqueues ksqies 0 1 4 event range base latch No latch 0 1 1 kks stats kks stats alloc/free 0 1 1 lgwr LWN SCN kcs023 0 7 0 library cache kglobpn: child: 0 36 32 library cache kglLockCursor 0 31 8 library cache kglhdgn: child: 0 28 90 library cache kglpndl: child: after proc 0 26 0 library cache kglic 0 24 4 library cache kgldti: 2child 0 23 6 library cache kglpin 0 9 4 library cache kglpnp: child 0 9 28 library cache kglpndl: child: before pro 0 7 33 library cache kglhdgc: child: 0 6 0 library cache kglati 0 2 0 library cache kgldte: child 0 0 2 19 library cache kgldtld: 2child 0 2 6 library cache kglScanDependency 0 1 0 library cache kglnti 0 1 0 library cache lock kgllkdl: child: no lock ha 0 36 50 library cache lock kgllkdl: child: cleanup 0 19 24 library cache lock kgllkal: child: multiinsta 0 17 12 library cache pin kglpnal: child: alloc spac 0 3 2 library cache pin kglpnp: child 0 3 2 library cache pin kglpndl 0 2 4 messages ksaamb: after wakeup 0 1 0 mostly latch-free SCN kcslcu3 0 6 13 multiblock read objects kcbzib: mbr get 0 14 8 multiblock read objects kcbzib: normal mbr free 0 6 12 object queue header oper kcbw_unlink_q 0 6 3 object queue header oper kcbw_link_q 0 5 1 object queue header oper kcbo_switch_cq 0 2 9 object queue header oper kcbo_swap_buf 0 1 0 Latch Miss Sources DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- object queue header oper kcbo_switch_mq_bg 0 1 2 redo allocation kcrfw_redo_gen: redo alloc 0 11 0 redo writing kcrfw_post: rba scn pair 0 1 0 row cache objects kqreqd: reget 0 69 25 row cache objects kqrpre: find obj 0 69 114 row cache objects kqreqd 0 29 33 row cache objects kqrso 0 17 8 row cache objects kqrpre: init complete 0 1 1 session allocation ksuprc 0 17 5 session allocation ksudlc 0 11 8 session allocation ksufap: active sessions 0 5 0 session allocation ksucri 0 4 25 session allocation ksuxds: not user session 0 3 3 session allocation ksuxds: KSUSFCLC not set 0 1 0 session idle bit ksupuc: set busy 0 21 11 session idle bit ksupuc: clear busy 0 14 23 session idle bit ksuxds 0 1 2 shared pool kghalo 0 738 435 shared pool kghfrunp: alloc: wait 0 104 5 shared pool kghupr1 0 93 361 shared pool kghdmp 0 72 0 shared pool kghfre 0 42 217 shared pool kghfrunp: clatch: nowait 0 38 0 shared pool kghalp 0 34 81 shared pool kghfrunp: clatch: wait 0 17 10 shared pool kgh: quiesce extents 0 7 0 shared pool kgh: sim resz update 0 4 0 shared pool kgh_next_free 0 4 0 shared pool kgh: add extent to quiesce 0 3 0 shared pool kghasp 0 2 5 shared pool kghfen: not perm alloc cla 0 1 1 shared pool kghfrh 0 1 0 shared pool kghquiesce: clatch: nowait 0 1 0 shared pool simulator kglsim_unpin_simhp 0 6 3 shared pool simulator kglsim_scan_lru: scan 0 2 2 shared pool simulator kglsim_unpin_simhp: fast p 0 1 5 shared pool simulator kglsim_upd_newhp 0 1 0 simulator hash latch kcbs_lookup_setid: lookup 0 1 0 simulator hash latch kcbsacc: insert dba 0 1 0 simulator hash latch kcbsacc: lookup dba 0 1 0 simulator lru latch kcbs_free_granule_sim_buff 0 2 0 simulator lru latch kcbs_shrink_pool 0 2 0 slave class create ksvcreate 0 1 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Logical Reads: 35,989,213 -> Captured Segments account for 84.9% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- LSSR_01 TS_DATA el_trx TABLE 5,849,344 16.25 EOD TS_INDEX XPKelem_tabl_RPT_PAPE INDEX 5,535,504 15.38 vspc_26 TS_DATA HIST_SCORE_VCTR TABLE 3,224,800 8.96 EOD TS_DATA elem_tabl_RPT_PAPER TABLE 2,937,808 8.16 vspc_26 TS_INDEX XPKVCTR_COLOR2B INDEX 2,544,032 7.07 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Total Physical Reads: 4,809,619 -> Captured Segments account for 91.2% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- vspc_26 TS_DATA HIST_SCORE_VCTR TABLE 2,634,902 54.78 LOR TS_LOR_DAT VCTR_EVAL_RESPONSE TABLE 485,825 10.10 TEDV_BD TS_DATA VCTR_SIMP_REQS TABLE 294,150 6.12 vspc_26 TS_DATA VCTR_COUPON TABLE 186,285 3.87 vspc_26 TS_DATA ONLINE_USER TABLE 141,655 2.95 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- EOD TS_INDEX XPKRPT_LOR_ARCHIVE INDEX 94 29.75 LSSR_01 TS_INDEX SYS_C00283899 INDEX 64 20.25 LSSR_01 TS_DATA COMMIT_REL_LOG TABLE 32 10.13 LSSR_01 TS_INDEX SYS_C0033150 INDEX 28 8.86 LOR TS_LOR_IDX XPKVCTR_RECC INDEX 12 3.80 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> % of Capture shows % of ITL waits for each top segment compared -> with total ITL waits for all segments captured by the Snapshot Tablespace Subobject Obj. ITL % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- CRS TS_INDEX XIF186CRS_QUERY_TAG INDEX 2 40.00 ** UNAVAIL ** UNAVAIL ** UNAVAILABLE ** AILABLE ** UNDEF 1 20.00 QUEST40 QUEST_IDX QUEST_ADV_BKG_EVENT_ INDEX 1 20.00 SYS SYSAUX WRH$_SGA_TARGET_ADVI INDEX 1 20.00 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- LSSR_01 TS_DATA COMMIT_REL_LOG TABLE 18 22.50 vspc_26 TS_INDEX CNDHISTTXNDTPC1 INDEX 7 8.75 vspc_26 TS_DATA VCTR_HISTORY TABLE 6 7.50 LSSR_01 TS_DATA REPT_REQ_DET TABLE 5 6.25 EOD TS_INDEX XPKAQ_REPORT_REQUEST INDEX 5 6.25 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> "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 19 21.1 0 N/A 2 1 dc_constraints 48 45.8 0 N/A 48 0 dc_database_links 343 1.7 0 N/A 0 1 dc_files 1,200 13.3 0 N/A 0 80 dc_free_extents 39,798 18.4 925 0.0 547 0 dc_global_oids 55 27.3 0 N/A 0 0 dc_histogram_data 996,624 1.8 0 N/A 0 4,137 dc_histogram_defs 332,547 4.2 0 N/A 0 2,363 dc_object_grants 75,349 2.6 0 N/A 0 257 dc_object_ids 981,681 0.2 0 N/A 5 730 dc_objects 81,515 8.6 0 N/A 12 613 dc_profiles 190 0.0 0 N/A 0 1 dc_rollback_segments 540 0.0 0 N/A 0 92 dc_segments 179,830 2.4 0 N/A 143 865 dc_sequences 3,580 2.7 0 N/A 3,580 7 dc_table_scns 6 100.0 0 N/A 0 0 dc_tablespace_quotas 131 5.3 0 N/A 130 0 dc_tablespaces 366,084 0.0 0 N/A 0 13 dc_used_extents 246 95.9 0 N/A 246 0 dc_usernames 47,947 0.3 0 N/A 0 79 dc_users 452,987 0.2 0 N/A 0 58 outstanding_alerts 18 94.4 0 N/A 0 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 1,072 3.8 132,602 0.2 266 0 CLUSTER 110 1.8 351 1.1 2 0 INDEX 39 48.7 123 15.4 0 0 PIPE 4,818 0.2 4,889 0.2 0 0 SQL AREA 427,215 6.1 2,578,835 2.4 7,292 1,180 TABLE/PROCEDURE 50,045 1.8 431,262 3.4 8,277 0 TRIGGER 8,045 0.9 126,799 0.5 542 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 561.6 N/A 3.1 8.2 54 61 183 183 Freeable 122.0 .0 .9 .6 5 N/A 140 140 SQL 97.5 88.3 .6 5.9 78 119 173 162 PL/SQL 8.1 4.8 .0 .0 0 0 183 179 E Other 538.9 N/A 3.3 8.7 54 61 165 165 Freeable 115.8 .0 .9 .9 9 N/A 129 129 SQL 30.3 21.5 .2 .8 8 119 155 144 PL/SQL 6.9 4.3 .0 .0 0 0 165 161 ------------------------------------------------------------- SGA Memory Summary DB/Inst: DESPO/DESPO Snaps: 27321-27322 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 1,358,954,496 1,426,063,360 Fixed Size 2,061,680 Redo Buffers 14,712,832 Variable Size 2,919,238,288 2,852,129,424 ------------------- sum 4,294,967,296 ------------------------------------------------------------- SGA breakdown difference DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 208.0 208.0 0.00 large free memory 159.8 159.8 0.00 shared CCursor 37.2 24.5 -34.09 shared Cursor Stats 29.4 29.4 0.00 shared KGH: NO ACCESS 198.2 277.4 40.00 shared PCursor 23.2 18.1 -22.15 shared free memory 941.1 964.9 2.53 shared kglsim heap 31.1 31.1 0.00 shared kglsim object batch 52.6 52.6 0.00 shared library cache 37.4 35.6 -4.78 shared sql area 88.6 35.2 -60.25 stream KGH: NO ACCESS 16.0 16.0 0.00 stream free memory 32.0 32.0 0.00 buffer_cache 1,296.0 1,360.0 4.94 fixed_sga 2.0 2.0 0.00 log_buffer 14.0 14.0 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> 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 Coordinator 8,939 0 0 QMON Slaves 7,786 0 0 ------------------------------------------------------------- Streams Capture DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Streams TRXOy DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Buffered Subscribers DB/Inst: DESPO/DESPO Snaps: 27321-27322 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: DESPO/DESPO Snaps: 27321-27322 -> Rule Sets ordered by Evaluations Fast SQL CPU Elapsed Ruleset Name Evals Evals Execs Time Time ------------------------- -------- -------- -------- -------- -------- SYS.ALERT_QUE_R 0 0 0 0 0 ------------------------------------------------------------- Resource Limit Stats DB/Inst: DESPO/DESPO Snap: 27322 -> 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 ------------------------------ ------------ ------------ ---------- ---------- processes 166 416 500 500 ------------------------------------------------------------- init.ora Parameters DB/Inst: DESPO/DESPO Snaps: 27321-27322 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- archive_lag_target 900 audit_trail DB background_dump_dest /elprod/admin/DESPO/bdump blank_trimming TRUE compatible 10.2.0 control_file_record_keep_time 31 control_files /elprod/data/DESPO/temp/control01. core_dump_dest /elprod/admin/DESPO/cdump cursor_space_for_time FALSE db_block_size 8192 db_cache_advice ON db_cache_size 402653184 db_file_multiblock_read_count 8 db_files 101 db_name DESPO dml_locks 500 event 4031 trace name errorstack, 10262 fal_client DESPO_PRIM fal_server DESPO_STDBY global_names TRUE java_pool_size 218103808 job_queue_processes 3 large_pool_size 167772160 local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=elpr lock_sga TRUE log_archive_dest log_archive_dest_1 location=/elprod/admin/DESPO/arch log_archive_dest_2 service=DESPO_STDBY OPTIONAL ARCH log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE log_archive_format arch_%s%r%t.arc log_archive_max_processes 6 log_archive_min_succeed_dest 1 log_buffer 14485504 log_checkpoint_interval 100000 max_dump_file_size 1024000 nls_date_format DD-MON-RR nls_language american nls_numeric_characters ., nls_sort binary nls_territory america open_cursors 255 open_links 5 optimizer_mode CHOOSE parallel_max_servers 0 pga_aggregate_target 1073741824 pre_page_sga TRUE processes 500 remote_login_passwordfile EXCLUSIVE resource_limit TRUE sga_max_size 4294967296 sga_target 3221225472 shared_pool_reserved_size 40000000 shared_pool_size 637534208 standby_archive_dest /elprod/admin/DESPO/arch standby_file_management AUTO timed_statistics TRUE undo_management AUTO undo_retention 1800 user_dump_dest /elprod/admin/DESPO/udump init.ora Parameters DB/Inst: DESPO/DESPO Snaps: 27321-27322 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- utl_file_dir /elprod/admin/DESPO/utlfile workarea_size_policy auto ------------------------------------------------------------- End of Report