Webinar - Understanding Oracle Optimizer Statistics

Embarcadero is sponsoring another Oracle Community webinar on January 29  (rescheduled) February 5 conducted by yours truly. Register for Understanding Oracle Optimizer Statistics and join me as I cover

• Oracle optimizer statistics fundamentals for computing cardinality/selectivity on single and multi-column predicates.
• How statistics can help the optimizer understand data distribution patterns.
• Reasons why certain ways of writing SQL limit the optimizer's ability and how query transformations improve the odds of getting a better execution plan.

Hope to "see" you there!

Statistics musings

I've been thinking about a few problems that seem to turn up with Oracle query optimization lately. Jonathan Lewis posted on one case where the high value statistic was likely "off" at times and caused the plans to switch between good and bad. I had recently seen a similar case where a new partition was added each day and query plans that involved the new partition would pick the wrong index due to a high value problem. The stats were manually set to help fix it.

Anyway, these situations and several others I've seen have got me to wondering about whether or not using dynamic stats collections more aggressively would help some of these types of problems. I mean, if stats were missing and dynamic sampling kicked in, could/would better plans be derived more frequently?

I'm going to set up a set of tests to see where these musings take me, but I thought I'd mention it first to see if anyone has any experience with doing this and what kinds of results you've seen. If you have any input, let me hear from you!

Optimizer NOWORKLOAD statistics

I am often asked about statistics collection. People want to know the magic formula for collecting them. When? How much? But, the truth is there isn't one formula, magic or otherwise, that works for everyone. Think about it... If there were, Oracle would have already implemented it, don't you think? But hope is still alive that someday the mystery about it all will be revealed.

I think Oracle is getting better and better in its attempts to automate the process of statistics collection. But there continues to be alot of questions and discussion on how to "do it right" in the meantime.

So, I thought I'd start trying to put together some of the questions I've received and do a little research and testing to see if I can't answer some of them here in the blog. Today, I wanted to start with a few words about system statistics, particularly about noworkload statistics. Starting with 10g, noworkload statistics are always available. Even if you delete them, if the database restarts, then they are automatically regathered.

The noworkload statistics are basically a few generic benchmarks used as performance measures for your system. The stats that are maintained are:

CPUSPEEDNW
The number of operations per second (in millions) that a CPU can process.
IOSEEKTIM
Average number of milliseconds needed to find data on the disk (default 10).
IOTFRSPEED
Average number of bytes per millisecond that can be transferred from the disk (default 4,096).

I've never touched the noworkload statistics on my laptop test database so I started by checking them to see what they were (they should be the defaults from when my database was first started). I used dbms_stats.get_system_stats to retrieve the values.

SQL> @getsysstats
iotfrspeed: 4096
ioseektm  : 10
cpuspeednw: 773.806

I then executed a single SQL statement and grabbed the execution plan. The statement was a 4 part UNION.


Then, I gathered noworkload statistics to see what they would be now. I started the collection and then executed several queries in another session while they were being collected.

SQL> exec dbms_stats.gather_system_statistics (gathering_mode => 'noworkload');

Using the gathering_mode parameter set to 'noworkload' will collect the values in about a minute. I'm not totally positive, but I believe the CPU speed is likely measured by executing some sort of calibration operation in a loop and the I/O is measured by doing different sized reads on several different datafiles.

These were my new values.

SQL> @getsysstats
iotfrspeed: 29126.045
ioseektm  : 14.856
cpuspeednw: 917.075

So, I ran the same SQL and checked the plan and you can see the difference the new noworkload statistics made.



I notice two key things: 1) the cost for this plan went down, but the estimated %CPU went up. This makes sense if you look at the new statistics. The iotfrspeed was significantly larger than my original value. This would mean that the optimizer now thinks it is much less expensive to transfer data from the disk than it was previously and reduce the cost of those operations.

In my example, the optimizer still chose the same plan it originally chose, but now, in comparison to any other plan choices, this plan is even more likely to be selected due to the much lower cost.

Several people have commented to me that they've chosen not to use system stats in their 10 databases because they had trouble initially when upgrading from 9i. But, what they often don't realize is that they are using system statistics no matter what. They are simply using the noworkload variety is they disable/delete the others. And, as you can see in this simple example, noworkload stats can change and cause the optimizer's calculations to change as as result. This could mean plans change for apparently no reason. This is one of those cases when someone swears "nothing has changed" but then they tell me that the database had just been restarted for some reason. When that happened, the noworkload stats could've been recomputed. Even if that were the only thing that changed, it might be enough to cause some plans to break.

So, as I've mentioned before in a previous post: instead of insisting nothing has changed, evaluate what might have changed that might not be on your radar. Something is bound to have changed somewhere.

More stats stuff to come in later posts.