Home » RDBMS Server » Performance Tuning » Erratic explain plan (10.1.0.4.0, 64 bit)
icon1.gif  Erratic explain plan [message #415729] Wed, 29 July 2009 01:07 Go to next message
cherry
Messages: 56
Registered: December 2007
Member
pls have a look @ the attached explain plans.
this plan in on the same instance but for two different days.
using the first plan, a delete statement took 2+ hrs to complete.
whereas using the 2nd plan, it took a few seconds.
DELETE FROM xx_bo_extracts_flat_file a
      WHERE a.flag1 IS NULL
        AND EXISTS (SELECT 'x'
                      FROM xx_bo_extracts_flat_file b
                     WHERE b.flag1 = 'Y' AND a.linerowid = b.linerowid)

in b/w these days, there was NO ddl issued on this table.

99/100 times, its using the 2nd plan, but for once it used the 1st. it was in prod instance.

i dont understand the reason of this erratic behaviour. neither am i able to replicate the 1st plan.

here is the table with indexes on it. i have also attached the plans for your review.

SQL> desc xx_bo_extracts_flat_file
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JE_HEADER_ID                                       NUMBER
 LINEROWID                                          VARCHAR2(100)
 JE_BATCH_ID                                        NUMBER
 CLM                                                VARCHAR2(1000)
 PROCESSING_DATE                                    DATE
 CREATION_DATE                                      DATE
 FLAG1                                              VARCHAR2(1)

it has a index on 3 columns.
CREATE INDEX XX_BO_EXTRACTS_FLAT_FILE_I1 ON XX_BO_EXTRACTS_FLAT_FILE
(PROCESSING_DATE, LINEROWID, FLAG1)
LOGGING
TABLESPACE TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL
COMPRESS 1;

from the plan, it appears that the delete ignores the index & does a full table scan. results are good in case of full table scan.

but when it uses the index, delete goes for a toss. why did it use the index on that particular day baffles me.

appreciate your advises. thanks.

[EDITED by LF: specified product version after topics have being merged]

[Updated on: Wed, 29 July 2009 01:43] by Moderator

Report message to a moderator

Re: Erratic explain plan [message #415774 is a reply to message #415729] Wed, 29 July 2009 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the plan with the full table scans really is faster then your stats are wrong, because judging from the plans it should be the other way round.

As for why it occasionally changes plans - bind variable peeking would be my first guess.
Re: Erratic explain plan [message #415789 is a reply to message #415774] Wed, 29 July 2009 05:43 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
thanks for your reply.
but i have not based my assumptions looking @ the plans only.

when the delete was running endlessly, i took the plan. it was using index. cost=71.

after doing more dmls on the table, i ran the delete again.
i just took a few seconds. surprised, i looked @ the plan & it showed a full scan with cost =18.
Quote:
If the plan with the full table scans really is faster then your stats are wrong,

why do you say so?
Re: Erratic explain plan [message #415792 is a reply to message #415729] Wed, 29 July 2009 05:54 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
slow plan:

Cost: 71
Bytes: 50
Card: 1

fast plan

Cost: 18,000 (not 18, notice the K)
Bytes: 1,000,000
Card: 24,000

While you should never read too much into cost the fact that bytes and cardinality are massively higher in the fast plan means that the stats are almost certainly wrong - probably for the index.

How many rows in the table and how many get deleted?
Re: Erratic explain plan [message #415801 is a reply to message #415792] Wed, 29 July 2009 06:23 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
good observation.
DATE             COUNT(*)
---------        --------
7/17/2009        77686
7/20/2009        108101
7/21/2009        86543
7/22/2009        94731
7/23/2009        141738
7/24/2009        23493
7/28/2009        195352

from date 28th, it will delete 500 odd duplicate rows.
thanks
Re: Erratic explain plan [message #415802 is a reply to message #415729] Wed, 29 July 2009 06:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
try creating an index on LINEROWID, FLAG1
Re: Erratic explain plan [message #415814 is a reply to message #415802] Wed, 29 July 2009 07:08 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
i will drop the existing composite index & create 2 separate indexes. thanks for your suggestion.

but i remember trying this out & got the same plan of cost=71 bytes

[Updated on: Wed, 29 July 2009 07:10]

Report message to a moderator

Re: Erratic explain plan [message #415818 is a reply to message #415729] Wed, 29 July 2009 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're where clause can be broken into two parts for this.
the IS NULL and the exists.
The exists can be satisfied with the index specified but oracle might decide not to use it.
The problem is the IS NULL.
NULL values aren't really indexed (they are if the column is not the last one in a composite index, but not otherwise).
What you really want to do is have flag1 be set to a none NULL value for the records you want to delete - then it should be able to use indexes properly.
Re: Erratic explain plan [message #415964 is a reply to message #415818] Wed, 29 July 2009 23:51 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Quote:
What you really want to do is have flag1 be set to a none NULL value for the records you want to delete - then it should be able to use indexes properly.

ok. i get it. but do i apply this on the query like
DELETE FROM ..flat_file a
      WHERE NVL(a.flag1,'~') = '~'
        AND EXISTS (SELECT 'x'
                      FROM ..flat_file b
                     WHERE b.flag1 = 'Y' AND a.linerowid = b.linerowid)

or insert a special char @ the time of insert into flat_file table.
thanks for all your help.

[Updated on: Thu, 30 July 2009 02:05]

Report message to a moderator

Re: Erratic explain plan [message #416015 is a reply to message #415729] Thu, 30 July 2009 03:14 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That might work if you add a function based index on
NVL(a.flag1,'~')

My preference would be to put an actual value in at insert/update.
Your system, your choice.
Previous Topic: Negative Tablespace Free Space value
Next Topic: Bind variables and SGA size
Goto Forum:
  


Current Time: Sat Dec 03 20:37:33 CST 2016

Total time taken to generate the page: 0.11734 seconds