STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1487638468 FREE 08-Nov-25 06:59 23.0.0.0.0 NO Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- ------------ localhost.locald Linux x86 64-bit 6 6 1 15.2 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------- ----------- Begin Snap: 106 08-Nov-25 08:15:00 21 2.9 End Snap: 107 08-Nov-25 08:30:04 21 2.5 Elapsed: 15.07 (mins) Av Act Sess: 10.6 DB time: 159.51 (mins) DB CPU: 12.75 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 528M Std Block Size: 8K Shared Pool: 880M Log Buffer: 4,192K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 10.6 0.5 0.01 0.09 DB CPU(s): 0.9 0.0 0.00 0.01 Redo size: 150,624.6 6,354.5 Logical reads: 14,104.4 595.0 Block changes: 931.4 39.3 Physical reads: 625.5 26.4 Physical writes: 0.2 0.0 User calls: 119.3 5.0 Parses: 50.2 2.1 Hard parses: 0.0 0.0 W/A MB processed: 0.1 0.0 Logons: 0.0 0.0 Executes: 717.5 30.3 Rollbacks: 0.0 0.0 Transactions: 23.7 Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 99.99 Buffer Hit %: 95.57 Optimal W/A Exec %: 100.00 Library Hit %: 100.01 Soft Parse %: 100.00 Execute to Parse %: 93.01 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 41.92 % Non-Parse CPU: 99.57 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 64.01 64.11 % SQL with executions>1: 59.81 59.84 % Memory for SQL w/exec>1: 56.77 56.78 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ free buffer waits 308,144 4,244 14 42.5 log file sync 21,889 1,672 76 16.7 write complete waits 384 1,631 4248 16.3 CPU time 618 6.2 enq: CR - block range reuse ckpt 31 590 19040 5.9 ------------------------------------------------------------- Host CPU (CPUs: 6 Cores: 6 Sockets: 1) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 7.43 6.38 3.32 3.61 39.35 5.11 Note: There is a 22% discrepancy between the OS Stat total CPU time and the total CPU time estimated by Statspack OS Stat CPU time: 4239(s) (BUSY_TIME + IDLE_TIME) Statspack CPU time: 5424(s) (Elapsed time * num CPUs in end snap) Instance CPU ~~~~~~~~~~~~ % Time (seconds) -------- -------------- Host: Total time (s): 4,239.0 Host: Busy CPU time (s): 2,570.8 % of time Host is Busy: 60.6 Instance: Total CPU time (s): 765.1 % of Busy CPU used for Instance: 29.8 Instance: Total Database time (s): 9,570.5 %DB time waiting for CPU (Resource Mgr): 0.0 Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 15,537.2 15,537.2 SGA use (MB): 1,529.1 1,529.1 PGA use (MB): 120.6 110.7 % Host Mem used for SGA+PGA: 10.6 10.6 ------------------------------------------------------------- Time Model System Stats DB/Inst: FREE/FREE Snaps: 106-107 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % DB time ----------------------------------- -------------------- --------- sql execute elapsed time 7,797.8 81.5 DB CPU 765.1 8.0 PL/SQL execution elapsed time 205.2 2.1 parse time elapsed 6.7 .1 repeated bind elapsed time 0.1 .0 DB time 9,570.5 ------------------------------------------------------------- Foreground Wait Events DB/Inst: FREE/FREE Snaps: 106-107 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ free buffer waits 307,804 0 4,204 14 14.4 42.1 log file sync 21,877 0 1,672 76 1.0 16.7 write complete waits 384 0 1,631 4248 0.0 16.3 buffer busy waits 513 0 577 1124 0.0 5.8 db file sequential read 413,202 0 416 1 19.3 4.2 db file parallel read 5,191 0 37 7 0.2 .4 resmgr:cpu quantum 1,319 0 31 23 0.1 .3 latch: cache buffers lru cha 2,576 0 21 8 0.1 .2 enq: TX - contention 5 0 18 3601 0.0 .2 library cache: mutex X 1,249 0 18 14 0.1 .2 enq: FB - contention 5 0 10 2063 0.0 .1 latch free 717 0 6 8 0.0 .1 enq: TX - index contention 10 0 5 496 0.0 .0 latch: cache buffers chains 394 0 3 8 0.0 .0 Free private memory to OS 6,308 0 2 0 0.3 .0 direct path write 29 0 2 54 0.0 .0 log file switch (private str 12 0 1 122 0.0 .0 cursor: pin S 206 0 1 7 0.0 .0 latch: In memory undo latch 182 0 1 7 0.0 .0 Allocate UGA memory from OS 7,574 0 1 0 0.4 .0 latch: enqueue hash chains 144 0 1 8 0.0 .0 latch: redo allocation 83 0 1 8 0.0 .0 library cache: bucket mutex 28 0 0 12 0.0 .0 cursor: mutex X 2,206 0 0 0 0.1 .0 cursor: mutex S 46 0 0 6 0.0 .0 latch: object queue header o 36 0 0 7 0.0 .0 read by other session 20 0 0 11 0.0 .0 lock deadlock retry 2,942 100 0 0 0.1 .0 latch: redo copy 14 0 0 12 0.0 .0 Allocate PGA memory from OS 287 0 0 0 0.0 .0 latch: session allocation 7 0 0 7 0.0 .0 latch: undo global data 6 0 0 6 0.0 .0 enq: HW - contention 5 0 0 5 0.0 .0 row cache lock 1 0 0 9 0.0 .0 rdbms ipc reply 1 0 0 9 0.0 .0 latch: messages 1 0 0 6 0.0 .0 SQL*Net message from client 64,737 0 4,807 74 3.0 PL/SQL lock timer 293 100 880 3004 0.0 SQL*Net message to client 64,724 0 5 0 3.0 ------------------------------------------------------------- Background Wait Events DB/Inst: FREE/FREE Snaps: 106-107 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ enq: CR - block range reuse 31 0 590 19040 0.0 5.9 enq: HW - contention 1 0 56 56021 0.0 .6 free buffer waits 340 0 40 119 0.0 .4 buffer busy waits 9 0 12 1358 0.0 .1 Data file init write 66 0 3 41 0.0 .0 DLM cross inst call completi 1 0 2 2390 0.0 .0 resmgr:cpu quantum 4 0 1 175 0.0 .0 latch: cache buffers lru cha 48 0 1 15 0.0 .0 log file sync 12 0 1 58 0.0 .0 direct path sync 1 0 1 561 0.0 .0 lock deadlock retry 1,806 100 0 0 0.1 .0 reliable message 30 0 0 4 0.0 .0 db file sequential read 1,321 0 0 0 0.1 .0 control file parallel write 3 0 0 12 0.0 .0 Disk file operations I/O 25 0 0 1 0.0 .0 datafile move cleanup during 1 0 0 11 0.0 .0 latch free 1 0 0 10 0.0 .0 row cache lock 1 0 0 8 0.0 .0 db file single write 1 0 0 8 0.0 .0 latch: enqueue hash chains 1 0 0 6 0.0 .0 db file scattered read 52 0 0 0 0.0 .0 read by other session 1 0 0 6 0.0 .0 Allocate CGA memory from OS 7 0 0 1 0.0 .0 latch: cache buffers chains 1 0 0 4 0.0 .0 Allocate UGA memory from OS 11 0 0 0 0.0 .0 asynch descriptor resize 12 100 0 0 0.0 .0 KSV master wait 1 100 0 14 0.0 ------------------------------------------------------------- Wait Events (fg and bg) DB/Inst: FREE/FREE Snaps: 106-107 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ free buffer waits 308,144 0 4,244 14 14.4 42.5 log file sync 21,889 0 1,672 76 1.0 16.7 write complete waits 384 0 1,631 4248 0.0 16.3 enq: CR - block range reuse 31 0 590 19040 0.0 5.9 buffer busy waits 522 0 589 1128 0.0 5.9 db file sequential read 414,523 0 416 1 19.3 4.2 enq: HW - contention 6 0 56 9341 0.0 .6 db file parallel read 5,191 0 37 7 0.2 .4 resmgr:cpu quantum 1,323 0 31 24 0.1 .3 latch: cache buffers lru cha 2,624 0 22 8 0.1 .2 enq: TX - contention 5 0 18 3601 0.0 .2 library cache: mutex X 1,249 0 18 14 0.1 .2 enq: FB - contention 5 0 10 2063 0.0 .1 latch free 718 0 6 8 0.0 .1 enq: TX - index contention 10 0 5 496 0.0 .0 latch: cache buffers chains 395 0 3 8 0.0 .0 Data file init write 66 0 3 41 0.0 .0 DLM cross inst call completi 1 0 2 2390 0.0 .0 Free private memory to OS 6,308 0 2 0 0.3 .0 direct path write 29 0 2 54 0.0 .0 log file switch (private str 12 0 1 122 0.0 .0 cursor: pin S 206 0 1 7 0.0 .0 latch: In memory undo latch 182 0 1 7 0.0 .0 Allocate UGA memory from OS 7,585 0 1 0 0.4 .0 latch: enqueue hash chains 145 0 1 8 0.0 .0 latch: redo allocation 83 0 1 8 0.0 .0 direct path sync 1 0 1 561 0.0 .0 lock deadlock retry 4,748 100 0 0 0.2 .0 library cache: bucket mutex 28 0 0 12 0.0 .0 cursor: mutex X 2,206 0 0 0 0.1 .0 cursor: mutex S 46 0 0 6 0.0 .0 latch: object queue header o 36 0 0 7 0.0 .0 read by other session 21 0 0 11 0.0 .0 latch: redo copy 14 0 0 12 0.0 .0 reliable message 30 0 0 4 0.0 .0 Allocate PGA memory from OS 329 0 0 0 0.0 .0 latch: session allocation 7 0 0 7 0.0 .0 latch: undo global data 6 0 0 6 0.0 .0 control file parallel write 3 0 0 12 0.0 .0 Disk file operations I/O 31 0 0 1 0.0 .0 row cache lock 2 0 0 9 0.0 .0 datafile move cleanup during 1 0 0 11 0.0 .0 rdbms ipc reply 1 0 0 9 0.0 .0 db file single write 1 0 0 8 0.0 .0 db file scattered read 65 0 0 0 0.0 .0 latch: messages 1 0 0 6 0.0 .0 Allocate CGA memory from OS 20 0 0 0 0.0 .0 asynch descriptor resize 12 100 0 0 0.0 .0 SQL*Net message from client 64,737 0 4,807 74 3.0 Wait Events (fg and bg) DB/Inst: FREE/FREE Snaps: 106-107 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ PL/SQL lock timer 293 100 880 3004 0.0 SQL*Net message to client 64,724 0 5 0 3.0 KSV master wait 1 100 0 14 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: FREE/FREE Snaps: 106-107 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- Allocate CGA memory from O 20 95.0 5.0 Allocate DGA memory from O 5 100.0 Allocate PGA memory from O 329 96.0 1.5 1.2 .6 .6 Allocate UGA memory from O 7583 96.1 2.7 .7 .3 .2 .1 DLM cross inst call comple 1 100.0 Data file init write 66 3.0 3.0 3.0 25.8 65.2 Disk file operations I/O 31 80.6 9.7 3.2 6.5 Free private memory to OS 6308 94.3 3.2 1.0 .9 .4 .1 .0 asynch descriptor resize 12 91.7 8.3 buffer busy waits 522 7.5 2.7 4.0 7.3 6.1 2.9 46.9 22.6 control file parallel writ 3 33.3 33.3 33.3 control file sequential re 139 100.0 cursor: mutex S 46 17.4 37.0 23.9 19.6 2.2 cursor: mutex X 2206 98.1 .4 .4 .5 .4 .2 cursor: pin S 206 9.2 24.3 41.3 20.9 3.4 1.0 datafile move cleanup duri 1 100.0 db file parallel read 5190 65.2 8.3 7.5 3.6 3.5 4.4 7.6 db file scattered read 65 96.9 1.5 1.5 db file sequential read 414K 93.4 1.5 .7 .7 1.2 1.7 .8 db file single write 1 100.0 direct path sync 1 100.0 direct path write 29 20.7 79.3 enq: CR - block range reus 32 100.0 enq: FB - contention 5 40.0 60.0 enq: HW - contention 6 33.3 16.7 33.3 16.7 enq: TX - contention 5 100.0 enq: TX - index contention 10 80.0 10.0 10.0 free buffer waits 308K .1 .1 .1 .2 86.7 12.6 .2 .0 latch free 718 7.8 7.1 13.9 35.7 22.8 11.1 1.5 latch: In memory undo latc 182 19.2 3.8 15.4 24.7 29.7 6.6 .5 latch: cache buffers chain 395 10.6 6.1 17.7 33.7 19.0 12.4 .5 latch: cache buffers lru c 2624 14.0 9.8 15.3 25.3 23.1 10.3 2.2 latch: enqueue hash chains 145 7.6 7.6 15.9 31.0 26.2 11.7 latch: messages 1 100.0 latch: object queue header 36 5.6 8.3 19.4 30.6 27.8 8.3 latch: redo allocation 84 8.3 9.5 15.5 28.6 27.4 10.7 latch: redo copy 14 7.1 21.4 50.0 21.4 latch: session allocation 7 28.6 14.3 14.3 42.9 latch: undo global data 6 33.3 16.7 50.0 library cache: bucket mute 28 14.3 7.1 3.6 46.4 28.6 library cache: mutex X 1249 4.0 2.7 3.5 3.8 59.3 23.7 2.9 lock deadlock retry 4748 98.4 .5 .5 .4 .1 .1 log file switch (private s 12 100.0 log file sync 21K .0 .0 .2 1.2 1.6 6.2 90.8 rdbms ipc reply 1 100.0 read by other session 21 4.8 33.3 42.9 19.0 reliable message 30 50.0 13.3 10.0 20.0 3.3 3.3 resmgr:cpu quantum 1323 7.9 3.8 10.8 20.0 30.2 22.5 4.8 row cache lock 2 100.0 write complete waits 384 .3 4.2 95.6 KSV master wait 1 100.0 Wait Event Histogram DB/Inst: FREE/FREE Snaps: 106-107 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- PL/SQL lock timer 293 100.0 SQL*Net message from clien 64K .3 1.3 1.1 1.9 6.6 13.3 75.5 SQL*Net message to client 64K 98.3 .9 .4 .3 .1 .0 .0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: FREE/FREE Snaps: 106-107 -> Total DB CPU (s): 765 -> Captured SQL accounts for 135.7% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 343.62 13,167 0.03 44.9 5599.62 7,140,521 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 120.08 158,151 0.00 15.7 774.96 5,490,021 2319948924 Module: New Order SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV 104.14 16,485 0.01 13.6 263.46 2,000,553 1914154499 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 58.75 198,773 0.00 7.7 157.57 2,000,369 1113394757 Module: Browse Products SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE 54.73 39,873 0.00 7.2 2352.95 629,489 4219272024 Module: New Order INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN IT_PRICE, QUANTITY, GIFT_WRAP, CONDITION, ESTIMATED_DELIVERY ) V ALUES ( :B4 , :B3 , :B2 , :B1 , 1, 'None', 'New', (SYSDATE + 3) ) 47.38 5,019 0.01 6.2 1226.40 237,261 3844908645 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7,:8,:9,:10) ; END; 28.83 13,166 0.00 3.8 1191.05 244,053 4194254847 Module: New Order INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ ID, ORDER_STATUS, WAREHOUSE_ID, DELIVERY_TYPE, DELIVERY_ADDRESS_ ID, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, CUSTOMER_CLASS, C ARD_ID, INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 , 23.72 705 0.03 3.1 140.57 1,191,995 3656256132 BEGIN :1 := orderentry.WarehouseActivityQuery(:2,:3,:4); END; 23.54 5,020 0.00 3.1 689.59 102,287 3463613875 Module: Update Customer Details INSERT INTO CUSTOMERS ( CUSTOMER_ID , CUST_FIRST_NAME , CUST_LAS T_NAME , NLS_LANGUAGE , NLS_TERRITORY , CREDIT_LIMIT , CUST_EMAI L , ACCOUNT_MGR_ID, CUSTOMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_MAILSHOT, PREFERRED_ADDRESS, PREFERRED_C 22.79 705 0.03 3.0 138.86 1,191,995 3331357829 SQL ordered by CPU DB/Inst: FREE/FREE Snaps: 106-107 -> Total DB CPU (s): 765 -> Captured SQL accounts for 135.7% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- Module: Warehouse Activity Query WITH STAGE1 AS (SELECT /*+ materialize CARDINALITY(12) */ (ROWNU M*(1/288)) OFFSET FROM DUAL CONNECT BY ROWNUM <= 12 ), STAGE2 AS (SELECT /*+ materialize CARDINALITY(12) */ LAG(OFFSET, 1, 0) OV ER (ORDER BY ROWNUM) OSTART, OFFSET OEND FROM STAGE1 ), STAGE3 A 19.59 705 0.03 2.6 86.01 996,576 40071047 Module: Update Customer Details BEGIN :1 := orderentry.SalesRepsQuery(:2,:3,:4); END; 18.82 706 0.03 2.5 84.33 996,651 4212635381 Module: Sales Rep Query SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE, CUSTOMERS .CUST_FIRST_NAME, CUSTOMERS.CUST_LAST_NAME FROM (SELECT ORDERS.O RDER_TOTAL, ORDERS.SALES_REP_ID, ORDERS.ORDER_DATE, ORDERS.CUSTO MER_ID, RANK() OVER (ORDER BY ORDERS.ORDER_TOTAL DESC) SAL_RANK 17.52 610 0.03 2.3 101.10 1,031,354 2561751562 BEGIN :1 := orderentry.WarehouseOrdersQuery(:2,:3,:4); END; 16.86 611 0.03 2.2 99.76 1,031,354 3828217192 Module: Warehouse Orders Query SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID, SUM(ORDER_TOTAL), COUNT( 1) FROM ORDERS, WAREHOUSES WHERE ORDERS.WAREHOUSE_ID = WAREHOUSE S.WAREHOUSE_ID AND WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY CUBE(O RDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID) 15.96 39,581 0.00 2.1 183.75 158,382 1822227481 Module: New Order SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST OMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_ MAILSHOT, PREFERRED_ADDRESS, PREFERRED_CARD FROM CUSTOMERS WHERE 12.46 1,608 0.01 1.6 142.74 40,074 2386275519 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 11.58 14,771 0.00 1.5 116.87 66,518 336093312 Module: New Order SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADD RESSES WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 11.04 13,167 0.00 1.4 245.03 127,170 3812575787 Module: New Order UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DB MS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B 1 10.79 12,618 0.00 1.4 391.58 160,543 2385983258 Module: New Order SQL ordered by CPU DB/Inst: FREE/FREE Snaps: 106-107 -> Total DB CPU (s): 765 -> Captured SQL accounts for 135.7% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 9.77 1,681 0.01 1.3 170.61 49,062 1709086327 BEGIN :1 := orderentry.processorders(:2,:3,:4); END; 8.76 13,163 0.00 1.1 103.52 59,276 140204897 Module: New Order SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE , IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 7.66 5,519 0.00 1.0 225.66 61,784 522486300 Module: Update Customer Details INSERT INTO ADDRESSES ( ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, H OUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE ) VALUES ( :B6 , :B5 , TRUNC(SYSDATE,'MI'), FLOOR(DBMS_ RANDOM.VALUE(1, :B4 )), 'Street Name', :B3 , :B2 , :B1 , 'Postco ------------------------------------------------------------- SQL ordered by Elapsed time for DB: FREE Instance: FREE Snaps: 106 -107 -> Total DB Time (s): 9,571 -> Captured SQL accounts for 160.0% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 5599.62 13,167 0.43 58.5 343.62 340,825 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 2352.95 39,873 0.06 24.6 54.73 28,349 4219272024 Module: New Order INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN IT_PRICE, QUANTITY, GIFT_WRAP, CONDITION, ESTIMATED_DELIVERY ) V ALUES ( :B4 , :B3 , :B2 , :B1 , 1, 'None', 'New', (SYSDATE + 3) ) 1226.40 5,019 0.24 12.8 47.38 17,289 3844908645 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7,:8,:9,:10) ; END; 1191.05 13,166 0.09 12.4 28.83 26,085 4194254847 Module: New Order INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ ID, ORDER_STATUS, WAREHOUSE_ID, DELIVERY_TYPE, DELIVERY_ADDRESS_ ID, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, CUSTOMER_CLASS, C ARD_ID, INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 , 774.96 158,151 0.00 8.1 120.08 194,127 2319948924 Module: New Order SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV 689.59 5,020 0.14 7.2 23.54 12,310 3463613875 Module: Update Customer Details INSERT INTO CUSTOMERS ( CUSTOMER_ID , CUST_FIRST_NAME , CUST_LAS T_NAME , NLS_LANGUAGE , NLS_TERRITORY , CREDIT_LIMIT , CUST_EMAI L , ACCOUNT_MGR_ID, CUSTOMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_MAILSHOT, PREFERRED_ADDRESS, PREFERRED_C 391.58 12,618 0.03 4.1 10.79 7 2385983258 Module: New Order UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 263.46 16,485 0.02 2.8 104.14 1,384 1914154499 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 249.85 5,020 0.05 2.6 5.93 1,768 1006760834 Module: Update Customer Details INSERT INTO CARD_DETAILS ( CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD _NUMBER, EXPIRY_DATE, IS_VALID, SECURITY_CODE ) VALUES ( :B2 , : B1 , 'Visa(Debit)', FLOOR(DBMS_RANDOM.VALUE(1111111111, 99999999 99)), TRUNC(SYSDATE + (DBMS_RANDOM.VALUE(365, 1460))), 'Y', FLOO SQL ordered by Elapsed time for DB: FREE Instance: FREE Snaps: 106 -107 -> Total DB Time (s): 9,571 -> Captured SQL accounts for 160.0% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 245.03 13,167 0.02 2.6 11.04 1,462 3812575787 Module: New Order UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DB MS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B 1 225.66 5,519 0.04 2.4 7.66 4,120 522486300 Module: Update Customer Details INSERT INTO ADDRESSES ( ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, H OUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE ) VALUES ( :B6 , :B5 , TRUNC(SYSDATE,'MI'), FLOOR(DBMS_ RANDOM.VALUE(1, :B4 )), 'Street Name', :B3 , :B2 , :B1 , 'Postco 183.75 39,581 0.00 1.9 15.96 23,978 1822227481 Module: New Order SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST OMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_ MAILSHOT, PREFERRED_ADDRESS, PREFERRED_CARD FROM CUSTOMERS WHERE 170.61 1,681 0.10 1.8 9.77 8,472 1709086327 BEGIN :1 := orderentry.processorders(:2,:3,:4); END; 168.08 6,574 0.03 1.8 5.43 49 2137532914 Module: New Order INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID, LOGON_DATE) VALUES (LOG ON_SEQ.NEXTVAL, :B2 , :B1 ) 157.57 198,773 0.00 1.6 58.75 1,384 1113394757 Module: Browse Products SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE 142.74 1,608 0.09 1.5 12.46 10,684 2386275519 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 140.57 705 0.20 1.5 23.72 65,351 3656256132 BEGIN :1 := orderentry.WarehouseActivityQuery(:2,:3,:4); END; 138.86 705 0.20 1.5 22.79 65,351 3331357829 Module: Warehouse Activity Query WITH STAGE1 AS (SELECT /*+ materialize CARDINALITY(12) */ (ROWNU M*(1/288)) OFFSET FROM DUAL CONNECT BY ROWNUM <= 12 ), STAGE2 AS (SELECT /*+ materialize CARDINALITY(12) */ LAG(OFFSET, 1, 0) OV ER (ORDER BY ROWNUM) OSTART, OFFSET OEND FROM STAGE1 ), STAGE3 A 116.87 14,771 0.01 1.2 11.58 32,748 336093312 Module: New Order SQL ordered by Elapsed time for DB: FREE Instance: FREE Snaps: 106 -107 -> Total DB Time (s): 9,571 -> Captured SQL accounts for 160.0% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADD RESSES WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 110.42 1,681 0.07 1.2 3.13 754 4220433507 Module: Process Orders UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1 103.52 13,163 0.01 1.1 8.76 30,293 140204897 Module: New Order SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE , IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 101.10 610 0.17 1.1 17.52 56,107 2561751562 BEGIN :1 := orderentry.WarehouseOrdersQuery(:2,:3,:4); END; 99.76 611 0.16 1.0 16.86 56,107 3828217192 Module: Warehouse Orders Query SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID, SUM(ORDER_TOTAL), COUNT( 1) FROM ORDERS, WAREHOUSES WHERE ORDERS.WAREHOUSE_ID = WAREHOUSE S.WAREHOUSE_ID AND WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY CUBE(O RDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID) ------------------------------------------------------------- SQL ordered by Gets DB/Inst: FREE/FREE Snaps: 106-107 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 12,750,332 -> Captured SQL accounts for 99.9% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 7,140,521 13,167 542.3 56.0 343.62 5599.62 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 5,490,021 158,151 34.7 43.1 120.08 774.96 2319948924 Module: New Order SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV 2,000,553 16,485 121.4 15.7 104.14 263.46 1914154499 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 2,000,369 198,773 10.1 15.7 58.75 157.57 1113394757 Module: Browse Products SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE 1,191,995 705 1,690.8 9.3 22.79 138.86 3331357829 Module: Warehouse Activity Query WITH STAGE1 AS (SELECT /*+ materialize CARDINALITY(12) */ (ROWNU M*(1/288)) OFFSET FROM DUAL CONNECT BY ROWNUM <= 12 ), STAGE2 AS (SELECT /*+ materialize CARDINALITY(12) */ LAG(OFFSET, 1, 0) OV ER (ORDER BY ROWNUM) OSTART, OFFSET OEND FROM STAGE1 ), STAGE3 A 1,191,995 705 1,690.8 9.3 23.72 140.57 3656256132 BEGIN :1 := orderentry.WarehouseActivityQuery(:2,:3,:4); END; 1,031,354 610 1,690.7 8.1 17.52 101.10 2561751562 BEGIN :1 := orderentry.WarehouseOrdersQuery(:2,:3,:4); END; 1,031,354 611 1,688.0 8.1 16.86 99.76 3828217192 Module: Warehouse Orders Query SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID, SUM(ORDER_TOTAL), COUNT( 1) FROM ORDERS, WAREHOUSES WHERE ORDERS.WAREHOUSE_ID = WAREHOUSE S.WAREHOUSE_ID AND WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY CUBE(O RDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID) 996,651 706 1,411.7 7.8 18.82 84.33 4212635381 Module: Sales Rep Query SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE, CUSTOMERS .CUST_FIRST_NAME, CUSTOMERS.CUST_LAST_NAME FROM (SELECT ORDERS.O RDER_TOTAL, ORDERS.SALES_REP_ID, ORDERS.ORDER_DATE, ORDERS.CUSTO MER_ID, RANK() OVER (ORDER BY ORDERS.ORDER_TOTAL DESC) SAL_RANK 996,576 705 1,413.6 7.8 19.59 86.01 40071047 Module: Update Customer Details BEGIN :1 := orderentry.SalesRepsQuery(:2,:3,:4); END; SQL ordered by Gets DB/Inst: FREE/FREE Snaps: 106-107 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 12,750,332 -> Captured SQL accounts for 99.9% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 629,489 39,873 15.8 4.9 54.73 2352.95 4219272024 Module: New Order INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN IT_PRICE, QUANTITY, GIFT_WRAP, CONDITION, ESTIMATED_DELIVERY ) V ALUES ( :B4 , :B3 , :B2 , :B1 , 1, 'None', 'New', (SYSDATE + 3) ) 251,334 43,907 5.7 2.0 6.50 35.04 2308915322 Module: New Order SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I .WAREHOUSE_ID = :B1 244,053 13,166 18.5 1.9 28.83 1191.05 4194254847 Module: New Order INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ ID, ORDER_STATUS, WAREHOUSE_ID, DELIVERY_TYPE, DELIVERY_ADDRESS_ ID, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, CUSTOMER_CLASS, C ARD_ID, INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 , 237,261 5,019 47.3 1.9 47.38 1226.40 3844908645 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7,:8,:9,:10) ; END; 160,543 12,618 12.7 1.3 10.79 391.58 2385983258 Module: New Order UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 158,382 39,581 4.0 1.2 15.96 183.75 1822227481 Module: New Order SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST OMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_ MAILSHOT, PREFERRED_ADDRESS, PREFERRED_CARD FROM CUSTOMERS WHERE ------------------------------------------------------------- SQL ordered by Reads DB/Inst: FREE/FREE Snaps: 106-107 -> End Disk Reads Threshold: 1000 Total Disk Reads: 565,423 -> Captured SQL accounts for 100.2% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 340,825 13,167 25.9 60.3 343.62 5599.62 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 194,127 158,151 1.2 34.3 120.08 774.96 2319948924 Module: New Order SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV 65,351 705 92.7 11.6 22.79 138.86 3331357829 Module: Warehouse Activity Query WITH STAGE1 AS (SELECT /*+ materialize CARDINALITY(12) */ (ROWNU M*(1/288)) OFFSET FROM DUAL CONNECT BY ROWNUM <= 12 ), STAGE2 AS (SELECT /*+ materialize CARDINALITY(12) */ LAG(OFFSET, 1, 0) OV ER (ORDER BY ROWNUM) OSTART, OFFSET OEND FROM STAGE1 ), STAGE3 A 65,351 705 92.7 11.6 23.72 140.57 3656256132 BEGIN :1 := orderentry.WarehouseActivityQuery(:2,:3,:4); END; 58,951 706 83.5 10.4 18.82 84.33 4212635381 Module: Sales Rep Query SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE, CUSTOMERS .CUST_FIRST_NAME, CUSTOMERS.CUST_LAST_NAME FROM (SELECT ORDERS.O RDER_TOTAL, ORDERS.SALES_REP_ID, ORDERS.ORDER_DATE, ORDERS.CUSTO MER_ID, RANK() OVER (ORDER BY ORDERS.ORDER_TOTAL DESC) SAL_RANK 58,928 705 83.6 10.4 19.59 86.01 40071047 Module: Update Customer Details BEGIN :1 := orderentry.SalesRepsQuery(:2,:3,:4); END; 56,107 610 92.0 9.9 17.52 101.10 2561751562 BEGIN :1 := orderentry.WarehouseOrdersQuery(:2,:3,:4); END; 56,107 611 91.8 9.9 16.86 99.76 3828217192 Module: Warehouse Orders Query SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID, SUM(ORDER_TOTAL), COUNT( 1) FROM ORDERS, WAREHOUSES WHERE ORDERS.WAREHOUSE_ID = WAREHOUSE S.WAREHOUSE_ID AND WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY CUBE(O RDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID) 32,748 14,771 2.2 5.8 11.58 116.87 336093312 Module: New Order SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADD RESSES WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 30,293 13,163 2.3 5.4 8.76 103.52 140204897 Module: New Order SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE , IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = SQL ordered by Reads DB/Inst: FREE/FREE Snaps: 106-107 -> End Disk Reads Threshold: 1000 Total Disk Reads: 565,423 -> Captured SQL accounts for 100.2% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- :B2 AND ROWNUM < :B1 28,349 39,873 0.7 5.0 54.73 2352.95 4219272024 Module: New Order INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN IT_PRICE, QUANTITY, GIFT_WRAP, CONDITION, ESTIMATED_DELIVERY ) V ALUES ( :B4 , :B3 , :B2 , :B1 , 1, 'None', 'New', (SYSDATE + 3) ) 26,085 13,166 2.0 4.6 28.83 1191.05 4194254847 Module: New Order INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ ID, ORDER_STATUS, WAREHOUSE_ID, DELIVERY_TYPE, DELIVERY_ADDRESS_ ID, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, CUSTOMER_CLASS, C ARD_ID, INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 , 23,978 39,581 0.6 4.2 15.96 183.75 1822227481 Module: New Order SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST OMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_ MAILSHOT, PREFERRED_ADDRESS, PREFERRED_CARD FROM CUSTOMERS WHERE 17,289 5,019 3.4 3.1 47.38 1226.40 3844908645 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7,:8,:9,:10) ; END; 12,310 5,020 2.5 2.2 23.54 689.59 3463613875 Module: Update Customer Details INSERT INTO CUSTOMERS ( CUSTOMER_ID , CUST_FIRST_NAME , CUST_LAS T_NAME , NLS_LANGUAGE , NLS_TERRITORY , CREDIT_LIMIT , CUST_EMAI L , ACCOUNT_MGR_ID, CUSTOMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_MAILSHOT, PREFERRED_ADDRESS, PREFERRED_C 10,684 1,608 6.6 1.9 12.46 142.74 2386275519 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 9,963 43,907 0.2 1.8 6.50 35.04 2308915322 Module: New Order SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I .WAREHOUSE_ID = :B1 8,472 1,681 5.0 1.5 9.77 170.61 1709086327 BEGIN :1 := orderentry.processorders(:2,:3,:4); END; 7,718 1,681 4.6 1.4 5.09 56.40 1391979773 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND WAREHOUSE_ID = :B1 AND ROWNUM < 10 SQL ordered by Reads DB/Inst: FREE/FREE Snaps: 106-107 -> End Disk Reads Threshold: 1000 Total Disk Reads: 565,423 -> Captured SQL accounts for 100.2% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- ) SELECT O.ORDER_ID, OI.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PR ICE, OI.QUANTITY, O.ORDER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O ------------------------------------------------------------- SQL ordered by Executions DB/Inst: FREE/FREE Snaps: 106-107 -> End Executions Threshold: 100 Total Executions: 648,600 -> Captured SQL accounts for 100.5% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 198,773 2,783,326 14.0 0.00 0.00 1113394757 Module: Browse Products SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVE 158,151 719,725 4.6 0.00 0.00 2319948924 Module: New Order SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C ATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_ STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FRO M PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INV 43,907 39,875 0.9 0.00 0.00 2308915322 Module: New Order SELECT QUANTITY_ON_HAND FROM PRODUCT_INFORMATION P, INVENTORIES I WHERE I.PRODUCT_ID = :B2 AND I.PRODUCT_ID = P.PRODUCT_ID AND I .WAREHOUSE_ID = :B1 39,873 39,873 1.0 0.00 0.06 4219272024 Module: New Order INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN IT_PRICE, QUANTITY, GIFT_WRAP, CONDITION, ESTIMATED_DELIVERY ) V ALUES ( :B4 , :B3 , :B2 , :B1 , 1, 'None', 'New', (SYSDATE + 3) ) 39,581 39,586 1.0 0.00 0.00 1822227481 Module: New Order SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID, CUST OMER_SINCE, CUSTOMER_CLASS, SUGGESTIONS, DOB, MAILSHOT, PARTNER_ MAILSHOT, PREFERRED_ADDRESS, PREFERRED_CARD FROM CUSTOMERS WHERE 16,485 16,485 1.0 0.01 0.02 1914154499 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 14,771 22,104 1.5 0.00 0.01 336093312 Module: New Order SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADD RESSES WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 13,167 13,167 1.0 0.03 0.43 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 13,167 13,167 1.0 0.00 0.02 3812575787 Module: New Order UPDATE ORDERS SET ORDER_MODE = 'online', ORDER_STATUS = FLOOR(DB MS_RANDOM.VALUE(0, :B3 )), ORDER_TOTAL = :B2 WHERE ORDER_ID = :B SQL ordered by Executions DB/Inst: FREE/FREE Snaps: 106-107 -> End Executions Threshold: 100 Total Executions: 648,600 -> Captured SQL accounts for 100.5% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 1 13,166 13,166 1.0 0.00 0.09 4194254847 Module: New Order INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ ID, ORDER_STATUS, WAREHOUSE_ID, DELIVERY_TYPE, DELIVERY_ADDRESS_ ID, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, CUSTOMER_CLASS, C ARD_ID, INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 , 13,163 19,744 1.5 0.00 0.01 140204897 Module: New Order SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER, EXPIRY_DATE , IS_VALID, SECURITY_CODE FROM CARD_DETAILS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 12,618 39,884 3.2 0.00 0.03 2385983258 Module: New Order UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 6,574 6,574 1.0 0.00 0.03 2137532914 Module: New Order INSERT INTO LOGON (LOGON_ID,CUSTOMER_ID, LOGON_DATE) VALUES (LOG ON_SEQ.NEXTVAL, :B2 , :B1 ) 6,574 6,574 1.0 0.00 0.00 2293415029 Module: New Order SELECT SYSDATE FROM DUAL ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: FREE/FREE Snaps: 106-107 -> End Parse Calls Threshold: 1000 Total Parse Calls: 45,347 -> Captured SQL accounts for 105.7% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 16,471 16,485 36.32 1914154499 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 13,155 13,167 29.01 1724576531 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 5,019 5,019 11.07 3844908645 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7,:8,:9,:10) ; END; 3,250 3,250 7.17 696707637 BEGIN :1 := orderentry.updateCustomerDetails(:2,:3,:4,:5,:6,:7, :8); END; 1,681 1,681 3.71 1709086327 BEGIN :1 := orderentry.processorders(:2,:3,:4); END; 1,607 1,608 3.54 2386275519 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 918 5,519 2.02 522486300 Module: Update Customer Details INSERT INTO ADDRESSES ( ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, H OUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE ) VALUES ( :B6 , :B5 , TRUNC(SYSDATE,'MI'), FLOOR(DBMS_ RANDOM.VALUE(1, :B4 )), 'Street Name', :B3 , :B2 , :B1 , 'Postco 918 5,519 2.02 4084548783 Module: Update Customer Details SELECT ADDRESS_SEQ.NEXTVAL FROM DUAL 705 705 1.55 40071047 Module: Update Customer Details BEGIN :1 := orderentry.SalesRepsQuery(:2,:3,:4); END; 705 705 1.55 3656256132 BEGIN :1 := orderentry.WarehouseActivityQuery(:2,:3,:4); END; 610 610 1.35 2561751562 BEGIN :1 := orderentry.WarehouseOrdersQuery(:2,:3,:4); END; ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: FREE/FREE Snaps: 106-107 -> End Sharable Memory Threshold(KB): 1024 Max End Sharable Sharable Old Memory (KB) Memory (KB) Parse Calls Executions % Total Hash Value ----------- ----------- ------------ ------------ ------- ---------- 3,037 3,037 0 0 0.3 609543940 Module: DBMS_SCHEDULER INSERT INTO WWV_SCHEMA_AGGR_METRICS ( LOG_DAY, SCHEMA, DATA_FILE _CNT, DATA_FILE_SIZE, FREE_SPACE, USED_SPACE, JOBS_RUN_CNT, JOBS _ELAPSED_TIME_TOTAL_SEC, JOBS_FAILED_LOGS_CNT, JOB_ELAPSED_TIME_ 10PCT_SEC, JOB_ELAPSED_TIME_AVG_SEC, JOB_ELAPSED_TIME_MEDIAN_SEC ------------------------------------------------------------- Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ ASSM cbk:blocks examined 5,351 5.9 0.3 ASSM cbk:blocks marked full 3,274 3.6 0.2 ASSM gsp:L1 bitmaps examined 4,364 4.8 0.2 ASSM gsp:L2 bitmap full 4 0.0 0.0 ASSM gsp:L2 bitmaps examined 3,014 3.3 0.1 ASSM gsp:Search all 20 0.0 0.0 ASSM gsp:Search hint 34 0.0 0.0 ASSM gsp:Search steal 20 0.0 0.0 ASSM gsp:bump HWM 20 0.0 0.0 ASSM gsp:get free block 4,179 4.6 0.2 ASSM gsp:good hint 1,489 1.7 0.1 ASSM gsp:reject db 0 0.0 0.0 ASSM rsv:fill reserve 0 0.0 0.0 ASSM wasted db state change 1,461 1.6 0.1 Batched IO (bound) vector count 384 0.4 0.0 Batched IO (full) vector count 2,528 2.8 0.1 Batched IO block miss count 1,644,396 1,819.0 76.7 Batched IO buffer defrag count 49 0.1 0.0 Batched IO double miss count 1,488,863 1,647.0 69.5 Batched IO same unit count 81,076 89.7 3.8 Batched IO single block count 2,817 3.1 0.1 Batched IO vector block count 1,588,134 1,756.8 74.1 Batched IO vector read count 34,129 37.8 1.6 Block Cleanout Optim referenced 412 0.5 0.0 CCursor + sql area evicted 0 0.0 0.0 CCursor + sql area evicted - fail 1 0.0 0.0 CPU used by this session 61,843 68.4 2.9 CPU used when call started 61,469 68.0 2.9 CR blocks created 365 0.4 0.0 DB time 1,283,384 1,419.7 59.9 HSC Heap Segment Block Changes 133,856 148.1 6.3 Heap Segment Array Inserts 134 0.2 0.0 IMU CR rollbacks 153 0.2 0.0 IMU Flushes 8,817 9.8 0.4 IMU Redo allocation size 70,991,148 78,530.0 3,313.0 IMU commits 12,154 13.4 0.6 IMU contention 5,961 6.6 0.3 IMU ktichg flush 558 0.6 0.0 IMU pool not allocated 420 0.5 0.0 IMU recursive-transaction flush 239 0.3 0.0 IMU undo allocation size 158,123,016 174,914.8 7,379.3 IMU- failed to get a private stra 420 0.5 0.0 KTFB alloc myinst 14 0.0 0.0 KTFB alloc req 260 0.3 0.0 KTFB alloc search FFB 14 0.0 0.0 KTFB alloc space (block) 62,326,528 68,945.3 2,908.7 KTFB alloc time (ms) 157,281 174.0 7.3 KTFB apply req 246 0.3 0.0 KTFB apply time (ms) 4,214 4.7 0.2 Requests to/from client 64,637 71.5 3.0 RowCR - row contention 11 0.0 0.0 RowCR attempts 33,646 37.2 1.6 RowCR hits 33,619 37.2 1.6 SQL*Net roundtrips to/from client 64,643 71.5 3.0 Session total flash IO requests 565,474 625.5 26.4 TBS Extension: bytes extended 67,108,864 74,235.5 3,131.8 Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ TBS Extension: files extended 1 0.0 0.0 TBS Extension: tasks created 2 0.0 0.0 TBS Extension: tasks executed 2 0.0 0.0 active txn count during cleanout 2,467 2.7 0.1 application wait time 0 0.0 0.0 blocks cleaned out using minact 13,267 14.7 0.6 branch node splits 3 0.0 0.0 buffer is not pinned count 12,718,639 14,069.3 593.6 buffer is pinned count 10,994,160 12,161.7 513.1 bytes received via SQL*Net from c 10,334,359 11,431.8 482.3 bytes sent via SQL*Net to client 4,826,832 5,339.4 225.3 calls to get snapshot scn: kcmgss 603,558 667.7 28.2 calls to kcmadj 20,676 22.9 1.0 calls to kcmgas 31,386 34.7 1.5 calls to kcmgcs 334,097 369.6 15.6 cell physical IO interconnect byt 4,635,443,200 5,127,702.7 216,326.5 change write time 1,791 2.0 0.1 cleanout - number of ktugct calls 4,348 4.8 0.2 cleanouts and rollbacks - consist 167 0.2 0.0 cleanouts only - consistent read 221 0.2 0.0 cluster key scan block gets 71 0.1 0.0 cluster key scans 71 0.1 0.0 commit batch/immediate performed 37 0.0 0.0 commit batch/immediate requested 37 0.0 0.0 commit cleanout failures: buffer 275 0.3 0.0 commit cleanout failures: callbac 25 0.0 0.0 commit cleanout failures: cannot 62 0.1 0.0 commit cleanout failures: in-memo 5,004 5.5 0.2 commit cleanouts 264,781 292.9 12.4 commit cleanouts successfully com 259,410 287.0 12.1 commit immediate performed 37 0.0 0.0 commit immediate requested 37 0.0 0.0 commit txn count during cleanout 6,797 7.5 0.3 concurrency wait time 60,602 67.0 2.8 consistent changes 1,392 1.5 0.1 consistent gets 11,485,857 12,705.6 536.0 consistent gets examination 4,617,197 5,107.5 215.5 consistent gets examination (fast 4,378,281 4,843.2 204.3 consistent gets from cache 11,485,857 12,705.6 536.0 consistent gets pin 6,868,660 7,598.1 320.6 consistent gets pin (fastpath) 6,736,748 7,452.2 314.4 control file blocks written 6 0.0 0.0 control file cache search for rea 248 0.3 0.0 control file cache search for rea 0 0.0 0.0 control file exclusive latch succ 0 0.0 0.0 control file read req (single-blo 133 0.2 0.0 control file read req (single-blo 139 0.2 0.0 cursor authentications 1 0.0 0.0 data blocks consistent reads - un 348 0.4 0.0 db block changes 841,971 931.4 39.3 db block gets 1,264,483 1,398.8 59.0 db block gets from cache 1,264,483 1,398.8 59.0 db block gets from cache (fastpat 1,003,039 1,109.6 46.8 deferred (CURRENT) block cleanout 121,011 133.9 5.7 dirty buffers inspected 125,640 139.0 5.9 enqueue conversions 17,682 19.6 0.8 Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ enqueue releases 120,079 132.8 5.6 enqueue requests 120,051 132.8 5.6 enqueue waits 25 0.0 0.0 exchange deadlocks 11,421 12.6 0.5 execute count 648,600 717.5 30.3 file io service time 0 0.0 0.0 file io wait time 452,865,956 500,957.9 21,134.3 free buffer inspected 5,168,653 5,717.5 241.2 free buffer requested 931,729 1,030.7 43.5 heap block compress 757 0.8 0.0 hot buffers moved to head of LRU 1,221,621 1,351.4 57.0 immediate (CR) block cleanout app 388 0.4 0.0 immediate (CURRENT) block cleanou 13,291 14.7 0.6 in call idle wait time 317,695 351.4 14.8 index crx upgrade (positioned) 0 0.0 0.0 index fast full scans (full) 0 0.0 0.0 index fetch by key 1,184,539 1,310.3 55.3 index range scans 1,391,335 1,539.1 64.9 index split cancel wait noclean 10 0.0 0.0 leaf node 90-10 splits 14 0.0 0.0 leaf node splits 1,063 1.2 0.1 lob reads 162 0.2 0.0 lob writes 160 0.2 0.0 lob writes unaligned 160 0.2 0.0 logical read bytes from cache 104,450,785,280 115,542,904.1 4,874,500.0 logons cumulative 15 0.0 0.0 messages sent 10,070 11.1 0.5 min active SCN optimization appli 2,494 2.8 0.1 no buffer to keep pinned count 4 0.0 0.0 no work - consistent read gets 6,824,889 7,549.7 318.5 non-idle wait count 1,203,689 1,331.5 56.2 non-idle wait time 855,459 946.3 39.9 number of successful DB user logi 0 0.0 0.0 number of successful DB user logi 0 0.0 0.0 opened cursors cumulative 648,697 717.6 30.3 parse count (failures) 0 0.0 0.0 parse count (hard) 0 0.0 0.0 parse count (total) 45,347 50.2 2.1 parse time cpu 267 0.3 0.0 parse time elapsed 637 0.7 0.0 physical read IO requests 565,317 625.4 26.4 physical read bytes 4,631,945,216 5,123,833.2 216,163.2 physical read total IO requests 565,446 625.5 26.4 physical read total bytes 4,634,099,712 5,126,216.5 216,263.8 physical read total multi block r 3 0.0 0.0 physical reads 565,423 625.5 26.4 physical reads cache 565,423 625.5 26.4 physical reads cache prefetch 148,231 164.0 6.9 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 29 0.0 0.0 physical write bytes 1,343,488 1,486.2 62.7 physical write total IO requests 29 0.0 0.0 physical write total bytes 1,343,488 1,486.2 62.7 physical write total multi block 2 0.0 0.0 physical writes 164 0.2 0.0 physical writes direct 164 0.2 0.0 Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical writes direct (lob) 164 0.2 0.0 physical writes non checkpoint 164 0.2 0.0 pinned buffers inspected 126 0.1 0.0 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 5 0.0 0.0 process last non-idle time 897 1.0 0.0 recursive calls 687,596 760.6 32.1 recursive cpu usage 49,783 55.1 2.3 redo buffer allocation retries 12 0.0 0.0 redo entries 139,168 154.0 6.5 redo log space requests 19 0.0 0.0 redo log space wait time 145 0.2 0.0 redo size 136,164,672 150,624.6 6,354.5 redo synch long waits 5 0.0 0.0 redo synch time 167,853 185.7 7.8 redo synch time (usec) 1,678,539,075 1,856,791.0 78,333.9 redo synch time overhead (usec) 119,218,834 131,879.2 5,563.7 redo synch time overhead count ( 6,525 7.2 0.3 redo synch time overhead count ( 10,676 11.8 0.5 redo synch time overhead count ( 4,603 5.1 0.2 redo synch time overhead count (1 43 0.1 0.0 redo synch time overhead count (i 6 0.0 0.0 redo synch writes 22,160 24.5 1.0 redo write info find 21,853 24.2 1.0 rollback changes - undo records a 73 0.1 0.0 rollbacks only - consistent read 53 0.1 0.0 rows fetched via callback 1,007,782 1,114.8 47.0 scheduler wait time 3,051 3.4 0.1 securefile alloc all sources 30 0.0 0.0 securefile allocation bytes 1,343,488 1,486.2 62.7 securefile allocation chunks 30 0.0 0.0 securefile bytes non-transformed 1,310,720 1,449.9 61.2 securefile direct write bytes 1,343,488 1,486.2 62.7 securefile direct write ops 29 0.0 0.0 securefile number of non-transfor 2 0.0 0.0 securefiles dispenser load chunks 131 0.1 0.0 segment chunks allocation from di 30 0.0 0.0 segment dispenser allocations 3 0.0 0.0 segment dispenser load tasks 1 0.0 0.0 segment prealloc tasks 1 0.0 0.0 session connect time 0 0.0 0.0 session cursor cache hits 648,357 717.2 30.3 session logical reads 12,750,332 14,104.4 595.0 session pga memory 2,783,920 3,079.6 129.9 session pga memory max 2,686,976 2,972.3 125.4 session uga memory 55,499,008 61,392.7 2,590.0 session uga memory max 72,474,192 80,170.6 3,382.2 shared hash latch upgrades - no w 2,229,750 2,466.5 104.1 shared hash latch upgrades - wait 44 0.1 0.0 shared io pool buffer get success 9 0.0 0.0 sorts (memory) 5,774 6.4 0.3 sorts (rows) 3,301,408 3,652.0 154.1 sql area evicted 0 0.0 0.0 sql area purged 0 0.0 0.0 switch current caused by our pin 12 0.0 0.0 switch current to new buffer 32 0.0 0.0 Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ table fetch by rowid 10,547,544 11,667.6 492.2 table fetch continued row 0 0.0 0.0 table scan blocks gotten 175 0.2 0.0 table scan disk non-IMC rows gott 2,108 2.3 0.1 table scan rows gotten 2,108 2.3 0.1 table scans (short tables) 56 0.1 0.0 total number of online undo segme 0 0.0 0.0 transaction rollbacks 37 0.0 0.0 undo change vector size 46,340,724 51,261.9 2,162.6 user I/O wait time 45,290 50.1 2.1 user calls 107,874 119.3 5.0 user commits 21,428 23.7 1.0 user commits Autonomous 27 0.0 0.0 user commits Commit on Success 18 0.0 0.0 user commits PL/SQL 7 0.0 0.0 user commits Top Level 21,412 23.7 1.0 user logons cumulative 0 0.0 0.0 user logouts cumulative 0 0.0 0.0 user rollbacks 0 0.0 0.0 workarea executions - optimal 7,582 8.4 0.4 write clones created in foregroun 7,634 8.4 0.4 ------------------------------------------------------------- Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 21 21 opened cursors current 61 52 session cursor cache count 2,707 2,691 ------------------------------------------------------------- Instance Activity Stats DB/Inst: FREE/FREE Snaps: 106-107 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 1 3.98 ------------------------------------------------------------- OS Statistics DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- INACTIVE_MEMORY_BYTES 4,645,081,088 SWAP_FREE_BYTES 4,294,963,200 BUSY_TIME 257,080 IDLE_TIME 166,816 IOWAIT_TIME 21,682 NICE_TIME 14 SYS_TIME 15,293 USER_TIME 14,088 RSRC_MGR_CPU_WAIT_TIME 3,579 PHYSICAL_MEMORY_BYTES 16,291,966,976 NUM_CPUS 6 NUM_CPU_CORES 6 NUM_CPU_SOCKETS 1 GLOBAL_RECEIVE_SIZE_MAX 4,194,304 GLOBAL_SEND_SIZE_MAX 1,048,576 TCP_RECEIVE_SIZE_DEFAULT 131,072 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 ------------------------------------------------------------- OS Statistics - detail DB/Inst: FREE/FREE Snaps: 106-107 Snap Snapshot Id Day Time Load %Busy %User %System %WIO %WCPU ------ --------------- ------ ------ ------ ------- ------ ------ 106 Sat 08 08:15:00 7.4 107 Sat 08 08:30:04 6.4 60.6 3.3 3.6 5.1 ------------------------------------------------------------- IO Stat by Function - summary DB/Inst: FREE/FREE Snaps: 106-107 ->Data Volume values suffixed with M,G,T,P are in multiples of 1024, other values suffixed with K,M,G,T,P are in multiples of 1000 ->ordered by Data Volume (Read+Write) desc ---------- Read --------- --------- Write -------- --- Wait ---- Data Requests Data Data Requests Data Avg Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms) --------------- ------ -------- -------- ------ -------- -------- ------ ------ Buffer Cache Re 4388M 620.6 4.9M 415K 0.0 LGWR .0 138M 11.1 .2M 9975 0.0 Others 2M .2 .0M 15M .9 .0M 535 0.0 Streams AQ .0 31 0.0 ------------------------------------------------------------- IO Stat by Function - detail DB/Inst: FREE/FREE Snaps: 106-107 ->ordered by Data Volume (Read+Write) desc ----------- Read ---------- ----------- Write --------- Small Large Small Large Small Large Small Large Read Read Data Data Write Write Data Data Function Reqs Reqs Read Read Reqs Reqs Writn Writn ------------------ ------ ------ ------ ------ ------ ------ ------ ------ Buffer Cache Reads 561K 4 4387M 1M LGWR 2 10K 14 134M 4M Others 140 2M 817 2 14M 1M Streams AQ 31 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: FREE/FREE Snaps: 106-107 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ SOE 565,976 626 0.8 1.0 110,164 122 847 680.8 UNDOTBS1 14 0 0.0 1.0 4,092 5 27 454.8 SYSAUX 1,375 2 0.1 1.1 179 0 0 0.0 PERFSTAT 520 1 2.5 1.1 379 0 0 0.0 SYSTEM 497 1 0.1 1.4 59 0 1 10.0 APEX 4 0 0.0 1.0 0 0 0 0.0 USERS 4 0 0.0 1.0 0 0 0 0.0 TEMP 2 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: FREE/FREE Snaps: 106-107 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ APEX /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 4 0 0.0 1 1.0 0 0 0 PERFSTAT /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 520 1 2.5 1 1.1 379 0 0 SOE /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 565,976 626 0.8 1 1.0 110,164 122 847 680.8 SYSAUX /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf 1,375 2 0.1 1 1.1 179 0 0 SYSTEM /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf 497 1 0.1 1 1.4 59 0 1 10.0 TEMP /opt/oracle/oradata/FREE/FREEPDB1/temp01.dbf 2 0 0.0 1.0 0 0 0 UNDOTBS1 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf 14 0 0.0 1 1.0 4,092 5 27 454.8 USERS /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf 4 0 0.0 1 1.0 0 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: FREE/FREE Snaps: 106-107 ->Number of single block reads in each time range ->Tempfiles are not included ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ APEX /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 4 0 0 0 0 0 PERFSTAT /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 450 0 0 0 0 0 SOE /opt/oracle/oradata/FREE/421E5E37FAA139E5E0650000000 533,842 0 0 0 0 0 SYSAUX /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf 1,324 0 0 0 0 0 SYSTEM /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf 454 0 0 0 0 0 UNDOTBS1 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf 14 0 0 0 0 0 USERS /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf 4 0 0 0 0 0 ------------------------------------------------------------- IO Stats Function Detail DB/Inst: FREE/FREE Snaps: 106-107 Small Small Large Large Function FileType Read Write Read Write Wait Time Name Name (MB) (MB) (MB) (MB) Waits (ms) ------------ ------------ ------- ------- ------- ------- --------- ----------- DBWR Data File 0 1003 0 22 0 0 DBWR Temp File 0 0 0 0 0 0 DBWR Other(8) 0 0 0 0 86 882 LGWR Control File 0 0 0 0 0 0 LGWR Data File 0 0 0 0 0 0 LGWR Log File 0 134 0 4 4 67 LGWR Other(8) 0 0 0 0 9971 444 Streams AQ Data File 0 0 0 0 31 0 Buffer Cache Data File 4387 0 1 0 410365 450256 Buffer Cache Temp File 0 0 0 0 2 0 Buffer Cache Other(8) 0 0 0 0 5091 32 Direct Reads Data File 0 0 0 0 0 0 Direct Reads Other(8) 0 0 0 0 0 0 Direct Write Data File 0 0 0 0 0 0 Direct Write Other(8) 0 0 0 0 29 1 Others(13) Control File 2 13 0 0 140 6 Others(13) Data File 0 1 0 1 0 0 Others(13) Temp File 0 0 0 0 0 0 Others(13) Other(8) 0 0 0 0 395 22 Others(13) Parameter Fi 0 0 0 0 0 0 Others(13) Password Fil 0 0 0 0 0 0 Others(13) External Tab 0 0 0 0 0 0 Others(13) OS Audit Fil 0 0 0 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: FREE/FREE Snaps: 106-107 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 97 16779 91777 245936 663552 245936 E 0 99 17116 121532 519114 663552 519114 ------------------------------------------------------------- Memory Dynamic Components DB/Inst: FREE/FREE Snaps: 106-107 -> Op - memory resize Operation -> Cache: D: Default, K: Keep, R: Recycle -> Mode: DEF: DEFerred mode, IMM: IMMediate mode Begin Snap End Snap Op Last Op Cache Size (M) Size (M) Count Type/Mode Last Op Time ---------------------- ---------- -------- ------- ---------- --------------- D:buffer cache 528 0 INITIA/ 08-Nov 06:59:40 PGA Target 512 0 STATIC SGA Target 1,536 0 STATIC Shared IO Pool 80 0 STATIC large pool 16 0 STATIC shared pool 880 0 STATIC ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: FREE/FREE End Snap: 107 -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtime P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds --- -------- ----- ------------ ------ -------------- ------------ -------- D 48 .1 6 12.0 12,942 48,095 238.8 D 96 .2 12 10.1 10,907 40,520 201.2 D 144 .3 18 8.4 9,109 33,824 167.9 D 192 .4 23 6.9 7,504 27,850 138.3 D 240 .5 29 5.6 6,068 22,504 111.7 D 288 .5 35 4.4 4,790 17,749 88.1 D 336 .6 41 3.4 3,680 13,616 67.6 D 384 .7 47 2.5 2,747 10,143 50.4 D 432 .8 53 1.8 1,990 7,324 36.4 D 480 .9 59 1.3 1,445 5,295 26.3 D 528 1.0 65 1.0 1,082 3,945 19.6 D 576 1.1 70 0.8 851 3,085 15.3 D 624 1.2 76 0.7 707 2,550 12.7 D 672 1.3 82 0.6 623 2,235 11.1 D 720 1.4 88 0.5 572 2,045 10.2 D 768 1.5 94 0.5 539 1,925 9.6 D 816 1.5 100 0.5 515 1,833 9.1 D 864 1.6 106 0.5 495 1,760 8.7 D 912 1.7 112 0.4 477 1,691 8.4 D 960 1.8 117 0.4 461 1,633 8.1 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: FREE/FREE Snaps: 106-107 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------- D 64K 96 12,790,737 569,441 134,746 ####### #### 876 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 841 577 686 undo header 27 12 455 2nd level bmb 1 0 10 1st level bmb 6 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: FREE/FREE Snaps: 106-107 -> B: Begin snap E: End snap (rows identified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas -> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of WorkArea memory under Manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 131 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 512 0 120.6 0.0 .0 .0 .0 102,400 E 512 0 110.7 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: FREE/FREE Snaps: 106-107 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 6,359 6,359 0 0 64K 128K 1,335 1,335 0 0 128K 256K 80 80 0 0 256K 512K 1 1 0 0 512K 1024K 28 28 0 0 1M 2M 11 11 0 0 2M 4M 1 1 0 0 16M 32M 1 1 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: FREE/FREE End Snap: 107 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Aggr W/A MB Estd Time PGA Estd PGA Target Size W/A MB Read/Written to Process Cache Overalloc Est (MB) Factr Processed to Disk Bytes (s) Hit % Count ---------- ------ -------------- -------------- ---------- ------ ---------- 64 0.1 890 256 0.0 78.0 32 128 0.3 890 256 0.0 78.0 32 256 0.5 890 233 0.0 79.0 30 384 0.8 890 0 0.0 100.0 0 512 1.0 890 0 0.0 100.0 0 614 1.2 890 0 0.0 100.0 0 717 1.4 890 0 0.0 100.0 0 819 1.6 890 0 0.0 100.0 0 922 1.8 890 0 0.0 100.0 0 1,024 2.0 890 0 0.0 100.0 0 1,536 3.0 890 0 0.0 100.0 0 2,048 4.0 890 0 0.0 100.0 0 3,072 6.0 890 0 0.0 100.0 0 4,096 8.0 890 0 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: FREE/FREE Snaps: 106-107 -> 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 -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ ------ B -------- 321.1 237.4 59.1 3.5 3.9 34 110 93 Other 245.6 2.6 1.5 9 103 93 Freeable 59.4 .0 1.6 4.6 29 38 PL/SQL 9.7 7.2 .1 .2 1 5 90 SQL 6.3 2.8 .1 .3 1 34 34 E -------- 308.6 241.3 41.4 3.2 2.7 20 110 95 Other 252.9 2.7 1.5 9 104 95 Freeable 41.1 .0 1.1 2.8 17 39 PL/SQL 9.7 7.2 .1 .2 1 5 92 SQL 5.0 2.0 .1 .2 1 34 37 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 30 J001 -------- 34.2 4.7 28.9 34.2 51.5 Freeable 28.8 .0 28.8 Other 3.8 3.8 3.8 SQL 1.5 1.4 1.5 33.7 PL/SQL .2 .2 .2 5.1 28 MMON -------- 13.4 7.6 4.7 13.4 109.8 Other 8.2 8.2 103.1 Freeable 4.7 .0 4.7 PL/SQL .3 .3 .3 .4 SQL .1 .0 .1 1.7 19 DBW0 -------- 9.3 8.6 .1 9.3 9.5 Other 9.2 9.2 9.4 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 70 Q004 -------- 9.2 4.1 4.8 9.2 12.0 Freeable 4.8 .0 4.8 Other 3.9 3.9 4.9 PL/SQL .4 .4 .4 .4 SQL .1 .0 .1 1.9 82 M004 -------- 8.7 7.1 .6 8.7 11.2 Other 6.8 6.8 6.8 PL/SQL 1.2 1.2 1.2 1.2 Freeable .6 .0 .6 SQL .0 .0 .0 4.5 52 BG00 -------- 6.4 5.4 .0 6.4 6.4 Other 6.3 6.3 6.3 PL/SQL .1 .0 .1 .6 SQL .0 .0 .0 1.0 56 D000 -------- 6.1 5.9 .0 6.1 6.1 Other 6.1 6.1 6.1 72 CJQ0 -------- 5.9 4.2 1.2 5.9 12.2 Other 4.7 4.7 7.8 Freeable 1.2 .0 1.2 SQL .0 .0 .0 3.3 PL/SQL .0 .0 .0 .0 88 ------------ 5.8 4.5 1.0 5.8 15.3 Other 4.2 4.2 10.7 Freeable 1.0 .0 1.0 PL/SQL .4 .4 .4 .4 SQL .2 .0 .2 3.2 79 M001 -------- 5.7 4.3 1.2 5.7 5.7 Other 3.4 3.4 3.4 Freeable 1.2 .0 1.2 PL/SQL 1.1 1.1 1.1 1.1 SQL .0 .0 .0 1.2 80 M002 -------- 5.5 4.8 .4 5.5 9.1 Other 3.9 3.9 4.6 PL/SQL 1.1 .9 1.1 1.1 Freeable .4 .0 .4 SQL .2 .0 .2 3.0 91 ------------ 5.3 4.5 .6 5.3 8.9 Other 4.4 4.4 4.4 Freeable .6 .0 .6 Top Process Memory (by component) DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 91 SQL .2 .0 .2 4.8 PL/SQL .1 .0 .1 .2 71 ------------ 5.3 4.0 .9 5.3 11.0 Other 4.0 4.0 4.0 Freeable .9 .0 .9 SQL .2 .0 .2 3.6 PL/SQL .2 .0 .2 4.8 95 ------------ 5.2 4.2 .0 5.2 9.0 Other 3.2 3.2 3.2 Freeable 1.0 .0 1.0 SQL .2 .0 .2 5.1 PL/SQL .1 .0 .1 .2 85 M008 -------- 5.2 4.2 .8 5.2 5.4 Other 3.7 3.7 3.7 Freeable .8 .0 .8 PL/SQL .6 .6 .6 .6 SQL .1 .1 .1 1.5 90 ------------ 5.1 4.2 .5 5.1 8.2 Other 4.4 4.4 4.4 Freeable .5 .0 .5 SQL .1 .0 .1 3.6 PL/SQL .1 .0 .1 .2 76 ------------ 5.0 3.9 .8 5.0 9.2 Other 3.9 3.9 3.9 Freeable .8 .0 .8 SQL .2 .0 .2 5.0 PL/SQL .1 .0 .1 .2 84 M007 -------- 4.8 4.2 .3 4.8 8.8 Other 4.0 4.0 5.2 PL/SQL .6 .5 .6 .6 Freeable .3 .0 .3 SQL .0 .0 .0 2.7 99 ------------ 4.7 3.4 .6 4.7 7.4 Other 3.7 3.7 3.7 SQL 1.3 1.2 1.3 3.4 Freeable .1 .0 .1 PL/SQL .1 .0 .1 .2 25 LREG -------- 4.6 4.0 .0 4.6 5.1 Other 4.6 4.6 4.6 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .8 E 30 J001 -------- 20.5 2.7 17.4 20.5 51.5 Freeable 17.4 .0 17.4 Other 2.5 2.5 2.5 PL/SQL .5 .4 .5 5.1 SQL .0 .0 .0 33.7 28 MMON -------- 11.7 7.6 3.3 11.7 109.8 Other 7.9 7.9 104.5 Freeable 3.3 .0 3.3 PL/SQL .3 .3 .3 .4 SQL .1 .0 .1 1.7 19 DBW0 -------- 9.8 8.6 .3 9.8 9.8 Other 9.5 9.5 9.5 Top Process Memory (by component) DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 19 Freeable .3 .0 .3 PL/SQL .0 .0 .0 .0 82 M004 -------- 8.2 7.1 .1 8.2 11.2 Other 6.8 6.8 6.8 PL/SQL 1.2 1.2 1.2 1.2 Freeable .1 .0 .1 SQL .0 .0 .0 4.5 70 Q004 -------- 7.4 4.1 2.9 7.4 12.0 Other 3.9 3.9 6.7 Freeable 2.9 .0 2.9 PL/SQL .4 .4 .4 .4 SQL .1 .0 .1 1.9 52 BG00 -------- 6.8 5.8 .0 6.8 6.8 Other 6.7 6.7 6.7 PL/SQL .1 .0 .1 .6 SQL .0 .0 .0 1.0 56 D000 -------- 6.1 5.9 .0 6.1 6.1 Other 6.1 6.1 6.1 61 J000 -------- 5.9 4.7 .8 5.9 5.9 Other 3.6 3.6 3.6 SQL 1.5 1.4 1.5 3.1 Freeable .7 .0 .7 PL/SQL .2 .2 .2 1.0 88 ------------ 5.8 4.5 1.0 5.8 15.3 Other 4.2 4.2 10.7 Freeable 1.0 .0 1.0 PL/SQL .4 .4 .4 .4 SQL .2 .0 .2 3.2 79 M001 -------- 5.4 4.3 .1 5.4 5.7 Other 4.2 4.2 4.2 PL/SQL 1.1 1.1 1.1 1.1 Freeable .1 .0 .1 SQL .0 .0 .0 1.2 72 CJQ0 -------- 5.3 4.2 .6 5.3 12.2 Other 4.7 4.7 8.4 Freeable .6 .0 .6 SQL .0 .0 .0 3.3 PL/SQL .0 .0 .0 .0 71 ------------ 5.3 3.9 1.0 5.3 11.0 Other 4.0 4.0 4.0 Freeable 1.0 .0 1.0 PL/SQL .2 .0 .2 4.8 SQL .1 .0 .1 3.6 80 M002 -------- 5.2 4.8 .1 5.2 9.1 Other 4.0 4.0 4.8 PL/SQL 1.1 .9 1.1 1.1 Freeable .1 .0 .1 SQL .0 .0 .0 3.0 95 ------------ 5.2 4.3 .4 5.2 9.0 Other 4.6 4.6 4.6 Freeable .4 .0 .4 SQL .1 .0 .1 5.1 PL/SQL .1 .0 .1 .2 Top Process Memory (by component) DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 76 ------------ 5.0 3.9 .9 5.0 9.2 Other 3.9 3.9 3.9 Freeable .9 .0 .9 SQL .2 .0 .2 5.0 PL/SQL .1 .0 .1 .2 85 M008 -------- 5.0 4.2 .6 5.0 5.7 Other 3.7 3.7 3.7 PL/SQL .6 .6 .6 .6 Freeable .6 .0 .6 SQL .1 .0 .1 1.5 25 LREG -------- 4.6 4.0 .0 4.6 5.1 Other 4.6 4.6 4.6 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .8 84 M007 -------- 4.6 4.2 .1 4.6 8.8 Other 4.0 4.0 5.4 PL/SQL .6 .5 .6 .6 Freeable .1 .0 .1 SQL .0 .0 .0 2.7 99 ------------ 4.6 3.3 .6 4.6 7.4 Other 3.8 3.8 3.8 Freeable .6 .0 .6 SQL .1 .0 .1 3.4 PL/SQL .1 .0 .1 .2 18 DIA0 -------- 4.6 4.4 .0 4.6 4.6 Other 4.6 4.6 4.6 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: FREE/FREE Snaps: 106-107 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- CR-Reuse Block Range (block range reuse ckpt) 301 302 0 31 581 18,742.19 HW-Segment High Water Mark 474 474 0 6 56 9,341.33 TX-Transaction 23,875 23,875 0 5 18 3,601.00 FB-Format Block 108 108 0 5 10 2,062.60 TX-Transaction (index contention) 10 10 0 10 5 496.70 JG-Job Scheduler1 (queue lock) 77,510 77,510 0 9 4 451.11 ------------------------------------------------------------- Undo Segment Summary DB/Inst: FREE/FREE Snaps: 106-107 -> 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) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 2 3.9 13,908 936 23 30.6/30.6 0/0 0/0/0/0/0/3 634 ------------------------------------------------------------- Undo Segment Stats DB/Inst: FREE/FREE Snaps: 106-107 -> Most recent 35 Undostat rows, ordered by End 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 08-Nov 08:20 3,945 13,908 936 23 31 0/0 0/0/0/0/0/3 634 ------------------------------------------------------------- Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ AIM IM populated segment 1 0.0 0 0 AIM scan ovhd HT parent 1 0.0 0 0 AIM txn ovhd HT parent l 1 0.0 0 0 AQ Background: interrupt 1 0.0 0 46 0.0 AQ Coord jobx_kwsbgsgn l 30 0.0 0 0 AQ Message drive state o 1 0.0 0 0 AQ Sharded master pool l 30 0.0 0 0 AQ deq hash table latch 1 0.0 0 0 ASM db client latch 645 0.0 0 0 ASM map operation hash t 1 0.0 0 0 Active DGPDB source PDB 1 0.0 0 0 Autotask Queue 6 0.0 0 0 Autotask management 13 0.0 0 0 Blocker Resolver wait ev 56 0.0 0 0 Column stats entry latch 1 0.0 0 0 Composite SG context lat 1 0.0 0 0 Consistent RBA 10,065 0.0 0 0 Control file cache acces 2,162 0.0 0 0 DGPDB filesync cache 1 0.0 0 0 DML lock allocation 192,818 0.0 0.5 0 0 Distributed Index Node L 129 0.0 0 0 EDSLib File 1 0.0 0 0 EDSLib Message 1 0.0 0 0 EDSLib Root 1 0.0 0 0 EGSLib Root 1 0.0 0 0 EGSLibDS Root 1 0.0 0 0 EGSLibDataStore Object 1 0.0 0 0 EGSLibDataStoreShard Obj 1 0.0 0 0 EGSLibForum Object 1 0.0 0 0 EGSLibNet Root 1 0.0 0 0 EGSLibSP Root 1 0.0 0 0 EGSLibStoragePool Object 1 0.0 0 0 EGSLibStoragePoolRing Ob 1 0.0 0 0 Event Group Locks 59 0.0 0 0 FIB s.o chain latch 2 0.0 0 0 FOB s.o list parent latc 3 0.0 0 0 FSD File Descriptor latc 1 0.0 0 0 FSD Inode File latch 1 0.0 0 0 FSD Mount Descriptor lat 1 0.0 0 0 FSD block latch 1 0.0 0 0 FSDDB file entry latch 1 0.0 0 0 FSDDB file list bucket 1 0.0 0 0 FSDDB mount table entry 1 0.0 0 0 FSDDB mount table latch 26 0.0 0 0 File State Object Pool P 5 0.0 0 0 FsDirect bvec platch 1 0.0 0 0 FsDirect fblist platch 1 0.0 0 0 FsDirect pool platch 1 0.0 0 0 FsDirect root platch 1 0.0 0 0 GCS logfile block 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ GCS logfile write queue 1 0.0 0 0 HNSW HT Element Latch 1 0.0 0 0 HNSW HT Latch 1 0.0 0 0 HNSW Partition HT Elemen 1 0.0 0 0 HNSW optimization statis 1 0.0 0 0 HNSW parallel insert ctx 1 0.0 0 0 I/O Staticstics latch 1 0.0 0 0 I/O state object list la 37 0.0 0 0 ILM Stats Stripe Latch 1 0.0 0 0 ILM Stats main anchor la 81 0.0 0 0 ILM access tracking exte 1 0.0 0 0 ILM activity tracking la 1 0.0 0 0 IM Global dictionary lat 1 0.0 0 0 IM Global dictionary que 1 0.0 0 0 IM Global dictionary rw 1 0.0 0 0 IM area sb latch 1 0.0 0 0 IM area scb latch 1 0.0 0 0 IM emb latch 1 0.0 0 0 IM seg hdr latch 1 0.0 0 0 IMFS defer write list 1 0.0 0 0 IMXT hash table latch 1 0.0 0 0 IPC stats buffer allocat 1 0.0 0 0 IR Slave latch 15,250 0.0 0 0 IVF Centroids HT Element 1 0.0 0 0 IVF Centroids HT Latch 1 0.0 0 0 IVF Centroids kghx latch 1 0.0 0 0 In memory undo latch 381,431 0.1 0.7 1 25,784 0.0 Intermediate key cache l 1 0.0 0 0 JS IMT Dump latch 38 0.0 0 1,578,482 0.0 JS IMT directory link la 54 0.0 0 0 JS Sh mem access 4,324 0.1 1.0 0 0 JS count autotask runnin 2 0.0 0 0 JS global state obj latc 72 0.0 0 0 JS mem alloc latch 32 0.0 0 0 JS queue access latch 34,502 0.0 0 0 JS queue state obj latch 154,984 0.0 0 0 JS slv state obj latch 146 0.0 0 0 KCNIBR - invalid block r 1 0.0 0 0 KDCStatHash latch 1 0.0 0 0 KDFI Buffer Latch 1 0.0 0 0 KDFI Hash Bucket Latch 1 0.0 0 0 KDFI Hash Elem Latch 1 0.0 0 0 KDIVECQC latch 1 0.0 0 0 KDMADO action list latch 1 0.0 0 0 KDMADO bll latch 1 0.0 0 0 KDMADO bll level 0 latch 1 0.0 0 0 KDMADO latch 1 0.0 0 0 KFC FX Hash Latch 1 0.0 0 0 KFC Hash Latch 1 0.0 0 0 KFCL LE Freelist 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ KFIAS client latch 1 0.0 0 0 KFIAS cluster latch 1 0.0 0 0 KFIAS endpoint latch 1 0.0 0 0 KFIAS node latch 1 0.0 0 0 KFIAS process latch 1 0.0 0 0 KFIAS queue latch 1 0.0 0 0 KGNFS-NFS:SHM structure 1 0.0 0 0 KGNFS-NFS:SVR LIST 1 0.0 0 0 KJC message pool KGH cal 1 0.0 0 0 KJC message pool free li 1 0.0 0 0 KJC message pool pending 1 0.0 0 0 KJC receiver message poo 1 0.0 0 0 KJC snd proxy sub messag 1 0.0 0 0 KJCT flow control latch 1 0.0 0 0 KMG MMAN ready and start 299 0.0 0 0 KSFS In/Output Queue and 599 0.0 0 0 KSFS OFS ctx level paren 1 0.0 0 0 KSFS OFS layer alloc lat 57 0.0 0 0 KSFS OFS req layer paren 1 0.0 0 0 KSFS OFS sess layer pare 1 0.0 0 0 KSFS OFS threads latch 1 0.0 0 0 KSFS Receive and Send Qu 1 0.0 0 0 KSFS id table parent lat 1 0.0 0 0 KSFS ksfs_node latch 1 0.0 0 0 KSFSD name cache parent 1 0.0 0 0 KSGL LS Segment latch 1 0.0 0 0 KSGL SGA anchor latch 1 0.0 0 0 KSIPC ASPC anchor latch 1 0.0 0 0 KSIPC SGA allocation par 1 0.0 0 0 KSIPC SHREG latch 1 0.0 0 0 KSIPCGSN Hash Table latc 1 0.0 0 0 KSIPCGSN Namespace latch 1 0.0 0 0 KSIPCLNT SGA parent latc 1 0.0 0 0 KSK PDB IO STAT 1,197 0.0 0 0 KSNMON Flow Table latch 1 0.0 0 0 KSNMON Index Queue latch 1 0.0 0 0 KSNMON Interface hashtab 1 0.0 0 0 KSPMXS undo latch 1 0.0 0 0 KSPMXS wait bucket pare 1 0.0 0 0 KSPMXS App root latch 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS Unit Test Persist 1 0.0 0 0 KSPMXS managed latches f 1 0.0 0 0 KSPMXS trans table latch 1 0.0 0 0 KSRMA SGA anchor latch 1 0.0 0 0 KSRMF SGA anchor latch 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ KSSPM free list latch 1 0.0 0 0 KSSPM tree latch 1 0.0 0 0 KSTRC Operation Pool lat 1 0.0 0 0 KSTRC SGA allocation par 1 0.0 0 0 KSTRC distributed operat 1 0.0 0 0 KSTRC distributed operat 1 0.0 0 0 KSTRC kstrc_opclassctx l 20,251 0.0 0 0 KSTRC next_mga_idx_kstrc 1 0.0 0 0 KSTRC per MGA opctx list 1 0.0 0 0 KSTRC spare latch 1 0.0 0 0 KSTRC unit test latch 1 0.0 0 0 KSTRCB SGA allocation pa 1 0.0 0 0 KSTRCB unit test latch 1 0.0 0 0 KSWL SGA anchor latch 1 0.0 0 0 KSWP In/Output Queue and 1,791 0.0 0 0 KSWP threads latch 1 0.0 0 0 KSXM OATS MView activity 4 0.0 0 0 KSXM OATS SGA metadata l 8 0.0 0 0 KSXR hang analysis phase 1 0.0 0 0 KSXR hang analysis phase 1 0.0 0 0 KTF sga latch 2 0.0 0 281 0.0 KTILM latch 1 0.0 0 0 KTU in-memory txn table 1 0.0 0 0 KTURMA ODPSCN latch 1 0.0 0 0 KTURMA SGA allocation pa 1 0.0 0 0 KTURMA cache alloc latch 1 0.0 0 0 KWQMN job cache list lat 99 0.0 0 0 KWQP Prop Status 2 0.0 0 0 Latch for NonceHistory 1 0.0 0 0 Locator state objects po 1 0.0 0 0 Lost write change tracki 1 0.0 0 0 Lsod array latch 1 0.0 0 0 MGA asr alloc latch 15 0.0 0 0 MGA heap latch 1 0.0 0 0 MGA pid list latch 1 0.0 0 0 MGA pid object latch 1 0.0 0 0 MGA shared context latch 1 0.0 0 0 MGA shared context root 15 0.0 0 0 MMON per PDB bit map lat 2 0.0 0 0 MQL Tracking Latch 0 0 36 0.0 Memory Management Latch 0 0 299 0.0 Memory Queue 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Subscriber 1 0.0 0 0 Message cache latch 1 0.0 0 0 MinActiveScn Latch 56 0.0 0 0 Mutex 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Mutex Stats 1 0.0 0 0 New MC recovery requests 1 0.0 0 0 Nologging Standby SCN Ti 279 0.0 0 0 OFS mount parent latch 1 0.0 0 0 OFS request latch 1 0.0 0 0 OS process 1,086 1.0 1.0 0 0 OS process allocation 1,869 0.0 0 0 OS process: request allo 37 0.0 0 0 PDB Hash Table Latch 6 0.0 0 0 PDB LRU structure 2 0.0 0 0 PL/SQL warning settings 39 0.0 0 0 PX hash array latch 1 0.0 0 0 Parent latch for depende 1 0.0 0 0 Parent latch for query h 1 0.0 0 0 Parent latch for segment 1 0.0 0 0 QMT 1 0.0 0 0 Queue entry latch 1 0.0 0 0 Report Request stats lat 68 0.0 0 0 Report Request struct la 3 0.0 0 0 Request holder compeltio 39 0.0 0 0 Result Cache: Blocklist 13 0.0 0 0 Result Cache: Main Latch 146 0.0 0 0 Result Cache: SO Latch 26 0.0 0 0 Result Cache: Set 97 0.0 0 0 Retry Ht elm latch 1 0.0 0 0 Retry bkt latch 1 0.0 0 0 SGA IO buffer pool latch 2,205 0.0 0 2,244 0.0 SGA Logging Bkt Latch 1 0.0 0 0 SGA Logging Log Latch 1,209 0.0 0 0 SGA blob parent 1 0.0 0 0 SGA bucket locks 1 0.0 0 0 SGA heap locks 1 0.0 0 0 SGA pool locks 1 0.0 0 0 SO cache branch latch 1 0.0 0 0 SO cache latch 1 0.0 0 0 SO cache root latch 1 0.0 0 0 SO private so latch 21 0.0 0 26 0.0 SQL memory manager latch 1 0.0 0 299 0.0 SQL memory manager worka 30,205 0.0 0 0 SR Stats Stripe Latch 1 0.0 0 0 Sched IM Job latch 1 0.0 0 0 Sched InMem Job Cache 6 0.0 0 0 Scheduler group context 7,157 0.0 0 0 Shared B-Tree 36 0.0 0 0 Shared fixed UGA latch 1 0.0 0 0 Snapshot Queue Latch 1 0.0 0 0 Streams Generic 1 0.0 0 0 Subscriber Ht elm latch 1 0.0 0 0 Testing 1 0.0 0 0 Text Monitor latch 1,191 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Text PDB context latch 1 0.0 0 0 Text Waiting Queue latch 299 0.0 0 0 Text batch latch 1 0.0 0 0 Text batch ready list ta 299 0.0 0 0 Token Manager 1 0.0 0 0 Txn Ht elm latch 1 0.0 0 0 Txn bkt latch 1 0.0 0 0 UM object latch 1 0.0 0 0 WCR: sync 1 0.0 0 0 Write State Object Pool 1 0.0 0 0 XDB NFS Security Latch 1 0.0 0 0 XDB unused session pool 1 0.0 0 0 XDB used session pool 1 0.0 0 0 act trk latch 678 0.0 0 0 active checkpoint queue 274 0.0 0 0 active service list 79,039 0.0 0 11,341 0.0 archive destination 32 0.0 0 0 base template latch 1 0.0 0 0 begin backup scn array 29 0.0 0 0 buffer cache nvm promoti 1 0.0 0 0 buffer pool 1 0.0 0 0 business card 1 0.0 0 0 cache buffer handles 1,222 0.1 0.0 0 0 cache buffers chains 25,890,820 0.0 0.3 3 5,171,366 0.0 cache buffers lru chain 400,750 1.4 0.5 21 804,403 2.2 cache table scan latch 103 0.0 0 103 0.0 call allocation 1,827 0.0 0 0 change notification clie 1 0.0 0 0 channel handle pool latc 75 0.0 0 0 channel operations paren 299 0.0 0 0 checkpoint queue latch 144,834 0.0 0.3 0 103,364 0.0 client/application info 1,597,074 0.2 0.2 6 0 compile environment latc 38 0.0 0 0 corrupted undo seg latch 240 0.0 0 0 cp handoff latch 1 0.0 0 0 cp pdb latch 599 0.0 0 0 cp pool latch 1 0.0 0 0 cp server hash latch 1 0.0 0 0 cp srv type state latch 599 0.0 0 0 cp srv type wait latch 1 0.0 0 0 cr slave free list 1 0.0 0 0 cvmap freelist lock 1 0.0 0 0 dispatcher info 2 0.0 0 0 done queue latch 1 0.0 0 0 dtp latch 36 0.0 0 0 dummy allocation 74 0.0 0 0 edge index parent latch 1 0.0 0 0 eighteenth generic spare 1 0.0 0 0 eighteenth spare latch - 1 0.0 0 0 eighth pdb spare latch - 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ eighth pdb_modec spare l 1 0.0 0 0 eighth pdb_ns spare latc 1 0.0 0 0 eleventh generic spare l 1 0.0 0 0 eleventh pdb spare latch 1 0.0 0 0 eleventh pdb_modec spare 1 0.0 0 0 eleventh pdb_ns spare la 1 0.0 0 0 eleventh spare latch - X 1 0.0 0 0 enqueue freelist latch 1 0.0 0 324,068 0.0 enqueue hash chains 591,738 0.1 0.5 1 199 1.0 enqueues 83 0.0 0 0 fast space usage latch 17 0.0 0 0 fifteenth generic spare 1 0.0 0 0 fifteenth pdb spare latc 1 0.0 0 0 fifteenth pdb_modec spar 1 0.0 0 0 fifteenth pdb_ns spare l 1 0.0 0 0 fifteenth spare latch - 1 0.0 0 0 fiftieth generic spare l 1 0.0 0 0 fiftieth spare latch - S 1 0.0 0 0 file cache latch 679 0.0 0 2 0.0 flash file access latch 1 0.0 0 0 flashback copy 1 0.0 0 0 fortieth generic spare l 1 0.0 0 0 forty-eighth generic spa 1 0.0 0 0 forty-eighth spare latch 1 0.0 0 0 forty-fifth generic spar 1 0.0 0 0 forty-fifth spare latch 1 0.0 0 0 forty-first generic spar 1 0.0 0 0 forty-first spare latch 1 0.0 0 0 forty-fourth generic spa 1 0.0 0 0 forty-fourth spare latch 1 0.0 0 0 forty-ninth generic spar 1 0.0 0 0 forty-ninth spare latch 1 0.0 0 0 forty-second generic spa 1 0.0 0 0 forty-second spare latch 1 0.0 0 0 forty-seventh generic sp 1 0.0 0 0 forty-seventh spare latc 1 0.0 0 0 forty-sixth generic spar 1 0.0 0 0 forty-sixth spare latch 1 0.0 0 0 forty-third generic spar 1 0.0 0 0 forty-third spare latch 1 0.0 0 0 fourteenth generic spare 1 0.0 0 0 fourteenth pdb spare lat 1 0.0 0 0 fourteenth pdb_modec spa 1 0.0 0 0 fourteenth pdb_ns spare 1 0.0 0 0 fourteenth spare latch - 1 0.0 0 0 fourth Audit Vault latch 1 0.0 0 0 gc element 1 0.0 0 0 gcs commit scn state 1 0.0 0 0 gcs lock state resolutio 1 0.0 0 0 gcs lock state resolutio 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ gcs lsr state freelist 1 0.0 0 0 gcs opaque info freelist 1 0.0 0 0 gcs partitioned table ha 1 0.0 0 0 gcs pcm hashed value buc 1 0.0 0 0 gcs resource defer fixwr 1 0.0 0 0 gcs resource freelist 1 0.0 0 0 gcs resource hash 1 0.0 0 0 gcs resource scan list 1 0.0 0 0 gcs resource timerq list 1 0.0 0 0 gcs resource validate li 1 0.0 0 0 gcs rmt lock list per in 1 0.0 0 0 gcs shadows freelist 1 0.0 0 0 ges cached resource list 1 0.0 0 0 ges domain table 1 0.0 0 0 ges enqueue table freeli 1 0.0 0 0 ges group table 1 0.0 0 0 ges process hash list 1 0.0 0 0 ges process parent latch 1 0.0 0 0 ges remote lock freelist 1 0.0 0 0 ges resource hash list 1 0.0 0 0 ges resource scan list 1 0.0 0 0 ges resource table freel 1 0.0 0 0 ges timeout list 1 0.0 0 0 ges value block free lis 1 0.0 0 0 global KZLD latch for au 4 0.0 0 0 global tx hash mapping 1 0.0 0 0 granule from data transf 1 0.0 0 0 gws:Sharding global desc 120 0.0 0 0 hang blkrs cache mem 149,408 0.0 0 0 hash table Sql Plan Find 38 0.0 0 0 hash table column usage 58 0.0 0 17 0.0 hash table expression us 1 0.0 0 0 hash table modification 1 0.0 0 0 heartbeat check 1 0.0 0 0 imc buf hdl lat 1 0.0 0 0 imc cr clone buf 1 0.0 0 0 imc hash lat 1 0.0 0 0 imc sj lat 1 0.0 0 0 imc srchsp lat 1 0.0 0 0 imc-txn-wrk-lat 1 0.0 0 0 in-memory area latch 1 0.0 0 0 in-memory columnar segme 1 0.0 0 0 in-memory columnar ts ex 1 0.0 0 0 in-memory global pool la 1 0.0 0 0 intra txn parallel recov 1 0.0 0 0 io pool granule list 18 0.0 0 0 io pool granule metadata 51 0.0 0 0 job workq parent latch 19 0.0 0 18 0.0 job_queue_processes free 74 0.0 0 0 jslv pdb context latch 30 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ k2q lock allocation 1 0.0 0 0 kKCN Cqn SGA 1 0.0 0 0 kcb DW scan objtemp hash 1 0.0 0 0 kcb TC keep hash table l 1 0.0 0 0 kcbtsemkid latch 1 0.0 0 0 kcn buffer chains 1 0.0 0 0 kdlx hb parent latch 1 0.0 0 0 kgb parent 1 0.0 0 0 kgnfs mount latch 1 0.0 0 0 kjbr roi instance map 1 0.0 0 0 kjci objects freelist la 7 0.0 0 0 kjci process context lat 81 0.0 0 14,564 0.0 kjci process list latch 4 0.0 0 0 kjci request sequence la 1 0.0 0 0 kjcipctx state object fr 2 0.0 0 0 kjoeq omni enqueue hash 1 0.0 0 0 kjoer owner hash bucket 1 0.0 0 0 kokc descriptor allocati 40 0.0 0 0 kpoxcAppContMaxInstanceS 70 0.0 0 0 kradcm msg queue latch 1 0.0 0 0 krso process 35 0.0 0 0 ksacl hash table 300 0.0 0 0 ksbsg action descriptor 5,185 0.0 0 0 ksbsg action group descr 5,354 0.0 0 0 ksbsg context latch 3,579 0.0 0 0 ksbsg instance recovery 299 0.0 0 0 kscht test latch 1 0.0 0 0 ksevn object root latch 293 0.0 0 0 ksfs dircache queue latc 14 0.0 0 0 ksfv messages 1 0.0 0 0 ksi resource reuse count 1 0.0 0 0 ksim group membership ca 1 0.0 0 0 ksm_pga_um_list_latch 2 0.0 0 0 ksm_pga_um_root_latch 888 0.0 0 0 ksns object latch 1 0.0 0 0 kspmfut context latch 1 0.0 0 0 ksrk latch 1 0.0 0 0 ksrk memory op latch 1 0.0 0 0 ksrk partition latch 1 0.0 0 0 ksrk session latch 1 0.0 0 0 kss move lock 37 0.0 0 0 ksstat sctx latch 1 0.0 0 0 kstrcbPoolBucket segment 1 0.0 0 0 kstrcbPoolBuf MGA segmen 1 0.0 0 0 kstrcbPoolBuf SGA segmen 1 0.0 0 0 kstream global latch 1 0.0 0 0 kstream topic latch 1 0.0 0 0 ksuosstats global area 63 0.0 0 343 0.0 ksv allocation latch 71 0.0 0 0 ksv class latch 586 0.2 2.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ksv msg queue latch 75 1.3 1.0 0 74 1.4 ksz_so allocation latch 37 0.0 0 0 ktcti pdb state latch 525 0.0 0 0 ktfbn latch 566 0.0 0 0 ktm adg jrnls 1 0.0 0 0 ktm adg lookuptable leve 1 0.0 0 0 ktm adg lookuptable leve 1 0.0 0 0 ktm adg space 1 0.0 0 0 ktm adg stages 1 0.0 0 0 ktm adg worklink 1 0.0 0 0 ktm global data 107 0.0 0 0 ktm prv jrnls 1 0.0 0 0 ktmpj AO ls 1 0.0 0 0 ktmpj HT ls 1 0.0 0 0 kturma rtxntab latch 1 0.0 0 0 ktv prv jrnls 1 0.0 0 0 ktvpj HT ls 1 0.0 0 0 kwqbsn:qsga 34 0.0 0 0 kwslbmdl: metadata latch 31 0.0 0 0 kwslbql: queue latch 1 0.0 0 0 kwslbutl: uncommitted tx 1 0.0 0 0 kwsptQcachLt: queue cach 1 0.0 0 0 kwsptTrncTsksLt: trunc t 1 0.0 0 0 kwsptjobAdPtLt: AdPt lis 33 0.0 0 0 kwsslLat: skiplist latch 1 0.0 0 0 kxfxscanrate_latch 1 0.0 0 0 lang hdl init latch 38 0.0 0 0 lgwr LWN SCN 10,071 0.0 1.0 0 0 list of block allocation 88 0.0 0 0 lob segment dispenser la 99 0.0 0 0 lob segment hash table l 172 0.0 0 0 lob segment query latch 1 0.0 0 0 lock DBA buffer during m 1 0.0 0 0 log write info 0 0 10,063 0.0 log write worker phase 1 0.0 0 0 logical standby cache 1 0.0 0 0 logminer context allocat 1 0.0 0 0 logminer local 1 0.0 0 0 logminer work area 1 0.0 0 0 longop free list parent 1 0.0 0 0 lreg configuration 299 0.0 0 0 managed standby 30 0.0 0 0 message bitmap latch 1 0.0 0 0 message pool operations 94 0.0 0 0 messages 65,927 0.0 0.2 0 0 msg queue latch 1 0.0 0 0 multi-plans bitvector la 1 0.0 0 0 multiblock read objects 68,502 0.0 0.0 0 0 name-service namespace b 1 0.0 0 0 ncodef allocation latch 15 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ nineteenth generic spare 1 0.0 0 0 nineteenth spare latch - 1 0.0 0 0 ninth pdb spare latch - 1 0.0 0 0 ninth pdb_modec spare la 1 0.0 0 0 ninth pdb_ns spare latch 1 0.0 0 0 nodemap pool 1 0.0 0 0 object affinity queue he 1 0.0 0 0 object queue header free 1,953 0.0 0 0 object queue header oper 1,606,153 0.0 0.4 0 0 object queue memory 1 0.0 0 0 object stats modificatio 312 0.0 0 0 parallel query alloc buf 1 0.0 0 120 0.0 parallel query stats 1 0.0 0 0 parameter list 2 0.0 0 0 parameter table fixed al 1,051 0.0 0 1,140 0.0 parameter table manageme 337 0.0 0 0 parameter table manageme 537 0.0 0 0 pdb enqueue hash chains 1 0.0 0 0 pdb stats rollup 4,906 0.0 0 0 peshm 1 0.0 0 0 pesom_free_list 1 0.0 0 0 pesom_hash_node 1 0.0 0 0 pkey global statistics 1 0.0 0 0 plan existence bitvector 1 0.0 0 0 pmem direct commit boc 1 0.0 0 0 pmem direct commit slb 1 0.0 0 0 pmon dead latch 14 0.0 0 0 post/wait queue 357,987 0.1 0.2 0 364,285 0.0 process allocation 56 0.0 0 0 process group creation 37 0.0 0 0 process memory detail 1 0.0 0 0 process pkey statistics 1 0.0 0 0 process queue 1 0.0 0 0 process queue reference 1 0.0 0 0 qmn task queue latch 202 2.5 1.0 0 0 quarantine object 14 0.0 0 0 query server freelists 1 0.0 0 0 query server process 595 0.0 0 0 queuing load statistics 1 0.0 0 0 read-mostly lock queue u 1 0.0 0 0 recovery domain hash buc 1 0.0 0 0 redo allocation 212,215 0.1 0.4 1 0 redo copy 21 71.4 1.0 0 140,704 0.4 redo gen encryption key 1 0.0 0 0 redo transport task 5 0.0 0 0 redo writing 30,948 0.0 0 0 remote tool request latc 35 0.0 0 0 request defer latch 878 0.0 0 0 resmgr group change latc 265 0.0 0 0 resmgr:active threads 439 0.0 0 9,385 0.0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ resmgr:actses change gro 262 0.0 0 0 resmgr:actses change sta 74 0.0 0 0 resmgr:free threads list 229 0.0 0 0 resmgr:plan CPU method 1 0.0 0 0 resmgr:plan change alloc 1 0.0 0 0 resmgr:plan change latch 1 0.0 0 0 resmgr:resource group CP 2,110 0.0 0.0 0 655 0.3 resmgr:schema config 172 0.0 0 0 resmgr:session queuing 1 0.0 0 0 resmgr:vc list latch 1 0.0 0 0 rules engine rule set st 100 0.0 0 0 second Audit Vault latch 1 0.0 0 0 sequence cache 35,323 0.1 0.6 0 0 sequence cache free list 70,650 0.0 0.2 0 0 session allocation 21,779 0.1 0.3 0 21,741 0.1 session idle bit 259,259 0.0 0.0 0 0 session queue latch 1 0.0 0 0 session signature latch 1 0.0 0 0 session state list latch 76 0.0 0 0 session statistics 74 0.0 0 0 session switching 32 0.0 0 0 session template latch 1 0.0 0 0 session template new lis 1 0.0 0 0 session timer 299 0.0 0 0 seventeenth generic spar 1 0.0 0 0 seventeenth spare latch 1 0.0 0 0 seventh pdb spare latch 1 0.0 0 0 seventh pdb_modec spare 1 0.0 0 0 seventh pdb_ns spare lat 1 0.0 0 0 sga hash table parent la 21 0.0 0 0 shard latch 1 0.0 0 0 shardgroup list latch 12 0.0 0 0 shared pool 7,690 0.0 0 0 shared pool sim alloc 3 0.0 0 0 shared pool simulator 23 0.0 0 0 shared server configurat 299 0.0 0 0 sim partition latch 1 0.0 0 0 simulator hash latch 925,396 0.0 0 0 simulator lru latch 133,146 0.0 0 777,304 0.6 sixteenth generic spare 1 0.0 0 0 sixteenth spare latch - 1 0.0 0 0 sixth pdb spare latch - 1 0.0 0 0 sixth pdb_modec spare la 1 0.0 0 0 sixth pdb_ns spare latch 1 0.0 0 0 sort extent pool 32 0.0 0 0 space background state o 27 0.0 0 0 space background task la 409 2.9 0.8 0 608 0.0 statistics rollup 163 0.0 0 0 subscriber Ht bkt 1 0.0 0 0 subshard operation latch 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ tablespace key chain 1 0.0 0 0 temp lob duration state 2 0.0 0 0 temporary table state ob 4 0.0 0 0 tenth pdb spare latch - 1 0.0 0 0 tenth pdb_modec spare la 1 0.0 0 0 tenth pdb_ns spare latch 1 0.0 0 0 test excl. parent l0 1 0.0 0 0 test excl. parent2 l0 1 0.0 0 0 test excl. parent2 lmid 1 0.0 0 0 test mixed exclusive 1 0.0 0 0 test mode exclusive 1 0.0 0 0 test open exclusive 1 0.0 0 0 test pdb exclusive 1 0.0 0 0 test shared parent2 lmid 1 0.0 0 0 thirteenth generic spare 1 0.0 0 0 thirteenth pdb spare lat 1 0.0 0 0 thirteenth pdb_modec spa 1 0.0 0 0 thirteenth pdb_ns spare 1 0.0 0 0 thirteenth spare latch - 1 0.0 0 0 thirtieth spare latch - 1 0.0 0 0 thirty-eighth generic sp 1 0.0 0 0 thirty-fifth generic spa 1 0.0 0 0 thirty-first generic spa 1 0.0 0 0 thirty-fourth generic sp 1 0.0 0 0 thirty-ninth generic spa 1 0.0 0 0 thirty-second generic sp 1 0.0 0 0 thirty-seventh generic s 1 0.0 0 0 thirty-sixth generic spa 1 0.0 0 0 thirty-third generic spa 1 0.0 0 0 threshold alerts latch 58 0.0 0 0 transaction allocation 147 0.0 0 0 transaction branch alloc 1 0.0 0 0 twelfth generic spare la 1 0.0 0 0 twelfth pdb spare latch 1 0.0 0 0 twelfth pdb_modec spare 1 0.0 0 0 twelfth pdb_ns spare lat 1 0.0 0 0 twelfth spare latch - X 1 0.0 0 0 twentieth generic spare 1 0.0 0 0 twentieth spare latch - 1 0.0 0 0 twenty-eighth spare latc 1 0.0 0 0 twenty-fifth pdb spare l 1 0.0 0 0 twenty-fifth pdb_modec s 1 0.0 0 0 twenty-fifth pdb_ns spar 1 0.0 0 0 twenty-fifth spare latch 1 0.0 0 0 twenty-first pdb spare l 1 0.0 0 0 twenty-first pdb_modec s 1 0.0 0 0 twenty-first pdb_ns spar 1 0.0 0 0 twenty-first spare latch 1 0.0 0 0 twenty-fourth pdb spare 1 0.0 0 0 twenty-fourth pdb_modec 1 0.0 0 0 Latch Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ twenty-fourth pdb_ns spa 1 0.0 0 0 twenty-fourth spare latc 1 0.0 0 0 twenty-ninth spare latch 1 0.0 0 0 twenty-second pdb spare 1 0.0 0 0 twenty-second pdb_modec 1 0.0 0 0 twenty-second pdb_ns spa 1 0.0 0 0 twenty-second spare latc 1 0.0 0 0 twenty-seventh spare lat 1 0.0 0 0 twenty-sixth spare latch 1 0.0 0 0 twenty-third pdb spare l 1 0.0 0 0 twenty-third pdb_modec s 1 0.0 0 0 twenty-third pdb_ns spar 1 0.0 0 0 twenty-third spare latch 1 0.0 0 0 undo global data 131,694 0.0 0.4 0 0 virtual circuit buffers 1 0.0 0 0 virtual circuit holder 1 0.0 0 0 virtual circuit queues 1 0.0 0 0 worker slave queue 1 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers lru chain 400,750 5,802 2,617 3,202 client/application info 1,597,074 2,891 709 2,182 cache buffers chains 25,890,820 1,531 425 1,106 enqueue hash chains 591,738 342 157 185 In memory undo latch 381,431 269 184 86 redo allocation 212,215 229 101 128 post/wait queue 357,987 179 39 140 object queue header operat 1,606,153 88 38 50 sequence cache 35,323 42 26 16 session allocation 21,779 28 7 21 DML lock allocation 192,818 26 14 12 undo global data 131,694 17 6 11 sequence cache free list 70,650 17 4 13 redo copy 21 15 15 1 space background task latc 409 12 10 2 OS process 1,086 11 11 0 messages 65,927 9 2 7 qmn task queue latch 202 5 5 0 JS Sh mem access 4,324 5 5 0 checkpoint queue latch 144,834 3 1 2 lgwr LWN SCN 10,071 1 1 0 ksv msg queue latch 75 1 1 0 ksv class latch 586 1 2 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: FREE/FREE Snaps: 106-107 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- DML lock allocation ktaiam 0 10 10 DML lock allocation ktaidm 0 4 4 In memory undo latch ktiFlush: child 0 102 115 In memory undo latch ktichg: child 0 54 58 In memory undo latch kticmt: child 0 30 17 In memory undo latch ktiFlushTopTxn 0 2 0 In memory undo latch kticflush_2 0 2 0 In memory undo latch ktiFlushMe 0 1 0 JS Sh mem access jsksGetShMemLatch 0 5 5 OS process ksoreq_reply 0 11 0 cache buffers chains kcbgtcr: convert latch 4 0 72 0 cache buffers chains kcbgtcr: fast path pin 0 72 159 cache buffers chains kcbzgb: exit_loop 0 50 12 cache buffers chains kcbget: new pin 0 35 10 cache buffers chains kcbgtcr: fast path exam 0 35 77 cache buffers chains kcbzibmlt: multi-block rea 0 30 0 cache buffers chains kcbgtcr: convert latch 0 26 0 cache buffers chains kcbzgb: scan from tail now 0 20 0 cache buffers chains kcbgcur: fast path excl 0 15 11 cache buffers chains kcbrls: fast pin release 0 11 59 cache buffers chains kcbgtcr: retry after read 0 10 0 cache buffers chains kcbzib: finish processing 0 9 1 cache buffers chains kcbget: fast path 0 8 11 cache buffers chains kcbnlc 0 5 8 cache buffers chains kcbzibmlt: finish free buf 0 4 8 cache buffers chains kcbbxsv 0 4 14 cache buffers chains kcbzibmlt: check lock 0 3 2 cache buffers chains kcbbic2 0 3 14 cache buffers chains kcbnew_1 0 3 0 cache buffers chains kcbzwc 0 3 1 cache buffers chains kcbnew: new latch again 0 2 0 cache buffers chains kcbget: release shr 0 2 4 cache buffers chains kcbgcur: fast path shr 0 2 6 cache buffers chains kcbchg: change complete 0 2 1 cache buffers chains kcb_pre_apply 0 2 2 cache buffers chains kcb_commit 0 1 4 cache buffers chains kcbchg: aux pin 0 1 0 cache buffers chains kcbzwb 0 1 0 cache buffers chains kcbbic1 0 1 1 cache buffers chains KCBZ_EXCHANGE_RELEASE: shr 0 1 1 cache buffers chains kcbrls: pin release 0 1 0 cache buffers chains kcbgtcr: slow path shr 0 1 0 cache buffers chains kcb_post_apply 0 1 0 cache buffers lru chain kcbzgb_1 0 1,907 2,241 cache buffers lru chain kcbzgws 0 649 172 cache buffers lru chain kcbzgb 0 44 176 cache buffers lru chain kcbbic2 0 9 25 cache buffers lru chain kcbbxsv: move to being wri 0 7 0 cache buffers lru chain kcbbwlru 0 1 1 checkpoint queue latch kcbklbc_cql 0 1 0 client/application info ksuinfos_modact 0 727 728 client/application info kskirefrattrmap 0 1 0 ksv class latch ksvclsl: checkpool 0 2 0 Latch Miss Sources DB/Inst: FREE/FREE Snaps: 106-107 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- ksv msg queue latch ksvmql: getwork 0 1 1 messages ksaamb: after wakeup 0 1 1 messages ksarcv 0 1 0 mostly latch-free SCN kcs024 0 1 0 object queue header oper kcbo_switch_q_bg 0 16 2 object queue header oper kcbo_link_q 0 12 10 object queue header oper kcbo_switch_cq 0 7 23 object queue header oper kcbo_unlink_q 0 3 3 post/wait queue ksliwat:add:nowait 0 23 0 post/wait queue ksliwat:remove 0 16 39 qmn task queue latch kwqmnmvtsks: delay to read 0 5 0 redo allocation kcrfw_redo_gen: redo alloc 0 91 84 redo allocation kcrfw_redo_write: before w 0 9 4 redo allocation kcrfw_post: more space 0 1 13 redo copy kcrfw_redo_gen: nowait 0 14 0 redo copy kcrfw_redo_gen: wait 0 1 15 sequence cache kdnnxt: cached seq 0 26 26 sequence cache free list kdnssd 0 4 0 session allocation ksuxds_int: free 0 6 7 session allocation ksucri_int : SSO 0 1 0 space background task la ktsj_grab_task 0 4 10 space background task la ktsjCreateTask 0 2 0 space background task la ktsj_smco_movetasks 0 2 0 space background task la ktsj_detach_task 0 1 0 space background task la ktsj_smco_purgeexpired 0 1 0 undo global data ktudnx:child 0 4 2 undo global data ktudba: KSLBEGIN 0 2 0 unknown latch ksqgtl3 0 66 86 unknown latch ksqrcl 0 54 54 unknown latch ksqcnl 0 35 14 unknown latch ksq_local_blocker_bytype 0 2 0 ------------------------------------------------------------- Mutex Sleep DB/Inst: FREE/FREE Snaps: 106-107 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Library Cache kglpin1 4 1,962 10.3 Library Cache kglpndl1 95 1,032 5.4 Library Cache kglhdgn2 106 337 1.8 Cursor Pin kksfbc [KKSCHLFSP2] 157 0.8 Cursor Pin kksLockDelete [KKSCHLPIN6] 93 0.5 Library Cache kglobpn1 71 84 0.4 Library Cache kglhdgn1 62 71 0.3 Cursor Stat qesdpDPCSetupNextExec [KKSSTALOC 2,218 0.3 Cursor Stat qesdpIsResolved [KKSSTALOC14] 52 0.3 Cursor Pin kksxsccmp [KKSCHLPIN5] 16 0.1 Library Cache kglpnal1 90 12 0.0 ------------------------------------------------------------- Segments by Logical Reads DB/Inst: FREE/FREE Snaps: 106-107 -> End Segment Logical Reads Threshold: 10000 -> Pct Total shows % of logical reads for each top segment compared with total logical reads for all segments captured by the Snapshot Subobject Obj. Logical Pct Owner Tablespace Object Name Name Type Reads Total ---------- ---------- -------------------- ------------ ----- ------------ ----- SOE SOE ORDERS TABLE 3,232,112 25.4 SOE SOE INVENTORY_PK INDEX 2,149,648 16.9 SOE SOE PRD_DESC_PK INDEX 1,507,008 11.9 SOE SOE INVENTORIES TABLE 1,005,584 7.9 SOE SOE PRODUCT_DESCRIPTIONS TABLE 934,272 7.4 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: FREE/FREE Snaps: 106-107 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Pct Owner Tablespace Object Name Name Type Reads Total ---------- ---------- -------------------- ------------ ----- ------------ ----- SOE SOE INVENTORIES TABLE 204,307 35.8 SOE SOE ORDERS TABLE 162,689 28.5 SOE SOE CUSTOMERS TABLE 24,555 4.3 SOE SOE ADDRESSES TABLE 21,243 3.7 SOE SOE CARD_DETAILS TABLE 18,777 3.3 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: FREE/FREE Snaps: 106-107 -> End Segment Buffer Busy Waits Threshold: 100 Buffer Subobject Obj. Busy Pct Owner Tablespace Object Name Name Type Waits Total ---------- ---------- -------------------- ------------ ----- ------------ ----- SOE SOE ORDER_ITEMS TABLE 264 31.1 SOE SOE ORDERS TABLE 209 24.6 SOE SOE CARDDETAILS_CUST_IX INDEX 113 13.3 SOE SOE ITEM_PRODUCT_IX INDEX 110 13.0 SOE SOE LOGON TABLE 55 6.5 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: FREE/FREE Snaps: 106-107 ->"Pct Misses" should be very low (< 2% in most cases) ->"Final Usage" is the number of cache entries being used in End Snapshot Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 51 0.0 0 4 2 dc_global_oids 32 0.0 0 0 74 dc_histogram_defs 45 53.3 0 0 8,625 dc_objects 2,080 0.3 0 22 5,741 dc_profiles 14 0.0 0 0 1 dc_rollback_segments 2,031 0.0 0 0 111 dc_segments 318 1.9 0 20 2,141 dc_sequences 5 0.0 0 5 22 dc_tablespace_quotas 13 0.0 0 0 3 dc_tablespaces 189 0.0 0 0 25 dc_users 1,175 0.0 328 0.0 0 474 outstanding_alerts 14 0.0 0 0 11 sch_lj_oids 32 0.0 0 0 68 ------------------------------------------------------------- Library Cache Activity DB/Inst: FREE/FREE Snaps: 106-107 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- AUDIT POLICY 340 0.0 340 0.0 0 0 BODY 43,595 0.0 240,423 -0.0 0 0 DBLINK 3 33.3 0 0 0 EDITION 25 0.0 50 0.0 0 0 INDEX 2 0.0 2 0.0 0 0 SCHEMA 24 0.0 0 0 0 SQL AREA 592 2.4 703,569 -0.0 1 1 SQL AREA BUILD 10 100.0 0 0 0 SQL AREA STATS 10 100.0 10 100.0 0 0 TABLE/PROCEDURE 382 0.5 488,090 0.0 0 0 TRIGGER 4 0.0 4 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: FREE/FREE End Snap: 107 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 496 .6 84 3,751 ####### 1.0 1,483 14.4 882,803 592 .7 180 6,469 ####### 1.0 363 3.5 886,090 688 .8 233 8,649 ####### 1.0 103 1.0 886,729 720 .8 233 8,649 ####### 1.0 103 1.0 886,729 736 .8 233 8,649 ####### 1.0 103 1.0 886,729 752 .9 233 8,649 ####### 1.0 103 1.0 886,729 768 .9 233 8,649 ####### 1.0 103 1.0 886,729 784 .9 233 8,649 ####### 1.0 103 1.0 886,729 800 .9 233 8,649 ####### 1.0 103 1.0 886,729 816 .9 233 8,649 ####### 1.0 103 1.0 886,729 832 .9 233 8,649 ####### 1.0 103 1.0 886,729 848 1.0 233 8,649 ####### 1.0 103 1.0 886,729 864 1.0 233 8,649 ####### 1.0 103 1.0 886,729 880 1.0 233 8,649 ####### 1.0 103 1.0 886,729 896 1.0 233 8,649 ####### 1.0 103 1.0 886,729 912 1.0 233 8,649 ####### 1.0 103 1.0 886,729 928 1.1 233 8,649 ####### 1.0 103 1.0 886,729 944 1.1 233 8,649 ####### 1.0 103 1.0 886,729 960 1.1 233 8,649 ####### 1.0 103 1.0 886,729 976 1.1 233 8,649 ####### 1.0 103 1.0 886,729 992 1.1 233 8,649 ####### 1.0 103 1.0 886,729 1,008 1.1 233 8,649 ####### 1.0 103 1.0 886,729 1,024 1.2 233 8,649 ####### 1.0 103 1.0 886,729 1,072 1.2 233 8,649 ####### 1.0 103 1.0 886,729 1,168 1.3 233 8,649 ####### 1.0 103 1.0 886,729 1,264 1.4 233 8,649 ####### 1.0 103 1.0 886,729 1,360 1.5 233 8,649 ####### 1.0 103 1.0 886,729 1,456 1.7 233 8,649 ####### 1.0 103 1.0 886,729 1,552 1.8 233 8,649 ####### 1.0 103 1.0 886,729 1,648 1.9 233 8,649 ####### 1.0 103 1.0 886,729 1,744 2.0 233 8,649 ####### 1.0 103 1.0 886,729 1,840 2.1 233 8,649 ####### 1.0 103 1.0 886,729 ------------------------------------------------------------- SGA Target Advisory DB/Inst: FREE/FREE End Snap: 107 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 768 .5 56,756 2.8 19,581,989 1,152 .8 23,776 1.2 2,320,308 1,536 1.0 20,143 1.0 1,063,337 1,920 1.3 19,719 1.0 990,817 2,304 1.5 19,722 1.0 990,817 2,688 1.8 19,722 1.0 990,817 3,072 2.0 19,722 1.0 990,817 ------------------------------------------------------------- SGA Memory Summary DB/Inst: FREE/FREE Snaps: 106-107 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 637,534,208 Fixed Size 5,007,584 Redo Buffers 4,530,176 Variable Size 956,301,312 -------------------- -------------------- sum 1,603,373,280 ------------------------------------------------------------- SGA breakdown difference DB/Inst: FREE/FREE Snaps: 106-107 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null 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 ------ ------------------------------ -------------- -------------- -------- large free memory 15.5 15.5 0.00 shared KGLH0 37.8 37.8 0.05 shared KGLHD 7.3 7.3 0.06 shared KGLS 34.4 34.4 0.00 shared KQR X PO 8.7 8.7 0.00 shared SQLA 77.3 77.3 0.02 shared free memory 316.7 315.8 -0.29 buffer_cache 528.0 528.0 0.00 fixed_sga 4.8 4.8 0.00 log_buffer 2.2 2.2 0.00 shared_io_pool 80.0 80.0 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: FREE/FREE Snaps: 106-107 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 57.69 57.67 -.04 Cursor to Parent ratio: 1.16 1.16 -.01 Total Cursors: 1,566 1,567 .06 Total Parents: 1,346 1,347 .07 ------------------------------------------------------------- init.ora Parameters DB/Inst: FREE/FREE Snaps: 106-107 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _instance_recovery_bloom_filt 1048576 compatible 23.6.0 control_files /opt/oracle/oradata/FREE/control0 1.ctl, /opt/oracle/oradata/FREE/c ontrol02.ctl control_management_pack_acces NONE db_block_size 8192 db_create_file_dest /opt/oracle/oradata db_name FREE diagnostic_dest /opt/oracle dispatchers (PROTOCOL=TCP) (SERVICE=FREEXDB) enable_pluggable_database TRUE fast_start_parallel_rollback LOW local_listener LISTENER_FREE nls_language AMERICAN nls_territory AMERICA open_cursors 300 pga_aggregate_target 536870912 processes 200 remote_login_passwordfile EXCLUSIVE sga_target 0 spatial_vector_acceleration TRUE undo_tablespace UNDOTBS1 ------------------------------------------------------------- End of Report ( /vagrant/sp_106_107.txt )