- Re-set optimizer costing - Consider unsetting yourYour interpretation of CPU costing, and the optimizercost_model parameter is incorrect.
CPU-based optimizer costing (the 10g default, a
change from 9i). CPU costing is best of you see CPU
in your top-5 timed events in your STATSPACK/AWR
report, and the 10g default of
optimizercost_model=cpu will try to minimize CPU by
invoking more full scans, especially in tablespaces
with large blocksizes. To return to your 9i CBO
I/O-based costing, set the hidden parameter
Interesting. Can we generalize that, or does it depend on many, many factors?The effect of the change to the I/O cost component is to allow for the fact that multiblock reads take more time than single block reads
Well, Jonathan Lewis, ANYTHING is "incorrect" if we use your definition:Your interpretation of CPU costing, and the optimizercost_model parameter is incorrect.
I keep a large library of STATSPACK and AWR reports, and I thought I would do some verification on your "fact" because you have raised an important issue!the fact that multiblock reads take more time than single block reads (under reasonable workloads).
When considering the effect of the optimizer parameters, bear in mind that the optimizer is attempting to model what it thinks happens in the real world. If you don't keep a very clear distinction between "what the model is assuming" and "what actually happened on a given system" you can easily get confused by Oracle's behaviour.is to allow for the fact that multiblock reads takeThe effect of the change to the I/O cost component
more time than single block reads
Interesting. Can we generalize that, or does it
depend on many, many factors?
In general, how do you see disk level I/O as beingThis is where you switch from the model to the real world - which means the place where the model fails. Remember, the model believes that multiblock reads are going to be slower than single block reads.
Oracle guru David Aldridge has a great write-up onBottom line - statements about what the optimizer is doing are not statements about what happens in the real world; they are statements about what the optimizer thinks will happen in the real world when the run-time engine takes over. It's important to notice when there is a big difference between the model and the real world.
Linux kernel I/O for large Oracle systems in Linux.
Also full-scan access speed is aggravated by Oracle
willy-nilly block placement in Automated Storage
Management (ASM) and using bitmap freelists
(Automated Segment Storage Management).
Ah, but is it "traditional"?The model assumes that a multiblock read takes longer than a single block read because it assumes that your hardware behaves in a very traditional fashion.
Interesting observation. I agree that the seek delay is atleast 90% of the access latency, but with a large stripe size Oracle must only incur it once. Once located under the proper cylinder, the RW head sits there, sucking-in data blocks as fast as the platter spins.whilst a multiblock read requires a disk seek followed by a longer wait because more data has to travel under the disk head.
That's an issue, for sure.i am getting very slow when i run the statspack (more than 1 hrs)
By "it" do you mean the hardware you happen to be working with ? If so the question is irrelevant to the optimizer. The optimizer has a model - it does what it does. The fact that your hardware doesn't match the optimizer's model of your hardware doesn't change the way the optimizer decides on an execution plan. Your hardware may, of course, mean that the plan is actually a bad one for the circumstances. (The same is true of data, of course - you may understand your data content and distribution much better than the optimizer does - that doesn't affect the optimizer's plan).longer than a single block read because it assumesThe model assumes that a multiblock read takes
that your hardware behaves in a very traditional
Ah, but is it "traditional"?
I'm not a close follower of hardware - but I think the latest figures for reasonably fast disks are: 160 I/Os per second random reads, and 100Megabytes per second sustained sequential, on the 15,000 rpm disc. Quick approximations from these figures give:followed by a longer wait because more data has towhilst a multiblock read requires a disk seek
travel under the disk head.
Interesting observation. I agree that the seek delay
is atleast 90% of the access latency, but with a
large stripe size Oracle must only incur it once.
Once located under the proper cylinder, the RW head
sits there, sucking-in data blocks as fast as the