Re: Re: Performance Problem

Dear Jonathan

thanks a lot for your trick "+0".

Now the performance is much better, so I had just deleted the index and things goes well!

But I am a little afraid just deleteing the index, because there is this rule "All FK's should have an index". We're still using 8i, so really all FK's should have an index ?(becuause of table locking).

Maybe another SQL hase to use the index for performing well? Or can you say if an FK does not need an index then all SQL statements will have got a good performance using this FK ?

I analized the NT_SALES_CLASS1_ID and found that a lot of the ID's have a NULL value. Is that the reason, why you shouldn't use an index, why we encounter this performance problem ?

  COUNT(*) NT_SALES_CLASS1_ID

---------- ------------------
      224                  1
    1103                  2
    2484                  3
  10180              NULL

I've got another relation on the same table where I have exact the same problem, but I can't (dont want) delete the index, because its a UNIQUE one (because of 1 <-->> 0...1)

This index has also a lot of NULL values.

How to solve this problem?

Peter

P.S.
Maybe I posted this request twice due to a crash of my e-mail prog, sorry if it is like this.

>>> Jonathan Lewis< jonathan_at_jlcomp.demon.co.uk > 21.12.2006 11:25 >>>

"COAST" < coast_at_cedes.com > wrote in message news:458a58c8$0$427$4d4ef98e_at_read.news.ch.uu.net ... 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

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.

Start by looking at the execution plan and it's predicates. Assuming 9i + , suitably installed:

explain plan for
select count(*) ....

select * from table(dbms_xplan.display);

The fact that you have an index on T4 is probably an (accidental) underlying cause of the problem. The optimizer is probably using the index when it shouldn't, possibly starting the query on this table rather than ending here, possibly just using the index in an unsuitable join. The structure of the execution plan should tell you the answer.

Dirty trick - to disable the index if that is the problem, use the good old-fashioned
method from the RBO:

AND T4.nt_sales_class1_id+0 = 2

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com 

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html 

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html