| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Antw: Re: Performance Problem (8i)
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
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.htmlReceived on Thu Dec 21 2006 - 06:29:51 CST
 |
 |