WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB NameDB IdInstanceInst numStartup TimeReleaseRAC
BIUAT89294728biuat1121-Apr-12 22:0411.2.0.3.0YES

Snap IdSnap TimeSessionsCursors/Session
Begin Snap:1641105-May-12 11:00:1089 2.4
End Snap:1641205-May-12 11:15:0480 2.6
Elapsed:  14.91 (mins)  
DB Time:  6.32 (mins)  

SQL Summary

SQL IdElapsed Time (ms)ModuleActionSQL Text
6x2sh0kvzac0t34,063 sqlplus.exe   select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c1...


Back to Top

SQL ID: 6x2sh0kvzac0t

#Plan Hash ValueTotal Elapsed Time(ms)Executions1st Capture Snap IDLast Capture Snap ID
1324960632534,06311641216412


Back to Top

Plan 1(PHV: 3249606325)

Back to Top

Plan Statistics

Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms)34,06334,063.398.98
CPU Time (ms)33,11633,115.9717.89
Executions1  
Buffer Gets1,729,7171,729,717.0033.07
Disk Reads1,722,9971,722,997.0032.97
Parse Calls11.000.04
Rows307307.00 
User I/O Wait Time (ms)804  
Cluster Wait Time (ms)0  
Application Wait Time (ms)0  
Concurrency Wait Time (ms)1,269,932  
Invalidations0  
Version Count1  
Sharable Mem(KB)146  

Back to Plan 1(PHV: 3249606325)
Back to Top

Execution Plan

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

Back to Plan 1(PHV: 3249606325)
Back to Top

Full SQL Text

SQL IdSQL Text
6x2sh0kvzac0tselect /* 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_P ER_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


Back to Top