Range Scan Cost Fluctuations

[Dan]

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

[Mark D Powell]

Re: Range Scan Cost Fluctuations

dobrien@amcad.c om (Dan) wrote in message news:<bdc62384. 0406121559.2126 beb1@posting.go ogle.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 --