WORKLOAD REPOSITORY report for DB Name DB Id Unique Name DB Role Edition Release RAC CDB ------------ ----------- ----------- ---------------- ------- ---------- --- --- RFDWSY20 3943921809 RFDWSY20 PRIMARY EE 18.0.0.0.0 NO NO Instance Inst Num Startup Time ------------ -------- --------------- RFDWSY20 1 08-Jun-21 15:22 Host Name Platform CPUs Cores Sockets Memory(GB) ---------------- -------------------------------- ---- ----- ------- ---------- sy2siddifdwdbdw Linux x86 64-bit 8 8 4 137.69 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 17092 01-Oct-21 11:00:55 73 2.0 End Snap: 17094 01-Oct-21 13:00:03 68 1.9 Elapsed: 119.13 (mins) DB Time: 42.11 (mins) Top ADDM Findings by Average Active Sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Finding Name Avg act Percen Task Name ---------------------------------------- ------- ------ ------------------------ Top SQL Statements .35 85.37 ADDM:3943921809_1_17093 Top SQL Statements .36 75.38 ADDM:3943921809_1_17094 Top Segments by "User I/O" and "Cluster" .36 16.67 ADDM:3943921809_1_17094 Top Segments by "User I/O" and "Cluster" .35 14.06 ADDM:3943921809_1_17093 Undersized PGA .36 11.88 ADDM:3943921809_1_17094 Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 0.4 1.9 0.02 0.02 DB CPU(s): 0.3 1.5 0.01 0.01 Background CPU(s): 0.0 0.2 0.00 0.00 Redo size (bytes): 704,149.3 3,804,370.3 Logical read (blocks): 26,161.4 141,344.5 Block changes: 2,706.1 14,620.7 Physical read (blocks): 12,694.8 68,587.3 Physical write (blocks): 1,671.8 9,032.3 Read IO requests: 145.7 787.3 Write IO requests: 68.6 370.6 Read IO (MB): 99.2 535.8 Write IO (MB): 13.1 70.6 IM scan rows: 0.0 0.0 Session Logical Read IM: 0.0 0.0 User calls: 22.8 122.9 Parses (SQL): 12.9 69.9 Hard parses (SQL): 0.2 1.3 SQL Work Area (MB): 5.8 31.1 Logons: 0.1 0.5 User logons: 0.1 0.5 Executes (SQL): 23.3 126.0 Rollbacks: 0.0 0.0 Transactions: 0.2 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 98.78 In-memory Sort %: 100.00 Library Hit %: 99.50 Soft Parse %: 98.14 Execute to Parse %: 44.52 Latch Hit %: 99.99 Parse CPU to Parse Elapsd %: 83.40 % Non-Parse CPU: 98.86 Flash Cache Hit %: 0.00 Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Avg % DB Wait Event Waits Time (sec) Wait time Class ------------------------------ ----------- ---------- --------- ------ -------- DB CPU 2000.4 79.2 direct path read 276,418 208.9 755.78us 8.3 User I/O direct path read temp 185,550 183 .99ms 7.2 User I/O direct path write temp 112,864 105.9 938.23us 4.2 User I/O db file scattered read 10,684 29.5 2.76ms 1.2 User I/O db file sequential read 56,600 25.3 446.74us 1.0 User I/O SQL*Net more data to client 368,338 11.9 32.42us .5 Network SQL*Net more data from client 112,323 1.9 16.57us .1 Network PGA memory operation 70,129 1.4 19.53us .1 Other control file sequential read 8,563 1.3 153.99us .1 System I Wait Classes by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg Avg Total Wait Wait % DB Active Wait Class Waits Time (sec) Time time Sessions ---------------- ---------------- ---------------- ---------- ------ -------- DB CPU 2,000 79.2 0.3 User I/O 647,080 554 856.78us 21.9 0.1 System I/O 110,019 52 475.27us 2.1 0.0 Other 97,024 25 262.81us 1.0 0.0 Network 641,078 14 21.88us .6 0.0 Application 655 2 3.11ms .1 0.0 Commit 692 1 1.32ms .0 0.0 Concurrency 1,716 1 502.72us .0 0.0 Configuration 17 0 11.62ms .0 0.0 Administrative 2 0 23.33ms .0 0.0 Host CPU ~~~~~~~~ Load Average CPUs Cores Sockets Begin End %User %System %WIO %Idle ----- ----- ------- --------- --------- --------- --------- --------- --------- 8 8 4 0.14 0.06 3.5 0.5 0.1 96.0 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 4.0 % of busy CPU for Instance: 101.7 %DB time waiting for CPU - Resource Mgr: 0.0 IO Profile Read+Write/Second Read/Second Write/Second ~~~~~~~~~~ ----------------- --------------- --------------- Total Requests: 229.2 151.2 77.9 Database Requests: 214.3 145.7 68.6 Optimized Requests: 0.0 0.0 0.0 Redo Requests: 7.5 1.0 6.5 Total (MB): 116.2 100.7 15.5 Database (MB): 112.2 99.2 13.1 Optimized Total (MB): 0.0 0.0 0.0 Redo (MB): 2.4 1.0 1.4 Database (blocks): 14,366.6 12,694.8 1,671.8 Via Buffer Cache (blocks): 272.0 184.9 87.1 Direct (blocks): 14,094.6 12,509.9 1,584.7 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End ------------ ------------ Host Mem (MB): 140,992.8 140,992.8 SGA use (MB): 98,304.0 98,304.0 PGA use (MB): 3,138.3 3,122.8 % Host Mem used for SGA+PGA: 71.95 71.94 Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 81,920M 81,920M Std Block Size: 8K Shared Pool Size: 12,664M 12,665M Log Buffer: 253,672K In-Memory Area: 0M 0M Shared Pool Statistics Begin End ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ------ ------ Memory Usage %: 93.96 93.96 % SQL with executions>1: 98.95 98.83 % Memory for SQL w/exec>1: 89.78 89.64 Time Model Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> DB Time represents total time in user calls -> DB CPU represents CPU time of foreground processes -> Total CPU Time represents foreground and background processes -> Statistics including the word "background" measure background process time, therefore do not contribute to the DB time statistic -> Ordered by % of DB time in descending order, followed by Statistic Name % of % of Total Statistic Name Time (s) DB Time CPU Time ------------------------------------------ ------------------ ------- ---------- sql execute elapsed time 2,474.4 97.9 DB CPU 2,000.4 79.2 87.0 RMAN cpu time (backup/restore) 37.5 1.5 1.6 connection management call elapsed time 27.7 1.1 parse time elapsed 27.4 1.1 hard parse elapsed time 7.3 .3 PL/SQL execution elapsed time 1.5 .1 inbound PL/SQL rpc elapsed time 1.4 .1 hard parse (sharing criteria) elapsed time 0.0 .0 hard parse (bind mismatch) elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 DB time 2,526.6 background elapsed time 344.3 background cpu time 298.0 13.0 total CPU time 2,298.4 ------------------------------------------------------ Operating System Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> *TIME statistic values are diffed. All others display actual values. End Value is displayed if different -> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name Statistic Value End Value ------------------------- ---------------------- ---------------- FREE_MEMORY_BYTES 7,086,686,208 7,271,034,880 INACTIVE_MEMORY_BYTES 27,829,321,728 27,829,436,416 SWAP_FREE_BYTES 19,273,867,264 BUSY_TIME 226,104 IDLE_TIME 5,472,249 IOWAIT_TIME 6,728 NICE_TIME 0 SYS_TIME 26,507 USER_TIME 197,586 LOAD 0 0 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 0 VM_OUT_BYTES 0 PHYSICAL_MEMORY_BYTES 147,841,638,400 NUM_CPUS 8 NUM_CPU_CORES 8 NUM_CPU_SOCKETS 4 GLOBAL_RECEIVE_SIZE_MAX 4,194,304 GLOBAL_SEND_SIZE_MAX 1,048,576 TCP_RECEIVE_SIZE_DEFAULT 87,380 TCP_RECEIVE_SIZE_MAX 6,291,456 TCP_RECEIVE_SIZE_MIN 4,096 TCP_SEND_SIZE_DEFAULT 16,384 TCP_SEND_SIZE_MAX 4,194,304 TCP_SEND_SIZE_MIN 4,096 ------------------------------------------------------ Operating System Statistics - DetailDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1 Snap Time Load %busy %user %sys %idle %iowait --------------- -------- -------- -------- -------- -------- -------- 01-Oct 11:00:55 0.1 N/A N/A N/A N/A N/A 01-Oct 12:00:59 1.1 3.9 3.4 0.4 96.1 0.2 01-Oct 13:00:03 0.1 4.0 3.5 0.5 96.0 0.1 ------------------------------------------------------ Foreground Wait Class DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> s - second, ms - millisecond, us - microsecond, ns - nanosecond -> ordered by wait time desc, waits desc -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Captured Time accounts for 101.9% of Total DB time 2,526.64 (s) -> Total FG Wait Time: 575.31 (s) DB CPU time: 2,000.37 (s) %Time Total Wait Wait Class Waits -outs Time (s) Avg wait %DB time -------------------- --------------- ----- --------------- ---------- --------- DB CPU 2,000 79.2 User I/O 646,075 0 554 857.51us 21.9 Network 639,110 0 14 21.94us 0.6 Application 655 0 2 3.11ms 0.1 Other 70,945 0 2 27.61us 0.1 System I/O 8,683 0 1 162.16us 0.1 Commit 679 0 1 1.33ms 0.0 Concurrency 1,443 0 1 511.05us 0.0 Configuration 15 93 0 12.45ms 0.0 Administrative 2 0 0 23.33ms 0.0 ------------------------------------------------------ Foreground Wait Events DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> s - second, ms - millisecond, us - microsecond, ns - nanosecond -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Total %Time Wait Waits % DB Event Waits -outs Time (s) Avg wait /txn time -------------------------- ----------- ----- -------- --------- -------- ------ direct path read 276,418 209 755.78us 208.9 8.3 direct path read temp 185,550 183 .99ms 140.2 7.2 direct path write temp 112,864 106 938.23us 85.3 4.2 db file scattered read 10,684 29 2.76ms 8.1 1.2 db file sequential read 56,600 25 446.74us 42.8 1.0 SQL*Net more data to clien 368,338 12 32.42us 278.4 .5 SQL*Net more data from cli 112,323 2 16.57us 84.9 .1 PGA memory operation 70,129 1 19.53us 53.0 .1 control file sequential re 8,563 1 153.99us 6.5 .1 local write wait 849 1 1.34ms 0.6 .0 enq: RO - fast object reus 558 1 1.66ms 0.4 .0 log file sync 679 1 1.33ms 0.5 .0 enq: KO - fast object chec 18 1 35.49ms 0.0 .0 library cache: mutex X 1,291 1 396.71us 1.0 .0 enq: TX - row lock content 1 0 413.93ms 0.0 .0 recovery area: computing o 2 0 150.65ms 0.0 .0 SQL*Net message to client 158,449 0 1.39us 119.8 .0 Disk file operations I/O 2,694 0 73.63us 2.0 .0 undo segment extension 14 100 0 11.97ms 0.0 .0 reliable message 584 0 225.36us 0.4 .0 recovery area: computing b 2 0 62.83ms 0.0 .0 db file parallel read 416 0 273.89us 0.3 .0 library cache lock 16 0 6.26ms 0.0 .0 library cache load lock 26 0 3.72ms 0.0 .0 control file parallel writ 96 0 841.55us 0.1 .0 SQL*Net break/reset to cli 78 0 679.15us 0.1 .0 switch logfile command 2 0 23.33ms 0.0 .0 log file switch completion 1 0 19.25ms 0.0 .0 recovery area: computing d 2 0 8.04ms 0.0 .0 asynch descriptor resize 218 100 0 60.62us 0.2 .0 library cache: bucket mute 10 0 1.00ms 0.0 .0 cursor: pin S 7 0 1.07ms 0.0 .0 log file sequential read 22 0 309.77us 0.0 .0 latch: shared pool 70 0 96.56us 0.1 .0 row cache lock 1 0 3.52ms 0.0 .0 Log archive I/O 2 0 931.50us 0.0 .0 SQL*Net message from clien 158,454 111,213 701.86ms 119.8 watchdog main loop 4,765 14,292 2999.38ms 3.6 ------------------------------------------------------ Background Wait Events DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by wait time desc, waits desc (idle events last) -> Only events with Total Wait Time (s) >= .001 are shown -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Total %Time Wait Waits % bg Event Waits -outs Time (s) Avg wait /txn time -------------------------- ----------- ----- -------- --------- -------- ------ log file parallel write 18,317 0 26 1.42ms 13.8 7.5 LGWR worker group ordering 1,611 0 8 4.71ms 1.2 2.2 latch free 9,639 0 7 743.32us 7.3 2.1 LGWR all worker groups 6,692 0 7 1.02ms 5.1 2.0 control file parallel writ 3,824 0 5 1.35ms 2.9 1.5 Log archive I/O 161 0 5 29.94ms 0.1 1.4 control file sequential re 19,847 0 5 234.26us 15.0 1.4 db file parallel write 29,035 0 4 149.52us 21.9 1.3 log file sequential read 4,381 0 4 852.47us 3.3 1.1 db file async I/O submit 25,700 0 2 82.02us 19.4 .6 oracle thread bootstrap 33 0 1 32.67ms 0.0 .3 db file sequential read 735 0 0 301.77us 0.6 .1 LGWR any worker group 461 0 0 366.44us 0.3 .0 PGA memory operation 6,168 0 0 26.60us 4.7 .0 reliable message 230 0 0 635.03us 0.2 .0 library cache: mutex X 249 0 0 437.35us 0.2 .0 Disk file operations I/O 173 0 0 538.01us 0.1 .0 change tracking file synch 117 0 0 717.66us 0.1 .0 os thread creation 33 0 0 2.46ms 0.0 .0 direct path write 94 0 0 802.26us 0.1 .0 control file single write 32 0 0 2.01ms 0.0 .0 RMAN backup & recovery I/O 24 0 0 2.27ms 0.0 .0 enq: CF - contention 3 0 0 17.12ms 0.0 .0 change tracking file synch 117 0 0 429.37us 0.1 .0 KSV master wait 473 100 0 96.70us 0.4 .0 oradebug request completio 1 0 0 40.56ms 0.0 .0 enq: CR - block range reus 190 0 0 93.29us 0.1 .0 ADR block file read 25 0 0 699.20us 0.0 .0 log file switch completion 1 0 0 10.22ms 0.0 .0 log file sync 13 0 0 682.62us 0.0 .0 SQL*Net message to client 1,968 0 0 3.13us 1.5 .0 latch: shared pool 9 0 0 590.78us 0.0 .0 library cache: bucket mute 1 0 0 5.13ms 0.0 .0 buffer busy waits 7 0 0 648.14us 0.0 .0 log file single write 16 0 0 208.81us 0.0 .0 ADR block file write 9 0 0 357.22us 0.0 .0 cursor: pin S 1 0 0 1.20ms 0.0 .0 rdbms ipc message 101,123 61 142,969 1413.81ms 76.4 Space Manager: slave idle 18,526 0 56,925 3072.72ms 14.0 class slave wait 498 0 28,551 57.33 s 0.4 pmon timer 4,766 100 14,297 2999.80ms 3.6 LGWR worker group idle 18,121 0 14,260 786.93ms 13.7 DIAG idle wait 14,240 100 14,237 999.81ms 10.8 dispatcher timer 120 100 7,201 60.01 s 0.1 Streams AQ: qmn coordinato 631 0 7,168 11.36 s 0.5 Streams AQ: qmn slave idle 518 0 7,168 13.84 s 0.4 OFS idle 2,383 100 7,149 3000.01ms 1.8 heartbeat redo informer 13,518 0 7,148 528.80ms 10.2 wait for unread message on 7,148 100 7,148 999.99ms 5.4 pman timer 2,383 100 7,148 2999.42ms 1.8 lreg timer 2,380 100 7,147 3003.01ms 1.8 Data Guard: Timer 238 0 7,140 30.00 s 0.2 AQPC idle 238 100 7,140 30.00 s 0.2 Data Guard: Gap Manager 119 0 7,140 60.00 s 0.1 smon timer 28 75 7,127 254.52 s 0.0 jobq slave wait 730 99 362 496.46ms 0.6 SQL*Net message from clien 2,566 0 65 25.17ms 1.9 ------------------------------------------------------ Wait Event Histogram DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: us is microseconds -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (idle events last) % of Total Waits ----------------------------------------------- Total Event Waits <8us <16us <32us <64us <128u <256u <512u >=512 ------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 25 16.0 16.0 52.0 16.0 ADR block file write 9 77.8 22.2 ADR file lock 14 35.7 7.1 7.1 14.3 21.4 14.3 AWR Flush 11 100.0 Disk file operations I/O 2867 .1 9.0 44.3 30.2 6.8 .8 4.2 4.6 KSV master wait 473 .2 4.0 15.0 66.2 14.6 LGWR all worker groups 6692 .1 .2 2.5 97.2 LGWR any worker group 461 .7 .2 1.3 8.9 23.0 26.2 24.5 15.2 LGWR wait for redo copy 13 23.1 61.5 15.4 LGWR worker group orderin 1611 .4 .1 .9 4.7 2.0 1.4 .7 89.8 Log archive I/O 163 .6 99.4 PGA memory operation 76.3K 75.5 16.7 5.0 1.6 .4 .5 .2 .2 Parameter File I/O 2 50.0 50.0 RMAN backup & recovery I/ 24 12.5 8.3 20.8 25.0 33.3 Redo Transport Open 4 100.0 SQL*Net break/reset to cl 78 44.9 1.3 3.8 1.3 35.9 2.6 10.3 SQL*Net message to client 160.4K 99.5 .4 .1 .0 .0 SQL*Net more data from cl 112.3K 35.8 27.6 27.5 7.4 1.4 .3 .1 .0 SQL*Net more data to clie 368.3K 10.8 4.5 33.5 47.8 3.4 .0 .0 .0 asynch descriptor resize 445 50.3 15.3 25.4 7.2 .2 1.6 buffer busy waits 8 12.5 12.5 37.5 37.5 change tracking file sync 117 8.5 78.6 12.8 change tracking file sync 117 32.5 60.7 6.8 control file parallel wri 3919 35.0 65.0 control file sequential r 28.4K 24.4 55.1 16.3 4.2 control file single write 32 9.4 37.5 53.1 cursor: pin S 8 100.0 db file async I/O submit 25.7K 1.6 28.6 35.3 21.6 9.1 2.7 1.1 db file parallel read 416 11.3 53.8 28.6 6.3 db file parallel write 29K 32.5 13.9 6.6 8.1 11.1 11.0 10.8 5.9 db file scattered read 10.7K .0 .3 2.1 97.6 db file sequential read 57.4K 1.9 5.8 74.3 17.9 direct path read 276.4K .2 .2 .8 3.3 6.6 12.8 23.4 52.8 direct path read temp 185.6K .0 .0 .4 2.3 7.8 14.0 9.3 66.2 direct path write 93 2.2 19.4 57.0 21.5 direct path write temp 112.9K .1 .5 .3 1.5 2.3 4.6 10.9 79.7 enq: CF - contention 3 100.0 enq: CR - block range reu 190 3.2 14.2 13.7 46.3 21.6 1.1 enq: HW - contention 1 100.0 enq: KO - fast object che 18 100.0 enq: RO - fast object reu 558 .2 4.1 24.4 19.4 1.8 50.2 enq: TX - row lock conten 1 100.0 get branch/thread/sequenc 8 12.5 62.5 12.5 12.5 latch free 9640 .0 .0 .1 .0 .0 2.1 97.7 latch: cache buffers chai 2 100.0 latch: call allocation 1 100.0 latch: redo allocation 6 16.7 33.3 33.3 16.7 latch: shared pool 79 3.8 1.3 43.0 35.4 11.4 1.3 3.8 library cache load lock 26 3.8 3.8 92.3 library cache lock 16 12.5 87.5 library cache: bucket mut 11 27.3 45.5 9.1 18.2 library cache: mutex X 1539 74.1 14.2 5.4 1.1 .5 .3 .1 4.2 local write wait 849 100.0 log file parallel write 18.3K 6.4 35.4 58.1 log file sequential read 4403 .2 .1 .2 1.2 4.4 11.3 23.2 59.5 log file single write 16 12.5 75.0 12.5 log file switch completio 2 100.0 log file sync 692 .1 .1 1.2 3.0 11.6 37.0 47.0 oracle thread bootstrap 33 100.0 oradebug request completi 1 100.0 Wait Event Histogram DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: us is microseconds -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (idle events last) % of Total Waits ----------------------------------------------- Total Event Waits <8us <16us <32us <64us <128u <256u <512u >=512 ------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- os thread creation 33 100.0 recovery area: computing 2 100.0 recovery area: computing 2 100.0 recovery area: computing 2 100.0 reliable message 814 .1 7.7 52.5 30.0 9.7 row cache lock 1 100.0 row cache mutex 25 20.0 52.0 24.0 4.0 switch logfile command 2 100.0 undo segment extension 14 7.1 28.6 64.3 AQPC idle 238 100.0 DIAG idle wait 14.2K 100.0 Data Guard: Gap Manager 119 100.0 Data Guard: Timer 238 100.0 LGWR worker group idle 18.1K .1 .0 .6 2.0 3.1 3.4 3.1 87.6 OFS idle 2383 100.0 SQL*Net message from clie 161K .0 .1 .4 1.9 19.6 56.9 21.1 Space Manager: slave idle 18.5K .0 .5 .5 3.1 .9 1.0 .1 93.9 Streams AQ: qmn coordinat 629 17.3 1.3 .3 26.9 13.7 40.5 Streams AQ: qmn slave idl 516 50.6 49.4 class slave wait 497 .2 2.0 2.8 95.0 dispatcher timer 120 100.0 heartbeat redo informer 13.5K .0 .3 99.7 jobq slave wait 730 100.0 lreg timer 2381 100.0 pman timer 2383 100.0 pmon timer 4765 100.0 rdbms ipc message 101.1K 4.3 .5 .2 1.5 3.7 1.8 4.5 83.6 smon timer 28 100.0 wait for unread message o 7148 100.0 watchdog main loop 4761 100.0 ------------------------------------------------------ Wait Event Histogram (up to 64 us)DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: us is microseconds -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------------- Waits 1us Event - 64us <1us <2us <4us <8us <16us <32us <64us >=64u ------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ADR file lock 9 7.1 14.3 14.3 7.1 7.1 14.3 35.7 AWR Flush 11 27.3 63.6 9.1 Disk file operations I/O 2399 .1 9.0 44.3 30.2 16.3 KSV master wait 91 .2 4.0 15.0 80.8 LGWR any worker group 51 .4 .2 .2 1.3 8.9 88.9 LGWR wait for redo copy 11 23.1 61.5 15.4 LGWR worker group orderin 97 .1 .2 .1 .1 .9 4.7 94.0 PGA memory operation 75.4K 22.5 53.0 16.7 5.0 1.6 1.2 RMAN backup & recovery I/ 5 4.2 4.2 4.2 8.3 79.2 Redo Transport Open 4 25.0 75.0 SQL*Net break/reset to cl 40 2.6 30.8 11.5 1.3 3.8 1.3 48.7 SQL*Net message to client 160.4K 9.4 51.8 36.9 1.5 .4 .1 .0 .0 SQL*Net more data from cl 110.3K .0 35.8 27.6 27.5 7.4 1.8 SQL*Net more data to clie 355.7K .3 10.5 4.5 33.5 47.8 3.4 asynch descriptor resize 437 4.0 39.1 7.2 15.3 25.4 7.2 1.8 buffer busy waits 1 12.5 87.5 db file async I/O submit 16.8K 1.6 28.6 35.3 34.5 db file parallel write 17.8K 9.9 22.6 13.9 6.6 8.1 38.9 direct path read 12.4K .0 .2 .2 .8 3.3 95.5 direct path read temp 5074 .0 .0 .4 2.3 97.3 direct path write temp 2796 .0 .1 .5 .3 1.5 97.5 enq: CR - block range reu 59 3.2 14.2 13.7 68.9 enq: RO - fast object reu 24 .2 4.1 95.7 get branch/thread/sequenc 8 12.5 62.5 12.5 12.5 latch free 10 .0 .0 .0 .1 99.9 latch: call allocation 1 100.0 latch: redo allocation 3 16.7 33.3 50.0 latch: shared pool 38 1.3 2.5 1.3 43.0 51.9 library cache: bucket mut 9 18.2 9.1 45.5 9.1 18.2 library cache: mutex X 1459 37.6 36.5 14.2 5.4 1.1 5.2 log file sequential read 72 .0 .2 .0 .1 .2 1.2 98.4 log file sync 10 .1 .1 1.2 98.6 reliable message 1 .1 99.9 row cache mutex 25 12.0 8.0 52.0 24.0 4.0 undo segment extension 5 7.1 28.6 64.3 ------------------------------------------------------ Wait Event Histogram (up to 32 ms)DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: us is microseconds, ms is 1024 microseconds (approximately 1 millisecond) -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------------- Waits 1ms Event to 32m <512 <1ms <2ms <4ms <8ms <16ms <32ms >=32m ------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 4 84.0 8.0 4.0 4.0 ADR block file write 2 77.8 22.2 Disk file operations I/O 132 95.4 3.8 .6 .1 .1 .1 LGWR all worker groups 6502 2.8 86.4 7.4 1.4 1.4 .2 .3 .0 LGWR any worker group 70 84.8 11.7 1.3 1.1 1.1 LGWR worker group orderin 1447 10.2 1.1 1.3 16.8 65.3 5.2 .1 Log archive I/O 137 .6 .6 1.2 82.2 15.3 PGA memory operation 131 99.8 .0 .0 .1 .0 .0 .0 .0 RMAN backup & recovery I/ 8 66.7 4.2 4.2 4.2 20.8 SQL*Net break/reset to cl 8 89.7 1.3 2.6 3.8 2.6 SQL*Net more data from cl 26 100.0 .0 .0 .0 .0 SQL*Net more data to clie 6 100.0 .0 .0 .0 .0 .0 asynch descriptor resize 7 98.4 .9 .4 .2 buffer busy waits 3 62.5 12.5 25.0 change tracking file sync 15 87.2 12.0 .9 change tracking file sync 7 93.2 5.1 .9 .9 control file parallel wri 2528 35.0 57.2 3.4 1.0 .8 .7 1.5 .5 control file sequential r 1188 95.8 3.6 .5 .1 .0 .0 control file single write 17 46.9 9.4 34.4 9.4 cursor: pin S 8 100.0 db file async I/O submit 281 98.9 .7 .2 .1 .1 .0 db file parallel read 26 93.8 4.8 .7 .5 .2 db file parallel write 1723 94.1 4.3 1.2 .2 .1 .0 .0 db file scattered read 10.4K 2.4 4.3 19.4 67.3 5.6 .4 .0 .7 db file sequential read 10.3K 82.1 13.3 4.3 .3 .0 .0 .0 .0 direct path read 145.8K 47.2 29.3 18.4 4.8 .3 .0 .0 .1 direct path read temp 122.8K 33.8 17.4 46.4 2.2 .2 .1 .0 .1 direct path write 19 78.5 17.2 1.1 2.2 1.1 direct path write temp 89.9K 20.3 49.3 28.4 1.0 .5 .2 .3 .0 enq: CF - contention 3 33.3 66.7 enq: KO - fast object che 10 5.6 5.6 22.2 22.2 44.4 enq: RO - fast object reu 280 49.8 2.2 29.0 10.4 4.5 3.0 1.1 latch free 9420 2.3 92.9 4.7 .1 .1 latch: redo allocation 1 83.3 16.7 latch: shared pool 3 96.2 2.5 1.3 library cache load lock 24 7.7 3.8 26.9 15.4 46.2 library cache lock 13 12.5 12.5 6.3 18.8 43.8 6.3 library cache: bucket mut 2 81.8 9.1 9.1 library cache: mutex X 65 95.8 .1 .1 .1 3.8 .1 local write wait 849 54.5 41.6 2.1 .8 .2 .7 log file parallel write 10.6K 41.9 38.6 3.1 3.5 11.5 1.2 .2 .0 log file sequential read 2619 40.5 26.1 28.1 5.1 .1 log file switch completio 2 50.0 50.0 log file sync 323 53.0 21.2 8.7 10.3 5.5 .6 .4 .3 oracle thread bootstrap 22 66.7 33.3 os thread creation 33 3.0 93.9 3.0 recovery area: computing 2 50.0 50.0 reliable message 79 90.3 2.2 6.0 1.5 row cache lock 1 100.0 switch logfile command 2 100.0 undo segment extension 7 35.7 35.7 14.3 14.3 ------------------------------------------------------ Wait Event Histogram (up to 2 sec)DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second) -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------------- Waits 64ms Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s ------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- LGWR all worker groups 2 100.0 .0 Log archive I/O 25 84.7 15.3 PGA memory operation 4 100.0 .0 SQL*Net more data to clie 2 100.0 .0 change tracking file sync 1 99.1 .9 control file parallel wri 18 99.5 .4 .0 db file scattered read 72 99.3 .7 db file sequential read 4 100.0 .0 direct path read 141 99.9 .1 .0 direct path read temp 147 99.9 .1 direct path write 1 98.9 1.1 direct path write temp 22 100.0 .0 enq: KO - fast object che 8 55.6 27.8 16.7 enq: TX - row lock conten 1 100.0 library cache lock 1 93.8 6.3 log file parallel write 4 100.0 .0 log file sync 2 99.7 .3 oracle thread bootstrap 11 66.7 33.3 oradebug request completi 1 100.0 recovery area: computing 2 100.0 recovery area: computing 2 100.0 undo segment extension 2 85.7 14.3 ------------------------------------------------------ Wait Event Histogram (up to 2 min)DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 No data exists for this section of the report. ------------------------------------------------------ Wait Event Histogram (up to 1 hr)DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 No data exists for this section of the report. ------------------------------------------------------ Service Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads (K) Reads (K) ---------------------------- ------------ ------------ ------------ ------------ SYS$USERS 1,811 1,388 63,217 90,332 RFDWSY20 716 613 27,523 92,310 RFDWSY20XDB 0 0 0 0 SYS$BACKGROUND 0 0 1 4,357 ------------------------------------------------------ Service Wait Class Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 seconds Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 496809 449 1443 1 2 0 638858 14 RFDWSY20 149492 105 0 0 0 0 420 0 SYS$BACKGROUND 792 0 267 0 0 0 0 0 ------------------------------------------------------ Top 10 Channel Waits DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top 10 Channel Waits by wait time Channel ---------------------------------------------------------------- Total Wait Waits Time (s) Avg Wait ----------- -------- --------- obj broadcast channel 586 0 225.34us RBR channel 230 0 634.35us ------------------------------------------------------ Top Process Types by Wait Class DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> This table displays top process types ordered by wait class Proce Description Wait Class Type Wait Time (sec ----- ------------------------------ ---------------- -------------- Others (foregrounds + unclassi User I/O 554.1 LG Log Writer Slave System I/O 25.9 Others (foregrounds + unclassi Network 14.1 CKPT checkpoint System I/O 8.2 LG Log Writer Slave Other 7.6 W space management slave pool Other 7.1 LGWR Redo etc. Other 7.0 DBW0 db writer process 0 System I/O 6.4 ARC3 Archival Process 3 System I/O 4.8 ARC0 Archival Process 0 System I/O 3.8 Others (foregrounds + unclassi Other 2.1 Others (foregrounds + unclassi Application 2.1 Others (foregrounds + unclassi System I/O 1.9 Others (foregrounds + unclassi Commit .9 MMON Manageability Monitor Process Other .8 Others (foregrounds + unclassi Concurrency .7 MMON Manageability Monitor Process System I/O .5 M MMON slave class 1 System I/O .5 CJQ0 Job Queue Coordinator Other .3 Others (foregrounds + unclassi Configuration .2 ------------------------------------------------------ Top Process Types by CPU Used DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> This table displays top process types ordered by CPU time Proces Description CPU Time (sec) ------ ------------------------------ -------------- Others (foregrounds + unclassi 2,039.7 M MMON slave class 1 66.3 DIA0 diagnosibility process 0 59.3 MMNL Manageability Monitor Process 24.4 MZ MMON slave class 2 22.8 DBW0 db writer process 0 10.7 DBRM DataBase Resource Manager 9.5 CTWR Change Tracking Writer 8.9 CJQ0 Job Queue Coordinator 8.7 GEN1 generic1 8.6 LG Log Writer Slave 8.3 MMON Manageability Monitor Process 7.8 CKPT checkpoint 7.0 W space management slave pool 3.8 ARC0 Archival Process 0 3.7 ARC3 Archival Process 3 3.7 LGWR Redo etc. 3.3 P Parallel query slave 3.3 VKTM Virtual Keeper of TiMe process 2.9 SMCO Space Manager Process 2.1 ------------------------------------------------------ SQL ordered by Elapsed Time DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 -> %Total - Elapsed Time as a percentage of Total DB time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 97.2% of Total DB Time (s): 2,527 -> Captured PL/SQL account for 1.1% of Total DB Time (s): 2,527 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- 658.8 4 164.70 26.1 79.7 20.9 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 403.6 21 19.22 16.0 100.0 .0 3d0cwt2sd9sbp Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept 372.5 1 372.54 14.7 68.8 31.9 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 356.6 1 356.57 14.1 69.8 30.7 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 271.3 21 12.92 10.7 62.1 38.8 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 58.7 4 14.68 2.3 63.6 36.8 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR EATE_DATE) >= TRUNC(sysdate) - '180' 43.6 1 43.63 1.7 68.3 33.8 fkg79qq2bstg3 Module: JDBC Thin Client delete from FDW_DW.FDW_F_ASSET_STATUS where SOURCE_APP = 'OEE' and SITE_ACTIV ITY_ID = 'SY2 Z' and ASSET_STATUS_START_DATE BETWEEN TO_DATE('20/08/2021 10:22 :22','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:22:23','DD/MM/YYYY HH24 :MI:SS') 40.6 21 1.93 1.6 100.0 .0 92mp5yqvnn33y Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 25.7 2 12.83 1.0 99.1 1.1 cxmv6m3u3fyfu Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_F_ASSET_STATUS target USING (SELECT FDW_STG_OSCARNG_TC.FDW _S_ASSET_STATUS.ASSET_ID, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STATUS.ASSET_STATUS_STA RT_DATE, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STATUS.OAQ10_LEVEL5_ID, FDW_STG_OSCARNG_ SQL ordered by Elapsed Time DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 -> %Total - Elapsed Time as a percentage of Total DB time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 97.2% of Total DB Time (s): 2,527 -> Captured PL/SQL account for 1.1% of Total DB Time (s): 2,527 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- TC.FDW_S_ASSET_STATUS.ASSET_STATUS_CAUSE_ID, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STAT 19.9 1 19.88 .8 98.6 1.4 6y6x11sncwr1b Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_F_ASSET_STATUS target USING (SELECT FDW_STG_OEE_Z.FDW_S_AS SET_STATUS.ASSET_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.ASSET_STATUS_START_DATE, F DW_STG_OEE_Z.FDW_S_ASSET_STATUS.OAQ10_LEVEL5_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATU S.ASSET_STATUS_CAUSE_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.CREW_ID, FDW_STG_OEE_Z ------------------------------------------------------ SQL ordered by CPU Time DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - CPU Time as a percentage of Total DB CPU -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 95.9% of Total CPU Time (s): 2,000 -> Captured PL/SQL account for 1.3% of Total CPU Time (s): 2,000 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 524.9 4 131.22 26.2 658.8 79.7 20.9 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 403.6 21 19.22 20.2 403.6 100.0 .0 3d0cwt2sd9sbp Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept 256.1 1 256.13 12.8 372.5 68.8 31.9 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 248.8 1 248.76 12.4 356.6 69.8 30.7 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 168.4 21 8.02 8.4 271.3 62.1 38.8 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 40.6 21 1.93 2.0 40.6 100.0 .0 92mp5yqvnn33y Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 37.4 4 9.34 1.9 58.7 63.6 36.8 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR EATE_DATE) >= TRUNC(sysdate) - '180' 29.8 1 29.81 1.5 43.6 68.3 33.8 fkg79qq2bstg3 Module: JDBC Thin Client delete from FDW_DW.FDW_F_ASSET_STATUS where SOURCE_APP = 'OEE' and SITE_ACTIV ITY_ID = 'SY2 Z' and ASSET_STATUS_START_DATE BETWEEN TO_DATE('20/08/2021 10:22 :22','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:22:23','DD/MM/YYYY HH24 :MI:SS') 25.4 2 12.71 1.3 25.7 99.1 1.1 cxmv6m3u3fyfu Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_F_ASSET_STATUS target USING (SELECT FDW_STG_OSCARNG_TC.FDW _S_ASSET_STATUS.ASSET_ID, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STATUS.ASSET_STATUS_STA RT_DATE, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STATUS.OAQ10_LEVEL5_ID, FDW_STG_OSCARNG_ SQL ordered by CPU Time DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - CPU Time as a percentage of Total DB CPU -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 95.9% of Total CPU Time (s): 2,000 -> Captured PL/SQL account for 1.3% of Total CPU Time (s): 2,000 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- TC.FDW_S_ASSET_STATUS.ASSET_STATUS_CAUSE_ID, FDW_STG_OSCARNG_TC.FDW_S_ASSET_STAT 19.6 1 19.61 1.0 19.9 98.6 1.4 6y6x11sncwr1b Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_F_ASSET_STATUS target USING (SELECT FDW_STG_OEE_Z.FDW_S_AS SET_STATUS.ASSET_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.ASSET_STATUS_START_DATE, F DW_STG_OEE_Z.FDW_S_ASSET_STATUS.OAQ10_LEVEL5_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATU S.ASSET_STATUS_CAUSE_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.CREW_ID, FDW_STG_OEE_Z ------------------------------------------------------ SQL ordered by User I/O Wait TimeDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - User I/O Time as a percentage of Total User I/O Wait time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 98.9% of Total User I/O Wait Time (s): -> Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): User I/O UIO per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 137.8 4 34.44 24.8 658.8 79.7 20.9 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 118.7 1 118.66 21.4 372.5 68.8 31.9 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 109.5 1 109.48 19.7 356.6 69.8 30.7 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 105.2 21 5.01 19.0 271.3 62.1 38.8 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 21.6 4 5.40 3.9 58.7 63.6 36.8 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR EATE_DATE) >= TRUNC(sysdate) - '180' 14.8 1 14.76 2.7 43.6 68.3 33.8 fkg79qq2bstg3 Module: JDBC Thin Client delete from FDW_DW.FDW_F_ASSET_STATUS where SOURCE_APP = 'OEE' and SITE_ACTIV ITY_ID = 'SY2 Z' and ASSET_STATUS_START_DATE BETWEEN TO_DATE('20/08/2021 10:22 :22','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:22:23','DD/MM/YYYY HH24 :MI:SS') 6.6 1 6.57 1.2 13.9 53.8 47.2 bvr6dfxktxg8b Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 5.5 1 5.54 1.0 12.3 56.6 45.0 bx3yag0xry8hz Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 5.3 1 5.34 1.0 11.7 55.4 45.6 85q9xgdvpyckw Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN SQL ordered by User I/O Wait TimeDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - User I/O Time as a percentage of Total User I/O Wait time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 98.9% of Total User I/O Wait Time (s): -> Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): User I/O UIO per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 5.2 1 5.24 0.9 10.5 51.2 49.7 bd3yn0h5zqk2j Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = ------------------------------------------------------ SQL ordered by Gets DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - Buffer Gets as a percentage of Total Buffer Gets -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Buffer Gets: 186,998,812 -> Captured SQL account for 94.6% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ----- ----- ------------- 43,314,037 21 2,062,573.2 23.2 271.3 62.1 38.8 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 39,731,652 21 1,891,983.4 21.2 403.6 100 0 3d0cwt2sd9sbp Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept 23,627,971 4 5,906,992.8 12.6 658.8 79.7 20.9 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 10,058,932 1 1.005893E+07 5.4 356.6 69.8 30.7 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 9,974,422 1 9,974,422.0 5.3 372.5 68.8 31.9 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 9,714,509 1 9,714,509.0 5.2 43.6 68.3 33.8 fkg79qq2bstg3 Module: JDBC Thin Client delete from FDW_DW.FDW_F_ASSET_STATUS where SOURCE_APP = 'OEE' and SITE_ACTIV ITY_ID = 'SY2 Z' and ASSET_STATUS_START_DATE BETWEEN TO_DATE('20/08/2021 10:22 :22','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:22:23','DD/MM/YYYY HH24 :MI:SS') 9,264,213 21 441,153.0 5.0 40.6 100 0 92mp5yqvnn33y Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 7,450,704 1 7,450,704.0 4.0 19.9 98.6 1.4 6y6x11sncwr1b Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_F_ASSET_STATUS target USING (SELECT FDW_STG_OEE_Z.FDW_S_AS SET_STATUS.ASSET_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.ASSET_STATUS_START_DATE, F DW_STG_OEE_Z.FDW_S_ASSET_STATUS.OAQ10_LEVEL5_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATU S.ASSET_STATUS_CAUSE_ID, FDW_STG_OEE_Z.FDW_S_ASSET_STATUS.CREW_ID, FDW_STG_OEE_Z 5,333,888 4 1,333,472.0 2.9 58.7 63.6 36.8 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR SQL ordered by Gets DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - Buffer Gets as a percentage of Total Buffer Gets -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Buffer Gets: 186,998,812 -> Captured SQL account for 94.6% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ----- ----- ------------- EATE_DATE) >= TRUNC(sysdate) - '180' 2,157,926 2 1,078,963.0 1.2 14.5 69.9 34.3 2qn9qn0pq7qjx Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_REL_PRODUCT_OPE_TRACEABILITY target USING (SELECT FDW_STG_ DW_BI.FDW_S_PRODUCT_OPE_TRACEABILITY.INITIAL_PRODUCT_UNIT_ID, FDW_STG_DW_BI.FDW_ S_PRODUCT_OPE_TRACEABILITY.CURRENT_PRODUCT_UNIT_ID, FDW_STG_DW_BI.FDW_S_PRODUCT_ OPE_TRACEABILITY.CURRENT_ASSET_OPERATION_ID, FDW_STG_DW_BI.FDW_S_PRODUCT_OPE_TRA ------------------------------------------------------ SQL ordered by Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> %Total - Physical Reads as a percentage of Total Disk Reads -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Disk Reads: 90,740,978 -> Captured SQL account for 100.0% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- 27,522,852 21 1.3106E+06 30.3 271.3 62.1 38.8 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 23,740,849 4 5.9352E+06 26.2 658.8 79.7 20.9 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 12,501,617 1 1.2502E+07 13.8 356.6 69.8 30.7 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 12,501,435 1 1.2501E+07 13.8 372.5 68.8 31.9 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 5,242,448 4 1.3106E+06 5.8 58.7 63.6 36.8 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR EATE_DATE) >= TRUNC(sysdate) - '180' 1,310,612 1 1.3106E+06 1.4 10.8 54.2 46.1 1sdyw8yzb4x2n Module: JDBC Thin Client SELECT pu.PRODUCT_SERIAL_NUMBER, pu.PRODUCT_UNIT_ID, pf.PRODUCT_TYPE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFER ENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID =pu.LOCAL_PRODUCT_REFERENCE_ID INN ER JOIN FDW_DW.FDW_R_PRODUCT_REFERENCE pr on pr.PRODUCT_REFERENCE_ID = Lopr.PRO 1,310,612 1 1.3106E+06 1.4 11.7 55.4 45.6 85q9xgdvpyckw Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 1,310,612 1 1.3106E+06 1.4 10.2 66.2 34.4 8dt8gc8j5gzqg Module: JDBC Thin Client SELECT pu.PRODUCT_SERIAL_NUMBER, pu.PRODUCT_UNIT_ID, pf.PRODUCT_TYPE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFER ENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID =pu.LOCAL_PRODUCT_REFERENCE_ID INN ER JOIN FDW_DW.FDW_R_PRODUCT_REFERENCE pr on pr.PRODUCT_REFERENCE_ID = Lopr.PRO 1,310,612 1 1.3106E+06 1.4 10.5 51.2 49.7 bd3yn0h5zqk2j Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN SQL ordered by Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> %Total - Physical Reads as a percentage of Total Disk Reads -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Disk Reads: 90,740,978 -> Captured SQL account for 100.0% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 1,310,612 1 1.3106E+06 1.4 13.9 53.8 47.2 bvr6dfxktxg8b Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 1,310,612 1 1.3106E+06 1.4 12.3 56.6 45.0 bx3yag0xry8hz Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 1,310,612 1 1.3106E+06 1.4 10.7 64.9 35.8 dsqqzu52nrpfp Module: JDBC Thin Client SELECT pu.PRODUCT_SERIAL_NUMBER, pu.PRODUCT_UNIT_ID, pf.PRODUCT_TYPE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFER ENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID =pu.LOCAL_PRODUCT_REFERENCE_ID INN ER JOIN FDW_DW.FDW_R_PRODUCT_REFERENCE pr on pr.PRODUCT_REFERENCE_ID = Lopr.PRO ------------------------------------------------------ SQL ordered by Physical Reads (UnOptimized)DB/Inst: RFDWSY20/RFDWSY20 Snaps: -> UnOptimized Read Reqs = Physical Read Reqs - (Optimized Read Reqs - Cell Flash Cache Read Hits for Controlfile) -> %Opt - Optimized Reads as percentage of SQL Read Requests -> %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs -> Total Physical Read Requests: 1,041,612 -> Captured SQL account for 73.8% of Total -> Total UnOptimized Read Requests: 1,041,612 -> Captured SQL account for 73.8% of Total -> Total Optimized Read Requests: 1 -> Captured SQL account for 0.0% of Total UnOptimized Physical UnOptimized Read Reqs Read Reqs Executions Reqs per Exe %Opt %Total SQL Id ----------- ----------- ---------- ------------ ------ ------ ------------- 288,154 288,154 4 72,038.5 0.0 27.7 89cf6rrncn00x Module: JDBC Thin Client SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR 182,526 182,526 1 182,526.0 0.0 17.5 ddhc1697xfnv7 Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 182,328 182,328 1 182,328.0 0.0 17.5 4vrwkq6321kny Module: JDBC Thin Client SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS 28,495 28,495 1 28,495.0 0.0 2.7 fkg79qq2bstg3 Module: JDBC Thin Client delete from FDW_DW.FDW_F_ASSET_STATUS where SOURCE_APP = 'OEE' and SITE_ACTIV ITY_ID = 'SY2 Z' and ASSET_STATUS_START_DATE BETWEEN TO_DATE('20/08/2021 10:22 :22','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:22:23','DD/MM/YYYY HH24 :MI:SS') 12,707 12,707 2 6,353.5 0.0 1.2 2qn9qn0pq7qjx Module: JDBC Thin Client MERGE INTO FDW_DW.FDW_REL_PRODUCT_OPE_TRACEABILITY target USING (SELECT FDW_STG_ DW_BI.FDW_S_PRODUCT_OPE_TRACEABILITY.INITIAL_PRODUCT_UNIT_ID, FDW_STG_DW_BI.FDW_ S_PRODUCT_OPE_TRACEABILITY.CURRENT_PRODUCT_UNIT_ID, FDW_STG_DW_BI.FDW_S_PRODUCT_ OPE_TRACEABILITY.CURRENT_ASSET_OPERATION_ID, FDW_STG_DW_BI.FDW_S_PRODUCT_OPE_TRA 10,348 10,348 1 10,348.0 0.0 1.0 1sdyw8yzb4x2n Module: JDBC Thin Client SELECT pu.PRODUCT_SERIAL_NUMBER, pu.PRODUCT_UNIT_ID, pf.PRODUCT_TYPE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFER ENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID =pu.LOCAL_PRODUCT_REFERENCE_ID INN ER JOIN FDW_DW.FDW_R_PRODUCT_REFERENCE pr on pr.PRODUCT_REFERENCE_ID = Lopr.PRO 10,348 10,348 1 10,348.0 0.0 1.0 85q9xgdvpyckw Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 10,348 10,348 1 10,348.0 0.0 1.0 8dt8gc8j5gzqg Module: JDBC Thin Client SELECT pu.PRODUCT_SERIAL_NUMBER, pu.PRODUCT_UNIT_ID, pf.PRODUCT_TYPE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFER ENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID =pu.LOCAL_PRODUCT_REFERENCE_ID INN ER JOIN FDW_DW.FDW_R_PRODUCT_REFERENCE pr on pr.PRODUCT_REFERENCE_ID = Lopr.PRO 10,348 10,348 1 10,348.0 0.0 1.0 bd3yn0h5zqk2j Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA SQL ordered by Physical Reads (UnOptimized)DB/Inst: RFDWSY20/RFDWSY20 Snaps: -> UnOptimized Read Reqs = Physical Read Reqs - (Optimized Read Reqs - Cell Flash Cache Read Hits for Controlfile) -> %Opt - Optimized Reads as percentage of SQL Read Requests -> %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs -> Total Physical Read Requests: 1,041,612 -> Captured SQL account for 73.8% of Total -> Total UnOptimized Read Requests: 1,041,612 -> Captured SQL account for 73.8% of Total -> Total Optimized Read Requests: 1 -> Captured SQL account for 0.0% of Total UnOptimized Physical UnOptimized Read Reqs Read Reqs Executions Reqs per Exe %Opt %Total SQL Id ----------- ----------- ---------- ------------ ------ ------ ------------- TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = 10,348 10,348 1 10,348.0 0.0 1.0 bvr6dfxktxg8b Module: JDBC Thin Client SELECT DISTINCT pu.PRODUCT_SERIAL_NUMBER, pu.LOCAL_PRODUCT_REFERENCE_ID AS REF_BANDAGE, pu.PRODUCT_UNIT_ID AS PU_BANDAGE, pu.PRODUCT_PRODUCTION_END_DA TE AS PRODUCTION_END_DATE_BANDAGE FROM FDW_DW.FDW_R_PRODUCT_UNIT pu INNER JOIN FDW_DW.FDW_R_LOCAL_PRODUCT_REFERENCE Lopr on Lopr.LOCAL_PRODUCT_REFERENCE_ID = ------------------------------------------------------ SQL ordered by Executions DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Executions: 166,655 -> Captured SQL account for 4.9% of Total Elapsed Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id ------------ --------------- -------------- ---------- ----- ----- ------------- 23,998 23,998 1.0 0.9 83.5 0 75fyw6ds49atv Module: w3wp.exe begin :con := "USER_PACKAGE"."VPD_FUNCTION_ROLE_RO"(:sn, :on); end; 1,452 1,452 1.0 4.5 86.4 .7 c6hp07afw53nz Module: JDBC Thin Client BEGIN FDW_DW.PCK_FDW_DW.RETRIEVE_FROM_DOSSIER_TO_CAMPAIGN(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ); END; 1,110 345 0.3 0.1 99.8 0 87gaftwrm2h68 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1 690 0 0.0 0.4 83.5 0 091fb1shwqyn8 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.prope rty,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_ob j#=:1 and (d.p_timestamp=nvl(:2,d.p_timestamp) or d.property=2) and o.owne r#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# 607 607 1.0 8.8 94.8 0 935kfxc90ps4s Module: JDBC Thin Client BEGIN user_package.set_user_context; END; 598 598 1.0 0.4 101.3 0 f0h5rpzmhju11 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_ NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANC E'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', ' SERVICE_NAME') from v$instance 589 0 0.0 0.4 85 0 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege# >0 578 578 1.0 0.2 98.9 0 5x2x7y2umr3y7 select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'P REPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2 , 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 8, 1 ), failover_retries, failover_ delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_co 568 568 1.0 4.0 100 .1 9zg9qd9bm4spu Module: JDBC Thin Client update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date( NULL), :2) where user#=:1 402 0 0.0 0.0 99.6 0 3t6crmxzc7gwj select /*+ opt_param('parallel_execution_enabled', 'false') EXEC_FROM_DBMS_XPLAN */ * from gv$sql_plan where 1=0 ------------------------------------------------------ SQL ordered by Parse Calls DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Parse Calls: 92,455 -> Captured SQL account for 10.1% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 43,151 N/A 46.67 2p9fv35c7zxtg 23,998 23,998 25.96 75fyw6ds49atv Module: w3wp.exe begin :con := "USER_PACKAGE"."VPD_FUNCTION_ROLE_RO"(:sn, :on); end; 1,452 N/A 1.57 5qr7ubcpm40x9 SELECT FRD.DOSSIER_ID , FRI.INDUS_ID , FRG.GAMME_ID , FRGO.GAMME_OPERATION_ID, F RP.PDP_ID , FRPS.PDP_SCHEDULE_ID , FRC.CAMPAIGN_ID FROM FDW_DW.FDW_R_DOSSIER FRD INNER JOIN FDW_DW.FDW_R_INDUS FRI ON ( FRD.DOSSIER_ID = FRI.DOSSIER_ID ) INNER JOIN FDW_DW.FDW_R_GAMME FRG ON ( FRG.INDUS_ID = FRI.INDUS_ID ) INNER JOIN FDW_DW 853 N/A 0.92 3dbzmtf9ahvzt 690 690 0.75 091fb1shwqyn8 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.prope rty,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_ob j#=:1 and (d.p_timestamp=nvl(:2,d.p_timestamp) or d.property=2) and o.owne r#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# 607 607 0.66 935kfxc90ps4s Module: JDBC Thin Client BEGIN user_package.set_user_context; END; 607 N/A 0.66 chgh9xa2r74bq SELECT DISTINCT SUBSTR(GRANTED_ROLE, 13, 4) AS GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = SYS_CONTEXT('USERENV', 'SESSION_USER') AND GRANTED_ROLE LIKE ( ' R_FDW_RO_D__%' ) 598 598 0.65 f0h5rpzmhju11 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_ NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'INSTANC E'), STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN'), SYS_CONTEXT('USERENV', ' SERVICE_NAME') from v$instance 589 589 0.64 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege# >0 578 578 0.63 5x2x7y2umr3y7 select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'P REPARSE', 4 , 0), decode(upper(failover_type), NULL, 1, 'NONE', 1, 'SESSION', 2 , 'SELECT', 4, 'TRANSACTION', 8, 'AUTO' , 8, 1 ), failover_retries, failover_ delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_co ------------------------------------------------------ SQL ordered by Sharable Memory DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- 510,313,072 N/A 3.84 5qr7ubcpm40x9 SELECT FRD.DOSSIER_ID , FRI.INDUS_ID , FRG.GAMME_ID , FRGO.GAMME_OPERATION_ID, F RP.PDP_ID , FRPS.PDP_SCHEDULE_ID , FRC.CAMPAIGN_ID FROM FDW_DW.FDW_R_DOSSIER FRD INNER JOIN FDW_DW.FDW_R_INDUS FRI ON ( FRD.DOSSIER_ID = FRI.DOSSIER_ID ) INNER JOIN FDW_DW.FDW_R_GAMME FRG ON ( FRG.INDUS_ID = FRI.INDUS_ID ) INNER JOIN FDW_DW 254,195,544 67 1.91 fymn7u9vgv747 WITH MONITOR_DATA AS ( SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance' ) AS INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST _REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXE C_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_FULL_PLAN_HASH_VALUE, SE 175,617,192 N/A 1.32 4bugmu5z8szn5 SELECT /*+ ordered push_pred(v) OPT_PARAM('_parallel_syspls_o bey_force' 'false') */ U.NAME, OT.NAME, DECODE(OT.TYPE#, 34, V.COMPART, OT.SUBNA ME) PART, DECODE(OT.TYPE#, 34, OT.SUBNAME, NULL) SPART, V.COL, V.GLOB_ST, V.USER _ST, V.DIST, V.DENS, V.SDIST, V.SSIZE, V.NCNT, V.TSTAMP, V.CLEN, V.MINVAL, V.MAX 117,487,495 5 0.88 6kbpur5uatk51 select /*jskqjobqlod1*/ /**/ /*+ no_m onitor no_statement_queuing current_instance */ /**/ CON_ID, OBJOID, CL SOID, RUNTIME, PRI, JOBTYPE, /**/ SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( /**/ select nvl(a.con_id,0) CON_ID, a.ob 75,090,336 N/A 0.57 f705bwx3q0ydq select count(*) from dba_autotask_window_clients c, (select window_name, max(log _date) max_log_date from dba_scheduler_window_log where operation = 'OPEN' group by window_name) wo, (select window_name, max(log_date) max_log_date from dba_sc heduler_window_log where operation = 'CLOSE' group by window_name) wc where c.wi 59,040,704 N/A 0.44 0nakmm882vmq0 50,463,160 N/A 0.38 9qnpv67wr6fcg SELECT /*jskqjobqlod3*/ /*+ no_monitor no_statement_queuing current_instanc e */ CON_ID, OBJOID, CLSOID, DECODE(BITAND(FLAGS, 16384), 0, RUNTIME, LETIME), (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, decode(INST, :1, -1, 1))), JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM, INST_ID, SRVNAME 49,879,024 N/A 0.38 bzz4yamw4369m SELECT /*jskqjobqlod2*/ /*+ no_monitor no_statement_queuing current_instanc e */ CON_ID, OBJOID, CLSOID, DECODE(BITAND(FLAGS,16384), 0,RUNTIME,LETIME), (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, decode(INST, :1, -1, 1))), JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM, INST_ID FROM ( select nvl(a.co 48,722,360 N/A 0.37 7z9wmu1t3kawd 48,164,872 N/A 0.36 6wm3n4d7bnddg 46,872,632 N/A 0.35 8bfgn7a4ff82x 44,485,304 N/A 0.33 7av38g2q94htu 44,394,528 N/A 0.33 3dbzmtf9ahvzt 41,027,512 N/A 0.31 9sg6u8xys290z 35,746,510 8 0.27 3kqrku32p6sfn MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_ PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME, ROUND(MAX(S .DELTA_READ_IO_BYTES/S.DELTA_TIME), 3) SCANRATE FROM GV$ACTIVE_SESSION_HISTORY S SQL ordered by Sharable Memory DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- , GV$SQL_PLAN P, OBJ$ O, USER$ U WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQ 34,961,384 N/A 0.26 f7y0pkb4a8yx3 34,099,408 N/A 0.26 chgh9xa2r74bq SELECT DISTINCT SUBSTR(GRANTED_ROLE, 13, 4) AS GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = SYS_CONTEXT('USERENV', 'SESSION_USER') AND GRANTED_ROLE LIKE ( ' R_FDW_RO_D__%' ) 32,600,648 N/A 0.25 3ws7wcfvukrjf SELECT OBJOID, CLSOID, (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, DECODE(INST, :1, -1, 1))), WT, INST, DECODE(B ITAND(STATUS, 8388608), 0, 0, 1), SCHLIM, ISLW, INST_ID FROM ( select a.obj# O BJOID, a.class_oid CLSOID, a.job_status STATUS, a.flags FLAGS, a.priorit 26,613,064 N/A 0.20 2gg8vg5n2263a 26,006,256 N/A 0.20 121ffmrc95v7g 24,134,528 N/A 0.18 2p9fv35c7zxtg 23,559,360 N/A 0.18 58bafyxdgn8r7 22,103,440 N/A 0.17 69k5bhm12sz98 21,276,392 N/A 0.16 6jwgqpwkwcab8 15,605,208 N/A 0.12 aaz3bayav0jwj 13,371,600 N/A 0.10 adds4wma0y8c4 13,197,112 N/A 0.10 38243c4tqrkxm 12,167,104 N/A 0.09 8p9z2ztb272bm 11,905,712 N/A 0.09 89f0xd8m24art 10,544,896 N/A 0.08 23nad9x295gkf 9,399,496 N/A 0.07 34rznuxy8h2a4 9,278,040 N/A 0.07 ajkubg7upg9pu 8,640,096 N/A 0.07 7p0q1q6sz5rvu INSERT INTO FDW_STG_OSCARNG_TC.FDW_S_PRODUCT_UNIT (PRODUCT_UNIT_ID,CAMPAIGN_ID,L OCAL_PRODUCT_REFERENCE_ID,CONDITIONING_ID,INTERMEDIATE_PROCESS_ID,PRODUCT_SERIAL _NUMBER,PRODUCT_PRODUCTION_START_DATE,PRODUCT_PRODUCTION_END_DAY,PRODUCT_PRODUCT ION_END_DATE,PRODUCT_RELEASED_DAY,PRODUCT_RELEASED_DATE,SOURCE_APP,JOB_ID,SITE_A 8,588,504 N/A 0.06 ch8t72tsys7vx 8,267,136 N/A 0.06 340chgpg8x78v 8,106,400 N/A 0.06 06gfrprr7w0r2 7,505,704 N/A 0.06 c8h20n1d0k95m 7,203,408 N/A 0.05 50vxqdkj4zu1w 6,946,368 N/A 0.05 a1zv6wju3ftgv SQL ordered by Sharable Memory DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- 6,531,728 690 0.05 091fb1shwqyn8 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.prope rty,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_ob j#=:1 and (d.p_timestamp=nvl(:2,d.p_timestamp) or d.property=2) and o.owne r#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# 6,412,666 2 0.05 1kb56hd6c73bv select count(*) num_windows, sum(case when jobs_started<>jobs_completed then 1 e lse 0 end) num_jobs_incomplete from (select x.cname_ketcl client_name, su m(case when operation = 'RUN' then 1 else 0 end) as jobs_started, sum(case when operation = 'RUN' and status = 'SUCCEEDED' then 1 else 5,950,394 568 0.04 9zg9qd9bm4spu Module: JDBC Thin Client update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date( NULL), :2) where user#=:1 5,449,440 N/A 0.04 fu9xz9pnu520q 4,885,504 N/A 0.04 a8ybwb4wf635t 4,173,408 N/A 0.03 b9nbhsbx8tqz5 3,890,467 21 0.03 3d0cwt2sd9sbp Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept 3,494,016 N/A 0.03 865qwpcdyggkk select spare6 from user$ where user#=:1 2,858,758 21 0.02 frrjd8g9c44yd Module: w3wp.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 2,657,971 21 0.02 92mp5yqvnn33y Module: iisexpress.exe with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase 2,607,672 N/A 0.02 3ms7w0c6ph91t 2,036,138 1,110 0.02 87gaftwrm2h68 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1 1,544,376 60 0.01 gs43xajg8rd5q Module: JDBC Thin Client INSERT INTO FDW_STG_DW_BI.FDW_S_PRODUCT_UNIT_COMPONENT_GENEALOGY (INITIAL_PRODUC T_UNIT_ID,CURRENT_PRODUCT_UNIT_ID,CURRENT_ASSET_OPERATION_ID,COMPONENT_PRODUCT_U NIT_ID,HIERARCHY_LEVEL,INITIAL_LOCAL_PRODUCT_REF_ID,INITIAL_OPERATION_ID,CURRENT _LOCAL_PRODUCT_REF_ID,CURRENT_ASSET_ID,CURRENT_ASSET_SUB_ASSEMBLY_ID,CURRENT_OPE SQL ordered by Sharable Memory DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id ---------------- ------------ -------- ------------- ------------------------------------------------------ SQL ordered by Version Count DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 3,723 N/A 5qr7ubcpm40x9 SELECT FRD.DOSSIER_ID , FRI.INDUS_ID , FRG.GAMME_ID , FRGO.GAMME_OPERATION_ID, F RP.PDP_ID , FRPS.PDP_SCHEDULE_ID , FRC.CAMPAIGN_ID FROM FDW_DW.FDW_R_DOSSIER FRD INNER JOIN FDW_DW.FDW_R_INDUS FRI ON ( FRD.DOSSIER_ID = FRI.DOSSIER_ID ) INNER JOIN FDW_DW.FDW_R_GAMME FRG ON ( FRG.INDUS_ID = FRI.INDUS_ID ) INNER JOIN FDW_DW 1,404 N/A 7av38g2q94htu 971 N/A 58bafyxdgn8r7 965 N/A f7y0pkb4a8yx3 878 N/A 6jwgqpwkwcab8 731 N/A 2gg8vg5n2263a 487 N/A 340chgpg8x78v 464 N/A c8h20n1d0k95m 463 N/A 3ms7w0c6ph91t 397 N/A 2p9fv35c7zxtg 392 N/A 3dbzmtf9ahvzt 387 N/A 34rznuxy8h2a4 386 N/A aaz3bayav0jwj 382 N/A ajkubg7upg9pu 376 N/A 69k5bhm12sz98 365 N/A 121ffmrc95v7g 362 N/A 38243c4tqrkxm 319 N/A 89f0xd8m24art 298 N/A ch8t72tsys7vx 297 N/A 06gfrprr7w0r2 295 N/A a8ybwb4wf635t 293 N/A fu9xz9pnu520q 286 N/A a1zv6wju3ftgv 275 N/A adds4wma0y8c4 274 N/A 23nad9x295gkf 273 N/A 8p9z2ztb272bm 261 N/A 6wm3n4d7bnddg 259 N/A 50vxqdkj4zu1w SQL ordered by Version Count DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 258 N/A b9nbhsbx8tqz5 252 N/A 7p0q1q6sz5rvu INSERT INTO FDW_STG_OSCARNG_TC.FDW_S_PRODUCT_UNIT (PRODUCT_UNIT_ID,CAMPAIGN_ID,L OCAL_PRODUCT_REFERENCE_ID,CONDITIONING_ID,INTERMEDIATE_PROCESS_ID,PRODUCT_SERIAL _NUMBER,PRODUCT_PRODUCTION_START_DATE,PRODUCT_PRODUCTION_END_DAY,PRODUCT_PRODUCT ION_END_DATE,PRODUCT_RELEASED_DAY,PRODUCT_RELEASED_DATE,SOURCE_APP,JOB_ID,SITE_A 242 N/A chgh9xa2r74bq SELECT DISTINCT SUBSTR(GRANTED_ROLE, 13, 4) AS GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = SYS_CONTEXT('USERENV', 'SESSION_USER') AND GRANTED_ROLE LIKE ( ' R_FDW_RO_D__%' ) 234 N/A 9sg6u8xys290z 224 N/A 8bfgn7a4ff82x 222 N/A 0nakmm882vmq0 222 N/A f705bwx3q0ydq select count(*) from dba_autotask_window_clients c, (select window_name, max(log _date) max_log_date from dba_scheduler_window_log where operation = 'OPEN' group by window_name) wo, (select window_name, max(log_date) max_log_date from dba_sc heduler_window_log where operation = 'CLOSE' group by window_name) wc where c.wi 220 N/A 7z9wmu1t3kawd 216 N/A 865qwpcdyggkk select spare6 from user$ where user#=:1 214 N/A 4bugmu5z8szn5 SELECT /*+ ordered push_pred(v) OPT_PARAM('_parallel_syspls_o bey_force' 'false') */ U.NAME, OT.NAME, DECODE(OT.TYPE#, 34, V.COMPART, OT.SUBNA ME) PART, DECODE(OT.TYPE#, 34, OT.SUBNAME, NULL) SPART, V.COL, V.GLOB_ST, V.USER _ST, V.DIST, V.DENS, V.SDIST, V.SSIZE, V.NCNT, V.TSTAMP, V.CLEN, V.MINVAL, V.MAX 205 N/A bzz4yamw4369m SELECT /*jskqjobqlod2*/ /*+ no_monitor no_statement_queuing current_instanc e */ CON_ID, OBJOID, CLSOID, DECODE(BITAND(FLAGS,16384), 0,RUNTIME,LETIME), (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, decode(INST, :1, -1, 1))), JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM, INST_ID FROM ( select nvl(a.co 204 N/A 9qnpv67wr6fcg SELECT /*jskqjobqlod3*/ /*+ no_monitor no_statement_queuing current_instanc e */ CON_ID, OBJOID, CLSOID, DECODE(BITAND(FLAGS, 16384), 0, RUNTIME, LETIME), (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, decode(INST, :1, -1, 1))), JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM, INST_ID, SRVNAME 201 N/A 3ws7wcfvukrjf SELECT OBJOID, CLSOID, (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, DECODE(INST, :1, -1, 1))), WT, INST, DECODE(B ITAND(STATUS, 8388608), 0, 0, 1), SCHLIM, ISLW, INST_ID FROM ( select a.obj# O BJOID, a.class_oid CLSOID, a.job_status STATUS, a.flags FLAGS, a.priorit 198 568 9zg9qd9bm4spu Module: JDBC Thin Client update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date( NULL), :2) where user#=:1 179 8 3kqrku32p6sfn SQL ordered by Version Count DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_ PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME, ROUND(MAX(S .DELTA_READ_IO_BYTES/S.DELTA_TIME), 3) SCANRATE FROM GV$ACTIVE_SESSION_HISTORY S , GV$SQL_PLAN P, OBJ$ O, USER$ U WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQ 160 5 6kbpur5uatk51 select /*jskqjobqlod1*/ /**/ /*+ no_m onitor no_statement_queuing current_instance */ /**/ CON_ID, OBJOID, CL SOID, RUNTIME, PRI, JOBTYPE, /**/ SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( /**/ select nvl(a.con_id,0) CON_ID, a.ob 90 1,110 87gaftwrm2h68 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1 71 690 091fb1shwqyn8 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.prope rty,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_ob j#=:1 and (d.p_timestamp=nvl(:2,d.p_timestamp) or d.property=2) and o.owne r#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# 71 2 1kb56hd6c73bv select count(*) num_windows, sum(case when jobs_started<>jobs_completed then 1 e lse 0 end) num_jobs_incomplete from (select x.cname_ketcl client_name, su m(case when operation = 'RUN' then 1 else 0 end) as jobs_started, sum(case when operation = 'RUN' and status = 'SUCCEEDED' then 1 else 43 67 fymn7u9vgv747 WITH MONITOR_DATA AS ( SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance' ) AS INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST _REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXE C_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_FULL_PLAN_HASH_VALUE, SE 34 60 gs43xajg8rd5q Module: JDBC Thin Client INSERT INTO FDW_STG_DW_BI.FDW_S_PRODUCT_UNIT_COMPONENT_GENEALOGY (INITIAL_PRODUC T_UNIT_ID,CURRENT_PRODUCT_UNIT_ID,CURRENT_ASSET_OPERATION_ID,COMPONENT_PRODUCT_U NIT_ID,HIERARCHY_LEVEL,INITIAL_LOCAL_PRODUCT_REF_ID,INITIAL_OPERATION_ID,CURRENT _LOCAL_PRODUCT_REF_ID,CURRENT_ASSET_ID,CURRENT_ASSET_SUB_ASSEMBLY_ID,CURRENT_OPE 22 4 a5wm9uvmhfrzr Module: JDBC Thin Client SELECT pu.PRODUCT_UNIT_ID, pu.SITE_ID FROM FDW_DW.FDW_R_PRODUCT_UNIT pu Where pu.SITE_ID = 'SY2' and pu.SITE_ACTIVITY_ID = 'SY2 PL' and TRUNC(pu.CR EATE_DATE) >= TRUNC(sysdate) - '180' ------------------------------------------------------ Key Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- db block changes 19,343,197 2,706.1 14,620.7 execute count 166,655 23.3 126.0 logons cumulative 640 0.1 0.5 opened cursors cumulative 105,089 14.7 79.4 parse count (total) 92,455 12.9 69.9 parse time elapsed 3,139 0.4 2.4 physical reads 90,740,978 12,694.8 68,587.3 physical writes 11,949,747 1,671.8 9,032.3 redo size 5,033,181,908 704,149.3 3,804,370.3 session cursor cache hits 87,997 12.3 66.5 session logical reads 186,998,812 26,161.4 141,344.5 user calls 162,656 22.8 122.9 user commits 1,323 0.2 1.0 user rollbacks 0 0.0 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 14 0.0 0.0 workarea executions - optimal 10,814 1.5 8.2 ------------------------------------------------------ Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- ASSM bg: segment fix monitor 11 0.0 0.0 ASSM cbk:blocks examined 311,351 43.6 235.3 ASSM cbk:blocks marked full 151,201 21.2 114.3 ASSM gsp:L1 bitmaps examined 179,306 25.1 135.5 ASSM gsp:L2 bitmap full 1,453 0.2 1.1 ASSM gsp:L2 bitmaps examined 34,062 4.8 25.8 ASSM gsp:L3 bitmaps examined 1 0.0 0.0 ASSM gsp:Optimized data block re 0 0.0 0.0 ASSM gsp:Optimized reject DB 5 0.0 0.0 ASSM gsp:Optimized reject l1 2 0.0 0.0 ASSM gsp:Optimized reject l2 0 0.0 0.0 ASSM gsp:Search all 2,127 0.3 1.6 ASSM gsp:Search hint 2,974 0.4 2.3 ASSM gsp:Search steal 2,127 0.3 1.6 ASSM gsp:bump HWM 2,127 0.3 1.6 ASSM gsp:get free block 160,135 22.4 121.0 ASSM gsp:get free data block 152,126 21.3 115.0 ASSM gsp:get free index block 8,007 1.1 6.1 ASSM gsp:good hint 158,939 22.2 120.1 ASSM gsp:reject L1 4 0.0 0.0 ASSM gsp:reject L2 0 0.0 0.0 ASSM gsp:reject db 17 0.0 0.0 ASSM rsv:alloc from reserve 0 0.0 0.0 ASSM rsv:alloc from reserve succ 0 0.0 0.0 ASSM rsv:clear reserve 0 0.0 0.0 ASSM rsv:fill reserve 6 0.0 0.0 ASSM wasted db state change 132 0.0 0.1 Batched IO (bound) vector count 1,164 0.2 0.9 Batched IO (full) vector count 0 0.0 0.0 Batched IO (space) vector count 0 0.0 0.0 Batched IO block miss count 4,885 0.7 3.7 Batched IO buffer defrag count 32 0.0 0.0 Batched IO double miss count 12 0.0 0.0 Batched IO same unit count 2,962 0.4 2.2 Batched IO single block count 753 0.1 0.6 Batched IO slow jump count 0 0.0 0.0 Batched IO vector block count 1,301 0.2 1.0 Batched IO vector read count 414 0.1 0.3 Block Cleanout Optim referenced 289 0.0 0.2 CCursor + sql area evicted 1,718 0.2 1.3 CPU used by this session 208,949 29.2 157.9 CPU used when call started 195,610 27.4 147.9 CR blocks created 65,488 9.2 49.5 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DBWR checkpoint buffers written 618,596 86.5 467.6 DBWR checkpoints 818 0.1 0.6 DBWR object drop buffers written 2,840 0.4 2.2 DBWR parallel query checkpoint b 23,919 3.4 18.1 DBWR revisited being-written buf 0 0.0 0.0 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 0 0.0 0.0 DBWR transaction table writes 195 0.0 0.2 DBWR undo block writes 239,381 33.5 180.9 DML statements retried 0 0.0 0.0 Effective IO time 208,911,305 29,227.0 157,907.3 HSC Heap Segment Block Changes 3,691,580 516.5 2,790.3 HSC IDL Compressed Blocks 0 0.0 0.0 Heap Segment Array Deletes 12,332 1.7 9.3 Heap Segment Array Inserts 272,426 38.1 205.9 Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- Heap Segment Array Updates 0 0.0 0.0 Heatmap BlkLevel Flushed 132,160 18.5 99.9 Heatmap BlkLevel Flushed to BF 132,160 18.5 99.9 Heatmap BlkLevel Ranges Flushed 2,346 0.3 1.8 Heatmap Blklevel Flush Task Coun 2 0.0 0.0 IMU CR rollbacks 0 0.0 0.0 IMU Flushes 0 0.0 0.0 IMU Redo allocation size 0 0.0 0.0 IMU commits 0 0.0 0.0 IMU contention 0 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 0 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 0 0.0 0.0 IMU- failed to get a private str 0 0.0 0.0 KTFB alloc req 2,544 0.4 1.9 KTFB alloc space (block) 2,766,471,168 387,033.3 2,091,059.1 KTFB alloc time (ms) 306,760 42.9 231.9 KTFB apply req 646 0.1 0.5 KTFB apply time (ms) 15,402 2.2 11.6 KTFB commit req 2,001 0.3 1.5 KTFB commit time (ms) 102,561 14.4 77.5 KTFB free req 2,231 0.3 1.7 KTFB free space (block) 207,224 29.0 156.6 KTFB free time (ms) 17,958 2.5 13.6 LOB table id lookup cache misses 0 0.0 0.0 Misses for writing mapping 0 0.0 0.0 Number of read IOs issued 617,098 86.3 466.4 Requests to/from client 158,449 22.2 119.8 RowCR - row contention 730 0.1 0.6 RowCR attempts 163,582 22.9 123.6 RowCR hits 120,400 16.8 91.0 SMON posted for undo segment shr 7 0.0 0.0 SQL*Net roundtrips to/from clien 158,614 22.2 119.9 active txn count during cleanout 164,154 23.0 124.1 background checkpoints completed 4 0.0 0.0 background checkpoints started 4 0.0 0.0 background timeouts 61,899 8.7 46.8 blocks cleaned out using minact 15,383 2.2 11.6 branch node splits 64 0.0 0.1 buffer is not pinned count 12,834,213 1,795.5 9,700.8 buffer is pinned count 11,844,199 1,657.0 8,952.5 bytes received via SQL*Net from 935,838,508 130,925.1 707,360.9 bytes sent via SQL*Net to client 3,171,546,451 443,703.9 2,397,238.4 calls to get snapshot scn: kcmgs 282,904 39.6 213.8 calls to kcmgas 115,357 16.1 87.2 calls to kcmgcs 490,120 68.6 370.5 cell physical IO interconnect by 871,258,313,728 121,890,280.0 6.5854748E+08 change write time 3,396 0.5 2.6 cleanout - number of ktugct call 169,716 23.7 128.3 cleanouts and rollbacks - consis 58,547 8.2 44.3 cleanouts only - consistent read 4,546 0.6 3.4 cluster key scan block gets 2,687 0.4 2.0 cluster key scans 2,630 0.4 2.0 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 964 0.1 0.7 commit batch/immediate requested 964 0.1 0.7 commit cleanout failures: block 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit cleanout failures: callba 792 0.1 0.6 commit cleanout failures: cannot 6 0.0 0.0 commit cleanouts 437,020 61.1 330.3 commit cleanouts successfully co 436,222 61.0 329.7 commit immediate performed 964 0.1 0.7 commit immediate requested 964 0.1 0.7 commit txn count during cleanout 13,216 1.9 10.0 consistent changes 400,875 56.1 303.0 consistent gets 163,423,382 22,863.2 123,524.9 consistent gets direct 78,410,842 10,969.8 59,267.5 consistent gets examination 19,450,275 2,721.1 14,701.6 consistent gets examination (fas 17,273,391 2,416.6 13,056.2 consistent gets from cache 85,012,540 11,893.4 64,257.4 consistent gets pin 65,562,265 9,172.3 49,555.8 consistent gets pin (fastpath) 65,439,657 9,155.1 49,463.1 cursor authentications 85 0.0 0.1 cursor reload failures 0 0.0 0.0 data blocks consistent reads - u 1,145,731 160.3 866.0 db block changes 19,343,197 2,706.1 14,620.7 db block gets 23,575,430 3,298.2 17,819.7 db block gets direct 0 0.0 0.0 db block gets from cache 23,575,430 3,298.2 17,819.7 db block gets from cache (fastpa 17,692,023 2,475.1 13,372.7 deferred (CURRENT) block cleanou 199,085 27.9 150.5 dirty buffers inspected 1 0.0 0.0 enqueue conversions 21,336 3.0 16.1 enqueue deadlocks 0 0.0 0.0 enqueue releases 876,082 122.6 662.2 enqueue requests 876,081 122.6 662.2 enqueue timeouts 8 0.0 0.0 enqueue waits 771 0.1 0.6 exchange deadlocks 0 0.0 0.0 execute count 166,655 23.3 126.0 failed probes on index block rec 17 0.0 0.0 fastpath consistent get quota li 0 0.0 0.0 file io service time 49,972,700 6,991.3 37,772.3 free buffer inspected 1,506,908 210.8 1,139.0 free buffer requested 1,854,202 259.4 1,401.5 heap block compress 39,085 5.5 29.5 hot buffers moved to head of LRU 90,471 12.7 68.4 immediate (CR) block cleanout ap 63,093 8.8 47.7 immediate (CURRENT) block cleano 95,863 13.4 72.5 index crx state invalidation 0 0.0 0.0 index crx upgrade (positioned) 0 0.0 0.0 index crx upgrade (prefetch) 0 0.0 0.0 index fast full scans (full) 301 0.0 0.2 index fetch by key 6,159,545 861.7 4,655.7 index range scans 530,427 74.2 400.9 index reclamation/extension swit 0 0.0 0.0 index split cancel wait noclean 0 0.0 0.0 leaf node 90-10 splits 11 0.0 0.0 leaf node splits 7,928 1.1 6.0 lob reads 31,499 4.4 23.8 lob writes 60,950 8.5 46.1 lob writes unaligned 60,942 8.5 46.1 logical read bytes from cache 889,552,650,240 124,449,683.8 6.7237540E+08 logons cumulative 640 0.1 0.5 max cf enq hold time 95 0.0 0.1 messages received 81,550 11.4 61.6 messages sent 81,550 11.4 61.6 Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- min active SCN optimization appl 39,514 5.5 29.9 no buffer to keep pinned count 9 0.0 0.0 no work - consistent read gets 143,535,662 20,080.8 108,492.6 non-idle wait count 1,511,345 211.4 1,142.4 opened cursors cumulative 105,089 14.7 79.4 parse count (describe) 0 0.0 0.0 parse count (failures) 0 0.0 0.0 parse count (hard) 1,724 0.2 1.3 parse count (total) 92,455 12.9 69.9 parse time cpu 2,618 0.4 2.0 parse time elapsed 3,139 0.4 2.4 physical read IO requests 1,041,612 145.7 787.3 physical read bytes 743,350,091,776 103,995,737.5 5.6186704E+08 physical read total IO requests 1,080,924 151.2 817.0 physical read total bytes 754,774,450,176 105,594,021.5 5.7050223E+08 physical read total multi block 992,278 138.8 750.0 physical reads 90,740,978 12,694.8 68,587.3 physical reads cache 1,321,668 184.9 999.0 physical reads cache prefetch 1,253,624 175.4 947.6 physical reads direct 89,419,310 12,509.9 67,588.3 physical reads direct (lob) 0 0.0 0.0 physical reads direct temporary 11,008,596 1,540.1 8,320.9 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 490,313 68.6 370.6 physical write bytes 97,892,327,424 13,695,276.2 73,992,688.9 physical write total IO requests 557,140 77.9 421.1 physical write total bytes 116,483,863,552 16,296,258.6 88,045,248.3 physical write total multi block 409,730 57.3 309.7 physical writes 11,949,747 1,671.8 9,032.3 physical writes direct 11,327,461 1,584.7 8,562.0 physical writes direct (lob) 117 0.0 0.1 physical writes direct temporary 11,327,028 1,584.7 8,561.6 physical writes from cache 622,286 87.1 470.4 physical writes non checkpoint 11,757,867 1,644.9 8,887.3 pinned buffers inspected 482 0.1 0.4 pinned cursors current 1 0.0 0.0 prefetch clients - default 0 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetch warmup blocks flushed o 0 0.0 0.0 prefetched blocks aged out befor 0 0.0 0.0 process last non-idle time 7,149 1.0 5.4 recursive aborts on index block 0 0.0 0.0 recursive calls 477,290 66.8 360.8 recursive cpu usage 8,236 1.2 6.2 redo KB read 7,046,079 985.8 5,325.8 redo blocks checksummed by FG (e 3,561,963 498.3 2,692.3 redo blocks written 10,166,980 1,422.4 7,684.8 redo blocks written (group 0) 10,145,162 1,419.3 7,668.3 redo blocks written (group 1) 19,632 2.8 14.8 redo buffer allocation retries 2 0.0 0.0 redo entries 9,856,227 1,378.9 7,449.9 redo log space requests 3 0.0 0.0 redo ordering marks 7,740 1.1 5.9 redo size 5,033,181,908 704,149.3 3,804,370.3 redo size for direct writes 0 0.0 0.0 redo subscn max counts 1,514 0.2 1.1 redo synch long waits 1 0.0 0.0 redo synch time 70 0.0 0.1 redo synch time (usec) 921,403 128.9 696.5 redo synch time overhead (usec) 41,808 5.9 31.6 Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- redo synch time overhead count ( 692 0.1 0.5 redo synch time overhead count ( 0 0.0 0.0 redo synch time overhead count ( 0 0.0 0.0 redo synch time overhead count ( 0 0.0 0.0 redo synch writes 1,527 0.2 1.2 redo wastage 5,346,212 747.9 4,041.0 redo write active strands 18,639 2.6 14.1 redo write finish time 28,857,325 4,037.2 21,812.0 redo write gather time 2,700,545 377.8 2,041.2 redo write info find 692 0.1 0.5 redo write schedule time 2,793,772 390.9 2,111.7 redo write size count ( 4KB) 13,994 2.0 10.6 redo write size count ( 8KB) 799 0.1 0.6 redo write size count ( 16KB) 225 0.0 0.2 redo write size count ( 32KB) 183 0.0 0.1 redo write size count ( 128KB) 210 0.0 0.2 redo write size count ( 256KB) 83 0.0 0.1 redo write size count ( 512KB) 52 0.0 0.0 redo write size count (1024KB) 92 0.0 0.1 redo write size count (inf) 2,675 0.4 2.0 redo write time 2,958 0.4 2.2 redo write time (usec) 29,590,625 4,139.8 22,366.3 redo write total time 37,496,385 5,245.8 28,341.9 redo write worker delay (usec) 2,087,692 292.1 1,578.0 redo write worker delay count 18,311 2.6 13.8 redo writes 18,313 2.6 13.8 redo writes (group 0) 15,791 2.2 11.9 redo writes (group 1) 2,520 0.4 1.9 redo writes adaptive all 18,313 2.6 13.8 redo writes adaptive worker 18,311 2.6 13.8 remote Oradebug requests 2 0.0 0.0 rollback changes - undo records 24 0.0 0.0 rollbacks only - consistent read 50,367 7.1 38.1 root node splits 0 0.0 0.0 rows fetched via callback 5,825,047 814.9 4,402.9 securefile allocation bytes 958,464 134.1 724.5 securefile allocation chunks 72 0.0 0.1 securefile bytes non-transformed 889,245 124.4 672.1 securefile direct read bytes 0 0.0 0.0 securefile direct read ops 0 0.0 0.0 securefile direct write bytes 958,464 134.1 724.5 securefile direct write ops 72 0.0 0.1 securefile number of non-transfo 623 0.1 0.5 segment cfs allocations 0 0.0 0.0 segment chunks allocation from d 72 0.0 0.1 segment dispenser allocations 69 0.0 0.1 segment dispenser load empty 0 0.0 0.0 segment dispenser load tasks 1 0.0 0.0 segment prealloc bytes 1,048,576 146.7 792.6 segment prealloc ops 28 0.0 0.0 segment prealloc tasks 1 0.0 0.0 segment prealloc time (ms) 94 0.0 0.1 segment prealloc ufs2cfs bytes 0 0.0 0.0 segment total chunk allocation 0 0.0 0.0 session connect time 0 0.0 0.0 session cursor cache hits 87,997 12.3 66.5 session logical reads 186,998,812 26,161.4 141,344.5 shared hash latch upgrades - no 154,633 21.6 116.9 shared hash latch upgrades - wai 0 0.0 0.0 shared io pool buffer get succes 69 0.0 0.1 Instance Activity Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- sorts (disk) 0 0.0 0.0 sorts (memory) 8,648 1.2 6.5 sorts (rows) 1,723,132 241.1 1,302.4 sql area evicted 378 0.1 0.3 sql area purged 355 0.1 0.3 summed dirty queue length 1 0.0 0.0 switch current caused by our pin 87,423 12.2 66.1 switch current to new buffer 87,425 12.2 66.1 table fetch by rowid 7,295,372 1,020.6 5,514.3 table fetch continued row 7,146 1.0 5.4 table scan blocks gotten 113,645,859 15,899.2 85,900.1 table scan disk non-IMC rows got 3,676,181,422 514,303.0 2,778,670.8 table scan rows gotten 3,676,181,422 514,303.0 2,778,670.8 table scans (direct read) 44 0.0 0.0 table scans (long tables) 44 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 9,773 1.4 7.4 total cf enq hold time 2,385 0.3 1.8 total number of cf enq holders 449 0.1 0.3 total number of times SMON poste 7 0.0 0.0 transaction rollbacks 964 0.1 0.7 transaction tables consistent re 6 0.0 0.0 transaction tables consistent re 341 0.1 0.3 undo change vector size 1,812,542,636 253,577.3 1,370,024.7 user calls 162,656 22.8 122.9 user commits 1,323 0.2 1.0 user logons cumulative 598 0.1 0.5 user logouts cumulative 603 0.1 0.5 user rollbacks 0 0.0 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 14 0.0 0.0 workarea executions - optimal 10,814 1.5 8.2 write clones created in backgrou 849 0.1 0.6 write clones created in foregrou 0 0.0 0.0 ------------------------------------------------------ Instance Activity Stats - Absolute ValuesDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- logons current 73 68 opened cursors current 143 129 session cursor cache count 16,723,298 16,731,038 session pga memory 555,286,240 542,083,608 session pga memory max 4,106,226,016 4,068,119,704 session uga memory 1.548097806E+11 1.548775251E+11 session uga memory max 1.535189205E+13 1.536084193E+13 ------------------------------------------------------ Instance Activity Stats - Thread ActivityDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 4 2.01 ------------------------------------------------------ IOStat by Function summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> ordered by (Data Read + Write) desc Reads: Reqs Data Writes: Reqs Data Waits: Avg Function Name Data per sec per sec Data per sec per sec Count Time --------------- ------- ------- ------- ------- ------- ------- ------- -------- Direct Reads 632.5G 128.8 90.606M 44.2G 26.1 6.325M 0 Direct Writes 49.8G 7.5 7.131M 42.3G 25.1 6.055M 1819 180.12us Others 7.2G 4.7 1.033M 6.9G 2.7 .991M 30.4K 348.36us Buffer Cache Re 10.1G 9.6 1.445M 0M 0.0 0M 68.1K 811.00us LGWR 1M 0.0 0M 9.7G 6.5 1.39M 18.4K 1.40ms DBWR 0M 0.0 0M 4.7G 17.4 .68M 0 RMAN 3.4G 0.6 .487M 719M 0.1 .101M 686 588.92us TOTAL: 702.9G 151.2 100.702 108.5G 77.9 15.542M 119.4K 773.81us ------------------------------------------------------ IOStat by Filetype summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> Small Read and Large Read are average service times -> Ordered by (Data Read + Write) desc Reads: Reqs Data Writes: Reqs Data Small Large Filetype Name Data per sec per sec Data per sec per sec Read Read -------------- ------- ------- ------- ------- ------- ------- -------- -------- Data File 608.3G 96.1 87.146M 4.8G 17.5 .681M 70.31us 2.07ms Temp File 84G 49.7 12.032M 86.4G 51.1 12.38M 943.56us Log File 6.7G 1.0 .963M 9.7G 6.5 1.389M .00ns 3.39ms Archive Log 3.3G 0.5 .479M 6.7G 1.0 .963M 800.00us 71.79ms Control File 587M 4.0 .082M 198M 1.6 .028M 7.11us 729.37us Archive Log Ba 0M 0.0 0M 688M 0.1 .096M Other 0M 0.0 0M 35M 0.1 .005M 8.40us Data File Back 0M 0.0 0M 0M 0.0 0M .00ns TOTAL: 702.9G 151.2 100.702 108.5G 77.9 15.541M 50.22us 1.92ms ------------------------------------------------------ IOStat by Function/Filetype summaryDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> Ordered by (Data Read + Write) desc for each function Reads: Reqs Data Writes: Reqs Data Waits: Avg Data per sec per sec Data per sec per sec Count Time ------- ------- ------- ------- ------- ------- ------- ---------- Direct Reads 632.5G 128.8 90.606M 44.2G 26.1 6.325M 0 Direct Reads (Data File) 548.8G 79.3 78.62M 0M 0.0 0M 0 Direct Reads (Temp File) 83.7G 49.5 11.986M 44.2G 26.1 6.325M 0 Direct Writes 49.8G 7.5 7.131M 42.3G 25.1 6.055M 1819 180.12us Direct Writes (Data File) 49.4G 7.1 7.081M 3M 0.1 0M 0 Direct Writes (Temp File) 329M 0.2 .046M 42.3G 25.0 6.055M 0 Direct Writes (Control File) 29M 0.3 .004M 0M 0.0 0M 1819 180.12us Others 7.2G 4.7 1.033M 6.9G 2.6 .988M 26.4K 209.91us Others (Archive Log) 0M 0.0 0M 6.7G 1.0 .963M 0 Others (Log File) 6.7G 1.0 .963M 0M 0.0 0M 36 138.89us Others (Control File) 502M 3.6 .07M 178M 1.6 .025M 26K 210.50us Others (Data File) 3M 0.1 0M 0M 0.0 0M 395 177.69us Buffer Cache Reads (Data File) 10.1G 9.6 1.445M 0M 0.0 0M 67.6K 814.33us Buffer Cache Reads 10.1G 9.6 1.445M 0M 0.0 0M 67.6K 814.33us LGWR 1M 0.0 0M 9.7G 6.5 1.389M 88 181.82us LGWR (Log File) 0M 0.0 0M 9.7G 6.5 1.389M 32 156.25us LGWR (Control File) 1M 0.0 0M 2M 0.0 0M 56 196.43us DBWR (Data File) 0M 0.0 0M 4.7G 17.4 .68M 0 DBWR 0M 0.0 0M 4.7G 17.4 .68M 0 RMAN 3.4G 0.6 .487M 705M 0.1 .099M 609 382.59us RMAN (Archive Log) 3.3G 0.5 .479M 0M 0.0 0M 9 111.11us RMAN (Archive Log Backup) 0M 0.0 0M 688M 0.1 .096M 0 RMAN (Control File) 55M 0.1 .008M 17M 0.0 .002M 598 387.96us RMAN (Data File Backup) 0M 0.0 0M 0M 0.0 0M 2 .00ns TOTAL: 702.9G 151.2 100.702 108.4G 77.8 15.536M 96.5K 633.84us ------------------------------------------------------ Tablespace IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Av Buffer Av Buf Writes avg/s Writes(ms) Waits Wt(ms) ----------- ----------- ---------- ---------- ---------- TMP01 355,116 50 1.5 31.0 0 0.0 N/A 365,388 51 21.2 0 0.0 D_FDW01 627,767 88 2.5 124.9 1 0.3 .0 69,620 10 2.7 0 0.0 I_FDW01 57,147 8 0.9 22.9 7 0.5 .0 34,845 5 1.1 0 0.0 UNDO 941 0 0.4 1.0 0 0.4 .0 16,438 2 1.3 3 0.0 SYSAUX 437 0 0.3 1.0 0 0.3 .0 2,383 0 1.5 0 0.0 SYSTEM 172 0 0.5 1.0 0 0.4 .0 1,601 0 2.4 6 0.0 D_I_DW_BUSINESS 20 0 1.5 1.0 0 2.0 .0 20 0 1.0 0 0.0 AUDIT_TRAIL_TS 8 0 1.3 1.0 0 1.3 .0 6 0 0.0 0 0.0 D_FDW_DOC01 4 0 2.5 1.0 0 2.5 .0 4 0 0.0 0 0.0 I_FDW_DOC01 4 0 2.5 1.0 0 0.0 .0 4 0 0.0 0 0.0 USERS 4 0 0.0 1.0 0 2.5 .0 4 0 2.5 0 0.0 ------------------------------------------------------ File IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Buffer Av Buf Writes avg/s Waits Wt(ms) ----------- ----------- ---------- ---------- AUDIT_TRAIL_TS /busdata/rfdw/sy20/oracle/audit/AUDIT_TRAIL_TS_01.DB 8 0 1.3 1.0 0 1.3 .0 6 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_01.DBF 60,219 8 2.2 126.8 0 0.3 .0 3,787 1 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_02.DBF 47,851 7 2.4 127.3 0 0.3 .0 2,013 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_03.DBF 58,230 8 2.3 126.6 0 0.7 .0 1,420 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_04.DBF 47,262 7 2.4 127.5 0 1.7 .0 1,275 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_05.DBF 55,222 8 2.3 123.8 0 0.4 .0 1,893 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_06.DBF 50,585 7 2.3 126.9 0 0.4 .0 3,206 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_07.DBF 50,892 7 2.3 126.1 0 0.5 .0 2,725 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_08.DBF 59,000 8 2.6 127.9 0 0.0 .0 1,823 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_09.DBF 35,770 5 2.9 123.9 0 0.3 .0 6,462 1 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_10.DBF 36,268 5 2.8 123.5 0 0.3 .0 5,378 1 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_11.DBF 1,642 0 4.1 126.3 0 0.5 .0 2,229 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_12.DBF 3,880 1 3.0 127.0 0 0.4 .0 1,694 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_13.DBF 3,766 1 3.0 126.9 0 0.4 .0 1,830 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_14.DBF 1,608 0 3.3 125.8 0 0.4 .0 670 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_15.DBF 7,277 1 1.8 74.7 0 0.2 .0 1,593 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_16.DBF 7,415 1 2.5 102.5 0 0.1 .0 4,773 1 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_17.DBF 6,694 1 3.0 122.7 0 0.5 .0 3,027 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_18.DBF 3,797 1 3.4 126.9 0 0.7 .0 1,637 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_19.DBF 5,129 1 3.0 108.5 0 0.4 .0 1,885 0 0 0.0 File IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Buffer Av Buf Writes avg/s Waits Wt(ms) ----------- ----------- ---------- ---------- D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_20.DBF 7,512 1 2.4 113.7 0 0.4 .0 3,064 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_21.DBF 6,519 1 3.7 125.9 0 0.4 .0 1,683 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_22.DBF 6,918 1 2.6 123.5 0 0.3 .0 1,215 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_23.DBF 4,364 1 3.0 125.1 0 0.3 .0 1,250 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_24.DBF 6,816 1 2.8 125.0 0 0.3 .0 1,334 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_25.DBF 7,006 1 2.7 124.2 0 0.3 .0 1,496 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_26.DBF 6,674 1 2.9 127.7 0 1.0 .0 1,202 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_27.DBF 6,672 1 2.9 127.7 0 1.0 .0 1,148 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_28.DBF 6,700 1 2.7 124.8 0 0.4 .0 906 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_29.DBF 6,631 1 2.7 126.2 0 0.3 .0 943 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_30.DBF 6,494 1 2.6 123.9 0 0.3 .0 1,054 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_31.DBF 6,485 1 2.7 126.8 0 0.4 .0 3,209 0 0 0.0 D_FDW01 /busdata/rfdw/sy20/oracle/data1/D_FDW01_32.DBF 6,469 1 2.8 127.5 0 1.1 .0 1,796 0 0 0.0 D_FDW_DOC01 /busdata/rfdw/sy20/oracle/data1/D_FDW_DOC01_01.DBF 4 0 2.5 1.0 0 2.5 .0 4 0 0 0.0 D_I_DW_BUSINESS /busdata/rfdw/sy20/oracle/data2/d_i_DW_FDW_01.DBF 4 0 2.5 1.0 0 2.5 .0 4 0 0 0.0 D_I_DW_BUSINESS /busdata/rfdw/sy20/oracle/data2/d_i_DW_FDW_02.DBF 4 0 2.5 1.0 0 2.5 .0 4 0 0 0.0 D_I_DW_BUSINESS /busdata/rfdw/sy20/oracle/data2/d_i_DW_FDW_03.DBF 4 0 0.0 1.0 0 2.5 .0 4 0 0 0.0 D_I_DW_BUSINESS /busdata/rfdw/sy20/oracle/data2/d_i_DW_FDW_04.DBF 4 0 0.0 1.0 0 2.5 .0 4 0 0 0.0 D_I_DW_BUSINESS /busdata/rfdw/sy20/oracle/data2/d_i_DW_FDW_05.DBF 4 0 2.5 1.0 0 0.0 .0 4 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_01.DBF 1,343 0 1.0 28.4 0 0.4 .0 678 0 0 0.0 File IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Buffer Av Buf Writes avg/s Waits Wt(ms) ----------- ----------- ---------- ---------- I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_02.DBF 1,436 0 0.9 26.6 0 0.5 .0 1,879 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_03.DBF 938 0 1.1 40.2 0 0.5 .0 855 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_04.DBF 1,447 0 0.9 26.4 0 0.4 .0 670 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_05.DBF 2,541 0 0.7 15.5 0 0.4 .0 1,419 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_06.DBF 3,399 0 0.6 11.8 0 0.4 .0 2,196 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_07.DBF 2,850 0 0.7 15.3 0 0.4 .0 1,546 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_08.DBF 1,761 0 0.9 26.1 0 0.5 .0 707 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_09.DBF 2,452 0 0.9 19.2 0 0.6 .0 965 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_10.DBF 2,099 0 0.8 21.9 0 0.4 .0 946 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_11.DBF 1,725 0 0.9 26.4 0 0.5 .0 770 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_12.DBF 2,061 0 0.9 22.3 0 0.5 .0 691 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_13.DBF 2,922 0 0.9 16.0 0 0.5 .0 1,217 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_14.DBF 2,595 0 1.0 17.9 0 0.5 .0 1,132 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_15.DBF 2,212 0 1.0 20.8 0 0.4 .0 937 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_16.DBF 1,756 0 1.2 26.0 0 0.4 .0 926 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_17.DBF 1,224 0 1.3 36.8 0 0.4 .0 519 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_18.DBF 1,120 0 1.1 33.8 0 0.4 .0 675 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_19.DBF 677 0 1.5 55.3 0 0.4 .0 2,053 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_20.DBF 1,277 0 0.9 29.8 0 0.4 .0 1,541 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_21.DBF 1,698 0 0.8 22.7 0 0.4 .0 1,462 0 0 0.0 File IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Buffer Av Buf Writes avg/s Waits Wt(ms) ----------- ----------- ---------- ---------- I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_22.DBF 2,065 0 0.9 18.8 0 0.5 .0 1,735 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_23.DBF 1,984 0 0.9 19.5 0 0.5 .0 1,895 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_24.DBF 1,017 0 1.2 37.1 0 0.5 .0 1,564 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_25.DBF 1,072 0 1.0 35.3 0 0.4 .0 645 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_26.DBF 543 0 1.8 68.7 0 0.6 .0 365 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_27.DBF 1,706 0 1.0 22.6 0 0.5 .0 871 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_28.DBF 1,489 0 0.9 25.7 0 0.5 .0 531 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_29.DBF 1,702 0 0.8 22.6 0 0.4 .0 670 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_30.DBF 2,646 0 0.7 14.9 0 0.4 .0 1,081 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_31.DBF 1,821 0 0.8 21.2 0 0.5 .0 1,028 0 0 0.0 I_FDW01 /busdata/rfdw/sy20/oracle/index1/I_FDW01_32.DBF 1,569 0 1.0 24.4 0 0.6 .0 676 0 0 0.0 I_FDW_DOC01 /busdata/rfdw/sy20/oracle/index1/I_FDW_DOC01_01.DBF 4 0 2.5 1.0 0 0.0 .0 4 0 0 0.0 SYSAUX /busdata/rfdw/sy20/oracle/data1/sysaux01.dbf 437 0 0.3 1.0 0 0.3 .0 2,383 0 0 0.0 SYSTEM /busdata/rfdw/sy20/oracle/data1/system01.dbf 172 0 0.5 1.0 0 0.4 .0 1,601 0 6 0.0 TMP01 /busdata/rfdw/sy20/oracle/tmp/tmp01_01.dbf 92,426 13 1.3 31.0 0 N/A N/A 97,562 14 0 N/A TMP01 /busdata/rfdw/sy20/oracle/tmp/tmp01_02.dbf 33,045 5 1.3 31.0 0 N/A N/A 35,613 5 0 N/A TMP01 /busdata/rfdw/sy20/oracle/tmp/tmp01_04.dbf 229,645 32 1.6 31.0 0 N/A N/A 232,213 32 0 N/A UNDO /busdata/rfdw/sy20/oracle/undo/undo01.dbf 895 0 0.4 1.0 0 0.4 .0 4,115 1 3 0.0 UNDO /busdata/rfdw/sy20/oracle/undo/undo02.dbf 5 0 2.0 1.0 0 2.0 .0 3,867 1 0 0.0 UNDO /busdata/rfdw/sy20/oracle/undo/undo03.dbf 8 0 1.3 1.0 0 1.3 .0 3,853 1 0 0.0 File IO Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av 1-bk Av 1-bk % Opt Reads Rds/s Rd(ms) Blks/Rd Rds/s Rd(ms) Reads ----------- ----------- ---------- ---------- ----------- ---------- ------ Writes Buffer Av Buf Writes avg/s Waits Wt(ms) ----------- ----------- ---------- ---------- UNDO /busdata/rfdw/sy20/oracle/undo/undo04.dbf 33 0 0.6 1.0 0 0.6 .0 4,603 1 0 0.0 USERS /busdata/rfdw/sy20/oracle/data1/users01.dbf 4 0 0.0 1.0 0 2.5 .0 4 0 0 0.0 ------------------------------------------------------ Buffer Pool Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 1.0034E+07 99 108,583,450 1,321,661 622,286 0 0 9 ------------------------------------------------------ Checkpoint Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Physical Writes: 11,949,747 Other Autotune Thread MTTR Log Size Log Ckpt Settings Ckpt Ckpt Writes Writes Writes Writes Writes Writes ----------- ----------- ----------- ----------- ----------- ----------- 0 0 0 0 594,677 0 ------------------------------------------------------ Instance Recovery Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> B: Begin Snapshot, E: End Snapshot Estd Targt Estd Log Ckpt Log Ckpt Opt RAC MTTR MTTR Recovery Actual Target Log Sz Timeout Interval Log Avail (s) (s) Estd IOs RedoBlks RedoBlks RedoBlks RedoBlks RedoBlks Sz(M) Time - ----- ----- -------- -------- -------- -------- -------- -------- ------ ----- B 0 14 4340 62409 742023 2.72E+07 742023 N/A N/A N/A E 0 14 860 2094 960080 2.72E+07 960080 N/A N/A N/A ------------------------------------------------------ MTTR Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 No data exists for this section of the report. ------------------------------------------------------ Buffer Pool Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys %DBtime P Est (M) Factor (thousands) Factor (thousands) Read Time for Rds --- -------- ------ ------------ ------ -------------- ------------ ------- D 8,192 .1 1,003 3.6 186,078,611 1 5.6E+07 D 16,384 .2 2,007 2.4 123,551,341 1 3.1E+07 D 24,576 .3 3,010 1.7 91,327,771 1 1.8E+07 D 32,768 .4 4,014 1.5 77,688,189 1 1.2E+07 D 40,960 .5 5,017 1.4 70,767,951 1 9.1E+06 D 49,152 .6 6,021 1.2 65,258,715 1 6.8E+06 D 57,344 .7 7,024 1.2 60,356,327 1 4.8E+06 D 65,536 .8 8,027 1.1 56,957,035 1 3.4E+06 D 73,728 .9 9,031 1.0 54,869,554 1 2.6E+06 D 81,920 1.0 10,034 1.0 52,417,672 1 1.6E+06 D 82,176 1.0 10,066 1.0 52,345,002 1 1.5E+06 D 90,112 1.1 11,038 1.0 50,088,199 1 1.1E+06 D 98,304 1.2 12,041 0.9 46,973,918 1 1.1E+06 D 106,496 1.3 13,045 0.8 44,078,378 1 1.1E+06 D 114,688 1.4 14,048 0.8 41,626,146 1 1.1E+06 D 122,880 1.5 15,051 0.7 39,182,803 1 1.1E+06 D 131,072 1.6 16,055 0.7 35,917,769 1 1.1E+06 D 139,264 1.7 17,058 0.6 31,848,333 1 1.1E+06 D 147,456 1.8 18,062 0.5 27,132,129 1 1.1E+06 D 155,648 1.9 19,065 0.4 21,844,221 1 1.1E+06 D 163,840 2.0 20,068 0.3 17,746,438 1 1.1E+06 ------------------------------------------------------ PGA Aggr Summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 --------------- ------------------ -------------------------- 56.0 41,307 32,470 ------------------------------------------------------ PGA Aggr Target Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 18,432 16,140 3,138.3 0.0 .0 .0 .0 1,048,576 E 18,432 16,149 3,122.8 0.0 .0 .0 .0 1,048,576 ------------------------------------------------------ PGA Aggr Target Histogram DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 7,053 7,053 0 0 64K 128K 62 62 0 0 128K 256K 211 211 0 0 256K 512K 110 110 0 0 512K 1024K 1,404 1,404 0 0 1M 2M 1,375 1,375 0 0 2M 4M 354 354 0 0 4M 8M 105 105 0 0 8M 16M 46 46 0 0 16M 32M 52 52 0 0 32M 64M 30 30 0 0 64M 128M 10 10 0 0 128M 256M 2 2 0 0 1G 2G 6 0 6 0 2G 4G 8 0 8 0 ------------------------------------------------------ PGA Memory Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd P Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overallo Estd Est (MB) Factr Processed Written to Disk Hit % Count Time ---------- ------- ---------------- ---------------- ------ -------- ------- 2,304 0.1 105,923,150.6 30,794,298.5 77.0 11 9.9E+09 4,608 0.3 105,923,150.6 28,003,222.5 79.0 3 9.7E+09 9,216 0.5 105,923,150.6 27,483,178.2 79.0 0 9.6E+09 13,824 0.8 105,923,150.6 27,477,624.1 79.0 0 9.6E+09 18,432 1.0 105,923,150.6 27,048,056.6 80.0 0 9.6E+09 22,118 1.2 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 25,805 1.4 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 29,491 1.6 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 33,178 1.8 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 36,864 2.0 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 55,296 3.0 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 73,728 4.0 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 110,592 6.0 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 147,456 8.0 105,923,150.6 26,696,924.6 80.0 0 9.6E+09 ------------------------------------------------------ Shared Pool Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 -> 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 Obj Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Hits (K) -------- ----- -------- ------------ -------- ------ ------- ------ ------------ 6,656 .5 811 54,742 8.78E+08 .3 2.2E+09 1.E+04 803,638 8,192 .6 2,285 88,144 1.43E+09 .5 1.7E+09 9.E+03 169,428 9,728 .7 3,821 126,212 2.01E+09 .6 1.1E+09 6.E+03 1,878,521 11,264 .8 5,357 149,852 2.57E+09 .8 5.5E+08 3.E+03 3,250,200 11,776 .8 5,869 158,299 2.73E+09 .9 3.8E+08 2.E+03 513,483 12,032 .8 6,124 162,603 2.80E+09 .9 3.1E+08 2.E+03 1,217,243 12,288 .9 6,380 168,569 2.88E+09 .9 2.4E+08 1.E+03 1,912,088 12,544 .9 6,636 174,177 2.95E+09 .9 1.6E+08 868.7 2,596,622 12,800 .9 6,892 179,457 3.02E+09 1.0 9.2E+07 491.1 3,275,239 13,056 .9 7,147 186,954 3.06E+09 1.0 5.4E+07 286.0 3,665,791 13,312 .9 7,403 195,462 3.10E+09 1.0 1.7E+07 88.6 4,043,002 13,568 .9 7,659 203,970 3.10E+09 1.0 9.7E+06 51.6 4,111,281 13,824 1.0 7,915 212,478 3.11E+09 1.0 4.2E+06 22.2 4,165,365 14,080 1.0 8,171 220,986 3.11E+09 1.0 1.5E+06 7.8 4,193,387 14,336 1.0 8,427 229,494 3.11E+09 1.0 1.9E+05 1.0 4,207,581 14,592 1.0 8,683 238,002 3.11E+09 1.0 1.9E+05 1.0 4,207,582 14,848 1.0 8,939 246,510 3.11E+09 1.0 1.9E+05 1.0 4,207,583 15,104 1.1 9,195 255,018 3.11E+09 1.0 1.9E+05 1.0 4,207,585 15,360 1.1 9,451 263,526 3.11E+09 1.0 1.9E+05 1.0 4,207,586 15,616 1.1 9,707 272,034 3.11E+09 1.0 1.9E+05 1.0 4,207,587 15,872 1.1 9,963 280,542 3.11E+09 1.0 1.9E+05 1.0 4,207,588 16,128 1.1 10,219 289,050 3.11E+09 1.0 1.9E+05 1.0 4,207,589 16,384 1.1 10,475 297,558 3.11E+09 1.0 1.9E+05 1.0 4,207,590 16,640 1.2 10,731 306,066 3.11E+09 1.0 1.9E+05 1.0 4,207,592 17,408 1.2 11,499 331,591 3.11E+09 1.0 1.9E+05 1.0 4,207,595 18,944 1.3 13,035 382,641 3.11E+09 1.0 1.9E+05 1.0 4,207,600 20,480 1.4 14,571 433,691 3.11E+09 1.0 1.9E+05 1.0 4,207,605 22,016 1.5 16,107 484,741 3.11E+09 1.0 1.9E+05 1.0 4,207,611 23,552 1.6 17,643 535,791 3.11E+09 1.0 1.9E+05 1.0 4,207,617 25,088 1.8 19,179 586,841 3.11E+09 1.0 1.9E+05 1.0 4,207,621 26,624 1.9 20,715 637,891 3.11E+09 1.0 1.9E+05 1.0 4,207,622 28,160 2.0 22,251 688,941 3.11E+09 1.0 1.9E+05 1.0 4,207,623 29,696 2.1 23,787 739,991 3.11E+09 1.0 1.9E+05 1.0 4,207,625 ------------------------------------------------------ SGA Target Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 18,432 0.2 1.749805E+09 186,076,014,631 24,576 0.3 64,294,231 186,076,014,631 30,720 0.3 48,152,745 123,549,909,231 36,864 0.4 38,644,655 123,549,909,231 43,008 0.4 25,425,978 91,326,325,736 49,152 0.5 19,830,376 77,690,452,604 55,296 0.6 16,992,478 70,770,443,283 61,440 0.6 16,991,535 70,770,443,283 67,584 0.7 14,731,972 65,258,514,527 73,728 0.8 12,720,536 60,353,787,799 79,872 0.8 11,326,117 56,956,597,142 86,016 0.9 10,470,408 54,868,031,545 92,160 0.9 10,470,408 54,868,031,545 98,304 1.0 9,434,500 52,345,002,428 104,448 1.1 9,016,552 50,088,932,823 110,592 1.1 9,016,552 50,088,932,823 116,736 1.2 9,016,552 46,974,405,179 122,880 1.3 9,015,608 46,974,405,179 129,024 1.3 9,015,608 44,079,726,545 135,168 1.4 9,015,608 41,624,745,931 141,312 1.4 9,015,608 39,180,234,317 147,456 1.5 9,015,608 35,919,140,666 153,600 1.6 9,014,665 31,846,699,477 159,744 1.6 9,014,665 27,130,414,758 165,888 1.7 9,014,665 21,843,569,513 172,032 1.8 9,013,721 21,843,569,513 178,176 1.8 9,013,721 17,744,955,823 184,320 1.9 9,013,721 17,744,955,823 190,464 1.9 9,013,721 17,744,955,823 196,608 2.0 9,013,721 17,744,955,823 ------------------------------------------------------ Streams Pool Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 256 0.5 0 0 0 0 512 1.0 0 0 0 0 768 1.5 0 0 0 0 1,024 2.0 0 0 0 0 1,280 2.5 0 0 0 0 1,536 3.0 0 0 0 0 1,792 3.5 0 0 0 0 2,048 4.0 0 0 0 0 2,304 4.5 0 0 0 0 2,560 5.0 0 0 0 0 2,816 5.5 0 0 0 0 3,072 6.0 0 0 0 0 3,328 6.5 0 0 0 0 3,584 7.0 0 0 0 0 3,840 7.5 0 0 0 0 4,096 8.0 0 0 0 0 4,352 8.5 0 0 0 0 4,608 9.0 0 0 0 0 4,864 9.5 0 0 0 0 5,120 10.0 0 0 0 0 ------------------------------------------------------ Java Pool Advisory DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 No data exists for this section of the report. ------------------------------------------------------ Buffer Wait Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 6 0 0 undo header 3 0 0 ------------------------------------------------------ Enqueue Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> only enqueues with requests are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc, Requests desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 5,660 5,660 0 558 1 1.67 KO-Multiple Object Checkpoint (fast object checkpoint) 180 180 0 18 1 35.44 TX-Transaction (row lock contention) 1 1 0 1 0 414.00 CF-Controlfile Transaction 6,940 6,940 0 3 0 17.33 CR-Reuse Block Range (block range reuse ckpt) 2,300 2,300 0 190 0 .08 HW-Segment High Water Mark 10,380 10,380 0 1 0 .00 JG-Job Scheduler1 341,576 341,576 0 0 0 N/A JG-Job Scheduler1 (queue lock) 341,576 341,576 0 0 0 N/A TT-Tablespace 101,376 101,376 0 0 0 N/A JG-Job Scheduler1 (q mem clnup lck) 32,528 32,528 0 0 0 N/A TX-Transaction 21,636 21,634 0 0 0 N/A FB-Format Block 9,816 9,816 0 0 0 N/A TM-DML 9,327 9,327 0 0 0 N/A SJ-KTSJ Slave Task Cancel (Slave Task Cancel) 5,951 5,950 0 0 0 N/A MR-Media Recovery 2,686 2,686 0 0 0 N/A CU-Cursor 1,367 1,367 0 0 0 N/A IS-Instance State 848 848 0 0 0 N/A SE-Session Migration 663 663 0 0 0 N/A AE-Edition Lock (lock) 633 633 0 0 0 N/A RC-Result Cache: Enqueue (Result Cache: Contention) 349 349 0 0 0 N/A JZ-Join group dictionary (Join group dictionary) 304 304 0 0 0 N/A TG-In Memory Temp Object (IMCDT global resource) 268 268 0 0 0 N/A TI-In Memory Temp Object HT (IMCDT object HT) 268 268 0 0 0 N/A JD-Job Queue Date 238 238 0 0 0 N/A TH-Threshold Chain (metric threshold evaluation) 119 119 0 0 0 N/A WF-AWR Flush 73 70 3 0 0 N/A WT-AWR CDB-Wide Table Lock 47 47 0 0 0 N/A US-Undo Segment 41 41 0 0 0 N/A PR-Process Startup 33 33 0 0 0 N/A DW-In memory Dispenser 28 27 1 0 0 N/A Enqueue Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> only enqueues with requests are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc, Requests desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- PV-KSV slave startup (syncstart) 25 25 0 0 0 N/A TA-Instance Undo 24 24 0 0 0 N/A SH-Active Session History Flushing 23 23 0 0 0 N/A AF-Advisor Framework (task serialization) 14 14 0 0 0 N/A SQ-Sequence Cache 14 14 0 0 0 N/A TD-KTF map table enqueue (KTF dump entries) 14 14 0 0 0 N/A RS-Reclaimable Space (prevent file delete) 11 11 0 0 0 N/A TO-Temp Object 10 10 0 0 0 N/A FP-File Object (global fob contention) 8 8 0 0 0 N/A RS-Reclaimable Space (read alert level) 8 8 0 0 0 N/A WL-Being Written Redo Log 8 4 4 0 0 N/A CT-Block Change Tracking (global space management) 4 4 0 0 0 N/A DR-Distributed Recovery 4 4 0 0 0 N/A MW-MWIN Schedule 4 4 0 0 0 N/A FH-Flush Stat 2 2 0 0 0 N/A RS-Reclaimable Space (prevent aging list update) 2 2 0 0 0 N/A PG-Global Parameter 1 1 0 0 0 N/A ------------------------------------------------------ Undo Segment Summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 ---- ---------- --------------- -------- -------- --------- ----- -------------- 2 239.2 21,442 1,524 9 28.1/38.4 0/0 0/0/0/0/0/0 ------------------------------------------------------ Undo Segment Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Most recent 35 Undostat rows, ordered by Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ------------ 01-Oct 12:52 16,307 1,044 1,524 5 38 0/0 0/0/0/0/0/0 01-Oct 12:42 428 594 1,522 3 38 0/0 0/0/0/0/0/0 01-Oct 12:32 77,989 1,253 920 4 28 0/0 0/0/0/0/0/0 01-Oct 12:22 3,263 978 919 5 28 0/0 0/0/0/0/0/0 01-Oct 12:12 6,446 532 1,517 4 38 0/0 0/0/0/0/0/0 01-Oct 12:02 79,701 2,631 1,515 9 38 0/0 0/0/0/0/0/0 01-Oct 11:52 16,444 1,094 1,513 5 38 0/0 0/0/0/0/0/0 01-Oct 11:42 433 432 1,511 3 38 0/0 0/0/0/0/0/0 01-Oct 11:32 19,189 6,813 1,510 5 38 0/0 0/0/0/0/0/0 01-Oct 11:22 3,059 910 908 4 28 0/0 0/0/0/0/0/0 01-Oct 11:12 414 365 906 3 28 0/0 0/0/0/0/0/0 01-Oct 11:02 15,482 4,796 1,505 6 38 0/0 0/0/0/0/0/0 ------------------------------------------------------ Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ AQ Background: interrupt 2 0.0 0 369 0.0 AQ Coord jobx_kwsbgsgn l 238 0.0 0 0 N/A AQ Message drive state o 2 0.0 0 0 N/A AQ Sharded master pool l 238 0.0 0 0 N/A AQ deq hash table latch 2 0.0 0 0 N/A ASM db client latch 6,389 0.0 0 0 N/A ASM map operation hash t 2 0.0 0 0 N/A ASM network state latch 113 0.0 0 0 N/A ASM remote client latch 113 0.0 0 0 N/A AWR Alerted Metric Eleme 63,875 0.0 0 0 N/A Change Notification Hash 2,380 0.0 0 0 N/A Column stats entry latch 2 0.0 0 0 N/A Consistent RBA 18,343 0.0 0.0 0 0 N/A DML lock allocation 18,699 0.0 0 0 N/A Event Group Locks 1,273 0.0 0 0 N/A FAL Queue 390 0.0 0 0 N/A FIB s.o chain latch 84 0.0 0 0 N/A FOB consolidated s.o lis 84 0.0 0 0 N/A File State Object Pool P 2 0.0 0 0 N/A GCS logfile block 2 0.0 0 0 N/A GCS logfile write queue 2 0.0 0 0 N/A Hang Manager wait event 472 0.0 0 0 N/A I/O Staticstics latch 2 0.0 0 0 N/A ILM Stats Stripe Latch 2 0.0 0 0 N/A ILM Stats main anchor la 37,501 0.0 0 0 N/A ILM access tracking exte 2 0.0 0 0 N/A ILM activity tracking la 2 0.0 0 0 N/A IM Global dictionary lat 2 0.0 0 0 N/A IM Global dictionary que 2 0.0 0 0 N/A IM Global dictionary rw 2 0.0 0 0 N/A IM area sb latch 2 0.0 0 0 N/A IM area scb latch 2 0.0 0 0 N/A IM emb latch 2 0.0 0 0 N/A IM seg hdr latch 2 0.0 0 0 N/A IMFS defer write list 2 0.0 0 0 N/A IMXT hash table latch 2 0.0 0 0 N/A IPC stats buffer allocat 2 0.0 0 0 N/A In memory undo latch 29,130 0.0 0 0 N/A JS Sh mem access 16,266 0.0 0 0 N/A JS mem alloc latch 26 0.0 0 0 N/A JS queue access latch 28 0.0 0 0 N/A JS queue state obj latch 683,152 0.0 0 0 N/A JS slv state obj latch 108 0.0 0 0 N/A KCNIBR - invalid block r 2 0.0 0 0 N/A KDCStatHash latch 2 0.0 0 0 N/A KDFI Buffer Latch 2 0.0 0 0 N/A KDFI Hash Bucket Latch 2 0.0 0 0 N/A KDFI Hash Elem Latch 2 0.0 0 0 N/A KDMADO action list latch 2 0.0 0 0 N/A KDMADO bll latch 2 0.0 0 0 N/A KDMADO latch 2 0.0 0 0 N/A KFC FX Hash Latch 2 0.0 0 0 N/A KFC Hash Latch 2 0.0 0 0 N/A KFCL LE Freelist 2 0.0 0 0 N/A KFIAS client latch 2 0.0 0 0 N/A KFIAS cluster latch 2 0.0 0 0 N/A KFIAS endpoint latch 2 0.0 0 0 N/A KFIAS node latch 2 0.0 0 0 N/A KFIAS process latch 2 0.0 0 0 N/A KFIAS queue latch 2 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ KGNFS-NFS:SHM structure 2 0.0 0 0 N/A KGNFS-NFS:SVR LIST 2 0.0 0 0 N/A KJC message pool free li 2 0.0 0 0 N/A KJC message pool pending 2 0.0 0 0 N/A KJCT flow control latch 2 0.0 0 0 N/A KMG MMAN ready and start 2,383 0.0 0 0 N/A KSFS OFS ctx level paren 2 0.0 0 0 N/A KSFS OFS req layer paren 2 0.0 0 0 N/A KSFS OFS sess layer pare 2 0.0 0 0 N/A KSFS Receive and Send Qu 2 0.0 0 0 N/A KSFS id table parent lat 2 0.0 0 0 N/A KSFS ksfs_node latch 2 0.0 0 0 N/A KSFSD name cache parent 2 0.0 0 0 N/A KSGL LS Segment latch 2 0.0 0 0 N/A KSGL SGA anchor latch 2 0.0 0 0 N/A KSIPC SGA allocation par 115 0.0 0 0 N/A KSIPCGSN Hash Table latc 2 0.0 0 0 N/A KSIPCGSN Namespace latch 2 0.0 0 0 N/A KSK PDB IO STAT 2 0.0 0 0 N/A KSRMA SGA anchor latch 2 0.0 0 0 N/A KSRMF SGA anchor latch 2 0.0 0 0 N/A KSXR hang analysis phase 2 0.0 0 0 N/A KSXR hang analysis phase 2 0.0 0 0 N/A KTF sga latch 14 0.0 0 2,396 0.0 KTILM latch 2 0.0 0 0 N/A KTU in-memory txn table 2 0.0 0 0 N/A KTURMA ODPSCN latch 2 0.0 0 0 N/A KTURMA SGA allocation pa 2 0.0 0 0 N/A KWQMN job cache list lat 633 0.0 0 0 N/A KWQS pqueue ctx latch 4 0.0 0 0 N/A Latch for NonceHistory 2 0.0 0 0 N/A Locator state objects po 2 0.0 0 0 N/A Lsod array latch 2 0.0 0 0 N/A MGA asr alloc latch 390 0.0 0 0 N/A MGA heap latch 34,998 0.0 0 0 N/A MGA shared context latch 1,355 0.0 0 0 N/A MGA shared context root 661 0.0 0 0 N/A MQL Tracking Latch 0 N/A 0 143 0.0 Memory Management Latch 0 N/A 0 2,383 0.0 Memory Queue 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Subscriber 2 0.0 0 0 N/A Message cache latch 2 0.0 0 0 N/A MinActiveScn Latch 238 0.0 0 0 N/A Mutex 2 0.0 0 0 N/A Mutex Stats 2 0.0 0 0 N/A Nologging Standby SCN Ti 2,382 0.0 0 0 N/A OS process 5,086 0.0 0 0 N/A OS process allocation 15,637 0.0 0 0 N/A OS process: request allo 1,267 0.0 0 0 N/A PDB Hash Table Latch 2 0.0 0 0 N/A PDB LRU structure 2 0.0 0 0 N/A PL/SQL warning settings 640 0.0 0 0 N/A PX hash array latch 2 0.0 0 0 N/A Parent latch for depende 2 0.0 0 0 N/A Parent latch for query h 672 0.0 0 0 N/A Parent latch for segment 24,122 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ QMT 2 0.0 0 0 N/A RTT parent context 4 0.0 0 0 N/A Real-time descriptor lat 56,688 0.0 0 0 N/A Report Request stats lat 545 0.0 0 0 N/A Report Request struct la 135 0.0 0 0 N/A Request holder compeltio 68 0.0 0 0 N/A Result Cache: RC Latch 698 0.0 0 0 N/A Retry Ht elm latch 2 0.0 0 0 N/A Retry bkt latch 2 0.0 0 0 N/A SGA IO buffer pool latch 3,418 0.0 0 3,459 0.0 SGA Logging Bkt Latch 2 0.0 0 0 N/A SGA Logging Log Latch 19,258 0.0 0 0 N/A SGA blob parent 2 0.0 0 0 N/A SGA bucket locks 2 0.0 0 0 N/A SGA heap locks 2 0.0 0 0 N/A SGA pool locks 2 0.0 0 0 N/A SQL memory manager latch 2 0.0 0 2,380 0.0 SQL memory manager worka 181,018 0.0 0 0 N/A SR Stats Stripe Latch 2 0.0 0 0 N/A Sched IM Job latch 115 0.0 0 0 N/A Sched InMem Job Cache 2 0.0 0 0 N/A Shared B-Tree 268 0.0 0 0 N/A Streams Generic 2 0.0 0 0 N/A Subscriber Ht elm latch 2 0.0 0 0 N/A Testing 2 0.0 0 0 N/A Token Manager 2 0.0 0 0 N/A Txn Ht elm latch 2 0.0 0 0 N/A Txn bkt latch 2 0.0 0 0 N/A WCR: sync 2 0.0 0 0 N/A Write State Object Pool 2 0.0 0 0 N/A X$KSFQP 40,396 0.0 0 0 N/A XDB NFS Security Latch 2 0.0 0 0 N/A XDB unused session pool 2 0.0 0 0 N/A XDB used session pool 2 0.0 0 0 N/A active checkpoint queue 69,168 0.0 0.0 0 0 N/A active service list 269,344 0.0 0.0 0 10,837 0.0 alert log latch 8 0.0 0 0 N/A archive destination 622 0.0 0 0 N/A archive process 486 0.0 0 0 N/A begin backup scn array 73 0.0 0 0 N/A buffer pool 2 0.0 0 0 N/A business card 2 0.0 0 0 N/A cache buffer handles 129,558 0.0 0 0 N/A cache buffers chains 231,287,279 0.0 0.0 0 3,077,908 0.0 cache buffers lru chain 850,373 0.0 0.0 0 2,669,814 0.0 cache table scan latch 10,684 0.0 0 10,684 0.0 call allocation 5,262 0.0 0.5 0 0 N/A change notification clie 2 0.0 0 0 N/A channel handle pool latc 2,778 0.0 0 0 N/A channel operations paren 8,994 0.0 0 0 N/A checkpoint queue latch 1,748,514 0.0 0.0 0 615,025 0.0 client/application info 23,221 0.0 0 0 N/A compile environment latc 707 0.0 0 0 N/A corrupted undo seg latch 1,035 0.1 0.0 0 0 N/A cp handoff latch 2 0.0 0 0 N/A cp pool latch 2 0.0 0 0 N/A cp server hash latch 2 0.0 0 0 N/A cp sga latch 113 0.0 0 0 N/A cp srv type state latch 7,151 0.0 0 0 N/A cp srv type wait latch 2 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ cr slave free list 2 0.0 0 0 N/A cvmap freelist lock 2 0.0 0 0 N/A deferred cleanup latch 113 0.0 0 0 N/A dispatcher info 68 0.0 0 0 N/A dml lock allocation 113 0.0 0 0 N/A done queue latch 2 0.0 0 0 N/A dtp latch 669 0.0 0 0 N/A dummy allocation 1,287 0.1 0.0 0 0 N/A eighth spare latch - X p 2 0.0 0 0 N/A eleventh spare latch - c 2 0.0 0 16 0.0 enqueue freelist latch 2 0.0 0 1,689,019 0.0 enqueue hash chains 1,790,034 0.0 0.0 0 0 N/A fast space usage latch 70 0.0 0 0 N/A fifteenth spare latch - 2 0.0 0 0 N/A file cache latch 16,947 0.0 0 0 N/A first Audit Vault latch 589 0.0 0 0 N/A flash file access latch 2 0.0 0 0 N/A flashback copy 2 0.0 0 0 N/A fourteenth spare latch - 2 0.0 0 0 N/A fourth Audit Vault latch 2 0.0 0 0 N/A gc element 2 0.0 0 0 N/A gcs commit scn state 2 0.0 0 0 N/A gcs nodemap pool 2 0.0 0 0 N/A gcs opaque info freelist 2 0.0 0 0 N/A gcs partitioned table ha 2 0.0 0 0 N/A gcs pcm hashed value buc 2 0.0 0 0 N/A gcs resource freelist 2 0.0 0 0 N/A gcs resource hash 2 0.0 0 0 N/A gcs resource scan list 2 0.0 0 0 N/A gcs resource timerq list 2 0.0 0 0 N/A gcs shadows freelist 2 0.0 0 0 N/A ges cached resource list 2 0.0 0 0 N/A ges domain table 2 0.0 0 0 N/A ges enqueue table freeli 2 0.0 0 0 N/A ges group table 2 0.0 0 0 N/A ges process hash list 2 0.0 0 0 N/A ges process parent latch 2 0.0 0 0 N/A ges remote lock freelist 2 0.0 0 0 N/A ges resource hash list 2 0.0 0 0 N/A ges resource scan list 2 0.0 0 0 N/A ges resource table freel 2 0.0 0 0 N/A ges timeout list 2 0.0 0 0 N/A ges value block free lis 2 0.0 0 0 N/A global KZLD latch for au 589 0.0 0 0 N/A global ctx hash table la 239,980 0.0 0 0 N/A global tx hash mapping 2 0.0 0 0 N/A granule from data transf 2 0.0 0 0 N/A gws:Sharding global desc 476 0.0 0 0 N/A hash table Sql Plan Find 2,310 0.0 0 166 0.0 hash table column usage 918 0.0 0 43,865 0.0 hash table expression us 2 0.0 0 0 N/A heartbeat check 2 0.0 0 0 N/A imc buf hdl lat 2 0.0 0 0 N/A imc cr clone buf 2 0.0 0 0 N/A imc hash lat 2 0.0 0 0 N/A imc sj lat 2 0.0 0 0 N/A imc srchsp lat 2 0.0 0 0 N/A imc-txn-wrk-lat 2 0.0 0 0 N/A in-memory area latch 2 0.0 0 0 N/A in-memory columnar segme 2 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ in-memory columnar ts ex 2 0.0 0 0 N/A in-memory global pool la 2 0.0 0 0 N/A internal temp table obje 20 0.0 0 0 N/A intra txn parallel recov 2 0.0 0 0 N/A io pool granule list 140 0.0 0 0 N/A io pool granule metadata 285 0.0 0 0 N/A job workq parent latch 11 0.0 0 9 11.1 job_queue_processes free 36 0.0 0 0 N/A job_queue_processes para 19 0.0 0 0 N/A jslv pdb context latch 18 0.0 0 0 N/A k2q lock allocation 2 0.0 0 0 N/A kcb DW scan objtemp hash 2 0.0 0 0 N/A kcbtsemkid latch 4 0.0 0 0 N/A kcn buffer chains 2 0.0 0 0 N/A kdlx hb parent latch 2 0.0 0 0 N/A keiut hash table modific 53 0.0 0 0 N/A kgb parent 2 0.0 0 0 N/A kgnfs mount latch 2 0.0 0 0 N/A kjbr roi instance map 2 0.0 0 0 N/A kjci objects freelist la 2 0.0 0 0 N/A kjci process context lat 2 0.0 0 79,378 0.0 kjoedcso state object fr 113 0.0 0 0 N/A kjoeq omni enqueue hash 2 0.0 0 0 N/A kjoer owner hash bucket 2 0.0 0 0 N/A kjsca protect pkey hash 2 0.0 0 0 N/A kjsca protect service ha 2 0.0 0 0 N/A kokc descriptor allocati 158 0.0 0 0 N/A krbmrosl 135 0.0 0 0 N/A krso process 436 0.0 0 0 N/A ksevn object root latch 2,383 0.0 0 0 N/A ksfv messages 2 0.0 0 0 N/A ksi resource reuse count 2 0.0 0 0 N/A ksim group membership ca 2 0.0 0 0 N/A kss move lock 52 0.0 0 0 N/A ksuosstats global area 493 0.0 0 2,732 0.0 ksv allocation latch 213 0.0 0 0 N/A ksv class latch 3,416 2.3 0.0 0 0 N/A ksv msg queue latch 476 0.4 0.0 0 473 0.6 ksz_so allocation latch 1,267 0.0 0 0 N/A ktfbn latch 1,538 0.0 0 0 N/A ktm adg jrnls 2 0.0 0 0 N/A ktm adg lookuptable leve 2 0.0 0 0 N/A ktm adg lookuptable leve 2 0.0 0 0 N/A ktm adg space 2 0.0 0 0 N/A ktm adg stages 2 0.0 0 0 N/A ktm adg worklink 2 0.0 0 0 N/A ktm global data 153 0.0 0 0 N/A ktm prv jrnls 2 0.0 0 0 N/A ktmpj HT ls 2 0.0 0 0 N/A kwqbsn:qsga 260 0.0 0 0 N/A kwslbmdl: metadata latch 240 0.0 0 0 N/A kwslbql: queue latch 2 0.0 0 0 N/A kwslbutl: uncommitted tx 2 0.0 0 0 N/A kwsptQcachLt: queue cach 2 0.0 0 0 N/A kwsptTrncTsksLt: trunc t 2 0.0 0 0 N/A kwsptjobAdPtLt: AdPt lis 210 0.0 0 0 N/A kwsslLat: skiplist latch 2 0.0 0 0 N/A kxfxscanrate_latch 2 0.0 0 0 N/A lgwr LWN SCN 24,692 0.3 0.0 0 0 N/A list of block allocation 40,201 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ loader state object free 316 0.0 0 0 N/A lob segment dispenser la 158 0.0 0 0 N/A lob segment hash table l 658 0.0 0 0 N/A lob segment query latch 2 0.0 0 0 N/A lock DBA buffer during m 2 0.0 0 0 N/A log write info 0 N/A 0 18,319 0.0 log write worker phase 3,226 0.0 0.0 0 0 N/A logical standby cache 2 0.0 0 0 N/A logminer context allocat 2 0.0 0 0 N/A logminer local 2 0.0 0 0 N/A logminer work area 2 0.0 0 0 N/A longop free list parent 91 0.0 0 89 0.0 managed standby 242 0.0 0 0 N/A message bitmap latch 2 0.0 0 0 N/A message pool operations 2,444 0.0 0 0 N/A messages 347,698 0.1 0.0 0 0 N/A msg queue latch 2 0.0 0 0 N/A multiblock read objects 22,440 0.0 0 0 N/A name-service namespace b 2 0.0 0 0 N/A ncodef allocation latch 113 0.0 0 0 N/A ninth spare latch - X pa 2 0.0 0 0 N/A object queue header free 45,376 0.0 0 0 N/A object queue header oper 5,538,760 0.0 0.0 0 0 N/A object queue memory 2 0.0 0 0 N/A object stats modificatio 2,105 0.0 0 0 N/A parallel query alloc buf 2 0.0 0 868 0.0 parallel query stats 2 0.0 0 0 N/A parameter table manageme 3,300 0.0 0.0 0 0 N/A pdb enqueue hash chains 2 0.0 0 0 N/A peshm 2 0.0 0 0 N/A pesom_free_list 2 0.0 0 0 N/A pesom_hash_node 2 0.0 0 0 N/A pkey global statistics 2 0.0 0 0 N/A pmon dead latch 114 0.0 0 0 N/A post/wait queue 23,632 0.4 0.0 0 26,594 0.2 process allocation 1,300 0.2 1.0 0 0 N/A process group creation 1,267 0.0 0 0 N/A process pkey statistics 2 0.0 0 0 N/A process queue 2 0.0 0 0 N/A process queue reference 2 0.0 0 0 N/A qmn task queue latch 1,554 0.0 0 0 N/A quarantine object 114 0.0 0 0 N/A query server freelists 2 0.0 0 0 N/A query server process 5,112 0.0 0 0 N/A queued dump request 24 0.0 0 0 N/A queuing load statistics 2 0.0 0 0 N/A recovery domain hash buc 2 0.0 0 0 N/A redo allocation 156,941 1.0 0.0 0 9,855,924 0.0 redo copy 2 0.0 0 9,856,204 0.0 redo gen encryption key 4 0.0 0 0 N/A redo transport task 524 0.0 0 0 N/A redo writing 125,655 0.1 0.0 0 0 N/A remote tool request latc 645 0.0 0 0 N/A resmgr group change latc 624 0.0 0 0 N/A resmgr:active threads 1,326 0.0 0 119 0.0 resmgr:actses change gro 668 0.0 0 0 N/A resmgr:actses change sta 19 0.0 0 0 N/A resmgr:free threads list 1,285 0.0 0 0 N/A resmgr:plan CPU method 2 0.0 0 0 N/A resmgr:plan change alloc 2 0.0 0 0 N/A Latch Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ resmgr:plan change latch 2 0.0 0 0 N/A resmgr:resource group CP 42 0.0 0 0 N/A resmgr:schema config 142 0.0 0 119 0.0 resmgr:session queuing 2 0.0 0 0 N/A row cache objects 2 0.0 0 0 N/A rules engine rule set st 200 0.0 0 0 N/A second Audit Vault latch 2 0.0 0 0 N/A sequence cache 609 0.0 0 0 N/A service drain list 7,148 0.0 0 0 N/A session allocation 5,693 0.0 0 4,451 0.0 session idle bit 333,581 0.0 0 0 N/A session queue latch 2 0.0 0 0 N/A session state list latch 1,372 0.0 0 0 N/A session statistics 1,287 0.0 0 0 N/A session switching 752 0.0 0 0 N/A session timer 2,383 0.0 0 0 N/A seventh spare latch - X 2 0.0 0 0 N/A sga hash table parent la 2 0.0 0 0 N/A shard latch 2 0.0 0 0 N/A shared pool 221,489 0.2 0.2 0 0 N/A shared pool sim alloc 2 0.0 0 0 N/A shared pool simulator 3,221 0.0 0 0 N/A shared server configurat 2,389 0.0 0 0 N/A sim partition latch 2 0.0 0 0 N/A simulator hash latch 18,356,585 0.0 0.1 0 0 N/A simulator lru latch 622,311 0.0 0.0 0 17,543,624 0.0 sixth spare latch - X pa 2 0.0 0 0 N/A sort extent pool 124,733 0.0 0 0 N/A space background task la 21,144 49.7 0.9 7 5,724 0.9 state object free list 4 0.0 0 0 N/A statistics aggregation 290 0.0 0 0 N/A subscriber Ht bkt 2 0.0 0 0 N/A tablespace key chain 764 0.0 0 0 N/A temp lob duration state 9,364 0.0 0 0 N/A temporary table state ob 16 0.0 0 0 N/A tenth spare latch - X pa 2 0.0 0 0 N/A test excl. parent l0 2 0.0 0 0 N/A test excl. parent2 l0 2 0.0 0 0 N/A test excl. parent2 lmid 2 0.0 0 0 N/A test mixed exclusive 2 0.0 0 0 N/A test mode exclusive 2 0.0 0 0 N/A test open exclusive 2 0.0 0 0 N/A test pdb exclusive 2 0.0 0 0 N/A test shared parent2 lmid 2 0.0 0 0 N/A thirteenth spare latch - 2 0.0 0 0 N/A threshold alerts latch 291 0.0 0 0 N/A transaction allocation 5,690 0.0 0 0 N/A twelfth spare latch - ch 2 0.0 0 0 N/A twenty-fifth spare latch 2 0.0 0 0 N/A twenty-first spare latch 2 0.0 0 0 N/A twenty-fourth spare latc 2 0.0 0 0 N/A twenty-second spare latc 2 0.0 0 0 N/A twenty-third spare latch 2 0.0 0 0 N/A undo global data 611,203 0.0 0.0 0 0 N/A virtual circuit buffers 2 0.0 0 0 N/A virtual circuit holder 2 0.0 0 0 N/A virtual circuit queues 2 0.0 0 0 N/A ------------------------------------------------------ Latch Sleep Breakdown DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- space background task latc 21,144 10,509 9,673 882 redo allocation 156,941 1,609 6 1,603 shared pool 221,489 463 85 410 simulator lru latch 622,311 304 6 298 cache buffers chains 231,287,279 106 2 104 ksv class latch 3,416 80 2 78 simulator hash latch 18,356,585 12 1 11 call allocation 5,262 2 1 1 process allocation 1,300 2 2 0 ------------------------------------------------------ Latch Miss Sources DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbgcur: fast path excl 0 3 0 call allocation ksuxds 0 1 1 ksv class latch ksvclsl: checkpool 0 2 0 process allocation ksuapc 0 2 1 redo allocation kcrfw_redo_gen: redo alloc 0 6 0 simulator hash latch kcbs_get_or_extract_simbuf 0 1 0 simulator lru latch kcbs_lookup_setid 0 6 0 space background task la ktsj_grab_task 0 7,239 9,641 space background task la ktsj_detach_task 0 2,253 32 unknown latch No latch 0 181 0 unknown latch kghalo 0 81 62 unknown latch kghfre 0 3 15 unknown latch kghupr1 0 1 7 ------------------------------------------------------ Mutex Sleep Summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> ordered by number of sleeps desc Wait Mutex Type Location Sleeps Time (ms) --------------------- -------------------------------- ------------ ------------ Library Cache kgllkdl1 85 1,360 379 Library Cache kgllkc1 57 653 186 Row Cache [19] kqrpre 24 0 Library Cache kglhdgn2 106 18 9 Library Cache kglget2 2 14 29 Library Cache kglhdgn1 62 14 15 Library Cache kglpin1 4 6 0 Cursor Pin kksLockDelete [KKSCHLPIN6] 4 4 Cursor Pin kkslce [KKSCHLPIN2] 4 4 Row Cache [10] kqreqd 2 0 Library Cache kglpnal1 90 2 0 Library Cache kglpndl1 95 2 0 ------------------------------------------------------ Parent Latch Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Child Latch Statistics DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Segments by Logical Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Logical Reads: 186,998,812 -> Captured Segments account for 97.4% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Logical Object Name Name Type Obj# Dataobj# Reads %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 48,223,152 25.79 FDW_DW D_FDW01 FDW_R_PDP_SCHEDULE TABLE 154303 154303 29,223,632 15.63 FDW_DW I_FDW01 FDW_F_LOCAL_PRODUCT_ INDEX 1726240 1726240 24,436,304 13.07 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 13,609,040 7.28 FDW_DW D_FDW01 FDW_REL_PROD_OPE_COM TABLE 154337 154337 13,283,600 7.10 ------------------------------------------------------ Segments by Physical Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Physical Reads: 90,740,978 -> Captured Segments account for 87.9% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Physical Object Name Name Type Obj# Dataobj# Reads %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 47,183,637 52.00 FDW_DW D_FDW01 FDW_REL_PROD_OPE_COM TABLE 154337 154337 13,280,946 14.64 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 13,138,606 14.48 FDW_DW D_FDW01 FDW_REL_PRODUCT_ASSE TABLE 154338 154338 4,812,306 5.30 FDW_DW I_FDW01 FDW_REL_PRODUCT_ASSE INDEX 1726184 1726184 1,264,727 1.39 ------------------------------------------------------ Segments by Physical Read RequestsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 -> Total Physical Read Requests: 1,041,612 -> Captured Segments account for 65.3% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Phys Read Object Name Name Type Obj# Dataobj# Requests %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 374,133 35.92 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 105,076 10.09 FDW_DW D_FDW01 FDW_REL_PROD_OPE_COM TABLE 154337 154337 103,950 9.98 FDW_DW D_FDW01 FDW_REL_PRODUCT_ASSE TABLE 154338 154338 39,184 3.76 FDW_DW I_FDW01 FDW_REL_PRODUCT_ASSE INDEX 1726184 1726184 12,018 1.15 ------------------------------------------------------ Segments by UnOptimized Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total UnOptimized Read Requests: 1,041,612 -> Captured Segments account for 65.3% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. UnOptimized Object Name Name Type Obj# Dataobj# Reads %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 374,133 35.92 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 105,076 10.09 FDW_DW D_FDW01 FDW_REL_PROD_OPE_COM TABLE 154337 154337 103,950 9.98 FDW_DW D_FDW01 FDW_REL_PRODUCT_ASSE TABLE 154338 154338 39,184 3.76 FDW_DW I_FDW01 FDW_REL_PRODUCT_ASSE INDEX 1726184 1726184 12,018 1.15 ------------------------------------------------------ Segments by Optimized Reads DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Segments by Direct Physical ReadsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 -> Total Direct Physical Reads: 89,419,310 -> Captured Segments account for 87.7% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Direct Object Name Name Type Obj# Dataobj# Reads %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 47,182,032 52.76 FDW_DW D_FDW01 FDW_REL_PROD_OPE_COM TABLE 154337 154337 13,280,946 14.85 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 13,136,380 14.69 FDW_DW D_FDW01 FDW_REL_PRODUCT_ASSE TABLE 154338 154338 4,810,892 5.38 ------------------------------------------------------ Segments by Physical Writes DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Physical Writes: 11,949,747 -> Captured Segments account for 2.8% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Physical Object Name Name Type Obj# Dataobj# Writes %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_F_ASSET_STATUS TABLE 154257 154257 29,352 .25 FDW_DW D_FDW01 FDW_REL_PRODUCT_OPE_ TABLE 154339 531942 20,691 .17 FDW_DW D_FDW01 FDW_F_LOCAL_PRODUCT_ TABLE 348633 348633 17,421 .15 FDW_STG_DW_BI D_FDW01 FDW_S_PRODUCT_UNIT_C TABLE 1998234 2380052 17,407 .15 FDW_DW D_FDW01 FDW_R_CAMPAIGN TABLE 348634 348634 16,475 .14 ------------------------------------------------------ Segments by Physical Write RequestsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17 -> Total Physical Write Requests: 490,313 -> Captured Segments account for 17.4% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Phys Write Object Name Name Type Obj# Dataobj# Requests %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_F_LOCAL_PRODUCT_ TABLE 348633 348633 15,920 3.25 FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 8,258 1.68 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 6,244 1.27 FDW_DW D_FDW01 FDW_REL_PRODUCT_OPE_ TABLE 154339 531942 5,843 1.19 FDW_DW D_FDW01 FDW_F_ASSET_STATUS TABLE 154257 154257 5,404 1.10 ------------------------------------------------------ Segments by Direct Physical WritesDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 No data exists for this section of the report. ------------------------------------------------------ Segments by Table Scans DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Total Table Scans: 345 -> Captured Segments account for 20.0% of Total -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. Table Object Name Name Type Obj# Dataobj# Scans %Total -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_PRODUCT_UNIT TABLE 700667 700667 36 10.43 FDW_DW I_FDW01 FDW_F_LOCAL_PRODUCT_ INDEX 1726240 1726240 21 6.09 FDW_DW I_FDW01 FDW_REL_PRODUCT_ASSE INDEX 1726184 1726184 4 1.16 FDW_DW D_FDW01 FDW_R_ASSET_OPERATIO TABLE 154268 154268 4 1.16 FDW_DW D_FDW01 FDW_REL_PRODUCT_ASSE TABLE 154338 154338 2 .58 ------------------------------------------------------ Segments by DB Blocks Changes DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> % of Capture shows % of DB Block Changes for each top segment compared -> with total DB Block Changes for all segments captured by the Snapshot -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Subobject Obj. DB Block % of Object Name Name Type Obj# Dataobj# Changes Capture -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW I_FDW01 FDW_F_ASSET_STATUS_P INDEX 1726191 1726191 711,920 7.48 FDW_DW I_FDW01 IT_FDW_F_ASSET_STATU INDEX 1726295 1726295 700,624 7.36 FDW_DW I_FDW01 IT_FDW_F_ASSET_STATU INDEX 1726402 1726402 693,760 7.29 FDW_DW I_FDW01 IT_FDW_F_ASSET_STATU INDEX 1726380 1726380 693,584 7.28 FDW_DW D_FDW01 FDW_R_PDP_SCHEDULE TABLE 154303 154303 690,576 7.25 ------------------------------------------------------ Segments by Row Lock Waits DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Row Subobject Obj. Lock % of Object Name Name Type Obj# Dataobj# Waits Capture -------------------- ---------- ----- ---------- ---------- ------------ ------- FDW_DW D_FDW01 FDW_R_GAMME TABLE 172452 172452 1 100.00 ------------------------------------------------------ Segments by ITL Waits DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Segments by Buffer Busy Waits DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> % of Capture shows % of Buffer Busy Waits for each top segment compared -> with total Buffer Busy Waits for all segments captured by the Snapshot -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------- Buffer Subobject Obj. Busy % of Object Name Name Type Obj# Dataobj# Waits Capture -------------------- ---------- ----- ---------- ---------- ------------ ------- SYS SYSTEM SEG$ TABLE 14 8 6 100.00 ------------------------------------------------------ Dictionary Cache Stats DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "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 282 0.0 0 N/A 46 1 dc_global_oids 2,491 0.0 0 N/A 0 52 dc_histogram_data 41,347 0.0 0 N/A 0 62,136 dc_histogram_defs 15,384 0.0 0 N/A 0 86,761 dc_objects 49,920 0.7 0 N/A 349 45,735 dc_profiles 2,356 0.0 0 N/A 0 4 dc_props 7,580 0.0 0 N/A 0 83 dc_rollback_segments 2,497 0.0 0 N/A 0 16 dc_segments 9,822 0.0 0 N/A 660 3,544 dc_sequences 81 0.0 0 N/A 81 11 dc_statistics_status 3 100.0 0 N/A 0 416 dc_tablespace_quotas 4,103 0.0 0 N/A 0 32 dc_tablespaces 5,751 0.0 0 N/A 0 12 dc_users 137,479 0.0 2,428 0.0 0 999 outstanding_alerts 18 0.0 0 N/A 0 5 sch_lj_oids 22 0.0 0 N/A 0 98 ------------------------------------------------------ Library Cache Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- ACCOUNT_STATUS 2,356 0.0 0 N/A 0 0 BODY 1,673 0.0 54,621 0.0 0 0 CLUSTER 4 0.0 4 0.0 0 0 DBLINK 2,387 0.0 0 N/A 0 0 EDITION 633 0.0 1,265 0.0 0 0 SCHEMA 598 0.0 0 N/A 0 0 SQL AREA 13,201 0.5 778,443 0.6 1,682 1,657 SQL AREA BUILD 1,379 2.5 0 N/A 0 0 SQL AREA STATS 1,720 2.2 1,720 3.0 13 0 TABLE/PROCEDURE 3,765 0.0 133,113 0.0 0 0 TRIGGER 2,027 0.0 2,027 0.0 0 0 ------------------------------------------------------ Memory Dynamic Components DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Min/Max sizes since instance startup -> Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred -> ordered by Component Begin Snap Current Min Max Oper Last Op Component Size (Mb) Size (Mb) Size (Mb) Size (Mb) Count Typ/Mod --------------- ----------- ----------- ----------- ----------- ------ ------- ASM Buffer Cach .00 .00 .00 .00 0 STA/ DEFAULT 16K buf .00 .00 .00 .00 0 STA/ DEFAULT 2K buff .00 .00 .00 .00 0 STA/ DEFAULT 32K buf .00 .00 .00 .00 0 STA/ DEFAULT 4K buff .00 .00 .00 .00 0 STA/ DEFAULT 8K buff .00 .00 .00 .00 0 STA/ DEFAULT buffer 81,920.00 81,920.00 81,920.00 86,528.00 0 SHR/IMM Data Transfer C .00 .00 .00 .00 0 STA/ In Memory RO Ex .00 .00 .00 .00 0 STA/ In Memory RW Ex .00 .00 .00 .00 0 STA/ In-Memory Area .00 .00 .00 .00 0 STA/ KEEP buffer cac .00 .00 .00 .00 0 STA/ PGA Target 18,432.00 18,432.00 18,432.00 18,432.00 0 STA/ RECYCLE buffer .00 .00 .00 .00 0 STA/ SGA Target 98,304.00 98,304.00 98,304.00 98,304.00 0 STA/ Shared IO Pool 512.00 512.00 .00 512.00 0 GRO/IMM java pool 256.00 256.00 256.00 256.00 0 STA/ large pool 512.00 512.00 512.00 1,792.00 0 SHR/DEF memoptimize buf .00 .00 .00 .00 0 STA/ shared pool 14,336.00 14,336.00 10,752.00 14,336.00 0 GRO/IMM streams pool 512.00 512.00 .00 512.00 0 GRO/IMM ------------------------------------------------------ Memory Resize Operations SummaryDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Memory Resize Ops DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Process Memory Summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 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 -> Top 10 containers by Alloc (MB) at end snapshot time are displayed Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B Freeable 2,582.5 .0 63.0 330.4 2,118 N/A 41 41 Other 545.0 N/A 6.6 12.0 43 43 83 83 PL/SQL 9.1 .7 .1 .5 2 5 73 73 SQL 1.8 .7 .0 .1 0 2,647 43 36 Total 3,138.4 1.5 69.7 342.9 2,164 2,695 240 233 E Freeable 2,580.7 .0 71.7 352.4 2,119 N/A 36 36 Other 531.7 N/A 6.8 12.2 43 43 78 78 PL/SQL 8.8 .6 .1 .5 2 5 68 68 SQL 1.7 .6 .0 .1 0 2,647 38 31 Total 3,122.8 1.3 78.7 365.2 2,164 2,695 220 213 ------------------------------------------------------ SGA Memory Summary DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 86,436,216,832 Fixed Size 8,672,088 Redo Buffers 259,760,128 Variable Size 16,374,562,816 ------------------- sum 103,079,211,864 ------------------------------------------------------ SGA breakdown difference by Pool and NameDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17 -> 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 256.0 256.0 0.00 large CTWR dba buffer 30.8 30.8 0.00 large free memory 474.8 474.8 0.00 large krcc extent chunk 5.9 5.9 0.00 shared KGLDA 204.9 204.9 0.01 shared KGLH0 2,663.4 2,663.5 0.00 shared KGLHD 362.2 362.3 0.01 shared SQLA 7,956.7 7,956.9 0.00 shared db_block_hash_buckets 712.0 712.0 0.00 shared free memory 765.3 765.5 0.03 shared row cache .0 .0 0.00 stream free memory 512.0 512.0 0.00 buffer_cache 81,920.0 81,920.0 0.00 fixed_sga 8.3 8.3 0.00 log_buffer 247.7 247.7 0.00 shared_io_pool 512.0 512.0 0.00 ------------------------------------------------------ Replication System Resource UsageDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 No data exists for this section of the report. ------------------------------------------------------ Replication SGA Usage DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Capture DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Capture Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Apply Reader DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Apply Coordinator DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Apply Server DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Apply Coordinator RateDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1709 No data exists for this section of the report. ------------------------------------------------------ GoldenGate Apply Reader and Server RateDB/Inst: RFDWSY20/RFDWSY20 Snaps: 1709 No data exists for this section of the report. ------------------------------------------------------ XStream Capture DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Capture Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Apply Reader DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Apply Coordinator DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Apply Server DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Apply Coordinator Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ XStream Apply Reader and Server RateDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-1 No data exists for this section of the report. ------------------------------------------------------ Table Statistics by DML OperationsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-170 No data exists for this section of the report. ------------------------------------------------------ Table Statistics by Conflict ResolutionsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 170 No data exists for this section of the report. ------------------------------------------------------ Replication Large Transaction StatisticsDB/Inst: RFDWSY20/RFDWSY20 Snaps: 170 No data exists for this section of the report. ------------------------------------------------------ Replication Long Running Transaction StatisticsDB/Inst: RFDWSY20/RFDWSY20 Sna No data exists for this section of the report. ------------------------------------------------------ Streams CPU/IO Usage DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Streams processes ordered by CPU Time in descending order User IO SYS IO Session Type First Logon CPU time(s) Wait time(s) Wait time(s) -------------------- ------------------ ------------ ------------ ------------ QMON Slave 08-Jun-21 15:22:37 0.2 0.0 0.0 QMON Coordinator 08-Jun-21 15:22:37 0.2 0.0 0.0 ------------------------------------------------------ Streams Capture DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Streams Capture Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Streams Apply DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Streams Apply Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Buffered Queues DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Buffered Queue Subscribers DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Rule Set DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Persistent Queues DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Persistent Queues Rate DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Persistent Queue Subscribers DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Resource Limit Stats DB/Inst: RFDWSY20/RFDWSY20 Snap: 17094 -> Only rows with Current or Maximum Utilization > 80% of Limit are shown -> For "UNLIMITED" resources, rows whose Current or Maximum Utilization exceeds 2*Initial Allocation are shown -> Ordered by Resource Name Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- smartio_overhead_memory 0 168,984 0 UNLIMITED smartio_sessions 0 1 0 UNLIMITED ------------------------------------------------------ Shared Servers Activity DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Values represent averages for all samples Avg Total Avg Active Avg Total Avg Active Avg Total Avg Active Connections Connections Shared Srvrs Shared Srvrs Dispatchers Dispatchers ------------ ------------ ------------ ------------ ------------ ------------ 0 0 1 0 1 0 ------------------------------------------------------ Shared Servers Rates DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 Common Disp Common Disp Server Queue Queue Server Server Queue Queue Total Server Per Sec Per Sec Msgs/Sec KB/Sec Total Total Msgs Total(KB) -------- -------- -------- --------- ---------- ---------- ---------- ---------- 0 0 0 0.0 0 0 0 0 ------------------------------------------------------ Shared Servers Utilization DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Statistics are combined for all servers -> Incoming and Outgoing Net % are included in %Busy Total Server Incoming Outgoing Time (s) %Busy %Idle Net % Net % -------------- -------- -------- --------- --------- 7,148 0.0 100.0 0.0 0.0 ------------------------------------------------------ Shared Servers Common Queue DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Shared Servers Dispatchers DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Ordered by %Busy, descending -> Total Queued, Total Queue Wait and Avg Queue Wait are for dispatcher queue -> Name suffixes: "(N)" - dispatcher started between begin and end snapshots "(R)" - dispatcher re-started between begin and end snapshots Avg Total Disp Total Total Queue Avg Queue Name Conns Time (s) %Busy %Idle Queued Wait (s) Wait (ms) ------- --------- ---------- -------- -------- ---------- ----------- ---------- D000 0.0 7,148 0.0 100.0 0 0 ------------------------------------------------------ init.ora Parameters DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /busdata/rfdw/sy20/oracle/admin/a audit_trail NONE compatible 18.0.0 control_files /busdata/rfdw/sy20/oracle/redoa/c cpu_count 4 db_block_size 8192 db_name RFDWSY20 db_recovery_file_dest /busdata/rfdw/sy20/oracle/arch db_recovery_file_dest_size 2208301056 diagnostic_dest /busdata/rfdw/sy20/oracle/admin/ dispatchers (PROTOCOL=TCP) (SERVICE=RFDWSY20X filesystemio_options SETALL log_archive_dest_1 LOCATION=/busdata/rfdw/sy20/oracl log_archive_format RFDWSY20_%s%r%t.arc nls_language AMERICAN nls_territory AMERICA open_cursors 1000 optimizer_adaptive_plans FALSE pga_aggregate_limit 38654705664 pga_aggregate_target 19327352832 processes 4000 query_rewrite_integrity TRUSTED remote_login_passwordfile EXCLUSIVE sga_max_size 103079215104 sga_target 103079215104 star_transformation_enabled TRUE undo_tablespace UNDO ------------------------------------------------------ init.ora Multi-Valued ParametersDB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> This section only displays parameters that have more than one value ->'(NULL)' indicates a missing parameter value -> A blank in the 'End Snapshot' indicates the same value as the 'Begin Snapshot End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- control_files /busdata/rfdw/sy20/oracle/arch/RF /busdata/rfdw/sy20/oracle/redoa/c /busdata/rfdw/sy20/oracle/redob/c ------------------------------------------------------ Top SQL with Top Events DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top SQL statements by DB Time along with the top events by DB Time for those SQLs. -> % Activity is the percentage of DB Time due to the SQL. -> % Event is the percentage of DB Time due to the event that the SQL is waiting on. -> % Row Source is the percentage of DB Time due to the row source for the SQL waiting on the event. -> Executions is the number of executions of the SQL that were sampled in ASH. SQL ID Plan Hash Executions % Activity ----------------------- -------------------- -------------------- -------------- % Row Event % Event Top Row Source Source ------------------------------ ------- --------------------------------- ------- 89cf6rrncn00x 1768323407 4 22.26 CPU + Wait for CPU 16.78 HASH JOIN 6.16 SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR direct path write temp 2.05 HASH JOIN 1.37 db file scattered read 1.03 INDEX - FAST FULL SCAN 1.03 3d0cwt2sd9sbp 904057217 21 13.70 CPU + Wait for CPU 13.70 HASH - GROUP BY 8.90 with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept 4vrwkq6321kny 3698348656 1 12.67 CPU + Wait for CPU 8.56 HASH JOIN - OUTER 7.53 SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS direct path read temp 2.74 HASH JOIN - OUTER 2.74 direct path write temp 1.03 HASH JOIN - OUTER 1.03 ddhc1697xfnv7 3698348656 1 11.99 CPU + Wait for CPU 7.88 HASH JOIN - OUTER 5.48 SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS direct path read temp 2.74 HASH JOIN - OUTER 2.74 frrjd8g9c44yd 1009549452 21 8.90 CPU + Wait for CPU 6.85 TABLE ACCESS - FULL 5.48 with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase direct path read 2.05 TABLE ACCESS - FULL 2.05 ------------------------------------------------------ Top SQL with Top Row Sources DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top SQL statements by DB Time along with the top row sources by DB Time for those SQLs. -> % Activity is the percentage of DB Time due to the SQL. -> % Row Source is the percentage of DB Time spent on the row source by that SQL. -> % Event is the percentage of DB Time spent on the event by the SQL executing the row source. -> Executions is the number of executions of the SQL that were sampled in ASH. SQL ID Plan Hash Executions % Activity ----------------------- -------------------- -------------------- -------------- % Row Row Source Source Top Event % Event ---------------------------------------- ------- ----------------------- ------- 89cf6rrncn00x 1768323407 4 22.26 HASH JOIN 7.88 CPU + Wait for CPU 6.16 SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_ OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERAT ION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATI ON_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PR HASH JOIN 4.45 CPU + Wait for CPU 3.08 TABLE ACCESS - FULL 3.08 CPU + Wait for CPU 2.40 3d0cwt2sd9sbp 904057217 21 13.70 HASH - GROUP BY 8.90 CPU + Wait for CPU 8.90 with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKU P_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inte r, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept INDEX - FAST FULL SCAN 3.42 CPU + Wait for CPU 3.42 TABLE ACCESS - FULL 1.37 CPU + Wait for CPU 1.37 4vrwkq6321kny 3698348656 1 12.67 HASH JOIN - OUTER 11.30 CPU + Wait for CPU 7.53 SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS ddhc1697xfnv7 3698348656 1 11.99 HASH JOIN - OUTER 8.90 CPU + Wait for CPU 5.48 SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT _ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu. LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATI ON_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_AS TABLE ACCESS - FULL 2.40 CPU + Wait for CPU 1.71 frrjd8g9c44yd 1009549452 21 8.90 TABLE ACCESS - FULL 7.53 CPU + Wait for CPU 5.48 with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOO KUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg , FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS in ter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET ase TABLE ACCESS - FULL 1.37 CPU + Wait for CPU 1.37 ------------------------------------------------------ Top Sessions DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 1532,45318 24.32 CPU + Wait for CPU 22.26 U_COPILOT_RO w3wp.exe 65/715 [ 9%] 4 direct path read 2.05 6/715 [ 1%] 0 4547,52624 12.67 CPU + Wait for CPU 8.56 U_TND_RW JDBC Thin Client 25/715 [ 3%] 0 direct path read temp 2.74 8/715 [ 1%] 0 direct path write temp 1.03 3/715 [ 0%] 0 4535,52353 11.99 CPU + Wait for CPU 7.88 U_TND_RW JDBC Thin Client 23/715 [ 3%] 0 direct path read temp 2.74 8/715 [ 1%] 0 35,53473 7.19 CPU + Wait for CPU 5.14 U_TND_RW JDBC Thin Client 15/715 [ 2%] 1 db file scattered read 1.03 3/715 [ 0%] 0 26,60878 5.82 CPU + Wait for CPU 3.42 U_TND_RW JDBC Thin Client 10/715 [ 1%] 1 direct path read 1.03 3/715 [ 0%] 0 ------------------------------------------------------ Top Blocking Sessions DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 No data exists for this section of the report. ------------------------------------------------------ Top PL/SQL Procedures DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> 'PL/SQL entry subprogram' represents the application's top-level entry-point (procedure, function, trigger, package initialization or RPC call) into PL/SQL. -> 'PL/SQL current subprogram' is the PL/SQL subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular PL/SQL entry subprogram PL/SQL Entry Subprogram % Activity ----------------------------------------------------------------- ---------- PL/SQL Current Subprogram % Current ----------------------------------------------------------------- ---------- SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE#13 1.37 SQL 1.37 ------------------------------------------------------ Top Events DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top Events by DB Time -> % Activity is the percentage of DB Time due to the event Event Event Class Session Type ----------------------------------- --------------- --------------- Avg Active % Activity Sessions ---------- ---------- CPU + Wait for CPU CPU FOREGROUND 66.44 0.27 CPU + Wait for CPU CPU BACKGROUND 10.27 0.04 direct path read temp User I/O FOREGROUND 6.51 0.03 direct path read User I/O FOREGROUND 6.16 0.03 direct path write temp User I/O FOREGROUND 3.77 0.02 ------------------------------------------------------ Top Event P1/P2/P3 Values DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top Events by DB Time and the top P1/P2/P3 values for those events. -> % Event is the percentage of DB Time due to the event -> % Activity is the percentage of DB Time due to the event with the given P1,P2,P3 Values. Event % Event P1, P2, P3 Values % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- direct path read temp 6.51 "201","1538653","31" 0.34 file number first dba block cnt direct path read 6.16 "5","336640","128" 0.34 file number first dba block cnt direct path write temp 3.77 "201","1101599","31" 0.34 file number first dba block cnt db file sequential read 1.37 "22","1942965","1" 0.34 file# block# blocks db file scattered read 1.03 "21","2003758","30" 0.34 file# block# blocks ------------------------------------------------------ Top DB Objects DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Top DB Objects by DB Time with respect to Application, Cluster, User I/O, buffer busy waits and In-Memory DB events only. -> Tablespace name is not available for reports generated from the root PDB of a consolidated database. -> When ** MISSING ** occurs, some of the object attributes may not be available Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- -------------------------- 154337 6.51 direct path read temp 5.48 FDW_DW.FDW_REL_PROD_OPE_COMPONENT (TABLE) D_FDW01 direct path read 1.03 700667 5.14 direct path read 4.45 FDW_DW.FDW_R_PRODUCT_UNIT (TABLE) D_FDW01 154268 2.74 direct path write temp 1.37 FDW_DW.FDW_R_ASSET_OPERATION (TABLE) D_FDW01 154338 1.71 direct path write temp 1.71 FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION (TABLE) D_FDW01 1726184 1.37 db file scattered read 1.03 FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION_PK (INDEX) I_FDW01 ------------------------------------------------------ Activity Over Time DB/Inst: RFDWSY20/RFDWSY20 Snaps: 17092-17094 -> Analysis period is divided into smaller time slots as indicated in the 'Slot Time (Duration)'. -> Top 3 events are reported in each of those slots -> 'Slot Count' shows the number of ASH samples in that slot -> 'Event Count' shows the number of ASH samples waiting for that event in that slot -> '% Event' is 'Event Count' over all ASH samples in the analysis period Slot Event Slot Time (Duration) Count Event Count % Event -------------------- -------- ------------------------------ -------- ------- 11:00:55 (9.1 min) 34 CPU + Wait for CPU 26 8.90 db file scattered read 3 1.03 direct path read 2 0.68 11:10:00 (10.0 min) 12 CPU + Wait for CPU 11 3.77 direct path read 1 0.34 11:20:00 (10.0 min) 44 CPU + Wait for CPU 31 10.62 direct path read temp 8 2.74 direct path read 2 0.68 11:30:00 (10.0 min) 27 CPU + Wait for CPU 23 7.88 direct path write temp 2 0.68 LGWR all worker groups 1 0.34 11:40:00 (10.0 min) 10 CPU + Wait for CPU 8 2.74 direct path read 1 0.34 latch free 1 0.34 11:50:00 (10.0 min) 8 CPU + Wait for CPU 7 2.40 log file sequential read 1 0.34 12:00:00 (10.0 min) 44 CPU + Wait for CPU 32 10.96 direct path read 4 1.37 SQL*Net more data to client 2 0.68 12:10:00 (10.0 min) 13 CPU + Wait for CPU 11 3.77 direct path read 2 0.68 12:20:00 (10.0 min) 54 CPU + Wait for CPU 38 13.01 direct path read temp 8 2.74 db file sequential read 3 1.03 12:30:00 (10.0 min) 24 CPU + Wait for CPU 20 6.85 direct path read 1 0.34 direct path read temp 1 0.34 12:40:00 (10.0 min) 12 CPU + Wait for CPU 10 3.42 direct path read 2 0.68 12:50:00 (10.0 min) 10 CPU + Wait for CPU 7 2.40 log file parallel write 2 0.68 direct path read 1 0.34 ------------------------------------------------------ ADDM Report for Task 'ADDM:3943921809_1_17094' ---------------------------------------------- Analysis Period --------------- AWR snapshot range from 17093 to 17094. Time period starts at 01-OCT-21 12.00.59 PM Time period ends at 01-OCT-21 01.00.03 PM Analysis Target --------------- Database 'RFDWSY20' with DB ID 3943921809. Database version 18.0.0.0.0. ADDM performed an analysis of instance RFDWSY20, numbered 1 and hosted at sy2siddifdwdbdw. Activity During the Analysis Period ----------------------------------- Total database time was 1271 seconds. The average number of active sessions was .36. Summary of Findings ------------------- Description Active Sessions Recommendation s Percent of Activity ---------------------------------------- ------------------- -------------- - 1 Top SQL Statements .27 | 75.38 4 2 Top Segments by "User I/O" and "Cluster" .06 | 16.67 3 3 Undersized PGA .04 | 11.88 0 4 Undersized SGA .02 | 4.49 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is .27 active sessions, 75.38% of total activity. -------------------------------------------------------- SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. Recommendation 1: SQL Tuning Estimated benefit is .1 active sessions, 28.46% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "4vrwkq6321kny". Related Object SQL statement with SQL_ID 4vrwkq6321kny. SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT_ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu.LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATION_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_ASSET_SUB_ASSEMBLY_ID, gen.CURRENT_OPERATION_ID, gen.CURRENT_INTERMEDIATE_PROCESS_ID, gen.COMPONENT_LOCAL_PRODUCT_REF_ID, tpu.PRODUCT_PRODUCTION_START_DATE AS INITIAL_PRODUCT_PRODUCTION_START_DATE, tpu.PRODUCT_PRODUCTION_END_DAY AS INITIAL_PRODUCT_PRODUCTION_END_DAY, tpu.PRODUCT_PRODUCTION_END_DATE AS INITIAL_PRODUCT_PRODUCTION_END_DATE, gen.CURRENT_OPERATION_START_DATE, gen.CURRENT_OPERATION_END_DATE, gen.CURRENT_PRODUCT_PRODUCTION_END_DAY, gen.CURRENT_PRODUCT_PRODUCTION_END_DATE, gen.COMPONENT_PRODUCT_PRODUCTION_START_DATE, gen.COMPONENT_PRODUCT_PRODUCTION_END_DATE FROM ( SELECT DISTINCT CONNECT_BY_ROOT pao.PRODUCT_UNIT_ID AS INITIAL_PRODUCT_UNIT_ID, CONNECT_BY_ROOT op.OPERATION_ID AS INITIAL_OPERATION_ID, pao.PRODUCT_UNIT_ID AS CURRENT_PRODUCT_UNIT_ID, pao.ASSET_OPERATION_ID AS CURRENT_ASSET_OPERATION_ID, nvl(poc.PRODUCT_UNIT_COMPONENT_ID,pao.PRODUCT_UNIT_ID) AS COMPONENT_PRODUCT_UNIT_ID, LEVEL AS HIERARCHY_LEVEL, pu.LOCAL_PRODUCT_REFERENCE_ID AS CURRENT_LOCAL_PRODUCT_REF_ID, ao.ASSET_ID AS CURRENT_ASSET_ID, nvl(ao.ASSET_SUB_ASSEMBLY_ID,ao.ASSET_ID) AS CURRENT_ASSET_SUB_ASSEMBLY_ID, ao.OPERATION_ID AS CURRENT_OPERATION_ID, ao.ASSET_OPERATION_START_DATE AS CURRENT_OPERATION_START_DATE, ao.ASSET_OPERATION_END_DATE AS CURRENT_OPERATION_END_DATE, pu.PRODUCT_PRODUCTION_END_DAY AS CURRENT_PRODUCT_PRODUCTION_END_DAY, pu.PRODUCT_PRODUCTION_END_DATE AS CURRENT_PRODUCT_PRODUCTION_END_DATE, op.INTERMEDIATE_PROCESS_ID AS CURRENT_INTERMEDIATE_PROCESS_ID, nvl(pu2.LOCAL_PRODUCT_REFERENCE_ID, pu.LOCAL_PRODUCT_REFERENCE_ID) AS COMPONENT_LOCAL_PRODUCT_REF_ID, nvl(pu2.PRODUCT_PRODUCTION_START_DATE,pu.PRODUCT_PRODUCTION_START_DAT E) AS COMPONENT_PRODUCT_PRODUCTION_START_DATE, nvl(pu2.PRODUCT_PRODUCTION_END_DATE,pu.PRODUCT_PRODUCTION_END_DATE) AS COMPONENT_PRODUCT_PRODUCTION_END_DATE FROM FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao LEFT OUTER JOIN FDW_DW.FDW_REL_PROD_OPE_COMPONENT poc ON pao.ASSET_OPERATION_ID = poc.ASSET_OPERATION_ID INNER JOIN FDW_DW.FDW_R_ASSET_OPERATION ao ON pao.ASSET_OPERATION_ID = ao.ASSET_OPERATION_ID INNER JOIN FDW_DW.FDW_R_PRODUCT_UNIT pu ON pao.PRODUCT_UNIT_ID = pu.PRODUCT_UNIT_ID LEFT OUTER JOIN FDW_DW.FDW_R_PRODUCT_UNIT pu2 ON poc.PRODUCT_UNIT_COMPONENT_ID = pu2.PRODUCT_UNIT_ID INNER JOIN FDW_DW.FDW_R_OPERATION op ON ao.OPERATION_ID = op.OPERATION_ID START WITH pao.PRODUCT_UNIT_ID IN ( SELECT PRODUCT_UNIT_ID FROM FDW_DW.fdw_rel_product_asset_operation pao LEFT OUTER JOIN FDW_DW.fdw_rel_prod_ope_component poc on pao.asset_operation_id = poc.asset_operation_id WHERE pao.create_date BETWEEN TO_DATE('01/10/2021 05:20:12','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:20:09','DD/MM/YYYY HH24:MI:SS') OR poc.create_date BETWEEN TO_DATE('01/10/2021 05:20:12','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 12:20:09','DD/MM/YYYY HH24:MI:SS') ) CONNECT BY PRIOR poc.PRODUCT_UNIT_COMPONENT_ID = pao.PRODUCT_UNIT_ID ) gen, FDW_DW.FDW_R_PRODUCT_UNIT tpu WHERE gen.INITIAL_PRODUCT_UNIT_ID = tpu.PRODUCT_UNIT_ID Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "4vrwkq6321kny" was executed 1 times and had an average elapsed time of 372 seconds. Rationale I/O and Cluster wait for TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337 consumed 78% of the database time spent on this SQL statement. Rationale Full scan of TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337 consumed 27% of the database time spent on this SQL statement. Recommendation 2: SQL Tuning Estimated benefit is .08 active sessions, 22.31% of total activity. ------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "89cf6rrncn00x". Related Object SQL statement with SQL_ID 89cf6rrncn00x. SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERATION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATION_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PRODUCT_UNIT_ID = pu.PRODUCT_UNIT_ID WHERE ao.SOURCE_APP = 'GIRAFAVC' and ao.SITE_ACTIVITY_ID = 'SY2 TC' group by ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER Rationale The SQL spent 96% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "89cf6rrncn00x" was executed 2 times and had an average elapsed time of 156 seconds. Rationale I/O and Cluster wait for TABLE "FDW_DW.FDW_R_ASSET_OPERATION" with object ID 154268 consumed 70% of the database time spent on this SQL statement. Rationale I/O and Cluster wait for TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667 consumed 16% of the database time spent on this SQL statement. Recommendation 3: SQL Tuning Estimated benefit is .05 active sessions, 14.62% of total activity. ------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "3d0cwt2sd9sbp". Related Object SQL statement with SQL_ID 3d0cwt2sd9sbp. with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept, FDW_DW.FDW_R_ASSET_LOOKUP look Where asept.SITE_ACTIVITY_ID = :siteparam and asept.ASSET_ID = look.ASSET_ID and asept.ASSET_REFERENCE_ID = assref.ASSET_REFERENCE_ID and assref.INTERMEDIATE_PROCESS_ID = inter.INTERMEDIATE_PROCESS_ID and inter.PROCESS_ID = pro.PROCESS_ID and pro.PROCESS_SEGMENT_ID = seg.PROCESS_SEGMENT_ID and seg.PROCESS_SEGMENT_DESC = 'PREPARATION' ), dossier as ( select distinct(dos.LOCAL_PRODUCT_REFERENCE_ID) as doslpr, ass.machine as mac from FDW_DW.FDW_R_PDP_SCHEDULE pdps, FDW_DW.FDW_R_PDP pdp, FDW_DW.FDW_R_GAMME gam, FDW_DW.FDW_R_INDUS ind, FDW_DW.FDW_R_DOSSIER dos, assetprep ass where pdps.SOURCE_APP='FYT' and pdps.SITE_ACTIVITY_ID= :siteparam and pdps.PDP_ID = pdp.PDP_ID and pdp.GAMME_ID = gam.GAMME_ID and gam.INDUS_ID = ind.INDUS_ID and ind.DOSSIER_ID = dos.DOSSIER_ID and pdps.ASSET_ID = ass.id), prod as ( SELECT STOCK.local_product_reference_id PROD_ID, STOCK.LOCAL_PRODUCT_STOCK_QTY PROD_QTE, STOCK.LOCAL_PRODUCT_STOCK_DATE PROD_DATE, fam.PRODUCT_FAMILY_ID prod, asset.ASSET_LOOKUP_APPLICATION_VALUE as machine FROM fdw_dw.fdw_f_local_product_stock_detail STOCK, fdw_dw.fdw_r_generic_article genart, FDW_DW.FDW_R_PRODUCT_FAMILY fam, FDW_DW.FDW_R_ASSET_LOOKUP asset, dossier dos WHERE dos.doslpr = STOCK.local_product_reference_id and STOCK.ASSET_ID = asset.ASSET_ID and STOCK.local_product_reference_id = genart.GENERIC_ARTICLE_ID and Fam.PRODUCT_FAMILY_ID = genart.PRODUCT_FAMILY_ID ), stock as ( SELECT S.prod ID, S.PROD_ID prodid, sum(S.PROD_QTE) QTE from prod S where S.PROD_DATE = ( select max(ST.local_product_stock_date) from fdw_dw.fdw_f_local_product_stock_detail ST where S.PROD_ID = ST.local_product_reference_id) group by S.PROD_ID, s.prod ) Select S.ID ArticleCode, S.QTE TotalStock, asset.mac Machine, Sysdate DateSave From stock S, dossier asset where s.prodid = asset.doslpr Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "3d0cwt2sd9sbp" was executed 10 times and had an average elapsed time of 19 seconds. Recommendation 4: SQL Tuning Estimated benefit is .03 active sessions, 9.23% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "frrjd8g9c44yd". Related Object SQL statement with SQL_ID frrjd8g9c44yd. with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept, FDW_DW.FDW_R_ASSET_LOOKUP look Where asept.SITE_ACTIVITY_ID = :siteparam and asept.ASSET_ID = look.ASSET_ID and asept.ASSET_REFERENCE_ID = assref.ASSET_REFERENCE_ID and assref.INTERMEDIATE_PROCESS_ID = inter.INTERMEDIATE_PROCESS_ID and inter.PROCESS_ID = pro.PROCESS_ID and pro.PROCESS_SEGMENT_ID = seg.PROCESS_SEGMENT_ID and seg.PROCESS_SEGMENT_DESC = 'PREPARATION' ), dossier as ( select distinct(dos.LOCAL_PRODUCT_REFERENCE_ID) as doslpr, ass.machine as mac from FDW_DW.FDW_R_PDP_SCHEDULE pdps, FDW_DW.FDW_R_PDP pdp, FDW_DW.FDW_R_GAMME gam, FDW_DW.FDW_R_INDUS ind, FDW_DW.FDW_R_DOSSIER dos, assetprep ass where pdps.SOURCE_APP='FYT' and pdps.SITE_ACTIVITY_ID= :siteparam and pdps.PDP_ID = pdp.PDP_ID and pdp.GAMME_ID = gam.GAMME_ID and gam.INDUS_ID = ind.INDUS_ID and ind.DOSSIER_ID = dos.DOSSIER_ID and pdps.ASSET_ID = ass.id ) select distinct(family.PRODUCT_FAMILY_ID) ArticleCode, prodUnit.PRODUCT_PRODUCTION_START_DATE DatePreparation, prodUnit.PRODUCT_SERIAL_NUMBER numser, family.PRODUCT_TYPE_ID Type, dos.mac machine from FDW_DW.FDW_REL_GENERIC_ARTICLE_LPR prodlpr, FDW_DW.FDW_R_GENERIC_ARTICLE prod, FDW_DW.FDW_R_PRODUCT_UNIT prodUnit, FDW_DW.FDW_R_PRODUCT_FAMILY family, dossier dos where prodlpr.GENERIC_ARTICLE_ID = prod.GENERIC_ARTICLE_ID and prodlpr.LOCAL_PRODUCT_REFERENCE_ID = prodUnit.LOCAL_PRODUCT_REFERENCE_ID and prod.PRODUCT_FAMILY_ID = family.PRODUCT_FAMILY_ID and prodUnit.PRODUCT_PRODUCTION_START_DATE >= sysdate-1 and prodlpr.LOCAL_PRODUCT_REFERENCE_ID = dos.doslpr Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "frrjd8g9c44yd" was executed 10 times and had an average elapsed time of 12 seconds. Rationale At least 2 distinct execution plans were utilized for this SQL statement during the analysis period. Rationale Full scan of TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667 consumed 83% of the database time spent on this SQL statement. Finding 2: Top Segments by "User I/O" and "Cluster" Impact is .06 active sessions, 16.67% of total activity. -------------------------------------------------------- Individual database segments responsible for significant "User I/O" and "Cluster" waits were found. Recommendation 1: Segment Tuning Estimated benefit is .02 active sessions, 6.52% of total activity. ------------------------------------------------------------------ Action Investigate application logic involving I/O on TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337. Related Object Database object with ID 154337. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the SELECT statement with SQL_ID "4vrwkq6321kny" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Rationale The I/O usage statistics for the object are: 1 full object scans, 6640481 physical reads, 26 physical writes and 6640481 direct reads. Recommendation 2: Segment Tuning Estimated benefit is .02 active sessions, 6.52% of total activity. ------------------------------------------------------------------ Action Run "Segment Advisor" on TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667. Related Object Database object with ID 700667. Action Investigate application logic involving I/O on TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667. Related Object Database object with ID 700667. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the SELECT statement with SQL_ID "frrjd8g9c44yd" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Rationale The I/O usage statistics for the object are: 17 full object scans, 22280406 physical reads, 5904 physical writes and 22280404 direct reads. Recommendation 3: Segment Tuning Estimated benefit is .01 active sessions, 3.62% of total activity. ------------------------------------------------------------------ Action Run "Segment Advisor" on TABLE "FDW_DW.FDW_R_ASSET_OPERATION" with object ID 154268. Related Object Database object with ID 154268. Action Investigate application logic involving I/O on TABLE "FDW_DW.FDW_R_ASSET_OPERATION" with object ID 154268. Related Object Database object with ID 154268. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the SELECT statement with SQL_ID "89cf6rrncn00x" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Rationale The I/O usage statistics for the object are: 2 full object scans, 6568240 physical reads, 6048 physical writes and 6568238 direct reads. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 21.02% of total activity. Finding 3: Undersized PGA Impact is .04 active sessions, 11.88% of total activity. -------------------------------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. The value of parameter "pga_aggregate_target" was "18432 M" during the analysis period. No recommendations are available. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 21.02% of total activity. Finding 4: Undersized SGA Impact is .02 active sessions, 4.49% of total activity. ------------------------------------------------------- The SGA was inadequately sized, causing additional I/O or hard parses. The value of parameter "sga_target" was "98304 M" during the analysis period. Recommendation 1: Database Configuration Estimated benefit is .02 active sessions, 4.41% of total activity. ------------------------------------------------------------------ Action Increase the size of the SGA by setting the parameter "sga_target" to 104448 M. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 21.02% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. CPU was not a bottleneck for the instance. Wait class "Network" was not consuming significant database time. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time. ADDM Report for Task 'ADDM:3943921809_1_17093' ---------------------------------------------- Analysis Period --------------- AWR snapshot range from 17092 to 17093. Time period starts at 01-OCT-21 11.00.55 AM Time period ends at 01-OCT-21 12.00.59 PM Analysis Target --------------- Database 'RFDWSY20' with DB ID 3943921809. Database version 18.0.0.0.0. ADDM performed an analysis of instance RFDWSY20, numbered 1 and hosted at sy2siddifdwdbdw. Activity During the Analysis Period ----------------------------------- Total database time was 1256 seconds. The average number of active sessions was .35. Summary of Findings ------------------- Description Active Sessions Recommendation s Percent of Activity ---------------------------------------- ------------------- -------------- - 1 Top SQL Statements .3 | 85.37 4 2 Top Segments by "User I/O" and "Cluster" .05 | 14.06 2 3 Undersized PGA .04 | 10.98 0 4 Undersized SGA .02 | 4.46 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is .3 active sessions, 85.37% of total activity. ------------------------------------------------------- SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. Recommendation 1: SQL Tuning Estimated benefit is .1 active sessions, 28.46% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "89cf6rrncn00x". Related Object SQL statement with SQL_ID 89cf6rrncn00x. SELECT ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER, MAX(ao.ASSET_OPERATION_START_DATE) ASSET_OPERATION_START_DATE FROM FDW_DW.FDW_R_ASSET_OPERATION ao inner join FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao on ao.ASSET_OPERATION_ID = pao.ASSET_OPERATION_ID inner join FDW_DW.FDW_R_PRODUCT_UNIT pu on pao.PRODUCT_UNIT_ID = pu.PRODUCT_UNIT_ID WHERE ao.SOURCE_APP = 'GIRAFAVC' and ao.SITE_ACTIVITY_ID = 'SY2 TC' group by ao.ASSET_ID, ao.OPERATION_ID, pu.PRODUCT_SERIAL_NUMBER Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "89cf6rrncn00x" was executed 2 times and had an average elapsed time of 172 seconds. Recommendation 2: SQL Tuning Estimated benefit is .1 active sessions, 28.46% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "ddhc1697xfnv7". Related Object SQL statement with SQL_ID ddhc1697xfnv7. SELECT gen.INITIAL_PRODUCT_UNIT_ID, gen.CURRENT_PRODUCT_UNIT_ID, gen.CURRENT_ASSET_OPERATION_ID, gen.COMPONENT_PRODUCT_UNIT_ID, gen.HIERARCHY_LEVEL, tpu.LOCAL_PRODUCT_REFERENCE_ID AS INITIAL_LOCAL_PRODUCT_REF_ID, gen.INITIAL_OPERATION_ID, gen.CURRENT_LOCAL_PRODUCT_REF_ID, gen.CURRENT_ASSET_ID, gen.CURRENT_ASSET_SUB_ASSEMBLY_ID, gen.CURRENT_OPERATION_ID, gen.CURRENT_INTERMEDIATE_PROCESS_ID, gen.COMPONENT_LOCAL_PRODUCT_REF_ID, tpu.PRODUCT_PRODUCTION_START_DATE AS INITIAL_PRODUCT_PRODUCTION_START_DATE, tpu.PRODUCT_PRODUCTION_END_DAY AS INITIAL_PRODUCT_PRODUCTION_END_DAY, tpu.PRODUCT_PRODUCTION_END_DATE AS INITIAL_PRODUCT_PRODUCTION_END_DATE, gen.CURRENT_OPERATION_START_DATE, gen.CURRENT_OPERATION_END_DATE, gen.CURRENT_PRODUCT_PRODUCTION_END_DAY, gen.CURRENT_PRODUCT_PRODUCTION_END_DATE, gen.COMPONENT_PRODUCT_PRODUCTION_START_DATE, gen.COMPONENT_PRODUCT_PRODUCTION_END_DATE FROM ( SELECT DISTINCT CONNECT_BY_ROOT pao.PRODUCT_UNIT_ID AS INITIAL_PRODUCT_UNIT_ID, CONNECT_BY_ROOT op.OPERATION_ID AS INITIAL_OPERATION_ID, pao.PRODUCT_UNIT_ID AS CURRENT_PRODUCT_UNIT_ID, pao.ASSET_OPERATION_ID AS CURRENT_ASSET_OPERATION_ID, nvl(poc.PRODUCT_UNIT_COMPONENT_ID,pao.PRODUCT_UNIT_ID) AS COMPONENT_PRODUCT_UNIT_ID, LEVEL AS HIERARCHY_LEVEL, pu.LOCAL_PRODUCT_REFERENCE_ID AS CURRENT_LOCAL_PRODUCT_REF_ID, ao.ASSET_ID AS CURRENT_ASSET_ID, nvl(ao.ASSET_SUB_ASSEMBLY_ID,ao.ASSET_ID) AS CURRENT_ASSET_SUB_ASSEMBLY_ID, ao.OPERATION_ID AS CURRENT_OPERATION_ID, ao.ASSET_OPERATION_START_DATE AS CURRENT_OPERATION_START_DATE, ao.ASSET_OPERATION_END_DATE AS CURRENT_OPERATION_END_DATE, pu.PRODUCT_PRODUCTION_END_DAY AS CURRENT_PRODUCT_PRODUCTION_END_DAY, pu.PRODUCT_PRODUCTION_END_DATE AS CURRENT_PRODUCT_PRODUCTION_END_DATE, op.INTERMEDIATE_PROCESS_ID AS CURRENT_INTERMEDIATE_PROCESS_ID, nvl(pu2.LOCAL_PRODUCT_REFERENCE_ID, pu.LOCAL_PRODUCT_REFERENCE_ID) AS COMPONENT_LOCAL_PRODUCT_REF_ID, nvl(pu2.PRODUCT_PRODUCTION_START_DATE,pu.PRODUCT_PRODUCTION_START_DAT E) AS COMPONENT_PRODUCT_PRODUCTION_START_DATE, nvl(pu2.PRODUCT_PRODUCTION_END_DATE,pu.PRODUCT_PRODUCTION_END_DATE) AS COMPONENT_PRODUCT_PRODUCTION_END_DATE FROM FDW_DW.FDW_REL_PRODUCT_ASSET_OPERATION pao LEFT OUTER JOIN FDW_DW.FDW_REL_PROD_OPE_COMPONENT poc ON pao.ASSET_OPERATION_ID = poc.ASSET_OPERATION_ID INNER JOIN FDW_DW.FDW_R_ASSET_OPERATION ao ON pao.ASSET_OPERATION_ID = ao.ASSET_OPERATION_ID INNER JOIN FDW_DW.FDW_R_PRODUCT_UNIT pu ON pao.PRODUCT_UNIT_ID = pu.PRODUCT_UNIT_ID LEFT OUTER JOIN FDW_DW.FDW_R_PRODUCT_UNIT pu2 ON poc.PRODUCT_UNIT_COMPONENT_ID = pu2.PRODUCT_UNIT_ID INNER JOIN FDW_DW.FDW_R_OPERATION op ON ao.OPERATION_ID = op.OPERATION_ID START WITH pao.PRODUCT_UNIT_ID IN ( SELECT PRODUCT_UNIT_ID FROM FDW_DW.fdw_rel_product_asset_operation pao LEFT OUTER JOIN FDW_DW.fdw_rel_prod_ope_component poc on pao.asset_operation_id = poc.asset_operation_id WHERE pao.create_date BETWEEN TO_DATE('01/10/2021 04:20:09','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 11:20:12','DD/MM/YYYY HH24:MI:SS') OR poc.create_date BETWEEN TO_DATE('01/10/2021 04:20:09','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('01/10/2021 11:20:12','DD/MM/YYYY HH24:MI:SS') ) CONNECT BY PRIOR poc.PRODUCT_UNIT_COMPONENT_ID = pao.PRODUCT_UNIT_ID ) gen, FDW_DW.FDW_R_PRODUCT_UNIT tpu WHERE gen.INITIAL_PRODUCT_UNIT_ID = tpu.PRODUCT_UNIT_ID Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "ddhc1697xfnv7" was executed 1 times and had an average elapsed time of 356 seconds. Rationale I/O and Cluster wait for TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337 consumed 80% of the database time spent on this SQL statement. Rationale Full scan of TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337 consumed 28% of the database time spent on this SQL statement. Recommendation 3: SQL Tuning Estimated benefit is .06 active sessions, 17.07% of total activity. ------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "3d0cwt2sd9sbp". Related Object SQL statement with SQL_ID 3d0cwt2sd9sbp. with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept, FDW_DW.FDW_R_ASSET_LOOKUP look Where asept.SITE_ACTIVITY_ID = :siteparam and asept.ASSET_ID = look.ASSET_ID and asept.ASSET_REFERENCE_ID = assref.ASSET_REFERENCE_ID and assref.INTERMEDIATE_PROCESS_ID = inter.INTERMEDIATE_PROCESS_ID and inter.PROCESS_ID = pro.PROCESS_ID and pro.PROCESS_SEGMENT_ID = seg.PROCESS_SEGMENT_ID and seg.PROCESS_SEGMENT_DESC = 'PREPARATION' ), dossier as ( select distinct(dos.LOCAL_PRODUCT_REFERENCE_ID) as doslpr, ass.machine as mac from FDW_DW.FDW_R_PDP_SCHEDULE pdps, FDW_DW.FDW_R_PDP pdp, FDW_DW.FDW_R_GAMME gam, FDW_DW.FDW_R_INDUS ind, FDW_DW.FDW_R_DOSSIER dos, assetprep ass where pdps.SOURCE_APP='FYT' and pdps.SITE_ACTIVITY_ID= :siteparam and pdps.PDP_ID = pdp.PDP_ID and pdp.GAMME_ID = gam.GAMME_ID and gam.INDUS_ID = ind.INDUS_ID and ind.DOSSIER_ID = dos.DOSSIER_ID and pdps.ASSET_ID = ass.id), prod as ( SELECT STOCK.local_product_reference_id PROD_ID, STOCK.LOCAL_PRODUCT_STOCK_QTY PROD_QTE, STOCK.LOCAL_PRODUCT_STOCK_DATE PROD_DATE, fam.PRODUCT_FAMILY_ID prod, asset.ASSET_LOOKUP_APPLICATION_VALUE as machine FROM fdw_dw.fdw_f_local_product_stock_detail STOCK, fdw_dw.fdw_r_generic_article genart, FDW_DW.FDW_R_PRODUCT_FAMILY fam, FDW_DW.FDW_R_ASSET_LOOKUP asset, dossier dos WHERE dos.doslpr = STOCK.local_product_reference_id and STOCK.ASSET_ID = asset.ASSET_ID and STOCK.local_product_reference_id = genart.GENERIC_ARTICLE_ID and Fam.PRODUCT_FAMILY_ID = genart.PRODUCT_FAMILY_ID ), stock as ( SELECT S.prod ID, S.PROD_ID prodid, sum(S.PROD_QTE) QTE from prod S where S.PROD_DATE = ( select max(ST.local_product_stock_date) from fdw_dw.fdw_f_local_product_stock_detail ST where S.PROD_ID = ST.local_product_reference_id) group by S.PROD_ID, s.prod ) Select S.ID ArticleCode, S.QTE TotalStock, asset.mac Machine, Sysdate DateSave From stock S, dossier asset where s.prodid = asset.doslpr Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "3d0cwt2sd9sbp" was executed 11 times and had an average elapsed time of 19 seconds. Recommendation 4: SQL Tuning Estimated benefit is .04 active sessions, 11.38% of total activity. ------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "frrjd8g9c44yd". Related Object SQL statement with SQL_ID frrjd8g9c44yd. with assetprep as ( select distinct(asept.ASSET_ID) id , look.ASSET_LOOKUP_APPLICATION_VALUE machine from FDW_DW.FDW_R_PROCESS_SEGMENT seg, FDW_DW.FDW_R_PROCESS pro, FDW_DW.FDW_R_INTERMEDIATE_PROCESS inter, FDW_DW.FDW_R_ASSET_REFERENCE assref, FDW_DW.FDW_R_ASSET asept, FDW_DW.FDW_R_ASSET_LOOKUP look Where asept.SITE_ACTIVITY_ID = :siteparam and asept.ASSET_ID = look.ASSET_ID and asept.ASSET_REFERENCE_ID = assref.ASSET_REFERENCE_ID and assref.INTERMEDIATE_PROCESS_ID = inter.INTERMEDIATE_PROCESS_ID and inter.PROCESS_ID = pro.PROCESS_ID and pro.PROCESS_SEGMENT_ID = seg.PROCESS_SEGMENT_ID and seg.PROCESS_SEGMENT_DESC = 'PREPARATION' ), dossier as ( select distinct(dos.LOCAL_PRODUCT_REFERENCE_ID) as doslpr, ass.machine as mac from FDW_DW.FDW_R_PDP_SCHEDULE pdps, FDW_DW.FDW_R_PDP pdp, FDW_DW.FDW_R_GAMME gam, FDW_DW.FDW_R_INDUS ind, FDW_DW.FDW_R_DOSSIER dos, assetprep ass where pdps.SOURCE_APP='FYT' and pdps.SITE_ACTIVITY_ID= :siteparam and pdps.PDP_ID = pdp.PDP_ID and pdp.GAMME_ID = gam.GAMME_ID and gam.INDUS_ID = ind.INDUS_ID and ind.DOSSIER_ID = dos.DOSSIER_ID and pdps.ASSET_ID = ass.id ) select distinct(family.PRODUCT_FAMILY_ID) ArticleCode, prodUnit.PRODUCT_PRODUCTION_START_DATE DatePreparation, prodUnit.PRODUCT_SERIAL_NUMBER numser, family.PRODUCT_TYPE_ID Type, dos.mac machine from FDW_DW.FDW_REL_GENERIC_ARTICLE_LPR prodlpr, FDW_DW.FDW_R_GENERIC_ARTICLE prod, FDW_DW.FDW_R_PRODUCT_UNIT prodUnit, FDW_DW.FDW_R_PRODUCT_FAMILY family, dossier dos where prodlpr.GENERIC_ARTICLE_ID = prod.GENERIC_ARTICLE_ID and prodlpr.LOCAL_PRODUCT_REFERENCE_ID = prodUnit.LOCAL_PRODUCT_REFERENCE_ID and prod.PRODUCT_FAMILY_ID = family.PRODUCT_FAMILY_ID and prodUnit.PRODUCT_PRODUCTION_START_DATE >= sysdate-1 and prodlpr.LOCAL_PRODUCT_REFERENCE_ID = dos.doslpr Rationale The SQL spent 100% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Rationale Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "frrjd8g9c44yd" was executed 11 times and had an average elapsed time of 13 seconds. Rationale At least 2 distinct execution plans were utilized for this SQL statement during the analysis period. Rationale Full scan of TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667 consumed 85% of the database time spent on this SQL statement. Finding 2: Top Segments by "User I/O" and "Cluster" Impact is .05 active sessions, 14.06% of total activity. -------------------------------------------------------- Individual database segments responsible for significant "User I/O" and "Cluster" waits were found. Recommendation 1: Segment Tuning Estimated benefit is .03 active sessions, 8.78% of total activity. ------------------------------------------------------------------ Action Run "Segment Advisor" on TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337. Related Object Database object with ID 154337. Action Investigate application logic involving I/O on TABLE "FDW_DW.FDW_REL_PROD_OPE_COMPONENT" with object ID 154337. Related Object Database object with ID 154337. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the SELECT statement with SQL_ID "ddhc1697xfnv7" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Rationale The I/O usage statistics for the object are: 1 full object scans, 6640465 physical reads, 46 physical writes and 6640465 direct reads. Recommendation 2: Segment Tuning Estimated benefit is .02 active sessions, 5.27% of total activity. ------------------------------------------------------------------ Action Run "Segment Advisor" on TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667. Related Object Database object with ID 700667. Action Investigate application logic involving I/O on TABLE "FDW_DW.FDW_R_PRODUCT_UNIT" with object ID 700667. Related Object Database object with ID 700667. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the SELECT statement with SQL_ID "frrjd8g9c44yd" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Rationale The I/O usage statistics for the object are: 19 full object scans, 24903231 physical reads, 6078 physical writes and 24901628 direct reads. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 22.84% of total activity. Finding 3: Undersized PGA Impact is .04 active sessions, 10.98% of total activity. -------------------------------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. The value of parameter "pga_aggregate_target" was "18432 M" during the analysis period. No recommendations are available. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 22.84% of total activity. Finding 4: Undersized SGA Impact is .02 active sessions, 4.46% of total activity. ------------------------------------------------------- The SGA was inadequately sized, causing additional I/O or hard parses. The value of parameter "sga_target" was "98304 M" during the analysis period. Recommendation 1: Database Configuration Estimated benefit is .02 active sessions, 4.46% of total activity. ------------------------------------------------------------------ Action Increase the size of the SGA by setting the parameter "sga_target" to 104448 M. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .08 active sessions, 22.84% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. CPU was not a bottleneck for the instance. Wait class "Network" was not consuming significant database time. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time. End of Report