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: 22 12-Aug-08 21:41:46 24 15.9 End Snap: 23 12-Aug-08 21:47:43 23 13.4 Elapsed: 5.95 (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: 24,091.71 3,179.57 Logical reads: 5,741.40 757.74 Block changes: 144.31 19.05 Physical reads: 29.24 3.86 Physical writes: 15.85 2.09 User calls: 13.40 1.77 Parses: 12.05 1.59 Hard parses: 0.24 0.03 Sorts: 6.48 0.86 Logons: 0.03 0.00 Executes: 59.73 7.88 Transactions: 7.58 % Blocks changed per Read: 2.51 Recursive Call %: 92.34 Rollback per transaction %: 0.15 Rows per Sort: 603.08 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.49 In-memory Sort %: 100.00 Library Hit %: 99.40 Soft Parse %: 98.05 Execute to Parse %: 79.83 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 9.39 % Non-Parse CPU: 99.60 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 83.62 85.64 % SQL with executions>1: 83.11 83.64 % Memory for SQL w/exec>1: 88.09 88.83 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ db file sequential read 4,818 175 36 46.3 CPU time 57 15.2 db file parallel write 1,085 42 39 11.2 log file sync 821 38 46 10.0 log file parallel write 2,702 28 10 7.5 ------------------------------------------------------------- Host CPU (CPUs: 2) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 44.89 10.87 44.23 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 8.34 % of busy CPU for Instance: 14.96 %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): 35.0 32.6 % Host Mem used for SGA+PGA: 30.3 30.1 ------------------------------------------------------------- Time Model System Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 282.9 88.0 DB CPU 57.4 17.9 PL/SQL execution elapsed time 7.8 2.4 parse time elapsed 6.2 1.9 hard parse elapsed time 4.6 1.4 hard parse (sharing criteria) elaps 2.3 .7 hard parse (bind mismatch) elapsed 0.6 .2 PL/SQL compilation elapsed time 0.4 .1 connection management call elapsed 0.0 .0 repeated bind elapsed time 0.0 .0 sequence load elapsed time 0.0 .0 DB time 321.5 background elapsed time 249.4 background cpu time 2.3 ------------------------------------------------------------- Wait Events DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 4,818 0 175 36 1.8 db file parallel write 1,085 0 42 39 0.4 log file sync 821 0 38 46 0.3 log file parallel write 2,702 0 28 10 1.0 db file scattered read 470 0 23 50 0.2 control file sequential read 2,053 0 7 3 0.8 control file parallel write 116 0 5 42 0.0 latch: cache buffers chains 4 0 2 447 0.0 LGWR wait for redo copy 26 38 0 4 0.0 direct path write 1 0 0 96 0.0 latch: shared pool 1 0 0 50 0.0 SQL*Net more data to client 18 0 0 1 0.0 SQL*Net break/reset to client 34 0 0 0 0.0 latch: cache buffers lru chain 1 0 0 9 0.0 SQL*Net message from client 2,828 0 3,206 1134 1.0 virtual circuit status 12 100 360 30010 0.0 wait for unread message on broadc 352 100 357 1014 0.1 jobq slave wait 119 95 356 2996 0.0 Streams AQ: waiting for messages 74 100 355 4803 0.0 SQL*Net message to client 2,827 0 0 0 1.0 ------------------------------------------------------------- Background Wait Events DB/Inst: TEST1020/test1020 Snaps: 22-23 -> %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 parallel write 1,085 0 42 39 0.4 log file parallel write 2,702 0 28 10 1.0 control file parallel write 116 0 5 42 0.0 control file sequential read 156 0 4 26 0.1 latch: cache buffers chains 3 0 2 580 0.0 events in waitclass Other 29 34 0 4 0.0 latch: shared pool 1 0 0 50 0.0 rdbms ipc message 3,688 29 4,412 1196 1.4 pmon timer 125 100 358 2866 0.0 smon timer 1 0 297 ###### 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 26 38.5 3.8 23.1 11.5 23.1 SQL*Net break/reset to cli 34 88.2 11.8 SQL*Net more data to clien 18 38.9 61.1 control file parallel writ 116 18.1 10.3 11.2 20.7 16.4 23.3 control file sequential re 2053 89.4 1.2 .6 1.0 2.1 3.0 2.6 db file parallel write 1085 34.8 13.2 9.4 6.9 6.5 8.7 20.4 .1 db file scattered read 470 .2 4.5 5.3 1.5 5.5 18.7 64.3 db file sequential read 4818 3.3 1.3 1.6 7.1 19.1 29.4 38.1 .0 direct path write 1 100.0 latch free 1 100.0 latch: cache buffers chain 4 100.0 latch: cache buffers lru c 1 100.0 latch: library cache 3 100.0 latch: messages 1 100.0 latch: shared pool 1 100.0 log file parallel write 2702 84.2 1.9 1.0 .5 1.8 5.3 5.1 .1 log file sync 821 24.4 3.5 3.3 3.0 8.0 30.7 27.0 SQL*Net message from clien 2828 21.0 52.7 6.8 5.9 6.2 1.4 1.8 4.1 SQL*Net message to client 2827 100.0 SQL*Net more data from cli 1 100.0 Streams AQ: waiting for me 74 2.7 1.4 95.9 dispatcher timer 6 100.0 jobq slave wait 119 100.0 pmon timer 125 4.8 95.2 rdbms ipc message 3687 34.7 1.7 1.1 .4 3.3 11.0 29.3 18.5 smon timer 1 100.0 virtual circuit status 12 100.0 wait for unread message on 352 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 57 -> Captured SQL accounts for 169.4% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 38.69 518 0.07 67.4 122.10 1,864,666 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 27.46 1,571 0.02 47.8 40.25 1,803,914 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 10.47 4 2.62 18.2 44.68 19,504 4101483272 Module: java.exe BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; 3.67 528 0.01 6.4 33.55 128,804 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 2.11 1 2.11 3.7 3.13 2,852 2522684317 Module: sqlplus.exe BEGIN statspack.snap; END; 1.71 1,784 0.00 3.0 24.30 30,966 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.69 1,593 0.00 2.9 1.76 122,558 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 1.20 1 1.20 2.1 18.55 4,913 3089015780 Module: java.exe select min(customer_id), max(customer_id) from customers 1.16 555 0.00 2.0 43.86 6,872 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 1.12 2 0.56 2.0 5.74 5,244 3479051921 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 0.79 48 0.02 1.4 1.05 0 3872095438 Module: Realtime Connection select begin_time, wait_class#, (time_waited)/(intsize_cs SQL ordered by CPU DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 57 -> Captured SQL accounts for 169.4% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- ec/100) from v$waitclassmetric union all select begin_time, -1, value from v$sysmetric where metric_name = 'CPU Usage Per Sec' a nd group_id = 2 order by begin_time, wait_class# 0.74 1,798 0.00 1.3 82.60 5,436 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 0.65 197 0.00 1.1 30.06 6,335 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 0.61 518 0.00 1.1 23.99 8,475 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 Elapsed DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 321 -> Captured SQL accounts for 170.4% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 122.10 518 0.24 38.0 38.69 3,406 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 82.60 1,798 0.05 25.7 0.74 1,523 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 44.68 4 11.17 13.9 10.47 50 4101483272 Module: java.exe BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; 43.86 555 0.08 13.6 1.16 871 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 40.25 1,571 0.03 12.5 27.46 1,118 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 33.55 528 0.06 10.4 3.67 523 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 30.06 197 0.15 9.4 0.65 963 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 29.55 197 0.15 9.2 0.33 963 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 ))) 24.30 1,784 0.01 7.6 1.71 991 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 23.99 518 0.05 7.5 0.61 726 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 Elapsed DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 321 -> Captured SQL accounts for 170.4% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 18.55 1 18.55 5.8 1.20 4,561 3089015780 Module: java.exe select min(customer_id), max(customer_id) from customers 12.48 555 0.02 3.9 0.27 301 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 5.74 2 2.87 1.8 1.12 4 3479051921 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 3.51 1,798 0.00 1.1 0.48 72 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) ------------------------------------------------------------- SQL ordered by Gets DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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: 2,049,681 -> Captured SQL accounts for 100.9% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 1,864,666 518 3,599.7 91.0 38.69 122.10 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 1,803,914 1,571 1,148.3 88.0 27.46 40.25 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 128,804 528 243.9 6.3 3.67 33.55 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 122,558 1,593 76.9 6.0 1.69 1.76 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 30,966 1,784 17.4 1.5 1.71 24.30 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: 22-23 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,438 -> Captured SQL accounts for 102.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 4,561 1 4,561.0 43.7 1.20 18.55 3089015780 Module: java.exe select min(customer_id), max(customer_id) from customers 3,406 518 6.6 32.6 38.69 122.10 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 1,523 1,798 0.8 14.6 0.74 82.60 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,118 1,571 0.7 10.7 27.46 40.25 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 991 1,784 0.6 9.5 1.71 24.30 2698772428 Module: New Order INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT _PRICE, QUANTITY) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 963 197 4.9 9.2 0.65 30.06 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 963 197 4.9 9.2 0.33 29.55 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 ))) 871 555 1.6 8.3 1.16 43.86 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 726 518 1.4 7.0 0.61 23.99 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 523 528 1.0 5.0 3.67 33.55 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 301 555 0.5 2.9 0.27 12.48 287228808 Module: Browse and Update Orders SQL ordered by Reads DB/Inst: TEST1020/test1020 Snaps: 22-23 -> End Disk Reads Threshold: 1000 Total Disk Reads: 10,438 -> Captured SQL accounts for 102.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- SELECT ORDER_ID, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TO TAL, SALES_REP_ID, PROMOTION_ID FROM ORDERS WHERE CUSTOMER_ID = :B1 139 53 2.6 1.3 0.13 3.09 1087823723 SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) in dex(t2) */ t2.snap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLI DTAB t1, WRH$_SQL_BIND_METADATA t2 WHERE t2.dbid(+) = :dbi d AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE AND t2.POSITION(+) = 1) W HERE nvl(snap_id, 0) < :snap_id 118 54 2.2 1.1 0.23 2.75 3379029892 SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) in dex(t2) */ t2.snap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLI DTAB t1, WRH$_SQLTEXT t2 WHERE t2.dbid(+) = :dbid AND t2. SQL_ID(+) = t1.SQLID_KEWRSIE) WHERE nvl(snap_id, 0) < :snap_id ------------------------------------------------------------- SQL ordered by Executions DB/Inst: TEST1020/test1020 Snaps: 22-23 -> End Executions Threshold: 100 Total Executions: 21,325 -> 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,800 1,800 1.0 0.00 0.00 2293415029 Module: emagent.exe SELECT SYSDATE FROM DUAL 1,798 1,798 1.0 0.00 0.00 1809837244 Module: New Order INSERT INTO LOGON VALUES (:B2 , :B1 ) 1,798 1,795 1.0 0.00 0.05 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,784 1,784 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 1,784 1,784 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) 1,593 47,790 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 1,571 157,100 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 555 54 0.1 0.00 0.02 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 555 555 1.0 0.00 0.08 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 528 528 1.0 0.01 0.06 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; SQL ordered by Executions DB/Inst: TEST1020/test1020 Snaps: 22-23 -> End Executions Threshold: 100 Total Executions: 21,325 -> 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 ------------ --------------- ---------------- ----------- ---------- ---------- 518 518 1.0 0.07 0.24 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 518 518 1.0 0.00 0.05 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 518 1,784 3.4 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 518 518 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 266 219 0.8 0.00 0.00 3665763022 update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre ds = range_preds + decode(bitand(:flag,8),0,0,1), 216 216 1.0 0.00 0.00 1667689875 Module: EM_PING SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 214 85 0.4 0.00 0.00 2803285 update sys.mon_mods$ set inserts = inserts + :ins, updates = upd ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop _segments + :dropseg, timestamp = :time where obj# = :objn 214 0 0.0 0.00 0.00 2396279102 lock table sys.mon_mods$ in exclusive mode nowait ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: TEST1020/test1020 Snaps: 22-23 -> End Parse Calls Threshold: 1000 Total Parse Calls: 4,301 -> Captured SQL accounts for 94.7% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 555 555 12.90 727831036 BEGIN :1 := orderentry.browseandupdateorders(:2,:3,:4); END; 528 528 12.28 1094891389 BEGIN :1 := orderentry.browseproducts(:2,:3,:4); END; 518 518 12.04 152401265 Module: java.exe BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 214 214 4.98 2803285 update sys.mon_mods$ set inserts = inserts + :ins, updates = upd ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop _segments + :dropseg, timestamp = :time where obj# = :objn 214 214 4.98 2396279102 lock table sys.mon_mods$ in exclusive mode nowait 197 197 4.58 2445712067 BEGIN :1 := orderentry.newcustomer(:2,:3,:4,:5,:6,:7); END; 165 165 3.84 1348827743 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 129 129 3.00 3447189756 insert into sys.mon_mods$ (obj#, inserts, updates, deletes, time stamp, flags, drop_segments) values (:1, :2, :3, :4, :5, :6, :7) 109 47 2.53 2322535947 insert into sys.col_usage$ (obj#, intcol#, equality_preds, equij oin_preds, nonequijoin_preds, range_preds, like_preds, null_pred s, timestamp) values ( :objn, :coln, decode(bitand(:flag,1), 0,0,1), decode(bitand(:flag,2),0,0,1), decode(bitand(:flag,4 ),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag 109 109 2.53 2554034351 lock table sys.col_usage$ in exclusive mode nowait 109 266 2.53 3665763022 update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre ds = range_preds + decode(bitand(:flag,8),0,0,1), 105 105 2.44 2757634508 BEGIN :1 := orderentry.processorders(:2,:3); END; SQL ordered by Parse Calls DB/Inst: TEST1020/test1020 Snaps: 22-23 -> End Parse Calls Threshold: 1000 Total Parse Calls: 4,301 -> Captured SQL accounts for 94.7% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 104 104 2.42 1270390304 SELECT COUNT(*) as cnt FROM WRM$_SNAP_ERROR e , X$KEHSQT x W HERE e.dbid = :dbid AND e.instance_number = :inst_num AND e.snap_id in (:bid, :eid) AND e.table_name = x.name AND x .ver_type = :edge_type AND x.critical = 'Y' 104 104 2.42 2515148281 SELECT dbin.db_name, dbin.instance_name, dbin.version, CA SE WHEN s1.startup_time = s2.startup_time THEN 0 ELS E 1 END as bounce, CAST(s1.end_interval_time AS DATE) as begin_time, CAST(s2.end_interval_time AS DATE) as end_tim e, ROUND((cast( (case when s2.end_interval 88 88 2.05 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.93 2142234931 SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual 54 54 1.26 3379029892 SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) in dex(t2) */ t2.snap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLI DTAB t1, WRH$_SQLTEXT t2 WHERE t2.dbid(+) = :dbid AND t2. SQL_ID(+) = t1.SQLID_KEWRSIE) WHERE nvl(snap_id, 0) < :snap_id 53 53 1.23 1087823723 SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) in dex(t2) */ t2.snap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLI DTAB t1, WRH$_SQL_BIND_METADATA t2 WHERE t2.dbid(+) = :dbi d AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE AND t2.POSITION(+) = 1) W HERE nvl(snap_id, 0) < :snap_id ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 5,725 16.0 2.1 CPU used when call started 5,350 15.0 2.0 CR blocks created 65 0.2 0.0 Cached Commit SCN referenced 1,422,288 3,984.0 525.8 Commit SCN cached 1 0.0 0.0 DB time 139,878 391.8 51.7 DBWR checkpoint buffers written 5,543 15.5 2.1 DBWR checkpoints 0 0.0 0.0 DBWR thread checkpoint buffers wr 0 0.0 0.0 DBWR transaction table writes 57 0.2 0.0 DBWR undo block writes 466 1.3 0.2 IMU CR rollbacks 30 0.1 0.0 IMU Flushes 188 0.5 0.1 IMU Redo allocation size 473,556 1,326.5 175.1 IMU commits 2,600 7.3 1.0 IMU contention 1 0.0 0.0 IMU ktichg flush 1 0.0 0.0 IMU recursive-transaction flush 7 0.0 0.0 IMU undo allocation size 7,103,388 19,897.5 2,626.0 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 2,817 7.9 1.0 active txn count during cleanout 134 0.4 0.1 application wait time 1 0.0 0.0 background timeouts 1,127 3.2 0.4 buffer is not pinned count 34,818 97.5 12.9 buffer is pinned count 105,983 296.9 39.2 bytes received via SQL*Net from c 923,780 2,587.6 341.5 bytes sent via SQL*Net to client 677,449 1,897.6 250.4 calls to get snapshot scn: kcmgss 110,133 308.5 40.7 calls to kcmgas 3,556 10.0 1.3 calls to kcmgcs 98 0.3 0.0 change write time 164 0.5 0.1 cleanout - number of ktugct calls 171 0.5 0.1 cleanouts and rollbacks - consist 1 0.0 0.0 cleanouts only - consistent read 7 0.0 0.0 cluster key scan block gets 593 1.7 0.2 cluster key scans 430 1.2 0.2 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 4 0.0 0.0 commit batch/immediate requested 4 0.0 0.0 commit cleanout failures: block l 1 0.0 0.0 commit cleanout failures: buffer 1 0.0 0.0 commit cleanout failures: callbac 14 0.0 0.0 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 13,025 36.5 4.8 commit cleanouts successfully com 13,009 36.4 4.8 commit immediate performed 4 0.0 0.0 commit immediate requested 4 0.0 0.0 commit txn count during cleanout 104 0.3 0.0 concurrency wait time 184 0.5 0.1 consistent changes 87 0.2 0.0 consistent gets 1,983,037 5,554.7 733.1 consistent gets - examination 37,950 106.3 14.0 Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ consistent gets direct 1 0.0 0.0 consistent gets from cache 1,983,036 5,554.7 733.1 cursor authentications 70 0.2 0.0 data blocks consistent reads - un 61 0.2 0.0 db block changes 51,520 144.3 19.1 db block gets 66,644 186.7 24.6 db block gets direct 1 0.0 0.0 db block gets from cache 66,643 186.7 24.6 deferred (CURRENT) block cleanout 9,555 26.8 3.5 dirty buffers inspected 112 0.3 0.0 enqueue conversions 638 1.8 0.2 enqueue releases 16,905 47.4 6.3 enqueue requests 16,905 47.4 6.3 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 execute count 21,325 59.7 7.9 failed probes on index block recl 0 0.0 0.0 free buffer inspected 10,902 30.5 4.0 free buffer requested 10,968 30.7 4.1 heap block compress 35 0.1 0.0 hot buffers moved to head of LRU 9,990 28.0 3.7 immediate (CR) block cleanout app 8 0.0 0.0 immediate (CURRENT) block cleanou 319 0.9 0.1 index crx upgrade (found) 0 0.0 0.0 index crx upgrade (positioned) 826 2.3 0.3 index fast full scans (full) 32 0.1 0.0 index fetch by key 65,364 183.1 24.2 index scans kdiixs1 8,730 24.5 3.2 leaf node 90-10 splits 32 0.1 0.0 leaf node splits 73 0.2 0.0 lob reads 0 0.0 0.0 lob writes 108 0.3 0.0 lob writes unaligned 108 0.3 0.0 logons cumulative 9 0.0 0.0 messages received 3,791 10.6 1.4 messages sent 3,791 10.6 1.4 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 1,855,109 5,196.4 685.8 opened cursors cumulative 4,579 12.8 1.7 parse count (failures) 0 0.0 0.0 parse count (hard) 84 0.2 0.0 parse count (total) 4,301 12.1 1.6 parse time cpu 23 0.1 0.0 parse time elapsed 245 0.7 0.1 physical read IO requests 5,269 14.8 2.0 physical read bytes 85,508,096 239,518.5 31,611.1 physical read total IO requests 7,342 20.6 2.7 physical read total bytes 119,308,288 334,196.9 44,106.6 physical read total multi block r 470 1.3 0.2 physical reads 10,438 29.2 3.9 physical reads cache 10,437 29.2 3.9 physical reads cache prefetch 5,169 14.5 1.9 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: 22-23 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical write IO requests 3,664 10.3 1.4 physical write bytes 46,342,144 129,809.9 17,132.0 physical write total IO requests 6,718 18.8 2.5 physical write total bytes 62,184,448 174,186.1 22,988.7 physical write total multi block 3,083 8.6 1.1 physical writes 5,657 15.9 2.1 physical writes direct 1 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 5,656 15.8 2.1 physical writes non checkpoint 3,161 8.9 1.2 prefetch warmup blocks aged out b 233 0.7 0.1 prefetched blocks aged out before 11 0.0 0.0 process last non-idle time 642 1.8 0.2 recovery blocks read 0 0.0 0.0 recursive calls 57,625 161.4 21.3 recursive cpu usage 4,732 13.3 1.8 redo blocks read for recovery 0 0.0 0.0 redo blocks written 19,174 53.7 7.1 redo entries 10,282 28.8 3.8 redo ordering marks 275 0.8 0.1 redo size 8,600,740 24,091.7 3,179.6 redo subscn max counts 742 2.1 0.3 redo synch time 3,787 10.6 1.4 redo synch writes 1,935 5.4 0.7 redo wastage 851,544 2,385.3 314.8 redo write time 2,911 8.2 1.1 redo writer latching time 16 0.0 0.0 redo writes 2,702 7.6 1.0 rollback changes - undo records a 5 0.0 0.0 rollbacks only - consistent read 59 0.2 0.0 rows fetched via callback 7,920 22.2 2.9 session connect time 0 0.0 0.0 session cursor cache hits 3,565 10.0 1.3 session logical reads 2,049,681 5,741.4 757.7 session pga memory 4,937,636 13,830.9 1,825.4 session pga memory max 12,801,956 35,859.8 4,732.7 session uga memory 25,768,612,396 72,180,987.1 9,526,289.2 session uga memory max 14,736,700 41,279.3 5,448.0 shared hash latch upgrades - no w 3,205 9.0 1.2 shared hash latch upgrades - wait 0 0.0 0.0 sorts (memory) 2,313 6.5 0.9 sorts (rows) 1,394,916 3,907.3 515.7 sql area evicted 0 0.0 0.0 sql area purged 0 0.0 0.0 summed dirty queue length 1,644 4.6 0.6 switch current to new buffer 15 0.0 0.0 table fetch by rowid 18,383 51.5 6.8 table fetch continued row 43 0.1 0.0 table scan blocks gotten 1,835,641 5,141.9 678.6 table scan rows gotten 14,966,368 41,922.6 5,532.9 table scans (long tables) 0 0.0 0.0 table scans (short tables) 28,547 80.0 10.6 total number of times SMON posted 1 0.0 0.0 transaction rollbacks 4 0.0 0.0 undo change vector size 2,888,504 8,091.1 1,067.8 Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ user I/O wait time 19,801 55.5 7.3 user calls 4,783 13.4 1.8 user commits 2,701 7.6 1.0 user rollbacks 4 0.0 0.0 workarea executions - optimal 5,199 14.6 1.9 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 22 0.1 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 24 23 opened cursors current 382 309 session cursor cache count 20,123 20,266 ------------------------------------------------------------- Instance Activity Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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: 22-23 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 39,926 IDLE_TIME 31,667 SYS_TIME 7,785 USER_TIME 32,141 VM_IN_BYTES 191,528,960 VM_OUT_BYTES 58,474,496 PHYSICAL_MEMORY_BYTES 2,145,058,816 NUM_CPUS 2 NUM_CPU_CORES 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 ->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 3,172 9 30.4 2.3 2,555 7 0 0.0 SOE 2,006 6 49.1 1.5 404 1 0 0.0 SYSAUX 46 0 28.7 1.0 539 2 0 0.0 UNDOTBS1 0 0 0.0 132 0 0 0.0 SYSTEM 48 0 33.8 1.0 34 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 ->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 2,006 6 49.1 ### 1.5 404 1 0 SOEINDEX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOEINDEX.D 3,172 9 30.4 ### 2.3 2,555 7 0 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSAUX01.D 46 0 28.7 ### 1.0 539 2 0 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSTEM01.D 48 0 33.8 ### 1.0 34 0 0 UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\UNDOTBS01. 0 0 132 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 ->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 83 7 26 133 597 1,058 SOEINDEX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SOEINDEX.D 129 70 311 764 775 755 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSAUX01.D 5 0 0 9 17 11 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\SYSTEM01.D 3 2 3 9 19 12 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 99 2,049,657 10,433 5,656 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 32 456 2811 15288 1843200 15288 E 37 35 957 4518 34200 1843200 34200 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: TEST1020/test1020 End Snap: 23 -> 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.1 140 3,457 77.1 D 72 .2 9 1.7 109 2,469 55.1 D 108 .3 13 1.5 97 2,065 46.1 D 144 .4 18 1.4 91 1,857 41.4 D 180 .5 22 1.3 84 1,639 36.6 D 216 .5 27 1.2 78 1,455 32.5 D 252 .6 31 1.1 74 1,328 29.6 D 288 .7 36 1.1 70 1,200 26.8 D 324 .8 40 1.0 67 1,092 24.4 D 360 .9 45 1.0 67 1,078 24.1 D 396 1.0 49 1.0 66 1,062 23.7 D 432 1.1 54 1.0 66 1,059 23.6 D 468 1.2 58 1.0 66 1,059 23.6 D 504 1.3 63 1.0 66 1,059 23.6 D 540 1.4 67 1.0 66 1,059 23.6 D 576 1.5 72 1.0 66 1,059 23.6 D 612 1.5 76 1.0 66 1,059 23.6 D 648 1.6 81 1.0 66 1,059 23.6 D 684 1.7 85 1.0 66 1,059 23.6 D 720 1.8 90 1.0 66 1,059 23.6 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 2,369 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 155 35.0 0.0 .0 .0 .0 39,731 E 194 157 32.6 0.0 .0 .0 .0 39,731 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: TEST1020/test1020 Snaps: 22-23 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 433 433 0 0 64K 128K 6 6 0 0 128K 256K 4 4 0 0 256K 512K 3,142 3,142 0 0 512K 1024K 1,616 1,616 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: TEST1020/test1020 End Snap: 23 -> 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 3,712.7 98.9 97.0 31 49 0.3 3,712.7 0.0 100.0 0 97 0.5 3,712.7 0.0 100.0 0 146 0.8 3,712.7 0.0 100.0 0 194 1.0 3,712.7 0.0 100.0 0 233 1.2 3,712.7 0.0 100.0 0 272 1.4 3,712.7 0.0 100.0 0 310 1.6 3,712.7 0.0 100.0 0 349 1.8 3,712.7 0.0 100.0 0 388 2.0 3,712.7 0.0 100.0 0 582 3.0 3,712.7 0.0 100.0 0 776 4.0 3,712.7 0.0 100.0 0 1,164 6.0 3,712.7 0.0 100.0 0 1,552 8.0 3,712.7 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 -------- 35.3 21.8 .0 1.4 1.7 9 10 26 Other 33.9 1.3 1.7 9 10 26 SQL .9 .3 .0 .0 0 4 16 PL/SQL .5 .2 .0 .0 0 0 24 E -------- 32.9 20.0 .0 1.3 1.7 9 10 25 Other 31.8 1.3 1.7 9 10 25 SQL .8 .3 .0 .0 0 5 15 PL/SQL .4 .2 .0 .0 0 0 23 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 21 SHAD -------- 3.3 2.3 .0 3.3 5.7 Other 3.2 3.2 4.2 SQL .1 .1 .1 1.5 PL/SQL .0 .0 .0 .0 15 SHAD -------- 2.2 1.4 .0 2.2 5.2 Other 2.0 2.0 2.0 SQL .1 .0 .1 3.5 PL/SQL .1 .0 .1 .1 25 SHAD -------- 1.6 1.5 .0 1.6 3.2 Other 1.5 1.5 1.5 SQL .1 .0 .1 1.6 PL/SQL .0 .0 .0 .0 20 SHAD -------- 1.5 1.1 .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.2 .0 1.5 3.0 Other 1.5 1.5 1.8 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 26 SHAD -------- 1.4 1.2 .0 1.4 2.3 Other 1.3 1.3 1.5 SQL .1 .0 .1 .8 PL/SQL .1 .0 .1 .1 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 8 SMON -------- 1.2 .5 .0 1.2 1.8 Other 1.2 1.2 1.6 SQL .0 .0 .0 .3 PL/SQL .0 .0 .0 .0 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 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: 22-23 -> 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 21 SHAD -------- 3.1 2.3 .0 3.1 5.7 Other 2.9 2.9 4.2 SQL .1 .1 .1 1.5 PL/SQL .0 .0 .0 .0 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 8 SMON -------- 1.3 .5 .0 1.3 1.8 Other 1.3 1.3 1.6 SQL .0 .0 .0 .3 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 ------------------------------------------------------------- Undo Segment Summary DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 ---- ---------- --------------- -------- ---------- --------- ----- ----------- 1 .5 3,238 0 3 15/15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 12-Aug 21:48 494 3,238 0 3 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: TEST1020/test1020 Snaps: 22-23 ->"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 233 0.0 0 0 AWR Alerted Metric Eleme 2,604 0.0 0 0 Consistent RBA 2,703 0.0 0 0 FOB s.o list latch 26 0.0 0 0 In memory undo latch 25,783 0.0 0 2,890 0.0 JS mem alloc latch 2 0.0 0 0 JS queue access latch 2 0.0 0 0 JS queue state obj latch 2,556 0.0 0 0 KMG MMAN ready and start 119 0.0 0 0 KTF sga latch 0 0 113 0.0 KWQP Prop Status 5 0.0 0 0 MQL Tracking Latch 0 0 7 0.0 Memory Management Latch 0 0 119 0.0 OS process 12 0.0 0 0 OS process allocation 126 0.0 0 0 OS process: request allo 7 0.0 0 0 PL/SQL warning settings 1,974 0.0 0 0 SGA IO buffer pool latch 470 0.0 0 470 0.0 SQL memory manager latch 3 0.0 0 116 0.0 SQL memory manager worka 14,775 0.0 0 0 Shared B-Tree 10 0.0 0 0 active checkpoint queue 1,202 0.0 0 0 active service list 758 0.1 1.0 0 125 0.0 archive control 4 0.0 0 0 begin backup scn array 1 0.0 0 0 cache buffer handles 144 0.0 0 0 cache buffers chains 4,252,214 0.0 1.0 2 16,126 0.0 cache buffers lru chain 22,462 0.0 1.0 0 88 0.0 cache table scan latch 0 0 470 0.0 channel handle pool latc 9 0.0 0 0 channel operations paren 2,084 0.0 0 0 checkpoint queue latch 12,716 0.0 0 2,463 0.0 client/application info 73,815 0.0 0 0 commit callback allocati 2 0.0 0 0 compile environment latc 9 0.0 0 0 dml lock allocation 21,152 0.0 0 0 dummy allocation 19 0.0 0 0 enqueue hash chains 34,440 0.0 0 0 enqueues 11,133 0.0 0 0 event group latch 3 0.0 0 0 file cache latch 56 0.0 0 0 global KZLD latch for me 3 0.0 0 0 hash table column usage 117 0.0 0 4,432 0.0 hash table modification 79 0.0 0 0 job workq parent latch 0 0 12 0.0 job_queue_processes para 12 0.0 0 0 kks stats 441 0.0 0 0 kokc descriptor allocati 16 0.0 0 0 ksuosstats global area 30 0.0 0 0 ktm global data 1 0.0 0 0 Latch Activity DB/Inst: TEST1020/test1020 Snaps: 22-23 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ kwqbsn:qsga 8 0.0 0 0 lgwr LWN SCN 2,715 0.0 0 0 library cache 146,485 0.0 0.1 0 0 library cache load lock 156 0.0 0 0 library cache lock 14,831 0.0 0 0 library cache lock alloc 286 0.0 0 0 library cache pin 33,896 0.0 0 0 library cache pin alloca 75 0.0 0 0 list of block allocation 77 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,824 0.0 0.3 0 0 mostly latch-free SCN 2,717 0.1 0.0 0 0 multiblock read objects 944 0.0 0 0 ncodef allocation latch 6 0.0 0 0 object queue header heap 1,363 0.0 0 182 0.0 object queue header oper 39,113 0.0 0 0 object stats modificatio 162 0.0 0 0 parallel query alloc buf 48 0.0 0 0 parameter list 35 0.0 0 0 parameter table allocati 11 0.0 0 0 post/wait queue 1,308 0.0 0 833 0.0 process allocation 7 0.0 0 3 0.0 process group creation 7 0.0 0 0 redo allocation 13,911 0.0 0.0 0 10,275 0.0 redo copy 0 0 10,275 0.3 redo writing 9,561 0.0 0 0 resmgr group change latc 22,045 0.0 0 0 resmgr:active threads 22 0.0 0 0 resmgr:actses change gro 9 0.0 0 0 resmgr:free threads list 19 0.0 0 0 resmgr:schema config 3 0.0 0 0 row cache objects 95,580 0.0 0 0 rules engine rule set st 300 0.0 0 0 sequence cache 2,461 0.0 0 0 session allocation 36,126 0.0 0 0 session idle bit 14,410 0.0 0 0 session state list latch 24 0.0 0 0 session switching 6 0.0 0 0 session timer 125 0.0 0 0 shared pool 14,755 0.0 1.0 0 0 shared pool sim alloc 6 0.0 0 0 shared pool simulator 43,177 0.0 0 0 sim partition latch 2 0.0 0 4 0.0 simulator hash latch 141,232 0.0 0 0 simulator lru latch 5,656 0.0 0.0 0 134,424 0.0 sort extent pool 16 0.0 0 0 state object free list 24 0.0 0 0 statistics aggregation 1,120 0.0 0 0 Latch Activity DB/Inst: TEST1020/test1020 Snaps: 22-23 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ threshold alerts latch 55 0.0 0 0 transaction allocation 41 0.0 0 0 transaction branch alloc 125 0.0 0 0 undo global data 15,426 0.0 0 0 user lock 26 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: TEST1020/test1020 Snaps: 22-23 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- library cache 146,485 32 3 29 messages 12,824 4 1 3 cache buffers chains 4,252,214 4 4 0 shared pool 14,755 1 1 0 active service list 758 1 1 0 cache buffers lru chain 22,462 1 1 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: TEST1020/test1020 Snaps: 22-23 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- active service list kswssyis: find service ent 0 1 0 cache buffers chains kcbget: pin buffer 0 3 0 cache buffers chains kcbchg: kslbegin: bufs not 0 1 3 cache buffers lru chain kcbzgws_1 0 1 0 library cache kglic 0 3 0 messages ksaamb: after wakeup 0 1 0 shared pool kghalp 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: TEST1020/test1020 Snaps: 22-23 ->"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 15 0.0 0 4 1 dc_database_links 7 0.0 0 0 1 dc_global_oids 6,260 0.1 0 0 74 dc_histogram_data 2,673 4.1 0 0 3,407 dc_histogram_defs 2,436 6.3 0 0 4,408 dc_object_grants 28 25.0 0 0 62 dc_object_ids 8,239 0.8 0 0 1,221 dc_objects 658 16.1 0 0 1,486 dc_profiles 10 0.0 0 0 2 dc_rollback_segments 63 0.0 0 0 25 dc_segments 847 19.5 0 11 1,130 dc_sequences 11 18.2 0 11 33 dc_tablespace_quotas 5 20.0 0 5 2 dc_tablespaces 3,070 0.0 0 0 12 dc_usernames 92 1.1 0 0 48 dc_users 9,030 0.0 0 0 85 outstanding_alerts 24 0.0 0 0 22 ------------------------------------------------------------- Library Cache Activity DB/Inst: TEST1020/test1020 Snaps: 22-23 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 73 2.7 6,318 0.0 0 0 CLUSTER 2 0.0 3 0.0 0 0 INDEX 32 12.5 81 4.9 0 0 SQL AREA 906 4.6 24,779 0.7 0 0 TABLE/PROCEDURE 169 17.2 10,764 0.6 4 0 TRIGGER 4 0.0 48 0.0 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: TEST1020/test1020 Snaps: 22-23 -> * 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: 23 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: 23 -> 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 22 2,721 ####### .9 22,075 55.2 725,797 108 .6 41 4,104 ####### .9 8,221 20.6 730,496 128 .8 59 5,874 ####### 1.0 1,987 5.0 731,433 148 .9 78 7,747 ####### 1.0 407 1.0 732,065 168 1.0 98 9,196 ####### 1.0 400 1.0 732,447 188 1.1 115 11,399 ####### 1.0 398 1.0 732,514 208 1.2 115 11,399 ####### 1.0 398 1.0 732,520 228 1.4 115 11,399 ####### 1.0 398 1.0 732,520 248 1.5 115 11,399 ####### 1.0 398 1.0 732,520 268 1.6 115 11,399 ####### 1.0 398 1.0 732,520 288 1.7 115 11,399 ####### 1.0 398 1.0 732,520 308 1.8 115 11,399 ####### 1.0 398 1.0 732,520 328 2.0 115 11,399 ####### 1.0 398 1.0 732,520 348 2.1 115 11,399 ####### 1.0 398 1.0 732,520 ------------------------------------------------------------- SGA Target Advisory DB/Inst: TEST1020/test1020 End Snap: 23 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 292 .5 5,486 1.2 96,983 438 .8 4,748 1.1 74,359 584 1.0 4,482 1.0 66,191 730 1.3 4,477 1.0 66,098 876 1.5 4,477 1.0 66,098 1,022 1.8 4,477 1.0 66,098 1,168 2.0 4,477 1.0 66,098 ------------------------------------------------------------- SGA Memory Summary DB/Inst: TEST1020/test1020 Snaps: 22-23 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: 22-23 -> 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 11.6 12.0 3.35 shared Heap0: KGL 5.1 5.2 2.27 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 6.5 6.6 1.87 shared KQR M PO 3.4 3.5 4.81 shared KSFD SGA I/O b 3.8 3.8 0.00 shared PCursor 5.3 5.4 1.48 shared PL/SQL DIANA 2.2 2.3 2.65 shared PL/SQL MPCODE 5.2 5.3 0.90 shared XDB Schema Cac 4.6 4.6 0.00 shared free memory 27.5 24.1 -12.33 shared kglsim hash table bkts 2.0 2.0 0.00 shared library cache 8.9 9.0 0.44 shared row cache 3.6 3.6 0.00 shared sql area 38.0 40.1 5.40 shared sql area:PLSQL 2.1 2.2 3.70 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: 22-23 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 18.62 19.09 2.45 Cursor to Parent ratio: 1.29 1.30 1.13 Total Cursors: 2,443 2,510 2.67 Total Parents: 1,895 1,925 1.56 ------------------------------------------------------------- init.ora Parameters DB/Inst: TEST1020/test1020 Snaps: 22-23 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:\SOE1.txt )