STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 196335337 DB11G 1 05-Oct-10 12:52 11.2.0.2.0 NO Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- ------------ oel5-11gR2-demo. Linux x86 64-bit 1 0 0 2.0 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------ Begin Snap: 3 05-Oct-10 16:00:03 40 5.1 End Snap: 4 05-Oct-10 17:00:05 40 5.1 Elapsed: 60.03 (mins) Av Act Sess: 0.4 DB time: 25.94 (mins) DB CPU: 13.92 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 188M Std Block Size: 8K Shared Pool: 288M Log Buffer: 2,160K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 0.4 0.0 0.01 0.06 DB CPU(s): 0.2 0.0 0.00 0.03 Redo size: 20,766.2 2,092.1 Logical reads: 548.5 55.3 Block changes: 138.3 13.9 Physical reads: 22.9 2.3 Physical writes: 16.5 1.7 User calls: 7.0 0.7 Parses: 9.2 0.9 Hard parses: 0.0 0.0 W/A MB processed: 1.8 0.2 Logons: 0.1 0.0 Executes: 62.6 6.3 Rollbacks: 0.0 0.0 Transactions: 9.9 Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 95.83 Optimal W/A Exec %: 100.00 Library Hit %: 99.99 Soft Parse %: 99.94 Execute to Parse %: 85.33 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 46.70 % Non-Parse CPU: 99.64 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 80.20 80.54 % SQL with executions>1: 58.08 59.47 % Memory for SQL w/exec>1: 61.45 65.67 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ PL/SQL lock timer 144,426 24,011 166 95.4 CPU time 771 3.1 log file sync 11,808 142 12 .6 db file sequential read 78,045 62 1 .2 os thread startup 138 52 380 .2 ------------------------------------------------------------- Host CPU (CPUs: 1 Cores: 0 Sockets: 0) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 1.98 0.00 22.28 4.71 71.94 0.08 Instance CPU ~~~~~~~~~~~~ % Time (seconds) -------- -------------- Host: Total time (s): 3,549.9 Host: Busy CPU time (s): 996.2 % of time Host is Busy: 28.1 Instance: Total CPU time (s): 965.3 % of Busy CPU used for Instance: 96.9 Instance: Total Database time (s): 1,984.6 %DB time waiting for CPU (Resource Mgr): 0.0 Virtual Memory Paging ~~~~~~~~~~~~~~~~~~~~~ KB paged out per sec: 209.0 KB paged in per sec: 14.5 Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 2,011.3 2,011.3 SGA use (MB): 800.4 800.4 PGA use (MB): 129.6 127.7 % Host Mem used for SGA+PGA: 46.2 46.1 ------------------------------------------------------------- Time Model System Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % DB time ----------------------------------- -------------------- --------- sql execute elapsed time 1,306.2 83.9 DB CPU 835.3 53.7 PL/SQL execution elapsed time 455.7 29.3 parse time elapsed 5.2 .3 repeated bind elapsed time 0.0 .0 sequence load elapsed time 0.0 .0 DB time 1,556.2 background elapsed time 428.3 background cpu time 130.0 ------------------------------------------------------------- Foreground Wait Events DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 144,426 100 24,011 166 4.0 95.4 log file sync 11,808 0 142 12 0.3 .6 db file sequential read 76,864 0 60 1 2.1 .2 latch free 698 0 10 14 0.0 .0 latch: cache buffers lru cha 430 0 7 16 0.0 .0 library cache: mutex X 152 0 6 40 0.0 .0 latch: cache buffers chains 332 0 5 17 0.0 .0 latch: row cache objects 206 0 3 15 0.0 .0 latch: object queue header o 97 0 1 12 0.0 .0 buffer busy waits 70 0 1 15 0.0 .0 latch: In memory undo latch 63 0 1 16 0.0 .0 latch: redo allocation 62 0 1 13 0.0 .0 cursor: pin S 31 0 1 25 0.0 .0 db file parallel read 524 0 0 1 0.0 .0 read by other session 21 0 0 20 0.0 .0 latch: enqueue hash chains 26 0 0 15 0.0 .0 Disk file operations I/O 112 0 0 2 0.0 .0 latch: redo copy 7 0 0 27 0.0 .0 enq: TX - row lock contentio 10 0 0 17 0.0 .0 db file scattered read 107 0 0 2 0.0 .0 latch: session allocation 8 0 0 16 0.0 .0 latch: messages 6 0 0 11 0.0 .0 latch: shared pool 2 0 0 16 0.0 .0 cursor: mutex S 1 0 0 10 0.0 .0 control file sequential read 468 0 0 0 0.0 .0 asynch descriptor resize 710 100 0 0 0.0 .0 jobq slave wait 7,631 100 3,916 513 0.2 SQL*Net message from client 24,737 0 3,908 158 0.7 SQL*Net message to client 24,748 0 3 0 0.7 ------------------------------------------------------------- Background Wait Events DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 ---------------------------- ------------ ---- ---------- ------ -------- ------ os thread startup 138 0 52 380 0.0 .2 log file parallel write 30,685 0 45 1 0.9 .2 db file async I/O submit 17,082 0 38 2 0.5 .1 control file parallel write 1,595 0 3 2 0.0 .0 db file sequential read 1,181 0 2 2 0.0 .0 LGWR wait for redo copy 137 0 2 12 0.0 .0 control file sequential read 3,080 0 1 0 0.1 .0 latch: redo allocation 46 0 0 8 0.0 .0 latch: cache buffers lru cha 26 0 0 13 0.0 .0 db file scattered read 124 0 0 1 0.0 .0 latch free 12 0 0 11 0.0 .0 Disk file operations I/O 76 0 0 1 0.0 .0 latch: object queue header o 8 0 0 10 0.0 .0 latch: redo writing 2 0 0 42 0.0 .0 latch: cache buffers chains 2 0 0 35 0.0 .0 direct path sync 2 0 0 34 0.0 .0 reliable message 6 0 0 11 0.0 .0 rdbms ipc reply 8 0 0 6 0.0 .0 enq: PR - contention 1 0 0 46 0.0 .0 latch: messages 6 0 0 7 0.0 .0 asynch descriptor resize 201 100 0 0 0.0 .0 latch: shared pool 1 0 0 23 0.0 .0 latch: checkpoint queue latc 1 0 0 21 0.0 .0 ADR block file read 15 0 0 1 0.0 .0 buffer busy waits 5 0 0 3 0.0 .0 latch: enqueue hash chains 1 0 0 5 0.0 .0 latch: call allocation 1 0 0 5 0.0 .0 direct path write 4 0 0 1 0.0 .0 latch: In memory undo latch 4 0 0 1 0.0 .0 db file single write 12 0 0 0 0.0 .0 log file single write 4 0 0 0 0.0 .0 ADR block file write 5 0 0 0 0.0 .0 rdbms ipc message 47,627 36 42,817 899 1.3 Streams AQ: waiting for time 5 60 7,540 ###### 0.0 DIAG idle wait 7,039 100 7,196 1022 0.2 Space Manager: slave idle wa 1,425 93 6,826 4790 0.0 smon timer 33 18 3,657 ###### 0.0 Streams AQ: qmn coordinator 258 50 3,615 14010 0.0 Streams AQ: qmn slave idle w 132 0 3,614 27382 0.0 shared server idle wait 120 100 3,602 30019 0.0 dispatcher timer 60 100 3,601 60011 0.0 pmon timer 1,197 100 3,598 3006 0.0 class slave wait 20 0 0 0 0.0 ------------------------------------------------------------- Wait Events (fg and bg) DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 144,426 100 24,011 166 4.0 95.4 log file sync 11,808 0 142 12 0.3 .6 db file sequential read 78,045 0 62 1 2.2 .2 os thread startup 138 0 52 380 0.0 .2 log file parallel write 30,685 0 45 1 0.9 .2 db file async I/O submit 17,082 0 38 2 0.5 .1 latch free 710 0 10 14 0.0 .0 latch: cache buffers lru cha 456 0 7 16 0.0 .0 library cache: mutex X 152 0 6 40 0.0 .0 latch: cache buffers chains 334 0 6 17 0.0 .0 latch: row cache objects 206 0 3 15 0.0 .0 control file parallel write 1,595 0 3 2 0.0 .0 LGWR wait for redo copy 137 0 2 12 0.0 .0 latch: object queue header o 105 0 1 12 0.0 .0 latch: redo allocation 108 0 1 11 0.0 .0 buffer busy waits 75 0 1 14 0.0 .0 latch: In memory undo latch 67 0 1 15 0.0 .0 cursor: pin S 31 0 1 25 0.0 .0 control file sequential read 3,548 0 1 0 0.1 .0 db file parallel read 524 0 0 1 0.0 .0 read by other session 21 0 0 20 0.0 .0 latch: enqueue hash chains 27 0 0 14 0.0 .0 Disk file operations I/O 188 0 0 2 0.0 .0 db file scattered read 231 0 0 1 0.0 .0 latch: redo copy 7 0 0 27 0.0 .0 enq: TX - row lock contentio 10 0 0 17 0.0 .0 latch: session allocation 8 0 0 16 0.0 .0 latch: messages 12 0 0 9 0.0 .0 latch: redo writing 2 0 0 42 0.0 .0 direct path sync 2 0 0 34 0.0 .0 reliable message 6 0 0 11 0.0 .0 latch: shared pool 3 0 0 18 0.0 .0 rdbms ipc reply 8 0 0 6 0.0 .0 enq: PR - contention 1 0 0 46 0.0 .0 asynch descriptor resize 911 100 0 0 0.0 .0 latch: checkpoint queue latc 1 0 0 21 0.0 .0 ADR block file read 15 0 0 1 0.0 .0 cursor: mutex S 1 0 0 10 0.0 .0 latch: call allocation 1 0 0 5 0.0 .0 direct path write 4 0 0 1 0.0 .0 db file single write 12 0 0 0 0.0 .0 log file single write 4 0 0 0 0.0 .0 ADR block file write 5 0 0 0 0.0 .0 rdbms ipc message 47,627 36 42,817 899 1.3 Streams AQ: waiting for time 5 60 7,540 ###### 0.0 DIAG idle wait 7,039 100 7,196 1022 0.2 Space Manager: slave idle wa 1,425 93 6,826 4790 0.0 jobq slave wait 7,631 100 3,916 513 0.2 SQL*Net message from client 24,737 0 3,908 158 0.7 Wait Events (fg and bg) DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 ---------------------------- ------------ ---- ---------- ------ -------- ------ smon timer 33 18 3,657 ###### 0.0 Streams AQ: qmn coordinator 258 50 3,615 14010 0.0 Streams AQ: qmn slave idle w 132 0 3,614 27382 0.0 shared server idle wait 120 100 3,602 30019 0.0 dispatcher timer 60 100 3,601 60011 0.0 pmon timer 1,197 100 3,598 3006 0.0 SQL*Net message to client 24,748 0 3 0 0.7 class slave wait 20 0 0 0 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 15 86.7 6.7 6.7 ADR block file write 5 100.0 ADR file lock 6 100.0 Disk file operations I/O 188 84.6 12.2 .5 .5 .5 .5 1.1 LGWR wait for redo copy 137 8.8 5.1 18.2 16.1 30.7 17.5 3.6 PL/SQL lock timer 144K 100.0 asynch descriptor resize 911 99.9 .1 buffer busy waits 75 6.7 6.7 10.7 17.3 30.7 18.7 9.3 control file parallel writ 1595 34.7 51.2 11.1 .8 1.7 .5 .1 control file sequential re 3548 98.2 1.5 .0 .1 .1 .1 cursor: mutex S 1 100.0 cursor: pin S 31 12.9 3.2 12.9 12.9 25.8 16.1 16.1 db file async I/O submit 17K 90.1 2.5 .6 1.2 1.8 2.1 1.8 db file parallel read 524 93.3 3.1 .2 1.7 .6 1.1 db file scattered read 231 88.3 7.8 1.3 .4 1.3 .9 db file sequential read 78K 92.1 4.5 .7 .6 1.2 .6 .2 db file single write 12 100.0 direct path sync 2 50.0 50.0 direct path write 4 75.0 25.0 enq: PR - contention 1 100.0 enq: TX - row lock content 10 30.0 10.0 60.0 latch free 710 4.1 1.1 6.5 24.6 31.4 25.8 6.5 latch: In memory undo latc 67 11.9 3.0 10.4 7.5 23.9 37.3 6.0 latch: cache buffers chain 334 2.1 5.4 9.0 15.6 28.7 28.4 10.8 latch: cache buffers lru c 456 3.5 6.1 11.4 15.6 27.9 24.8 10.7 latch: call allocation 1 100.0 latch: checkpoint queue la 1 100.0 latch: enqueue hash chains 27 7.4 25.9 51.9 7.4 7.4 latch: messages 12 8.3 16.7 25.0 33.3 16.7 latch: object queue header 105 10.5 3.8 19.0 22.9 17.1 19.0 7.6 latch: redo allocation 108 4.6 12.0 14.8 15.7 32.4 16.7 3.7 latch: redo copy 7 28.6 14.3 28.6 28.6 latch: redo writing 2 50.0 50.0 latch: row cache objects 206 1.0 1.0 10.2 27.2 29.6 22.3 8.7 latch: session allocation 8 12.5 12.5 25.0 37.5 12.5 latch: shared pool 3 33.3 66.7 library cache: mutex X 152 5.9 2.0 12.5 11.8 13.8 22.4 31.6 log file parallel write 30K 48.0 37.9 11.0 1.0 1.6 .4 .0 log file sequential read 4 100.0 log file single write 4 100.0 log file sync 11K 12.1 5.3 10.2 14.3 32.2 21.1 4.8 os thread startup 138 98.6 1.4 rdbms ipc reply 8 50.0 12.5 12.5 25.0 read by other session 21 4.8 9.5 4.8 9.5 42.9 9.5 19.0 reliable message 6 16.7 16.7 33.3 33.3 DIAG idle wait 7037 90.6 9.4 SQL*Net message from clien 24K 100.0 SQL*Net message to client 24K 99.3 .1 .1 .2 .3 .0 .0 Space Manager: slave idle 1425 1.2 .1 .2 2.0 96.4 Streams AQ: qmn coordinato 258 14.3 18.2 4.3 2.7 7.8 2.7 50.0 Streams AQ: qmn slave idle 132 2.3 97.7 Wait Event Histogram DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- Streams AQ: waiting for ti 5 40.0 20.0 40.0 class slave wait 20 100.0 dispatcher timer 60 100.0 jobq slave wait 7631 100.0 pmon timer 1197 100.0 rdbms ipc message 47K 1.3 .3 .7 3.1 7.5 12.9 53.8 20.2 shared server idle wait 120 100.0 smon timer 33 6.1 30.3 63.6 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: DB11G/DB11G Snaps: 3-4 -> Total DB CPU (s): 835 -> Captured SQL accounts for 125.3% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 431.07 7,229 0.06 51.6 769.82 1,346,309 3565022785 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; 167.32 8,689 0.02 20.0 317.47 305,874 1852190137 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; 99.40 21,680 0.00 11.9 172.85 610,438 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 52.39 4,343 0.01 6.3 91.75 145,639 3934876099 Module: JDBC Thin Client BEGIN :1 := orderentry.processorders(:2 ,:3 ); END; 40.39 19,495 0.00 4.8 76.46 299,605 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 31.21 20,344 0.00 3.7 50.80 81,376 3107051069 Module: Browse and Update Orders SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 27.86 2,969 0.01 3.3 44.67 82,763 3646320859 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END ; 26.62 25,980 0.00 3.2 60.25 236,021 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 23.97 1,469 0.02 2.9 45.38 24,384 2835506982 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END; 22.05 7,226 0.00 2.6 47.56 110,594 2082097592 Module: New Order INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0 SQL ordered by CPU DB/Inst: DB11G/DB11G Snaps: 3-4 -> Total DB CPU (s): 835 -> Captured SQL accounts for 125.3% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 16.33 21,460 0.00 2.0 36.07 122,865 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 14.11 4,342 0.00 1.7 26.86 83,202 3149415580 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT 14.03 7,229 0.00 1.7 31.42 58,677 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 12.14 59 0.21 1.5 22.44 28,852 2689373535 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 11.25 20,346 0.00 1.3 24.98 61,698 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 10.52 2,969 0.00 1.3 17.76 55,967 3172424902 Module: New Customer INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO 9.83 4,342 0.00 1.2 21.68 58,063 4220433507 Module: Process Orders UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1 ------------------------------------------------------------- SQL ordered by Elapsed time for DB: DB11G Instance: DB11G Snaps: 3 -4 -> Total DB Time (s): 1,556 -> Captured SQL accounts for 124.7% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 769.82 7,229 0.11 49.5 431.07 49,112 3565022785 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; 317.47 8,689 0.04 20.4 167.32 14,233 1852190137 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; 172.85 21,680 0.01 11.1 99.40 11,928 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 91.75 4,343 0.02 5.9 52.39 3,940 3934876099 Module: JDBC Thin Client BEGIN :1 := orderentry.processorders(:2 ,:3 ); END; 76.46 19,495 0.00 4.9 40.39 15,399 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 60.25 25,980 0.00 3.9 26.62 58 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 50.80 20,344 0.00 3.3 31.21 28,303 3107051069 Module: Browse and Update Orders SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 47.56 7,226 0.01 3.1 22.05 7,876 2082097592 Module: New Order INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0 45.38 1,469 0.03 2.9 23.97 7,133 2835506982 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END; 44.67 2,969 0.02 2.9 27.86 2,599 3646320859 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END ; SQL ordered by Elapsed time for DB: DB11G Instance: DB11G Snaps: 3 -4 -> Total DB Time (s): 1,556 -> Captured SQL accounts for 124.7% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 36.07 21,460 0.00 2.3 16.33 2,007 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 31.42 7,229 0.00 2.0 14.03 50 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 26.86 4,342 0.01 1.7 14.11 3,888 3149415580 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT 24.98 20,346 0.00 1.6 11.25 58 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 22.44 59 0.38 1.4 12.14 1,661 2689373535 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 21.68 4,342 0.00 1.4 9.83 49 4220433507 Module: Process Orders UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1 17.76 2,969 0.01 1.1 10.52 2,587 3172424902 Module: New Customer INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO 17.45 4 4.36 1.1 5.41 676 1745153189 begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end; ------------------------------------------------------------- SQL ordered by Gets DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,975,798 -> Captured SQL accounts for 97.5% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 1,346,309 7,229 186.2 68.1 431.07 769.82 3565022785 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; 610,438 21,680 28.2 30.9 99.40 172.85 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 305,874 8,689 35.2 15.5 167.32 317.47 1852190137 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; 299,605 19,495 15.4 15.2 40.39 76.46 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 236,021 25,980 9.1 11.9 26.62 60.25 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 145,639 4,343 33.5 7.4 52.39 91.75 3934876099 Module: JDBC Thin Client BEGIN :1 := orderentry.processorders(:2 ,:3 ); END; 122,865 21,460 5.7 6.2 16.33 36.07 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 110,594 7,226 15.3 5.6 22.05 47.56 2082097592 Module: New Order INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0 83,202 4,342 19.2 4.2 14.11 26.86 3149415580 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT 82,763 2,969 27.9 4.2 27.86 44.67 3646320859 Module: JDBC Thin Client SQL ordered by Gets DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,975,798 -> Captured SQL accounts for 97.5% 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 --------------- ------------ -------------- ------ -------- --------- ---------- BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END ; 81,376 20,344 4.0 4.1 31.21 50.80 3107051069 Module: Browse and Update Orders SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 78,358 6,948 11.3 4.0 5.14 9.70 2385983258 Module: New Order UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 61,698 20,346 3.0 3.1 11.25 24.98 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 58,677 7,229 8.1 3.0 14.03 31.42 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 58,063 4,342 13.4 2.9 9.83 21.68 4220433507 Module: Process Orders UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1 55,967 2,969 18.9 2.8 10.52 17.76 3172424902 Module: New Customer INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO 28,852 59 489.0 1.5 12.14 22.44 2689373535 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 24,384 1,469 16.6 1.2 23.97 45.38 2835506982 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END; ------------------------------------------------------------- SQL ordered by Reads DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Disk Reads Threshold: 1000 Total Disk Reads: 82,396 -> Captured SQL accounts for 98.3% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 49,112 7,229 6.8 59.6 431.07 769.82 3565022785 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; 28,303 20,344 1.4 34.3 31.21 50.80 3107051069 Module: Browse and Update Orders SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 15,399 19,495 0.8 18.7 40.39 76.46 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 14,233 8,689 1.6 17.3 167.32 317.47 1852190137 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; 11,928 21,680 0.6 14.5 99.40 172.85 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 7,876 7,226 1.1 9.6 22.05 47.56 2082097592 Module: New Order INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0 7,133 1,469 4.9 8.7 23.97 45.38 2835506982 Module: JDBC Thin Client BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END; 3,940 4,343 0.9 4.8 52.39 91.75 3934876099 Module: JDBC Thin Client BEGIN :1 := orderentry.processorders(:2 ,:3 ); END; 3,888 4,342 0.9 4.7 14.11 26.86 3149415580 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT 3,034 1,469 2.1 3.7 3.70 9.36 1457647474 Module: Browse and Update Orders SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TO TAL, SALES_REP_ID, PROMOTION_ID FROM ORDERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 SQL ordered by Reads DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Disk Reads Threshold: 1000 Total Disk Reads: 82,396 -> Captured SQL accounts for 98.3% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 2,599 2,969 0.9 3.2 27.86 44.67 3646320859 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END ; 2,587 2,969 0.9 3.1 10.52 17.76 3172424902 Module: New Customer INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO 2,007 21,460 0.1 2.4 16.33 36.07 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 1,661 59 28.2 2.0 12.14 22.44 2689373535 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 1,542 2 771.0 1.9 0.33 0.85 390555132 select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INS T, RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + dec 1,009 57 17.7 1.2 1.24 2.25 1413267761 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_ guid,:metric_values) 870 433 2.0 1.1 1.27 3.24 3573222465 Module: Browse and Update Orders SELECT ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY FROM ORDER_ITEMS WHERE ORDER_ID = :B2 AND ROWNUM < :B1 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Executions Threshold: 100 Total Executions: 225,331 -> Captured SQL accounts for 99.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 ------------ --------------- ---------------- ----------- ---------- ---------- 25,980 363,720 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 21,680 98,099 4.5 0.00 0.01 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 21,460 19,495 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 20,346 20,346 1.0 0.00 0.00 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 20,346 20,346 1.0 0.00 0.00 2293415029 Module: New Order SELECT SYSDATE FROM DUAL 20,344 20,344 1.0 0.00 0.00 3107051069 Module: Browse and Update Orders SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG E, NLS_TERRITORY, CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1 19,495 19,495 1.0 0.00 0.00 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 8,689 8,689 1.0 0.02 0.04 1852190137 Module: JDBC Thin Client BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; 7,229 7,229 1.0 0.06 0.11 3565022785 Module: JDBC Thin Client BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; 7,229 7,229 1.0 0.00 0.00 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 SQL ordered by Executions DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Executions Threshold: 100 Total Executions: 225,331 -> Captured SQL accounts for 99.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 ------------ --------------- ---------------- ----------- ---------- ---------- 7,226 7,226 1.0 0.00 0.01 2082097592 Module: New Order INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (OR DERS_SEQ.NEXTVAL , SYSTIMESTAMP , :B1 ) RETURNING ORDER_ID INTO :O0 6,948 19,498 2.8 0.00 0.00 2385983258 Module: New Order UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 4,343 4,343 1.0 0.01 0.02 3934876099 Module: JDBC Thin Client BEGIN :1 := orderentry.processorders(:2 ,:3 ); END; 4,342 4,342 1.0 0.00 0.01 3149415580 Module: Process Orders WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDER S WHERE ORDER_STATUS <= 4 AND ROWNUM < 10 ) SELECT O.ORDER_ID, O I.LINE_ITEM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORD ER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOT 4,342 4,342 1.0 0.00 0.00 4220433507 Module: Process Orders UPDATE /*+ index(orders, order_pk) */ ORDERS SET ORDER_STATUS = FLOOR(DBMS_RANDOM.VALUE(:B3 + 1, :B2 )) WHERE ORDER_ID = :B1 3,381 4,811 1.4 0.00 0.00 2275360153 select /*+ connect_by_filtering */ privilege#,level from sysauth $ connect by grantee#=prior privilege# and privilege#>0 start wi th grantee#=:1 and privilege#>0 2,969 2,969 1.0 0.00 0.00 2690112681 Module: New Customer SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL 2,969 2,969 1.0 0.00 0.01 3172424902 Module: New Customer INSERT INTO CUSTOMERS(CUSTOMER_ID ,CUST_FIRST_NAME ,CUST_LAST_NA ME ,NLS_LANGUAGE ,NLS_TERRITORY ,CREDIT_LIMIT ,CUST_EMAIL ,ACCOU NT_MGR_ID ) VALUES (:B9 , :B4 , :B3 , :B8 , :B7 , FLOOR(DBMS_RAN DOM.VALUE(:B6 , :B5 )), :B4 ||'.'||:B3 ||'@'||'oracle.com', FLOO 2,969 2,969 1.0 0.01 0.02 3646320859 Module: JDBC Thin Client BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); END ; ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: DB11G/DB11G Snaps: 3-4 -> End Parse Calls Threshold: 1000 Total Parse Calls: 33,065 -> Captured SQL accounts for 19.9% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 3,381 3,381 10.23 2275360153 select /*+ connect_by_filtering */ privilege#,level from sysauth $ connect by grantee#=prior privilege# and privilege#>0 start wi th grantee#=:1 and privilege#>0 ------------------------------------------------------------- truncate table STATS$TEMP_SQLSTATS * ERROR at line 1: ORA-00942: table or view does not exist Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ Batched IO (bound) vector count 0 0.0 0.0 Batched IO (full) vector count 0 0.0 0.0 Batched IO block miss count 2,049 0.6 0.1 Batched IO buffer defrag count 0 0.0 0.0 Batched IO double miss count 1,092 0.3 0.0 Batched IO same unit count 0 0.0 0.0 Batched IO single block count 568 0.2 0.0 Batched IO vector block count 1,481 0.4 0.0 Batched IO vector read count 524 0.2 0.0 Block Cleanout Optim referenced 85 0.0 0.0 CCursor + sql area evicted 0 0.0 0.0 CPU used by this session 77,069 21.4 2.2 CPU used when call started 74,008 20.6 2.1 CR blocks created 4,824 1.3 0.1 Cached Commit SCN referenced 180 0.1 0.0 Commit SCN cached 399 0.1 0.0 DB time 2,943,666 817.2 82.3 DBWR checkpoint buffers written 24,140 6.7 0.7 DBWR checkpoints 2 0.0 0.0 DBWR parallel query checkpoint bu 0 0.0 0.0 DBWR thread checkpoint buffers wr 0 0.0 0.0 DBWR transaction table writes 147 0.0 0.0 DBWR undo block writes 3,762 1.0 0.1 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 0 0.0 0.0 HSC Heap Segment Block Changes 88,543 24.6 2.5 Heap Segment Array Inserts 222 0.1 0.0 Heap Segment Array Updates 113 0.0 0.0 IMU CR rollbacks 404 0.1 0.0 IMU Flushes 8,664 2.4 0.2 IMU Redo allocation size 21,571,360 5,988.7 603.3 IMU commits 27,085 7.5 0.8 IMU contention 6,953 1.9 0.2 IMU ktichg flush 1 0.0 0.0 IMU pool not allocated 110 0.0 0.0 IMU undo allocation size 49,310,696 13,689.8 1,379.2 IMU- failed to get a private stra 110 0.0 0.0 Number of read IOs issued 0 0.0 0.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 Parallel operations not downgrade 0 0.0 0.0 RowCR - row contention 447 0.1 0.0 RowCR attempts 12,262 3.4 0.3 RowCR hits 11,799 3.3 0.3 SMON posted for undo segment shri 6 0.0 0.0 SQL*Net roundtrips to/from client 24,742 6.9 0.7 TBS Extension: bytes extended 0 0.0 0.0 TBS Extension: files extended 0 0.0 0.0 TBS Extension: tasks created 6 0.0 0.0 TBS Extension: tasks executed 6 0.0 0.0 active txn count during cleanout 6,460 1.8 0.2 application wait time 16 0.0 0.0 background checkpoints completed 2 0.0 0.0 background checkpoints started 2 0.0 0.0 background timeouts 17,170 4.8 0.5 branch node splits 2 0.0 0.0 Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ buffer is not pinned count 874,131 242.7 24.5 buffer is pinned count 859,266 238.6 24.0 bytes received via SQL*Net from c 1,780,184 494.2 49.8 bytes sent via SQL*Net to client 3,494,684 970.2 97.7 calls to get snapshot scn: kcmgss 207,521 57.6 5.8 calls to kcmgas 41,690 11.6 1.2 calls to kcmgcs 3,994 1.1 0.1 cell physical IO interconnect byt 1,358,193,664 377,066.5 37,987.2 change write time 4,033 1.1 0.1 cleanout - number of ktugct calls 3,508 1.0 0.1 cleanouts and rollbacks - consist 2,053 0.6 0.1 cleanouts only - consistent read 23 0.0 0.0 cluster key scan block gets 2,340 0.7 0.1 cluster key scans 2,082 0.6 0.1 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 61 0.0 0.0 commit batch/immediate requested 61 0.0 0.0 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 1 0.0 0.0 commit cleanout failures: callbac 29 0.0 0.0 commit cleanout failures: cannot 1,487 0.4 0.0 commit cleanouts 151,644 42.1 4.2 commit cleanouts successfully com 150,127 41.7 4.2 commit immediate performed 61 0.0 0.0 commit immediate requested 61 0.0 0.0 commit nowait performed 0 0.0 0.0 commit nowait requested 0 0.0 0.0 commit txn count during cleanout 2,879 0.8 0.1 commit wait/nowait performed 0 0.0 0.0 commit wait/nowait requested 0 0.0 0.0 concurrency wait time 7,011 2.0 0.2 consistent changes 6,067 1.7 0.2 consistent gets 1,296,434 359.9 36.3 consistent gets - examination 908,384 252.2 25.4 consistent gets direct 1 0.0 0.0 consistent gets from cache 1,296,433 359.9 36.3 consistent gets from cache (fastp 363,818 101.0 10.2 cursor authentications 40 0.0 0.0 data blocks consistent reads - un 5,727 1.6 0.2 db block changes 498,029 138.3 13.9 db block gets 679,364 188.6 19.0 db block gets direct 17 0.0 0.0 db block gets from cache 679,347 188.6 19.0 db block gets from cache (fastpat 162,227 45.0 4.5 deferred (CURRENT) block cleanout 60,013 16.7 1.7 dirty buffers inspected 28,637 8.0 0.8 enqueue conversions 755 0.2 0.0 enqueue releases 135,586 37.6 3.8 enqueue requests 135,571 37.6 3.8 enqueue timeouts 1 0.0 0.0 enqueue waits 11 0.0 0.0 execute count 225,331 62.6 6.3 failed probes on index block recl 0 0.0 0.0 file io service time 398 0.1 0.0 file io wait time 99,089,682 27,509.6 2,771.4 Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ free buffer inspected 102,931 28.6 2.9 free buffer requested 91,711 25.5 2.6 heap block compress 172 0.1 0.0 hot buffers moved to head of LRU 80,783 22.4 2.3 immediate (CR) block cleanout app 2,076 0.6 0.1 immediate (CURRENT) block cleanou 6,674 1.9 0.2 index crx upgrade (positioned) 8 0.0 0.0 index fast full scans (direct rea 0 0.0 0.0 index fast full scans (full) 22 0.0 0.0 index fast full scans (rowid rang 0 0.0 0.0 index fetch by key 273,287 75.9 7.6 index scans kdiixs1 68,671 19.1 1.9 leaf node 90-10 splits 48 0.0 0.0 leaf node splits 477 0.1 0.0 lob reads 0 0.0 0.0 lob writes 77 0.0 0.0 lob writes unaligned 77 0.0 0.0 logons cumulative 206 0.1 0.0 max cf enq hold time 0 0.0 0.0 messages received 47,814 13.3 1.3 messages sent 47,814 13.3 1.3 min active SCN optimization appli 77 0.0 0.0 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 369,322 102.5 10.3 non-idle wait count 184,298 51.2 5.2 non-idle wait time 38,822 10.8 1.1 opened cursors cumulative 234,795 65.2 6.6 parse count (describe) 6 0.0 0.0 parse count (failures) 0 0.0 0.0 parse count (hard) 19 0.0 0.0 parse count (total) 33,065 9.2 0.9 parse time cpu 276 0.1 0.0 parse time elapsed 591 0.2 0.0 physical read IO requests 79,731 22.1 2.2 physical read bytes 674,988,032 187,392.6 18,878.7 physical read total IO requests 83,301 23.1 2.3 physical read total bytes 733,267,968 203,572.5 20,508.7 physical read total multi block r 27 0.0 0.0 physical reads 82,396 22.9 2.3 physical reads cache 82,395 22.9 2.3 physical reads cache prefetch 3,622 1.0 0.1 physical reads direct 1 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 45,080 12.5 1.3 physical write bytes 485,892,096 134,895.1 13,589.9 physical write total IO requests 78,995 21.9 2.2 physical write total bytes 624,925,696 173,494.1 17,478.5 physical write total multi block 173 0.1 0.0 physical writes 59,313 16.5 1.7 physical writes direct 17 0.0 0.0 physical writes direct (lob) 1 0.0 0.0 physical writes direct temporary 0 0.0 0.0 physical writes from cache 59,296 16.5 1.7 physical writes non checkpoint 41,878 11.6 1.2 pinned buffers inspected 0 0.0 0.0 Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 3,603 1.0 0.1 queries parallelized 0 0.0 0.0 recursive calls 388,437 107.8 10.9 recursive cpu usage 41,811 11.6 1.2 redo blocks checksummed by FG (ex 37,684 10.5 1.1 redo blocks written 169,274 47.0 4.7 redo buffer allocation retries 0 0.0 0.0 redo entries 193,997 53.9 5.4 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 0 0.0 0.0 redo size 74,799,920 20,766.2 2,092.1 redo size for direct writes 252 0.1 0.0 redo synch long waits 1,772 0.5 0.1 redo synch time 14,296 4.0 0.4 redo synch time (usec) 142,961,550 39,689.5 3,998.5 redo synch writes 24,817 6.9 0.7 redo wastage 9,211,748 2,557.4 257.6 redo write time 4,828 1.3 0.1 redo writes 30,679 8.5 0.9 rollback changes - undo records a 59 0.0 0.0 rollbacks only - consistent read 106 0.0 0.0 rows fetched via callback 176,972 49.1 5.0 session connect time 0 0.0 0.0 session cursor cache hits 232,015 64.4 6.5 session logical reads 1,975,798 548.5 55.3 session pga memory 149,329,632 41,457.4 4,176.6 session pga memory max 173,774,560 48,243.9 4,860.3 session uga memory 292,068,197,824 81,085,007.7 8,168,825.8 session uga memory max 170,845,712 47,430.8 4,778.4 shared hash latch upgrades - no w 58,119 16.1 1.6 shared hash latch upgrades - wait 18 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 10,092 2.8 0.3 sorts (rows) 34,104 9.5 1.0 sql area evicted 0 0.0 0.0 sql area purged 0 0.0 0.0 summed dirty queue length 36,510 10.1 1.0 switch current to new buffer 154 0.0 0.0 table fetch by rowid 694,399 192.8 19.4 table fetch continued row 0 0.0 0.0 table scan blocks gotten 4,152 1.2 0.1 table scan rows gotten 297,925 82.7 8.3 table scans (direct read) 0 0.0 0.0 table scans (long tables) 0 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 1,498 0.4 0.0 total cf enq hold time 680 0.2 0.0 total number of cf enq holders 115 0.0 0.0 total number of times SMON posted 27 0.0 0.0 transaction rollbacks 59 0.0 0.0 undo change vector size 24,748,468 6,870.8 692.2 user I/O wait time 6,387 1.8 0.2 user calls 25,351 7.0 0.7 Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ user commits 35,754 9.9 1.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 13,032 3.6 0.4 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 18 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 40 40 opened cursors current 203 202 session cursor cache count 12,435 15,850 ------------------------------------------------------------- Instance Activity Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 2 2.00 ------------------------------------------------------------- OS Statistics DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 99,621 IDLE_TIME 255,371 IOWAIT_TIME 278 NICE_TIME 98 SYS_TIME 16,726 USER_TIME 79,075 VM_IN_BYTES 53,616,640 VM_OUT_BYTES 770,998,272 PHYSICAL_MEMORY_BYTES 2,109,009,920 NUM_CPUS 1 GLOBAL_RECEIVE_SIZE_MAX 4,194,304 GLOBAL_SEND_SIZE_MAX 1,048,586 TCP_RECEIVE_SIZE_DEFAULT 87,380 TCP_RECEIVE_SIZE_MAX 4,194,304 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: DB11G/DB11G Snaps: 3-4 Snap Snapshot Id Day Time Load %Busy %User %System %WIO %WCPU ------ --------------- ------ ------ ------ ------- ------ ------ 3 Tue 05 16:00:03 2.0 4 Tue 05 17:00:05 .0 28.1 22.3 4.7 0.1 ------------------------------------------------------------- IO Stat by Function - summary DB/Inst: DB11G/DB11G Snaps: 3-4 ->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 642M 22.1 .2M 79K 0.0 Others 55M 1.0 .0M 49M .9 .0M 6678 0.0 LGWR .0 82M 8.5 .0M 61K 0.0 Streams AQ .0 17 0.0 ------------------------------------------------------------- IO Stat by Function - detail DB/Inst: DB11G/DB11G Snaps: 3-4 ->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 80K 27 631M 11M Others 3516 55M 3162 49M LGWR 44 31K 9 81M 1M Streams AQ 17 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: DB11G/DB11G Snaps: 3-4 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SWINGBENCH 77,154 21 0.8 1.0 43,759 12 72 14.6 SYSAUX 1,813 1 1.2 1.2 740 0 0 0.0 SYSTEM 698 0 0.9 4.2 58 0 0 0.0 UNDOTBS1 4 0 2.5 1.0 523 0 24 17.9 TEMP 4 0 0.0 1.0 0 0 0 0.0 EXAMPLE 1 0 0.0 1.0 0 0 0 0.0 USERS 1 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: DB11G/DB11G Snaps: 3-4 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ EXAMPLE /u01/app/oracle/oradata/DB11G/example01.dbf 1 0 0.0 1 1.0 0 0 0 SWINGBENCH /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf 77,154 21 0.8 ### 1.0 43,759 12 72 14.6 SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf 1,813 1 1.2 ### 1.2 740 0 0 SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf 698 0 0.9 64 4.2 58 0 0 TEMP /u01/app/oracle/oradata/DB11G/temp01.dbf 4 0 0.0 1.0 0 0 0 UNDOTBS1 /u01/app/oracle/oradata/DB11G/undotbs01.dbf 4 0 2.5 2 1.0 523 0 24 17.9 USERS /u01/app/oracle/oradata/DB11G/users01.dbf 1 0 0.0 1 1.0 0 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: DB11G/DB11G Snaps: 3-4 ->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 ------------ ------------ ------------ ------------ ------------ ------------ USERS /u01/app/oracle/oradata/DB11G/users01.dbf 1 0 0 0 0 0 SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf 1,623 14 26 37 18 14 UNDOTBS1 /u01/app/oracle/oradata/DB11G/undotbs01.dbf 4 0 0 0 0 0 EXAMPLE /u01/app/oracle/oradata/DB11G/example01.dbf 1 0 0 0 0 0 SWINGBENCH /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf 74,691 517 440 911 426 169 SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf 528 1 2 13 3 1 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 23 4204 27564 156761 165888 156761 E 0 20 908 3150 18102 165888 18102 ------------------------------------------------------------- Memory Target Advice DB/Inst: DB11G/DB11G Snaps: 3-4 -> Advice Reset: if this is null, the data shown has been diffed between the Begin and End snapshots. If this is 'Y', the advisor has been reset during this interval due to memory resize operations, and the data shown is since the reset operation. Memory Size Est. Advice Memory Size (M) Factor DB time (s) Reset --------------- ----------- ------------ ------ 402 .5 1,545 603 .8 1,555 804 1.0 1,559 1,005 1.3 1,559 1,206 1.5 1,559 1,407 1.8 1,559 1,608 2.0 1,559 ------------------------------------------------------------- Memory Dynamic Components DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 188 0 INITIA/ PGA Target 280 0 STATIC SGA Target 524 0 STATIC java pool 28 0 STATIC large pool 4 0 STATIC shared pool 288 0 STATIC streams pool 8 0 STATIC ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: DB11G/DB11G End Snap: 4 -> 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 16 .1 2 3.1 976 2,305 30.7 D 32 .2 4 2.6 826 1,945 25.9 D 48 .3 6 2.2 690 1,618 21.5 D 64 .3 8 1.9 600 1,402 18.7 D 80 .4 10 1.7 525 1,221 16.2 D 96 .5 12 1.5 468 1,084 14.4 D 112 .6 14 1.4 424 980 13.0 D 128 .7 16 1.3 390 898 11.9 D 144 .8 18 1.2 362 830 11.0 D 160 .9 20 1.1 340 778 10.3 D 176 .9 22 1.0 323 736 9.8 D 188 1.0 23 1.0 312 710 9.4 D 192 1.0 24 1.0 309 702 9.3 D 208 1.1 26 1.0 297 673 9.0 D 224 1.2 27 0.9 288 651 8.7 D 240 1.3 29 0.9 281 635 8.4 D 256 1.4 31 0.9 274 619 8.2 D 272 1.4 33 0.9 268 605 8.0 D 288 1.5 35 0.8 264 595 7.9 D 304 1.6 37 0.8 261 587 7.8 D 320 1.7 39 0.8 258 580 7.7 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 23K 96 1,975,209 82,332 59,296 0 0 96 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 72 1 15 undo header 20 0 16 undo block 3 0 30 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 6,608 0 PGA Aggr Target Histogram DB/Inst: DB11G/DB11G Snaps: 3-4 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 3,732 3,732 0 0 64K 128K 10 10 0 0 256K 512K 5 5 0 0 512K 1024K 9,279 9,279 0 0 1M 2M 5 5 0 0 4M 8M 4 4 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: DB11G/DB11G End Snap: 4 -> 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 ---------- ------ -------------- -------------- ---------- ------ ---------- 35 0.1 26,773 9,789 9.3 73.0 80 70 0.3 26,773 4,336 7.9 86.0 62 140 0.5 26,773 2,380 7.4 92.0 0 210 0.8 26,773 2,380 7.4 92.0 0 280 1.0 26,773 2,380 7.4 92.0 0 336 1.2 26,773 1,601 7.2 94.0 0 392 1.4 26,773 1,298 7.2 95.0 0 448 1.6 26,773 1,134 7.1 96.0 0 504 1.8 26,773 1,134 7.1 96.0 0 560 2.0 26,773 1,134 7.1 96.0 0 840 3.0 26,773 1,134 7.1 96.0 0 1,120 4.0 26,773 1,134 7.1 96.0 0 1,680 6.0 26,773 1,134 7.1 96.0 0 2,240 8.0 26,773 1,134 7.1 96.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 -------- 129.6 67.7 52.1 3.1 3.4 20 30 42 Other 72.5 1.7 1.9 11 17 42 Freeable 52.3 .0 1.9 2.6 13 27 PL/SQL 3.4 1.1 .1 .1 0 11 40 SQL 1.6 .2 .1 .0 0 6 24 E -------- 127.7 66.4 48.2 3.0 3.4 20 30 42 Other 74.8 1.8 1.9 11 17 42 Freeable 48.2 .0 1.8 2.6 13 27 PL/SQL 3.2 .9 .1 .1 0 11 40 SQL 1.5 .1 .1 .0 0 6 24 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 10 DBW0 -------- 19.7 6.8 12.6 19.7 26.0 Freeable 12.6 .0 12.6 Other 7.1 7.1 13.4 PL/SQL .0 .0 .0 .0 11 LGWR -------- 11.6 10.8 .1 11.6 11.6 Other 11.5 11.5 11.5 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 23 J000 -------- 8.2 1.5 6.4 8.2 8.2 Freeable 6.4 .0 6.4 Other 1.5 1.5 1.5 PL/SQL .2 .2 .2 .2 SQL .1 .1 .1 6.3 25 CJQ0 -------- 7.3 1.3 5.8 7.3 11.5 Freeable 5.8 .0 5.8 Other 1.5 1.5 1.5 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 5.6 27 Q001 -------- 4.6 3.8 .3 4.6 4.6 Other 4.4 4.4 4.4 Freeable .3 .0 .3 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .1 13 SMON -------- 4.4 1.3 2.1 4.4 5.6 Other 2.3 2.3 2.3 Freeable 2.1 .0 2.1 PL/SQL .0 .0 .0 .1 SQL .0 .0 .0 3.5 19 SMCO -------- 4.4 .8 3.4 4.4 5.3 Freeable 3.4 .0 3.4 Other .9 .9 1.9 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 33 ------------ 4.3 2.2 2.0 4.3 6.5 Other 2.1 2.1 2.1 Freeable 2.0 .0 2.0 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.1 21 W000 -------- 4.0 .8 3.0 4.0 5.6 Freeable 3.0 .0 3.0 Other 1.0 1.0 2.5 SQL .0 .0 .0 .1 PL/SQL .0 .0 .0 .0 15 MMON -------- 3.6 2.0 1.4 3.6 4.0 Other 2.1 2.1 2.1 Freeable 1.4 .0 1.4 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 1.1 39 ------------ 3.3 2.1 .9 3.3 5.6 Other 2.1 2.1 2.1 Freeable .9 .0 .9 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.2 Top Process Memory (by component) DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 32 ------------ 3.2 1.5 1.6 3.2 6.6 Freeable 1.7 .0 1.7 Other 1.4 1.4 1.4 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.3 37 ------------ 3.2 2.3 .8 3.2 6.4 Other 2.2 2.2 2.2 Freeable .8 .0 .8 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 22 TNS V1-V3 --- 3.2 2.0 1.0 3.2 30.1 Other 1.6 1.6 16.9 Freeable 1.0 .0 1.0 PL/SQL .4 .3 .4 10.6 SQL .2 .0 .2 1.6 29 ------------ 2.7 1.5 .9 2.7 6.1 Other 1.5 1.5 1.8 Freeable .9 .0 .9 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.2 31 ------------ 2.6 1.3 1.1 2.6 5.7 Other 1.3 1.3 1.3 Freeable 1.1 .0 1.1 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 41 ------------ 2.6 1.3 1.2 2.6 5.5 Other 1.2 1.2 1.2 Freeable 1.2 .0 1.2 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.2 30 ------------ 2.6 1.4 1.0 2.6 6.0 Other 1.3 1.3 1.6 Freeable 1.0 .0 1.0 PL/SQL .2 .0 .2 .2 SQL .1 .0 .1 3.2 38 ------------ 2.5 1.3 .9 2.5 6.7 Other 1.3 1.3 2.3 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 40 ------------ 2.5 1.6 .8 2.5 6.6 Other 1.5 1.5 2.3 Freeable .8 .0 .8 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.4 E 10 DBW0 -------- 19.7 6.8 12.6 19.7 26.0 Freeable 12.6 .0 12.6 Other 7.1 7.1 13.4 PL/SQL .0 .0 .0 .0 11 LGWR -------- 11.6 10.8 .1 11.6 11.6 Other 11.5 11.5 11.5 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 Top Process Memory (by component) DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 43 J001 -------- 8.1 1.5 6.3 8.1 8.1 Freeable 6.3 .0 6.3 Other 1.5 1.5 1.5 PL/SQL .2 .2 .2 .2 SQL .1 .1 .1 6.3 25 CJQ0 -------- 7.3 1.3 5.8 7.3 11.5 Freeable 5.8 .0 5.8 Other 1.5 1.5 1.5 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 5.6 27 Q001 -------- 4.6 3.8 .3 4.6 4.6 Other 4.4 4.4 4.4 Freeable .3 .0 .3 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .1 13 SMON -------- 4.4 1.3 2.1 4.4 5.6 Other 2.3 2.3 2.3 Freeable 2.1 .0 2.1 PL/SQL .0 .0 .0 .1 SQL .0 .0 .0 3.5 19 SMCO -------- 4.4 .8 3.4 4.4 5.3 Freeable 3.4 .0 3.4 Other .9 .9 1.9 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 15 MMON -------- 3.6 2.0 1.4 3.6 4.0 Other 2.1 2.1 2.1 Freeable 1.4 .0 1.4 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 1.1 21 M000 -------- 3.4 1.5 .7 3.4 3.4 Other 2.6 2.6 2.6 Freeable .7 .0 .7 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 1.5 33 ------------ 3.3 2.0 .9 3.3 6.5 Other 2.1 2.1 2.2 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.1 39 ------------ 3.3 2.0 .9 3.3 5.6 Other 2.1 2.1 2.1 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 37 ------------ 3.2 2.1 .8 3.2 6.4 Other 2.3 2.3 2.3 Freeable .8 .0 .8 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 22 TNS V1-V3 --- 3.2 2.0 1.0 3.2 30.1 Other 1.6 1.6 16.9 Freeable 1.0 .0 1.0 Top Process Memory (by component) DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 22 PL/SQL .4 .3 .4 10.6 SQL .2 .0 .2 1.6 40 ------------ 2.8 1.4 1.1 2.8 6.6 Other 1.5 1.5 1.9 Freeable 1.1 .0 1.1 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.4 29 ------------ 2.7 1.3 1.1 2.7 6.1 Other 1.4 1.4 1.7 Freeable 1.1 .0 1.1 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 30 ------------ 2.7 1.3 .9 2.7 6.0 Other 1.6 1.6 1.6 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.2 34 ------------ 2.6 1.3 1.1 2.6 6.0 Other 1.3 1.3 1.4 Freeable 1.1 .0 1.1 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.3 28 ------------ 2.5 1.2 1.1 2.5 5.6 Other 1.3 1.3 1.3 Freeable 1.1 .0 1.1 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.0 36 ------------ 2.5 1.1 1.0 2.5 5.6 Other 1.3 1.3 1.4 Freeable 1.0 .0 1.0 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.0 32 ------------ 2.5 1.3 .8 2.5 6.6 Other 1.4 1.4 2.3 Freeable .8 .0 .8 PL/SQL .1 .0 .1 .2 SQL .1 .0 .1 3.3 ------------------------------------------------------------- Enqueue activity DB/Inst: DB11G/DB11G Snaps: 3-4 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- TX-Transaction (row lock contention) 10 10 0 10 0 18.00 PR-Process Startup 138 138 0 1 0 40.00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 4.1 43,639 1,136 11 19.5/31 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 05-Oct 16:52 15 68 1,136 3 31 0/0 0/0/0/0/0/0 05-Oct 16:42 18 90 528 1 22 0/0 0/0/0/0/0/0 05-Oct 16:32 830 9,859 989 10 30 0/0 0/0/0/0/0/0 05-Oct 16:22 989 11,198 386 11 19 0/0 0/0/0/0/0/0 05-Oct 16:12 1,013 11,177 992 11 30 0/0 0/0/0/0/0/0 05-Oct 16:02 1,216 11,247 390 11 20 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ AQ deq hash table latch 22 0.0 0 0 AQ dequeue txn counter l 4 0.0 0 0 AQ disk delete txn count 4 0.0 0 0 ASM db client latch 2,406 0.0 0 0 ASM map operation hash t 2 0.0 0 0 ASM network state latch 57 0.0 0 0 AWR Alerted Metric Eleme 52,181 0.0 0 0 Change Notification Hash 1,194 0.0 0 0 Consistent RBA 30,678 0.0 0 0 DML lock allocation 137,831 0.0 1.0 0 0 Event Group Locks 140 0.0 0 0 FOB s.o list latch 373 0.0 0 0 File State Object Pool P 2 0.0 0 0 I/O Staticstics latch 2 0.0 0 0 IPC stats buffer allocat 2 0.0 0 0 In memory undo latch 213,565 0.0 1.0 1 36,633 0.0 JS Sh mem access 8 0.0 0 0 JS mem alloc latch 6 0.0 0 0 JS queue access latch 8 0.0 0 0 JS queue state obj latch 25,702 0.0 0 0 JS slv state obj latch 238 0.0 0 0 KFC FX Hash Latch 2 0.0 0 0 KFC Hash Latch 2 0.0 0 0 KFCL LE Freelist 2 0.0 0 0 KGNFS-NFS:SHM structure 2 0.0 0 0 KGNFS-NFS:SVR LIST 2 0.0 0 0 KJC message pool free li 2 0.0 0 0 KJCT flow control latch 2 0.0 0 0 KMG MMAN ready and start 1,194 0.0 0 0 KTF sga latch 21 0.0 0 1,159 0.0 KWQMN job cache list lat 11 0.0 0 0 KWQP Prop Status 3 0.0 0 0 KWQS pqsubs latch 5 0.0 0 0 KWQS pqueue ctx latch 81 0.0 0 0 Locator state objects po 2 0.0 0 0 Lsod array latch 2 0.0 0 0 MQL Tracking Latch 0 0 72 0.0 Memory Management Latch 2 0.0 0 1,194 0.0 Memory Queue 2 0.0 0 0 Memory Queue Message Sub 2 0.0 0 0 Memory Queue Message Sub 2 0.0 0 0 Memory Queue Message Sub 2 0.0 0 0 Memory Queue Message Sub 2 0.0 0 0 Memory Queue Subscriber 2 0.0 0 0 MinActiveScn Latch 40 0.0 0 0 Mutex 2 0.0 0 0 Mutex Stats 2 0.0 0 0 OS process 1,244 0.0 0 0 OS process allocation 7,568 0.0 0 0 OS process: request allo 276 0.0 0 0 Latch Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ PL/SQL warning settings 388 0.0 0 0 PX hash array latch 2 0.0 0 0 QMT 2 0.0 0 0 Real-time plan statistic 841 0.0 0 0 SGA IO buffer pool latch 15 0.0 0 15 0.0 SGA blob parent 2 0.0 0 0 SGA bucket locks 2 0.0 0 0 SGA heap locks 2 0.0 0 0 SGA pool locks 2 0.0 0 0 SQL memory manager latch 121 0.0 0 1,192 0.0 SQL memory manager worka 124,544 0.0 0 0 Shared B-Tree 361 0.0 0 0 Streams Generic 2 0.0 0 0 Testing 2 0.0 0 0 Token Manager 2 0.0 0 0 WCR: sync 2 0.0 0 0 Write State Object Pool 2 0.0 0 0 X$KSFQP 1 0.0 0 0 XDB NFS Security Latch 2 0.0 0 0 XDB unused session pool 2 0.0 0 0 XDB used session pool 2 0.0 0 0 active checkpoint queue 18,278 0.0 0 0 active service list 8,001 0.0 0 2,042 0.0 begin backup scn array 4 0.0 0 0 buffer pool 2 0.0 0 0 business card 2 0.0 0 0 cache buffer handles 1,182 0.0 0 0 cache buffers chains 4,963,169 0.0 0.4 6 97,334 0.0 cache buffers lru chain 152,465 0.3 1.0 7 66,554 0.2 cache table scan latch 231 0.0 0 231 0.0 call allocation 1,122 0.1 1.0 0 0 cas latch 2 0.0 0 0 change notification clie 2 0.0 0 0 channel handle pool latc 294 0.0 0 0 channel operations paren 20,014 0.0 0 0 checkpoint queue latch 140,531 0.0 1.0 0 50,616 0.0 client/application info 920,432 0.1 1.0 10 0 compile environment latc 206 0.0 0 0 corrupted undo seg latch 236 0.0 0 0 cp cmon/server latch 2 0.0 0 0 cp pool latch 2 0.0 0 0 cp server hash latch 2 0.0 0 0 cp sga latch 57 0.0 0 0 cvmap freelist lock 2 0.0 0 0 deferred cleanup latch 57 0.0 0 0 dml lock allocation 57 0.0 0 0 done queue latch 2 0.0 0 0 dummy allocation 414 0.0 0 0 enqueue hash chains 271,924 0.0 1.0 0 0 enqueues 59,725 0.0 0 0 Latch Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ fifth spare latch 2 0.0 0 0 file cache latch 182 0.0 0 0 flashback copy 2 0.0 0 0 fourth Audit Vault latch 2 0.0 0 0 gc element 2 0.0 0 0 gcs commit scn state 2 0.0 0 0 gcs partitioned table ha 2 0.0 0 0 gcs pcm hashed value buc 2 0.0 0 0 gcs resource freelist 2 0.0 0 0 gcs resource hash 2 0.0 0 0 gcs resource scan list 2 0.0 0 0 gcs shadows freelist 2 0.0 0 0 ges domain table 2 0.0 0 0 ges enqueue table freeli 2 0.0 0 0 ges group table 2 0.0 0 0 ges process hash list 2 0.0 0 0 ges process parent latch 2 0.0 0 0 ges resource hash list 2 0.0 0 0 ges resource scan list 2 0.0 0 0 ges resource table freel 2 0.0 0 0 ges value block free lis 2 0.0 0 0 global tx hash mapping 2 0.0 0 0 granule operation 2 0.0 0 0 hash table column usage 24 0.0 0 4,319 0.0 hash table modification 145 0.0 0 0 heartbeat check 2 0.0 0 0 internal temp table obje 8 0.0 0 0 intra txn parallel recov 2 0.0 0 0 io pool granule metadata 2 0.0 0 0 job workq parent latch 70 0.0 0 68 2.9 job_queue_processes free 140 0.0 0 0 job_queue_processes para 838 0.0 0 0 k2q lock allocation 2 0.0 0 0 kcbtsemkid latch 2 0.0 0 0 kdlx hb parent latch 2 0.0 0 0 kgb parent 2 0.0 0 0 kgnfs mount latch 2 0.0 0 0 kokc descriptor allocati 380 0.0 0 0 ksfv messages 2 0.0 0 0 ksim group membership ca 2 0.0 0 0 kss move lock 42 0.0 0 0 ksuosstats global area 246 0.0 0 0 ksv allocation latch 137 0.0 0 0 ksv class latch 82 0.0 0 0 ksv msg queue latch 2 0.0 0 0 ksz_so allocation latch 276 0.0 0 0 ktm global data 134 0.0 0 0 kwqbsn:qsga 134 0.0 0 0 lgwr LWN SCN 31,052 0.0 0 0 list of block allocation 219 0.0 0 0 Latch Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ loader state object free 10 0.0 0 0 lob segment dispenser la 2 0.0 0 0 lob segment hash table l 14 0.0 0 0 lob segment query latch 2 0.0 0 0 lock DBA buffer during m 2 0.0 0 0 logical standby cache 2 0.0 0 0 logminer context allocat 3 0.0 0 0 logminer work area 2 0.0 0 0 longop free list parent 2 0.0 0 0 mapped buffers lru chain 2 0.0 0 0 message pool operations 32 0.0 0 0 messages 160,738 0.0 1.0 0 0 mostly latch-free SCN 31,052 0.0 0 0 msg queue latch 2 0.0 0 0 multiblock read objects 1,530 0.0 0 0 name-service namespace b 2 0.0 0 0 ncodef allocation latch 57 0.0 0 0 object queue header heap 18,954 0.0 0 574 0.0 object queue header oper 366,767 0.0 1.0 1 0 object stats modificatio 77 0.0 0 0 parallel query alloc buf 478 0.0 0 0 parallel query stats 2 0.0 0 0 parameter list 182 0.0 0 0 parameter table manageme 974 0.0 0 0 peshm 2 0.0 0 0 pesom_free_list 2 0.0 0 0 pesom_hash_node 2 0.0 0 0 post/wait queue 17,468 0.0 0 11,816 0.0 process allocation 414 0.0 0 138 0.0 process group creation 276 0.0 0 0 process queue 2 0.0 0 0 process queue reference 2 0.0 0 0 qmn task queue latch 528 0.2 1.0 0 0 query server freelists 2 0.0 0 0 queued dump request 12 0.0 0 0 queuing load statistics 2 0.0 0 0 recovery domain hash lis 2 0.0 0 0 redo allocation 135,157 0.1 1.0 1 193,887 0.1 redo copy 153 4.6 1.0 0 193,799 0.2 redo writing 112,821 0.0 1.0 0 0 resmgr group change latc 273,677 0.0 0 0 resmgr:active threads 414 0.0 0 0 resmgr:actses change gro 90 0.0 0 0 resmgr:actses change sta 2 0.0 0 0 resmgr:free threads list 412 0.0 0 0 resmgr:plan CPU method 2 0.0 0 0 resmgr:resource group CP 2 0.0 0 0 resmgr:schema config 2 0.0 0 0 resmgr:session queuing 2 0.0 0 0 rm cas latch 2 0.0 0 0 Latch Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ row cache objects 626,277 0.0 1.0 3 0 rules engine aggregate s 1 0.0 0 0 rules engine rule set st 202 0.0 0 0 second Audit Vault latch 2 0.0 0 0 second spare latch 2 0.0 0 0 sequence cache 31,151 0.0 1.0 0 0 session allocation 121,296 0.0 1.0 0 0 session idle bit 110,965 0.0 0 0 session queue latch 2 0.0 0 0 session state list latch 472 0.0 0 0 session switching 749 0.0 0 0 session timer 1,197 0.0 0 0 shared pool 22,111 0.0 1.0 0 0 shared pool sim alloc 4 0.0 0 0 shared pool simulator 71 0.0 0 0 sim partition latch 2 0.0 0 0 simulator hash latch 118,044 0.0 1.0 0 0 simulator lru latch 5,482 0.0 1.0 0 106,986 0.4 sort extent pool 114 0.0 0 0 space background state o 9 0.0 0 0 space background task la 4,127 0.2 1.1 0 2,401 0.0 state object free list 2 0.0 0 0 statistics aggregation 560 0.0 0 0 tablespace key chain 2 0.0 0 0 temp lob duration state 2 0.0 0 0 test excl. parent l0 2 0.0 0 0 test excl. parent2 l0 2 0.0 0 0 third spare latch 2 0.0 0 0 threshold alerts latch 144 0.0 0 0 transaction allocation 925 0.0 0 0 undo global data 197,711 0.0 0 0 virtual circuit buffers 2 0.0 0 0 virtual circuit holder 2 0.0 0 0 virtual circuit queues 2 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 4,963,169 792 334 458 client/application info 920,432 693 694 0 cache buffers lru chain 152,465 457 463 2 row cache objects 626,277 206 206 0 redo allocation 135,157 108 108 0 object queue header operat 366,767 105 106 0 In memory undo latch 213,565 67 67 0 enqueue hash chains 271,924 27 27 0 messages 160,738 12 12 0 space background task latc 4,127 9 10 0 session allocation 121,296 8 8 0 redo copy 153 7 7 0 shared pool 22,111 3 3 0 simulator lru latch 5,482 2 2 0 sequence cache 31,151 2 2 0 DML lock allocation 137,831 2 2 0 redo writing 112,821 2 2 0 call allocation 1,122 1 1 0 qmn task queue latch 528 1 1 0 simulator hash latch 118,044 1 1 0 checkpoint queue latch 140,531 1 1 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: DB11G/DB11G Snaps: 3-4 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- DML lock allocation ktaidm 0 1 1 DML lock allocation ktaiam 0 1 1 In memory undo latch ktiFlush: child 0 34 61 In memory undo latch kticmt: child 0 25 1 In memory undo latch ktichg: child 0 7 3 In memory undo latch ktiFlushMe 0 1 0 cache buffers chains kcbgtcr: fast path (cr pin 0 110 25 cache buffers chains kcbgtcr: fast path 0 99 52 cache buffers chains kcbgcur: fast path (shr) 0 63 7 cache buffers chains kcbget: pin buffer 0 21 75 cache buffers chains kcbgcur_2 0 11 16 cache buffers chains kcbgtcr: kslbegin shared 0 11 2 cache buffers chains kcbchg1: kslbegin: bufs no 0 7 1 cache buffers chains kcbnlc 0 6 4 cache buffers chains kcbzibmlt: multi-block rea 0 1 0 cache buffers chains kcbrls_1 0 1 66 cache buffers chains kcbzgb: scan from tail. no 0 1 0 cache buffers chains kcbnew: new latch again 0 1 0 cache buffers chains kcbzwb 0 1 0 cache buffers chains kcbget: exchange 0 1 53 cache buffers lru chain kcbzgws_1 0 445 444 cache buffers lru chain kcbbxsv: move to being wri 0 10 0 cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP_2 0 4 0 cache buffers lru chain kcbzgb 0 2 1 cache buffers lru chain kcbbic2 0 1 18 cache buffers lru chain kcbzswcu 0 1 0 call allocation ksudlp: top call 0 1 0 checkpoint queue latch kcbklbc_cql 0 1 0 client/application info ksuinfos_modact 0 431 585 client/application info kskirefrattrmap 0 263 109 enqueue hash chains ksqrcl 0 18 0 enqueue hash chains ksqgtl3 0 9 27 messages ksaamb: after wakeup 0 8 6 messages ksarcv 0 4 2 object queue header oper kcbo_switch_cq 0 46 40 object queue header oper kcbo_unlink_q 0 22 52 object queue header oper kcbo_link_q 0 18 7 object queue header oper kcbo_switch_q_bg 0 13 0 object queue header oper kcbo_switch_mq_bg 0 7 5 qmn task queue latch kwqmnmvtsks: delay to read 0 1 0 redo allocation kcrfw_redo_gen: redo alloc 0 94 0 redo allocation kcrfw_post: more space 0 10 2 redo allocation kcrfw_redo_gen: redo alloc 0 2 63 redo allocation kcrfw_redo_write: before w 0 2 43 redo copy kcrfw_redo_gen: nowait 0 7 0 redo writing kcrfw_cal_target_rba 0 1 1 redo writing kcrfwcr 0 1 0 row cache objects kqreqd: reget 0 185 0 row cache objects kqrpre: find obj 0 18 205 row cache objects kqreqd 0 3 1 sequence cache kdnssd 0 2 0 session allocation ksucri_int : SSO 0 4 7 session allocation ksuxds 0 4 1 Latch Miss Sources DB/Inst: DB11G/DB11G Snaps: 3-4 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- shared pool kghalo 0 2 1 shared pool kghfre 0 1 2 simulator hash latch kcbs_lookup_setid: lookup 0 1 0 simulator lru latch kcbs_simulate: simulate se 0 2 0 space background task la ktsjCreateTask 0 6 0 space background task la ktsj_grab_task 0 4 10 ------------------------------------------------------------- Mutex Sleep DB/Inst: DB11G/DB11G Snaps: 3-4 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Library Cache kglpin1 4 56 3.6 Library Cache kglpndl1 95 55 1.4 Cursor Pin kksfbc [KKSCHLFSP2] 31 0.8 Library Cache kglhdgn2 106 25 0.7 Library Cache kglget1 1 10 0.3 Library Cache kglobpn1 71 6 0.1 Cursor Stat kkocsStoreBindAwareStats [KKSSTA 1 0.0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: DB11G/DB11G Snaps: 3-4 ->"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 68 0.0 0 3 1 dc_files 6 0.0 0 0 6 dc_global_oids 49,869 0.0 0 0 76 dc_histogram_data 622 0.0 0 0 2,138 dc_histogram_defs 838 0.0 0 0 5,633 dc_objects 50,886 0.0 0 5 3,048 dc_profiles 70 0.0 0 0 1 dc_rollback_segments 893 0.0 0 0 22 dc_segments 94 0.0 0 26 976 dc_sequences 10 0.0 0 10 17 dc_tablespaces 23,068 0.0 0 0 9 dc_users 80,031 0.0 0 0 311 global database name 2,470 0.0 0 0 1 outstanding_alerts 32 3.1 0 1 7 sch_lj_oids 5 0.0 0 0 9 ------------------------------------------------------------- Library Cache Activity DB/Inst: DB11G/DB11G Snaps: 3-4 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- SQL AREA 1,348 0.5 230,091 0.0 0 0 TABLE/PROCEDURE 25,174 0.0 145,140 0.0 0 0 BODY 1,077 0.0 109,138 0.0 0 0 TRIGGER 185 0.0 239 0.0 0 0 INDEX 18 0.0 18 0.0 0 0 CLUSTER 3 0.0 3 0.0 0 0 QUEUE 77 0.0 240 0.0 0 0 SUBSCRIPTION 8 0.0 8 0.0 0 0 EDITION 84 0.0 152 0.0 0 0 DBLINK 2 0.0 0 0 0 SCHEMA 68 0.0 0 0 0 SQL AREA STATS 13 100.0 13 100.0 0 0 SQL AREA BUILD 13 53.8 0 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: DB11G/DB11G Snaps: 3-4 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: DB11G/DB11G End Snap: 4 Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill Size (M) Size Factor Count Time (s) Count Time (s) ------------ ------------ --------- --------- ----------- ----------- 4.0 .5 0 0 0 0 8.0 1.0 0 0 0 0 12.0 1.5 0 0 0 0 16.0 2.0 0 0 0 0 20.0 2.5 0 0 0 0 24.0 3.0 0 0 0 0 28.0 3.5 0 0 0 0 32.0 4.0 0 0 0 0 36.0 4.5 0 0 0 0 40.0 5.0 0 0 0 0 44.0 5.5 0 0 0 0 48.0 6.0 0 0 0 0 52.0 6.5 0 0 0 0 56.0 7.0 0 0 0 0 60.0 7.5 0 0 0 0 64.0 8.0 0 0 0 0 68.0 8.5 0 0 0 0 72.0 9.0 0 0 0 0 76.0 9.5 0 0 0 0 80.0 10.0 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: DB11G/DB11G End Snap: 4 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 160 .6 30 2,247 32,981 1.0 281 2.8 622,731 192 .7 62 4,021 33,119 1.0 143 1.4 649,870 224 .8 94 5,906 33,161 1.0 101 1.0 650,330 256 .9 108 6,820 33,162 1.0 100 1.0 650,363 288 1.0 109 6,852 33,162 1.0 100 1.0 650,363 320 1.1 109 6,852 33,162 1.0 100 1.0 650,363 352 1.2 109 6,852 33,162 1.0 100 1.0 650,363 384 1.3 109 6,852 33,162 1.0 100 1.0 650,363 416 1.4 109 6,852 33,162 1.0 100 1.0 650,363 448 1.6 109 6,852 33,162 1.0 100 1.0 650,363 480 1.7 109 6,852 33,162 1.0 100 1.0 650,363 512 1.8 109 6,852 33,162 1.0 100 1.0 650,363 544 1.9 109 6,852 33,162 1.0 100 1.0 650,363 576 2.0 109 6,852 33,162 1.0 100 1.0 650,363 ------------------------------------------------------------- SGA Target Advisory DB/Inst: DB11G/DB11G End Snap: 4 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 262 .5 8,336 1.1 619,354 393 .8 7,673 1.0 367,986 524 1.0 7,517 1.0 311,562 655 1.3 7,502 1.0 304,365 786 1.5 7,502 1.0 304,365 917 1.8 7,502 1.0 304,365 1,048 2.0 7,502 1.0 304,365 ------------------------------------------------------------- SGA Memory Summary DB/Inst: DB11G/DB11G Snaps: 3-4 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 197,132,288 Fixed Size 2,231,128 Redo Buffers 2,383,872 Variable Size 637,535,400 -------------------- -------------------- sum 839,282,688 ------------------------------------------------------------- SGA breakdown difference DB/Inst: DB11G/DB11G Snaps: 3-4 -> 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 ------ ------------------------------ -------------- -------------- -------- java p free memory 28.0 28.0 0.00 large PX msg pool .5 .5 0.00 large free memory 3.5 3.5 0.00 shared FileOpenBlock 3.8 3.8 0.00 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLH0 39.4 39.6 0.47 shared KGLHD 8.8 8.8 0.22 shared KGLS 17.4 17.4 0.00 shared KGLSG 5.0 5.0 0.00 shared KQR L PO 3.3 3.3 0.09 shared KQR M PO 3.6 3.6 0.00 shared KSFD SGA I/O b 3.8 3.8 0.00 shared PLDIA 8.6 8.6 0.00 shared PLMCD 8.6 8.6 0.00 shared SQLA 53.0 53.7 1.38 shared event statistics per sess 3.1 3.1 0.00 shared free memory 57.0 56.0 -1.69 shared kglsim hash table bkts 4.0 4.0 0.00 shared private strands 3.5 3.5 0.00 shared row cache 7.2 7.2 0.00 stream free memory 8.0 8.0 0.00 buffer_cache 188.0 188.0 0.00 fixed_sga 2.1 2.1 0.00 log_buffer 2.3 2.3 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: DB11G/DB11G Snaps: 3-4 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 27.65 27.89 .83 Cursor to Parent ratio: 1.08 1.08 .26 Total Cursors: 2,083 2,096 .62 Total Parents: 1,925 1,932 .36 ------------------------------------------------------------- init.ora Parameters DB/Inst: DB11G/DB11G Snaps: 3-4 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /u01/app/oracle/admin/DB11G/adump audit_trail DB compatible 11.2.0.0.0 control_files /u01/app/oracle/oradata/DB11G/con trol01.ctl, /u01/app/oracle/flash _recovery_area/DB11G/control02.ct l db_block_size 8192 db_create_file_dest /u01/app/oracle/oradata/DB11G/ db_domain WORLD db_name DB11G db_recovery_file_dest /u01/app/oracle/flash_recovery_ar ea db_recovery_file_dest_size 4070572032 diagnostic_dest /u01/app/oracle dispatchers (PROTOCOL=TCP) (SERVICE=DB11GXDB) event memory_target 843055104 open_cursors 300 processes 150 remote_login_passwordfile EXCLUSIVE undo_tablespace UNDOTBS1 ------------------------------------------------------------- End of Report ( sp_demo.lst )