| 10g and Index Fast Full scans [message #243629] |
Fri, 08 June 2007 02:11  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
We recently moved from 9.2.0.8 to 10.2.0.3 and are experiencing the joys of a CBO that is now very partial to index fast full scans, which is basically never the right access path for our environment.
I have been working with one monstrous query that I was finally able to tune with some cardinality hints - which steered the CBO away from the index FFSs.
Now, in the PL/SQL where this query lives, this is actually an 'insert into/select from' statement, but I tuned just the select part. Now I notice that I get two very different plans depending on whether the 'insert into' portion is included (index FFS - bad) or not (range scan - good).
What is influencing the CBO here to radically change the plan? I don't remember seeing discrepencies like this before.
Of course, if I set optimizer_features_enable='9.2.0.8' all is right with the world, but I'd really prefer not to go down that path.
|
|
|
|
| Re: 10g and Index Fast Full scans [message #243791 is a reply to message #243629] |
Sat, 09 June 2007 00:44  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
That is fascinating - I've never seen it do that before.
Have you tried the hints immediately after the INSERT keyword instead of after the SELECT keyword? Are you using any views and/or ANSI joins? Hints have a way of going troppo with these.
Is the INSERT playing up only in the PL/SQL? Or does it do the same stand-alone?
My experience with 10g (admittedly R1) was the opposite - I found the optimizer darned near omnipitent.
Are you using the default Gather Statistics options? 10g gathers histograms for all columns - indexed and unindexed - with a dynamically determined number of buckets. I found that just using all the defaults for DBMS_STATS.GATHER_TABLE_STATS(user,tab) gave extraordinary results (compared to prior versions).
Lastly, there are a couple of initialisation parameters that tweak the "goodness" of indexes. They might help. Look in the Performance Tuning manual for initialiasation params.
Ross Leishman
|
|
|
|
| Re: 10g and Index Fast Full scans [message #244184 is a reply to message #243791] |
Mon, 11 June 2007 18:32 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The plan here is only affected by hints after the select which is a massive union all. No views or ANSI joins are involved.
The issue is apparent even in SQL - I can easily see the plan change by commenting out the insert just in the SQL Editor in TOAD.
Stats - We are computing and using a method_opt of 'for all columns size 1'.
We already use the optimizer_index_cost_adj and optimizer_index_caching parameters. Without these, we end up with way too many FTS.
Oracle Support have since basically acknowledged this as a bug (there is a similar one involving CTAS) but have no suggested workaround at this time.
Thanks Ross.
|
|
|
|
| Re: 10g and Index Fast Full scans [message #244197 is a reply to message #244184] |
Mon, 11 June 2007 22:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You could try using the default stats gathering params (FOR ALL COLUMNS AUTO). Giving the optimizer more info to work with is almost always a good thing.
Sounds like the INSERT is totally ignoring the hints. You're not using query block identifiers are you? Because they may differ between an INSERT and a SELECT.
You could also try migrating the hints from the SELECT keyword to the INSERT keyword. In this case, you will definitely need query block identifiers so that each hint knows which part of the UNION it applies to.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
