SYS@BIUAT1:TD01DB01> select dbms_sqltune.report_sql_monitor(type=>'HTML', sql_id=>'6x2sh0kvzac0t') monitor_report from dual;
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
Global Information: 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 |
Id | Operation | Name |
Estimated
Rows |
Cost |
Active Period
(34s) |
Execs | Rows |
Memory
(Max) |
Temp
(Max) |
IO Requests |
Cell Offload
Efficiency |
CPU Activity | Wait Activity | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
. |
0 | SELECT STATEMENT |
. |
. |
. |
|
1 | 307 |
. |
. |
|
. |
. |
|||||||||||
. |
1 | . WINDOW BUFFER |
. |
4M | 981K |
|
1 | 307 | 70.0KB |
. |
|
. |
. |
|||||||||||
. |
2 | .. SORT GROUP BY |
. |
4M | 981K |
|
1 | 307 | 94.0KB |
. |
|
|
|
|||||||||||
. |
3 | ... HASH JOIN |
. |
4M | 471K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
4 | .... TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 1973 | 138 |
|
1 | 1973 |
. |
. |
|
. |
. |
|||||||||||
. |
5 | .... HASH JOIN |
. |
4M | 471K |
|
1 | 1M | 1.3MB |
. |
|
|
|
|||||||||||
. |
6 | ..... TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 729 | 138 |
|
1 | 729 |
. |
. |
|
. |
. |
|||||||||||
. |
7 | ..... HASH JOIN |
. |
4M | 470K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
8 | ...... TABLE ACCESS STORAGE FULL | W_HIERARCHY_D | 303 | 138 |
|
1 | 304 |
. |
. |
|
. |
. |
|||||||||||
. |
9 | ...... HASH JOIN |
. |
4M | 470K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
10 | ....... TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 1652 | 446 |
|
1 | 1438 |
. |
. |
|
. |
. |
|||||||||||
. |
11 | ....... HASH JOIN |
. |
4M | 470K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
12 | ........ TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 605 | 446 |
|
1 | 601 |
. |
. |
|
. |
. |
|||||||||||
. |
13 | ........ HASH JOIN |
. |
4M | 469K |
|
1 | 1M | 1.3MB |
. |
|
|
|
|||||||||||
. |
14 | ......... TABLE ACCESS STORAGE FULL | W_INT_ORG_D | 857 | 30 |
|
1 | 856 |
. |
. |
|
. |
. |
|||||||||||
. |
15 | ......... HASH JOIN |
. |
4M | 469K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
16 | .......... TABLE ACCESS STORAGE FULL | WC_ACCT_HIER_DH | 363 | 7 |
|
1 | 363 |
. |
. |
|
. |
. |
|||||||||||
. |
17 | .......... HASH JOIN |
. |
6M | 469K |
|
1 | 1M | 1.3MB |
. |
|
. |
. |
|||||||||||
. |
18 | ........... TABLE ACCESS STORAGE FULL | W_GL_SEGMENT_D | 202 | 446 |
|
1 | 189 |
. |
. |
|
. |
. |
|||||||||||
. |
19 | ........... HASH JOIN |
. |
6M | 469K |
|
1 | 1M | 1.2MB |
. |
|
|
|
|||||||||||
. |
20 | ............ TABLE ACCESS STORAGE FULL | W_LEDGER_D | 11 | 6 |
|
1 | 11 |
. |
. |
|
. |
. |
|||||||||||
. |
21 | ............ MERGE JOIN |
. |
7M | 469K |
|
1 | 2M |
. |
. |
|
|
|
|||||||||||
. |
22 | ............. HASH JOIN |
. |
7M | 469K |
|
1 | 2M | 1.2MB |
. |
|
|
|
|||||||||||
. |
23 | .............. TABLE ACCESS BY INDEX ROWID | W_MCAL_DAY_D | 31 | 37 |
|
1 | 31 |
. |
. |
|
. |
. |
|||||||||||
. |
24 | ............... INDEX SKIP SCAN | IDX$$_2FA30002 | 31 | 32 |
|
1 | 31 |
. |
. |
|
. |
. |
|||||||||||
. |
25 | .............. TABLE ACCESS STORAGE FULL | WC_GL_BALANCE_A_KM | 7M | 469K |
|
1 | 43M |
. |
. |
|
63.37% |
|
|
||||||||||
. |
26 | ............. SORT JOIN |
. |
1 | 3 |
|
2M | 2M | 2.0KB |
. |
|
. |
. |
|||||||||||
. |
27 | .............. VIEW |
. |
1 | 2 |
|
1 | 1 |
. |
. |
|
. |
. |
|||||||||||
. |
28 | ............... HASH UNIQUE |
. |
1 | 2 |
|
1 | 1 | 470.0KB |
. |
|
. |
. |
|||||||||||
. |
29 | ................ BITMAP INDEX STORAGE FAST FULL SCAN | W_EXCH_RATE_G_M3 | 147 | 1 |
|
1 | 1 |
. |
. |
|
. |
. |