 Erratic explain plan [message #415729] |
Wed, 29 July 2009 01:07  |
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  |
cookiemonster
Messages: 13925
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 |
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 |
cookiemonster
Messages: 13925
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 |
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 #415818 is a reply to message #415729] |
Wed, 29 July 2009 07:17 |
cookiemonster
Messages: 13925
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 |
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 |
cookiemonster
Messages: 13925
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
