Re: Fluctuating Cost with Range Scan

Thanks for the response Mark.

I looked into the problem a little farther 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 guarenteed 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

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0406132354.526d3c93_at_posting.google.com>...
> dobrien_at_amcad.com (Dan) wrote in message news:<bdc62384.0406121751.5bf79ee7_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
>
> Cost won't necessarily be influenced by relocating tablespaces.
> The foremost factor in cost is
> - selectivity
> - selectivity
> - selectivity
>
> and adequate optimizer_cost_adj and optimizer_index_cache parameters
> set.
> You probably don't have a histogram on the affected column.
> Analyze table compute statistics for all indexed columns is probably
> in order.
> Did you try to use hints in your statement (forcing the index)? If so,
> what was the result?
>
> Please also note : cost is a meaningless number. The only things that
> counts is the number of consistent gets.
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Thu Jun 17 2004 - 00:35:13 CEST