OraFAQ Forum: Performance Tuning » Single operation slows down a query

Home » RDBMS Server » Performance Tuning » Single operation slows down a query (Oracle9i, 9.2.0.6.0, Win 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Single operation slows down a query [message #303953]

Mon, 03 March 2008 07:27

irremediable
Messages: 38
Registered: December 2007

Member

Hello!
Could anybody please exaplain
why each of these operations
< or <= in "AND BF.DT_OPEN >= WH.DT_CLOSE"
dramatically slows down a query, whereas
any other operation like <>, > or >= doesnt
halt the query!?

SELECT BF.ACC_CHARGE_PRC,BF.CODE_EVENT,BF.DATE_CHARGE_PRC,WH.DT_CLOSE,BF.DT_OPEN,BF.ID_ACCOUNT,
BF.ID_CONOPER,BF.ID_CONTRACTS,BF.ID_FILE,BF.ID_FINSTR,BF.ID_ORD_LIAB,BF.ID_TACC,BF.ID_TREST,BF.SALDOPRCMAIN,BF.SALDOPRCOVN,
BF.SALDOPRCOVPRC,BF.SUM_ALL_CALC,BF.SUM_CHARGE_PRC,BF.SUM_PRC_M_DELAY,BF.SUM_PRC_M_PRCD,BF.SYSMOMENT,SYSDATE 
FROM DWH.FCT_CHARGE_PRC WH, LDR_BF.FCT_CHARGE_PRC BF WHERE 
WH.ID_ORD_LIAB = BF.ID_ORD_LIAB 
AND WH.ID_CONTRACTS = BF.ID_CONTRACTS 
AND WH.ID_FINSTR = BF.ID_FINSTR 
AND WH.ID_CONOPER = BF.ID_CONOPER 
AND WH.ID_TACC = BF.ID_TACC 
AND BF.DT_OPEN > WH.DT_OPEN 
AND BF.DT_OPEN >= WH.DT_CLOSE
AND BF.REC_STATUS IN(0)

Report message to a moderator

Re: Single operation slows down a query [message #304047 is a reply to message #303953]

Mon, 03 March 2008 19:59

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Post the Explain Plan for a slow version and one for the fast version.

One of four things is probably happening:
- The <= query is using an index and the other is (wisely) not. The index is a bad choice.
- The <= query is NOT using an index and the other IS (and it is a good index to use.
- Both queries are using the same index, but there are heaps more candidate matches to scan for <=.
- The plans are completely and utterly different.

Ross Leishman

Report message to a moderator

Re: Single operation slows down a query [message #304081 is a reply to message #303953]

Tue, 04 March 2008 00:04

irremediable
Messages: 38
Registered: December 2007

Member

Here is the exaplin plan and it is the same for both
versions of the query.

  SELECT STATEMENT 
    DWH.FCT_CHARGE_PRC TABLE ACCESS [BY INDEX ROWID] 
     NESTED LOOPS 
      LDR_BF.FCT_CHARGE_PRC TABLE ACCESS [FULL] 
      DWH.AK_CHARGE_PRC_FCT_CHAR INDEX [RANGE SCAN]

Seems this is the reason
" Both queries are using the same index, but there are heaps more candidate matches to scan for <=."
isnt it?

Report message to a moderator

Re: Single operation slows down a query [message #304325 is a reply to message #304081]

Tue, 04 March 2008 20:52

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Trace both of them and post the Tkprof output.

Report message to a moderator

Re: Single operation slows down a query [message #304339 is a reply to message #303953]

Tue, 04 March 2008 23:25

irremediable
Messages: 38
Registered: December 2007

Member

It works.
I gathered statistics on the LDR_BF.FCT_CHARGE_PRC
and it woked fine.
Thank you.

Report message to a moderator

Previous Topic:	Can you fix this query?
Next Topic:	query hangs on order by

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:19:33 CST 2024