ASH Report For DB11G/db11g DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ DB11G 278174245 db11g 1 11.2.0.3.0 NO IS-2YB6X4J CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 2 749M (100%) 240M (32.1%) 359M (47.9%) M (%) Analysis Begin Time: 06-Jun-13 16:30:00 Analysis End Time: 06-Jun-13 17:00:00 Elapsed Time: 30.0 (mins) Begin Data Source: DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 1378 End Data Source: DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 1379 Sample Count: 340 Average Active Sessions: 1.89 Avg. Active Session per CPU: 0.94 Report Target: None specified Top User Events DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Avg Active Event Event Class % Event Sessions ----------------------------------- --------------- ---------- ---------- db file sequential read User I/O 81.18 1.53 db file parallel read User I/O 3.24 0.06 log file sync Commit 2.65 0.05 CPU + Wait for CPU CPU 2.06 0.04 ------------------------------------------------------------- Top Background Events DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- control file sequential read System I/O 4.12 0.08 log file parallel write System I/O 3.53 0.07 db file parallel write System I/O 2.06 0.04 ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- db file sequential read 81.18 "6","620","1" 0.29 file# block# blocks control file sequential read 4.41 "0","1","1" 3.24 file# block# blocks log file parallel write 3.53 "1","3","1" 1.18 files blocks requests db file parallel read 3.24 "1","3","3" 0.88 files blocks requests log file sync 2.65 "241","7018316","0" 0.29 buffer# sync scn NOT DEFINED ------------------------------------------------------------- Top Service/Module DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Service Module % Activity Action % Action -------------- ------------------------ ---------- ------------------ ---------- DB11G.WORLD New Order 58.53 UNNAMED 26.76 getProductDetailsB 19.71 getCustomerDetails 6.47 Browse Products 14.12 getCustomerDetails 11.18 getProductDetails 1.76 UNNAMED 1.18 SYS$BACKGROUND UNNAMED 10.00 UNNAMED 10.00 DB11G.WORLD Process Orders 8.82 UNNAMED 8.82 Browse and Update Orders 4.41 getCustomerDetails 2.65 getOrdersByCustome 1.18 ------------------------------------------------------------- Top Client IDs DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Avg Active Client ID % Activity Sessions -------------------------------------------------- ---------- ---------- User Program Service --------------- --------------------------------- -------------------- Swingbench 89.41 1.69 SOE JDBC Thin Client DB11G.WORLD ------------------------------------------------------------- Top SQL Command Types DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period Distinct Avg Active SQL Command Type SQLIDs % Activity Sessions ---------------------------------------- ---------- ---------- ---------- SELECT 8 55.59 1.05 INSERT 3 23.24 0.44 UPDATE 3 6.47 0.12 PL/SQL EXECUTE 2 2.06 0.04 ------------------------------------------------------------- Top Phases of Execution DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Avg Active Phase of Execution % Activity Sessions ------------------------------ ---------- ---------- SQL Execution 87.35 1.65 PLSQL Execution 2.06 0.04 ------------------------------------------------------------- Top SQL with Top Events DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Sampled # SQL ID Planhash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Event % Event Top Row Source % RwSrc ------------------------------ ------- --------------------------------- ------- 8dq0v1mjngj7t 900611645 69 20.29 db file sequential read 20.29 TABLE ACCESS - BY INDEX ROWID 11.47 SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY , CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B 2 AND ROWNUM < :B1 c13sma6rkr27c 1640444070 67 19.71 db file sequential read 16.47 TABLE ACCESS - BY INDEX ROWID 16.18 SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG HT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, C ATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ ID = :B3 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND INVENTORIES.WAREHO db file parallel read 3.24 TABLE ACCESS - BY INDEX ROWID 2.65 bymb3ujkr3ubk 494735477 49 14.41 db file sequential read 14.12 LOAD TABLE CONVENTIONAL 14.12 INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, WAREHOUSE_ID) VALUES (ORDE RS_SEQ.NEXTVAL + :B3 , SYSTIMESTAMP , :B2 , :B1 ) RETURNING ORDER_ID INTO :O0 0yas01u2p9ch4 N/A 27 7.94 db file sequential read 7.94 ** Row Source Not Available ** 7.94 INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY ) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 7hk2m2702ua0g 4269299407 23 6.76 db file sequential read 6.76 TABLE ACCESS - BY INDEX ROWID 2.06 WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDERS WHERE ORDER_ST ATUS <= 4 AND WAREHOUSE_ID = :B1 AND ROWNUM < 10 ) SELECT O.ORDER_ID, OI.LINE_IT EM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORDER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOTION_ID, C.CUSTOMER_ID, C.CUST_FIRST_NAME, ------------------------------------------------------------- Top SQL with Top Row Sources DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Sampled # SQL ID PlanHash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Row Source % RwSrc Top Event % Event ---------------------------------------- ------- ----------------------- ------- 8dq0v1mjngj7t 900611645 69 20.29 TABLE ACCESS - BY INDEX ROWID 11.47 db file sequential read 11.47 SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY , CREDIT_LIMIT, CUST_EMAIL, ACCOUNT_MGR_ID FROM CUSTOMERS WHERE CUSTOMER_ID = :B 2 AND ROWNUM < :B1 INDEX - UNIQUE SCAN 8.82 db file sequential read 8.82 c13sma6rkr27c 1640444070 67 19.71 TABLE ACCESS - BY INDEX ROWID 18.82 db file sequential read 16.18 SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG HT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, C ATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ ID = :B3 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND INVENTORIES.WAREHO bymb3ujkr3ubk 494735477 49 14.41 LOAD TABLE CONVENTIONAL 14.41 db file sequential read 14.12 INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, WAREHOUSE_ID) VALUES (ORDE RS_SEQ.NEXTVAL + :B3 , SYSTIMESTAMP , :B2 , :B1 ) RETURNING ORDER_ID INTO :O0 0yas01u2p9ch4 N/A 27 7.94 ** Row Source Not Available ** 7.94 db file sequential read 7.94 INSERT INTO ORDER_ITEMS(ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY ) VALUES (:B4 , :B3 , :B2 , :B1 , 1) 7hk2m2702ua0g 4269299407 23 6.76 TABLE ACCESS - BY INDEX ROWID 2.06 db file sequential read 2.06 WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID FROM ORDERS WHERE ORDER_ST ATUS <= 4 AND WAREHOUSE_ID = :B1 AND ROWNUM < 10 ) SELECT O.ORDER_ID, OI.LINE_IT EM_ID, OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORDER_MODE, O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID, O.PROMOTION_ID, C.CUSTOMER_ID, C.CUST_FIRST_NAME, INDEX - UNIQUE SCAN 1.47 db file sequential read 1.47 INDEX - UNIQUE SCAN 1.18 db file sequential read 1.18 ------------------------------------------------------------- Top SQL using literals DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) No data exists for this section of the report. ------------------------------------------------------------- Top Parsing Module/Action DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) No data exists for this section of the report. ------------------------------------------------------------- Top PL/SQL Procedures DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> 'PL/SQL entry subprogram' represents the application's top-level entry-point(procedure, function, trigger, package initialization or RPC call) into PL/SQL. -> 'PL/SQL current subprogram' is the pl/sql subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular plsql entry subprogram PLSQL Entry Subprogram % Activity ----------------------------------------------------------------- ---------- PLSQL Current Subprogram % Current ----------------------------------------------------------------- ---------- SOE.ORDERENTRY.NEWORDER 58.53 SQL 57.65 SOE.ORDERENTRY.BROWSEPRODUCTS 14.12 SQL 12.94 SOE.ORDERENTRY.PROCESSORDERS#2 8.82 SQL 8.82 SOE.ORDERENTRY.BROWSEANDUPDATEORDERS 4.41 SQL 4.41 ------------------------------------------------------------- Top Java Workload DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) No data exists for this section of the report. ------------------------------------------------------------- Top Call Types DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) Call Type Count % Activity Avg Active ---------------------------------------- ---------- ---------- ---------- V8 Bundled Exec 306 90.00 1.70 ------------------------------------------------------------- Top Sessions DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event -> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics. Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 156, 3 6.18 db file sequential read 5.59 SOE JDBC Thin Client 19/180 [ 11%] 7 27, 1113 5.59 db file sequential read 5.00 SOE JDBC Thin Client 17/180 [ 9%] 7 139, 929 5.59 db file sequential read 5.29 SOE JDBC Thin Client 18/180 [ 10%] 11 142,21777 5.59 db file sequential read 4.71 SOE JDBC Thin Client 16/180 [ 9%] 4 153,27411 5.59 db file sequential read 4.71 SOE JDBC Thin Client 16/180 [ 9%] 5 ------------------------------------------------------------- Top Blocking Sessions DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> Blocking session activity percentages are calculated with respect to waits on enqueues, latches and "buffer busy" only -> '% Activity' represents the load on the database caused by a particular blocking session -> '# Samples Active' shows the number of ASH samples in which the blocking session was found active. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the blocking session was found active. Blocking Sid (Inst) % Activity Event Caused % Event -------------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 129, 1( 1) 2.65 log file sync 2.65 SYS ORACLE.EXE (LGWR) 12/180 [ 7%] 0 ------------------------------------------------------------- Top Sessions running PQs DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) No data exists for this section of the report. ------------------------------------------------------------- Top DB Objects DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> With respect to Application, Cluster, User I/O and buffer busy waits only. Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- ------------------------- 81227 24.41 db file sequential read 21.76 SOE.INVENTORIES (TABLE) SOE db file parallel read 2.65 81223 13.53 db file sequential read 13.53 SOE.CUSTOMERS (TABLE) SOE 81234 10.59 db file sequential read 10.59 SOE.CUSTOMERS_PK (INDEX) SOE 81237 8.53 db file sequential read 8.53 SOE.ORDER_PK (INDEX) SOE 81249 7.06 db file sequential read 6.76 SOE.ORD_WAREHOUSE_IX (INDEX) SOE ------------------------------------------------------------- Top DB Files DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> With respect to Cluster and User I/O events only. File ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- File Name Tablespace ----------------------------------------------------- ------------------------- 6 84.71 db file sequential read 81.18 C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SOE.DB SOE db file parallel read 3.24 ------------------------------------------------------------- Top Latches DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) No data exists for this section of the report. ------------------------------------------------------------- Activity Over Time DB/Inst: DB11G/db11g (Jun 06 16:30 to 17:00) -> Analysis period is divided into smaller time slots -> Top 3 events are reported in each of those slots -> 'Slot Count' shows the number of ASH samples in that slot -> 'Event Count' shows the number of ASH samples waiting for that event in that slot -> '% Event' is 'Event Count' over all ASH samples in the analysis period Slot Event Slot Time (Duration) Count Event Count % Event -------------------- -------- ------------------------------ -------- ------- 16:30:00 (5.0 min) 1 control file sequential read 1 0.29 16:35:00 (5.0 min) 30 db file sequential read 22 6.47 db file parallel read 4 1.18 control file sequential read 2 0.59 16:40:00 (5.0 min) 89 db file sequential read 68 20.00 control file sequential read 7 2.06 log file sync 4 1.18 16:45:00 (5.0 min) 76 db file sequential read 63 18.53 db file parallel read 4 1.18 log file parallel write 3 0.88 16:50:00 (5.0 min) 75 db file sequential read 62 18.24 log file parallel write 3 0.88 log file sync 3 0.88 16:55:00 (5.0 min) 69 db file sequential read 61 17.94 log file parallel write 3 0.88 CPU + Wait for CPU 2 0.59 ------------------------------------------------------------- End of Report