Home » RDBMS Server » Performance Tuning » A query with one additional condition slows down
A query with one additional condition slows down [message #299869] Wed, 13 February 2008 07:46 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member
Hello!
Here is a script that takes not more that a minute to complete

SELECT 
TO_DATE('02.02.2008', 'DD.MM.YYYY') AS DT, 
CC.ID_CONTRACTS, 
B.ID_BALANCE, 
S.SALDO,
C.NUM_CONTRACTS
FROM
FCT_PRCST_CRED P, DET_ACCOUNT A, DET_BALANCE B, FCT_ACOUNT_SALTRAN S, DET_CONTRACTS C,
CON_ASS_CONTR_ACC CC, DET_ORD_LIAB O, DET_TACC T, DET_SOURCE SR 
WHERE A.ID_ACCOUNT = S.ID_ACCOUNT
AND A.ID_BALANCE   = B.ID_BALANCE
AND A.ID_ACCOUNT   = CC.ID_ACCOUNT
AND C.ID_CONTRACTS = CC.ID_CONTRACTS
AND CC.ID_TACC     = T.ID_TACC
AND C.ID_SOURCE    = SR.ID_SOURCE
AND C.ID_CONTRACTS = P.ID_CONTRACTS
AND P.ID_ORD_LIAB  = O.ID_ORD_LIAB
--AND O.NL_ORD_LIAB  = '1'
AND TO_DATE('02.02.2008', 'DD.MM.YYYY') BETWEEN P.DT_OPEN  AND P.DT_CLOSE
AND TO_DATE('02.02.2008', 'DD.MM.YYYY') BETWEEN S.DT_OPEN  AND S.DT_CLOSE
AND TO_DATE('02.02.2008', 'DD.MM.YYYY') BETWEEN CC.DT_OPEN AND CC.DT_CLOSE
AND SR.CODE_SOURCE = 'Loans'
AND C.CODE_CONTRACTS NOT LIKE '%gar%'
AND SUBSTR(B.CODE_BALANCE, 1, 1) IN ( '1', '2' )
AND T.CODE_TACC = '1'
AND A.IS_GK != '1'
AND S.SALDO <> 0



As soon as I uncomment the commented condition it takes more than 15 minutes to complete.
The NL_ORD_LIAB column is of VARCHAR2(250) type and the table
DET_ORD_LIAB contains about 500.000 records in it.
What can be the problem?
What could you please recommend to read in order to overcome a situation like this one?
Thank's is advance.
Re: A query with one additional condition slows down [message #299871 is a reply to message #299869] Wed, 13 February 2008 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We could comment if we had the execution plans.

Regards
Michel

[Updated on: Wed, 13 February 2008 08:29]

Report message to a moderator

Re: A query with one additional condition slows down [message #299875 is a reply to message #299871] Wed, 13 February 2008 08:17 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member

I attached screenshots of plans.
The first is without that condition and the second with it.
Thanks!
  • Attachment: ExecPlans.doc
    (Size: 154.50KB, Downloaded 1206 times)
Re: A query with one additional condition slows down [message #299879 is a reply to message #299875] Wed, 13 February 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most of us can't or don't want download doc file.
Post it in txt file, with correct indentation and alignment.

Regards
Michel
Re: A query with one additional condition slows down [message #299884 is a reply to message #299869] Wed, 13 February 2008 08:58 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member

Here they are.
ExecPlan_With.html and ExecPlan_Without.htm.
Thanks!
Re: A query with one additional condition slows down [message #299886 is a reply to message #299869] Wed, 13 February 2008 08:59 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member

Excuse me, the 2nd file wasnt attached, here it is.
Re: A query with one additional condition slows down [message #299887 is a reply to message #299884] Wed, 13 February 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What in TXT file don't you understand?
This is text, ascii file. No possible code in it.

Regards
Michel
Re: A query with one additional condition slows down [message #299890 is a reply to message #299869] Wed, 13 February 2008 09:12 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


I cant make out what you're trying to say.
You asked to post in txt file, I posted in html, just what
EM generates as a report which is more convinient to read than reading a txt file.
What have I done wrong?
Re: A query with one additional condition slows down [message #299896 is a reply to message #299890] Wed, 13 February 2008 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and "explain plan" or "set autotrace", and copy and paste your screen.

Regards
Michel
Re: A query with one additional condition slows down [message #299897 is a reply to message #299869] Wed, 13 February 2008 09:22 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
The only thing you did wrong was not posting the result in a simple text file.
As Michael pointed not everyone can - or will - download word-files : working in a non-Microsoft environment (linux fi), company policy, possible virus-attacks and so on.
Same applies for HTML.

A text-file is down loadable on almost all platforms, that's why.

If someone asks to post a text-file, he/she will have his/her reasons to ask, so why don't you just do that: post a text-file.
Re: A query with one additional condition slows down [message #299964 is a reply to message #299897] Wed, 13 February 2008 20:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You haven't indicated how many rows the SQL returns with and without this predicate, but Oracle thinks that it will be a LOT less (say, <10%) with the predicate included.

As it thinks that so few rows will be returned, it uses indexes to access every table. Great for small volumes, rubbish for large volumes.

There are a number of different ways to tune this. I would try the following:
- Gather statistics with DBMS_STATS on all tables including the indexes
- If it still does not work, look through the Estimated Rows Returned in the plan. If Oracle is choosing the wrong plan, it usually means one of its estimates is way out. Find out which one (or more) is wrong, and provide a more accurate estimate with a /*+CARDINALITY(alias, rows)*/ hint.
- Still refuses to cooperate? Rearrange the FROM clause so that the tables are in the order that they should be optimally joined, and use the ORDERED hint in combination with FULL and USE_HASH hints to dictate the join order, join method and access method of every single table.

Ross Leishman
Re: A query with one additional condition slows down [message #300442 is a reply to message #299964] Fri, 15 February 2008 07:29 Go to previous message
irremediable
Messages: 38
Registered: December 2007
Member



That was the third option. I read in a doc
that Oracle parses tables form left to right.
I rearranged the order of the tablesa several times
and finally come to an order in which the script ran
fast enough.
Thank you very much.
Previous Topic: TOAD / Oracle qeps
Next Topic: Procedure is running from more than 3 hrs but do not return the results
Goto Forum:
  


Current Time: Fri Dec 02 16:46:54 CST 2016

Total time taken to generate the page: 0.46555 seconds