Re: Performance Problem

In message <458a58c8$0$427$4d4ef98e_at_read.news.ch.uu.net>, COAST <coast_at_cedes.com> writes
>Hello NG
> 
>I have got a perfomance problem with the following SQL statement
>(takes about 15min)
> 
>SELECT count(*)
>FROM SA_DOC_POS T0,
>NTD_SALES_DOCU_STATUS T2,
>PA_PART_GENERAL T3,
>NTD_SALES_DOCU_TYPE T6,
>SA_DOC T1,
>PA_GROUP_PART T4
>WHERE T1.DOCUMENT_DATE <= to_date('21.12.2006', 'DD.MM.YYYY')
>AND T1.DOCUMENT_DATE >= to_date('01.01.1995', 'DD.MM.YYYY')
>AND (T2.STATUS = 'fixed' OR T2.STATUS = 'done')
>AND T4.nt_sales_class1_id = 2
>AND (T6.SHORTCUT = 'IN' OR T6.SHORTCUT = 'CN' OR T6.SHORTCUT = 'WD')
>AND T1.CO_GRP_COMP_ID = 1
>AND T1.NTD_SALES_DOCU_STATUS_ID = T2.NTD_SALES_DOCU_STATUS_ID
>AND t0.PA_PART_GENERAL_ID = T3.PA_PART_GENERAL_ID
>AND T1.NTD_SALES_DOCU_TYPE_ID = T6.NTD_SALES_DOCU_TYPE_ID
>AND t0.SA_DOC_ID = T1.SA_DOC_ID
>AND T3.PA_GROUP_PART_ID = T4.PA_GROUP_PART_ID
> 
>Leaving "AND T4.nt_sales_class1_id = 2" the performance is excellent,
>so there must be a problem with T4 (about 3 sec) !!!!
> 
>First one would think there is no index on T4.nt_sales_class1_id, but
>there is one !!!
> 
>No my question:
>- Where to start to solve this problem ?
>- Why is the query slower giving the query a filter  which reduces
>(should reduce) the amount of data over an index ?
> 
>Quantity Structure:
> 
>T0: 412396
>T1: 219236
>T2: 4
>T3: 83820
>T4: 13991
>T6: 6
> 
>Thanks for any hint (maybe also in general handling performence
>problems).
>I have got the booklet "Oracle SQL Tuning" of Marc Gurry, actually it's
>a nice booklet,
>but now I not able going further on.
> 
>Peter

Try running an explain plan and/or sql trace and post the output here. Are your statistics up to date?
What oracle version?

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>