Home » RDBMS Server » Performance Tuning » 10g and Index Fast Full scans
10g and Index Fast Full scans [message #243629] Fri, 08 June 2007 02:11 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: How do you determine db_cache_size
Next Topic: DELETE with Parallel - Parallel is not working
Goto Forum:
  


Current Time: Mon Dec 05 13:17:36 CST 2016

Total time taken to generate the page: 0.11941 seconds