WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ TDLDB 311738270 tdldb1 1 10.2.0.1.0 YES TDLDB1 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 4376 04-Feb-10 05:30:11 46 5.8 End Snap: 4556 11-Feb-10 17:30:22 44 11.2 Elapsed: 10,800.17 (mins) DB Time: 2,514.53 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 1,392M 1,408M Std Block Size: 8K Shared Pool Size: 368M 368M Log Buffer: 2,112K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 2,607.84 4,296.08 Logical reads: 17,297.49 28,495.34 Block changes: 16.38 26.99 Physical reads: 1.06 1.74 Physical writes: 1.25 2.06 User calls: 22.71 37.42 Parses: 14.83 24.43 Hard parses: 0.25 0.40 Sorts: 3.03 5.00 Logons: 0.05 0.08 Executes: 320.00 527.16 Transactions: 0.61 % Blocks changed per Read: 0.09 Recursive Call %: 94.26 Rollback per transaction %: 35.23 Rows per Sort: 52.42 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.99 In-memory Sort %: 100.00 Library Hit %: 99.87 Soft Parse %: 98.34 Execute to Parse %: 95.36 Latch Hit %: 99.99 Parse CPU to Parse Elapsd %: 75.24 % Non-Parse CPU: 99.67 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 74.29 80.46 % SQL with executions>1: 87.38 90.00 % Memory for SQL w/exec>1: 92.16 92.27 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time 145,776 96.6 Streams AQ: qmn coordinator wa 611 3,050 4991 2.0 Other reliable message 66,540 1,868 28 1.2 Other control file sequential read 994,865 1,721 2 1.1 System I/O gc cr block 2-way 979,289 687 1 0.5 Cluster ------------------------------------------------------------- RAC Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Begin End ----- ----- Number of Instances: 2 2 Global Cache Load Profile ~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Global Cache blocks received: 2.57 4.24 Global Cache blocks served: 2.42 3.99 GCS/GES messages received: 6.89 11.35 GCS/GES messages sent: 7.60 12.52 DBWR Fusion writes: 0.15 0.25 Estd Interconnect traffic (KB) 42.76 Global Cache Efficiency Percentages (Target local+remote 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer access - local cache %: 99.98 Buffer access - remote cache %: 0.01 Buffer access - disk %: 0.01 Global Cache and Enqueue Services - Workload Characteristics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg global enqueue get time (ms): 0.5 Avg global cache cr block receive time (ms): 1.3 Avg global cache current block receive time (ms): 1.3 Avg global cache cr block build time (ms): 0.0 Avg global cache cr block send time (ms): 0.1 Global cache log flushes for cr blocks served %: 2.0 Avg global cache cr block flush time (ms): 2.6 Avg global cache current block pin time (ms): 0.0 Avg global cache current block send time (ms): 0.1 Global cache log flushes for current blocks served %: 0.3 Avg global cache current block flush time (ms): 1.0 Global Cache and Enqueue Services - Messaging Statistics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg message sent queue time (ms): 0.2 Avg message sent queue time on ksxp (ms): 0.1 Avg message received queue time (ms): 2.8 Avg GCS message process time (ms): 0.1 Avg GES message process time (ms): 0.0 % of direct sent messages: 56.62 % of indirect sent messages: 28.85 % of flow controlled messages: 14.53 ------------------------------------------------------------- Time Model Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total time in database user-calls (DB Time): 150871.5s -> 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 149,771.2 99.3 DB CPU 145,776.1 96.6 PL/SQL execution elapsed time 2,964.6 2.0 parse time elapsed 1,045.9 .7 hard parse elapsed time 648.4 .4 PL/SQL compilation elapsed time 85.8 .1 hard parse (sharing criteria) elapsed time 59.7 .0 failed parse elapsed time 44.5 .0 connection management call elapsed time 23.6 .0 sequence load elapsed time 10.9 .0 repeated bind elapsed time 4.6 .0 hard parse (bind mismatch) elapsed time 4.0 .0 DB time 150,871.5 N/A background elapsed time 11,637.6 N/A background cpu time 3,395.0 N/A ------------------------------------------------------------- Wait Class DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 -------------------- ---------------- ------ ---------------- ------- --------- Other 10,049,547 91.4 6,240 1 25.5 Cluster 2,164,459 .0 2,910 1 5.5 System I/O 1,699,774 .0 2,412 1 4.3 User I/O 261,084 .0 605 2 0.7 Concurrency 227,852 .1 380 2 0.6 Commit 112,643 .0 240 2 0.3 Application 233,752 .0 143 1 0.6 Network 11,545,146 .0 76 0 29.4 Configuration 126,149 98.8 5 0 0.3 ------------------------------------------------------------- Wait Events DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------------------------- -------------- ------ ----------- ------- --------- Streams AQ: qmn coordinator 611 100.0 3,050 4991 0.0 reliable message 66,540 2.1 1,868 28 0.2 control file sequential read 994,865 .0 1,721 2 2.5 gc cr block 2-way 979,289 .0 687 1 2.5 gc buffer busy 14,947 3.5 463 31 0.0 gc cr block busy 31,236 .4 427 14 0.1 db file sequential read 184,060 .0 367 2 0.5 log file parallel write 384,852 .0 363 1 1.0 gc cr failure 267 97.0 326 1222 0.0 gc current block 2-way 453,313 .0 315 1 1.2 log file sync 112,643 .0 240 2 0.3 KJC: Wait for msg sends to c 684 100.0 211 308 0.0 gc current block busy 2,696 .0 201 74 0.0 library cache lock 97,508 .3 187 2 0.2 db file scattered read 54,698 .0 182 3 0.1 control file parallel write 216,790 .0 180 1 0.6 name-service call wait 2,655 .0 177 67 0.0 gc current grant busy 216,600 .0 174 1 0.6 wait for scn ack 368,384 .0 167 0 0.9 DFS lock handle 45,042 .2 144 3 0.1 gc cr multi block request 249,649 .0 130 1 0.6 SQL*Net break/reset to clien 207,643 .0 116 1 0.5 lms flush message acks 2,392 .0 112 47 0.0 os thread startup 7,557 .0 104 14 0.0 ges enter server mode 50 78.0 88 1755 0.0 row cache lock 100,792 .0 83 1 0.3 gc current retry 342 38.3 83 242 0.0 IPC send completion sync 1,601,766 99.8 82 0 4.1 gcs resource directory to be 849 2.8 76 90 0.0 db file parallel write 98,626 .0 73 1 0.3 gc current grant 2-way 150,828 .0 69 0 0.4 log file sequential read 1,921 .0 69 36 0.0 SQL*Net more data to client 813,718 .0 66 0 2.1 gcs log flush sync 31,570 14.1 51 2 0.1 CGS wait for IPC msg 6,213,203 100.0 44 0 15.8 gc cr grant 2-way 58,652 .0 29 1 0.1 enq: WF - contention 1,770 2.7 28 16 0.0 ges global resource director 821 100.0 25 31 0.0 PX Deq: Signal ACK 21,728 31.7 24 1 0.1 db file parallel read 2,770 .0 23 8 0.0 read by other session 9,653 .0 20 2 0.0 enq: PS - contention 20,111 .0 15 1 0.1 enq: US - contention 30,703 .0 14 0 0.1 enq: TM - contention 24,473 .0 14 1 0.1 gcs enter server mode 10 50.0 13 1293 0.0 direct path read temp 1,916 .0 12 6 0.0 PX Deq Credit: send blkd 25,520 .0 10 0 0.1 enq: TX - row lock contentio 21 95.2 10 496 0.0 SQL*Net message to client 10,728,692 .0 10 0 27.3 Log archive I/O 2,637 .0 7 3 0.0 enq: TT - contention 8,134 .0 6 1 0.0 library cache pin 9,827 .0 5 1 0.0 ges lmd/lmses to freeze in r 294 94.6 4 15 0.0 PX Deq: reap credit 776,633 98.5 4 0 2.0 rdbms ipc reply 27,396 .0 4 0 0.1 ksxr poll remote instances 789,754 74.3 4 0 2.0 enq: JQ - contention 2,753 .0 3 1 0.0 wait for votes 93 100.0 3 28 0.0 enq: RO - fast object reuse 784 .4 2 3 0.0 enq: TQ - TM contention 1,881 .0 2 1 0.0 Wait Events DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------------------------- -------------- ------ ----------- ------- --------- gc domain validation 9 .0 2 250 0.0 ges2 LMON to wake up lms - m 68 97.1 2 31 0.0 log file switch completion 62 1.6 2 32 0.0 direct path read 1,726 .0 2 1 0.0 ges2 LMON to wake up LMD - m 63 92.1 2 28 0.0 log file switch (checkpoint 3 33.3 1 488 0.0 gc cr disk read 4,131 .0 1 0 0.0 cr request retry 8 100.0 1 141 0.0 latch free 592 .0 1 2 0.0 enq: HW - contention 1,464 .0 1 1 0.0 enq: TA - contention 1,591 .0 1 0 0.0 enq: UL - contention 831 .0 1 1 0.0 gc cr block congested 129 .0 1 5 0.0 enq: DR - contention 302 .0 1 2 0.0 enq: PI - contention 854 .0 1 1 0.0 enq: TD - KTF dump entries 843 .0 0 1 0.0 enq: CF - contention 154 .0 0 3 0.0 gc current multi block reque 2,102 .0 0 0 0.0 enq: TX - index contention 38 .0 0 9 0.0 gc current grant congested 91 .0 0 3 0.0 SQL*Net more data from clien 2,736 .0 0 0 0.0 SGA: allocation forcing comp 20 35.0 0 14 0.0 enq: AF - task serialization 327 .0 0 1 0.0 enq: FB - contention 495 .0 0 0 0.0 ges LMD to inherit communica 2 100.0 0 102 0.0 buffer busy waits 1,410 .0 0 0 0.0 enq: JS - job run lock - syn 249 .0 0 1 0.0 direct path write temp 2,304 .0 0 0 0.0 latch: cache buffers chains 9,597 .0 0 0 0.0 undo segment extension 124,610 100.0 0 0 0.3 kksfbc child completion 3 100.0 0 54 0.0 enq: PR - contention 3 .0 0 44 0.0 log buffer space 1 .0 0 133 0.0 library cache load lock 7 .0 0 19 0.0 latch: shared pool 249 .0 0 0 0.0 gc current block unknown 109 .0 0 1 0.0 latch: library cache 849 .0 0 0 0.0 ges LMON to get to FTDONE 4 50.0 0 16 0.0 enq: MW - contention 114 .0 0 1 0.0 ges inquiry response 135 .0 0 0 0.0 gc recovery quiesce 8 .0 0 7 0.0 gc cr grant congested 20 .0 0 3 0.0 recovery read 3 .0 0 19 0.0 latch: KCL gc element parent 31 .0 0 2 0.0 ges reconfiguration to start 2 100.0 0 25 0.0 gc current block congested 8 .0 0 5 0.0 enq: PE - contention 43 .0 0 1 0.0 enq: TO - contention 74 .0 0 1 0.0 kjbdrmcvtq lmon drm quiesce: 10 .0 0 3 0.0 LGWR wait for redo copy 1,053 .1 0 0 0.0 enq: WL - contention 19 .0 0 1 0.0 latch: ges resource hash lis 684 .0 0 0 0.0 latch: cache buffers lru cha 170 .0 0 0 0.0 enq: PG - contention 29 .0 0 1 0.0 enq: TS - contention 56 .0 0 0 0.0 gc current split 33 .0 0 1 0.0 enq: MD - contention 29 .0 0 1 0.0 log file single write 79 .0 0 0 0.0 direct path write 3,957 .0 0 0 0.0 global enqueue expand wait 1 100.0 0 12 0.0 Wait Events DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------------------------- -------------- ------ ----------- ------- --------- enq: DL - contention 20 .0 0 0 0.0 enq: TX - allocate ITL entry 7 .0 0 1 0.0 enq: SK - contention 9 .0 0 1 0.0 enq: JS - wdw op 5 .0 0 1 0.0 kjctssqmg: quick message sen 8 100.0 0 0 0.0 enq: SQ - contention 2 .0 0 2 0.0 enq: FU - contention 5 .0 0 1 0.0 latch: object queue header o 26 .0 0 0 0.0 latch: messages 34 .0 0 0 0.0 buffer deadlock 23 60.9 0 0 0.0 latch: library cache pin 8 .0 0 0 0.0 latch: row cache objects 8 .0 0 0 0.0 enq: XR - database force log 1 .0 0 0 0.0 latch: checkpoint queue latc 5 .0 0 0 0.0 control file single write 1 .0 0 0 0.0 latch: session allocation 2 .0 0 0 0.0 latch: gcs resource hash 4 .0 0 0 0.0 PX qref latch 8 37.5 0 0 0.0 latch: enqueue hash chains 2 .0 0 0 0.0 lock escalate retry 17 100.0 0 0 0.0 ges lmd/lmses to unfreeze in 2 .0 0 0 0.0 cursor: mutex X 2 .0 0 0 0.0 ges LMON for send queues 1 .0 0 0 0.0 SQL*Net message from client 10,728,694 .0 6,133,252 572 27.3 PX Idle Wait 527,277 97.5 1,295,494 2457 1.3 gcs remote message 44,598,282 98.9 1,294,742 29 113.4 Streams AQ: qmn slave idle w 25,530 2.8 679,687 26623 0.1 DIAG idle wait 3,190,007 .0 647,866 203 8.1 ges remote message 7,719,979 97.2 647,807 84 19.6 wait for unread message on b 653,550 99.1 647,798 991 1.7 ASM background timer 130,034 .0 647,501 4979 0.3 Streams AQ: waiting for mess 133,123 95.1 646,839 4859 0.3 virtual circuit status 21,601 100.0 644,735 29847 0.1 Streams AQ: qmn coordinator 47,915 50.8 641,906 13397 0.1 class slave wait 6,628 95.2 639,763 96524 0.0 Streams AQ: waiting for time 14,581 63.1 532,901 36548 0.0 jobq slave wait 127,169 97.2 377,562 2969 0.3 PX Deq: Execution Msg 52,118 .0 595 11 0.1 PX Deq: Execute Reply 12,659 .0 37 3 0.0 PX Deq: Join ACK 8,118 .0 35 4 0.0 PX Deq: Parse Reply 12,415 .0 21 2 0.0 PX Deq: Msg Fragment 30,066 .0 10 0 0.1 SGA: MMAN sleep for componen 190 62.1 2 10 0.0 KSV master wait 321 .0 2 5 0.0 Streams AQ: RAC qmn coordina 47,927 100.0 0 0 0.1 ------------------------------------------------------------- Background Wait Events DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 9,179,401 91.7 5,032 1 23.3 control file sequential read 825,977 .0 1,681 2 2.1 log file parallel write 384,852 .0 363 1 1.0 control file parallel write 216,790 .0 180 1 0.6 db file parallel write 98,626 .0 73 1 0.3 log file sequential read 1,921 .0 69 36 0.0 gc cr block 2-way 78,866 .0 65 1 0.2 row cache lock 35,616 .0 58 2 0.1 os thread startup 6,904 .0 55 8 0.0 gc current block 2-way 45,130 .0 32 1 0.1 db file sequential read 5,596 .0 15 3 0.0 gc buffer busy 45 20.0 14 322 0.0 gc current block busy 401 .0 13 31 0.0 enq: TM - contention 18,006 .0 9 0 0.0 Log archive I/O 2,637 .0 7 3 0.0 library cache lock 8,193 .0 6 1 0.0 gc current grant busy 6,285 .0 4 1 0.0 gc cr block busy 59 .0 1 21 0.0 gc cr grant 2-way 2,060 .0 1 1 0.0 gc domain validation 2 .0 1 459 0.0 db file scattered read 86 .0 0 6 0.0 gc cr multi block request 1,231 .0 0 0 0.0 gc current grant 2-way 553 .0 0 1 0.0 direct path read 299 .0 0 1 0.0 enq: RO - fast object reuse 369 .0 0 1 0.0 enq: HW - contention 86 .0 0 1 0.0 library cache pin 90 .0 0 1 0.0 gc recovery quiesce 8 .0 0 7 0.0 recovery read 3 .0 0 19 0.0 db file parallel read 5 .0 0 10 0.0 log file switch completion 3 .0 0 13 0.0 latch: library cache 73 .0 0 0 0.0 log file single write 79 .0 0 0 0.0 latch: shared pool 34 .0 0 0 0.0 direct path write 302 .0 0 0 0.0 log file sync 1 .0 0 11 0.0 buffer busy waits 83 .0 0 0 0.0 gc current block congested 1 .0 0 2 0.0 latch: cache buffers chains 29 .0 0 0 0.0 gc current block unknown 1 .0 0 1 0.0 latch: row cache objects 7 .0 0 0 0.0 control file single write 1 .0 0 0 0.0 rdbms ipc message 10,249,696 92.1 9,517,825 929 26.1 gcs remote message 44,598,280 98.9 1,294,742 29 113.4 DIAG idle wait 3,190,007 .0 647,866 203 8.1 ges remote message 7,719,957 97.2 647,807 84 19.6 pmon timer 228,129 100.0 647,677 2839 0.6 ASM background timer 130,034 .0 647,501 4979 0.3 Streams AQ: qmn coordinator 47,915 50.8 641,906 13397 0.1 smon timer 2,846 71.3 611,431 214839 0.0 Streams AQ: waiting for time 14,581 63.1 532,901 36548 0.0 PX Deq: Join ACK 2,155 .0 6 3 0.0 PX Deq: Parse Reply 2,692 .0 3 1 0.0 SGA: MMAN sleep for componen 190 62.1 2 10 0.0 KSV master wait 321 .0 2 5 0.0 PX Deq: Execute Reply 2,171 .0 1 0 0.0 Streams AQ: RAC qmn coordina 47,927 100.0 0 0 0.1 ------------------------------------------------------------- Operating System Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME #################### AVG_IDLE_TIME 62,247,310 AVG_SYS_TIME #################### AVG_USER_TIME 2,254,610 BUSY_TIME 20,303,013 IDLE_TIME 498,130,938 SYS_TIME 2,114,675 USER_TIME 18,188,338 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES #################### VM_OUT_BYTES #################### PHYSICAL_MEMORY_BYTES 6,439,952,384 NUM_CPUS 8 NUM_CPU_CORES 2 ------------------------------------------------------------- Service Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- TDLDB 147,743.4 144,193.3 491,802 ########## SYS$USERS 3,124.3 1,579.5 169,258 ########## SYS$BACKGROUND 0.1 0.1 24,032 ########## TDLDBXDB 0.0 0.0 0 0 TDLDB_SVC 0.0 0.0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 --------- --------- --------- --------- --------- --------- --------- --------- TDLDB 186390 38282 70545 16234 0 0 11141315 7517 SYS$USERS 47331 15535 55025 7520 0 0 386673 63 SYS$BACKGROUND 27364 6713 102303 14264 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ---------- ------- ------------- 28,224 28,010 25,336 1.1 18.7 agp1d79ak8qh1 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, E.CAVLOCONUMB || ' ' || SUBSTR(E.CAVFRONTR EAR, 1, 1) || E.CANLOCOPOSN LOCONUMB, E.CACATCHDTCHFLAG ATCHDTCHFLAG, 'L' WBLFLA G, DECODE(E.CANSQNCNUMB,1,'Y',DECODE(C.CANBOARDID,:B3 ,'N','Y')) ORIGINFLAG,B.CA VBASESHED BASESHED, B.CADSCHEDULEDUEDATE SCHEDULEDATE,SPK_FUNCTIONS.GET_MULTILOC 26,532 26,469 9,300 2.9 17.6 7dmt4t1vkxuud Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL, DPRTARVLTIME , DPRTTIME , ARVLLATE,DEPTLATE,LI NENO,SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN, STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRK CODE,BLKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRD 26,384 25,830 38,256 0.7 17.5 4ytg55vnx6w8v Module: COAServer.exe begin SPK_TRNMVMT.sp_TrnMvmtInsrtnew(trainid=>:trainid, sttncode=>:sttncode, arr vtime=>:arrvtime, depttime=>:depttime, lineno=>:lineno, abnrmlflag=>:abnrmlflag, blksctnname=>:blksctnname, rmk=>:rmk, syslineno=>:syslineno, userid=>:userid, l inenumb=>:linenumb, status=>:status, sttn=>:sttn, errflag=>:errflag, traininblk= 17,694 17,653 5,583 3.2 11.7 56h1rmntt2tn8 Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL,DPRTARVLTIME,DPRTTIME,ARVLLATE,DEPTLATE,LINENO, SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN,STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRKCODE,B LKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRDID,NXT 12,122 11,973 37,844 0.3 8.0 c9s24n2vz6h07 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME, 'dd-mm-yy hh24:mi') FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID) = RTRIM(:B3 ) AND CAVSTTNCODE = RTRIM(:B2 )AND CANSQNCNUMB = R TRIM(:B1 ) 9,525 9,236 23,160 0.4 6.3 appuckj66mb1c Module: COAServer.exe SELECT CAVTRAINID,CACSTTSFLAG,CAVSTTNCODE FROM CCETTRAINPIPELINE P WHERE P.CACST TSFLAG='D' AND P.CAVTRAINID IN (SELECT * FROM TABLE(SPK_FUNCTIONS.F_CONVERT(:B1 ))) AND P.CANSQNCNUMB=(SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CANB OARDID=:B2 AND CAVTRAINID = P.CAVTRAINID) 7,176 7,050 35,243 0.2 4.8 68b0mrhbvk8yb Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE TRIM(CAVTRAINID)=TRIM(:B1 ) 6,469 6,422 14,631 0.4 4.3 2kcbn01p7ur00 Module: COAServer.exe SELECT '' SCTNNUMB, SPK_FUNCTIONS.GET_SCNTNUMB(:B3 , A.CAVSTTNCODE, C.CANDSPLNUM B, CASE WHEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X.CAVTRAINID = B.CAVTRAINID AND X.CANSQNCNUMB = C.CANSQNCNUMB+1 AND X.CANBOARDID = C.CANBOARDI D) <> C.CANDSPLNUMB THEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X. 4,337 4,229 31,154 0.1 2.9 1vh8uk4tw4fww Module: COAServer.exe SELECT DISTINCT A.CACSTTSFLAG,A.CAVTRAINNAME FROM CCETTRAINPIPELINE A,CCETTRAINM VMT B,CCETTRAINORDR C WHERE A.CAVTRAINID=B.CAVTRAINID AND A.CAVTRAINID=C.CAVTRAI NID AND A.CAVBLCKSCTNNAME=B.CAVBLCKSCTNNAME AND C.CACTRNSCMPLFLAG='N' AND B.CACL ASTREPTFLAG='Y' AND A.CACSTTSFLAG='D' AND CANDSPLNUMB = :B2 AND B.CAVBLCKSCTNNAM 3,144 3,103 341,913 0.0 2.1 3v88vny75772r SQL ordered by Elapsed Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ---------- ------- ------------- Module: COAServer.exe SELECT COUNT(CAVTRAINID) FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID) = :B2 AND (TR IM(CAVDVSNENTRYSTTN) = :B1 OR TRIM(CAVDVSNEXITSTTN) = :B1 ) AND TRIM(CAVDVSNEXIT STTN) = TRIM(CAVDSTNSTTN) 3,129 3,065 14,113 0.2 2.1 5p64c5g4vf4w7 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, CASE WHEN C.CAVDRTN IS NU LL THEN (SELECT CAVDRTN FROM CCETTRAINPIPELINE WHERE CANSQNCNUMB=CASE WHEN B.CAN SQNCNUMB>1 THEN B.CANSQNCNUMB-1 ELSE 1 END AND CAVTRAINID=A.CAVTRAINID ) ELSE C. CAVDRTN END AS "Drtn",B.CAVSTTNCODE CURRSTTN, DECODE(B.CADARVLTIME,B.CADDPRTTIME 2,811 2,664 40,749 0.1 1.9 7tnc1wa85j90n Module: COAServer.exe SELECT DISTINCT C.CANBOARDID BOARDID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_ SUBSECTIONID (C.CANBOARDID ,H.CAVTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SECTI ONID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_SUBSECTIONID (C.CANBOARDID ,H.CA VTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SUBSECTIONID, B.CAVTRAINID TRAINID, T 2,599 2,569 7,573 0.3 1.7 91fajhfm2rbus Module: COAServer.exe SELECT /*+ ordered use_nl(t A B) index(A PK_CCETTRAINORDR) */ T.TRAINID,T.TRAINN O,T.DRTN,T.TRAINTYPE,T.SCTNNO,T.SUBSCTNNO,T.DVSNENTRYSTTN, T.STTS,T.TIME,T.LINEN O,T.LATEARVLTIME,T.LATEDPRTTIME,T.ORIGSTTN,T.DSTNSTTN,T.TRMNFLAG, T.ARVLTIME,T.M VMVTFLAG,T.TRNRMRK, T.SDNGSTTN FROM ( SELECT A.CAVTRAINID TRAINID,NVL(A.CAVTRAIN 2,540 2,493 14,672 0.2 1.7 17d23235hu010 Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,B .CANDSPLNUMB DSPLNUMB, C.CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLT IME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG,D.CAVDRTN DRTN, COALESCE(C.CANLINENUMB ,C.CANSYSLINENUMB,-1) LINENO FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINO 2,495 2,380 9,225 0.3 1.7 fqmpjygajy9c1 Module: COAServer.exe SELECT F.CAVTRAINID TRAINID, E.CAVTRAINNAME TRAINNAME, C.MANDSPLNUMB SUBSECID, E .CAVTRAINTYPE TRAINTYPE, G.CACLEFLAG ARVLEFLAG, F.CACDPRTLEFLAG DPRTLEFLAG, SPK_ FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGDPRTTIME, 'dd-mm-yy hh24:mi'), 'P ') ARVPICNT, SPK_FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGARVLTIME, 'dd-mm 2,166 2,122 17,027 0.1 1.4 7cmnmyraycd4f Module: COAServer.exe SELECT DISTINCT A.CAVBLCKRSTNID, CASE WHEN A.CAVBLOCKSCTNNAME IS NULL THEN CASE WHEN A.CAVSTTN = 'KOK' AND D.MANDSPLNUMB = 1 THEN 21 ELSE D.MANDSPLNUMB END ELSE SPK_FUNCTIONS.SF_BLCKDSPLNUMB(A.CAVBLOCKSCTNNAME,F.MANBOARDID,A.CAVBLCKRSTNID) END CANSCTNNUMB, A.CAVSTTN, SUBSTR(A.CAVBLOCKSCTNNAME,1,INSTR(CAVBLOCKSCTNNAME,' 2,128 2,052 24,904 0.1 1.4 c2cd2rrr1j5c3 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, NVL((SELECT SUM(B.CANTOTLUNIT) FROM CCETTR AINATCHDTCH B WHERE B.CAVTRAINID = A.CAVTRAINID AND B.CANSQNCNUMB <= (CASE (SELE CT MIN(X.CANSQNCNUMB) FROM CCETTRAINPIPELINE X WHERE X.CANBOARDID = :B3 AND X.CA VTRAINID = A.CAVTRAINID ) WHEN 1 THEN 1 ELSE (SELECT MIN(X.CANSQNCNUMB) FROM CCE 1,786 1,741 15,782 0.1 1.2 4f5aj871r27uc Module: COAServer.exe SELECT DISTINCT CAVCTNORDRID CTNORDRID, CACPERMTEMPFLAG PERMTEMPFLAG, D.MAVSUBSC TNCODE SCTN, CAVSTTN STTN, CAVBLCKSCTNNAME BLCKSCTNNAME, CANLINENUMB LINENUMB, C AVDRTN DRTN, CACOHEKMFLAG OHEKMFLAG, CAVCAUSE CAUSE, CAVMSGNUMB MSGNUMB, CAVDEPT CODE DEPTCODE, CAVCAUSEDESC CAUSEDESC, TO_CHAR(CADSTRTTIME,'DD-MM-YY HH24:MI') S SQL ordered by Elapsed Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ---------- ------- ------------- 1,531 1,504 9,212 0.2 1.0 gfwvgr5sc95vw Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,C .CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLTIME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINORDR D WHERE D.CAVTRAINID=C.CAVTRAINID AND B.CAVTRAINID=D.CAVTRAINID AND B.CANTRAINSQNC=D.CA ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ----------- ------- ------------- 28,010 28,224 25,336 1.11 18.7 agp1d79ak8qh1 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, E.CAVLOCONUMB || ' ' || SUBSTR(E.CAVFRONTR EAR, 1, 1) || E.CANLOCOPOSN LOCONUMB, E.CACATCHDTCHFLAG ATCHDTCHFLAG, 'L' WBLFLA G, DECODE(E.CANSQNCNUMB,1,'Y',DECODE(C.CANBOARDID,:B3 ,'N','Y')) ORIGINFLAG,B.CA VBASESHED BASESHED, B.CADSCHEDULEDUEDATE SCHEDULEDATE,SPK_FUNCTIONS.GET_MULTILOC 26,469 26,532 9,300 2.85 17.6 7dmt4t1vkxuud Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL, DPRTARVLTIME , DPRTTIME , ARVLLATE,DEPTLATE,LI NENO,SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN, STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRK CODE,BLKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRD 25,830 26,384 38,256 0.68 17.5 4ytg55vnx6w8v Module: COAServer.exe begin SPK_TRNMVMT.sp_TrnMvmtInsrtnew(trainid=>:trainid, sttncode=>:sttncode, arr vtime=>:arrvtime, depttime=>:depttime, lineno=>:lineno, abnrmlflag=>:abnrmlflag, blksctnname=>:blksctnname, rmk=>:rmk, syslineno=>:syslineno, userid=>:userid, l inenumb=>:linenumb, status=>:status, sttn=>:sttn, errflag=>:errflag, traininblk= 17,653 17,694 5,583 3.16 11.7 56h1rmntt2tn8 Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL,DPRTARVLTIME,DPRTTIME,ARVLLATE,DEPTLATE,LINENO, SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN,STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRKCODE,B LKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRDID,NXT 11,973 12,122 37,844 0.32 8.0 c9s24n2vz6h07 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME, 'dd-mm-yy hh24:mi') FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID) = RTRIM(:B3 ) AND CAVSTTNCODE = RTRIM(:B2 )AND CANSQNCNUMB = R TRIM(:B1 ) 9,236 9,525 23,160 0.40 6.3 appuckj66mb1c Module: COAServer.exe SELECT CAVTRAINID,CACSTTSFLAG,CAVSTTNCODE FROM CCETTRAINPIPELINE P WHERE P.CACST TSFLAG='D' AND P.CAVTRAINID IN (SELECT * FROM TABLE(SPK_FUNCTIONS.F_CONVERT(:B1 ))) AND P.CANSQNCNUMB=(SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CANB OARDID=:B2 AND CAVTRAINID = P.CAVTRAINID) 7,050 7,176 35,243 0.20 4.8 68b0mrhbvk8yb Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE TRIM(CAVTRAINID)=TRIM(:B1 ) 6,422 6,469 14,631 0.44 4.3 2kcbn01p7ur00 Module: COAServer.exe SELECT '' SCTNNUMB, SPK_FUNCTIONS.GET_SCNTNUMB(:B3 , A.CAVSTTNCODE, C.CANDSPLNUM B, CASE WHEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X.CAVTRAINID = B.CAVTRAINID AND X.CANSQNCNUMB = C.CANSQNCNUMB+1 AND X.CANBOARDID = C.CANBOARDI D) <> C.CANDSPLNUMB THEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X. 4,229 4,337 31,154 0.14 2.9 1vh8uk4tw4fww Module: COAServer.exe SELECT DISTINCT A.CACSTTSFLAG,A.CAVTRAINNAME FROM CCETTRAINPIPELINE A,CCETTRAINM VMT B,CCETTRAINORDR C WHERE A.CAVTRAINID=B.CAVTRAINID AND A.CAVTRAINID=C.CAVTRAI NID AND A.CAVBLCKSCTNNAME=B.CAVBLCKSCTNNAME AND C.CACTRNSCMPLFLAG='N' AND B.CACL ASTREPTFLAG='Y' AND A.CACSTTSFLAG='D' AND CANDSPLNUMB = :B2 AND B.CAVBLCKSCTNNAM 3,103 3,144 341,913 0.01 2.1 3v88vny75772r SQL ordered by CPU Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ----------- ------- ------------- Module: COAServer.exe SELECT COUNT(CAVTRAINID) FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID) = :B2 AND (TR IM(CAVDVSNENTRYSTTN) = :B1 OR TRIM(CAVDVSNEXITSTTN) = :B1 ) AND TRIM(CAVDVSNEXIT STTN) = TRIM(CAVDSTNSTTN) 3,065 3,129 14,113 0.22 2.1 5p64c5g4vf4w7 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, CASE WHEN C.CAVDRTN IS NU LL THEN (SELECT CAVDRTN FROM CCETTRAINPIPELINE WHERE CANSQNCNUMB=CASE WHEN B.CAN SQNCNUMB>1 THEN B.CANSQNCNUMB-1 ELSE 1 END AND CAVTRAINID=A.CAVTRAINID ) ELSE C. CAVDRTN END AS "Drtn",B.CAVSTTNCODE CURRSTTN, DECODE(B.CADARVLTIME,B.CADDPRTTIME 2,664 2,811 40,749 0.07 1.9 7tnc1wa85j90n Module: COAServer.exe SELECT DISTINCT C.CANBOARDID BOARDID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_ SUBSECTIONID (C.CANBOARDID ,H.CAVTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SECTI ONID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_SUBSECTIONID (C.CANBOARDID ,H.CA VTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SUBSECTIONID, B.CAVTRAINID TRAINID, T 2,569 2,599 7,573 0.34 1.7 91fajhfm2rbus Module: COAServer.exe SELECT /*+ ordered use_nl(t A B) index(A PK_CCETTRAINORDR) */ T.TRAINID,T.TRAINN O,T.DRTN,T.TRAINTYPE,T.SCTNNO,T.SUBSCTNNO,T.DVSNENTRYSTTN, T.STTS,T.TIME,T.LINEN O,T.LATEARVLTIME,T.LATEDPRTTIME,T.ORIGSTTN,T.DSTNSTTN,T.TRMNFLAG, T.ARVLTIME,T.M VMVTFLAG,T.TRNRMRK, T.SDNGSTTN FROM ( SELECT A.CAVTRAINID TRAINID,NVL(A.CAVTRAIN 2,493 2,540 14,672 0.17 1.7 17d23235hu010 Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,B .CANDSPLNUMB DSPLNUMB, C.CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLT IME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG,D.CAVDRTN DRTN, COALESCE(C.CANLINENUMB ,C.CANSYSLINENUMB,-1) LINENO FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINO 2,380 2,495 9,225 0.26 1.7 fqmpjygajy9c1 Module: COAServer.exe SELECT F.CAVTRAINID TRAINID, E.CAVTRAINNAME TRAINNAME, C.MANDSPLNUMB SUBSECID, E .CAVTRAINTYPE TRAINTYPE, G.CACLEFLAG ARVLEFLAG, F.CACDPRTLEFLAG DPRTLEFLAG, SPK_ FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGDPRTTIME, 'dd-mm-yy hh24:mi'), 'P ') ARVPICNT, SPK_FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGARVLTIME, 'dd-mm 2,122 2,166 17,027 0.12 1.4 7cmnmyraycd4f Module: COAServer.exe SELECT DISTINCT A.CAVBLCKRSTNID, CASE WHEN A.CAVBLOCKSCTNNAME IS NULL THEN CASE WHEN A.CAVSTTN = 'KOK' AND D.MANDSPLNUMB = 1 THEN 21 ELSE D.MANDSPLNUMB END ELSE SPK_FUNCTIONS.SF_BLCKDSPLNUMB(A.CAVBLOCKSCTNNAME,F.MANBOARDID,A.CAVBLCKRSTNID) END CANSCTNNUMB, A.CAVSTTN, SUBSTR(A.CAVBLOCKSCTNNAME,1,INSTR(CAVBLOCKSCTNNAME,' 2,052 2,128 24,904 0.08 1.4 c2cd2rrr1j5c3 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, NVL((SELECT SUM(B.CANTOTLUNIT) FROM CCETTR AINATCHDTCH B WHERE B.CAVTRAINID = A.CAVTRAINID AND B.CANSQNCNUMB <= (CASE (SELE CT MIN(X.CANSQNCNUMB) FROM CCETTRAINPIPELINE X WHERE X.CANBOARDID = :B3 AND X.CA VTRAINID = A.CAVTRAINID ) WHEN 1 THEN 1 ELSE (SELECT MIN(X.CANSQNCNUMB) FROM CCE 1,741 1,786 15,782 0.11 1.2 4f5aj871r27uc Module: COAServer.exe SELECT DISTINCT CAVCTNORDRID CTNORDRID, CACPERMTEMPFLAG PERMTEMPFLAG, D.MAVSUBSC TNCODE SCTN, CAVSTTN STTN, CAVBLCKSCTNNAME BLCKSCTNNAME, CANLINENUMB LINENUMB, C AVDRTN DRTN, CACOHEKMFLAG OHEKMFLAG, CAVCAUSE CAUSE, CAVMSGNUMB MSGNUMB, CAVDEPT CODE DEPTCODE, CAVCAUSEDESC CAUSEDESC, TO_CHAR(CADSTRTTIME,'DD-MM-YY HH24:MI') S SQL ordered by CPU Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ---------- ---------- ------------ ----------- ------- ------------- 1,504 1,531 9,212 0.16 1.0 gfwvgr5sc95vw Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,C .CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLTIME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINORDR D WHERE D.CAVTRAINID=C.CAVTRAINID AND B.CAVTRAINID=D.CAVTRAINID AND B.CANTRAINSQNC=D.CA ------------------------------------------------------------- SQL ordered by Gets DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 11,208,956,212 -> Captured SQL account for 102.3% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 3,898,217,740 25,336 153,860.8 34.8 ######## 28223.79 agp1d79ak8qh1 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, E.CAVLOCONUMB || ' ' || SUBSTR(E.CAVFRONTR EAR, 1, 1) || E.CANLOCOPOSN LOCONUMB, E.CACATCHDTCHFLAG ATCHDTCHFLAG, 'L' WBLFLA G, DECODE(E.CANSQNCNUMB,1,'Y',DECODE(C.CANBOARDID,:B3 ,'N','Y')) ORIGINFLAG,B.CA VBASESHED BASESHED, B.CADSCHEDULEDUEDATE SCHEDULEDATE,SPK_FUNCTIONS.GET_MULTILOC 1,722,127,598 9,300 185,175.0 15.4 ######## 26531.80 7dmt4t1vkxuud Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL, DPRTARVLTIME , DPRTTIME , ARVLLATE,DEPTLATE,LI NENO,SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN, STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRK CODE,BLKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRD 1,177,066,173 38,256 30,768.1 10.5 ######## 26383.86 4ytg55vnx6w8v Module: COAServer.exe begin SPK_TRNMVMT.sp_TrnMvmtInsrtnew(trainid=>:trainid, sttncode=>:sttncode, arr vtime=>:arrvtime, depttime=>:depttime, lineno=>:lineno, abnrmlflag=>:abnrmlflag, blksctnname=>:blksctnname, rmk=>:rmk, syslineno=>:syslineno, userid=>:userid, l inenumb=>:linenumb, status=>:status, sttn=>:sttn, errflag=>:errflag, traininblk= 1,139,743,471 5,583 204,145.3 10.2 ######## 17694.06 56h1rmntt2tn8 Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL,DPRTARVLTIME,DPRTTIME,ARVLLATE,DEPTLATE,LINENO, SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN,STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRKCODE,B LKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRDID,NXT 575,922,471 37,844 15,218.3 5.1 ######## 12122.07 c9s24n2vz6h07 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME, 'dd-mm-yy hh24:mi') FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID) = RTRIM(:B3 ) AND CAVSTTNCODE = RTRIM(:B2 )AND CANSQNCNUMB = R TRIM(:B1 ) 515,028,435 40,749 12,639.0 4.6 2664.43 2811.07 7tnc1wa85j90n Module: COAServer.exe SELECT DISTINCT C.CANBOARDID BOARDID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_ SUBSECTIONID (C.CANBOARDID ,H.CAVTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SECTI ONID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_SUBSECTIONID (C.CANBOARDID ,H.CA VTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SUBSECTIONID, B.CAVTRAINID TRAINID, T 449,674,458 24,904 18,056.3 4.0 2052.11 2127.66 c2cd2rrr1j5c3 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, NVL((SELECT SUM(B.CANTOTLUNIT) FROM CCETTR AINATCHDTCH B WHERE B.CAVTRAINID = A.CAVTRAINID AND B.CANSQNCNUMB <= (CASE (SELE CT MIN(X.CANSQNCNUMB) FROM CCETTRAINPIPELINE X WHERE X.CANBOARDID = :B3 AND X.CA VTRAINID = A.CAVTRAINID ) WHEN 1 THEN 1 ELSE (SELECT MIN(X.CANSQNCNUMB) FROM CCE 385,875,186 14,631 26,373.8 3.4 6421.54 6469.21 2kcbn01p7ur00 Module: COAServer.exe SELECT '' SCTNNUMB, SPK_FUNCTIONS.GET_SCNTNUMB(:B3 , A.CAVSTTNCODE, C.CANDSPLNUM B, CASE WHEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X.CAVTRAINID = B.CAVTRAINID AND X.CANSQNCNUMB = C.CANSQNCNUMB+1 AND X.CANBOARDID = C.CANBOARDI D) <> C.CANDSPLNUMB THEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X. 350,970,369 23,160 15,154.2 3.1 9235.73 9524.65 appuckj66mb1c Module: COAServer.exe SELECT CAVTRAINID,CACSTTSFLAG,CAVSTTNCODE FROM CCETTRAINPIPELINE P WHERE P.CACST TSFLAG='D' AND P.CAVTRAINID IN (SELECT * FROM TABLE(SPK_FUNCTIONS.F_CONVERT(:B1 ))) AND P.CANSQNCNUMB=(SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CANB SQL ordered by Gets DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 11,208,956,212 -> Captured SQL account for 102.3% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- OARDID=:B2 AND CAVTRAINID = P.CAVTRAINID) 318,494,957 31,154 10,223.2 2.8 4229.20 4337.16 1vh8uk4tw4fww Module: COAServer.exe SELECT DISTINCT A.CACSTTSFLAG,A.CAVTRAINNAME FROM CCETTRAINPIPELINE A,CCETTRAINM VMT B,CCETTRAINORDR C WHERE A.CAVTRAINID=B.CAVTRAINID AND A.CAVTRAINID=C.CAVTRAI NID AND A.CAVBLCKSCTNNAME=B.CAVBLCKSCTNNAME AND C.CACTRNSCMPLFLAG='N' AND B.CACL ASTREPTFLAG='Y' AND A.CACSTTSFLAG='D' AND CANDSPLNUMB = :B2 AND B.CAVBLCKSCTNNAM 235,579,292 341,913 689.0 2.1 3103.33 3144.28 3v88vny75772r Module: COAServer.exe SELECT COUNT(CAVTRAINID) FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID) = :B2 AND (TR IM(CAVDVSNENTRYSTTN) = :B1 OR TRIM(CAVDVSNEXITSTTN) = :B1 ) AND TRIM(CAVDVSNEXIT STTN) = TRIM(CAVDSTNSTTN) 206,930,336 14,113 14,662.4 1.8 3065.11 3128.94 5p64c5g4vf4w7 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, CASE WHEN C.CAVDRTN IS NU LL THEN (SELECT CAVDRTN FROM CCETTRAINPIPELINE WHERE CANSQNCNUMB=CASE WHEN B.CAN SQNCNUMB>1 THEN B.CANSQNCNUMB-1 ELSE 1 END AND CAVTRAINID=A.CAVTRAINID ) ELSE C. CAVDRTN END AS "Drtn",B.CAVSTTNCODE CURRSTTN, DECODE(B.CADARVLTIME,B.CADDPRTTIME 182,049,532 14,672 12,408.0 1.6 2493.08 2539.99 17d23235hu010 Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,B .CANDSPLNUMB DSPLNUMB, C.CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLT IME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG,D.CAVDRTN DRTN, COALESCE(C.CANLINENUMB ,C.CANSYSLINENUMB,-1) LINENO FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINO 166,759,885 35,243 4,731.7 1.5 7049.65 7175.51 68b0mrhbvk8yb Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE TRIM(CAVTRAINID)=TRIM(:B1 ) 158,207,451 53,573,818 3.0 1.4 1122.27 1122.34 f2v61b467g910 Module: COAServer.exe SELECT COUNT(CAVLOCONUMB) FROM CCETLOCOMAST WHERE CAVLOCONUMB=:B1 AND CANBASELOC ONUMB IS NOT NULL 151,814,193 7,573 20,046.8 1.4 2569.48 2599.25 91fajhfm2rbus Module: COAServer.exe SELECT /*+ ordered use_nl(t A B) index(A PK_CCETTRAINORDR) */ T.TRAINID,T.TRAINN O,T.DRTN,T.TRAINTYPE,T.SCTNNO,T.SUBSCTNNO,T.DVSNENTRYSTTN, T.STTS,T.TIME,T.LINEN O,T.LATEARVLTIME,T.LATEDPRTTIME,T.ORIGSTTN,T.DSTNSTTN,T.TRMNFLAG, T.ARVLTIME,T.M VMVTFLAG,T.TRNRMRK, T.SDNGSTTN FROM ( SELECT A.CAVTRAINID TRAINID,NVL(A.CAVTRAIN 126,941,278 550 230,802.3 1.1 1210.24 1219.00 2zkd93trbw490 Module: COAServer.exe SELECT DISTINCT TRAINID,TRAINNAME,DRTN,CURRSTTN,STATUS,LASTACTDT,LINENO,PASSGDSF LAG,PRIORITY,COLORCODE,SUBSCTNID,PTTDPRT,STTNSQNC,TRAINSQNC,TRAINRMRK,DSPLSQNC,N EWDSPL,NEWSTTNSQNC,LASTTIME,TRAINTYPE,LOCONUMB,MIDLINEFLAG,PARENTID,ENTRYTIME FR OM ( SELECT A.CAVTRAINID TRAINID,B.CAVTRAINNAME TRAINNAME, NVL(B.CAVDRTN,A.CAVDR 119,807,087 9,225 12,987.2 1.1 2380.30 2495.10 fqmpjygajy9c1 Module: COAServer.exe SELECT F.CAVTRAINID TRAINID, E.CAVTRAINNAME TRAINNAME, C.MANDSPLNUMB SUBSECID, E .CAVTRAINTYPE TRAINTYPE, G.CACLEFLAG ARVLEFLAG, F.CACDPRTLEFLAG DPRTLEFLAG, SPK_ FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGDPRTTIME, 'dd-mm-yy hh24:mi'), 'P ') ARVPICNT, SPK_FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGARVLTIME, 'dd-mm 114,099,248 9,212 12,385.9 1.0 1504.35 1531.41 gfwvgr5sc95vw SQL ordered by Gets DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 11,208,956,212 -> Captured SQL account for 102.3% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,C .CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLTIME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINORDR D WHERE D.CAVTRAINID=C.CAVTRAINID AND B.CAVTRAINID=D.CAVTRAINID AND B.CANTRAINSQNC=D.CA ------------------------------------------------------------- SQL ordered by Reads DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Disk Reads: 685,094 -> Captured SQL account for 22.9% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 47,324 3 15,774.7 6.9 9.05 46.80 5qaxbzhf9fs84 Module: COAServer.exe UPDATE CCEATRAINMVMT SET CAVSTTNCODE=:B2 WHERE TRIM(CAVSTTNCODE)=:B1 27,242 38,256 0.7 4.0 ######## 26383.86 4ytg55vnx6w8v Module: COAServer.exe begin SPK_TRNMVMT.sp_TrnMvmtInsrtnew(trainid=>:trainid, sttncode=>:sttncode, arr vtime=>:arrvtime, depttime=>:depttime, lineno=>:lineno, abnrmlflag=>:abnrmlflag, blksctnname=>:blksctnname, rmk=>:rmk, syslineno=>:syslineno, userid=>:userid, l inenumb=>:linenumb, status=>:status, sttn=>:sttn, errflag=>:errflag, traininblk= 25,869 25,336 1.0 3.8 ######## 28223.79 agp1d79ak8qh1 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, E.CAVLOCONUMB || ' ' || SUBSTR(E.CAVFRONTR EAR, 1, 1) || E.CANLOCOPOSN LOCONUMB, E.CACATCHDTCHFLAG ATCHDTCHFLAG, 'L' WBLFLA G, DECODE(E.CANSQNCNUMB,1,'Y',DECODE(C.CANBOARDID,:B3 ,'N','Y')) ORIGINFLAG,B.CA VBASESHED BASESHED, B.CADSCHEDULEDUEDATE SCHEDULEDATE,SPK_FUNCTIONS.GET_MULTILOC 18,282 37,844 0.5 2.7 ######## 12122.07 c9s24n2vz6h07 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME, 'dd-mm-yy hh24:mi') FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID) = RTRIM(:B3 ) AND CAVSTTNCODE = RTRIM(:B2 )AND CANSQNCNUMB = R TRIM(:B1 ) 10,106 1 10,106.0 1.5 0.88 3.70 a54d41k4f5qs9 Module: SQL*Plus select count(*) from cceatrainpipeline where cavtrainid in ( select cavtrainid from ccettrainordr where CADTRNSCMPLDATE < to_date('15-12-09','dd-mm-yy')) 8,126 1 8,126.0 1.2 6.71 9.81 3z7j9a6743qxb Module: exp.exe SELECT /*+NESTED_TABLE_GET_REFS+*/ "COAARCH"."CCEATRAINMVMT".* FROM "COAARCH"."C CEATRAINMVMT" 6,048 14,113 0.4 0.9 3065.11 3128.94 5p64c5g4vf4w7 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, CASE WHEN C.CAVDRTN IS NU LL THEN (SELECT CAVDRTN FROM CCETTRAINPIPELINE WHERE CANSQNCNUMB=CASE WHEN B.CAN SQNCNUMB>1 THEN B.CANSQNCNUMB-1 ELSE 1 END AND CAVTRAINID=A.CAVTRAINID ) ELSE C. CAVDRTN END AS "Drtn",B.CAVSTTNCODE CURRSTTN, DECODE(B.CADARVLTIME,B.CADDPRTTIME 5,791 23,160 0.3 0.8 9235.73 9524.65 appuckj66mb1c Module: COAServer.exe SELECT CAVTRAINID,CACSTTSFLAG,CAVSTTNCODE FROM CCETTRAINPIPELINE P WHERE P.CACST TSFLAG='D' AND P.CAVTRAINID IN (SELECT * FROM TABLE(SPK_FUNCTIONS.F_CONVERT(:B1 ))) AND P.CANSQNCNUMB=(SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CANB OARDID=:B2 AND CAVTRAINID = P.CAVTRAINID) 5,532 1 5,532.0 0.8 0.75 4.64 ggyjnq6huqkmj Module: SQL*Plus select count(*) from cceatrainmvmt where cavtrainid in ( select cavtrainid fro m ccettrainordr where CADTRNSCMPLDATE < to_date('15-12-09','dd-mm-yy')) 5,483 3 1,827.7 0.8 5.93 11.92 0qcrt43y68fus Module: COAServer.exe UPDATE CCEATRAINMVMT SET CAVBLCKSCTNNAME=REPLACE(TRIM(CAVBLCKSCTNNAME), '-'||:B1 ,'-'||:B2 ) WHERE TRIM(CAVBLCKSCTNNAME) LIKE '%-'||:B1 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Executions: 207,366,148 -> Captured SQL account for 59.2% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 53,573,818 53,573,455 1.0 0.00 0.00 f2v61b467g910 Module: COAServer.exe SELECT COUNT(CAVLOCONUMB) FROM CCETLOCOMAST WHERE CAVLOCONUMB=:B1 AND CANBASELOC ONUMB IS NOT NULL 14,543,154 14,543,116 1.0 0.00 0.00 1nrjfqh7tftjk Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CAVTRAINID = :B1 14,065,036 14,064,975 1.0 0.00 0.00 fhnv0cyw7xwtf Module: COAServer.exe SELECT CASE WHEN COUNT(A.CAVTRAINID) > 0 THEN 'Y' ELSE 'N' END FROM CCETTRAINPIP ELINE A WHERE A.CAVTRAINID = :B3 AND A.CAVSTTNCODE = :B2 AND A.CANSQNCNUMB = :B1 AND LENGTH(TRIM(CACSTTSFLAG))<>0 11,471,848 11,471,799 1.0 0.00 0.00 5cn4ck8frk2m8 Module: COAServer.exe SELECT COUNT(CANBOARDID) FROM CCETTRAINPIPELINE WHERE CAVTRAINID = :B2 AND CANSQ NCNUMB = NVL(:B1 ,-1)+1 10,508,838 10,508,779 1.0 0.00 0.00 awzm6ub8c2j03 Module: COAServer.exe SELECT CANBOARDID FROM CCETTRAINPIPELINE WHERE CAVTRAINID = :B2 AND CANSQNCNUMB = :B1 +1 8,428,066 8,250,405 1.0 0.00 0.00 dd8zqqddt8b98 Module: COAServer.exe SELECT CASE MAVLINECATEGORY WHEN 'REVS' THEN 'Y' ELSE 'N' END FROM CCEMBLOCKSCTN LINE WHERE MAVBLOCKSCTNNAME = :B2 AND MANLINENO = :B1 3,450,205 3,450,223 1.0 0.00 0.00 bbk54hmfkq0kf Module: COAServer.exe SELECT COUNT(*) FROM CCETABNLTRAINMVMT WHERE CAVTRAINID = :B1 AND CANPARTNO > 1 607,660 607,655 1.0 0.00 0.00 ak1w1jr8mqgf1 Module: COAServer.exe SELECT CASE WHEN COUNT(A.CAVTRAINID) > 0 THEN 'Y' ELSE 'N' END FROM CCETTRAINORD R A, CCETTRAINPIPELINE B WHERE A.CAVTRAINID = :B3 AND A.CAVDSTNSTTN = CAVDVSNEXI TSTTN AND A.CAVTRAINID = B.CAVTRAINID AND B.CAVSTTNCODE = :B2 AND B.CANSQNCNUMB = :B1 AND B.CACSTTSFLAG IS NOT NULL 513,056 100,254 0.2 0.00 0.00 c5kay5dujagha Module: COAServer.exe SELECT NVL(CACLINEDSPLFLAG,'N') FROM CCETLINEOCC WHERE CAVSTTN=:B2 AND CANLINENU MB=:B1 341,913 341,913 1.0 0.01 0.01 3v88vny75772r Module: COAServer.exe SELECT COUNT(CAVTRAINID) FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID) = :B2 AND (TR IM(CAVDVSNENTRYSTTN) = :B1 OR TRIM(CAVDVSNEXITSTTN) = :B1 ) AND TRIM(CAVDVSNEXIT STTN) = TRIM(CAVDSTNSTTN) ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Parse Calls: 9,611,634 -> Captured SQL account for 41.1% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 279,705 279,705 2.91 gt2kvqawf0xhk Module: COAServer.exe begin SPK_CHART.sp_cautnspeedrtv(cautnid=>:cautnid, crsrCutnSpeedDtls=>:crsrCutn SpeedDtls); end; 273,513 273,514 2.85 5nfcgzz32km75 Module: COAServer.exe SELECT A.CAVCTNORDRID CTNORDRID,A.CANSQNCNUMB SQNCNUMB,A.CANSTRTLOCNNUMB||'/'||A .CACALFAFROM STRTLOCNNUMB, A.CANSTRTSUBLOCNNUMB||'/'||A.CACSUBALFAFROM STRTSUBLO CNNUMB,A.CANENDLOCNNUMB||'/'||A.CACALFATO ENDLOCNNUMB, A.CANENDSUBLOCNNUMB||'/'| |A.CACSUBALFATO ENDSUBLOCNNUMB,A.CANSPEED SPEED,B.CAVCAUSEDESC CAUSE, TO_CHAR(B. 219,124 219,124 2.28 ga1qc8xmm8y4s Module: COAServer.exe begin SPK_TRNMVMT.sp_trnschdDtls(trainid=>:trainid, sttn=>:sttn, sqno=>:sqno, sc hdarvltime=>:schdarvltime, schddprttime=>:schddprttime, schdflag=>:schdflag); en d; 198,946 198,946 2.07 1ahuv0z7v7dzs Module: COAServer.exe SELECT CAVBLCKRSTNID,CANSQNCNUMB,TO_CHAR(CADRQSTSTRTTIME,'DD-MM-YY HH24:MI'), TO _CHAR(CADRQSTENDTIME,'DD-MM-YY HH24:MI'),TO_CHAR(CADPRMTSTRTTIME,'DD-MM-YY HH24: MI'), TO_CHAR(CADPRMTENDTIME,'DD-MM-YY HH24:MI'),CANEXTNTIME,CAVPRIVATENUMB,CAVR MRK FROM CCETBLCKEXTN WHERE CAVBLCKRSTNID =:B1 ORDER BY CAVBLCKRSTNID,CANSQNCNUM 198,181 198,181 2.06 asccxzs9bjhs8 Module: COAServer.exe begin SPK_CHART.sp_BlockBurstRtv(blockid=>:blockid, crsrblockburst=>:crsrblockbu rst); end; 107,974 107,974 1.12 ftj9uawt4wwzb select condition from cdef$ where rowid=:1 106,241 185,553 1.11 aadt0jcbysby4 Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CAVTRAINID=:B1 80,670 80,670 0.84 55t5whwjjrknj Module: COAServer.exe begin SPK_TRNMVMT.sp_trainpipeline(boardid=>:boardid, trainid=>:trainid, crsrtrn pipeline=>:crsrtrnpipeline); end; 79,739 79,739 0.83 6rdnqf32vdw75 Module: COAServer.exe begin SPK_TRNMVMT.sp_blcksctndtls(trainid=>:trainid, blcksctnname=>:blcksctnname , crsrblksctnlinedtls=>:crsrblksctnlinedtls); end; 78,531 78,531 0.82 0jft3yc2uvzgs Module: COAServer.exe SELECT BOARDID,STTNCODE,SEQNUMB,STATUS,ISDESTSTTN,SCTNNO, ARVLTIME, DEPTTIME,SDN GFLAG,FINALTRM FROM (SELECT DISTINCT C.MANBOARDID BOARDID,A.CAVSTTNCODE STTNCODE ,A.CANSQNCNUMB SEQNUMB, A.CACSTTSFLAG STATUS,DECODE(A.CANSQNCNUMB,:B3 ,'L','N') ISDESTSTTN,A.CANDSPLNUMB SCTNNO, TO_CHAR(F.CADARVLTIME,'dd-mm-yy HH24:MI') ARVLT ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- 448.75 1.7 26,383.86 25,829.76 38,256 4ytg55vnx6w8v Module: COAServer.exe begin SPK_TRNMVMT.sp_TrnMvmtInsrtnew(trainid=>:trainid, sttncode=>:sttncode, arr vtime=>:arrvtime, depttime=>:depttime, lineno=>:lineno, abnrmlflag=>:abnrmlflag, blksctnname=>:blksctnname, rmk=>:rmk, syslineno=>:syslineno, userid=>:userid, l inenumb=>:linenumb, status=>:status, sttn=>:sttn, errflag=>:errflag, traininblk= 242.50 26.9 902.09 632.76 14,492 89fy9mms4xx45 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, (SELECT X.CAVDRT N FROM CCETTRAINPIPELINE X WHERE X.CANSQNCNUMB =(SELECT MIN(Y.CANSQNCNUMB) FROM CCETTRAINPIPELINE Y WHERE Y.CAVTRAINID=C.CAVTRAINID AND Y.CANBOARDID=:B2 ) AND X .CAVTRAINID=C.CAVTRAINID) DRTN, B.CAVSTTNCODE CURRSTTN, CASE WHEN C.CACSTTSFLAG= 201.96 2.1 9,524.65 9,235.73 23,160 appuckj66mb1c Module: COAServer.exe SELECT CAVTRAINID,CACSTTSFLAG,CAVSTTNCODE FROM CCETTRAINPIPELINE P WHERE P.CACST TSFLAG='D' AND P.CAVTRAINID IN (SELECT * FROM TABLE(SPK_FUNCTIONS.F_CONVERT(:B1 ))) AND P.CANSQNCNUMB=(SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE CANB OARDID=:B2 AND CAVTRAINID = P.CAVTRAINID) 123.79 42.5 291.54 190.59 78,531 0jft3yc2uvzgs Module: COAServer.exe SELECT BOARDID,STTNCODE,SEQNUMB,STATUS,ISDESTSTTN,SCTNNO, ARVLTIME, DEPTTIME,SDN GFLAG,FINALTRM FROM (SELECT DISTINCT C.MANBOARDID BOARDID,A.CAVSTTNCODE STTNCODE ,A.CANSQNCNUMB SEQNUMB, A.CACSTTSFLAG STATUS,DECODE(A.CANSQNCNUMB,:B3 ,'L','N') ISDESTSTTN,A.CANDSPLNUMB SCTNNO, TO_CHAR(F.CADARVLTIME,'dd-mm-yy HH24:MI') ARVLT 98.14 0.3 28,223.79 28,009.70 25,336 agp1d79ak8qh1 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, E.CAVLOCONUMB || ' ' || SUBSTR(E.CAVFRONTR EAR, 1, 1) || E.CANLOCOPOSN LOCONUMB, E.CACATCHDTCHFLAG ATCHDTCHFLAG, 'L' WBLFLA G, DECODE(E.CANSQNCNUMB,1,'Y',DECODE(C.CANBOARDID,:B3 ,'N','Y')) ORIGINFLAG,B.CA VBASESHED BASESHED, B.CADSCHEDULEDUEDATE SCHEDULEDATE,SPK_FUNCTIONS.GET_MULTILOC 92.07 3.7 2,495.10 2,380.30 9,225 fqmpjygajy9c1 Module: COAServer.exe SELECT F.CAVTRAINID TRAINID, E.CAVTRAINNAME TRAINNAME, C.MANDSPLNUMB SUBSECID, E .CAVTRAINTYPE TRAINTYPE, G.CACLEFLAG ARVLEFLAG, F.CACDPRTLEFLAG DPRTLEFLAG, SPK_ FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGDPRTTIME, 'dd-mm-yy hh24:mi'), 'P ') ARVPICNT, SPK_FUNCTIONS.GET_PIDOCOUNT(:B3 , TO_CHAR(F.CADRPTGARVLTIME, 'dd-mm 80.33 96.5 83.21 0.02 45 aq8yqxyyb40nn update sys.job$ set this_date=:1 where job=:2 79.30 92.3 85.90 5.78 1,427 cydnuss99swtd Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 77.48 96.5 80.26 0.36 1,520 g17xjfym83gqb Module: OEM.SystemPool SELECT T.TARGET_GUID, T.TARGET_TYPE, M.METRIC_GUID FROM MGMT_TARGETS T, MGMT_MET RICS M, MGMT_CURRENT_AVAILABILITY A WHERE T.TARGET_TYPE = M.TARGET_TYPE AND T.TA RGET_GUID = A.TARGET_GUID AND A.CURRENT_STATUS = :B4 AND M.TYPE_META_VER = T.TYP E_META_VER AND (M.CATEGORY_PROP_1 = T.CATEGORY_PROP_1 OR M.CATEGORY_PROP_1 = ' ' 62.71 45.5 137.73 85.00 219,124 ga1qc8xmm8y4s Module: COAServer.exe begin SPK_TRNMVMT.sp_trnschdDtls(trainid=>:trainid, sttn=>:sttn, sqno=>:sqno, sc hdarvltime=>:schdarvltime, schddprttime=>:schddprttime, schdflag=>:schdflag); en SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- d; 52.66 99.3 53.05 0.54 5,122 2fpz4r47xss40 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME,'dd-mm-yy hh24:mi'),TO_CHAR(CADSCHDDPRTTIME,'dd-mm -yy hh24:mi'),CASE WHEN :B4 =0 THEN 'Y' ELSE 'N' END FROM CCETTRAINPIPELINE WHER E CAVTRAINID=:B3 AND CAVSTTNCODE=:B2 AND CANSQNCNUMB=:B1 45.51 22.2 205.17 106.24 1,549 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 45.07 1.6 2,811.07 2,664.43 40,749 7tnc1wa85j90n Module: COAServer.exe SELECT DISTINCT C.CANBOARDID BOARDID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_ SUBSECTIONID (C.CANBOARDID ,H.CAVTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SECTI ONID, CASE WHEN :B4 = 'Y'THEN SPK_FUNCTIONS.GET_SUBSECTIONID (C.CANBOARDID ,H.CA VTRAINID,H.CAVSTTN) ELSE C.CANDSPLNUMB END SUBSECTIONID, B.CAVTRAINID TRAINID, T 40.60 0.3 12,122.07 11,972.69 37,844 c9s24n2vz6h07 Module: COAServer.exe SELECT TO_CHAR(CADEXPARVLTIME, 'dd-mm-yy hh24:mi') FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID) = RTRIM(:B3 ) AND CAVSTTNCODE = RTRIM(:B2 )AND CANSQNCNUMB = R TRIM(:B1 ) 38.41 38.2 100.56 92.66 79,739 6rdnqf32vdw75 Module: COAServer.exe begin SPK_TRNMVMT.sp_blcksctndtls(trainid=>:trainid, blcksctnname=>:blcksctnname , crsrblksctnlinedtls=>:crsrblksctnlinedtls); end; 36.41 0.1 26,531.80 26,468.88 9,300 7dmt4t1vkxuud Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL, DPRTARVLTIME , DPRTTIME , ARVLLATE,DEPTLATE,LI NENO,SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN, STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRK CODE,BLKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRD 35.07 21.1 166.37 150.54 46,561 c6kqst085urmh Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL, DPRTARVLTIME ,DPRTTIME ,ARVLLATE,DEPTLATE,LINE NO,SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN, STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRKCO DE,BLKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRDID 33.94 0.5 7,175.51 7,049.65 35,243 68b0mrhbvk8yb Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE TRIM(CAVTRAINID)=TRIM(:B1 ) 29.83 14.5 205.54 160.72 9,882 949twt84dwxzu Module: w3wp.exe SELECT DISTINCT A.CAVTRAINID TRAINID, A.CAVTRAINNAME TNAME, A.CAVTRAINNUMB TNUMB , A.CAVTRAINTYPE TRAINTYPE , C.CAVDRTN, B.CAVSTTNCODE LASTRPTGSTAION,E.MAVSTTNNA ME STTNNAME, D.MAVSUBSCTNCODE SCNTNAME,TO_CHAR(B.CADARVLTIME,'DD-MM-YY HH24:MI') ARVLTIME,TO_CHAR(B.CADDPRTTIME,'DD-MM-YY HH24:MI') DPTRTIME ,TO_CHAR(C.CADSCHDD 27.31 0.6 4,337.16 4,229.20 31,154 1vh8uk4tw4fww Module: COAServer.exe SELECT DISTINCT A.CACSTTSFLAG,A.CAVTRAINNAME FROM CCETTRAINPIPELINE A,CCETTRAINM VMT B,CCETTRAINORDR C WHERE A.CAVTRAINID=B.CAVTRAINID AND A.CAVTRAINID=C.CAVTRAI SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- NID AND A.CAVBLCKSCTNNAME=B.CAVBLCKSCTNNAME AND C.CACTRNSCMPLFLAG='N' AND B.CACL ASTREPTFLAG='Y' AND A.CACSTTSFLAG='D' AND CANDSPLNUMB = :B2 AND B.CAVBLCKSCTNNAM 25.11 0.1 17,694.06 17,652.83 5,583 56h1rmntt2tn8 Module: COAServer.exe SELECT DISTINCT BOARDID,SECTIONID,SUBSCTNID,TRINID,TRAINNAME,TRAINTYPE,DIRECTION ,SEQNO,ISARVLACTUAL,ISDEPTACTUAL,DPRTARVLTIME,DPRTTIME,ARVLLATE,DEPTLATE,LINENO, SCHARVL,SCHDEPT,TIMEFLAG,LOSS,GAIN,STTNCODE,ARVLHALT,DPRTHALT,VIASTTN,RMRKCODE,B LKRMRKCODE,DRAWFLAG,LASTRPTFLG,STTSFLAG,ETD,DESTNFLAG,ALLPFLAG,PREVSTTNBRDID,NXT 21.62 18.7 115.61 111.85 64,729 c7hnvhqxmc3ct Module: COAServer.exe begin SPK_TRNMVMT.sp_nextsttndtls(boardid=>:boardid, trainid=>:trainid, nextsttn =>:nextsttn, linenumb=>:linenumb, crsrlineOcupDtls=>:crsrlineOcupDtls, exparvlti me=>:exparvltime, expdprttime=>:expdprttime, endflag=>:endflag, crossflg=>:cross flg); end; 17.80 34.3 51.85 50.36 77,116 a789hza5dgsdb Module: COAServer.exe SELECT DISTINCT RTRIM(A.CAVBLCKSCTN) BLCKSCTNN,A.CANLINENO LINENO, A.CAVDRTN DTR N,RTRIM(C.CAVTRAINNAME) TRAINNAME,A.CACSTTS STATUS,B.MANLINENO||' '||SUBSTR(B.MA VDRTN,1,1)||' '||SUBSTR(B.MAVLINECATEGORY,1,1) LINENAME,A.CANBOARDID BOARDID FRO M CCETBLCKSCTNLINEOCC A,CCEMBLOCKSCTNLINE B,CCETTRAINPIPELINE C WHERE A.CAVBLCKS 17.49 0.6 3,128.94 3,065.11 14,113 5p64c5g4vf4w7 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID, C.CAVTRAINNAME TRAINNAME, CASE WHEN C.CAVDRTN IS NU LL THEN (SELECT CAVDRTN FROM CCETTRAINPIPELINE WHERE CANSQNCNUMB=CASE WHEN B.CAN SQNCNUMB>1 THEN B.CANSQNCNUMB-1 ELSE 1 END AND CAVTRAINID=A.CAVTRAINID ) ELSE C. CAVDRTN END AS "Drtn",B.CAVSTTNCODE CURRSTTN, DECODE(B.CADARVLTIME,B.CADDPRTTIME 15.71 33.6 46.80 9.05 3 5qaxbzhf9fs84 Module: COAServer.exe UPDATE CCEATRAINMVMT SET CAVSTTNCODE=:B2 WHERE TRIM(CAVSTTNCODE)=:B1 13.09 34.8 37.62 36.77 77,003 dkpyhxv5m7psq Module: COAServer.exe SELECT DISTINCT RTRIM(A.CAVBLCKSCTN) BLCKSCTNN,A.CANLINENO LINENO, A.CAVDRTN DTR N, '' TRAINNAME, 'F' STATUS, B.MANLINENO||' '||SUBSTR(B.MAVDRTN,1,1)||' '||SUBST R(B.MAVLINECATEGORY,1,1) LINENAME,C.CANBOARDID BOARDID,A.CACSKIPCHECKFLAG SKIPCH ECKFLAG FROM CCETBLCKSCTNLINEOCC A,CCEMBLOCKSCTNLINE B,CCETTRAINPIPELINE C, CCEM 11.71 2.7 427.63 427.19 10,508,838 awzm6ub8c2j03 Module: COAServer.exe SELECT CANBOARDID FROM CCETTRAINPIPELINE WHERE CAVTRAINID = :B2 AND CANSQNCNUMB = :B1 +1 11.30 0.4 2,599.25 2,569.48 7,573 91fajhfm2rbus Module: COAServer.exe SELECT /*+ ordered use_nl(t A B) index(A PK_CCETTRAINORDR) */ T.TRAINID,T.TRAINN O,T.DRTN,T.TRAINTYPE,T.SCTNNO,T.SUBSCTNNO,T.DVSNENTRYSTTN, T.STTS,T.TIME,T.LINEN O,T.LATEARVLTIME,T.LATEDPRTTIME,T.ORIGSTTN,T.DSTNSTTN,T.TRMNFLAG, T.ARVLTIME,T.M VMVTFLAG,T.TRNRMRK, T.SDNGSTTN FROM ( SELECT A.CAVTRAINID TRAINID,NVL(A.CAVTRAIN 10.74 0.2 6,469.21 6,421.54 14,631 2kcbn01p7ur00 Module: COAServer.exe SELECT '' SCTNNUMB, SPK_FUNCTIONS.GET_SCNTNUMB(:B3 , A.CAVSTTNCODE, C.CANDSPLNUM B, CASE WHEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X.CAVTRAINID = B.CAVTRAINID AND X.CANSQNCNUMB = C.CANSQNCNUMB+1 AND X.CANBOARDID = C.CANBOARDI D) <> C.CANDSPLNUMB THEN (SELECT X.CANDSPLNUMB FROM CCETTRAINPIPELINE X WHERE X. SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- 9.75 50.8 19.19 18.07 41,767 803b7z0t84sq7 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n ext_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, jo b 7.38 96.4 7.66 0.61 638 0s8d1q7rxgsfg Module: COAServer.exe UPDATE CCETBLCKSCTNLINEOCC SET CAVTRAINID=NULL,CAVTRAINNAME=NULL,CACSTTS='F',CAV RMRK=NULL WHERE CAVTRAINID=TRIM(:B1 ) AND CACSTTS='O' 7.05 0.5 1,531.41 1,504.35 9,212 gfwvgr5sc95vw Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,C .CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLTIME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINORDR D WHERE D.CAVTRAINID=C.CAVTRAINID AND B.CAVTRAINID=D.CAVTRAINID AND B.CANTRAINSQNC=D.CA 7.00 4.2 167.87 162.92 34,140 48jr61j40n95t Module: COAServer.exe SELECT A.CAVTRAINID TRAINID,B.CAVTRAINNAME TRAINNAME, NVL(B.CAVDRTN,A.CAVDRTN) D RTN, NVL(A.CAVDVSNENTRYSTTN,A.CAVORIGSTTN) CURRSTTN,DECODE(A.CAVDVSNENTRYSTTN,A. CAVORIGSTTN,'N','C') STATUS, TO_CHAR(CASE WHEN A.CAVDVSNENTRYSTTN <> A.CAVORIGST TN THEN NVL(A.CADETA, A.CADETD) ELSE B.CADEXPDPRTTIME END,'dd-mm-yy hh24:mi') LA 6.85 0.6 1,219.00 1,210.24 550 2zkd93trbw490 Module: COAServer.exe SELECT DISTINCT TRAINID,TRAINNAME,DRTN,CURRSTTN,STATUS,LASTACTDT,LINENO,PASSGDSF LAG,PRIORITY,COLORCODE,SUBSCTNID,PTTDPRT,STTNSQNC,TRAINSQNC,TRAINRMRK,DSPLSQNC,N EWDSPL,NEWSTTNSQNC,LASTTIME,TRAINTYPE,LOCONUMB,MIDLINEFLAG,PARENTID,ENTRYTIME FR OM ( SELECT A.CAVTRAINID TRAINID,B.CAVTRAINNAME TRAINNAME, NVL(B.CAVDRTN,A.CAVDR 6.43 0.3 2,539.99 2,493.08 14,672 17d23235hu010 Module: COAServer.exe SELECT DISTINCT C.CAVTRAINID TRAINID,B.CAVTRAINNAME TRNNAME,C.CAVSTTNCODE STTN,B .CANDSPLNUMB DSPLNUMB, C.CAVRMRKCODE RMRKCODE,C.CAVRMRK RMRK, TO_CHAR(C.CADARVLT IME,'DD-MM-YY HH24:MI') RMRKTIME,'A' FLAG,D.CAVDRTN DRTN, COALESCE(C.CANLINENUMB ,C.CANSYSLINENUMB,-1) LINENO FROM CCETTRAINPIPELINE B,CCETTRAINMVMT C,CCETTRAINO 6.08 0.3 2,127.66 2,052.11 24,904 c2cd2rrr1j5c3 Module: COAServer.exe SELECT DISTINCT A.CAVTRAINID TRAINID, NVL((SELECT SUM(B.CANTOTLUNIT) FROM CCETTR AINATCHDTCH B WHERE B.CAVTRAINID = A.CAVTRAINID AND B.CANSQNCNUMB <= (CASE (SELE CT MIN(X.CANSQNCNUMB) FROM CCETTRAINPIPELINE X WHERE X.CANBOARDID = :B3 AND X.CA VTRAINID = A.CAVTRAINID ) WHEN 1 THEN 1 ELSE (SELECT MIN(X.CANSQNCNUMB) FROM CCE 5.81 1.1 525.35 524.58 14,065,036 fhnv0cyw7xwtf Module: COAServer.exe SELECT CASE WHEN COUNT(A.CAVTRAINID) > 0 THEN 'Y' ELSE 'N' END FROM CCETTRAINPIP ELINE A WHERE A.CAVTRAINID = :B3 AND A.CAVSTTNCODE = :B2 AND A.CANSQNCNUMB = :B1 AND LENGTH(TRIM(CACSTTSFLAG))<>0 5.24 66.0 7.95 6.98 2,787 fc88h6z0gmg5v Module: COAServer.exe INSERT INTO P_CCEIPTRAINMVMT VALUES ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , NULL, P_CCEIPTRAINMVMT_SEQ.NEXTVAL, 0, SYSDATE, 1, NULL, NULL, ' N', -1, NULL) SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- 5.21 10.4 49.93 44.63 35 9h1g6vzwgrmfw Module: COAServer.exe begin SPK_TRAINORDER.sp_chngdstndvrsn(dvrsnflag=>:dvrsnflag, trainname=>:trainna me, destnsttn=>:destnsttn, exitsttn=>:exitsttn, route=>:route, authnumb=>:authnu mb, authdate=>:authdate, authby=>:authby, userid=>:userid, segmntstring=>:segmnt string, trnid=>:trnid, adjdvsn=>:adjdvsn, boardid=>:boardid, linktrnnumb=>:linkt 4.61 0.2 2,165.73 2,121.56 17,027 7cmnmyraycd4f Module: COAServer.exe SELECT DISTINCT A.CAVBLCKRSTNID, CASE WHEN A.CAVBLOCKSCTNNAME IS NULL THEN CASE WHEN A.CAVSTTN = 'KOK' AND D.MANDSPLNUMB = 1 THEN 21 ELSE D.MANDSPLNUMB END ELSE SPK_FUNCTIONS.SF_BLCKDSPLNUMB(A.CAVBLOCKSCTNNAME,F.MANBOARDID,A.CAVBLCKRSTNID) END CANSCTNNUMB, A.CAVSTTN, SUBSTR(A.CAVBLOCKSCTNNAME,1,INSTR(CAVBLOCKSCTNNAME,' 4.56 0.1 3,144.28 3,103.33 341,913 3v88vny75772r Module: COAServer.exe SELECT COUNT(CAVTRAINID) FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID) = :B2 AND (TR IM(CAVDVSNENTRYSTTN) = :B1 OR TRIM(CAVDVSNEXITSTTN) = :B1 ) AND TRIM(CAVDVSNEXIT STTN) = TRIM(CAVDSTNSTTN) 3.48 58.1 5.99 5.71 25,503 6k23knu2323yy Module: COAServer.exe SELECT MIN(CANLINENUMB) FROM CCETLINEOCC WHERE CAVSTTN=:B1 AND CACSTTS ='F' 3.26 87.8 3.71 1.18 1,577 6m745ttt4dmz7 Module: COAServer.exe UPDATE CCETLINEOCC SET CAVTRAINID=TRIM(:B4 ),CAVTRAINNAME=TRIM(:B3 ), CACSTTS='O ' WHERE CAVSTTN=TRIM(:B2 ) AND CANLINENUMB=:B1 AND CACSTTS <> 'B' 2.96 0.9 314.60 310.33 699 3y0k8cah4tzt8 Module: COAServer.exe SELECT A.CAVTRAINID TRAINID,B.CAVTRAINNAME TRAINNAME, NVL(B.CAVDRTN,A.CAVDRTN) D RTN, NVL(A.CAVDVSNENTRYSTTN,A.CAVORIGSTTN) CURRSTTN,DECODE(A.CAVDVSNENTRYSTTN,A. CAVORIGSTTN,'N','C') STATUS, TO_CHAR(CASE WHEN A.CAVDVSNENTRYSTTN <> A.CAVORIGST TN THEN NVL(A.CADETA, A.CADETD) ELSE B.CADEXPDPRTTIME END,'dd-mm-yy hh24:mi') LA 2.77 65.2 4.26 4.03 2,141 g275bvanf7jc8 UPDATE P_CCEIPTRAINMVMT SET ADB_L_DELIVERY_STATUS = 'C', ADB_TRACKINGID = :V1 WH ERE ADB_SEQUENCE = :V2 2.67 88.3 3.02 0.48 1 a6n0b4mcgas86 Module: COAServer.exe SELECT COUNT(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE RTRIM(CAVTRAINID)=RTRIM(: B1 ) 2.56 93.3 2.74 0.29 1,597 f4d7kna4uhj0v Module: COAServer.exe SELECT COUNT(*) FROM CCEMBLOCKSCTNLINE X, CCETTRAINMVMT Y WHERE Y.CAVTRAINID = : B1 AND X.MAVBLOCKSCTNNAME=Y.CAVBLCKSCTNNAME AND Y.CACLASTREPTFLAG = 'Y' AND X.MA NLINENO= NVL(Y.CANSYSBLCKSCTNLINE, Y.CANBLCKSCTNLINE) 2.42 13.7 17.65 16.86 3,359 52w608cpfxd4p Module: COAServer.exe SELECT DISTINCT C.CANDSPLNUMB SCTNNUMB,C.CANDSPLNUMB SUBSCTNNUMB,A.CAVTRAINID TR AINID, NVL(B.CAVTRAINNUMB,B.CAVTRAINNAME) TRAINNAME,A.CAVSTTNCODE STATION,TO_CHA R(E.CANTOTLUNIT) LOCONUMB,E.CACATCHDTCHFLAG ATCHDTCHFLAG,B.CAVDRTN DRTN, TO_CHAR (A.CADARVLTIME,'DD-MM-YY HH24:MI') ARVLTIME,TO_CHAR(A.CADDPRTTIME,'DD-MM-YY HH24 2.26 66.3 3.41 0.84 58 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- 2.18 91.4 2.38 0.14 1 89qyn4bbt03jq SELECT /*+ USE_HASH(f a) */ SUM(NVL(F.BYTES - NVL(A.BYTES, 0), 0)/1024/1024), SU M(NVL(F.BYTES, 0)/1024/1024) FROM (SELECT DISTINCT TABLESPACE_NAME FROM USER_TAB LES) U, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY T ABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE 2.17 6.3 34.70 34.05 77,487 7fdjxja58m7az Module: COAServer.exe begin SPK_TRNMVMT.sp_nxtblcksctndtls(boardid=>:boardid, trainid=>:trainid, sttn= >:sttn, blcksctnname=>:blcksctnname, crsrblksctnlinedtls=>:crsrblksctnlinedtls, sttnsqnc=>:sttnsqnc); end; 2.13 17.9 11.92 5.93 3 0qcrt43y68fus Module: COAServer.exe UPDATE CCEATRAINMVMT SET CAVBLCKSCTNNAME=REPLACE(TRIM(CAVBLCKSCTNNAME), '-'||:B1 ,'-'||:B2 ) WHERE TRIM(CAVBLCKSCTNNAME) LIKE '%-'||:B1 2.06 73.1 2.82 0.92 3,346 9rd4rw5g5cu47 Module: SQL*Plus INSERT INTO CCETTRAINPIPELINE (CAVTRAINID,CANTRAINSQNC,CANSQNCNUMB,CAVSTTNCODE, CAVTRAINTYPE,CACSTTSFLAG,CAVTRAINNAME,CANGRPPRTY,CANPRIORITY,CANSPEED,CAVDRTN, C ANDSPLNUMB,CANBOARDID,CADEXPARVLTIME,CADEXPDPRTTIME,CADSCHDDPRTTIME,CANRUNNINGTI ME, CACSTTNCLASS,CACCROSSFLAG,CAVCROSSINGTRAIN,CAVCROSSINGTIME,CANTOTLALLOWNCE, 1.78 8.7 20.50 20.29 9,865 gjkd4162p162s Module: COAServer.exe SELECT DISTINCT A.CAVSTTN STTN,A.CANLINENUMB LINENUMB,A.CACLINETYPE LINETYPE, A. CAVDRTN DTRN,A.CACPLATFORMFLAG PLATFORMFLAG,A.CAVTRAINID TRAINID, A.CAVTRAINNAME TRAINNAME, TO_CHAR(A.CADFROMTIME,'DD-MM-YY HH24:MI') FROMTIME, TO_CHAR(A.CADEXP TOTIME,'DD-MM-YY HH24:MI') TOTIME,A.CACSTTS STTS,A.CAVRMRK RMRK, NVL(E.MANFOURWH 1.64 0.3 493.11 437.15 35,761 71xjh9nrcjfh9 Module: COAServer.exe SELECT CAVTRAINNUMB,CANTRAINSQNC,CAVTRAINNAME,CADTRAINDATE,CAVORIGSTTN,CAVDSTNST TN,CAVDVSNEXITSTTN,CACPASSGDSFLAG, CAVCRNTDVSN,CAVORIGDVSN,CAVHANDOVERDVSN,TRIM( CAVTRNSFRTRAINID),CANCHNGDURN FROM CCETTRAINORDR WHERE TRIM(CAVTRAINID)=TRIM(:B1 ) 1.44 22.1 6.52 4.74 50 913yr7bw582b3 Module: COAServer.exe begin SPK_AUTOTRAINORDR.Sp_Pipeline(trainname=>:trainname, origsttn=>:origsttn, dstnsttn=>:dstnsttn, dvsnentrysttn=>:dvsnentrysttn, dvsnexitsttn=>:dvsnexitsttn, etd=>:etd, ordrdrtn=>:ordrdrtn, ordrtraintype=>:ordrtraintype, segmntstr=>:segm ntstr, routestr=>:routestr, gauge=>:gauge, leflag=>:leflag, stcktype=>:stcktype, 1.43 0.1 1,122.34 1,122.27 53,573,818 f2v61b467g910 Module: COAServer.exe SELECT COUNT(CAVLOCONUMB) FROM CCETLOCOMAST WHERE CAVLOCONUMB=:B1 AND CANBASELOC ONUMB IS NOT NULL 1.38 62.9 2.20 0.70 1 1arnzz56ypthm Module: COAServer.exe SELECT B.CAVSTTNCODE,A.CAVTRAINTYPE,RTRIM(NVL(CAVDVSNEXITSTTN,CAVDSTNSTTN)), TO_ CHAR(DECODE(B.CANSQNCNUMB,1,NVL(B.CADARVLTIME,B.CADDPRTTIME),NVL(B.CADDPRTTIME,B .CADARVLTIME)),'dd-mm-yy hh24:mi'), TO_CHAR(B.CADDPRTTIME,'dd-mm-yy hh24:mi'),A. CANTRAINSQNC FROM CCETTRAINORDR A,CCETTRAINMVMT B WHERE RTRIM(A.CAVTRAINID)=RTRI 1.36 4.3 31.55 30.73 607,660 ak1w1jr8mqgf1 Module: COAServer.exe SQL ordered by Cluster Wait Time DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- SELECT CASE WHEN COUNT(A.CAVTRAINID) > 0 THEN 'Y' ELSE 'N' END FROM CCETTRAINORD R A, CCETTRAINPIPELINE B WHERE A.CAVTRAINID = :B3 AND A.CAVDSTNSTTN = CAVDVSNEXI TSTTN AND A.CAVTRAINID = B.CAVTRAINID AND B.CAVSTTNCODE = :B2 AND B.CANSQNCNUMB = :B1 AND B.CACSTTSFLAG IS NOT NULL 1.32 13.4 9.81 6.71 1 3z7j9a6743qxb Module: exp.exe SELECT /*+NESTED_TABLE_GET_REFS+*/ "COAARCH"."CCEATRAINMVMT".* FROM "COAARCH"."C CEATRAINMVMT" 1.28 68.9 1.86 1.84 3,590 fnz96y69h28xh SELECT * FROM P_CCEIPTRAINMVMT WHERE ADB_L_DELIVERY_STATUS = 'N' ORDER BY ADB_SE QUENCE 1.10 40.2 2.74 2.27 5,389 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 1.07 20.5 5.22 2.12 7,789 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 .98 32.6 3.02 2.92 10,734 8g1yybj29gzuv Module: COAServer.exe SELECT MAX(CANSQNCNUMB) FROM CCETTRAINPIPELINE WHERE (CACSTTSFLAG IS NOT NULL AN D CACRPTGFLAG = 'Y') AND CAVTRAINID=:B1 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 14,642,295 22.6 37.2 CPU used when call started 14,558,321 22.5 37.0 CR blocks created 254,719 0.4 0.7 Cached Commit SCN referenced 51,451 0.1 0.1 Commit SCN cached 309 0.0 0.0 DB time 182,336,784 281.4 463.5 DBWR checkpoint buffers written 360,731 0.6 0.9 DBWR checkpoints 367 0.0 0.0 DBWR fusion writes 96,898 0.2 0.3 DBWR object drop buffers written 4 0.0 0.0 DBWR thread checkpoint buffers w 360,731 0.6 0.9 DBWR transaction table writes 4,236 0.0 0.0 DBWR undo block writes 87,048 0.1 0.2 DFO trees parallelized 7,962 0.0 0.0 IPC CPU used by this session 78,522 0.1 0.2 PX local messages recv'd 46,139 0.1 0.1 PX local messages sent 46,139 0.1 0.1 PX remote messages recv'd 41,270 0.1 0.1 PX remote messages sent 47,852 0.1 0.1 Parallel operations downgraded t 7 0.0 0.0 Parallel operations not downgrad 7,962 0.0 0.0 RowCR - resume 477 0.0 0.0 RowCR - row contention 16,823 0.0 0.0 RowCR attempts 365,132 0.6 0.9 RowCR hits 349,242 0.5 0.9 SMON posted for instance recover 1 0.0 0.0 SMON posted for undo segment shr 210 0.0 0.0 SQL*Net roundtrips to/from clien 10,711,949 16.5 27.2 active txn count during cleanout 168,282 0.3 0.4 application wait time 14,329 0.0 0.0 background checkpoints completed 38 0.0 0.0 background checkpoints started 38 0.0 0.0 background timeouts 4,956,748 7.7 12.6 branch node splits 5 0.0 0.0 buffer is not pinned count 5,443,488,479 8,400.3 13,838.4 buffer is pinned count 34,725,982,433 53,588.6 88,280.2 bytes received via SQL*Net from 2,057,624,979 3,175.3 5,230.9 bytes sent via SQL*Net to client 5,793,185,998 8,940.0 14,727.4 calls to get snapshot scn: kcmgs 242,090,868 373.6 615.4 calls to kcmgas 3,117,977 4.8 7.9 calls to kcmgcs 27,194 0.0 0.1 change write time 6,543 0.0 0.0 cleanout - number of ktugct call 280,789 0.4 0.7 cleanouts and rollbacks - consis 146,141 0.2 0.4 cleanouts only - consistent read 93,208 0.1 0.2 cluster key scan block gets 2,032,666 3.1 5.2 cluster key scans 957,465 1.5 2.4 cluster wait time 290,996 0.5 0.7 commit batch performed 735 0.0 0.0 commit batch requested 735 0.0 0.0 commit batch/immediate performed 8,773 0.0 0.0 commit batch/immediate requested 8,773 0.0 0.0 commit cleanout failures: block 8,291 0.0 0.0 commit cleanout failures: buffer 38 0.0 0.0 commit cleanout failures: callba 1,267 0.0 0.0 commit cleanout failures: cannot 36 0.0 0.0 commit cleanouts 1,370,043 2.1 3.5 commit cleanouts successfully co 1,360,411 2.1 3.5 commit immediate performed 8,038 0.0 0.0 commit immediate requested 8,038 0.0 0.0 Instance Activity Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit txn count during cleanout 110,064 0.2 0.3 concurrency wait time 38,014 0.1 0.1 consistent changes 905,307 1.4 2.3 consistent gets 11,199,149,842 17,282.4 28,470.4 consistent gets - examination 1,248,733,969 1,927.0 3,174.5 consistent gets direct 2,722 0.0 0.0 consistent gets from cache 11,200,005,777 17,283.7 28,472.6 current blocks converted for CR 23,631 0.0 0.1 cursor authentications 33,444 0.1 0.1 data blocks consistent reads - u 855,338 1.3 2.2 db block changes 10,615,089 16.4 27.0 db block gets 9,806,386 15.1 24.9 db block gets direct 16,348 0.0 0.0 db block gets from cache 9,778,893 15.1 24.9 deferred (CURRENT) block cleanou 759,547 1.2 1.9 dirty buffers inspected 234,624 0.4 0.6 enqueue conversions 2,384,605 3.7 6.1 enqueue releases 104,872,082 161.8 266.6 enqueue requests 104,881,866 161.9 266.6 enqueue timeouts 9,784 0.0 0.0 enqueue waits 98,016 0.2 0.3 exchange deadlocks 44 0.0 0.0 execute count 207,366,148 320.0 527.2 frame signature mismatch 4 0.0 0.0 free buffer inspected 2,258,352 3.5 5.7 free buffer requested 3,322,724 5.1 8.5 gc CPU used by this session 71,129 0.1 0.2 gc cr block build time 1,391 0.0 0.0 gc cr block flush time 5,601 0.0 0.0 gc cr block receive time 152,081 0.2 0.4 gc cr block send time 14,586 0.0 0.0 gc cr blocks received 1,153,693 1.8 2.9 gc cr blocks served 1,042,500 1.6 2.7 gc current block flush time 140 0.0 0.0 gc current block pin time 595 0.0 0.0 gc current block receive time 66,150 0.1 0.2 gc current block send time 6,802 0.0 0.0 gc current blocks received 512,539 0.8 1.3 gc current blocks served 525,488 0.8 1.3 gcs messages sent 4,076,629 6.3 10.4 ges messages sent 846,697 1.3 2.2 global enqueue CPU used by this 13,281 0.0 0.0 global enqueue get time 417,332 0.6 1.1 global enqueue gets async 136,198 0.2 0.4 global enqueue gets sync 7,764,445 12.0 19.7 global enqueue releases 7,723,068 11.9 19.6 heap block compress 128,894 0.2 0.3 hot buffers moved to head of LRU 779,703 1.2 2.0 immediate (CR) block cleanout ap 239,366 0.4 0.6 immediate (CURRENT) block cleano 262,531 0.4 0.7 index fast full scans (full) 5,641,568 8.7 14.3 index fetch by key 399,001,276 615.7 1,014.3 index scans kdiixs1 728,741,766 1,124.6 1,852.6 leaf node 90-10 splits 1,152 0.0 0.0 leaf node splits 6,120 0.0 0.0 lob reads 32,206 0.1 0.1 lob writes 17,285 0.0 0.0 lob writes unaligned 17,285 0.0 0.0 logons cumulative 29,782 0.1 0.1 messages received 697,204 1.1 1.8 Instance Activity Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- messages sent 697,133 1.1 1.8 no buffer to keep pinned count 42 0.0 0.0 no work - consistent read gets 9,237,873,342 14,255.8 23,484.5 opened cursors cumulative 9,574,122 14.8 24.3 parse count (failures) 67,289 0.1 0.2 parse count (hard) 159,233 0.3 0.4 parse count (total) 9,611,634 14.8 24.4 parse time cpu 47,982 0.1 0.1 parse time elapsed 63,769 0.1 0.2 physical read IO requests 260,807 0.4 0.7 physical read bytes 5,646,393,344 8,713.4 14,354.2 physical read total IO requests 1,259,597 1.9 3.2 physical read total bytes 23,871,146,496 36,837.6 60,685.1 physical read total multi block 58,703 0.1 0.2 physical reads 685,094 1.1 1.7 physical reads cache 626,839 1.0 1.6 physical reads cache prefetch 388,253 0.6 1.0 physical reads direct 62,417 0.1 0.2 physical reads direct (lob) 1,427 0.0 0.0 physical reads direct temporary 59,396 0.1 0.2 physical reads prefetch warmup 7 0.0 0.0 physical write IO requests 525,936 0.8 1.3 physical write bytes 6,627,008,512 10,226.7 16,847.1 physical write total IO requests 1,020,892 1.6 2.6 physical write total bytes 13,883,728,384 21,425.2 35,295.1 physical write total multi block 437,282 0.7 1.1 physical writes 808,961 1.3 2.1 physical writes direct 76,043 0.1 0.2 physical writes direct (lob) 1,354 0.0 0.0 physical writes direct temporary 67,729 0.1 0.2 physical writes from cache 732,918 1.1 1.9 physical writes non checkpoint 529,334 0.8 1.4 pinned buffers inspected 4,333 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 24,625 0.0 0.1 process last non-idle time 655,515 1.0 1.7 queries parallelized 6,197 0.0 0.0 recovery blocks read 691 0.0 0.0 recursive calls 241,511,727 372.7 614.0 recursive cpu usage 3,804,317 5.9 9.7 redo blocks read for recovery 13,460 0.0 0.0 redo blocks written 3,640,947 5.6 9.3 redo buffer allocation retries 118 0.0 0.0 redo entries 5,753,883 8.9 14.6 redo log space requests 106 0.0 0.0 redo log space wait time 338 0.0 0.0 redo ordering marks 43,789 0.1 0.1 redo size 1,689,908,656 2,607.8 4,296.1 redo synch time 21,137 0.0 0.1 redo synch writes 2,845,164 4.4 7.2 redo wastage 112,421,520 173.5 285.8 redo write time 36,070 0.1 0.1 redo writer latching time 2 0.0 0.0 redo writes 384,852 0.6 1.0 rollback changes - undo records 152,103 0.2 0.4 rollbacks only - consistent read 138,000 0.2 0.4 rows fetched via callback 130,693,736 201.7 332.3 session connect time 165 0.0 0.0 session cursor cache hits 2,541,938 3.9 6.5 session logical reads 11,208,956,212 17,297.5 28,495.3 Instance Activity Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- session pga memory 44,637,674,080 68,884.2 113,477.6 session pga memory max 72,574,622,304 111,996.1 184,498.8 session uga memory ################## 30,602,468.9 50,413,533.1 session uga memory max 22,243,110,688 34,325.2 56,546.3 shared hash latch upgrades - no 677,633,066 1,045.7 1,722.7 shared hash latch upgrades - wai 2,544 0.0 0.0 sorts (memory) 1,966,143 3.0 5.0 sorts (rows) 103,060,301 159.0 262.0 sql area purged 341 0.0 0.0 summed dirty queue length 1,621,587 2.5 4.1 switch current to new buffer 649,178 1.0 1.7 table fetch by rowid 19,548,467,143 30,166.9 49,696.0 table fetch continued row 472,878 0.7 1.2 table scan blocks gotten 4,019,300,447 6,202.5 10,217.8 table scan rows gotten 244,625,573,192 377,502.6 621,885.7 table scans (long tables) 121 0.0 0.0 table scans (short tables) 7,858,501 12.1 20.0 total number of times SMON poste 818 0.0 0.0 transaction lock background gets 0 0.0 0.0 transaction lock foreground requ 0 0.0 0.0 transaction rollbacks 8,770 0.0 0.0 undo change vector size 601,656,336 928.5 1,529.5 user I/O wait time 60,529 0.1 0.2 user calls 14,717,898 22.7 37.4 user commits 254,769 0.4 0.7 user rollbacks 138,592 0.2 0.4 workarea executions - onepass 2 0.0 0.0 workarea executions - optimal 5,764,816 8.9 14.7 write clones created in foregrou 1,092 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: TDLDB/tdldb1 Snaps: 4376-4 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 74,555 233,462 opened cursors current 265 493 workarea memory allocated 0 3,248 logons current 46 44 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: TDLDB/tdldb1 Snaps: 4376-45 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 38 .21 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ COAARCHDATA 114,923 0 1.6 2.9 186,029 0 0 0.0 SYSAUX 49,164 0 3.2 2.0 187,790 0 62 240.2 COATRXNDATA 58,792 0 2.2 1.8 102,190 0 24,617 18.2 UNDOTBS1 1,496 0 2.6 1.0 29,757 0 791 1.2 SYSTEM 18,796 0 3.5 1.4 10,100 0 8 11.3 COAINTGRDATA 10,470 0 2.5 5.6 7,426 0 1 0.0 TEMP 2,493 0 5.3 27.2 2,282 0 0 0.0 UNDOTBS2 4,114 0 1.2 1.0 76 0 137 10.9 COARFRNDATA 427 0 4.3 2.0 272 0 0 0.0 COAARCHINDX 26 0 0.0 1.0 23 0 0 0.0 COAINTGRINDX 26 0 0.8 1.0 23 0 0 0.0 COARFRNINDX 26 0 0.8 1.0 23 0 0 0.0 COATRXNINDX 26 0 0.4 1.0 23 0 0 0.0 USERS 26 0 0.4 1.0 23 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ COAARCHDATA +ORADISK/tdldb/datafile/coaarchdata.274.689784731 114,923 0 1.6 2.9 186,029 0 0 0.0 COAARCHINDX +ORADISK/tdldb/datafile/coaarchindx.275.689784739 26 0 0.0 1.0 23 0 0 0.0 COAINTGRDATA +ORADISK/tdldb/datafile/coaintgrdata.272.689784719 10,470 0 2.5 5.6 7,426 0 1 0.0 COAINTGRINDX +ORADISK/tdldb/datafile/coaintgrindx.273.689784725 26 0 0.8 1.0 23 0 0 0.0 COARFRNDATA +ORADISK/tdldb/datafile/coarfrndata.270.689784705 427 0 4.3 2.0 272 0 0 0.0 COARFRNINDX +ORADISK/tdldb/datafile/coarfrnindx.271.689784711 26 0 0.8 1.0 23 0 0 0.0 COATRXNDATA +ORADISK/tdldb/datafile/coatrxndata.268.689784695 58,792 0 2.2 1.8 102,190 0 24,617 18.2 COATRXNINDX +ORADISK/tdldb/datafile/coatrxnindx.269.689784699 26 0 0.4 1.0 23 0 0 0.0 SYSAUX +ORADISK/tdldb/datafile/sysaux.257.689765341 49,164 0 3.2 2.0 187,790 0 62 240.2 SYSTEM +ORADISK/tdldb/datafile/system.256.689765341 18,796 0 3.5 1.4 10,100 0 8 11.3 TEMP +ORADISK/tdldb/tempfile/temp.263.689765413 2,493 0 5.3 27.2 2,282 0 0 N/A UNDOTBS1 +ORADISK/tdldb/datafile/undotbs1.258.689765341 1,496 0 2.6 1.0 29,757 0 791 1.2 UNDOTBS2 +ORADISK/tdldb/datafile/undotbs2.264.689765429 4,114 0 1.2 1.0 76 0 137 10.9 USERS +ORADISK/tdldb/datafile/users.259.689765341 26 0 0.4 1.0 23 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 169,353 100 -5,970,295,685 626,144 732,918 0 0 25,616 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 0 1032 5386 5263 92160 5263 N/A E 0 0 959 6826 6810 92160 6810 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 -> 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,400 ###### 7,488,845,370 D 256 .2 30,800 ###### 4,983,108,437 D 384 .3 46,200 ###### 3,416,450,648 D 512 .4 61,600 ###### 1,653,682,472 D 640 .5 77,000 530.8 352,399,196 D 768 .5 92,400 25.6 16,989,125 D 896 .6 107,800 1.8 1,190,243 D 1,024 .7 123,200 1.3 874,258 D 1,152 .8 138,600 1.1 750,105 D 1,280 .9 154,000 1.1 709,913 D 1,408 1.0 169,400 1.0 663,926 D 1,536 1.1 184,800 1.0 632,896 D 1,664 1.2 200,200 0.9 616,040 D 1,792 1.3 215,600 0.9 598,370 D 1,920 1.4 231,000 0.9 577,635 D 2,048 1.5 246,400 0.8 561,242 D 2,176 1.5 261,800 0.8 543,269 D 2,304 1.6 277,200 0.8 527,833 D 2,432 1.7 292,600 0.8 522,007 D 2,560 1.8 308,000 0.8 506,748 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 --------------- ------------------ -------------------------- 100.0 3,698,459 1,015 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 604 407 272.0 0.0 .0 .0 .0 102,400 E 604 377 382.2 0.4 .1 100.0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 2,169,298 2,169,298 0 0 64K 128K 18,313 18,313 0 0 128K 256K 34,139 34,139 0 0 256K 512K 17,388 17,388 0 0 512K 1024K 633,806 633,806 0 0 1M 2M 2,891,006 2,891,006 0 0 2M 4M 842 842 0 0 4M 8M 19 19 0 0 8M 16M 8 8 0 0 16M 32M 1 1 0 0 32M 64M 8 8 0 0 256M 512M 2 0 2 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 76 0.1 5,969,930.0 443,514.6 93.0 50,174 151 0.3 5,969,930.0 291,625.4 95.0 32,444 302 0.5 5,969,930.0 507.5 100.0 0 453 0.8 5,969,930.0 507.5 100.0 0 604 1.0 5,969,930.0 507.5 100.0 0 725 1.2 5,969,930.0 507.5 100.0 0 846 1.4 5,969,930.0 507.5 100.0 0 966 1.6 5,969,930.0 507.5 100.0 0 1,087 1.8 5,969,930.0 507.5 100.0 0 1,208 2.0 5,969,930.0 507.5 100.0 0 1,812 3.0 5,969,930.0 507.5 100.0 0 2,416 4.0 5,969,930.0 507.5 100.0 0 3,624 6.0 5,969,930.0 507.5 100.0 0 4,832 8.0 5,969,930.0 507.5 100.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 272 .7 58 5,155 ####### 1.0 1,562 1.1 ########### 320 .9 103 8,973 ####### 1.0 1,467 1.1 ########### 368 1.0 148 13,026 ####### 1.0 1,389 1.0 ########### 416 1.1 195 16,911 ####### 1.0 1,329 1.0 ########### 464 1.3 242 20,367 ####### 1.0 1,288 .9 ########### 512 1.4 288 24,950 ####### 1.0 1,263 .9 ########### 560 1.5 311 27,607 ####### 1.0 1,249 .9 ########### 608 1.7 357 32,651 ####### 1.0 1,242 .9 ########### 656 1.8 401 39,052 ####### 1.0 1,239 .9 ########### 704 1.9 424 43,843 ####### 1.0 1,237 .9 ########### 752 2.0 471 47,228 ####### 1.0 1,235 .9 ########### ------------------------------------------------------------- SGA Target Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 912 0.5 3,619,360 1,653,759,218 1,368 0.8 247,539 874,243 1,824 1.0 246,971 663,915 2,280 1.3 246,773 598,320 2,736 1.5 246,654 543,215 3,192 1.8 246,555 506,700 3,648 2.0 246,555 506,700 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 No data exists for this section of the report. ------------------------------------------------------------- Java Pool Advisory DB/Inst: TDLDB/tdldb1 Snap: 4556 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 24,683 464 19 undo block 662 1 2 undo header 264 1 4 segment header 5 0 2 file header block 2 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- WF-AWR Flush 3,782 3,773 9 1,719 27 15.98 TM-DML 1,865,808 1,860,119 5,689 24,461 16 .64 PS-PX Process Reservation 62,467 58,437 4,030 20,111 14 .68 TX-Transaction (row lock contention) 1 1 0 1 10 10,422.00 TT-Tablespace 23,598 23,598 0 8,131 6 .71 US-Undo Segment 44,078 44,078 0 30,702 4 .12 RO-Multiple Object Reuse (fast object reuse) 2,346 2,346 0 781 2 2.88 JQ-Job Queue 4,426 4,414 12 2,752 2 .59 TA-Instance Undo 2,188 2,188 0 1,590 1 .74 HW-Segment High Water Mark 15,683 15,683 0 1,427 1 .68 TD-KTF map table enqueue (KTF dump entries) 1,225 1,225 0 843 1 .81 PI-Remote PX Process Spawn Status 1,485 1,485 0 854 1 .68 TX-Transaction (index contention) 43 43 0 38 0 9.03 AF-Advisor Framework (task serialization) 1,549 1,549 0 325 0 1.02 FB-Format Block 1,110 1,110 0 487 0 .64 CF-Controlfile Transaction 981,221 981,220 0 150 0 1.67 UL-User-defined 1,281 1,281 0 831 0 .26 PR-Process Startup 7,318 7,318 0 3 0 46.67 TQ-Queue table enqueue (TM contention) 6,896 6,896 0 1,881 0 .07 DR-Distributed Recovery 360 360 0 302 0 .31 PE-Parameter 30,801 30,801 0 36 0 2.58 TO-Temp Object 148 148 0 63 0 1.25 JS-Job Scheduler (job run lock - synchronize) 379 369 10 249 0 .25 PG-Global Parameter 76 76 0 29 0 2.10 TS-Temporary Segment 371 371 0 56 0 .84 WL-Being Written Redo Log 41 38 3 19 0 2.47 DL-Direct Loader Index Creation 84 84 0 20 0 1.50 MW-MWIN Schedule 359 359 0 114 0 .13 MD-Materialized View Log DDL 58 58 0 29 0 .52 SK-Shrink Segment 9 9 0 9 0 1.67 Enqueue Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 (allocate ITL entry) 7 7 0 7 0 .00 FU-DBFUS 7 7 0 5 0 .00 JS-Job Scheduler (wdw op) 5 5 0 5 0 .00 SQ-Sequence Cache 4,076 4,076 0 2 0 .00 XR-Quiesce / Force Logging (database force logging) 63 63 0 1 0 .00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 85.2 392,333 2,020 5 15/47.65 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 11-Feb 17:25 65 365 0 3 15 0/0 0/0/0/0/0/0 11-Feb 17:15 44 384 0 2 15 0/0 0/0/0/0/0/0 11-Feb 17:05 54 313 0 2 15 0/0 0/0/0/0/0/0 11-Feb 16:55 38 351 0 2 15 0/0 0/0/0/0/0/0 11-Feb 16:45 39 298 0 2 15 0/0 0/0/0/0/0/0 11-Feb 16:35 99 525 0 3 15 0/0 0/0/0/0/0/0 11-Feb 16:25 47 296 0 3 15 0/0 0/0/0/0/0/0 11-Feb 16:15 51 411 0 3 15 0/0 0/0/0/0/0/0 11-Feb 16:05 42 334 361 3 20 0/0 0/0/0/0/0/0 11-Feb 15:55 51 304 0 2 15 0/0 0/0/0/0/0/0 11-Feb 15:45 45 530 103 3 16 0/0 0/0/0/0/0/0 11-Feb 15:35 85 306 0 3 15 0/0 0/0/0/0/0/0 11-Feb 15:25 74 389 0 3 15 0/0 0/0/0/0/0/0 11-Feb 15:15 39 283 0 3 15 0/0 0/0/0/0/0/0 11-Feb 15:05 129 477 0 3 15 0/0 0/0/0/0/0/0 11-Feb 14:55 61 339 56 2 16 0/0 0/0/0/0/0/0 11-Feb 14:45 50 532 0 3 15 0/0 0/0/0/0/0/0 11-Feb 14:35 112 381 108 3 17 0/0 0/0/0/0/0/0 11-Feb 14:25 43 365 8 3 15 0/0 0/0/0/0/0/0 11-Feb 14:15 35 286 0 2 15 0/0 0/0/0/0/0/0 11-Feb 14:05 40 316 0 2 15 0/0 0/0/0/0/0/0 11-Feb 13:55 50 305 0 2 17 0/0 0/0/0/0/0/0 11-Feb 13:45 38 478 0 2 15 0/0 0/0/0/0/0/0 11-Feb 13:35 126 447 0 3 15 0/0 0/0/0/0/0/0 11-Feb 13:25 50 295 48 2 16 0/0 0/0/0/0/0/0 11-Feb 13:15 42 349 120 3 17 0/0 0/0/0/0/0/0 11-Feb 13:05 151 536 115 3 15 0/0 0/0/0/0/0/0 11-Feb 12:55 61 555 0 3 15 0/0 0/0/0/0/0/0 11-Feb 12:45 39 362 0 2 15 0/0 0/0/0/0/0/0 11-Feb 12:35 98 541 0 3 15 0/0 0/0/0/0/0/0 11-Feb 12:25 52 362 53 2 15 0/0 0/0/0/0/0/0 11-Feb 12:15 35 355 0 2 15 0/0 0/0/0/0/0/0 11-Feb 12:05 222 501 0 3 15 0/0 0/0/0/0/0/0 11-Feb 11:55 60 565 0 2 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> "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 allocation 2,075 0.0 N/A 0 0 N/A ASM map headers 960 0.0 N/A 0 0 N/A ASM map load waiting lis 160 0.0 N/A 0 0 N/A ASM map operation freeli 11,512 0.0 N/A 0 0 N/A ASM map operation hash t 4,958,306 0.0 0.0 0 0 N/A ASM network background l 260,181 0.0 N/A 0 0 N/A AWR Alerted Metric Eleme 3,666,234 0.0 N/A 0 0 N/A Consistent RBA 384,891 0.0 N/A 0 0 N/A FAL request queue 13,040 0.0 N/A 0 0 N/A FAL subheap alocation 13,040 0.0 N/A 0 0 N/A FIB s.o chain latch 396 0.0 N/A 0 0 N/A FOB s.o list latch 30,991 0.0 N/A 0 0 N/A JS broadcast add buf lat 2,370,429 0.0 N/A 0 0 N/A JS broadcast drop buf la 2,370,528 0.0 N/A 0 0 N/A JS broadcast kill buf la 5 0.0 N/A 0 0 N/A JS broadcast load blnc l 2,351,507 0.0 N/A 0 0 N/A JS mem alloc latch 1,982 0.0 N/A 0 0 N/A JS queue access latch 1,982 0.0 N/A 0 0 N/A JS queue state obj latch 98,783,932 0.0 N/A 0 0 N/A JS slv state obj latch 36,544 0.0 N/A 0 0 N/A KCL gc element parent la 14,164,313 0.0 0.0 0 188,466 0.1 KFK SGA context latch 219,418 0.0 N/A 0 0 N/A KFMD SGA 4,948 0.0 N/A 0 0 N/A KJC destination ctx free 9 0.0 N/A 0 1 0.0 KJC message pool free li 830,309 0.1 0.0 0 81,570 0.0 KJCT flow control latch 6,672,891 0.0 0.0 0 0 N/A KMG MMAN ready and start 216,305 0.0 N/A 0 0 N/A KMG resize request state 84 0.0 N/A 0 0 N/A KSXR large replies 43,283 0.0 N/A 0 0 N/A KTF sga latch 2,411 0.0 N/A 0 169,803 0.0 KWQMN job cache list lat 89,676 0.0 0.0 0 0 N/A KWQMN to-be-Stopped Buff 1 0.0 N/A 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 12,740 0.0 Memory Management Latch 1,061 0.0 N/A 0 216,293 0.0 OS process 82,503 0.0 N/A 0 0 N/A OS process allocation 271,007 0.0 0.3 0 0 N/A OS process: request allo 25,728 0.0 N/A 0 0 N/A PL/SQL warning settings 2,246,718 0.0 N/A 0 0 N/A SGA IO buffer pool latch 32,438 0.0 N/A 0 39,492 0.0 SQL memory manager latch 180 0.0 N/A 0 216,005 0.0 SQL memory manager worka 34,953,811 0.0 0.0 0 0 N/A Shared B-Tree 24,097 0.0 N/A 0 0 N/A active checkpoint queue 574,601 0.1 0.0 0 0 N/A active service list 21,637,463 0.0 0.4 0 228,096 0.0 alert log latch 0 N/A N/A 0 1 0.0 archive control 20,379 0.0 N/A 0 0 N/A archive process latch 229,047 0.0 1.0 0 0 N/A begin backup scn array 2,371 0.0 N/A 0 0 N/A buffer pool 1,094 0.0 N/A 0 0 N/A business card 15,932 0.0 N/A 0 0 N/A cache buffer handles 102,546,504 0.0 0.0 0 0 N/A cache buffers chains ############## 0.0 0.0 0 10,477,869 0.0 cache buffers lru chain 4,920,620 0.2 0.0 0 11,000,044 0.1 cache table scan latch 0 N/A N/A 0 54,684 0.0 channel handle pool latc 27,332 0.0 0.0 0 0 N/A channel operations paren 54,582,561 0.0 0.0 0 0 N/A checkpoint queue latch 17,900,548 0.0 0.0 0 1,024,675 0.0 client/application info 39,725 0.0 N/A 0 0 N/A commit callback allocati 21,460 0.0 N/A 0 0 N/A compile environment latc 701,966 0.0 N/A 0 0 N/A Latch Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ database property servic 3 0.0 N/A 0 0 N/A dictionary lookup 418 0.0 N/A 0 0 N/A dml lock allocation 3,742,730 0.0 0.0 0 0 N/A dummy allocation 61,438 0.0 N/A 0 0 N/A enqueue hash chains 216,235,601 0.0 0.0 0 10,913 0.0 enqueues 205,951,655 0.0 0.0 0 0 N/A error message lists 35,891 0.0 N/A 0 0 N/A event group latch 12,863 0.0 N/A 0 0 N/A file cache latch 63,825 0.0 N/A 0 0 N/A gcs domain validate latc 1 0.0 N/A 0 0 N/A gcs drop object freelist 15,567 0.2 0.0 0 0 N/A gcs opaque info freelist 1,181,516 0.0 0.0 0 0 N/A gcs partitioned table ha 16,668,989 0.0 N/A 0 0 N/A gcs remaster request que 987 0.0 N/A 0 0 N/A gcs remastering latch 85,255 0.0 0.3 0 0 N/A gcs resource freelist 973,725 0.0 0.0 0 0 N/A gcs resource hash 10,852,324 0.0 0.0 0 8,070 0.0 gcs resource scan list 16,140 0.0 N/A 0 0 N/A gcs resource validate li 30,382 0.0 N/A 0 0 N/A gcs shadows freelist 661,721 0.0 N/A 0 0 N/A generalized trace enabli 1 0.0 N/A 0 0 N/A ges caches resource list 5,891,456 0.0 0.0 0 7,202,479 0.0 ges deadlock list 197,466 0.0 N/A 0 213 0.0 ges domain table 15,378,498 0.0 0.0 0 0 N/A ges enqueue table freeli 15,826,384 0.0 0.0 0 0 N/A ges group table 22,864,528 0.0 0.0 0 0 N/A ges process hash list 506,751 0.0 N/A 0 2,080 0.0 ges process parent latch 32,002,425 0.0 0.0 0 0 N/A ges process table freeli 25,730 0.0 N/A 0 0 N/A ges resource hash list 23,603,585 0.0 0.3 0 742,849 0.0 ges resource scan list 11,408 0.0 N/A 0 0 N/A ges resource table freel 1,015,796 0.0 0.0 0 1 0.0 ges synchronous data 74 0.0 N/A 0 118,262 0.0 ges timeout list 204,951 0.0 N/A 0 218,359 0.0 global KZLD latch for me 5,545 0.0 N/A 0 0 N/A hash table column usage 62,734 0.0 N/A 0 15,971,401 0.0 hash table modification 14,850 0.0 N/A 0 0 N/A internal temp table obje 9 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 9,604 0.0 job_queue_processes free 0 N/A N/A 0 1 0.0 job_queue_processes para 15,591 0.0 N/A 0 0 N/A kks stats 231,044 0.0 1.0 0 0 N/A kmcptab latch 42 0.0 N/A 0 0 N/A kmcpvec latch 0 N/A N/A 0 42 0.0 ksuosstats global area 43,563 0.0 N/A 0 0 N/A ksv instance 1 0.0 N/A 0 0 N/A ktm global data 3,280 0.0 N/A 0 0 N/A kwqbsn:qsga 24,097 0.0 N/A 0 0 N/A lgwr LWN SCN 490,759 0.0 0.0 0 0 N/A library cache 483,871,265 0.0 0.0 0 340,937 0.0 library cache load lock 222,856 0.0 0.0 0 0 N/A library cache lock 48,590,238 0.0 0.0 0 0 N/A library cache lock alloc 376,950 0.0 N/A 0 0 N/A library cache pin 447,605,361 0.0 0.0 0 17 0.0 library cache pin alloca 321,201 0.0 N/A 0 0 N/A list of block allocation 43,504 0.0 N/A 0 0 N/A loader state object free 7,554 0.0 N/A 0 0 N/A longop free list parent 351 0.0 N/A 0 519 0.0 message pool operations 197,693 0.0 N/A 0 0 N/A messages 21,461,633 0.2 0.0 0 0 N/A Latch Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ mostly latch-free SCN 492,243 0.1 0.0 0 0 N/A msg queue 320 0.0 N/A 0 320 0.0 multiblock read objects 858,904 0.0 0.0 0 1 0.0 name-service memory obje 1,697,040 0.0 0.0 0 1 0.0 name-service namespace b 7,120,811 0.0 N/A 0 0 N/A name-service namespace o 84 0.0 N/A 0 0 N/A name-service pending que 772,622 0.0 N/A 0 0 N/A name-service request 5,310 0.0 0.0 0 0 N/A name-service request que 7,064,379 0.0 0.9 0 0 N/A ncodef allocation latch 10,864 0.0 N/A 0 0 N/A object queue header heap 9,917 0.0 N/A 0 9,663 0.0 object queue header oper 10,479,716 0.0 0.0 0 0 N/A object stats modificatio 1,511 0.0 N/A 0 3 0.0 parallel query alloc buf 271,564 0.0 N/A 0 0 N/A parallel query stats 35,132 0.0 N/A 0 0 N/A parallel txn reco latch 43,481 0.0 N/A 0 0 N/A parameter list 9,950 0.0 N/A 0 0 N/A parameter table allocati 32,241 0.0 N/A 0 0 N/A post/wait queue 213,072 0.1 0.0 0 146,666 0.0 process allocation 5,920,803 0.0 0.0 0 12,863 0.0 process group creation 25,728 0.0 N/A 0 0 N/A process queue 99,127 0.0 N/A 0 0 N/A process queue reference 1,722,215 0.0 0.0 0 180,805 0.0 qmn state object latch 1,222 0.0 N/A 0 0 N/A qmn task queue latch 97,037 0.1 0.0 0 0 N/A query server freelists 81,372 0.0 N/A 0 0 N/A query server process 885 0.1 1.0 0 811 9.1 queued dump request 3,189,998 0.0 N/A 0 0 N/A redo allocation 6,629,883 0.0 0.0 0 0 N/A redo copy 0 N/A N/A 0 5,753,975 0.0 redo writing 2,161,172 0.0 0.0 0 0 N/A reg$ timeout service tim 1 0.0 N/A 0 0 N/A row cache objects 109,549,817 0.0 0.0 0 25,233 0.0 rules engine aggregate s 2,973 0.0 N/A 0 0 N/A rules engine rule set st 5,952 0.0 N/A 0 0 N/A rules engine rule statis 6 0.0 N/A 0 0 N/A segmented array pool 320 0.0 N/A 0 0 N/A sequence cache 135,912 0.0 N/A 0 0 N/A session allocation 15,876,860 0.0 0.0 0 0 N/A session idle bit 30,108,905 0.0 0.0 0 0 N/A session state list latch 56,045 0.0 N/A 0 0 N/A session switching 10,864 0.0 N/A 0 0 N/A session timer 228,129 0.0 N/A 0 0 N/A shared pool 11,462,118 0.0 0.3 0 0 N/A simulator hash latch 772,824,482 0.0 0.0 0 0 N/A simulator lru latch 772,617,845 0.1 0.0 0 155,675 0.0 slave class 1,955 0.0 N/A 0 1 0.0 slave class create 4,724 3.8 1.0 1 0 N/A sort extent pool 21,459 0.0 N/A 0 0 N/A state object free list 362 0.0 N/A 0 0 N/A statistics aggregation 30,243 0.0 N/A 0 0 N/A temp lob duration state 40 0.0 N/A 0 0 N/A temporary table state ob 8 0.0 N/A 0 0 N/A threshold alerts latch 75,837 0.0 N/A 0 0 N/A trace latch 2 0.0 N/A 0 0 N/A transaction allocation 5,408,539 0.0 N/A 0 0 N/A transaction branch alloc 10,864 0.0 N/A 0 0 N/A undo global data 3,072,882 0.0 0.0 0 0 N/A user lock 31,708 0.0 N/A 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- cache buffers chains ############## 1,172,665 9,597 1,163,292 0 0 0 simulator lru latch 772,617,845 634,152 319 633,836 0 0 0 messages 21,461,633 39,577 34 39,543 0 0 0 library cache 483,871,265 36,566 852 35,723 0 0 0 library cache pin 447,605,361 10,510 8 10,502 0 0 0 cache buffers lru chain 4,920,620 10,045 170 9,878 0 0 0 row cache objects 109,549,817 9,591 8 9,583 0 0 0 channel operations parent latch 54,582,561 8,081 3 8,078 0 0 0 KCL gc element parent latch 14,164,313 2,897 31 2,869 0 0 0 ges resource hash list 23,603,585 2,625 684 1,943 0 0 0 session allocation 15,876,860 2,621 2 2,619 0 0 0 gcs resource hash 10,852,324 1,329 4 1,326 0 0 0 simulator hash latch 772,824,482 1,234 1 1,233 0 0 0 KJCT flow control latch 6,672,891 1,161 1 1,160 0 0 0 shared pool 11,462,118 865 249 629 0 0 0 KJC message pool free list 830,309 844 2 842 0 0 0 ges caches resource lists 5,891,456 652 2 650 0 0 0 object queue header operation 10,479,716 635 26 611 0 0 0 enqueue hash chains 216,235,601 606 2 604 0 0 0 ges enqueue table freelist 15,826,384 393 1 392 0 0 0 mostly latch-free SCN 492,243 342 1 341 0 0 0 checkpoint queue latch 17,900,548 225 5 220 0 0 0 dml lock allocation 3,742,730 179 1 178 0 0 0 slave class create 4,724 178 178 0 0 0 0 name-service request queue 7,064,379 66 61 5 0 0 0 qmn task queue latch 97,037 60 2 58 0 0 0 ges resource table freelist 1,015,796 45 1 44 0 0 0 active service list 21,637,463 31 13 18 0 0 0 OS process allocation 271,007 3 1 2 0 0 0 gcs remastering latch 85,255 3 1 2 0 0 0 Latch Sleep Breakdown DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- archive process latch 229,047 1 1 0 0 0 0 kks stats 231,044 1 1 0 0 0 0 query server process 885 1 1 0 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- KCL gc element parent la kclbla 0 24 1 KCL gc element parent la kclgcr 0 13 1 KCL gc element parent la kclnfndnewm 0 10 18 KCL gc element parent la kclfwrite1 0 8 5 KCL gc element parent la kclplz 0 5 9 KCL gc element parent la kclrwrite 0 5 10 KCL gc element parent la kclzcl 0 5 2 KCL gc element parent la kclebs 0 3 2 KCL gc element parent la kclevict 0 3 0 KCL gc element parent la kclulb 0 3 23 KCL gc element parent la kclnfnd 0 2 1 KCL gc element parent la kclpto 0 2 0 KCL gc element parent la kcldrop 0 1 0 KCL gc element parent la kclfwrite2 0 1 0 KCL gc element parent la kclhngcbk1 0 1 0 KCL gc element parent la kcllwr 0 1 3 KCL gc element parent la kclnfndnew 0 1 0 KCL gc element parent la kclpdc_3 0 1 1 KCL gc element parent la kclpto_1 0 1 0 KCL gc element parent la kclswc 0 1 2 KJC message pool free li kjcspfmbq: free vector of 0 2 0 KJCT flow control latch kjcts_sedeqv: dequeue a ve 0 1 0 KWQMN job cache list lat kwqmndej: dequeue 0 3 0 OS process allocation kso_new_process:1 0 1 0 active service list kswssyis: find service ent 0 20 0 archive process latch kcrrsarc 0 1 0 cache buffers chains kcbchg: kslbegin: bufs not 0 11,691 7,450 cache buffers chains kcbgtcr: kslbegin excl 0 3,848 3,733 cache buffers chains kclebs_1 0 1,751 1,715 cache buffers chains kcbgtcr: fast path 0 1,031 169 cache buffers chains kcbrls: kslbegin 0 617 2,190 cache buffers chains kcbzwb 0 357 828 cache buffers chains kcbgcur: kslbegin 0 313 86 cache buffers chains kcbgtcr: kslbegin shared 0 69 56 cache buffers chains kclpred 0 54 0 cache buffers chains kclbla 0 51 0 cache buffers chains kcbget: pin buffer 0 30 7 cache buffers chains kclwrt 0 26 4 cache buffers chains kcbzib: multi-block read: 0 18 0 cache buffers chains kcbzgb: scan from tail. no 0 17 0 cache buffers chains kclwcrs 0 14 1 cache buffers chains kcbgcur: buf pinned 0 13 10 cache buffers chains kcbbxsv 0 8 30 cache buffers chains kcbchg: kslbegin: call CR 0 7 3,419 cache buffers chains kcbgtcr 0 6 0 cache buffers chains kclpto 0 6 0 cache buffers chains kcbbic1 0 5 2 cache buffers chains kcbzib: finish free bufs 0 4 8 cache buffers chains kclpdc_1 0 4 0 cache buffers chains kcbnew: new latch again 0 3 0 cache buffers chains kcbgkcbcr 0 1 1 cache buffers chains kcbzib: exchange rls 0 1 0 cache buffers chains kclcls_3 0 1 2 cache buffers chains kclfbst_1 0 1 1 cache buffers chains kclgrantlk_1 0 1 1 cache buffers chains kclpdcl 0 1 0 cache buffers lru chain kcbzgws 0 140 0 cache buffers lru chain kcbbwlru 0 18 1 cache buffers lru chain kcbzgb: posted for free bu 0 4 112 cache buffers lru chain kcbzgm 0 3 0 Latch Miss Sources DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers lru chain kcbbxsv: move to being wri 0 1 16 cache buffers lru chain kcbkubc 0 1 14 cache buffers lru chain kcbo_link_q 0 1 0 cache buffers lru chain kcbzswcu 0 1 19 cache buffers lru chain kclmvreqbg 0 1 5 channel operations paren ksrwait() 0 3 5 channel operations paren ksrmfree() 0 1 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 5 0 dml lock allocation ktaidm 0 1 0 enqueue hash chains ksqcmi: if lk mode not req 0 1 0 enqueue hash chains ksqrcl 0 1 0 event range base latch No latch 0 2 2 gcs remastering latch 43 0 1 0 gcs remastering latch 59 0 1 0 gcs resource hash kjbcrcomplete 0 1 0 gcs resource hash kjbcrconvert 0 1 0 gcs resource hash kjbcropen 0 1 0 gcs resource hash kjbcropen:affinity 0 1 0 ges enqueue table freeli kjlfr: remove lock from pa 0 1 0 ges resource hash list kjrmas1: lookup master nod 0 539 0 ges resource hash list kjucvl: lock convert reque 0 127 458 ges resource hash list kjakcai: search for resp b 0 9 1 ges resource hash list kjlmfnd: search for lockp 0 8 126 ges resource hash list kjcvscn: remove from scan 0 1 0 ges resource table freel kjruch: cached obj cleanup 0 2 0 ges resource table freel kjralc: resource allocatio 0 1 0 kks stats kks stats alloc/free 0 1 1 lgwr LWN SCN kcs023 0 1 0 library cache kglpndl: child: after proc 0 33 17 library cache kglpndl: child: before pro 0 19 250 library cache kglpin: child: heap proces 0 15 18 library cache kglobpn: child: 0 14 47 library cache kglhdgc: child: 0 6 24 library cache kglpnc: child 0 5 641 library cache kglhdiv: child 0 4 3 library cache kglic 0 4 0 library cache kqlmbinv: parent 0 3 0 library cache kgldti: 2child 0 2 4 library cache kglrtl 0 2 2 library cache kqlmbpil: parent 0 2 0 library cache kgldtld: 2child 0 1 5 library cache kglivl: child 0 1 0 library cache kglpin 0 1 4 library cache kglpnp: child 0 1 20 library cache lock kgllkdl: child: no lock ha 0 279 25 library cache pin kglpndl 0 6 5 library cache pin kglpnal: child: alloc spac 0 1 1 library cache pin kglpnc: child 0 1 2 messages ksarcv: after wait 0 22 25 messages ksarcv 0 11 9 messages ksaamb: after wakeup 0 1 0 name-service request que kjxgnapq: process a reques 0 61 0 object queue header oper kclobj_3 0 13 23 object queue header oper kclobj_2 0 8 0 object queue header oper kclobj_1 0 4 0 object queue header oper kcbo_switch_cq 0 1 0 query server process kxfpcrer 0 1 0 row cache objects kqreqd: reget 0 4 0 row cache objects kqreqd 0 2 1 row cache objects kqrbgl 0 1 5 Latch Miss Sources DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- session allocation ksuxds: KSUSFCLC not set 0 2 0 shared pool kghfrunp: clatch: nowait 0 423 0 shared pool kghalo 0 183 56 shared pool kghfrunp: alloc: cursor du 0 51 0 shared pool kghfrunp: clatch: wait 0 38 3 shared pool kghupr1 0 7 128 shared pool kghalp 0 6 43 shared pool kghfre 0 1 21 shared pool kghfrunp: alloc: session d 0 1 0 simulator hash latch kcbsacc: lookup dba 0 1 1 simulator lru latch kcbs_simulate: simulate se 0 315 319 simulator lru latch kcbs_shrink_pool 0 2 0 simulator lru latch kcbs_free_granule_sim_buff 0 1 0 simulator lru latch kcbs_resize_pool : interpo 0 1 0 slave class create ksvcreate 0 178 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Logical Reads: 11,208,956,212 -> Captured Segments account for 99.6% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- COATRXN COATRXNDAT CCETTRAINMVMT TABLE ############ 45.50 COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE ############ 21.30 COATRXN COATRXNDAT PK_CCETTRAINPIPELINE INDEX ############ 15.90 COATRXN COATRXNDAT CCETTRAINORDR TABLE 511,004,080 4.56 COATRXN COATRXNDAT PK_CCETTRAINMVMT INDEX 398,839,408 3.56 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Physical Reads: 685,094 -> Captured Segments account for 68.6% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- COAARCH COAARCHDAT CCEATRAINPIPELINE TABLE 158,898 23.19 COAARCH COAARCHDAT CCEATRAINMVMT TABLE 83,916 12.25 COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE 57,764 8.43 COAINTGR COAINTGRDA P_CCEIPTRAINMVMT TABLE 42,039 6.14 COATRXN COATRXNDAT CCETTRAINMVMT TABLE 34,581 5.05 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- SYSMAN SYSAUX GEN_SNAP_PK INDEX 83 22.49 SYSMAN SYSAUX ESM_COLLECTION_PK INDEX 32 8.67 SYSMAN SYSAUX DI_PK INDEX 27 7.32 SYSMAN SYSAUX MGMT_HA_RAC_INTR_CON INDEX 21 5.69 SYSMAN SYSAUX MGMT_DB_CONTROLFILES INDEX 18 4.88 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- COAINTGR COAINTGRDA IDX1_P_CCEIPTRAINMVM INDEX 2 28.57 SYS SYSAUX WRH$_ACTIVE_SESSION_ 38270_4402 INDEX 1 14.29 SYS SYSAUX WRH$_ACTIVE_SESSION_ 38270_4450 INDEX 1 14.29 SYS SYSAUX WRH$_ACTIVE_SESSION_ 38270_4491 INDEX 1 14.29 SYS SYSAUX WRH$_ACTIVE_SESSION_ 38270_4515 INDEX 1 14.29 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE 676 59.40 COATRXN COATRXNDAT CCETTRAINMVMT TABLE 311 27.33 COATRXN COATRXNDAT PK_CCETLOCOMASTER INDEX 107 9.40 COATRXN COATRXNDAT CCETLOCOMAST TABLE 30 2.64 COATRXN COATRXNDAT CCETTRAINORDR TABLE 6 .53 ------------------------------------------------------------- Segments by Global Cache Buffer Busy DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> % of Capture shows % of GC Buffer Busy for each top segment compared -> with GC Buffer Busy for all segments captured by the Snapshot GC Tablespace Subobject Obj. Buffer % of Owner Name Object Name Name Type Busy Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE 9,081 62.92 COATRXN COATRXNDAT CCETTRAINMVMT TABLE 4,697 32.55 COATRXN COATRXNDAT PK_CCETTRAINMVMT INDEX 434 3.01 COATRXN COATRXNDAT PK_CCETTRAINORDR INDEX 59 .41 SYS SYSAUX SYS_IOT_TOP_8802 INDEX 55 .38 ------------------------------------------------------------- Segments by CR Blocks Received DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total CR Blocks Received: 1,153,693 -> Captured Segments account for 95.8% of Total CR Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE 255,516 22.15 COATRXN COATRXNDAT CCETTRAINMVMT TABLE 215,335 18.66 COATRXN COATRXNDAT PK_CCETTRAINMVMT INDEX 111,722 9.68 COATRXN COATRXNDAT CCETBLCKSCTNLINEOCC TABLE 71,002 6.15 COATRXN COATRXNDAT CCETLINEOCC TABLE 43,098 3.74 ------------------------------------------------------------- Segments by Current Blocks Received DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Total Current Blocks Received: 512,539 -> Captured Segments account for 92.2% of Total Current Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- COATRXN COATRXNDAT CCETTRAINPIPELINE TABLE 70,157 13.69 COATRXN COATRXNDAT CCETTRAINMVMT TABLE 60,054 11.72 COATRXN COATRXNDAT PK_CCETTRAINMVMT INDEX 35,655 6.96 COAINTGR COAINTGRDA P_CCEIPTRAINMVMT TABLE 27,235 5.31 COAINTGR COAINTGRDA IDX2_P_CCEIPTRAINMVM INDEX 26,901 5.25 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> "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 11,664 1.6 0 N/A 190 1 dc_constraints 996 33.5 0 N/A 996 0 dc_files 598 0.0 0 N/A 0 13 dc_global_oids 1,001,709 0.0 0 N/A 0 152 dc_histogram_data 2,591,721 0.6 0 N/A 9,456 6,961 dc_histogram_defs 1,044,034 2.5 0 N/A 6,160 6,648 dc_object_grants 12,951 11.8 0 N/A 0 472 dc_object_ids 2,771,226 0.1 0 N/A 249 1,916 dc_objects 1,390,390 0.7 0 N/A 1,521 1,579 dc_profiles 14,969 0.0 0 N/A 0 2 dc_rollback_segments 160,210 0.0 0 N/A 10 23 dc_segments 273,328 2.7 0 N/A 1,876 1,560 dc_sequences 5,324 42.2 0 N/A 5,324 7 dc_table_scns 58 100.0 0 N/A 0 0 dc_tablespace_quotas 102 10.8 0 N/A 0 2 dc_tablespaces 13,161,306 0.0 0 N/A 0 15 dc_usernames 80,604 0.0 0 N/A 0 20 dc_users 15,409,022 0.0 0 N/A 0 59 global database name 9 33.3 0 N/A 0 1 outstanding_alerts 34,791 83.3 0 N/A 2,973 26 ------------------------------------------------------------- Dictionary Cache Stats (RAC) DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 GES GES GES Cache Requests Conflicts Releases ------------------------- ------------ ------------ ------------ dc_awr_control 562 182 0 dc_constraints 1,660 1 1 dc_global_oids 65 0 87 dc_histogram_defs 38,738 6,937 2,881 dc_object_ids 2,321 119 890 dc_objects 12,787 502 7,537 dc_rollback_segments 31 10 0 dc_segments 10,516 722 6,418 dc_sequences 10,643 2,212 34 dc_table_scns 58 0 0 dc_tablespace_quotas 204 4 5 dc_tablespaces 1 0 0 dc_usernames 9 0 6 dc_users 62 0 59 global database name 3 0 3 outstanding_alerts 68,106 27,504 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 88,307 0.1 3,419,513 0.1 2,083 0 CLUSTER 3,613 0.1 6,972 3.5 239 0 INDEX 3,401 16.1 9,989 18.2 864 0 SQL AREA 117,681 11.1 210,047,542 0.1 130,015 2,203 TABLE/PROCEDURE 234,280 1.6 4,973,160 2.1 48,796 0 TRIGGER 8,006 1.1 192,892 0.5 822 0 ------------------------------------------------------------- Library Cache Activity (RAC) DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 GES Lock GES Pin GES Pin GES Inval GES Invali- Namespace Requests Requests Releases Requests dations --------------- ------------ ------------ ------------ ----------- ----------- BODY 1 125 113 122 0 CLUSTER 6,972 36 17 18 0 INDEX 9,947 2,030 691 857 0 TABLE/PROCEDURE 841,812 9,481 3,576 7,416 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 254.3 N/A 5.1 6.0 22 22 50 50 Freeable 15.6 .0 .7 .4 1 N/A 22 22 SQL 1.3 .6 .0 .1 0 11 29 23 PL/SQL .9 .3 .0 .0 0 0 48 46 E Other 355.1 N/A 7.4 11.2 61 62 48 48 Freeable 21.9 .0 1.0 .6 3 N/A 22 22 SQL 5.6 4.7 .2 .6 3 7 27 22 PL/SQL 1.4 .3 .0 .1 0 0 46 44 ------------------------------------------------------------- SGA Memory Summary DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 1,459,617,792 1,476,395,008 Fixed Size 2,103,496 Redo Buffers 2,162,688 Variable Size 448,718,648 431,941,432 ------------------- sum 1,912,602,624 ------------------------------------------------------------- SGA breakdown difference DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> 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 32.0 16.0 -50.00 large ASM map operations hashta .4 .4 0.00 large free memory 15.6 15.6 0.00 shared ASH buffers 15.5 15.5 0.00 shared CCursor 7.2 6.9 -3.98 shared Heap0: KGL 3.4 3.5 5.12 shared KCB Table Scan Buffer 4.0 4.0 0.00 shared KGH: NO ACCESS N/A 31.7 N/A shared KGLS heap 3.4 N/A -100.00 shared KQR L PO 9.9 11.5 15.85 shared KQR M SO 4.2 N/A -100.00 shared KSFD SGA I/O b 4.0 4.0 0.00 shared PCursor 5.3 5.4 2.60 shared PL/SQL MPCODE 4.1 4.0 -3.35 shared XDB Schema Cac 7.1 7.1 0.00 shared free memory 94.6 71.9 -23.97 shared gcs resources 28.2 28.2 0.00 shared gcs shadows 17.8 17.8 0.00 shared ges big msg buffers 8.3 8.3 0.00 shared ges resource 4.5 5.6 24.47 shared kglsim hash table bkts 4.0 4.0 0.00 shared kglsim heap 5.4 9.0 67.98 shared kglsim object batch N/A 5.4 N/A shared library cache 17.0 18.8 10.53 shared row cache 7.1 7.1 0.00 shared sql area 39.4 35.1 -10.77 buffer_cache 1,392.0 1,408.0 1.15 fixed_sga 2.0 2.0 0.00 log_buffer 2.1 2.1 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Streams Capture DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Buffered Subscribers DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 No data exists for this section of the report. ------------------------------------------------------------- Resource Limit Stats DB/Inst: TDLDB/tdldb1 Snap: 4556 No data exists for this section of the report. ------------------------------------------------------------- init.ora Parameters DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\TD background_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\TD cluster_database TRUE cluster_database_instances 2 compatible 10.2.0.1.0 control_files +ORADISK/tdldb/controlfile/curren core_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\TD db_block_size 8192 db_create_file_dest +ORADISK db_domain db_file_multiblock_read_count 16 db_name TDLDB dispatchers (PROTOCOL=TCP) (SERVICE=TDLDBXDB) instance_number 1 job_queue_processes 10 log_archive_dest_1 LOCATION=F:\ARCHIVE log_archive_format ARCH_%t_%s_%r.arc open_cursors 300 pga_aggregate_target 633339904 processes 150 remote_listener LISTENERS_TDLDB remote_login_passwordfile EXCLUSIVE service_names TDLDB, TDLDB_SVC sga_target 1912602624 spfile +ORADISK/tdldb/spfiletdldb.ora thread 1 undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest G:\ORACLE\PRODUCT\10.2.0\ADMIN\TD ------------------------------------------------------------- Global Enqueue Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ acks for commit broadcast(actual) 369,123 0.6 0.9 acks for commit broadcast(logical 378,553 0.6 1.0 broadcast msgs on commit(actual) 384,225 0.6 1.0 broadcast msgs on commit(logical) 384,316 0.6 1.0 broadcast msgs on commit(wasted) 17,078 0.0 0.0 dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 false posts waiting for scn acks 1 0.0 0.0 flow control messages received 23 0.0 0.0 flow control messages sent 46 0.0 0.0 gcs assume cvt 0 0.0 0.0 gcs assume no cvt 256,075 0.4 0.7 gcs ast xid 33 0.0 0.0 gcs blocked converts 416,158 0.6 1.1 gcs blocked cr converts 1,041,011 1.6 2.6 gcs compatible basts 722 0.0 0.0 gcs compatible cr basts (global) 175,559 0.3 0.4 gcs compatible cr basts (local) 101,244 0.2 0.3 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs dbwr flush pi msgs 81,275 0.1 0.2 gcs dbwr write request msgs 51,602 0.1 0.1 gcs error msgs 1 0.0 0.0 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 137,873 0.2 0.4 gcs immediate (null) converts 45,671 0.1 0.1 gcs immediate cr (compatible) con 22,763 0.0 0.1 gcs immediate cr (null) converts 414,528 0.6 1.1 gcs indirect ast 184,928 0.3 0.5 gcs lms flush pi msgs 0 0.0 0.0 gcs lms write request msgs 29,363 0.0 0.1 gcs msgs process time(ms) 345,686 0.5 0.9 gcs msgs received 3,667,905 5.7 9.3 gcs out-of-order msgs 5 0.0 0.0 gcs pings refused 2,082 0.0 0.0 gcs queued converts 164 0.0 0.0 gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 22 0.0 0.0 gcs regular cr 1 0.0 0.0 gcs retry convert request 8,080 0.0 0.0 gcs side channel msgs actual 81,001 0.1 0.2 gcs side channel msgs logical 1,970,118 3.0 5.0 gcs undo cr 3,033 0.0 0.0 gcs write notification msgs 6,232 0.0 0.0 gcs writes refused 191 0.0 0.0 ges msgs process time(ms) 10,077 0.0 0.0 ges msgs received 797,361 1.2 2.0 global posts dropped 0 0.0 0.0 global posts queue time 3,883,379 6.0 9.9 global posts queued 2,965 0.0 0.0 global posts requested 2,966 0.0 0.0 global posts sent 2,967 0.0 0.0 implicit batch messages received 68,518 0.1 0.2 implicit batch messages sent 89,873 0.1 0.2 lmd msg send time(ms) 0 0.0 0.0 lms(s) msg send time(ms) 0 0.0 0.0 messages flow controlled 541,801 0.8 1.4 messages queue sent actual 1,344,538 2.1 3.4 messages queue sent logical 1,724,848 2.7 4.4 messages received actual 3,642,429 5.6 9.3 Global Enqueue Statistics DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ messages received logical 4,465,261 6.9 11.4 messages sent directly 2,111,305 3.3 5.4 messages sent indirectly 1,075,921 1.7 2.7 messages sent not implicit batche 1,255,746 1.9 3.2 messages sent pbatched 1,618,315 2.5 4.1 msgs causing lmd to send msgs 260,241 0.4 0.7 msgs causing lms(s) to send msgs 645,096 1.0 1.6 msgs received queue time (ms) 12,331,521 19.0 31.3 msgs received queued 4,465,472 6.9 11.4 msgs sent queue time (ms) 258,842 0.4 0.7 msgs sent queue time on ksxp (ms) 570,022 0.9 1.4 msgs sent queued 1,706,314 2.6 4.3 msgs sent queued on ksxp 3,942,481 6.1 10.0 process batch messages received 1,029,986 1.6 2.6 process batch messages sent 998,385 1.5 2.5 ------------------------------------------------------------- Global CR Served Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 Statistic Total ------------------------------ ------------------ CR Block Requests 1,025,287 CURRENT Block Requests 17,313 Data Block Requests 1,025,287 Undo Block Requests 2,941 TX Block Requests 14,372 Current Results 1,020,378 Private results 894 Zero Results 21,229 Disk Read Results 87 Fail Results 1 Fairness Down Converts 203,769 Fairness Clears 0 Free GC Elements 0 Flushes 21,197 Flushes Queued 0 Flush Queue Full 0 Flush Max Time (us) 0 Light Works 12,088 Errors 0 ------------------------------------------------------------- Global CURRENT Served Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Pins = CURRENT Block Pin Operations -> Flushes = Redo Flush before CURRENT Block Served Operations -> Writes = CURRENT Block Fusion Write Operations Statistic Total % <1ms % <10ms % <100ms % <1s % <10s ---------- ------------ -------- -------- -------- -------- -------- Pins 525,490 99.99 0.00 0.01 0.00 0.00 Flushes 1,459 96.71 1.99 1.30 0.00 0.00 Writes 96,898 37.19 51.53 11.19 0.09 0.00 ------------------------------------------------------------- Global Cache Transfer Stats DB/Inst: TDLDB/tdldb1 Snaps: 4376-4556 -> Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays -> Busy (Busy) - Block Transfer impacted by Remote Contention -> Congested (Congst) - Block Transfer impacted by Remote System Load -> ordered by CR + Current Blocks Received desc CR Current ----------------------------- ----------------------------- Inst Block Blocks % % % Blocks % % % No Class Received Immed Busy Congst Received Immed Busy Congst ---- ----------- -------- ------ ------ ------ -------- ------ ------ ------ 2 data block ######## 92.5 4.2 3.3 500,701 95.2 .5 4.3 2 Others 12,179 99.9 .1 .0 10,540 99.7 .3 .0 2 undo block 5,725 98.7 1.3 .1 0 N/A N/A N/A 2 undo header 2,218 96.4 3.6 .0 871 99.1 .9 .0 ------------------------------------------------------------- End of Report