Performance Problem

[Database Administrator]

In message <458a58c8$0$427$4d4ef98e@read.news.ch.uu.net>, COAST
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
RSS

[Database Administrator]

In message <458a58c8$0$427$4d4ef98e@read.news.ch.uu.net>, COAST
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
RSS

[Database Administrator]

"COAST" wrote in message
news:458a58c8$0$427$4d4ef98e@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

[Database Administrator]

"COAST" wrote in message
news:458a58c8$0$427$4d4ef98e@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

[Database Administrator]

Hi Jonathan
thanks a lot!

The
AND T4.nt_sales_class1_id+0 = 2
"dirty-trick" solves the problem
(great trick by the way ;-)),

The performance is also very good when deleting the index
(of course because it should cause be the same as your trick...)

O.k.
I don't know how to tell my Application (WebObjects) how to generate the +0 trick,
therefore I have to delete the index.

But now I'm a little afraid just doing this.
Could it not happen that I will get troubles with other SQL-statements
needing the index.?

If there is another way to solve the problem, I would prefer it
(cause of the rule: each id should have an index, and I don't like it to have
an exception in our (big) project, cause somebody will just turn on the index
somewhen in the future (maybe in half a year or so).

Maybe someone could tell me a solution by knowing that my nt_sales_class1_id
has a lot of NULL values. Is the a "rule" that you should not use an index having a lot of NULL-values ?

COUNT(*) NT_SALES_CLASS1_ID
---------- ------------------
224 1
1103 2
2484 3
10180 null

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

[Database Administrator]

Hi Jonathan
thanks a lot!

The
AND T4.nt_sales_class1_id+0 = 2
"dirty-trick" solves the problem
(great trick by the way ;-)),

The performance is also very good when deleting the index
(of course because it should cause be the same as your trick...)

O.k.
I don't know how to tell my Application (WebObjects) how to generate the +0 trick,
therefore I have to delete the index.

But now I'm a little afraid just doing this.
Could it not happen that I will get troubles with other SQL-statements
needing the index.?

If there is another way to solve the problem, I would prefer it
(cause of the rule: each id should have an index, and I don't like it to have
an exception in our (big) project, cause somebody will just turn on the index
somewhen in the future (maybe in half a year or so).

Maybe someone could tell me a solution by knowing that my nt_sales_class1_id
has a lot of NULL values. Is the a "rule" that you should not use an index having a lot of NULL-values ?

COUNT(*) NT_SALES_CLASS1_ID
---------- ------------------
224 1
1103 2
2484 3
10180 null

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

[Database Administrator]

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@jlcomp.demon.co.uk > 21.12.2006 11:25 >>>


"COAST" < coast@cedes.com > wrote in message
news:458a58c8$0$427$4d4ef98e@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

[Database Administrator]

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@jlcomp.demon.co.uk > 21.12.2006 11:25 >>>


"COAST" < coast@cedes.com > wrote in message
news:458a58c8$0$427$4d4ef98e@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

[Database Administrator]

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

[Database Administrator]

In message <458a58c8$0$427$4d4ef98e@read.news.ch.uu.net>, COAST
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
RSS