Home » Fusion Middleware & Colab Suite » Business Intelligence » Problem with query (Oracle 10.2.0.4 (RHEL 5))
Problem with query [message #484536] Wed, 01 December 2010 05:23 Go to next message
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
Member
Hi I have a query used for a OBIEE report which takes around 3 and a half minutes to execute. But the client wants it to get executed in 30 seconds. But the problem is I cannot take an explain plan for the query through sqlplus and the explain plan taken out from toad can not be pasted here. I am typping the full query here. Can anyone help me in suggesting what needs to be done?

WITH 
SAWITH0 AS (select T156337.ROW_WID as c3,
     T156337.MCAL_YEAR_WID as c4,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c5,
     T156337.MCAL_PERIOD_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH1 AS (select Case when case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH0.c7 when 1 then SAWITH0.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH0.c4 ORDER BY case SAWITH0.c7 when 1 then SAWITH0.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH0.c3 as c3,
     SAWITH0.c4 as c4,
     SAWITH0.c6 as c5
from 
     SAWITH0),
SAWITH2 AS (select distinct min(SAWITH1.c1) over (partition by SAWITH1.c4)  as c1,
     min(SAWITH1.c2) over (partition by SAWITH1.c4, SAWITH1.c5)  as c2,
     SAWITH1.c3 as c3
from 
     SAWITH1),
SAWITH3 AS (select T156337.MCAL_QTR_WID as c3,
     T156337.MCAL_YEAR_WID as c4,
     T156337.MCAL_PERIOD_NAME as c5,
     T156337.ROW_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c7,
     T156337.MCAL_PERIOD_WID as c8,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH4 AS (select Case when case SAWITH3.c7 when 1 then SAWITH3.c6 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH3.c7 when 1 then SAWITH3.c6 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH3.c9 when 1 then SAWITH3.c6 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH3.c4 ORDER BY case SAWITH3.c9 when 1 then SAWITH3.c6 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH3.c3 as c3,
     SAWITH3.c4 as c4,
     SAWITH3.c5 as c5,
     SAWITH3.c8 as c6
from 
     SAWITH3),
SAWITH5 AS (select distinct min(SAWITH4.c1) over (partition by SAWITH4.c4)  as c1,
     min(SAWITH4.c2) over (partition by SAWITH4.c4, SAWITH4.c6)  as c2,
     SAWITH4.c3 as c3,
     SAWITH4.c4 as c4,
     SAWITH4.c5 as c5
from 
     SAWITH4),
SAWITH6 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               SAWITH5.c4 as c4,
               SAWITH5.c3 as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH5.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH5.c3 ASC) as c6
          from 
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
               W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */ ,
               SAWITH2,
               SAWITH5
          where  ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146219.PERIOD_END_DT_WID = SAWITH2.c3 and SAWITH5.c1 = SAWITH2.c1 and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and SAWITH5.c5 = 'NOV-09' and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH5.c2 >= SAWITH2.c2 ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH5.c3, SAWITH5.c4
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH7 AS (select T156337.ROW_WID as c3,
     T156337.MCAL_YEAR_WID as c4,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c5,
     T156337.MCAL_PERIOD_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH8 AS (select Case when case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH7.c7 when 1 then SAWITH7.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH7.c4 ORDER BY case SAWITH7.c7 when 1 then SAWITH7.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH7.c3 as c3,
     SAWITH7.c4 as c4,
     SAWITH7.c6 as c5
from 
     SAWITH7),
SAWITH9 AS (select distinct min(SAWITH8.c1) over (partition by SAWITH8.c4)  as c1,
     min(SAWITH8.c2) over (partition by SAWITH8.c4, SAWITH8.c5)  as c2,
     SAWITH8.c3 as c3
from 
     SAWITH8),
SAWITH10 AS (select T156337.MCAL_QTR_WID as c3,
     T156337.MCAL_PERIOD_NAME as c4,
     T156337.MCAL_YEAR_WID as c5,
     T156337.ROW_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c7,
     T156337.MCAL_PERIOD_WID as c8,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH11 AS (select Case when case SAWITH10.c7 when 1 then SAWITH10.c6 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH10.c7 when 1 then SAWITH10.c6 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH10.c9 when 1 then SAWITH10.c6 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH10.c5 ORDER BY case SAWITH10.c9 when 1 then SAWITH10.c6 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH10.c3 as c3,
     SAWITH10.c4 as c4,
     SAWITH10.c5 as c5,
     SAWITH10.c8 as c6
from 
     SAWITH10),
SAWITH12 AS (select distinct min(SAWITH11.c1) over (partition by SAWITH11.c5)  as c1,
     min(SAWITH11.c2) over (partition by SAWITH11.c5, SAWITH11.c6)  as c2,
     SAWITH11.c3 as c3,
     SAWITH11.c4 as c4,
     SAWITH11.c5 as c5
from 
     SAWITH11),
SAWITH13 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(case  when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               SAWITH12.c5 as c4,
               SAWITH12.c3 as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH12.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH12.c3 ASC) as c6
          from 
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
               W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
               SAWITH9,
               SAWITH12
          where  ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = SAWITH9.c3 and T91707.LEDGER_WID = T146058.ROW_WID and SAWITH12.c1 = SAWITH9.c1 and T91707.DELETE_FLG = 'N' and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and SAWITH12.c4 = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH12.c2 >= SAWITH9.c2 ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH12.c3, SAWITH12.c5
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH14 AS (select T156337.ROW_WID as c3,
     T156337.MCAL_QTR_WID as c4,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c5,
     T156337.MCAL_PERIOD_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH15 AS (select Case when case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH14.c7 when 1 then SAWITH14.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH14.c4 ORDER BY case SAWITH14.c7 when 1 then SAWITH14.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH14.c3 as c3,
     SAWITH14.c4 as c4,
     SAWITH14.c6 as c5
from 
     SAWITH14),
SAWITH16 AS (select distinct min(SAWITH15.c1) over (partition by SAWITH15.c4)  as c1,
     min(SAWITH15.c2) over (partition by SAWITH15.c4, SAWITH15.c5)  as c2,
     SAWITH15.c3 as c3
from 
     SAWITH15),
SAWITH17 AS (select T156337.MCAL_QTR_WID as c3,
     T156337.MCAL_PERIOD_NAME as c4,
     T156337.MCAL_YEAR_WID as c5,
     T156337.ROW_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c7,
     T156337.MCAL_PERIOD_WID as c8,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH18 AS (select Case when case SAWITH17.c7 when 1 then SAWITH17.c6 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH17.c7 when 1 then SAWITH17.c6 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH17.c9 when 1 then SAWITH17.c6 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH17.c3 ORDER BY case SAWITH17.c9 when 1 then SAWITH17.c6 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH17.c3 as c3,
     SAWITH17.c4 as c4,
     SAWITH17.c5 as c5,
     SAWITH17.c8 as c6
from 
     SAWITH17),
SAWITH19 AS (select distinct min(SAWITH18.c1) over (partition by SAWITH18.c3)  as c1,
     min(SAWITH18.c2) over (partition by SAWITH18.c3, SAWITH18.c6)  as c2,
     SAWITH18.c3 as c3,
     SAWITH18.c4 as c4,
     SAWITH18.c5 as c5
from 
     SAWITH18),
SAWITH20 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               SAWITH19.c5 as c4,
               SAWITH19.c3 as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH19.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH19.c3 ASC) as c6
          from 
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
               W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */ ,
               SAWITH16,
               SAWITH19
          where  ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146219.PERIOD_END_DT_WID = SAWITH16.c3 and SAWITH19.c1 = SAWITH16.c1 and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and SAWITH19.c4 = 'NOV-09' and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH19.c2 >= SAWITH16.c2 ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH19.c3, SAWITH19.c5
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH21 AS (select T156337.ROW_WID as c3,
     T156337.MCAL_QTR_WID as c4,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c5,
     T156337.MCAL_PERIOD_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH22 AS (select Case when case SAWITH21.c5 when 1 then SAWITH21.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH21.c5 when 1 then SAWITH21.c3 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH21.c7 when 1 then SAWITH21.c3 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH21.c4 ORDER BY case SAWITH21.c7 when 1 then SAWITH21.c3 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH21.c3 as c3,
     SAWITH21.c4 as c4,
     SAWITH21.c6 as c5
from 
     SAWITH21),
SAWITH23 AS (select distinct min(SAWITH22.c1) over (partition by SAWITH22.c4)  as c1,
     min(SAWITH22.c2) over (partition by SAWITH22.c4, SAWITH22.c5)  as c2,
     SAWITH22.c3 as c3
from 
     SAWITH22),
SAWITH24 AS (select T156337.MCAL_QTR_WID as c3,
     T156337.MCAL_YEAR_WID as c4,
     T156337.MCAL_PERIOD_NAME as c5,
     T156337.ROW_WID as c6,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c7,
     T156337.MCAL_PERIOD_WID as c8,
     ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from 
     W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ 
where  ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH25 AS (select Case when case SAWITH24.c7 when 1 then SAWITH24.c6 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH24.c7 when 1 then SAWITH24.c6 else NULL end  ASC NULLS LAST ) end as c1,
     Case when case SAWITH24.c9 when 1 then SAWITH24.c6 else NULL end  is not null then Rank() OVER ( PARTITION BY SAWITH24.c3 ORDER BY case SAWITH24.c9 when 1 then SAWITH24.c6 else NULL end  ASC NULLS LAST ) end as c2,
     SAWITH24.c3 as c3,
     SAWITH24.c4 as c4,
     SAWITH24.c5 as c5,
     SAWITH24.c8 as c6
from 
     SAWITH24),
SAWITH26 AS (select distinct min(SAWITH25.c1) over (partition by SAWITH25.c3)  as c1,
     min(SAWITH25.c2) over (partition by SAWITH25.c3, SAWITH25.c6)  as c2,
     SAWITH25.c3 as c3,
     SAWITH25.c4 as c4,
     SAWITH25.c5 as c5
from 
     SAWITH25),
SAWITH27 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(case  when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               SAWITH26.c4 as c4,
               SAWITH26.c3 as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH26.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH26.c3 ASC) as c6
          from 
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
               W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
               SAWITH23,
               SAWITH26
          where  ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = SAWITH23.c3 and T91707.LEDGER_WID = T146058.ROW_WID and SAWITH26.c1 = SAWITH23.c1 and T91707.DELETE_FLG = 'N' and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and SAWITH26.c5 = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH26.c2 >= SAWITH23.c2 ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH26.c3, SAWITH26.c4
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH28 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               T156337.MCAL_YEAR_WID as c4,
               T156337.MCAL_QTR_WID as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, T156337.MCAL_QTR_WID ASC) as c6
          from 
               W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ ,
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
               W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */ 
          where  ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.PERIOD_END_DT_WID = T156337.ROW_WID and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and T156337.MCAL_CAL_NAME = 'AHS Accounting' and T156337.MCAL_PERIOD_NAME = 'NOV-09' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID, T156337.MCAL_YEAR_WID
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH29 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     (select sum(case  when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
               T91397.GL_ACCOUNT_NUM as c2,
               T91397.GL_ACCOUNT_NAME as c3,
               T156337.MCAL_YEAR_WID as c4,
               T156337.MCAL_QTR_WID as c5,
               ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, T156337.MCAL_QTR_WID ASC) as c6
          from 
               W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ ,
               W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
               W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
               W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
               W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ 
          where  ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = T156337.ROW_WID and T91707.DELETE_FLG = 'N' and T91707.LEDGER_WID = T146058.ROW_WID and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and T156337.MCAL_CAL_NAME = 'AHS Accounting' and T156337.MCAL_PERIOD_NAME = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) ) 
          group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID, T156337.MCAL_YEAR_WID
     ) D1
where  ( D1.c6 = 1 ) ),
SAWITH30 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5,
     D1.c6 as c6,
     D1.c7 as c7,
     D1.c8 as c8,
     D1.c9 as c9,
     D1.c10 as c10,
     D1.c11 as c11,
     D1.c12 as c12,
     D1.c13 as c13
from 
     (select case  when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end  as c1,
               case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end  as c2,
               SAWITH29.c1 as c3,
               SAWITH28.c1 as c4,
               SAWITH28.c1 - SAWITH29.c1 as c5,
               SAWITH27.c1 as c6,
               SAWITH20.c1 as c7,
               SAWITH20.c1 - SAWITH27.c1 as c8,
               SAWITH13.c1 as c9,
               SAWITH6.c1 as c10,
               SAWITH6.c1 - SAWITH13.c1 as c11,
               case  when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end  as c12,
               case  when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end  as c13,
               ROW_NUMBER() OVER (PARTITION BY case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end , case  when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end , case  when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end , case  when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end  ORDER BY case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end  ASC, case  when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end  ASC, case  when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end  ASC, case  when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end  ASC) as c14
          from 
               (
                    (
                         (
                              (
                                   SAWITH6 full outer join SAWITH13 On nvl(SAWITH6.c5 , 88.0) = nvl(SAWITH13.c5 , 88.0) and nvl(SAWITH6.c5 , 99.0) = nvl(SAWITH13.c5 , 99.0) and nvl(SAWITH6.c2 , 'q') = nvl(SAWITH13.c2 , 'q') and nvl(SAWITH6.c2 , 'z') = nvl(SAWITH13.c2 , 'z') and nvl(SAWITH6.c3 , 'q') = nvl(SAWITH13.c3 , 'q') and nvl(SAWITH6.c3 , 'z') = nvl(SAWITH13.c3 , 'z')) full outer join SAWITH20 On nvl(SAWITH20.c2 , 'q') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 end  , 'q') and nvl(SAWITH20.c2 , 'z') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 end  , 'z') and nvl(SAWITH20.c3 , 'q') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end  , 'q') and nvl(SAWITH20.c3 , 'z') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end  , 'z') and nvl(SAWITH20.c5 , 88.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end  , 88.0) and nvl(SAWITH20.c5 , 99.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end  , 99.0)) full outer join SAWITH27 On nvl(SAWITH27.c2 , 'q') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end  , 'q') and nvl(SAWITH27.c2 , 'z') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end  , 'z') and nvl(SAWITH27.c3 , 'q') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 end  , 'q') and nvl(SAWITH27.c3 , 'z') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 end  , 'z') and nvl(SAWITH27.c5 , 88.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 end  , 88.0) and nvl(SAWITH27.c5 , 99.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 end  , 99.0)) full outer join SAWITH28 On nvl(SAWITH28.c2 , 'q') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 end  , 'q') and nvl(SAWITH28.c2 , 'z') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 end  , 'z') and nvl(SAWITH28.c3 , 'q') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 end  , 'q') and nvl(SAWITH28.c3 , 'z') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 end  , 'z') and nvl(SAWITH28.c5 , 88.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 end  , 88.0) and nvl(SAWITH28.c5 , 99.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 end  , 99.0)) full outer join SAWITH29 On nvl(SAWITH29.c2 , 'q') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH28.c2 is not null then SAWITH28.c2 end  , 'q') and nvl(SAWITH29.c2 , 'z') = nvl(case  when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH28.c2 is not null then SAWITH28.c2 end  , 'z') and nvl(SAWITH29.c3 , 'q') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH28.c3 is not null then SAWITH28.c3 end  , 'q') and nvl(SAWITH29.c3 , 'z') = nvl(case  when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH28.c3 is not null then SAWITH28.c3 end  , 'z') and nvl(SAWITH29.c5 , 88.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH28.c5 is not null then SAWITH28.c5 end  , 88.0) and nvl(SAWITH29.c5 , 99.0) = nvl(case  when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH28.c5 is not null then SAWITH28.c5 end  , 99.0)
     ) D1
where  ( D1.c14 = 1 ) )
select SAWITH30.c1 as c1,
     SAWITH30.c2 as c2,
     SAWITH30.c3 as c3,
     SAWITH30.c4 as c4,
     SAWITH30.c5 as c5,
     SAWITH30.c6 as c6,
     SAWITH30.c7 as c7,
     SAWITH30.c8 as c8,
     SAWITH30.c9 as c9,
     SAWITH30.c10 as c10,
     SAWITH30.c11 as c11,
     SAWITH30.c12 as c12,
     SAWITH30.c13 as c13
from 
     SAWITH30
order by c1, c2

Re: Problem with query [message #484540 is a reply to message #484536] Wed, 01 December 2010 05:35 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
"Hi I have a query used for a OBIEE report which takes around 3 and a half minutes to execute. But the client wants it to get executed in 30 seconds. But the problem is I cannot take an explain plan for the query through sqlplus and the explain plan taken out from toad can not be pasted here. I am typping the full query here. Can anyone help me in suggesting what needs to be done?"


I am unable to read the code like this

Sriram
Re: Problem with query [message #484542 is a reply to message #484540] Wed, 01 December 2010 05:43 Go to previous messageGo to next message
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
Member
Hi Sriram Sir,
Please suggest how to paste the entire code.
Re: Problem with query [message #484606 is a reply to message #484542] Wed, 01 December 2010 23:42 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
1) save it in a file and execute it
2) Get the explain plan

And paste here Before that read the performance tuning guide for the requirements

Sriram
Previous Topic: .rpd file moving Window into Linux
Next Topic: String and Number Parameters in BI
Goto Forum:
  


Current Time: Thu Mar 28 18:35:14 CDT 2024