ASH Report For PRD01/PRD012 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ PRD01 1874622897 PRD012 2 11.1.0.7.0 YES prod-rac-dwh CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 20 24,466M (100%) 18,688M (76.4%) 5,636M (23.0%) 35.0M (0.1%) Analysis Begin Time: 16-Apr-13 12:44:44 Analysis End Time: 23-Apr-13 12:44:44 Elapsed Time: 10,080.0 (mins) Begin Data Source: V$ACTIVE_SESSION_HISTORY End Data Source: V$ACTIVE_SESSION_HISTORY Sample Count: 26,186 Average Active Sessions: 0.04 Avg. Active Session per CPU: 0.00 Report Target: None specified Top User Events DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Avg Active Event Event Class % Event Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 29.11 0.01 PX Deq: Slave Session Stats Other 6.09 0.00 reliable message Other 5.24 0.00 direct path read temp User I/O 4.35 0.00 db file sequential read User I/O 4.23 0.00 ------------------------------------------------------------- Top Background Events DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 19.64 0.01 control file sequential read System I/O 7.14 0.00 reliable message Other 6.96 0.00 Streams AQ: qmn coordinator waiting Other 2.59 0.00 control file parallel write System I/O 2.04 0.00 ------------------------------------------------------------- Top Cluster Events DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- reliable message 12.20 "26787090840","26922423240"," 3.44 channel context channel handle broadcast message "26787090840","26922423616"," 2.81 control file sequential read 9.15 "0","35","1" 3.31 file# block# blocks PX Deq: Slave Session Stats 6.09 "268632063","1","0" 2.26 sleeptime/senderid passes NOT DEFINED db file sequential read 5.00 "1","273","1" 0.39 file# block# blocks direct path read temp 4.36 "202","1394144","1" 0.02 file number first dba block cnt ------------------------------------------------------------- Top Service/Module DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Service Module % Activity Action % Action -------------- ------------------------ ---------- ------------------ ---------- SYS$USERS DBMS_SCHEDULER 32.29 ORA$AT_SQ_SQL_SW_3 13.73 ORA$AT_SQ_SQL_SW_3 10.40 SYS$BACKGROUND UNNAMED 32.11 UNNAMED 32.11 MMON_SLAVE 9.82 Auto-Flush Slave A 7.89 SYS$USERS emagent_SQL_rac_database 6.72 streams_latency_th 3.91 racgimon@prod-rac-dwh02. 5.33 UNNAMED 5.33 ------------------------------------------------------------- Top Client IDs DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top SQL Command Types DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> '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 944 37.55 0.02 INSERT 25 3.85 0.00 CALL METHOD 3 1.26 0.00 ------------------------------------------------------------- Top Phases of Execution DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Avg Active Phase of Execution % Activity Sessions ------------------------------ ---------- ---------- SQL Execution 44.48 0.02 Parse 7.19 0.00 Hard Parse 6.76 0.00 PLSQL Execution 5.19 0.00 ------------------------------------------------------------- Top Remote Instances DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top SQL with Top Events DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Sampled # SQL ID Planhash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Event % Event Top Row Source % RwSrc ------------------------------ ------- --------------------------------- ------- 8szmwam7fysa3 2976124318 76 3.50 db file sequential read 3.32 INSERT STATEMENT 3.32 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a lloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N ULL, NULL, 'FALSE', :5, 'FALSE')) 7frqszzdu8sgg 4109302118 748 3.38 reliable message 1.42 PX COORDINATOR 1.42 select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),' OPEN','O','N') into :b0,:b1 from v$database d ,gv$instance i where i.INSTANCE_N AME=:b2 control file sequential read 1.21 FIXED TABLE - FULL 0.70 b7jz2gj2b1q09 1642664577 143 1.86 CPU + Wait for CPU 1.34 ** Row Source Not Available ** 0.58 /* SQL Analyze(1056,1) */ SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ ID, b.ID PROD_REN_KEY FROM 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 COM PANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.D 0tb6pqjhttu6p 77015730 77 1.36 direct path read temp 0.76 CONNECT BY - WITH FILTERING 0.16 /* SQL Analyze(1056,1) */ select * from DWH.DW_COMPANY_HIER_V where level_id = ' 250' 2388650043 80 1.34 direct path read temp 0.69 CONNECT BY - WITH FILTERING 0.12 /* SQL Analyze(1056,1) */ select * from DWH.DW_COMPANY_HIER_V where level_id = ' 250' ------------------------------------------------------------- Top SQL with Top Row Sources DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Sampled # SQL ID PlanHash of Executions % Activity ----------------------- -------------------- -------------------- -------------- Row Source % RwSrc Top Event % Event ---------------------------------------- ------- ----------------------- ------- 8szmwam7fysa3 2976124318 76 3.50 INSERT STATEMENT 3.50 db file sequential read 3.32 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a lloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N ULL, NULL, 'FALSE', :5, 'FALSE')) 7frqszzdu8sgg 4109302118 748 3.38 PX COORDINATOR 1.88 reliable message 1.42 select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),' OPEN','O','N') into :b0,:b1 from v$database d ,gv$instance i where i.INSTANCE_N AME=:b2 b7jz2gj2b1q09 1642664577 143 1.86 ** Row Source Not Available ** 0.58 CPU + Wait for CPU 0.58 /* SQL Analyze(1056,1) */ SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ ID, b.ID PROD_REN_KEY FROM 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 COM PANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.D 0tb6pqjhttu6p 77015730 77 1.36 CONNECT BY - WITH FILTERING 0.24 direct path read temp 0.16 /* SQL Analyze(1056,1) */ select * from DWH.DW_COMPANY_HIER_V where level_id = ' 250' 2388650043 80 1.34 CONNECT BY - WITH FILTERING 0.20 CPU + Wait for CPU 0.12 /* SQL Analyze(1056,1) */ select * from DWH.DW_COMPANY_HIER_V where level_id = ' 250' ------------------------------------------------------------- Top SQL using literals DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top Parsing Module/Action DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) Module Action % Activ Event ------------------------------ -------------------------------- ------- -------- DBMS_SCHEDULER ORA$AT_SQ_SQL_SW_3227 4.36 CPU + Wa ORA$AT_SQ_SQL_SW_3179 1.24 CPU + Wa ------------------------------------------------------------- Top PL/SQL Procedures DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> '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 ----------------------------------------------------------------- ---------- SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK#1 24.83 SQL 24.79 SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 3.59 SQL 2.05 SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC 3.56 SYS.DBMS_SPACE.OBJECT_SPACE_USAGE 3.50 ------------------------------------------------------------- Top Java Workload DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> '# 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 -------------------- ------------------------------ ------------------ -------- 1056,38674 13.73 CPU + Wait for CPU 10.80 SYS oracle@prod-ra....datapipe.net 2,827/605K [ 0%] 1 direct path read temp 2.48 650/605K [ 0%] 1 1056,41476 10.40 CPU + Wait for CPU 8.45 SYS oracle@prod-ra....datapipe.net 2,214/605K [ 0%] 3 direct path read temp 1.62 425/605K [ 0%] 1 1096, 1 8.48 CPU + Wait for CPU 4.51 SYS oracle@prod-ra...tapipe.net (L 1,181/605K [ 0%] 0 control file sequential read 3.72 973/605K [ 0%] 0 1097, 1 5.72 CPU + Wait for CPU 5.71 SYS oracle@prod-ra...tapipe.net (D 1,495/605K [ 0%] 0 1073, 14 3.95 reliable message 1.96 SYS racgimon@prod-...datapipe.net 513/605K [ 0%] 0 control file sequential read 1.21 316/605K [ 0%] 0 ------------------------------------------------------------- Top Blocking Sessions DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions running PQs DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> 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 -------------------- ------------------------------ 1073, 14(2) 4.11 7frqszzdu8sgg reliable message 1.42 SYS racgimon@prod-...datapipe.net select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),' OPEN','O','N') into :b0,:b1 from v$database d ,gv$instance i where i.INSTANCE_N AME=:b2 997,54805(1) 2.47 PX Deq: Slave Session Stats 2.08 UserID: 1035,24725(2) 1.11 PX Deq: Slave Session Stats 0.38 UserID: ------------------------------------------------------------- Top DB Objects DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> With respect to Application, Cluster, User I/O and buffer busy waits only. Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- ------------------------- 76845 3.58 direct path read temp 3.43 DWH.IDX_COMPA_PAID (INDEX) REFUSION_DATA 76765 2.62 db file sequential read 2.60 DWH.DW_MESSAGE_DATA_LOG (TABLE) REFUSION_DATA ------------------------------------------------------------- Top DB Files DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> With respect to Cluster and User I/O events only. File ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- File Name Tablespace ----------------------------------------------------- ------------------------- 6 7.71 direct path read temp 3.81 +PRD01/prd01/datafile/refusion_data.dbf REFUSION_DATA db file sequential read 3.43 2 1.90 db file sequential read 0.72 +PRD01/prd01/datafile/sysaux.353.762811641 SYSAUX ------------------------------------------------------------- Top Latches DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) No data exists for this section of the report. ------------------------------------------------------------- Activity Over Time DB/Inst: PRD01/PRD012 (Apr 16 12:44 to 12:44) -> 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:44:44 (99.3 min) 122 CPU + Wait for CPU 49 0.19 PX Deq: Slave Session Stats 29 0.11 Streams AQ: qmn coordinator wa 17 0.06 14:24:00 (1,008.0 mi 1,378 CPU + Wait for CPU 567 2.17 control file sequential read 265 1.01 PX Deq: Slave Session Stats 155 0.59 07:12:00 (1,008.0 mi 5,220 CPU + Wait for CPU 3,443 13.15 direct path read temp 652 2.49 control file sequential read 224 0.86 00:00:00 (1,008.0 mi 1,256 CPU + Wait for CPU 503 1.92 control file sequential read 252 0.96 PX Deq: Slave Session Stats 161 0.61 16:48:00 (1,008.0 mi 1,833 CPU + Wait for CPU 723 2.76 control file sequential read 332 1.27 PX Deq: Slave Session Stats 222 0.85 09:36:00 (1,008.0 mi 2,120 CPU + Wait for CPU 897 3.43 control file sequential read 214 0.82 Streams AQ: qmn coordinator wa 197 0.75 02:24:00 (1,008.0 mi 2,395 CPU + Wait for CPU 1,036 3.96 db file sequential read 309 1.18 PX Deq: Slave Session Stats 218 0.83 19:12:00 (1,008.0 mi 4,280 reliable message 1,954 7.46 CPU + Wait for CPU 1,034 3.95 db file sequential read 303 1.16 12:00:00 (1,008.0 mi 1,668 CPU + Wait for CPU 943 3.60 control file sequential read 208 0.79 reliable message 146 0.56 04:48:00 (1,008.0 mi 1,431 CPU + Wait for CPU 583 2.23 reliable message 265 1.01 control file sequential read 179 0.68 21:36:00 (908.7 min) 4,483 CPU + Wait for CPU 2,989 11.41 direct path read temp 430 1.64 db file sequential read 248 0.95 ------------------------------------------------------------- End of Report