Re: Range Scan Cost Fluctuations

dobrien_at_amcad.com (Dan) wrote in message news:<bdc62384.0406161434.23c0f51e_at_posting.google.com>...
> Thanks for the response Mark.
>
> I looked into the problem a little further and found that the
> difference comes down to two parts of the WHERE clause. When these
> two values are NOT sent as bind variables, the cost goes way down. I
> realize that cost is an arbitrary number. I looked at the number of
> consistent gets and there was a large difference (factor of 10x) when
> I was sending these two conditions as bind variables. My problem is,
> since the database has to run with CURSOR_SHARING=SIMILIAR (I'll be
> arm wrestling our developers over their lack of bind variable usage) I
> don't see a way to get around this issue.
>
> I have a couple of books that explicitly say that if you are not using
> bind variables from the application side, the CBO can't be guaranteed
> to produce predictable/consistent executions. Am I up the creek here
> because of this? I am forcing index usage using hints, as well as
> ORDERED to make sure the join is done in the proper order. Is there
> any way that you know of that I could selectively turn the
> CURSOR_SHARING off for part of the query? Some sort of escape
> characters of some sort that tell Oracle that I don't want it to bind
> these particular variables, but bind the rest of the query?
>
> I know that this is all patchwork, but I have an angry customer and
> there is no way the entire application can be changed for the
> deadline. Any ideas?
>
> Thanks for the help,
> -Dan
>
>
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0406140713.48d88178_at_posting.google.com>...
> > dobrien_at_amcad.com (Dan) wrote in message news:<bdc62384.0406121559.2126beb1_at_posting.google.com>...
> > > I am a relatively new user on Oracle 9.2.0.1 and I am having trouble
> > > performance tuning this production database.
> > >
> > > I am running a large query that joins two tables, document(3 mil) and
> > > entity(9 mil). I have reorganized my tablespaces so that the two
> > > tables are on different tablespaces, different disks. They both have
> > > their indexes stored on a third tablespace. Before reconfiguring the
> > > production database, I was getting a range scan on index ix_document_8
> > > that had a cost of 25. Now that I have reconfigured the prod
> > > database, the cost of the range scan has gone through the roof, 5933.
> > >
> > > I have tried moving this one particular index to different tablespaces
> > > on all three disks available to me to no avail. I run "analyze index
> > > compute statistics;" on the index after every time I recreate it. It
> > > appears that no matter where I create this index it still has trouble
> > > accessing it. I have computed the statistics for the table before
> > > computing the index statistics. It appears to me that there is an I/O
> > > problem here. Why did it cost so little before and now it costs so
> > > much to access it?
> > >
> > > Should the index be on the same disk/different tablespace?
> > > Different disk/different tablespace?
> > >
> > > I have run out of my testing capabilities here to try and troubleshoot
> > > why the cost is so high. Could this be a problem with my CBO?
> > >
> > > Any help is greatly appreciated,
> > > -Dan
> >
> > Dan, if the explan plan show the same access path for the query after
> > you moved the indexes and reanalyzed the table/indexes then the odds
> > are that the statistics were out of data prior to your re-calculating
> > them. In other words you are looking at the true cost. Is there a
> > difference in run time, or just in the cost shown by the explain
> > plans?
> >
> > Also, are or did you use constants in one plan and bind variables in
> > another? There can be major differences in the plan chosen and cost
> > based on the difference in the CBO generates because of the difference
> > in information the two options provide the optimizer.
> >
> > HTH -- Mark D Powell --

When you submit SQL to the rdbms or run an explain the plans produced for the same SQL except for where a constant is used in one and a bind variable is used in the other may be very different. Since the CBO does not know the value of the variable it works with certain assumptions. When a constant is provided it knows more and does things differently. Also constants enable the use of histograms if they exist on the referenced column while bind variables do not.

If the data is skewed (example only 6 distinct values and 1 value accounts for 80% of the data) then the CBO plan will probably ignore an index on this column. The developer will then run a test using one of the 5 rare values either using a constant, the RULE hint, or an index hint and complain about the CBO. However, if you run the SQL with the index hint and use the 80% of the rows data value the query performance is likely to be very, very bad. In this case the CBO produces a consistent plan but it is non-optimal for the minority of rows.

To solve a problem like the above one possibility is to code an IF so that if the program variable value is the 80% value to let the CBO have the SQL as is otherwise to submit the SQL with an index hint.

In your case I suggest the following (if my post is not to late to help)
make sure the statistics are current

try to determine why the CBO is not choosing the index you think should be used. The most common reasons for this are join order, instead of A to B to C the CBO is B to C to A making the index from A to B unusable, and join method such as hash join or merge join instead of a nested loop that would make the path available. Sometime just rearranging the query can fix this; if not then the ORDERED and USE_NL hints usually will.

Check for skewed data. The idea above where you have to test the value to be used in the query and provide hinted SQL might be an option in this case. Alternately rewriting the query to use an inline view and some of the hints above might be enough to make the desired plan available.

1- Make sure the SQL does not force reading the same table more than once where one time through would be enough

2- that all necessary join conditions are present

3- try to make sure filtering conditions are applied early in the join order

HTH -- Mark D Powell -- Received on Fri Jun 18 2004 - 19:20:48 CEST