please check that your table and index stats are up to date
and run the explain plan with and without the +0, that will tell us
what the +0 changes in the execution plan.
with that information, you will get better suggestions on how to get
around the performance issue. So far, Jonathan gave you a good guess.
You need more
COAST wrote:
> 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
>
> --____KLOQFCKXQVYQSWPLANEH____
> Content-Type: multipart/related; boundary="____KKQRIWFUWKTGYLHLXBIC____"
> X-Google-AttachSize: 7652
>
> --____KKQRIWFUWKTGYLHLXBIC____
> Content-Type: text/html; charset=windows-1252
> Content-Transfer-Encoding: quoted-printable
>
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD>
> <BODY style="MARGIN: 4px 4px 1px; FONT: 10pt Microsoft Sans Serif; COLOR: #000000">
> <DIV>Dear Jonathan<BR><BR>thanks a lot for your trick "+0".<BR><BR>Now the performance is much better, so I had just deleted the index<BR>and things goes well!<BR><BR>But I am a little afraid just deleteing the index,<BR>because there is this rule "All FK's should have an index".<BR>We're still using 8i, so really all FK's should have an index ?(becuause of table locking).<BR><BR>Maybe another SQL hase to use the index for performing well?</DIV>
> <DIV>Or can you say if an FK does not need an index then all SQL statements</DIV>
> <DIV>will have got a good performance using this FK ?</DIV>
> <DIV> </DIV>
> <DIV>I analized the NT_SALES_CLASS1_ID and found that a lot of the ID's have a NULL value.</DIV>
> <DIV>Is that the reason, why you shouldn't use an index, why we encounter this performance problem ?<BR><BR> COUNT(*) NT_SALES_CLASS1_ID<BR>---------- ------------------<BR> 224 1<BR> 1103 2<BR> 2484 3</DIV>
> <DIV> 10180 NULL</DIV>
> <DIV> </DIV>
> <DIV>I've got another relation on the same table where I have exact the same problem,</DIV>
> <DIV>but I can't (dont want) delete the index, because its a UNIQUE one</DIV>
> <DIV>(because of 1 <-->> 0...1)</DIV>
> <DIV> </DIV>
> <DIV>This index has also a lot of NULL values.</DIV>
> <DIV> </DIV>
> <DIV>How to solve this problem?</DIV>
> <DIV> </DIV>
> <DIV>Peter</DIV>
> <DIV> </DIV>
> <DIV>P.S.</DIV>
> <DIV>Maybe I posted this request twice due to a crash of my e-mail prog,</DIV>
> <DIV>sorry if it is like this.</DIV>
> <DIV> </DIV>
> <DIV><BR><BR>>>> Jonathan Lewis<<U> <A href="mailto:jonathan_at_jlcomp.demon.co.uk">jonathan_at_jlcomp.demon.co.uk</A></U> > 21.12.2006 11:25 >>><BR><BR><BR>"COAST" <<U> <A href="mailto:coast_at_cedes.com">coast_at_cedes.com</A></U> > wrote in message <BR><U><A href="mailto:news:458a58c8$0$427$4d4ef98e_at_read.news.ch.uu.net">news:458a58c8$0$427$4d4ef98e_at_read.news.ch.uu.net</A></U> ...<BR>Hello NG<BR><BR>I have got a perfomance problem with the following SQL statement<BR>(takes about 15min)<BR><BR>SELECT count(*)<BR>FROM SA_DOC_POS T0,<BR>NTD_SALES_DOCU_STATUS T2,<BR>PA_PART_GENERAL T3,<BR>NTD_SALES_DOCU_TYPE T6,<BR>SA_DOC T1,<BR>PA_GROUP_PART T4<BR>WHERE T1.DOCUMENT_DATE <= to_date('21.12.2006', 'DD.MM.YYYY')<BR>AND T1.DOCUMENT_DATE >= to_date('01.01.1995', 'DD.MM.YYYY')<BR>AND (T2.STATUS = 'fixed' OR T2.STATUS = 'done')<BR>AND T4.nt_sales_class1_id = 2<BR>AND (T6.SHORTCUT = 'IN' OR T6.SHORTCUT = 'CN' OR T6.SHORTCUT = 'WD')<BR>AND T1.CO_GRP_COMP_ID = 1<BR>AND T1.NTD_SALES_DOCU_STATUS_
ID = T2.NTD_SALES_DOCU_STATUS_ID<BR>AND t0.PA_PART_GENERAL_ID = T3.PA_PART_GENERAL_ID<BR>AND T1.NTD_SALES_DOCU_TYPE_ID = T6.NTD_SALES_DOCU_TYPE_ID<BR>AND t0.SA_DOC_ID = T1.SA_DOC_ID<BR>AND T3.PA_GROUP_PART_ID = T4.PA_GROUP_PART_ID<BR><BR>Leaving "AND T4.nt_sales_class1_id = 2" the performance is excellent,<BR>so there must be a problem with T4 (about 3 sec) !!!!<BR><BR>First one would think there is no index on T4.nt_sales_class1_id, but there <BR>is one !!!<BR><BR>No my question:<BR>- Where to start to solve this problem ?<BR>- Why is the query slower giving the query a filter which reduces (should <BR>reduce) the amount of data over an index ?<BR><BR>Quantity Structure:<BR><BR>T0: 412396<BR>T1: 219236<BR>T2: 4<BR>T3: 83820<BR>T4: 13991<BR>T6: 6<BR><BR>Thanks for any hint (maybe also in general handling performence problems).<BR>I have got the booklet "Oracle SQL Tuning" of Marc Gurry, actually it's a <BR>nice booklet,<BR>but now I not able going further on.<BR><BR>Peter<BR><BR><BR><BR>---------------------
-----------------------------------------------------------<BR><BR><BR>Hello NG<BR><BR>I have got a perfomance problem with the following SQL statement<BR>(takes about 15min)<BR><BR>SELECT count(*)<BR>FROM SA_DOC_POS T0,<BR>NTD_SALES_DOCU_STATUS T2,<BR>PA_PART_GENERAL T3,<BR>NTD_SALES_DOCU_TYPE T6,<BR>SA_DOC T1,<BR>PA_GROUP_PART T4<BR>WHERE T1.DOCUMENT_DATE <= to_date('21.12.2006', 'DD.MM.YYYY')<BR>AND T1.DOCUMENT_DATE >= to_date('01.01.1995', 'DD.MM.YYYY')<BR>AND (T2.STATUS = 'fixed' OR T2.STATUS = 'done')<BR>AND T4.nt_sales_class1_id = 2<BR>AND (T6.SHORTCUT = 'IN' OR T6.SHORTCUT = 'CN' OR T6.SHORTCUT = 'WD')<BR>AND T1.CO_GRP_COMP_ID = 1<BR>AND T1.NTD_SALES_DOCU_STATUS_ID = T2.NTD_SALES_DOCU_STATUS_ID<BR>AND t0.PA_PART_GENERAL_ID = T3.PA_PART_GENERAL_ID<BR>AND T1.NTD_SALES_DOCU_TYPE_ID = T6.NTD_SALES_DOCU_TYPE_ID<BR>AND t0.SA_DOC_ID = T1.SA_DOC_ID<BR>AND T3.PA_GROUP_PART_ID = T4.PA_GROUP_PART_ID<BR><BR>Leaving "AND T4.nt_sales_class1_id = 2" the performance is excellent,<BR>so there must be a problem
with T4 (about 3 sec) !!!!<BR><BR>First one would think there is no index on T4.nt_sales_class1_id, but there <BR>is one !!!<BR><BR>No my question:<BR>- Where to start to solve this problem ?<BR>- Why is the query slower giving the query a filter which reduces (should <BR>reduce) the amount of data over an index ?<BR><BR>Quantity Structure:<BR><BR>T0: 412396<BR>T1: 219236<BR>T2: 4<BR>T3: 83820<BR>T4: 13991<BR>T6: 6<BR><BR>Thanks for any hint (maybe also in general handling performence problems).<BR>I have got the booklet "Oracle SQL Tuning" of Marc Gurry, actually it's a <BR>nice booklet,<BR>but now I not able going further on.<BR><BR><BR><BR><BR><BR><BR>Start by looking at the execution plan and<BR>it's predicates. Assuming 9i + , suitably<BR>installed:<BR><BR>explain plan for<BR>select count(*) ....<BR><BR>select * from table(dbms_xplan.display);<BR><BR><BR>The fact that you have an index on T4 is<BR>probably an (accidental) underlying cause<BR>of the problem. The optimizer is probably<BR>using the index w
hen it shouldn't, possibly<BR>starting the query on this table rather than<BR>ending here, possibly just using the index<BR>in an unsuitable join. The structure of the<BR>execution plan should tell you the answer.<BR><BR>Dirty trick - to disable the index if that is<BR>the problem, use the good old-fashioned<BR>method from the RBO:<BR><BR>AND T4.nt_sales_class1_id+0 = 2<BR><BR>-- <BR>Regards<BR><BR>Jonathan Lewis<BR><U>
http://jonathanlewis.wordpress.com</U> <BR><BR>Author: Cost Based Oracle: Fundamentals<BR><U>
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html</U> <BR><BR>The Co-operative Oracle Users' FAQ<BR><U>
http://www.jlcomp.demon.co.uk/faq/ind_faq.html</U> <BR><BR><BR><BR><BR><BR></DIV></BODY></HTML>
>
> --____KKQRIWFUWKTGYLHLXBIC____--
Received on Fri Dec 22 2006 - 12:36:48 CST
