Re: Query Tuning.
- From: Vamshi Damidi <dbaprimatics@xxxxxxxxx>
- To: Timothy Kras <timk131@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 22 May 2010 11:23:04 -0400
there are indexes i have
INDEX_NAME INDEX_NAME
COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------
---------------------------------------- ---------------
IND_PTP_3 IND_PTP_3
COMPONENTTYPE 1
IND_PTP_3 IND_PTP_3
TRANSACTIONTYPE 2
what kind of index do you suggest this is enterprise edition
and table is POSITIONTRANSACTION_PARTS is partitioned.
On Sat, May 22, 2010 at 11:18 AM, Timothy Kras <timk131@xxxxxxxxx> wrote:
> Vamshi,
> Are POSTINGDATE and EFFECTIVEDATE columns indexed? An 'explain plan' of the
> query - with and without the join - should help reveal what is happening.
> Regards,
> Tim
>
> On Sat, May 22, 2010 at 9:05 AM, Vamshi Damidi <dbaprimatics@xxxxxxxxx>wrote:
>
>> 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:
