SQL>set timing on SQL>set autotrace traceonly SQL> SQL>select /* kmtst-auto */ 2 D1.c10 as c1, 3 D1.c5 as c2, 4 D1.c11 as c3, 5 D1.c12 as c4, 6 D1.c13 as c5, 7 D1.c14 as c6, 8 D1.c15 as c7, 9 D1.c7 as c8, 10 D1.c9 as c9, 11 D1.c8 as c10, 12 D1.c6 as c11, 13 sum(D1.c2) over (partition by D1.c5) as c12, 14 sum(D1.c4) over (partition by D1.c5) as c13, 15 sum(D1.c3) over (partition by D1.c5) as c14, 16 sum(D1.c1) over (partition by D1.c5) as c15 17 from 18 (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, 19 sum(T348279.ACTIVITY_LOC_AMT) as c2, 20 sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, 21 sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, 22 T348271.ACCOUNT_CODE_DISP as c5, 23 sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, 24 sum(T348279.ACTIVITY_LOC_AMT) as c7, 25 sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, 26 sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, 27 T149255.SEGMENT_VAL_CODE as c10, 28 T348271.ACCOUNT_NAME_DISP as c11, 29 T348271.LEVEL_NUM as c12, 30 T348271.REPORT_SIGN as c13, 31 case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' 32 then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) 33 else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) 34 end as c14, 35 T348271.SORT_NUM as c15 36 from 37 WC_ACCT_HIER_DH T348271 /* Dim_WC_ACCT_HIER_DH */ , 38 (SELECT DISTINCT FROM_CURCY_CD CURRENCY FROM W_EXCH_RATE_G) T347319, 39 W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ , 40 W_HIERARCHY_D T148616 /* Dim_W_HIERARCHY_D_Segment2 */ , 41 W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */ , 42 W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ , 43 W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ , 44 W_HIERARCHY_D T125129 /* Dim_W_HIERARCHY_D_Segment1 */ , 45 W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ , 46 W_INT_ORG_D T111515 /* Dim_W_INT_ORG_D_Company */ , 47 W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ , 48 WC_GL_BALANCE_A_KM T348279 /* Fact_WC_GL_BALANCE_A */ 49 where ( T156337.ROW_WID = T348279.BALANCE_DT_WID and T347319.CURRENCY = T348279.LOC_CURR_CODE 50 and T148616.HIER_CODE = T148908.SEGMENT_LOV_ID 51 and T148616.HIER20_CODE = T148908.SEGMENT_VAL_CODE and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID 52 and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE 53 and T148908.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG2_ATTRIB and T148908.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG2_CODE 54 and T125129.HIER_CODE = T149255.SEGMENT_LOV_ID 55 and T125129.HIER20_CODE = T149255.SEGMENT_VAL_CODE and T148937.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG3_ATTRIB 56 and T148937.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG3_CODE 57 and T111515.ROW_WID = T348279.COMPANY_ORG_WID and T146058.ROW_WID = T348279.LEDGER_WID 58 and T149255.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG1_ATTRIB 59 and T149255.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG1_CODE and T111515.COMPANY_FLG = 'Y' 60 and T148908.SEGMENT_LOV_ID = 'Department~SHARE' 61 and T148937.SEGMENT_LOV_ID = 'Operating Unit~SHARE' and T149255.SEGMENT_LOV_ID = 'Account~SHARE' 62 and T156337.MCAL_PERIOD_NAME = 'March' 63 and T156337.MCAL_PER_NAME_YEAR = '2012' and T347319.CURRENCY = 'USD' and T348271.ROW_WID = T348279.ACCOUNT_HIER_WID 64 and T348279.LOC_CURR_CODE = 'USD' 65 and T111515.ORG_NUM is not null and T148543.HIER9_CODE is not null 66 and (T146058.LEDGER_NAME in ('Actuals', 'Local Ledger in Local Currency')) 67 and (T348271.LEVEL_NUM in (2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) and T148616.HIER10_CODE is not null ) 68 group by T149255.SEGMENT_VAL_CODE, T348271.LEVEL_NUM, T348271.SORT_NUM, T348271.ACCOUNT_CODE_DISP, 69 T348271.ACCOUNT_NAME_DISP, T348271.REPORT_SIGN, 70 case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' 71 then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) 72 else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end 73 ) D1 74 order by c2, c3, c4, c5, c1, c6, c7 75 ; 307 rows selected. Elapsed: 00:00:34.10 Execution Plan ---------------------------------------------------------- Plan hash value: 3249606325 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4433K| 2300M| | 980K (1)| 00:00:39 | | 1 | WINDOW BUFFER | | 4433K| 2300M| | 980K (1)| 00:00:39 | | 2 | SORT GROUP BY | | 4433K| 2300M| 2474M| 980K (1)| 00:00:39 | |* 3 | HASH JOIN | | 4433K| 2300M| | 470K (1)| 00:00:19 | |* 4 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1973 | 94704 | | 138 (0)| 00:00:01 | |* 5 | HASH JOIN | | 4371K| 2068M| | 470K (1)| 00:00:19 | |* 6 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 729 | 29889 | | 138 (0)| 00:00:01 | |* 7 | HASH JOIN | | 4356K| 1890M| | 470K (1)| 00:00:19 | |* 8 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 303 | 13029 | | 138 (0)| 00:00:01 | |* 9 | HASH JOIN | | 4352K| 1710M| | 470K (1)| 00:00:19 | |* 10 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1652 | 44604 | | 446 (1)| 00:00:01 | |* 11 | HASH JOIN | | 4354K| 1598M| | 469K (1)| 00:00:19 | |* 12 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 605 | 16335 | | 446 (1)| 00:00:01 | |* 13 | HASH JOIN | | 4358K| 1487M| | 469K (1)| 00:00:19 | |* 14 | TABLE ACCESS STORAGE FULL | W_INT_ORG_D | 857 | 14569 | | 30 (0)| 00:00:01 | |* 15 | HASH JOIN | | 4363K| 1418M| | 469K (1)| 00:00:19 | |* 16 | TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 363 | 55176 | | 7 (0)| 00:00:01 | |* 17 | HASH JOIN | | 6451K| 1162M| | 469K (1)| 00:00:19 | |* 18 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 202 | 5454 | | 446 (1)| 00:00:01 | |* 19 | HASH JOIN | | 6458K| 997M| | 468K (1)| 00:00:19 | |* 20 | TABLE ACCESS STORAGE FULL | W_LEDGER_D | 11 | 275 | | 6 (0)| 00:00:01 | | 21 | MERGE JOIN | | 7046K| 920M| | 468K (1)| 00:00:19 | |* 22 | HASH JOIN | | 7046K| 893M| | 468K (1)| 00:00:19 | | 23 | TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 31 | 682 | | 37 (0)| 00:00:01 | |* 24 | INDEX SKIP SCAN | IDX$$_2FA30002 | 31 | | | 32 (0)| 00:00:01 | |* 25 | TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 7258K| 768M| | 468K (1)| 00:00:19 | |* 26 | SORT JOIN | | 1 | 4 | | 3 (67)| 00:00:01 | | 27 | VIEW | | 1 | 4 | | 2 (50)| 00:00:01 | | 28 | HASH UNIQUE | | 1 | 4 | | 2 (50)| 00:00:01 | |* 29 | BITMAP INDEX STORAGE FAST FULL SCAN| W_EXCH_RATE_G_M3 | 147 | 588 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T125129"."HIER_CODE"="T149255"."SEGMENT_LOV_ID" AND "T125129"."HIER20_CODE"="T149255"."SEGMENT_VAL_CODE") 4 - storage("T125129"."HIER_CODE"='Account~SHARE') filter("T125129"."HIER_CODE"='Account~SHARE') 5 - access("T148616"."HIER_CODE"="T148908"."SEGMENT_LOV_ID" AND "T148616"."HIER20_CODE"="T148908"."SEGMENT_VAL_CODE") 6 - storage("T148616"."HIER_CODE"='Department~SHARE' AND "T148616"."HIER10_CODE" IS NOT NULL) filter("T148616"."HIER_CODE"='Department~SHARE' AND "T148616"."HIER10_CODE" IS NOT NULL) 7 - access("T148543"."HIER_CODE"="T148937"."SEGMENT_LOV_ID" AND "T148543"."HIER20_CODE"="T148937"."SEGMENT_VAL_CODE") 8 - storage("T148543"."HIER_CODE"='Operating Unit~SHARE' AND "T148543"."HIER9_CODE" IS NOT NULL) filter("T148543"."HIER_CODE"='Operating Unit~SHARE' AND "T148543"."HIER9_CODE" IS NOT NULL) 9 - access("T149255"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG1_ATTRIB" AND "T149255"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG1_CODE") 10 - storage("T149255"."SEGMENT_LOV_ID"='Account~SHARE') filter("T149255"."SEGMENT_LOV_ID"='Account~SHARE') 11 - access("T148908"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG2_ATTRIB" AND "T148908"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG2_CODE") 12 - storage("T148908"."SEGMENT_LOV_ID"='Department~SHARE') filter("T148908"."SEGMENT_LOV_ID"='Department~SHARE') 13 - access("T111515"."ROW_WID"="T348279"."COMPANY_ORG_WID") 14 - storage("T111515"."COMPANY_FLG"='Y' AND "T111515"."ORG_NUM" IS NOT NULL) filter("T111515"."COMPANY_FLG"='Y' AND "T111515"."ORG_NUM" IS NOT NULL) 15 - access("T348271"."ROW_WID"="T348279"."ACCOUNT_HIER_WID") 16 - storage("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12) filter("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12) 17 - access("T148937"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG3_ATTRIB" AND "T148937"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG3_CODE") 18 - storage("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') filter("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') 19 - access("T146058"."ROW_WID"="T348279"."LEDGER_WID") 20 - storage("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency') filter("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency') 22 - access("T156337"."ROW_WID"="T348279"."BALANCE_DT_WID") 24 - access("T156337"."MCAL_PERIOD_NAME"='March' AND "T156337"."MCAL_PER_NAME_YEAR"='2012') filter("T156337"."MCAL_PER_NAME_YEAR"='2012' AND "T156337"."MCAL_PERIOD_NAME"='March') 25 - storage("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE') filter("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE') 26 - access("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") filter("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") 29 - storage("FROM_CURCY_CD"='USD') filter("FROM_CURCY_CD"='USD') Statistics ---------------------------------------------------------- 1 recursive calls 7 db block gets 1729710 consistent gets 1722997 physical reads 0 redo size 53471 bytes sent via SQL*Net to client 584 bytes received via SQL*Net from client 22 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 307 rows processed SQL>set autotrace off SQL> SQL>@fsx Enter value for sql_text: %kmtst-auto% Enter value for sql_id: SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT ------------- ------ --------------- ------ ---------- ------ ------- ---------- ---------------------------------------------------------------------- 6x2sh0kvzac0t 0 3249606325 1 34.06 0 Yes 63.42 select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 a SQL>get rs.sql 1 set echo off 2 rem $Header$ 3 rem $Name$ rs.sql (recentsql.sql) 4 rem 5 rem Usage: @rs &sql_str 6 rem 7 rem Parameters: 8 rem &sql_str = SQL pattern to match on lookup 9 rem 10 rem Retrieve the SQL text and hash values for recently executed SQL 11 rem which matches a specified pattern 12 rem 13 set verify off feed off lines 500 heading on termout on 14 --accept sql_user prompt 'Enter the user name: ' 15 --accept sql_str prompt 'Enter a pattern to match (must be exact or blank for all): ' 16 col sql_text for a150 17 select /* recentsql */ sql_id, child_number, hash_value, address, executions, sql_text 18 from v$sql 19 where command_type in (2,3,6,7,189) 20 and UPPER(sql_text) like UPPER('%&1%') 21 and UPPER(sql_text) not like UPPER('%recentsql%') 22 / 23 undefine 1 24 --clear columns 25 pause 26* @dcplan 27 SQL> SQL>get dcplan.sql 1 set serveroutput off 2 set feedback off 3 set verify off 4 select * 5 from table(dbms_xplan.display_cursor('&sql_id','&child_no','ALLSTATS LAST +COST +BYTES')) 6 / 7 set feedback on 8 set verify on 9 set serveroutput on 10* -- 'ALLSTATS LAST +COST +BYTES +PEEKED_BINDS +OUTLINE' 11 SQL> SQL> SQL>@rs kmtst-auto SQL_ID CHILD_NUMBER HASH_VALUE ADDRESS EXECUTIONS SQL_TEXT ------------- ------------ ---------- ---------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 6x2sh0kvzac0t 0 3086299161 000000020FE75408 1 select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7 , D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (p artition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /* + LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVIT Y_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum (T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGMENT_VAL_CODE as Enter value for sql_id: 6x2sh0kvzac0t Enter value for child_no: 0 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 6x2sh0kvzac0t, child number 0 ------------------------------------- select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7, D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (partition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGMENT_VAL_CODE as Plan hash value: 3249606325 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 980K(100)| 307 |00:00:32.64 | 1729K| 1722K| | | | | 1 | WINDOW BUFFER | | 1 | 4433K| 2300M| | 980K (1)| 307 |00:00:32.64 | 1729K| 1722K| 80896 | 80896 |71680 (0)| | 2 | SORT GROUP BY | | 1 | 4433K| 2300M| 2474M| 980K (1)| 307 |00:00:32.64 | 1729K| 1722K| 106K| 106K|96256 (0)| |* 3 | HASH JOIN | | 1 | 4433K| 2300M| | 470K (1)| 1303K|00:00:30.75 | 1729K| 1722K| 837K| 837K| 1336K (0)| |* 4 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1 | 1973 | 94704 | | 138 (0)| 1973 |00:00:00.01 | 517 | 0 | | | | |* 5 | HASH JOIN | | 1 | 4371K| 2068M| | 470K (1)| 1303K|00:00:30.05 | 1729K| 1722K| 940K| 940K| 1282K (0)| |* 6 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1 | 729 | 29889 | | 138 (0)| 729 |00:00:00.01 | 517 | 0 | | | | |* 7 | HASH JOIN | | 1 | 4356K| 1890M| | 470K (1)| 1303K|00:00:29.39 | 1728K| 1722K| 904K| 904K| 1283K (0)| |* 8 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1 | 303 | 13029 | | 138 (0)| 304 |00:00:00.01 | 517 | 0 | | | | |* 9 | HASH JOIN | | 1 | 4352K| 1710M| | 470K (1)| 1303K|00:00:28.67 | 1728K| 1722K| 981K| 981K| 1350K (0)| |* 10 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 1652 | 44604 | | 446 (1)| 1438 |00:00:00.01 | 1669 | 0 | | | | |* 11 | HASH JOIN | | 1 | 4354K| 1598M| | 469K (1)| 1303K|00:00:27.99 | 1726K| 1722K| 955K| 955K| 1290K (0)| |* 12 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 605 | 16335 | | 446 (1)| 601 |00:00:00.01 | 1669 | 0 | | | | |* 13 | HASH JOIN | | 1 | 4358K| 1487M| | 469K (1)| 1303K|00:00:27.27 | 1724K| 1722K| 1397K| 1397K| 1290K (0)| |* 14 | TABLE ACCESS STORAGE FULL | W_INT_ORG_D | 1 | 857 | 14569 | | 30 (0)| 856 |00:00:00.01 | 97 | 0 | | | | |* 15 | HASH JOIN | | 1 | 4363K| 1418M| | 469K (1)| 1303K|00:00:26.69 | 1724K| 1722K| 735K| 735K| 1290K (0)| |* 16 | TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 1 | 363 | 55176 | | 7 (0)| 363 |00:00:00.01 | 29 | 0 | | | | |* 17 | HASH JOIN | | 1 | 6451K| 1162M| | 469K (1)| 1456K|00:00:25.93 | 1724K| 1722K| 927K| 927K| 1286K (0)| |* 18 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 202 | 5454 | | 446 (1)| 189 |00:00:00.01 | 1669 | 0 | | | | |* 19 | HASH JOIN | | 1 | 6458K| 997M| | 468K (1)| 1456K|00:00:25.13 | 1723K| 1722K| 1348K| 1348K| 1270K (0)| |* 20 | TABLE ACCESS STORAGE FULL | W_LEDGER_D | 1 | 11 | 275 | | 6 (0)| 11 |00:00:00.01 | 10 | 0 | | | | | 21 | MERGE JOIN | | 1 | 7046K| 920M| | 468K (1)| 1541K|00:00:24.46 | 1723K| 1722K| | | | |* 22 | HASH JOIN | | 1 | 7046K| 893M| | 468K (1)| 1541K|00:00:22.78 | 1723K| 1722K| 1236K| 1236K| 1247K (0)| | 23 | TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 1 | 31 | 682 | | 37 (0)| 31 |00:00:00.01 | 8 | 0 | | | | |* 24 | INDEX SKIP SCAN | IDX$$_2FA30002 | 1 | 31 | | | 32 (0)| 31 |00:00:00.01 | 4 | 0 | | | | |* 25 | TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 1 | 7258K| 768M| | 468K (1)| 42M|00:00:11.97 | 1723K| 1722K| | | | |* 26 | SORT JOIN | | 1541K| 1 | 4 | | 3 (67)| 1541K|00:00:00.87 | 3 | 0 | 2048 | 2048 | 2048 (0)| | 27 | VIEW | | 1 | 1 | 4 | | 2 (50)| 1 |00:00:00.01 | 3 | 0 | | | | | 28 | HASH UNIQUE | | 1 | 1 | 4 | | 2 (50)| 1 |00:00:00.01 | 3 | 0 | 1453K| 1453K| 470K (0)| |* 29 | BITMAP INDEX STORAGE FAST FULL SCAN| W_EXCH_RATE_G_M3 | 1 | 147 | 588 | | 1 (0)| 1 |00:00:00.01 | 3 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T125129"."HIER_CODE"="T149255"."SEGMENT_LOV_ID" AND "T125129"."HIER20_CODE"="T149255"."SEGMENT_VAL_CODE") 4 - storage("T125129"."HIER_CODE"='Account~SHARE') filter("T125129"."HIER_CODE"='Account~SHARE') 5 - access("T148616"."HIER_CODE"="T148908"."SEGMENT_LOV_ID" AND "T148616"."HIER20_CODE"="T148908"."SEGMENT_VAL_CODE") 6 - storage(("T148616"."HIER_CODE"='Department~SHARE' AND "T148616"."HIER10_CODE" IS NOT NULL)) filter(("T148616"."HIER_CODE"='Department~SHARE' AND "T148616"."HIER10_CODE" IS NOT NULL)) 7 - access("T148543"."HIER_CODE"="T148937"."SEGMENT_LOV_ID" AND "T148543"."HIER20_CODE"="T148937"."SEGMENT_VAL_CODE") 8 - storage(("T148543"."HIER_CODE"='Operating Unit~SHARE' AND "T148543"."HIER9_CODE" IS NOT NULL)) filter(("T148543"."HIER_CODE"='Operating Unit~SHARE' AND "T148543"."HIER9_CODE" IS NOT NULL)) 9 - access("T149255"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG1_ATTRIB" AND "T149255"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG1_CODE") 10 - storage("T149255"."SEGMENT_LOV_ID"='Account~SHARE') filter("T149255"."SEGMENT_LOV_ID"='Account~SHARE') 11 - access("T148908"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG2_ATTRIB" AND "T148908"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG2_CODE") 12 - storage("T148908"."SEGMENT_LOV_ID"='Department~SHARE') filter("T148908"."SEGMENT_LOV_ID"='Department~SHARE') 13 - access("T111515"."ROW_WID"="T348279"."COMPANY_ORG_WID") 14 - storage(("T111515"."COMPANY_FLG"='Y' AND "T111515"."ORG_NUM" IS NOT NULL)) filter(("T111515"."COMPANY_FLG"='Y' AND "T111515"."ORG_NUM" IS NOT NULL)) 15 - access("T348271"."ROW_WID"="T348279"."ACCOUNT_HIER_WID") 16 - storage(("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12)) filter(("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12)) 17 - access("T148937"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG3_ATTRIB" AND "T148937"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG3_CODE") 18 - storage("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') filter("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') 19 - access("T146058"."ROW_WID"="T348279"."LEDGER_WID") 20 - storage(("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency')) filter(("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency')) 22 - access("T156337"."ROW_WID"="T348279"."BALANCE_DT_WID") 24 - access("T156337"."MCAL_PERIOD_NAME"='March' AND "T156337"."MCAL_PER_NAME_YEAR"='2012') filter(("T156337"."MCAL_PER_NAME_YEAR"='2012' AND "T156337"."MCAL_PERIOD_NAME"='March')) 25 - storage(("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE')) filter(("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE')) 26 - access("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") filter("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") 29 - storage("FROM_CURCY_CD"='USD') filter("FROM_CURCY_CD"='USD') -- Just in case you were wondering if this plan is "good", check out this plan for the same SQL statement. -- What's different? This second plan was derived after histogram statistics were collected. -------------------------------- -------------------------------- A-Time | Buffers | Reads | VS A-Time | Buffers | Reads | -------------------------------- -------------------------------- 00:00:32.64 | 1729K| 1722K| 00:00:50.58 | 7727K| 1722K| Plan hash value: 1770828545 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 470K(100)| 307 |00:00:50.58 | 7727K| 1722K| | | | | 1 | WINDOW BUFFER | | 1 | 14 | 7616 | 470K (1)| 307 |00:00:50.58 | 7727K| 1722K| 80896 | 80896 |71680 (0)| | 2 | SORT GROUP BY | | 1 | 14 | 7616 | 470K (1)| 307 |00:00:50.58 | 7727K| 1722K| 106K| 106K|96256 (0)| | 3 | NESTED LOOPS | | 1 | 14 | 7616 | 470K (1)| 1303K|00:00:48.33 | 7727K| 1722K| | | | | 4 | NESTED LOOPS | | 1 | 14 | 6944 | 470K (1)| 1303K|00:00:43.53 | 7690K| 1722K| | | | | 5 | NESTED LOOPS | | 1 | 14 | 6706 | 470K (1)| 1303K|00:00:40.19 | 6369K| 1722K| | | | | 6 | NESTED LOOPS | | 1 | 14 | 6132 | 470K (1)| 1303K|00:00:34.06 | 4161K| 1722K| | | | |* 7 | HASH JOIN | | 1 | 14 | 5530 | 470K (1)| 1303K|00:00:27.07 | 1728K| 1722K| 106M| 9046K| 146M (0)| |* 8 | HASH JOIN | | 1 | 20 | 4860 | 470K (1)| 1456K|00:00:25.76 | 1728K| 1722K| 981K| 981K| 1337K (0)| |* 9 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 1651 | 44577 | 446 (1)| 1438 |00:00:00.01 | 1669 | 0 | | | | |* 10 | HASH JOIN | | 1 | 864 | 182K| 470K (1)| 1456K|00:00:25.09 | 1726K| 1722K| 955K| 955K| 1290K (0)| |* 11 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 605 | 16335 | 446 (1)| 601 |00:00:00.01 | 1669 | 0 | | | | |* 12 | HASH JOIN | | 1 | 102K| 18M| 470K (1)| 1456K|00:00:24.40 | 1724K| 1722K| 927K| 927K| 1286K (0)| |* 13 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1 | 202 | 5454 | 446 (1)| 189 |00:00:00.01 | 1669 | 0 | | | | |* 14 | HASH JOIN | | 1 | 38M| 5878M| 469K (1)| 1456K|00:00:23.67 | 1723K| 1722K| 1348K| 1348K| 1271K (0)| |* 15 | TABLE ACCESS STORAGE FULL | W_LEDGER_D | 1 | 11 | 275 | 6 (0)| 11 |00:00:00.01 | 10 | 0 | | | | | 16 | MERGE JOIN | | 1 | 41M| 5422M| 469K (1)| 1541K|00:00:23.05 | 1723K| 1722K| | | | |* 17 | HASH JOIN | | 1 | 41M| 5264M| 469K (1)| 1541K|00:00:21.50 | 1723K| 1722K| 1236K| 1236K| 1247K (0)| | 18 | TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 1 | 31 | 682 | 37 (0)| 31 |00:00:00.01 | 8 | 0 | | | | |* 19 | INDEX SKIP SCAN | IDX$$_2FA30002 | 1 | 31 | | 32 (0)| 31 |00:00:00.01 | 4 | 0 | | | | |* 20 | TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 1 | 42M| 4526M| 469K (1)| 42M|00:00:10.81 | 1723K| 1722K| | | | |* 21 | SORT JOIN | | 1541K| 1 | 4 | 3 (67)| 1541K|00:00:00.77 | 3 | 0 | 2048 | 2048 | 2048 (0)| | 22 | VIEW | | 1 | 1 | 4 | 2 (50)| 1 |00:00:00.01 | 3 | 0 | | | | | 23 | HASH UNIQUE | | 1 | 1 | 4 | 2 (50)| 1 |00:00:00.01 | 3 | 0 | 1453K| 1453K| 467K (0)| |* 24 | BITMAP INDEX STORAGE FAST FULL SCAN| W_EXCH_RATE_G_M3 | 1 | 147 | 588 | 1 (0)| 1 |00:00:00.01 | 3 | 0 | | | | |* 25 | TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 1 | 363 | 55176 | 7 (0)| 363 |00:00:00.01 | 29 | 0 | | | | |* 26 | TABLE ACCESS BY INDEX ROWID | W_HIERARCHY_D | 1303K| 1 | 43 | 2 (0)| 1303K|00:00:06.45 | 2432K| 0 | | | | |* 27 | INDEX RANGE SCAN | IDX$$_2F620005 | 1303K| 1 | | 1 (0)| 1303K|00:00:03.53 | 1181K| 0 | | | | |* 28 | TABLE ACCESS BY INDEX ROWID | W_HIERARCHY_D | 1303K| 1 | 41 | 2 (0)| 1303K|00:00:05.60 | 2208K| 0 | | | | |* 29 | INDEX RANGE SCAN | IDX$$_2F620005 | 1303K| 1 | | 1 (0)| 1303K|00:00:03.07 | 1262K| 0 | | | | |* 30 | TABLE ACCESS BY INDEX ROWID | W_INT_ORG_D | 1303K| 1 | 17 | 1 (0)| 1303K|00:00:02.83 | 1321K| 0 | | | | |* 31 | INDEX UNIQUE SCAN | W_INT_ORG_D_P1 | 1303K| 1 | | 0 (0)| 1303K|00:00:01.05 | 17703 | 0 | | | | | 32 | TABLE ACCESS BY INDEX ROWID | W_HIERARCHY_D | 1303K| 1 | 48 | 2 (0)| 1303K|00:00:04.28 | 36689 | 0 | | | | |* 33 | INDEX RANGE SCAN | IDX$$_2F620005 | 1303K| 1 | | 1 (0)| 1303K|00:00:01.93 | 36187 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("T348271"."ROW_WID"="T348279"."ACCOUNT_HIER_WID") 8 - access("T149255"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG1_ATTRIB" AND "T149255"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG1_CODE") 9 - storage("T149255"."SEGMENT_LOV_ID"='Account~SHARE') filter("T149255"."SEGMENT_LOV_ID"='Account~SHARE') 10 - access("T148908"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG2_ATTRIB" AND "T148908"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG2_CODE") 11 - storage("T148908"."SEGMENT_LOV_ID"='Department~SHARE') filter("T148908"."SEGMENT_LOV_ID"='Department~SHARE') 12 - access("T148937"."SEGMENT_LOV_ID"="T348279"."ACCOUNT_SEG3_ATTRIB" AND "T148937"."SEGMENT_VAL_CODE"="T348279"."ACCOUNT_SEG3_CODE") 13 - storage("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') filter("T148937"."SEGMENT_LOV_ID"='Operating Unit~SHARE') 14 - access("T146058"."ROW_WID"="T348279"."LEDGER_WID") 15 - storage(("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency')) filter(("T146058"."LEDGER_NAME"='Actuals' OR "T146058"."LEDGER_NAME"='Local Ledger in Local Currency')) 17 - access("T156337"."ROW_WID"="T348279"."BALANCE_DT_WID") 19 - access("T156337"."MCAL_PERIOD_NAME"='March' AND "T156337"."MCAL_PER_NAME_YEAR"='2012') filter(("T156337"."MCAL_PER_NAME_YEAR"='2012' AND "T156337"."MCAL_PERIOD_NAME"='March')) 20 - storage(("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE')) filter(("T348279"."LOC_CURR_CODE"='USD' AND "T348279"."ACCOUNT_SEG2_ATTRIB"='Department~SHARE' AND "T348279"."ACCOUNT_SEG3_ATTRIB"='Operating Unit~SHARE' AND "T348279"."ACCOUNT_SEG1_ATTRIB"='Account~SHARE')) 21 - access("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") filter("T347319"."CURRENCY"="T348279"."LOC_CURR_CODE") 24 - storage("FROM_CURCY_CD"='USD') filter("FROM_CURCY_CD"='USD') 25 - storage(("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12)) filter(("T348271"."LEVEL_NUM"=2 OR "T348271"."LEVEL_NUM"=3 OR "T348271"."LEVEL_NUM"=4 OR "T348271"."LEVEL_NUM"=5 OR "T348271"."LEVEL_NUM"=6 OR "T348271"."LEVEL_NUM"=7 OR "T348271"."LEVEL_NUM"=8 OR "T348271"."LEVEL_NUM"=9 OR "T348271"."LEVEL_NUM"=10 OR "T348271"."LEVEL_NUM"=12)) 26 - filter("T148543"."HIER9_CODE" IS NOT NULL) 27 - access("T148543"."HIER_CODE"='Operating Unit~SHARE' AND "T148543"."HIER20_CODE"="T148937"."SEGMENT_VAL_CODE") 28 - filter("T148616"."HIER10_CODE" IS NOT NULL) 29 - access("T148616"."HIER_CODE"='Department~SHARE' AND "T148616"."HIER20_CODE"="T148908"."SEGMENT_VAL_CODE") 30 - filter(("T111515"."COMPANY_FLG"='Y' AND "T111515"."ORG_NUM" IS NOT NULL)) 31 - access("T111515"."ROW_WID"="T348279"."COMPANY_ORG_WID") 33 - access("T125129"."HIER_CODE"='Account~SHARE' AND "T125129"."HIER20_CODE"="T149255"."SEGMENT_VAL_CODE") SQL> SQL> SQL>get sql_monitor_rep.sql 1 set long 10000000 2 set longchunksize 10000000 3 set linesize 299 4 accept sql_id - 5 prompt 'Enter value for sql_id: ' - 6 default 'X0X0X0X0' 7 spool sql_monitor_&&sql_id 8 select dbms_sqltune.report_sql_monitor(SQL_ID => '&&sql_id', SESSION_ID => '&session_id') from dual 9 / 10 spool off 11* undef sql_id SQL> SQL>@sql_monitor_rep Enter value for sql_id: 6x2sh0kvzac0t Enter value for session_id: old 1: select dbms_sqltune.report_sql_monitor(SQL_ID => '&&sql_id', SESSION_ID => '&session_id') from dual new 1: select dbms_sqltune.report_sql_monitor(SQL_ID => '6x2sh0kvzac0t', SESSION_ID => '') from dual DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'6X2SH0KVZAC0T',SESSION_ID=>'') ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL Monitoring Report SQL Text ------------------------------ select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7, D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (partition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c 5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGM ENT_VAL_CODE as c10, T348271.ACCOUNT_NAME_DISP as c11, T348271.LEVEL_NUM as c12, T348271.REPORT_SIGN as c13, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end as c14, T348271.SORT_NUM as c15 from WC_ACCT_HIER_DH T348271 /* Dim_WC_ACCT_HIER_DH */ , (SELECT DISTINCT FROM_CURCY_CD CURRENCY FROM W_EXCH_RATE_G) T347319, W_MCAL_D AY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ , W_HIERARCHY_D T148616 /* Dim_W_HIERARCHY_D_Segment2 */ , W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */ , W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ , W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ , W_HIERARCHY_D T125129 /* Dim_W_HIERARCHY_D_Segment1 */ , W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ , W_INT_ORG_D T111515 /* Dim_W_INT_ORG_D_Company */ , W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ , WC_GL_BALANCE_A_KM T348279 /* Fact_WC_GL_BALANCE_A */ where ( T156337.ROW_WID = T348279.BALANCE_DT_WID and T347319.CURRENCY = T348279.LOC_CURR_CODE and T148616.HIER_CODE = T148908.SEGMENT_LOV_ID and T148616.HIER20_CODE = T148908.SEGMENT_VAL_CODE and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T148908.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG2_ATTRIB and T148908.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG2_CODE and T125129.HIER_CODE = T149255.SEGMENT_ LOV_ID and T125129.HIER20_CODE = T149255.SEGMENT_VAL_CODE and T148937.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG3_ATTRIB and T148937.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG3_CODE and T111515.ROW_WID = T348279.COMPANY_ORG_WID and T146058.ROW_WID = T348279.LEDGER_WID and T149255.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG1_ATTRIB and T149255.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG1_CODE and T111515.COMPANY_FLG = 'Y' and T148908.SEGMENT_LOV_ID = 'Department~SHARE' and T148937.SEGMENT_LOV_ID = 'Operating Un it~SHARE' and T149255.SEGMENT_LOV_ID = 'Account~SHARE' and T156337.MCAL_PERIOD_NAME = 'March' and T156337.MCAL_PER_NAME_YEAR = '2012' and T347319.CURRENCY = 'USD' and T348271.ROW_WID = T348279.ACCOUNT_HIER_WID and T348279.LOC_CURR_CODE = 'USD' and T111515.ORG_NUM is not null and T148543.HIER9_CODE is not null and (T146058.LEDGER_NAME in ('Actuals', 'Local Ledger in Local Currency')) and (T348271.LEVEL_NUM in (2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) and T148616.HIER10_CODE is not null ) group by T149255.SEGMENT_VAL_CODE, T348271.LEVEL_NUM, T348271.SORT_NUM, T348271.ACCOUNT_CODE_DISP, T348271.ACCOUNT_NAME_DISP, T348271.REPORT_SIGN, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end ) D1 order by c2, c3, c4, c5, c1, c6, c7 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (992:9971) SQL ID : 6x2sh0kvzac0t SQL Execution ID : 16777216 Execution Started : 05/05/2012 11:12:28 First Refresh Time : 05/05/2012 11:12:31 Last Refresh Time : 05/05/2012 11:13:02 Duration : 34s Module/Action : sqlplus.exe/- Service : BIUATAH Program : sqlplus.exe Fetch Calls : 22 Global Stats ==================================================================================================== | Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload | ==================================================================================================== | 33 | 32 | 0.80 | 0.00 | 0.14 | 22 | 2M | 18384 | 13GB | 63.37% | ==================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=3249606325) ===================================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | ===================================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +34 | 1 | 307 | | | | | | | | 1 | WINDOW BUFFER | | 4M | 981K | 1 | +34 | 1 | 307 | | | | 71680 | | | | 2 | SORT GROUP BY | | 4M | 981K | 32 | +3 | 1 | 307 | | | | 96256 | 14.71 | Cpu (5) | | 3 | HASH JOIN | | 4M | 471K | 32 | +3 | 1 | 1M | | | | 1M | | | | 4 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1973 | 138 | 1 | +3 | 1 | 1973 | | | | | | | | 5 | HASH JOIN | | 4M | 471K | 32 | +3 | 1 | 1M | | | | 1M | 2.94 | Cpu (1) | | 6 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 729 | 138 | 1 | +3 | 1 | 729 | | | | | | | | 7 | HASH JOIN | | 4M | 470K | 32 | +3 | 1 | 1M | | | | 1M | | | | 8 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 303 | 138 | 1 | +3 | 1 | 304 | | | | | | | | 9 | HASH JOIN | | 4M | 470K | 32 | +3 | 1 | 1M | | | | 1M | | | | 10 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1652 | 446 | 1 | +3 | 1 | 1438 | | | | | | | | 11 | HASH JOIN | | 4M | 470K | 32 | +3 | 1 | 1M | | | | 1M | | | | 12 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 605 | 446 | 1 | +3 | 1 | 601 | | | | | | | | 13 | HASH JOIN | | 4M | 469K | 32 | +3 | 1 | 1M | | | | 1M | 2.94 | Cpu (1) | | 14 | TABLE ACCESS STORAGE FULL | W_INT_ORG_D | 857 | 30 | 1 | +3 | 1 | 856 | | | | | | | | 15 | HASH JOIN | | 4M | 469K | 32 | +3 | 1 | 1M | | | | 1M | | | | 16 | TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 363 | 7 | 1 | +3 | 1 | 363 | | | | | | | | 17 | HASH JOIN | | 6M | 469K | 32 | +3 | 1 | 1M | | | | 1M | | | | 18 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 202 | 446 | 1 | +3 | 1 | 189 | | | | | | | | 19 | HASH JOIN | | 6M | 469K | 32 | +3 | 1 | 1M | | | | 1M | 2.94 | Cpu (1) | | 20 | TABLE ACCESS STORAGE FULL | W_LEDGER_D | 11 | 6 | 1 | +3 | 1 | 11 | | | | | | | | 21 | MERGE JOIN | | 7M | 469K | 32 | +3 | 1 | 2M | | | | | 5.88 | Cpu (2) | | 22 | HASH JOIN | | 7M | 469K | 33 | +2 | 1 | 2M | | | | 1M | 47.06 | Cpu (16) | | 23 | TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 31 | 37 | 1 | +3 | 1 | 31 | | | | | | | | 24 | INDEX SKIP SCAN | IDX$$_2FA30002 | 31 | 32 | 1 | +3 | 1 | 31 | | | | | | | | 25 | TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 7M | 469K | 32 | +3 | 1 | 43M | 17340 | 12GB | 63.37% | | 17.65 | Cpu (5) | | | | | | | | | | | | | | | | cell smart table scan (1)| | 26 | SORT JOIN | | 1 | 3 | 32 | +3 | 2M | 2M | | | | 2048 | | | | 27 | VIEW | | 1 | 2 | 1 | +3 | 1 | 1 | | | | | | | | 28 | HASH UNIQUE | | 1 | 2 | 1 | +3 | 1 | 1 | | | | 481K | | | | 29 | BITMAP INDEX STORAGE FAST FULL SCAN | W_EXCH_RATE_G_M3 | 147 | 1 | 1 | +3 | 1 | 1 | | | | | | | ===================================================================================================================================================================================================================== 1 row selected. SQL>get sql-runstats.sql 1 set long 32000 numwidth 15 2 set lines 4000 3 set pages 1000 4 col sql_text format a150 word_wrapped heading 'SQL Statement' 5 select sql_text from DBA_HIST_SQLTEXT where sql_id = '&&sql_id'; 6 column snap_id head 'Snap#' format 9999999 7 column instance_number head 'Inst#' format 99999 8 column sorts_total head 'Sorts' format 99999 9 column executions_total head 'Execs' format 99999 10 column disk_reads_total head 'PIO' 11 column buffer_gets_total head 'LIO' 12 column rows_processed_total head 'Rows' 13 column cpu_time_total head 'CPU' 14 column elapsed_time_total head 'Elapsed' 15 column iowait_total head 'IO Wait' 16 column clwait_total head 'Cluster Wait' 17 column sorts_delta head 'Sorts' format 99999 18 column executions_delta head 'Execs' format 99999 19 column disk_reads_delta head 'PIO' 20 column buffer_gets_delta head 'LIO' 21 column rows_processed_delta head 'Rows' 22 column cpu_time_delta head 'CPU' 23 column elapsed_time_delta head 'Elapsed' 24 column iowait_delta head 'IO Wait' 25 column clwait_delta head 'Cluster Wait' 26 column snap_tm head 'Snap Time' 27 column plan_hash_value head 'Plan HV' format 999999999999 28 /* 29 select snap_id, instance_number, sorts_total, executions_total, disk_reads_total, buffer_gets_total, rows_processed_total, 30 cpu_time_total, elapsed_time_total, iowait_total, clwait_total 31 --, bind_data 32 from dba_hist_sqlstat where sql_id = '&sql_id'; 33 */ 34 select a.snap_id, to_char(b.begin_interval_time,'mm/dd/yyyy hh24:mi') snap_tm, a.instance_number, a.plan_hash_value, a.sorts_delta, 35 a.executions_delta, a.disk_reads_delta, a.buffer_gets_delta, a.rows_processed_delta, 36 a.cpu_time_delta/1000000 cpu_time_delta, a.elapsed_time_delta/1000000 elapsed_time_delta, a.iowait_delta/1000000 iowait_delta, 37 a.clwait_delta/1000000 clwait_delta 38 from dba_hist_sqlstat a, dba_hist_snapshot b 39 where a.sql_id = '&sql_id' 40 --and a.plan_hash_value = 219107634 41 and a.snap_id = b.snap_id 42 and a.instance_number = b.instance_number 43 and a.dbid = b.dbid 44 --and snap_id = (select max(snap_id) from dba_hist_snapshot) 45 order by a.instance_number, a.snap_id desc, a.plan_hash_value ; 46 undef sql_id 47* clear columns 48 SQL> SQL>@sql-runstats Enter value for sql_id: 6x2sh0kvzac0t SQL Statement ------------------------------------------------------------------------------------------------------------------------------------------------------ select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7, D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (partition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGMENT_VAL_CODE as c10, T348271.ACCOUNT_NAME_DISP as c11, T348271.LEVEL_NUM as c12, T348271.REPORT_SIGN as c13, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end as c14, T348271.SORT_NUM as c15 from WC_ACCT_HIER_DH T348271 /* Dim_WC_ACCT_HIER_DH */ , (SELECT DISTINCT FROM_CURCY_CD CURRENCY FROM W_EXCH_RATE_G) T347319, W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ , W_HIERARCHY_D T148616 /* Dim_W_HIERARCHY_D_Segment2 */ , W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */ , W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ , W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ , W_HIERARCHY_D T125129 /* Dim_W_HIERARCHY_D_Segment1 */ , W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ , W_INT_ORG_D T111515 /* Dim_W_INT_ORG_D_Company */ , W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ , WC_GL_BALANCE_A_KM T348279 /* Fact_WC_GL_BALANCE_A */ where ( T156337.ROW_WID = T348279.BALANCE_DT_WID and T347319.CURRENCY = T348279.LOC_CURR_CODE and T148616.HIER_CODE = T148908.SEGMENT_LOV_ID and T148616.HIER20_CODE = T148908.SEGMENT_VAL_CODE and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T148908.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG2_ATTRIB and T148908.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG2_CODE and T125129.HIER_CODE = T149255.SEGMENT_LOV_ID and T125129.HIER20_CODE = T149255.SEGMENT_VAL_CODE and T148937.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG3_ATTRIB and T148937.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG3_CODE and T111515.ROW_WID = T348279.COMPANY_ORG_WID and T146058.ROW_WID = T348279.LEDGER_WID and T149255.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG1_ATTRIB and T149255.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG1_CODE and T111515.COMPANY_FLG = 'Y' and T148908.SEGMENT_LOV_ID = 'Department~SHARE' and T148937.SEGMENT_LOV_ID = 'Operating Unit~SHARE' and T149255.SEGMENT_LOV_ID = 'Account~SHARE' and T156337.MCAL_PERIOD_NAME = 'March' and T156337.MCAL_PER_NAME_YEAR = '2012' and T347319.CURRENCY = 'USD' and T348271.ROW_WID = T348279.ACCOUNT_HIER_WID and T348279.LOC_CURR_CODE = 'USD' and T111515.ORG_NUM is not null and T148543.HIER9_CODE is not null and (T146058.LEDGER_NAME in ('Actuals', 'Local Ledger in Local Currency')) and (T348271.LEVEL_NUM in (2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) and T148616.HIER10_CODE is not null ) group by T149255.SEGMENT_VAL_CODE, T348271.LEVEL_NUM, T348271.SORT_NUM, T348271.ACCOUNT_CODE_DISP, T348271.ACCOUNT_NAME_DISP, T348271.REPORT_SIGN, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end ) D1 order by c2, c3, c4, c5, c1, c6, c7 Snap# Snap Time Inst# Plan HV Sorts Execs PIO LIO Rows CPU Elapsed IO Wait Cluster Wait ------ ---------------- ------ ----------- ------ ------ --------- --------- ------ ----------- ----------- ---------- -------------- 16412 05/05/2012 11:00 1 3249606325 3 1 1722997 1729717 307 33.115966 34.063393 .803569 0 SQL> SQL>get xpdawr.sql 1 set long 32000 numwidth 15 2 set lines 4000 3 set pages 1000 4 SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id','&phv',null,'&fmt')); 5 undef sql_id 6 undef phv 7* undef fmt 8 SQL> SQL>@xpdawr Enter value for sql_id: 6x2sh0kvzac0t Enter value for phv: Enter value for fmt: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 6x2sh0kvzac0t -------------------- select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7, D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (partition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGMENT_VAL_CODE as c10, T348271.ACCOUNT_NAME_DISP as c11, T348271.LEVEL_NUM as c12, T348271.REPORT_SIGN as c13, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end as c14, T348271.SORT_NUM as c15 from WC_ACCT_HIER_DH T348271 /* Dim_WC_ACCT_HIER_DH */ , (SELECT DISTINCT FROM_CURCY_CD CURRENCY FROM W_EXCH_RATE_G) T347319, W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ , W_HIERARCHY_D T148616 /* Dim_W_HIERARCHY_D_Segment2 */ , W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */ , W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ , W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ , W_HIERARCHY_D T125129 /* Dim_W_HIERARCHY_D_Segment1 */ , W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ , W_INT_ORG_D T111515 /* Dim_W_INT_ORG_D_Company */ , W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ , WC_GL_BALANCE_A_KM T348279 /* Fact_WC_GL_BALANCE_A */ where ( T156337.ROW_WID = T348279.BALANCE_DT_WID and T347319.CURRENCY = T348279.LOC_CURR_CODE and T148616.HIER_CODE = T148908.SEGMENT_LOV_ID and T148616.HIER20_CODE = T148908.SEGMENT_VAL_CODE and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T148908.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG2_ATTRIB and T148908.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG2_CODE and T125129.HIER_CODE = T149255.SEGMENT_LOV_ID and T125129.HIER20_CODE = T149255.SEGMENT_VAL_CODE and T148937.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG3_ATTRIB and T148937.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG3_CODE and T111515.ROW_WID = T348279.COMPANY_ORG_WID and T146058.ROW_WID = T348279.LEDGER_WID and T149255.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG1_ATTRIB and T149255.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG1_CODE and T111515.COMPANY_FLG = 'Y' and T148908.SEGMENT_LOV_ID = 'Department~SHARE' and T148937.SEGMENT_LOV_ID = 'Operating Unit~SHARE' and T149255.SEGMENT_LOV_ID = 'Account~SHARE' and T156337.MCAL_PERIOD_NAME = 'March' and T156337.MCAL_PER_NAME_YEAR = '2012' and T347319.CURRENCY = 'USD' and T348271.ROW_WID = T348279.ACCOUNT_HIER_WID and T348279.LOC_CURR_CODE = 'USD' and T111515.ORG_NUM is not null and T148543.HIER9_CODE is not null and (T146058.LEDGER_NAME in ('Actuals', 'Local Ledger in Local Currency')) and (T348271.LEVEL_NUM in (2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) and T148616.HIER10_CODE is not null ) group by T149255.SEGMENT_VAL_CODE, T348271.LEVEL_NUM, T348271.SORT_NUM, T348271.ACCOUNT_CODE_DISP, T348271.ACCOUNT_NAME_DISP, T348271.REPORT_SIGN, case when T125129.HI Plan hash value: 3249606325 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 980K(100)| | | 1 | WINDOW BUFFER | | 4433K| 2300M| | 980K (1)| 03:16:08 | | 2 | SORT GROUP BY | | 4433K| 2300M| 2474M| 980K (1)| 03:16:08 | | 3 | HASH JOIN | | 4433K| 2300M| | 470K (1)| 01:34:09 | | 4 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1973 | 94704 | | 138 (0)| 00:00:02 | | 5 | HASH JOIN | | 4371K| 2068M| | 470K (1)| 01:34:07 | | 6 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 729 | 29889 | | 138 (0)| 00:00:02 | | 7 | HASH JOIN | | 4356K| 1890M| | 470K (1)| 01:34:05 | | 8 | TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 303 | 13029 | | 138 (0)| 00:00:02 | | 9 | HASH JOIN | | 4352K| 1710M| | 470K (1)| 01:34:03 | | 10 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1652 | 44604 | | 446 (1)| 00:00:06 | | 11 | HASH JOIN | | 4354K| 1598M| | 469K (1)| 01:33:58 | | 12 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 605 | 16335 | | 446 (1)| 00:00:06 | | 13 | HASH JOIN | | 4358K| 1487M| | 469K (1)| 01:33:52 | | 14 | TABLE ACCESS STORAGE FULL | W_INT_ORG_D | 857 | 14569 | | 30 (0)| 00:00:01 | | 15 | HASH JOIN | | 4363K| 1418M| | 469K (1)| 01:33:51 | | 16 | TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 363 | 55176 | | 7 (0)| 00:00:01 | | 17 | HASH JOIN | | 6451K| 1162M| | 469K (1)| 01:33:51 | | 18 | TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 202 | 5454 | | 446 (1)| 00:00:06 | | 19 | HASH JOIN | | 6458K| 997M| | 468K (1)| 01:33:46 | | 20 | TABLE ACCESS STORAGE FULL | W_LEDGER_D | 11 | 275 | | 6 (0)| 00:00:01 | | 21 | MERGE JOIN | | 7046K| 920M| | 468K (1)| 01:33:45 | | 22 | HASH JOIN | | 7046K| 893M| | 468K (1)| 01:33:45 | | 23 | TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 31 | 682 | | 37 (0)| 00:00:01 | | 24 | INDEX SKIP SCAN | IDX$$_2FA30002 | 31 | | | 32 (0)| 00:00:01 | | 25 | TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 7258K| 768M| | 468K (1)| 01:33:45 | | 26 | SORT JOIN | | 1 | 4 | | 3 (67)| 00:00:01 | | 27 | VIEW | | 1 | 4 | | 2 (50)| 00:00:01 | | 28 | HASH UNIQUE | | 1 | 4 | | 2 (50)| 00:00:01 | | 29 | BITMAP INDEX STORAGE FAST FULL SCAN| W_EXCH_RATE_G_M3 | 147 | 588 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------------------- 103 rows selected. SQL> -- sql-runstats output for SQL with plan that has changed over time (some good, some bad) SQL> @sql-runstats Enter value for sql_id: 07favwdwwxqpd SQL Statement ------------------------------------------------------------------------------------------------------------------------------------------------------ SELECT /*+ USE_HASH (X1 X2 X3 X4 X5) */ W_GL_OTHER_FS.GL_ACCOUNT_ID , ACCT.ROW_WID GL_ACCOUNT_WID , ACCT.COST_CENTER_NUM COST_CENTER_NUM , ACCT.COST_CENTER_ATTRIB COST_CENTER_ATTRIB , ACCT.PROFIT_CENTER_NUM PROFIT_CENTER_NUM , ACCT.PROFIT_CENTER_ATTRIB PROFIT_CENTER_ATTRIB , BUDGT.ROW_WID BUDGT_ORG_WID , W_GL_OTHER_FS.CUSTOMER_ID , W_GL_OTHER_FS.CUSTOMER_FIN_PROFL_ID1 , W_GL_OTHER_FS.CUSTOMER_FIN_PROFL_ID2 , W_TERR_D.ROW_WID TERRITORY_WID , SALES_GRP.ROW_WID SALES_GROUP_ORG_WID , W_GL_OTHER_FS.CUSTOMER_CONTACT_ID , W_GL_OTHER_FS.CUSTOMER_SOLD_TO_LOC_ID , W_GL_OTHER_FS.CUSTOMER_SHIP_TO_LOC_ID , W_GL_OTHER_FS.CUSTOMER_BILL_TO_LOC_ID , W_GL_OTHER_FS.CUSTOMER_PAYER_LOC_ID , W_GL_OTHER_FS.SUPPLIER_ID , W_GL_OTHER_FS.SUPPLIER_ACCOUNT_ID , W_GL_OTHER_FS.SALES_REP_ID , W_GL_OTHER_FS.SERVICE_REP_ID , W_GL_OTHER_FS.ACCOUNT_REP_ID , W_GL_OTHER_FS.PURCH_REP_ID , W_GL_OTHER_FS.PRODUCT_ID , W_GL_OTHER_FS.SALES_PRODUCT_ID , W_GL_OTHER_FS.INVENTORY_PRODUCT_ID , W_GL_OTHER_FS.SUPPLIER_PRODUCT_ID , BUSN_LOC_COMPANY.ROW_WID COMPANY_LOC_WID , BUSN_LOC_PLANT.ROW_WID PLANT_LOC_WID , BUSN_LOC_SALES.ROW_WID SALES_OFC_LOC_WID , W_LEDGER_D.ROW_WID LEDGER_WID , W_GL_OTHER_FS.LEDGER_ID , W_GL_OTHER_FS.COMPANY_ORG_ID , BUSN_AREA.ROW_WID BUSN_AREA_ORG_WID , CTRL_AREA.ROW_WID CTRL_AREA_ORG_WID , FIN_AREA.ROW_WID FIN_AREA_ORG_WID , SALES.ROW_WID SALES_ORG_WID , PURCH.ROW_WID PURCH_ORG_WID , ISSUE.ROW_WID ISSUE_ORG_WID , X5.ROW_WID DOC_TYPE_ID , X1.ROW_WID CLRNG_DOC_TYPE_ID , X2.ROW_WID REF_DOC_TYPE_ID , X4.ROW_WID POSTING_TYPE_ID , X3.ROW_WID CLR_POST_TYPE_ID , W_GL_OTHER_FS.COST_CENTER_ID , W_GL_OTHER_FS.PROFIT_CENTER_ID , W_GL_OTHER_FS.DOC_STATUS_ID , W_BANK_D.ROW_WID BANK_WID , W_TAX_TYPE_D.ROW_WID TAX_TYPE_WID , W_PAYMENT_TERMS_D.ROW_WID PAY_TERMS_WID , W_PAYMENT_METHOD_D.ROW_WID PAYMENT_METHOD_WID , W_GL_OTHER_FS.TRANSACTION_DT , W_GL_OTHER_FS.POSTED_ON_DT , W_GL_OTHER_FS.ACCT_PERIOD_END_DT , W_GL_OTHER_FS.CONVERSION_DT , W_GL_OTHER_FS.ORDERED_ON_DT , W_GL_OTHER_FS.INVOICED_ON_DT , W_GL_OTHER_FS.PURCH_ORDER_DT , W_GL_OTHER_FS.SUPPLIER_ORDER_DT , W_GL_OTHER_FS.INVOICE_RECEIPT_DT , W_GL_OTHER_FS.CLEARED_ON_DT , W_GL_OTHER_FS.CLEARING_DOC_DT , W_GL_OTHER_FS.BASELINE_DT , W_GL_OTHER_FS.PLANNING_DT , W_GL_OTHER_FS.PAYMENT_DUE_DT , W_GL_OTHER_FS.OTHER_DOC_AMT , W_GL_OTHER_FS.OTHER_LOC_AMT , W_GL_OTHER_FS.XACT_QTY , W_GL_OTHER_FS.UOM_CODE , W_GL_OTHER_FS.DB_CR_IND , W_GL_OTHER_FS.ACCT_DOC_NUM , W_GL_OTHER_FS.ACCT_DOC_ITEM , W_GL_OTHER_FS.ACCT_DOC_SUB_ITEM , W_GL_OTHER_FS.CLEARING_DOC_NUM , W_GL_OTHER_FS.CLEARING_DOC_ITEM , W_GL_OTHER_FS.SALES_ORDER_NUM , W_GL_OTHER_FS.SALES_ORDER_ITEM , W_GL_OTHER_FS.SALES_SCH_LINE , W_GL_OTHER_FS.SALES_INVOICE_NUM , W_GL_OTHER_FS.SALES_INVOICE_ITEM , W_GL_OTHER_FS.PURCH_ORDER_NUM , W_GL_OTHER_FS.PURCH_ORDER_ITEM , W_GL_OTHER_FS.PURCH_INVOICE_NUM , W_GL_OTHER_FS.PURCH_INVOICE_ITEM , W_GL_OTHER_FS.CUST_PUR_ORD_NUM , W_GL_OTHER_FS.CUST_PUR_ORD_ITEM , W_GL_OTHER_FS.SPLR_ORDER_NUM , W_GL_OTHER_FS.SPLR_ORDER_ITEM , W_GL_OTHER_FS.REF_DOC_NUM , W_GL_OTHER_FS.REF_DOC_ITEM , W_GL_OTHER_FS.DOC_HEADER_TEXT , W_GL_OTHER_FS.LINE_ITEM_TEXT , W_GL_OTHER_FS.ALLOCATION_NUM , W_GL_OTHER_FS.FED_BALANCE_ID , W_GL_OTHER_FS.BALANCE_ID , W_GL_OTHER_FS.DELETE_FLG , W_GL_OTHER_FS.REJECT_FLG , W_GL_OTHER_FS.DOC_CURR_CODE , W_GL_OTHER_FS.LOC_CURR_CODE , W_GL_OTHER_FS.LOC_EXCH_RATE_TYPE , W_GL_OTHER_FS.LOC_EXCHANGE_RATE , W_GL_OTHER_FS.EXCHANGE_DT , U1.ROW_WID CREATED_BY_WID , U2.ROW_WID CHANGED_BY_WID , W_GL_OTHER_FS.CREATED_ON_DT , W_GL_OTHER_FS.CHANGED_ON_DT , W_GL_OTHER_FS.AUX1_CHANGED_ON_DT , W_GL_OTHER_FS.AUX2_CHANGED_ON_DT , W_GL_OTHER_FS.AUX3_CHANGED_ON_DT , W_GL_OTHER_FS.AUX4_CHANGED_ON_DT , W_GL_OTHER_FS.FIN_STMT_ITEM_CODE , W_GL_OTHER_FS.DATASOURCE_NUM_ID , W_GL_OTHER_FS.INTEGRATION_ID , W_GL_OTHER_FS.TENANT_ID , W_GL_OTHER_FS.X_CUSTOM , W_GL_OTHER_FS.ADJUSTMENT_FLG , W_MCAL_CONTEXT_G.MCAL_CAL_WID FROM W_GL_OTHER_FS, W_USER_D U1, W_USER_D U2, W_GL_ACCOUNT_D ACCT, W_BANK_D, W_XACT_TYPE_D X1, W_XACT_TYPE_D X2, W_XACT_TYPE_D X3, W_XACT_TYPE_D X4, W_XACT_TYPE_D X5, W_PAYMENT_TERMS_D, W_TAX_TYPE_D, W_TERR_D, W_PAYMENT_METHOD_D, W_LEDGER_D, W_INT_ORG_D BUDGT, W_INT_ORG_D PURCH, W_INT_ORG_D SALES, W_INT_ORG_D FIN_AREA, W_INT_ORG_D ISSUE, W_INT_ORG_D CTRL_AREA, W_INT_ORG_D BUSN_AREA, W_INT_ORG_D SALES_GRP, W_BUSN_LOCATION_D BUSN_LOC_COMPANY, W_BUSN_LOCATION_D BUSN_LOC_PLANT, W_BUSN_LOCATION_D BUSN_LOC_SALES, W_MCAL_CONTEXT_G WHERE W_GL_OTHER_FS.CREATED_BY_ID = U1.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = U1.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.CREATED_ON_DT >= U1.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.CREATED_ON_DT < U1.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.CHANGED_BY_ID = U2.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = U2.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.CHANGED_ON_DT >= U2.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.CHANGED_ON_DT < U2.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.GL_ACCOUNT_ID = ACCT.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = ACCT.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= ACCT.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < ACCT.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.BANK_ID = W_BANK_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_BANK_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= W_BANK_D.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < W_BANK_D.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.CLRNG_DOC_TYPE_ID = X1.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = X1.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.REF_DOC_TYPE_ID = X2.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = X2.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.CLR_POST_TYPE_ID = X3.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = X3.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTING_TYPE_ID = X4.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = X4.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.DOC_TYPE_ID = X5.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = X5.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.PAY_TERMS_ID = W_PAYMENT_TERMS_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_PAYMENT_TERMS_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= W_PAYMENT_TERMS_D.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < W_PAYMENT_TERMS_D.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.TAX_ID = W_TAX_TYPE_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_TAX_TYPE_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= W_TAX_TYPE_D.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < W_TAX_TYPE_D.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.TERRITORY_ID = W_TERR_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_TERR_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.PAYMENT_METHOD_ID = W_PAYMENT_METHOD_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_PAYMENT_METHOD_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.LEDGER_ID = W_LEDGER_D.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = W_LEDGER_D.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.BUDGT_ORG_ID = BUDGT.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = BUDGT.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= BUDGT.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < BUDGT.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.PURCH_ORG_ID = PURCH.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = PURCH.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= PURCH.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < PURCH.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.SALES_ORG_ID = SALES.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = SALES.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= SALES.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < SALES.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.FIN_AREA_ORG_ID = FIN_AREA.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = FIN_AREA.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= FIN_AREA.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < FIN_AREA.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.ISSUE_ORG_ID = ISSUE.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = ISSUE.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= ISSUE.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < ISSUE.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.CTRL_AREA_ORG_ID = CTRL_AREA.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = CTRL_AREA.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= CTRL_AREA.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < CTRL_AREA.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.BUSN_AREA_ORG_ID = BUSN_AREA.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = BUSN_AREA.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= BUSN_AREA.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < BUSN_AREA.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.SALES_GROUP_ORG_ID = SALES_GRP.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = SALES_GRP.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= SALES_GRP.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < SALES_GRP.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.COMPANY_LOC_ID = BUSN_LOC_COMPANY.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = BUSN_LOC_COMPANY.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= BUSN_LOC_COMPANY.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < BUSN_LOC_COMPANY.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.PLANT_LOC_ID = BUSN_LOC_PLANT.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = BUSN_LOC_PLANT.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= BUSN_LOC_PLANT.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < BUSN_LOC_PLANT.EFFECTIVE_TO_DT(+) AND W_GL_OTHER_FS.SALES_OFC_LOC_ID = BUSN_LOC_SALES.INTEGRATION_ID(+) AND W_GL_OTHER_FS.DATASOURCE_NUM_ID = BUSN_LOC_SALES.DATASOURCE_NUM_ID(+) AND W_GL_OTHER_FS.POSTED_ON_DT >= BUSN_LOC_SALES.EFFECTIVE_FROM_DT(+) AND W_GL_OTHER_FS.POSTED_ON_DT < BUSN_LOC_SALES.EFFECTIVE_TO_DT(+) AND W_MCAL_CONTEXT_G.LEDGER_ID(+) = W_GL_OTHER_FS.LEDGER_ID AND W_MCAL_CONTEXT_G.DATASOURCE_NUM_ID(+) = W_GL_OTHER_FS.DATASOURCE_NUM_ID AND W_MCAL_CONTEXT_G."CLASS"(+) = 'GL' Snap# Snap Time Inst# Plan HV Execs PIO LIO Rows CPU Elapsed IO Wait Cluster Wait ------ ---------------- ------ ------------ ------ --------- ---------- ---------- ------------ ------------ ----------- ------------- 15421 05/05/2012 03:30 2 3666499829 0 257724 257724 3234060 60.08082 58.057574 .042667 0 15420 05/05/2012 03:15 2 3666499829 0 1170529 1170529 14588500 285.283599 295.738775 1.361819 0 15419 05/05/2012 03:01 2 3666499829 1 122846 147835 580221 18.349222 64.834199 46.242125 0 15392 05/04/2012 20:15 2 852274140 1 9 50457 16170 1.091834 1.14211 .004501 0 15375 05/04/2012 16:00 2 852274140 1 43 64603 20003 1.253813 1.38549 .03108 0 15318 05/04/2012 01:45 2 3666499829 0 650930 650930 8165238 157.937976 161.691122 .17524 0 15317 05/04/2012 01:30 2 3666499829 1 891731 919585 10181161 209.284093 237.173268 20.657544 0 15294 05/03/2012 19:45 2 788391805 1 7305 111057 72557 4.070377 6.575593 2.380415 0 15278 05/03/2012 15:45 2 852274140 1 39 27526 9052 .962852 1.030705 .022839 0 15262 05/03/2012 11:45 2 852274140 1 23 21042 6644 .873867 .912138 .011316 0 15229 05/03/2012 03:30 2 4210675574 0 400117 400117 5030454 98.547027 98.390432 .065905 0 15228 05/03/2012 03:15 2 4210675574 1 1134213 1159668 13205301 268.870107 302.375391 23.841077 0 15198 05/02/2012 19:45 2 788391805 1 97769 106288 48456 4.113376 17.438696 13.389429 0 15182 05/02/2012 15:45 2 852274140 1 135 22494 8116 .917859 .995086 .026203 0 15133 05/02/2012 03:30 2 4210675574 0 800232 800232 10030636 193.454715 185.054861 .433776 0 15132 05/02/2012 03:15 2 4210675574 1 722532 749004 8085281 170.811084 193.644039 25.703437 0 15103 05/01/2012 20:00 2 852274140 1 7 18191 5921 2.715591 4.731119 .055862 0 15086 05/01/2012 15:45 2 852274140 1 31 17623 7427 .933858 1.011056 .017398 0 15070 05/01/2012 11:45 2 852274140 1 10 6316 3180 .78688 .811209 .005114 0 15033 05/01/2012 02:30 2 3666499829 0 539311 539311 6780897 127.858584 121.066331 .072535 0 15032 05/01/2012 02:15 2 3666499829 1 973240 999770 11217561 223.399073 256.328639 31.567541 0 14932 04/30/2012 01:15 2 2038373861 0 307037 307037 3852713 73.516799 72.265999 .057586 0 14931 04/30/2012 01:00 2 2038373861 0 1020221 1020221 12701760 249.127171 252.143517 1.303384 0 14930 04/30/2012 00:45 2 2038373861 1 153585 181120 1040721 27.095925 41.038261 14.365126 0 12967 04/09/2012 12:15 1 3745727445 1 35 952 2 .12398 .235037 .039162 .002601 10616 03/16/2012 00:45 1 1421984965 0 1023627 1023627 12876982 233.212476 241.61316 .821124 0 10615 03/16/2012 00:30 1 1421984965 1 284195 294176 2928361 56.861358 69.183142 9.372652 .00169 10469 03/14/2012 12:00 1 1547595336 1 65 2609 262 .328951 .447762 .018171 .005063 10433 03/14/2012 03:00 1 763554696 1 255217 266926 2565981 43.898367 63.239774 19.446533 .00446 10405 03/13/2012 20:00 1 4141756590 1 58 1838 60 .238964 .496941 .151925 .004007 10332 03/13/2012 01:45 1 2038373861 1 625277 638259 7248841 146.011782 158.457934 12.953461 .003593 10309 03/12/2012 20:00 1 3470732005 1 46 1568 62 .252961 .393117 .048872 .005839 10246 03/12/2012 04:15 1 4098479061 1 733498 746038 8602081 160.628556 164.934106 5.33546 .807163 10105 03/10/2012 16:00 1 3610025018 1 74 4942 1018 .667899 .797981 .022027 .002893 10050 03/10/2012 02:15 1 3919901543 1 162000 174738 1427001 27.853758 36.2544 9.10046 .823799 10009 03/09/2012 16:00 1 1110357027 1 28 2266 226 .415937 .53664 .016699 .004668 9955 03/09/2012 02:30 1 3919901543 0 975715 975715 12292430 177.746989 171.944597 .89029 0 9954 03/09/2012 02:15 1 3919901543 1 313481 325248 3342981 53.617871 58.053277 4.70644 .003838 9825 03/07/2012 18:15 1 646745574 1 1 3178 1105 .641903 .663717 .00053 .002075 SQL> -- ASH / AWR requires Diagnostics Pack (non-discounted cost of $100/named user per year + $22 maintenance) SQL>@ashrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 89294728 BIUAT 1 biuat1 1 row selected. Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 89294728 1 BIUAT biuat1 td01db01.tnd.us.comp.net 89294728 1 BIUAT biuat2 td01db04.tnd.us.comp.net 89294728 2 BIUAT biuat2 td01db04.tnd.us.comp.net Defaults to current database Using database id: 89294728 Enter instance numbers. Enter 'ALL' for all instances in a RAC cluster or explicitly specify list of instances (e.g., 1,2,3). Defaults to current instance. Using instance number(s): 1 ASH Samples in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Oldest ASH sample available: 15-Mar-12 00:14:40 [ 74103 mins in the past] Latest ASH sample available: 05-May-12 11:17:47 [ 0 mins in the past] Specify the timeframe to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter begin time for report: -- Valid input formats: -- To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 -- To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Defaults to -15 mins Enter value for begin_time: -15 Report begin time specified: -15 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: Report duration specified: Using 05-May-12 11:02:53 as report begin time Using 05-May-12 11:17:54 as report end time Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- In the 'Activity Over Time' section of the ASH report, -- the analysis period is divided into smaller slots -- and top wait events are reported in each of those slots. -- Default: -- The analysis period will be automatically split upto 10 slots -- complying to a minimum slot width of -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY. Specify Slot Width in seconds to use in the 'Activity Over Time' section: Defaults to a value as explained above: Slot Width specified: Specify Report Targets (using ashrpti.sql) to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- ASH Report can accept "Report Targets", -- like a particular SQL statement, or a particular SESSION, -- to generate the report on. If one or more report targets are -- specified, then the data used to generate the report will only be -- the ASH samples that pertain to ALL the specified report targets. -- Default: -- If none of the report targets are specified, -- then the target defaults to all activity in the database instance. Specify SESSION_ID (eg: from V$SESSION.SID) report target: Defaults to NULL: SESSION report target specified: Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: Defaults to NULL: (% and _ wildcards allowed) SQL report target specified: Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target specified: Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target: Defaults to NULL: SERVICE report target specified: Specify MODULE name (eg: from V$SESSION.MODULE) report target: Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified: Specify ACTION name (eg: from V$SESSION.ACTION) report target: Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified: Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target: Defaults to NULL: (% and _ wildcards allowed) CLIENT_ID report target specified: Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target: Defaults to NULL: (% and _ wildcards allowed) PLSQL_ENTRY report target specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is ashrpt_1_0505_1117.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Using the report name ashrpt_1_0505_1117.html Summary of All User Input ------------------------- Format : HTML DB Id : 89294728 Inst num : 1 Begin time : 05-May-12 11:02:53 End time : 05-May-12 11:17:54 Slot width : Default Report targets : 0 Report name : ashrpt_1_0505_1117.html ... ... Report written to ashrpt_1_0505_1117.html SQL> SQL> SQL>@awrsqrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 89294728 BIUAT 1 biuat1 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 89294728 1 BIUAT biuat1 td01db01.tnd.us.comp.net 89294728 1 BIUAT biuat2 td01db04.tnd.us.comp.net 89294728 2 BIUAT biuat2 td01db04.tnd.us.comp.net Using 89294728 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- biuat1 BIUAT 16367 05 May 2012 00:00 1 16368 05 May 2012 00:15 1 16369 05 May 2012 00:30 1 16370 05 May 2012 00:45 1 16371 05 May 2012 01:00 1 16372 05 May 2012 01:15 1 16373 05 May 2012 01:30 1 16374 05 May 2012 01:45 1 16375 05 May 2012 02:00 1 16376 05 May 2012 02:15 1 16377 05 May 2012 02:30 1 16378 05 May 2012 02:45 1 16379 05 May 2012 03:00 1 16380 05 May 2012 03:15 1 16381 05 May 2012 03:30 1 16382 05 May 2012 03:45 1 16383 05 May 2012 04:00 1 16384 05 May 2012 04:15 1 16385 05 May 2012 04:30 1 16386 05 May 2012 04:45 1 16387 05 May 2012 05:00 1 16388 05 May 2012 05:15 1 16389 05 May 2012 05:30 1 16390 05 May 2012 05:45 1 16391 05 May 2012 06:00 1 16392 05 May 2012 06:15 1 16393 05 May 2012 06:30 1 16394 05 May 2012 06:45 1 16395 05 May 2012 07:00 1 16396 05 May 2012 07:15 1 16397 05 May 2012 07:30 1 16398 05 May 2012 07:45 1 16399 05 May 2012 08:00 1 16400 05 May 2012 08:15 1 16401 05 May 2012 08:30 1 16402 05 May 2012 08:45 1 16403 05 May 2012 09:00 1 16404 05 May 2012 09:15 1 16405 05 May 2012 09:30 1 16406 05 May 2012 09:45 1 16407 05 May 2012 10:00 1 16408 05 May 2012 10:15 1 16409 05 May 2012 10:30 1 16410 05 May 2012 10:45 1 16411 05 May 2012 11:00 1 16412 05 May 2012 11:15 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 16411 Begin Snapshot Id specified: 16411 Enter value for end_snap: 16412 End Snapshot Id specified: 16412 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ Enter value for sql_id: 6x2sh0kvzac0t SQL ID specified: 6x2sh0kvzac0t Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_16411_16412.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Using the report name awrsqlrpt_1_16411_16412.html ... ... Report written to awrsqlrpt_1_16411_16412.html SQL> SQL>