STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1510982787 test1020 1 11-Aug-08 11:28 10.2.0.4.0 NO Host Name: LAPTOP Num CPUs: 2 Phys Memory (MB): 2,046 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 24 12-Aug-08 21:50:44 26 15.6 End Snap: 25 12-Aug-08 21:56:14 27 15.7 Elapsed: 5.50 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 396M Std Block Size: 8K Shared Pool Size: 168M Log Buffer: 6,817K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 49,059.30 2,677.29 Logical reads: 13,360.46 729.11 Block changes: 333.02 18.17 Physical reads: 17.56 0.96 Physical writes: 38.15 2.08 User calls: 29.40 1.60 Parses: 16.81 0.92 Hard parses: 0.05 0.00 Sorts: 11.72 0.64 Logons: 0.04 0.00 Executes: 123.28 6.73 Transactions: 18.32 % Blocks changed per Read: 2.49 Recursive Call %: 89.74 Rollback per transaction %: 0.05 Rows per Sort: 769.47 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.87 In-memory Sort %: 100.00 Library Hit %: 99.96 Soft Parse %: 99.73 Execute to Parse %: 86.37 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 43.10 % Non-Parse CPU: 99.77 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 88.69 88.39 % SQL with executions>1: 87.61 88.36 % Memory for SQL w/exec>1: 82.87 84.06 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ db file sequential read 5,814 461 79 38.0 log file sync 4,246 421 99 34.7 log file parallel write 3,222 109 34 9.0 CPU time 108 8.9 db file parallel write 1,718 60 35 4.9 ------------------------------------------------------------- Host CPU (CPUs: 2) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 34.00 9.57 56.42 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 16.74 % of busy CPU for Instance: 38.43 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 2,045.7 2,045.7 SGA use (MB): 584.0 584.0 PGA use (MB): 39.2 40.9 % Host Mem used for SGA+PGA: 30.5 30.5 ------------------------------------------------------------- Time Model System Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 779.5 64.8 DB CPU 107.7 9.0 PL/SQL execution elapsed time 15.7 1.3 parse time elapsed 2.8 .2 hard parse elapsed time 2.2 .2 hard parse (sharing criteria) elaps 1.1 .1 hard parse (bind mismatch) elapsed 1.1 .1 connection management call elapsed 0.1 .0 repeated bind elapsed time 0.0 .0 sequence load elapsed time 0.0 .0 DB time 1,202.4 background elapsed time 408.3 background cpu time 2.7 ------------------------------------------------------------- Wait Events DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- db file sequential read 5,814 0 461 79 1.0 log file sync 4,246 1 421 99 0.7 log file parallel write 3,222 0 109 34 0.5 db file parallel write 1,718 0 60 35 0.3 latch: cache buffers chains 342 0 20 59 0.1 control file sequential read 1,992 0 18 9 0.3 control file parallel write 105 0 15 141 0.0 read by other session 15 0 1 49 0.0 enq: CF - contention 1 0 1 680 0.0 latch: cache buffers lru chain 11 0 1 50 0.0 buffer busy waits 22 0 1 24 0.0 latch: enqueue hash chains 4 0 0 46 0.0 db file scattered read 2 0 0 28 0.0 LGWR wait for redo copy 23 9 0 1 0.0 direct path write 2 0 0 14 0.0 latch: library cache 4 0 0 6 0.0 SQL*Net more data to client 47 0 0 1 0.0 latch free 5 0 0 5 0.0 SQL*Net break/reset to client 26 0 0 0 0.0 latch: In memory undo latch 15 0 0 0 0.0 enq: TX - row lock contention 2 0 0 1 0.0 SQL*Net message from client 5,298 0 2,354 444 0.9 wait for unread message on broadc 324 100 328 1013 0.1 jobq slave wait 109 95 327 3002 0.0 Streams AQ: waiting for messages 66 100 326 4941 0.0 virtual circuit status 11 100 323 29377 0.0 SQL*Net message to client 5,299 0 0 0 0.9 ------------------------------------------------------------- Background Wait Events DB/Inst: TEST1020/test1020 Snaps: 24-25 -> %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 %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- log file parallel write 3,222 0 109 34 0.5 db file parallel write 1,718 0 60 35 0.3 control file parallel write 105 0 15 141 0.0 control file sequential read 108 0 10 95 0.0 latch: cache buffers chains 17 0 5 315 0.0 events in waitclass Other 24 8 0 4 0.0 rdbms ipc message 1,341 69 2,227 1661 0.2 pmon timer 112 100 327 2921 0.0 smon timer 1 0 144 ###### 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- LGWR wait for redo copy 23 87.0 4.3 8.7 SQL*Net break/reset to cli 26 92.3 3.8 3.8 SQL*Net more data to clien 47 76.6 21.3 2.1 buffer busy waits 22 77.3 4.5 18.2 control file parallel writ 105 3.8 1.0 1.9 12.4 14.3 65.7 1.0 control file sequential re 1992 89.1 1.0 .4 .8 1.2 1.9 5.6 db file parallel write 1718 24.9 8.8 8.6 10.3 9.9 13.5 24.1 db file scattered read 2 50.0 50.0 db file sequential read 5815 .8 .1 .1 .8 3.6 15.5 78.9 .2 direct path write 2 50.0 50.0 enq: CF - contention 1 100.0 enq: TX - row lock content 2 100.0 latch free 5 40.0 20.0 40.0 latch: In memory undo latc 15 93.3 6.7 latch: cache buffers chain 340 5.3 1.8 2.6 6.8 11.5 28.8 42.9 .3 latch: cache buffers lru c 11 18.2 18.2 63.6 latch: enqueue hash chains 4 25.0 75.0 latch: library cache 4 75.0 25.0 latch: library cache pin 1 100.0 latch: object queue header 1 100.0 log file parallel write 3222 41.0 1.1 .7 2.5 9.7 21.3 23.4 .4 log file sync 4246 .9 1.3 1.2 2.5 6.3 16.2 71.6 read by other session 15 6.7 6.7 40.0 46.7 SQL*Net message from clien 5298 10.6 26.8 32.6 13.0 4.9 4.0 5.8 2.3 SQL*Net message to client 5299 100.0 .0 SQL*Net more data from cli 6 100.0 Streams AQ: waiting for me 66 1.5 98.5 dispatcher timer 5 100.0 jobq slave wait 109 100.0 pmon timer 112 2.7 97.3 rdbms ipc message 1341 20.1 .3 .4 .8 1.9 2.1 29.5 45.0 smon timer 1 100.0 virtual circuit status 11 100.0 wait for unread message on 324 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 108 -> Captured SQL accounts for 166.8% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 83.69 1,143 0.07 77.7 327.32 4,027,101 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 60.80 3,397 0.02 56.4 96.86 3,898,900 3803592478 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 = :B1 AND INV ENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.W 8.95 1,242 0.01 8.3 138.52 305,091 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 4.79 2 2.39 4.4 15.63 16,252 4101483272 Module: java.exe BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; 4.17 3,796 0.00 3.9 6.01 290,934 3352865145 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 = :B1 AND INVE NTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID 2.61 1,259 0.00 2.4 154.63 15,276 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 2.15 1 2.15 2.0 4.56 2,842 2522684317 Module: sqlplus.exe BEGIN statspack.snap; END; 1.85 4,124 0.00 1.7 374.96 12,394 3623764046 Module: New Order 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 = :B1 1.34 480 0.00 1.2 123.84 15,374 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 1.26 3,975 0.00 1.2 47.80 69,058 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 1.15 4,124 0.00 1.1 12.36 24,820 1809837244 Module: New Order SQL ordered by CPU DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 108 -> Captured SQL accounts for 166.8% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- INSERT INTO LOGON VALUES (:B2 , :B1 ) ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 1,202 -> Captured SQL accounts for 126.1% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 374.96 4,124 0.09 31.2 1.85 3,072 3623764046 Module: New Order 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 = :B1 327.32 1,143 0.29 27.2 83.69 1,822 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 154.63 1,259 0.12 12.9 2.61 1,198 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 138.52 1,242 0.11 11.5 8.95 1,060 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 123.84 480 0.26 10.3 1.34 1,567 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 122.08 480 0.25 10.2 0.59 1,566 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 R(DBMS_RANDOM.VALUE(:B2 , :B1 ))) 96.86 3,397 0.03 8.1 60.80 0 3803592478 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 = :B1 AND INV ENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.W 47.80 3,975 0.01 4.0 1.26 425 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 45.79 1,143 0.04 3.8 0.71 426 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 15.63 2 7.81 1.3 4.79 95 4101483272 Module: java.exe BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; SQL ordered by Elapsed DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 1,202 -> Captured SQL accounts for 126.1% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 12.36 4,124 0.00 1.0 1.15 28 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) ------------------------------------------------------------- SQL ordered by Gets DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 4,408,953 -> Captured SQL accounts for 99.7% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 4,027,101 1,143 3,523.3 91.3 83.69 327.32 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 3,898,900 3,397 1,147.7 88.4 60.80 96.86 3803592478 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 = :B1 AND INV ENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.W 305,091 1,242 245.6 6.9 8.95 138.52 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 290,934 3,796 76.6 6.6 4.17 6.01 3352865145 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 = :B1 AND INVE NTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID 69,058 3,975 17.4 1.6 1.26 47.80 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) ------------------------------------------------------------- SQL ordered by Reads DB/Inst: TEST1020/test1020 Snaps: 24-25 -> End Disk Reads Threshold: 1000 Total Disk Reads: 5,795 -> Captured SQL accounts for 99.0% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 3,072 4,124 0.7 53.0 1.85 374.96 3623764046 Module: New Order 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 = :B1 1,822 1,143 1.6 31.4 83.69 327.32 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 1,567 480 3.3 27.0 1.34 123.84 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 1,566 480 3.3 27.0 0.59 122.08 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 R(DBMS_RANDOM.VALUE(:B2 , :B1 ))) 1,198 1,259 1.0 20.7 2.61 154.63 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 1,060 1,242 0.9 18.3 8.95 138.52 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 426 1,143 0.4 7.4 0.71 45.79 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 425 3,975 0.1 7.3 1.26 47.80 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 123 1,259 0.1 2.1 0.15 9.03 287228808 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 = :B1 95 2 47.5 1.6 4.79 15.63 4101483272 Module: java.exe BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; ------------------------------------------------------------- SQL ordered by Executions DB/Inst: TEST1020/test1020 Snaps: 24-25 -> End Executions Threshold: 100 Total Executions: 40,683 -> Captured SQL accounts for 99.1% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 4,126 4,126 1.0 0.00 0.00 2293415029 Module: emagent.exe SELECT SYSDATE FROM DUAL 4,124 4,124 1.0 0.00 0.00 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 4,124 4,116 1.0 0.00 0.09 3623764046 Module: New Order 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 = :B1 3,975 3,975 1.0 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 3,975 3,975 1.0 0.00 0.01 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 3,796 113,880 30.0 0.00 0.00 3352865145 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 = :B1 AND INVE NTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID 3,397 339,700 100.0 0.02 0.03 3803592478 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 = :B1 AND INV ENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID ORDER BY INVENTORIES.W 1,259 108 0.1 0.00 0.01 287228808 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 = :B1 1,259 1,259 1.0 0.00 0.12 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 1,242 1,242 1.0 0.01 0.11 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; SQL ordered by Executions DB/Inst: TEST1020/test1020 Snaps: 24-25 -> End Executions Threshold: 100 Total Executions: 40,683 -> Captured SQL accounts for 99.1% 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,143 1,143 1.0 0.07 0.29 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 1,143 1,142 1.0 0.00 0.04 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 1,142 3,975 3.5 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 1,142 1,142 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 480 480 1.0 0.00 0.26 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 480 480 1.0 0.00 0.00 2690112681 Module: New Customer SELECT CUSTOMER_SEQ.NEXTVAL FROM DUAL 480 480 1.0 0.00 0.25 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 R(DBMS_RANDOM.VALUE(:B2 , :B1 ))) ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: TEST1020/test1020 Snaps: 24-25 -> End Parse Calls Threshold: 1000 Total Parse Calls: 5,547 -> Captured SQL accounts for 92.5% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 1,259 1,259 22.70 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 1,242 1,242 22.39 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 1,143 1,143 20.61 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 480 480 8.65 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 224 224 4.04 2757634508 BEGIN :1 := orderentry.processorders(:2,:3); END; 110 110 1.98 3716207873 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=: 1 89 89 1.60 651529688 INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message# ,fac,hdr,lm,nl,p1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,: 9,:10,:11,:12,:13) 83 83 1.50 2142234931 SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 10,750 32.6 1.8 CPU used when call started 10,490 31.8 1.7 CR blocks created 352 1.1 0.1 Cached Commit SCN referenced 3,081,269 9,337.2 509.6 Commit SCN cached 4 0.0 0.0 DB time 216,230 655.2 35.8 DBWR checkpoint buffers written 12,589 38.2 2.1 DBWR checkpoints 0 0.0 0.0 DBWR thread checkpoint buffers wr 0 0.0 0.0 DBWR transaction table writes 126 0.4 0.0 DBWR undo block writes 886 2.7 0.2 IMU CR rollbacks 50 0.2 0.0 IMU Flushes 293 0.9 0.1 IMU Redo allocation size 816,756 2,475.0 135.1 IMU commits 5,831 17.7 1.0 IMU contention 52 0.2 0.0 IMU ktichg flush 1 0.0 0.0 IMU recursive-transaction flush 8 0.0 0.0 IMU undo allocation size 15,461,572 46,853.3 2,556.9 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 SMON posted for undo segment shri 0 0.0 0.0 SQL*Net roundtrips to/from client 5,279 16.0 0.9 active txn count during cleanout 257 0.8 0.0 application wait time 1 0.0 0.0 background timeouts 1,025 3.1 0.2 buffer is not pinned count 51,490 156.0 8.5 buffer is pinned count 209,666 635.4 34.7 bytes received via SQL*Net from c 2,025,290 6,137.2 334.9 bytes sent via SQL*Net to client 1,404,312 4,255.5 232.2 calls to get snapshot scn: kcmgss 231,099 700.3 38.2 calls to kcmgas 7,063 21.4 1.2 calls to kcmgcs 113 0.3 0.0 change write time 111 0.3 0.0 cleanout - number of ktugct calls 358 1.1 0.1 cleanouts and rollbacks - consist 66 0.2 0.0 cleanouts only - consistent read 26 0.1 0.0 cluster key scan block gets 173 0.5 0.0 cluster key scans 91 0.3 0.0 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 3 0.0 0.0 commit batch/immediate requested 3 0.0 0.0 commit cleanout failures: block l 1 0.0 0.0 commit cleanout failures: buffer 5 0.0 0.0 commit cleanout failures: callbac 33 0.1 0.0 commit cleanout failures: cannot 31 0.1 0.0 commit cleanouts 26,905 81.5 4.5 commit cleanouts successfully com 26,835 81.3 4.4 commit immediate performed 3 0.0 0.0 commit immediate requested 3 0.0 0.0 commit txn count during cleanout 204 0.6 0.0 concurrency wait time 2,062 6.3 0.3 consistent changes 416 1.3 0.1 consistent gets 4,268,237 12,934.1 705.8 consistent gets - examination 65,452 198.3 10.8 Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ consistent gets direct 1 0.0 0.0 consistent gets from cache 4,268,236 12,934.1 705.8 cursor authentications 49 0.2 0.0 data blocks consistent reads - un 338 1.0 0.1 db block changes 109,898 333.0 18.2 db block gets 140,716 426.4 23.3 db block gets direct 2 0.0 0.0 db block gets from cache 140,714 426.4 23.3 deferred (CURRENT) block cleanout 20,090 60.9 3.3 dirty buffers inspected 0 0.0 0.0 enqueue conversions 1,257 3.8 0.2 enqueue releases 30,916 93.7 5.1 enqueue requests 30,916 93.7 5.1 enqueue timeouts 0 0.0 0.0 enqueue waits 3 0.0 0.0 execute count 40,683 123.3 6.7 failed probes on index block recl 0 0.0 0.0 free buffer inspected 6,769 20.5 1.1 free buffer requested 7,011 21.3 1.2 heap block compress 64 0.2 0.0 hot buffers moved to head of LRU 2,344 7.1 0.4 immediate (CR) block cleanout app 92 0.3 0.0 immediate (CURRENT) block cleanou 449 1.4 0.1 index crx upgrade (found) 0 0.0 0.0 index crx upgrade (positioned) 725 2.2 0.1 index fast full scans (full) 14 0.0 0.0 index fetch by key 129,756 393.2 21.5 index scans kdiixs1 9,144 27.7 1.5 leaf node 90-10 splits 37 0.1 0.0 leaf node splits 108 0.3 0.0 lob reads 1 0.0 0.0 lob writes 78 0.2 0.0 lob writes unaligned 78 0.2 0.0 logons cumulative 12 0.0 0.0 messages received 4,958 15.0 0.8 messages sent 4,958 15.0 0.8 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 4,009,504 12,150.0 663.1 opened cursors cumulative 5,655 17.1 0.9 parse count (failures) 0 0.0 0.0 parse count (hard) 15 0.1 0.0 parse count (total) 5,547 16.8 0.9 parse time cpu 25 0.1 0.0 parse time elapsed 58 0.2 0.0 physical read IO requests 5,787 17.5 1.0 physical read bytes 47,472,640 143,856.5 7,850.6 physical read total IO requests 7,809 23.7 1.3 physical read total bytes 80,355,328 243,501.0 13,288.5 physical read total multi block r 2 0.0 0.0 physical reads 5,795 17.6 1.0 physical reads cache 5,794 17.6 1.0 physical reads cache prefetch 8 0.0 0.0 physical reads direct 1 0.0 0.0 physical reads direct (lob) 0 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical write IO requests 9,297 28.2 1.5 physical write bytes 103,145,472 312,562.0 17,057.3 physical write total IO requests 12,862 39.0 2.1 physical write total bytes 125,916,160 381,564.1 20,822.9 physical write total multi block 4,471 13.6 0.7 physical writes 12,591 38.2 2.1 physical writes direct 2 0.0 0.0 physical writes direct (lob) 2 0.0 0.0 physical writes direct temporary 0 0.0 0.0 physical writes from cache 12,589 38.2 2.1 physical writes non checkpoint 3,505 10.6 0.6 prefetch warmup blocks aged out b 672 2.0 0.1 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 87 0.3 0.0 recovery blocks read 0 0.0 0.0 recursive calls 84,806 257.0 14.0 recursive cpu usage 9,182 27.8 1.5 redo blocks read for recovery 0 0.0 0.0 redo blocks written 34,394 104.2 5.7 redo entries 20,238 61.3 3.4 redo ordering marks 330 1.0 0.1 redo size 16,189,568 49,059.3 2,677.3 redo subscn max counts 1,004 3.0 0.2 redo synch time 42,284 128.1 7.0 redo synch writes 4,373 13.3 0.7 redo wastage 950,488 2,880.3 157.2 redo write time 10,945 33.2 1.8 redo writer latching time 3 0.0 0.0 redo writes 3,222 9.8 0.5 rollback changes - undo records a 6 0.0 0.0 rollbacks only - consistent read 261 0.8 0.0 rows fetched via callback 15,525 47.1 2.6 session connect time 0 0.0 0.0 session cursor cache hits 4,810 14.6 0.8 session logical reads 4,408,953 13,360.5 729.1 session pga memory 7,681,528 23,277.4 1,270.3 session pga memory max 16,791,032 50,881.9 2,776.8 session uga memory 21,475,899,488 65,078,483.3 3,551,496.5 session uga memory max 20,714,172 62,770.2 3,425.5 shared hash latch upgrades - no w 5,118 15.5 0.9 shared hash latch upgrades - wait 0 0.0 0.0 sorts (memory) 3,866 11.7 0.6 sorts (rows) 2,974,764 9,014.4 491.9 sql area evicted 18 0.1 0.0 sql area purged 0 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 17 0.1 0.0 table fetch by rowid 27,161 82.3 4.5 table fetch continued row 17 0.1 0.0 table scan blocks gotten 3,992,205 12,097.6 660.2 table scan rows gotten 32,502,341 98,491.9 5,375.0 table scans (long tables) 0 0.0 0.0 table scans (short tables) 61,801 187.3 10.2 total number of times SMON posted 1 0.0 0.0 transaction rollbacks 3 0.0 0.0 undo change vector size 5,528,944 16,754.4 914.3 Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ user I/O wait time 46,212 140.0 7.6 user calls 9,701 29.4 1.6 user commits 6,044 18.3 1.0 user rollbacks 3 0.0 0.0 workarea executions - optimal 10,564 32.0 1.8 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 132 0.4 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 26 27 opened cursors current 405 424 session cursor cache count 20,431 20,627 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- OS Statistics DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 28,738 IDLE_TIME 37,210 SYS_TIME 6,314 USER_TIME 22,424 VM_IN_BYTES 122,572,800 VM_OUT_BYTES 99,512,320 PHYSICAL_MEMORY_BYTES 2,145,058,816 NUM_CPUS 2 NUM_CPU_CORES 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SOEINDEX 2,371 7 75.7 1.0 6,800 21 16 73.1 SOE 3,300 10 84.3 1.0 1,280 4 13 11.5 SYSAUX 110 0 18.3 1.1 780 2 0 0.0 UNDOTBS1 0 0 0.0 402 1 8 0.0 SYSTEM 5 0 74.0 1.0 35 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ SOE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOE.DBF 3,300 10 84.3 ### 1.0 1,280 4 13 11.5 SOEINDEX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOEINDEX.D 2,371 7 75.7 ### 1.0 6,800 21 16 73.1 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSAUX01.D 110 0 18.3 ### 1.1 780 2 0 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSTEM01.D 5 0 74.0 ### 1.0 35 0 0 UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\UNDOTBS01. 0 0 402 1 8 0.0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ SOE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOE.DBF 0 0 3 54 501 2,742 SOEINDEX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOEINDEX.D 9 5 38 124 358 1,837 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSAUX01.D 38 1 7 21 31 10 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSTEM01.D 0 0 0 0 0 5 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 49K 100 4,408,173 5,793 12,589 0 0 37 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 37 35 1040 6158 36063 1843200 36063 E 37 35 617 4696 70235 1843200 70235 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: TEST1020/test1020 End Snap: 25 -> 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 36 .1 4 2.2 160 5,116 89.6 D 72 .2 9 1.7 124 3,628 63.6 D 108 .3 13 1.5 108 2,993 52.4 D 144 .4 18 1.4 98 2,569 45.0 D 180 .5 22 1.3 90 2,267 39.7 D 216 .5 27 1.2 85 2,030 35.6 D 252 .6 31 1.1 81 1,869 32.7 D 288 .7 36 1.1 77 1,707 29.9 D 324 .8 40 1.0 73 1,568 27.5 D 360 .9 45 1.0 73 1,548 27.1 D 396 1.0 49 1.0 72 1,526 26.7 D 432 1.1 54 1.0 72 1,520 26.6 D 468 1.2 58 1.0 72 1,520 26.6 D 504 1.3 63 1.0 72 1,520 26.6 D 540 1.4 67 1.0 72 1,520 26.6 D 576 1.5 72 1.0 72 1,520 26.6 D 612 1.5 76 1.0 72 1,520 26.6 D 648 1.6 81 1.0 72 1,520 26.6 D 684 1.7 85 1.0 72 1,520 26.6 D 720 1.8 90 1.0 72 1,520 26.6 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 29 1 46 undo header 8 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 5,099 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 194 152 39.2 0.0 .0 .0 .0 39,731 E 194 151 40.9 0.0 .0 .0 .0 39,731 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: TEST1020/test1020 Snaps: 24-25 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 305 305 0 0 64K 128K 6 6 0 0 128K 256K 4 4 0 0 256K 512K 6,794 6,794 0 0 512K 1024K 3,451 3,451 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: TEST1020/test1020 End Snap: 25 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 24 0.1 8,861.6 180.4 98.0 58 49 0.3 8,861.6 0.0 100.0 0 97 0.5 8,861.6 0.0 100.0 0 146 0.8 8,861.6 0.0 100.0 0 194 1.0 8,861.6 0.0 100.0 0 233 1.2 8,861.6 0.0 100.0 0 272 1.4 8,861.6 0.0 100.0 0 310 1.6 8,861.6 0.0 100.0 0 349 1.8 8,861.6 0.0 100.0 0 388 2.0 8,861.6 0.0 100.0 0 582 3.0 8,861.6 0.0 100.0 0 776 4.0 8,861.6 0.0 100.0 0 1,164 6.0 8,861.6 0.0 100.0 0 1,552 8.0 8,861.6 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 -------- 39.5 25.2 .0 1.4 1.7 9 14 28 Other 37.9 1.4 1.7 9 10 28 SQL 1.1 .4 .1 .1 0 6 18 PL/SQL .4 .3 .0 .0 0 0 26 E -------- 41.2 26.3 .0 1.4 1.7 9 14 29 Other 39.6 1.4 1.7 9 10 29 SQL 1.2 .4 .1 .1 0 6 19 PL/SQL .5 .3 .0 .0 0 0 27 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 6 LGWR -------- 9.1 4.2 .0 9.1 10.3 Other 9.1 9.1 10.3 PL/SQL .0 .0 .0 .0 22 SHAD -------- 4.3 3.4 .0 4.3 14.1 Other 4.1 4.1 7.5 SQL .2 .1 .2 6.5 PL/SQL .0 .0 .0 .2 21 SHAD -------- 3.0 2.2 .0 3.0 5.7 Other 2.8 2.8 4.2 SQL .1 .1 .1 1.5 PL/SQL .0 .0 .0 .0 15 SHAD -------- 2.0 1.2 .0 2.0 5.2 Other 1.9 1.9 1.9 SQL .1 .0 .1 3.6 PL/SQL .1 .0 .1 .1 20 SHAD -------- 1.8 1.1 .0 1.8 4.7 Other 1.7 1.7 1.7 SQL .1 .0 .1 3.4 PL/SQL .0 .0 .0 .0 28 SHAD -------- 1.5 .8 .0 1.5 4.0 Other 1.5 1.5 1.8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 2.2 11 MMON -------- 1.5 1.1 .0 1.5 3.0 Other 1.5 1.5 1.8 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 25 SHAD -------- 1.5 1.4 .0 1.5 3.2 Other 1.4 1.4 1.5 SQL .1 .0 .1 1.6 PL/SQL .0 .0 .0 .0 23 SHAD -------- 1.3 1.2 .0 1.3 3.3 Other 1.2 1.2 1.5 SQL .1 .0 .1 1.7 PL/SQL .0 .0 .0 .1 27 SHAD -------- 1.3 1.1 .0 1.3 2.3 Other 1.1 1.1 1.5 SQL .1 .0 .1 .7 PL/SQL .0 .0 .0 .1 17 J000 -------- 1.2 .1 .0 1.2 2.2 Other 1.2 1.2 1.6 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .5 24 SHAD -------- 1.1 1.1 .0 1.1 1.7 Other 1.0 1.0 1.4 SQL .1 .0 .1 .3 PL/SQL .0 .0 .0 .0 16 SHAD -------- 1.1 .8 .0 1.1 7.8 Other 1.1 1.1 2.8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 5.0 E 6 LGWR -------- 9.1 4.2 .0 9.1 10.3 Other 9.1 9.1 10.3 Top Process Memory (by component) DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 6 PL/SQL .0 .0 .0 .0 22 SHAD -------- 4.3 3.4 .0 4.3 14.1 Other 4.1 4.1 7.5 SQL .2 .1 .2 6.5 PL/SQL .0 .0 .0 .2 21 SHAD -------- 3.1 2.4 .0 3.1 5.7 Other 3.0 3.0 4.2 SQL .1 .1 .1 1.5 PL/SQL .0 .0 .0 .0 15 SHAD -------- 2.0 1.2 .0 2.0 5.2 Other 1.9 1.9 1.9 SQL .1 .0 .1 3.6 PL/SQL .1 .0 .1 .1 30 SHAD -------- 1.8 .8 .0 1.8 6.6 Other 1.8 1.8 1.8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 5.1 20 SHAD -------- 1.5 1.2 .0 1.5 4.7 Other 1.4 1.4 1.4 SQL .1 .0 .1 3.4 PL/SQL .0 .0 .0 .0 28 SHAD -------- 1.5 .8 .0 1.5 4.0 Other 1.5 1.5 1.8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 2.2 11 MMON -------- 1.5 1.1 .0 1.5 3.0 Other 1.5 1.5 1.8 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 25 SHAD -------- 1.5 1.4 .0 1.5 3.2 Other 1.4 1.4 1.5 SQL .1 .0 .1 1.6 PL/SQL .0 .0 .0 .0 23 SHAD -------- 1.3 1.2 .0 1.3 3.3 Other 1.2 1.2 1.5 SQL .1 .0 .1 1.7 PL/SQL .0 .0 .0 .1 27 SHAD -------- 1.3 1.1 .0 1.3 2.3 Other 1.1 1.1 1.5 SQL .1 .0 .1 .7 PL/SQL .0 .0 .0 .1 17 J000 -------- 1.2 .1 .0 1.2 2.2 Other 1.2 1.2 1.6 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .5 24 SHAD -------- 1.1 1.1 .0 1.1 1.7 Other 1.0 1.0 1.4 SQL .1 .0 .1 .3 PL/SQL .0 .0 .0 .0 16 SHAD -------- 1.1 .8 .0 1.1 7.8 Other 1.1 1.1 2.8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 5.0 Top Process Memory (by component) DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- ------------------------------------------------------------- Enqueue activity DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- CF-Controlfile Transaction 111 111 0 1 1 717.00 TX-Transaction (row lock contention) 2 2 0 2 0 23.00 ------------------------------------------------------------- Latch Activity DB/Inst: TEST1020/test1020 Snaps: 24-25 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ ASM db client latch 203 0.0 0 0 AWR Alerted Metric Eleme 1,996 0.0 0 0 Consistent RBA 3,222 0.0 0 0 FOB s.o list latch 44 2.3 0.0 0 0 In memory undo latch 55,928 0.0 0.8 0 6,220 0.0 JS queue state obj latch 2,376 0.0 0 0 KMG MMAN ready and start 109 0.0 0 0 KTF sga latch 0 0 107 0.0 KWQP Prop Status 5 0.0 0 0 MQL Tracking Latch 0 0 6 0.0 Memory Management Latch 0 0 109 0.0 OS process 18 0.0 0 0 OS process allocation 122 0.8 0.0 0 0 OS process: request allo 13 0.0 0 0 PL/SQL warning settings 4,419 0.4 0.0 0 0 SGA IO buffer pool latch 2 0.0 0 2 0.0 SQL memory manager latch 3 0.0 0 101 0.0 SQL memory manager worka 21,098 0.0 0 0 Shared B-Tree 10 0.0 0 0 active checkpoint queue 1,826 0.0 0 0 active service list 720 0.0 0 112 0.0 archive control 2 0.0 0 0 begin backup scn array 2 0.0 0 0 cache buffer handles 184 0.0 0 0 cache buffers chains 9,149,672 0.0 0.2 20 7,531 0.0 cache buffers lru chain 32,201 0.1 0.3 1 136 0.0 cache table scan latch 0 0 2 0.0 channel handle pool latc 15 6.7 0.0 0 0 channel operations paren 1,895 0.1 0.0 0 0 checkpoint queue latch 21,978 0.0 0 4,157 0.0 client/application info 167,723 0.1 0.0 0 0 commit callback allocati 2 0.0 0 0 compile environment latc 12 0.0 0 0 dml lock allocation 43,584 0.1 0.0 0 0 dummy allocation 23 0.0 0 0 enqueue hash chains 63,084 0.0 0.2 0 148 0.0 enqueues 16,417 0.0 0.5 0 0 event group latch 7 0.0 0 0 file cache latch 59 0.0 0 0 global KZLD latch for me 7 0.0 0 0 hash table column usage 0 0 491 0.0 hash table modification 2 0.0 0 0 job workq parent latch 0 0 10 0.0 job_queue_processes para 11 0.0 0 0 kks stats 105 0.0 0 0 kokc descriptor allocati 40 0.0 0 0 ksuosstats global area 28 0.0 0 0 ktm global data 1 0.0 0 0 kwqbsn:qsga 8 0.0 0 0 lgwr LWN SCN 3,223 0.0 0 0 Latch Activity DB/Inst: TEST1020/test1020 Snaps: 24-25 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ library cache 115,128 0.3 0.0 0 273 0.0 library cache load lock 20 0.0 0 0 library cache lock 15,538 0.2 0.0 0 0 library cache lock alloc 280 0.0 0 0 library cache pin 64,900 0.2 0.0 0 0 library cache pin alloca 71 0.0 0 0 list of block allocation 90 0.0 0 0 loader state object free 6 0.0 0 0 logminer context allocat 2 0.0 0 0 message pool operations 2 0.0 0 0 messages 12,441 0.1 0.0 0 0 mostly latch-free SCN 3,225 0.0 0.0 0 0 multiblock read objects 4 0.0 0 0 ncodef allocation latch 6 0.0 0 0 object queue header heap 1,836 0.0 0 111 0.0 object queue header oper 52,741 0.0 0.1 0 0 object stats modificatio 4 0.0 0 0 parallel query alloc buf 40 0.0 0 0 parameter list 54 0.0 0 0 parameter table allocati 15 0.0 0 0 post/wait queue 4,893 0.0 0 4,244 0.1 process allocation 13 0.0 0 7 0.0 process group creation 13 0.0 0 0 redo allocation 22,137 0.0 0.0 0 20,218 0.1 redo copy 0 0 20,227 0.1 redo writing 11,736 0.0 0 0 resmgr group change latc 50,086 0.1 0.0 0 0 resmgr:active threads 26 0.0 0 0 resmgr:actses change gro 12 0.0 0 0 resmgr:free threads list 23 0.0 0 0 resmgr:schema config 3 0.0 0 0 row cache objects 150,083 0.4 0.0 0 28 0.0 rules engine rule set st 300 0.0 0 0 sequence cache 5,192 0.0 0 0 session allocation 29,114 0.1 0.0 0 0 session idle bit 30,046 0.0 0.1 0 0 session state list latch 36 5.6 0.0 0 0 session switching 6 0.0 0 0 session timer 112 0.0 0 0 shared pool 2,950 0.0 0.0 0 0 shared pool simulator 34,726 0.0 0.0 0 0 sim partition latch 2 0.0 0 9 0.0 simulator hash latch 302,060 0.0 0 0 simulator lru latch 12,589 0.0 0.0 0 288,921 0.1 sort extent pool 20 0.0 0 0 state object free list 24 0.0 0 0 statistics aggregation 1,120 0.0 0 0 temp lob duration state 1 0.0 0 0 threshold alerts latch 54 0.0 0 0 transaction allocation 58 0.0 0 0 Latch Activity DB/Inst: TEST1020/test1020 Snaps: 24-25 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ transaction branch alloc 112 0.0 0 0 undo global data 31,750 0.0 0.0 0 0 user lock 36 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: TEST1020/test1020 Snaps: 24-25 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 9,149,672 1,935 342 1,595 library cache 115,128 342 4 338 library cache pin 64,900 161 1 160 client/application info 167,723 107 2 105 cache buffers lru chain 32,201 38 11 27 enqueue hash chains 63,084 25 4 21 In memory undo latch 55,928 18 15 3 object queue header operat 52,741 13 1 12 session idle bit 30,046 11 1 10 enqueues 16,417 4 2 2 ------------------------------------------------------------- Latch Miss Sources DB/Inst: TEST1020/test1020 Snaps: 24-25 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- In memory undo latch kticmt: child 0 12 0 In memory undo latch ktiFlush: child 0 4 2 In memory undo latch ktichg: child 0 1 4 cache buffers chains kcbgtcr: fast path 0 247 0 cache buffers chains kcbchg: kslbegin: bufs not 0 36 13 cache buffers chains kcbgtcr: kslbegin excl 0 32 293 cache buffers chains kcbgcur: kslbegin 0 11 3 cache buffers chains kcbnew: new latch again 0 9 1 cache buffers chains kcbget: pin buffer 0 5 3 cache buffers chains kcbrls: kslbegin 0 3 3 cache buffers chains kcbchg: kslbegin: call CR 0 1 2 cache buffers lru chain kcbzgws_1 0 11 11 client/application info kqp327i 0 1 2 client/application info kskirefrattrmap 0 1 0 enqueue hash chains ksqgtl3 0 2 3 enqueue hash chains ksqcmi: get hash chain lat 0 1 0 enqueue hash chains ksqrcl 0 1 1 enqueues ksqgtl2 0 1 0 enqueues ksqies 0 1 2 library cache kglpndl: child: before pro 0 2 71 library cache kglpndl: child: after proc 0 1 0 library cache kglobpn: child: 0 1 1 library cache pin kglpndl 0 1 1 object queue header oper kcbo_switch_cq 0 1 1 session idle bit ksupuc: clear busy 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: TEST1020/test1020 Snaps: 24-25 ->"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 13 0.0 0 4 1 dc_database_links 14 0.0 0 0 1 dc_global_oids 13,607 0.0 0 0 79 dc_histogram_data 336 0.6 0 0 2,976 dc_histogram_defs 230 15.7 0 0 4,421 dc_object_grants 6 0.0 0 0 80 dc_object_ids 13,851 0.2 0 0 1,260 dc_objects 246 15.0 0 0 1,555 dc_profiles 14 0.0 0 0 2 dc_rollback_segments 62 0.0 0 0 25 dc_segments 90 2.2 0 13 1,134 dc_sequences 10 0.0 0 10 33 dc_tablespace_quotas 8 12.5 0 7 3 dc_tablespaces 3,977 0.0 0 0 12 dc_usernames 49 0.0 0 0 49 dc_users 17,700 0.0 0 0 85 outstanding_alerts 24 0.0 0 0 22 ------------------------------------------------------------- Library Cache Activity DB/Inst: TEST1020/test1020 Snaps: 24-25 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 77 0.0 13,520 0.0 0 0 CLUSTER 1 0.0 2 0.0 0 0 INDEX 3 0.0 6 0.0 0 0 SQL AREA 461 0.4 45,498 0.0 1 0 TABLE/PROCEDURE 78 0.0 20,297 0.0 0 0 TRIGGER 4 0.0 40 0.0 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: TEST1020/test1020 Snaps: 24-25 -> * 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: TEST1020/test1020 End Snap: 25 Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill Size (M) Size Factor Count Time (s) Count Time (s) ------------ ------------ --------- --------- ----------- ----------- .0 1.0 0 0 0 0 .0 2.0 0 0 0 0 .0 3.0 0 0 0 0 .0 4.0 0 0 0 0 .0 5.0 0 0 0 0 .0 6.0 0 0 0 0 .0 7.0 0 0 0 0 .0 8.0 0 0 0 0 .0 9.0 0 0 0 0 .0 10.0 0 0 0 0 .0 11.0 0 0 0 0 .0 12.0 0 0 0 0 .0 13.0 0 0 0 0 .0 14.0 0 0 0 0 .0 15.0 0 0 0 0 .0 16.0 0 0 0 0 .0 17.0 0 0 0 0 .0 18.0 0 0 0 0 .0 19.0 0 0 0 0 .0 20.0 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: TEST1020/test1020 End Snap: 25 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 88 .5 24 1,790 ####### .9 22,171 53.6 760,916 108 .6 40 3,534 ####### .9 8,237 19.9 766,010 128 .8 59 5,229 ####### 1.0 2,002 4.8 766,990 148 .9 78 7,141 ####### 1.0 422 1.0 767,622 168 1.0 97 8,663 ####### 1.0 414 1.0 768,013 188 1.1 116 10,552 ####### 1.0 412 1.0 768,084 208 1.2 121 11,523 ####### 1.0 412 1.0 768,090 228 1.4 121 11,523 ####### 1.0 412 1.0 768,090 248 1.5 121 11,523 ####### 1.0 412 1.0 768,090 268 1.6 121 11,523 ####### 1.0 412 1.0 768,090 288 1.7 121 11,523 ####### 1.0 412 1.0 768,090 308 1.8 121 11,523 ####### 1.0 412 1.0 768,090 328 2.0 121 11,523 ####### 1.0 412 1.0 768,090 348 2.1 121 11,523 ####### 1.0 412 1.0 768,090 ------------------------------------------------------------- SGA Target Advisory DB/Inst: TEST1020/test1020 End Snap: 25 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 292 .5 7,176 1.3 108,217 438 .8 6,051 1.1 80,623 584 1.0 5,708 1.0 72,217 730 1.3 5,700 1.0 72,058 876 1.5 5,700 1.0 72,058 1,022 1.8 5,700 1.0 72,058 1,168 2.0 5,700 1.0 72,058 ------------------------------------------------------------- SGA Memory Summary DB/Inst: TEST1020/test1020 Snaps: 24-25 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 415,236,096 Fixed Size 1,298,160 Redo Buffers 7,090,176 Variable Size 188,743,952 -------------------- -------------------- sum 612,368,384 ------------------------------------------------------------- SGA breakdown difference DB/Inst: TEST1020/test1020 Snaps: 24-25 -> 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 4.0 4.0 0.00 large PX msg pool .9 .9 0.00 large free memory 3.1 3.1 0.00 shared ASH buffers 4.0 4.0 0.00 shared CCursor 12.0 11.9 -0.66 shared Heap0: KGL 5.2 5.1 -2.79 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 6.6 6.7 0.41 shared KQR M PO 3.6 3.6 0.92 shared KSFD SGA I/O b 3.8 3.8 0.00 shared PCursor 5.3 5.2 -2.49 shared PL/SQL DIANA 2.3 2.3 0.00 shared PL/SQL MPCODE 6.3 6.3 0.00 shared XDB Schema Cac 4.6 4.6 0.00 shared free memory 19.0 19.5 2.65 shared kglsim hash table bkts 2.0 2.0 0.00 shared library cache 9.1 9.1 -0.10 shared row cache 3.6 3.6 0.00 shared sql area 43.9 43.8 -0.29 shared sql area:PLSQL 2.2 2.2 0.00 stream free memory 4.0 4.0 0.00 buffer_cache 396.0 396.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 6.8 6.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: TEST1020/test1020 Snaps: 24-25 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 21.24 21.25 .07 Cursor to Parent ratio: 1.32 1.32 .35 Total Cursors: 2,436 2,430 -.25 Total Parents: 1,849 1,838 -.60 ------------------------------------------------------------- init.ora Parameters DB/Inst: TEST1020/test1020 Snaps: 24-25 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 0 audit_file_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\TE background_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\TE compatible 10.2.0.3.0 control_files C:\ORACLE\PRODUCT\10.2.0\ORADATA\ core_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\TE db_block_size 8192 db_domain db_file_multiblock_read_count 16 db_name TEST1020 db_recovery_file_dest C:\oracle\product\10.2.0\flash_re db_recovery_file_dest_size 2147483648 dispatchers (PROTOCOL=TCP) (SERVICE=TEST1020X fast_start_mttr_target 5 job_queue_processes 10 log_archive_dest_1 LOCATION="C:\ORACLE\PRODUCT\10.2. nls_length_semantics BYTE open_cursors 300 pga_aggregate_target 203423744 pre_11g_enable_capture TRUE processes 150 remote_login_passwordfile EXCLUSIVE resource_manager_plan sga_target 612368384 statistics_level TYPICAL undo_management AUTO undo_retention 900 undo_tablespace UNDOTBS1 user_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\TE ------------------------------------------------------------- End of Report ( c:\SOE4.txt )