ASH Report For PRD01/PRD011 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ PRD01 1874622897 PRD011 1 11.1.0.7.0 YES prod-rac-dwh CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 16 24,466M (100%) 11,008M (45.0%) 6,744M (27.6%) M (%) Analysis Begin Time: 16-Apr-13 12:38:53 Analysis End Time: 23-Apr-13 12:38:53 Elapsed Time: 10,080.0 (mins) No data: Missing 117.0 mins (1%) of activity Begin Data Source: DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 13648 End Data Source: DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 13760 + V$ACTIVE_SESSION_HISTORY Sample Count: 6,443 Average Active Sessions: 0.11 Avg. Active Session per CPU: 0.01 Report Target: None specified Top User Events DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Avg Active Event Event Class % Event Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 56.76 0.06 direct path read temp User I/O 6.47 0.01 PX Deq: Slave Session Stats Other 2.48 0.00 reliable message Other 2.34 0.00 direct path write temp User I/O 2.06 0.00 ------------------------------------------------------------- Top Background Events DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 12.98 0.01 control file sequential read System I/O 2.37 0.00 log file sequential read System I/O 1.89 0.00 db file parallel write System I/O 1.83 0.00 log file parallel write System I/O 1.35 0.00 ------------------------------------------------------------- Top Cluster Events DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- direct path read temp 6.47 "202","475565","3" 0.03 file number first dba block cnt control file sequential read 3.54 "0","35","1" 1.06 file# block# blocks PX Deq: Slave Session Stats 2.48 "268566527","1","0" 1.40 sleeptime/senderid passes NOT DEFINED reliable message 2.39 "26990133552","26925649744"," 0.17 channel context channel handle broadcast message log file sequential read 2.14 "0","968705","2048" 0.05 log# block# blocks ------------------------------------------------------------- Top Service/Module DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Service Module % Activity Action % Action -------------- ------------------------ ---------- ------------------ ---------- PRD01 BIBusTKServerMain.exe 42.11 UNNAMED 42.11 SYS$BACKGROUND UNNAMED 18.30 UNNAMED 18.30 SYS$USERS JDBC Thin Client 11.75 UNNAMED 11.75 DBMS_SCHEDULER 5.51 ORA$AT_SQ_SQL_SW_3 3.93 emagent_SQL_rac_database 3.12 streams_latency_th 2.05 ------------------------------------------------------------- Top Client IDs DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top SQL Command Types DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> '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 525 56.25 0.06 INSERT 181 8.44 0.01 UPDATE 12 2.44 0.00 ------------------------------------------------------------- Top Phases of Execution DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Avg Active Phase of Execution % Activity Sessions ------------------------------ ---------- ---------- SQL Execution 68.83 0.07 Parse 2.16 0.00 Hard Parse 1.83 0.00 ------------------------------------------------------------- Top Remote Instances DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top SQL with Top Events DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Sampled # SQL ID Planhash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Event % Event Top Row Source % RwSrc ------------------------------ ------- --------------------------------- ------- 8h76m78cym602 3374384702 223 4.45 CPU + Wait for CPU 2.69 HASH JOIN 1.01 select A.user_data_history_set_id, EVENT_DT, NAME, LEVEL1_ID, level1_name, LEVEL2_ID, level2_name, LEVEL3_ID, level3_name, LEVEL4_ID, level4_name , LEVEL5_ID, level5_name, LEVEL6_ID, level6_name, column_value,de n_cnt, num_cnt, 'Column1' from (SELECT USER_DATA_HISTORY_SET_ID, direct path read temp 1.23 HASH JOIN 0.67 1x69a7p4j6271 892862330 226 3.54 CPU + Wait for CPU 3.54 TABLE ACCESS - FULL 3.52 SELECT WNP_USER_ID, WNP_USER_BIRTH_DT, WNP_USER_KEY FROM DWH.DIM_WNP_USER WHERE ( ( WNP_USER_ID = :1 ) ) 6wuv2xshtqvc3 3361624783 49 2.65 CPU + Wait for CPU 2.65 HASH JOIN 0.61 select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__ 0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_L abel_1, Company_Label_2, Company_Label_3, Company_Label_4, Comp any_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, 2cdwk2yanzj9m 1642664577 125 1.94 CPU + Wait for CPU 1.12 CONNECT BY - WITH FILTERING 0.05 SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FR OM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.I D PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE 5djb2yp935v58 1134703240 42 1.92 CPU + Wait for CPU 1.91 HASH JOIN 0.40 select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", first_val ue("T0"."C4") over (partition by "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3") "C4 ", "T0"."C5" "C5", "T0"."C6" "C6" from (select "T0"."C0" "C0", "T0"."C1" "C1", " T0"."C2" "C2", "T0"."C3" "C3", sum("T0"."C4") over (partition by "T0"."C2", "T0" ------------------------------------------------------------- Top SQL with Top Row Sources DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) Sampled # SQL ID PlanHash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Row Source % RwSrc Top Event % Event ---------------------------------------- ------- ----------------------- ------- 8h76m78cym602 3374384702 223 4.45 HASH JOIN 1.82 CPU + Wait for CPU 0.79 select A.user_data_history_set_id, EVENT_DT, NAME, LEVEL1_ID, level1_name, LEVEL2_ID, level2_name, LEVEL3_ID, level3_name, LEVEL4_ID, level4_name , LEVEL5_ID, level5_name, LEVEL6_ID, level6_name, column_value,de n_cnt, num_cnt, 'Column1' from (SELECT USER_DATA_HISTORY_SET_ID, HASH JOIN 1.60 CPU + Wait for CPU 1.01 1x69a7p4j6271 892862330 226 3.54 TABLE ACCESS - FULL 3.52 CPU + Wait for CPU 3.52 SELECT WNP_USER_ID, WNP_USER_BIRTH_DT, WNP_USER_KEY FROM DWH.DIM_WNP_USER WHERE ( ( WNP_USER_ID = :1 ) ) 6wuv2xshtqvc3 3361624783 49 2.65 HASH JOIN 0.61 CPU + Wait for CPU 0.61 select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__ 0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_L abel_1, Company_Label_2, Company_Label_3, Company_Label_4, Comp any_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, 2cdwk2yanzj9m 1642664577 125 1.94 CONNECT BY - WITH FILTERING 0.05 direct path read temp 0.03 SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FR OM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.I D PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE 5djb2yp935v58 1134703240 42 1.92 HASH JOIN 0.40 CPU + Wait for CPU 0.40 select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", first_val ue("T0"."C4") over (partition by "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3") "C4 ", "T0"."C5" "C5", "T0"."C6" "C6" from (select "T0"."C0" "C0", "T0"."C1" "C1", " T0"."C2" "C2", "T0"."C3" "C3", sum("T0"."C4") over (partition by "T0"."C2", "T0" ------------------------------------------------------------- Top SQL using literals DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> FORCE_MATCHING_SIGNATURE is used to identify SQL statements that are identical except for their use of literals. -> Please refer to the Oracle Database Reference to understand how the FORCE_MATCHING_SIGNATURE is derived. # of Sampled FORCE_MATCHING_SIGNATURE % Activity SQL Versions ------------------------- ---------- ------------ Example SQL 1 -------------------- Example SQL 2 -------------------- 7329915485182577000 3.85 5 0vyjukjq3yh63 select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__ 0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_L abel_1, Company_Label_2, Company_Label_3, Company_Label_4, Comp any_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, 8v5cs5kx3f68f select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__ 0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_L abel_1, Company_Label_2, Company_Label_3, Company_Label_4, Comp any_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, ------------------------------------------------------------- Top Parsing Module/Action DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top PL/SQL Procedures DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> '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 ----------------------------------------------------------------- ---------- RPT_SE.PROC_PHR_REPORT 7.29 RPT_SE.PROC_PHR_PARTICIPANT_COUNT 0.57 SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK#1 4.75 SQL 4.73 SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE#12 3.31 SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE#12 3.31 ------------------------------------------------------------- Top Java Workload DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> '# 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 -------------------- ------------------------------ ------------------ -------- 988, 3829 4.53 CPU + Wait for CPU 4.18 RPT_SE BIBusTKServerMain.exe 269/60K [ 0%] 43 927, 6195 3.93 CPU + Wait for CPU 3.86 SYS oracle@prod-ra....datapipe.net 249/60K [ 0%] 0 1096, 1 3.55 CPU + Wait for CPU 2.16 SYS oracle@prod-ra...tapipe.net (L 139/60K [ 0%] 0 control file sequential read 1.16 75/60K [ 0%] 0 1097, 1 2.81 CPU + Wait for CPU 2.79 SYS oracle@prod-ra...tapipe.net (D 180/60K [ 0%] 0 1015, 8288 2.47 CPU + Wait for CPU 2.47 RPT_SE BIBusTKServerMain.exe 159/60K [ 0%] 0 ------------------------------------------------------------- Top Blocking Sessions DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions running PQs DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> This section aggregates all the work done by the PQ slaves into the session issuing the parallel query. Sid,Srl# (Inst) % Activity SQL ID Event % Event --------------- ---------- ------------- ----------------------------- -------- User Program -------------------- ------------------------------ 997,54805(1) 2.20 PX Deq: Slave Session Stats 0.84 UserID: ------------------------------------------------------------- Top DB Objects DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> With respect to Application, Cluster, User I/O and buffer busy waits only. Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- ------------------------- 376 2.47 direct path read temp 1.91 SYS.AUD$ (TABLE) SYSTEM 118277 1.57 direct path read temp 1.26 RPT_SE.PHR_PARTICIPANT_COUNT_TAB (TABLE) RPT_SE ------------------------------------------------------------- Top DB Files DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> With respect to Cluster and User I/O events only. File ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- File Name Tablespace ----------------------------------------------------- ------------------------- 1 2.92 direct path read temp 2.10 +PRD01/prd01/datafile/system.352.762811639 SYSTEM 6 2.73 direct path read temp 1.29 +PRD01/prd01/datafile/refusion_data.dbf REFUSION_DATA 13 1.60 direct path read temp 1.27 +PRD01/prd01/datafile/rpt_se_data_01.dbf RPT_SE ------------------------------------------------------------- Top Latches DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) No data exists for this section of the report. ------------------------------------------------------------- Activity Over Time DB/Inst: PRD01/PRD011 (Apr 16 12:38 to 12:38) -> 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 -------------------- -------- ------------------------------ -------- ------- 12:38:53 (841.1 min) 729 CPU + Wait for CPU 421 6.53 direct path read temp 70 1.09 db file sequential read 35 0.54 02:40:00 (1,010.0 mi 618 CPU + Wait for CPU 463 7.19 direct path read temp 43 0.67 control file sequential read 18 0.28 19:30:00 (1,010.0 mi 431 CPU + Wait for CPU 224 3.48 control file sequential read 29 0.45 direct path read temp 27 0.42 12:20:00 (1,010.0 mi 1,150 CPU + Wait for CPU 891 13.83 direct path read temp 55 0.85 reliable message 24 0.37 05:10:00 (1,010.0 mi 336 CPU + Wait for CPU 205 3.18 control file sequential read 28 0.43 PX Deq: Slave Session Stats 23 0.36 22:00:00 (1,010.0 mi 362 CPU + Wait for CPU 196 3.04 control file sequential read 25 0.39 reliable message 21 0.33 14:50:00 (1,010.0 mi 424 CPU + Wait for CPU 264 4.10 control file sequential read 34 0.53 reliable message 23 0.36 07:40:00 (1,010.0 mi 225 CPU + Wait for CPU 142 2.20 control file sequential read 13 0.20 db file sequential read 13 0.20 00:30:00 (1,010.0 mi 1,307 CPU + Wait for CPU 1,007 15.63 direct path read temp 138 2.14 direct path write temp 37 0.57 17:20:00 (1,010.0 mi 787 CPU + Wait for CPU 612 9.50 direct path read temp 48 0.74 log file sequential read 21 0.33 10:10:00 (148.9 min) 74 CPU + Wait for CPU 68 1.06 control file sequential read 2 0.03 os thread startup 2 0.03 ------------------------------------------------------------- End of Report