Query Tuning.
- From: Vamshi Damidi <dbaprimatics@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 22 May 2010 10:05:29 -0400
I have a query
select a12.TIMEID,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(169,170,172,173,175,176,385,386,388,389,391,392,601,602,604,605,607,608 )
then a11.AMOUNT else NULL end) BALANCETRANSFER,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (107,108,109,323,324,325,539,540,541
) then a11.AMOUNT else NULL end) CARRYOVERCOSTBASIS1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(131,132,133,134,135,164,166,167,184,347,348,349,350,351,380,382,383,400,563,564,565,566,567,596,598,599,616)
then a11.AMOUNT else NULL end) CHARGEOFF,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(107,108,109,110,323,324,325,326,539,540,541,542 ) then a11.AMOUNT else NULL
end) COSTBASISRELIEF1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(110,148,149,150,152,162,163,186,326,364,365,366,368,378,379,402,542,580,581,582,584,594,595,618
) then a11.AMOUNT else NULL end) COSTBASISRELIEVEDDUETOREO1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(136,177,178,179,180,185,352,393,394,395,396,401,568,609,610,611,612,617 )
then a11.AMOUNT else NULL end) COSTBASISRELIEVEDDUETOSALES1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (101,317,533 ) then a11.AMOUNT else
NULL end) FUNDEDUPB,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (126,127,201,342,343,417,558,559,633
) then a11.AMOUNT else NULL end) IMPAIRMENT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (115,331,547 ) then a11.AMOUNT else
NULL end) INTERESTACCRETIONAIR,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (104,116,168,320,332,384,536,548,600
) then a11.AMOUNT else NULL end) NETCAPITALIZEDINTEREST,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (113,114,157,329,330,373,545,546,589
) then a11.AMOUNT else NULL end) NETINTERESTACCRUEDCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(121,122,123,124,125,337,338,339,340,341,553,554,555,556,557 ) then
a11.AMOUNT else NULL end) NETINTERESTPAYMENTS,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (117,118,119,333,334,335,549,550,551
) then a11.AMOUNT else NULL end) NETPRINCIPALPAYMENTS,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (111,327,543 ) then a11.AMOUNT else
NULL end) NETPRORATEDFEESCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (214,430,646 ) then a11.AMOUNT else
NULL end) NETRECOVERIES1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(106,184,185,186,187,322,400,401,402,403,538,616,617,618,619 ) then
a11.AMOUNT else NULL end) NEWDEFERREDFEESAMOUNT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(142,143,144,145,146,147,187,358,359,360,361,362,363,403,574,575,576,577,578,579,619
) then a11.AMOUNT else NULL end) PAIDOFFCOSTBASIS1,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (102,103,318,319,534,535 ) then
a11.AMOUNT else NULL end) PURCHASEADJUSTMENTCU,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in (105,321,537 ) then a11.AMOUNT else
NULL end) RECOURSEADJUSTMENT,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(195,196,197,198,199,200,411,412,413,414,415,416,627,628,629,630,631,632 )
then a11.AMOUNT else NULL end) REDESIGNATION,
sum(CASE WHEN a11.JOURNALENTRYTYPEID in
(128,129,130,171,344,345,346,387,560,561,562,603 ) then a11.AMOUNT else NULL
end) TOTALAMORTIZATIONCU
from mstrlarge26.TIMEDIMREL a12
join mstrlarge26.POSITIONTRANSACTION_PARTS a11
on (a12.POSTINGDATE = a11.POSTINGDATE and a12.EFFECTIVEDATE =
a11.EFFECTIVEDATE)
-- on (a12.POSTINGDATE,a12.EFFECTIVEDATE) =
(a11.POSTINGDATE,a11.EFFECTIVEDATE)
where a11.JOURNALENTRYTYPEID in
(169,170,172,173,175,176,385,386,388,389,391,392,601,602,604,605,607,608,
107,108,109,323,324,325,539,540,541,
131,132,133,134,135,164,166,167,184,347,348,349,350,351,380,382,383,400,563,564,565,566,567,596,598,599,616,
107,108,109,110,323,324,325,326,539,540,541,542,
110,148,149,150,152,162,163,186,326,364,365,366,368,378,379,402,542,580,581,582,584,594,595,618,
136,177,178,179,180,185,352,393,394,395,396,401,568,609,610,611,612,617,
101,317,533,
126,127,201,342,343,417,558,559,633,
115,331,547,
104,116,168,320,332,384,536,548,600,
113,114,157,329,330,373,545,546,589,
121,122,123,124,125,337,338,339,340,341,553,554,555,556,557,
117,118,119,333,334,335,549,550,551,
111,327,543,
214,430,646,
106,184,185,186,187,322,400,401,402,403,538,616,617,618,619,
142,143,144,145,146,147,187,358,359,360,361,362,363,403,574,575,576,577,578,579,619,
102,103,318,319,534,535,
105,321,537,
195,196,197,198,199,200,411,412,413,414,415,416,627,628,629,630,631,632,
128,129,130,171,344,345,346,387,560,561,562,603)
group by a12.TIMEID;
The above query runs fine with out the timedim table join.
when the timedim table is joined it takes 3 min.
Env. : 10.2.0.1 Enterprise Edition no patches applied on top it just basic
version.
Dataware house environment. has millions of records.
This is table count.
SQL> select count(*) from mstrlarge26.TIMEDIMREL;
COUNT(*)
----------
2244
SQL> select count(*) from mstrlarge26.POSITIONTRANSACTION_PARTS;
COUNT(*)
----------
24173149
sga_target : 15G
pga_target : 10g
Any hint would be great help.
Let me know if you need any more information.
Thanks,
Vamshi .D
Other related posts:
