A few days ago I received an email about system statistics. I decided it was worth replying to, provided I wrote my response up as a series of questions and answers on the blog. (I don’t tend to respond to individual questions – it’s not an effective use of my time – so there has to be a good reason for replying).
The italicised text below is from the original email, the rest is my response.
I hope you don’t mind me emailing you. However, I would like to get your opinion on Oracle System Statistics (CPU Costing).
I regard system statistics as a critical item in 9i and believe that missing system statistics was the number one reason behind poor performance after migrating from 8i to 9i.
I agree – although setting optimizer_index_cost_adj to a suitable value had a similar effect in 8i; especially if you used the single and multi-block read wait times as a guideline.
However, there are a few items in regards to system statistics that I am not sure about:
1. My understanding is that to make effective use out of system statistics MREADTIM must be greater than SREADTIM and in fact be at least 1.2 times greater. Therefore after gathering stats I would often manually update them to set MREADTIM to a greater setting than SREADTIM (often I will set SREADTIM to 2 and MREADTIM to 5). Is this actually a good thing to be doing?
Things may have changed in the latest releases, but initially if mreadtim was less than sreadtim (which could happen with SANs and other caching mechanisms) then the traditional I/O costing applied – although a CPU component would still be added to the I/O cost.
I approve of your ‘fake’ settings as they are probably a reaonable indicator of relative disk response times for single block reads and multi-block reads of about 8 blocks. I would be perfectly happy, in an OLTP system particularly, to do the same.
2. Some authors suggest that we should be gathering different levels of system statistics at different times of the day; where as my school of thought is is best to gather them once then leave them alone (if stats are producing optimal explain plans why change them). What are your thoughts on this?
This “different times of day” approach is in principle correct – and an approach that I originally thought to be quite reasonable.
There is a problem, however, with caching mechanisms and bad code. You can get into a feedback loop where bad code is protected by (say) a file-system cache which makes multiblock reads appear to be very fast.
Collecting system stats at the wrong time therefore encourages the optimizer to believe that multi-block reads are always really fast – so it does more of them. This is why I now lean towards simply setting some times that reflect the actual disk speeds when tested in the absence of Oracle.
3. In 10g CPU Costing is enabled by default so the big drop in performance we witnessed when going from 8i to 9i is not so apparent when going from 9i to 10g. I therefore tended not to gather system stats manually as performance seemed to behave well enough without doing this. However after recently reading your book “Cost-Based Oracle Fundamentals” I discovered the impact of what “DB_FILE_MULTIBLOCK_READ_COUNT” does without system stats being gathered. Would you say gathering system stats in 10g is as critical as it is in 9i?
There have been some changes since I wrote the book. In 10.2, I would experiment with NOT setting the db_file_multiblock_read_count at all. Oracle then uses two internal read counts, one for the optimizer calculations (which becomes the assumed MBRC) and one for the run-time physical read attempt.
On many platforms the defaults leave Oracle deriving values of sreadtim and mreadtim that are quite reasonable – and happen to match quite well the ratio of the values that you’ve been using.
4. Lastly with system statistics gathered is there any benefit to be gained of still using “DB_FILE_MULTIBLOCK_READ_COUNT”?
See (3) – if you set the parameter, check what Oracle does with the hidden parameters _db_file_exec_read_count and _db_file_optimizer_read_count (if they exist in your version). If they don’t exist (and you’ve gathered system statistics) the value you set is the value that Oracle tries to use for reads at run-time, but it has no effect on the cost.