I wrote an article about system statistics / CPU Costing for Oracle magazine a few years ago – and last week I realised that I’ve never supplied a link to it in the notes and comments I’ve made about system statistics. So I’ve just run a search through the Oracle website trying to find it – and discovered that it’s no longer available. Apparently the editors have decided that any technical articles over a certain age should be withdrawn in case they are out of date and misleading. (Clearly they’ve read my blog on trust – I wish the people maintaining Metalink would do the same as the magazine editors – but they probably have a much larger volume to worry about).
However, I have discovered translations of the article in Russian, Korean and Chinese – so if you can read any of these languages, you might want to take a look at them before they disappear too.
If you want an original English version – dated April 2004, which is when I sent it in to Oracle Magazine, and before it underwent some editing – I’ve posted it as a pdf file.
Oracle Scratchpad 
Hi,
better ask to remove that Russian translation (http://www.oracle.com/global/ru/oramag/march2005/admin_sys_stat.html), it is enormously bad.
Oleksandr
Comment by al0 — December 17, 2010 @ 7:55 pm GMT Dec 17,2010 |
Oleksander,
Thank you for supplying the warning.
My ability to read Russian is limited to pressing buttons on Google Translate – and assuming that the occasional strange word is the wrong literal translation of a word that can be used in many circumstances. Within the bounds that limitation, it seemed that most of the text made sense – but I hope readers following will be duly cautious.
Comment by Jonathan Lewis — December 21, 2010 @ 7:51 am GMT Dec 21,2010 |
Should we now be hoping for an updated current posting soon on that topic with your most current thinking? An early XMAS present or Happy New Year item?
Keeping my fingers crossed!
Comment by John Hurley — December 17, 2010 @ 8:32 pm GMT Dec 17,2010 |
John,
Quick summary (slightly evolved from the “Further Posts” due to the passage of time).
Initial testing – from 10g onwards, don’t set them
If necessary – use set_system_stats to create realistic seek and transfer figures
Similarly, don’t set db_file_multiblock_read_count, and don’t set the two optimizer_ind% parameters (without extreme provocation.)
Comment by Jonathan Lewis — December 21, 2010 @ 7:56 am GMT Dec 21,2010 |
Peabody here. http://web.archive.org/web/20040624011940/otn.oracle.com/pub/articles/lewis_cbo.html
Which just goes to show, you can lead the CBO to order, but you can’t make it think.
Comment by joel garry — December 20, 2010 @ 11:11 pm GMT Dec 20,2010 |
Joel.
Thanks for the link.
Comment by Jonathan Lewis — December 21, 2010 @ 7:57 am GMT Dec 21,2010 |
Hi Jonathan
As per note 368252.1, system statistics are not recommended for Oracle Applications and the reason quoted and I paste it here is this.
But I am not sure if this is the right recommendation because the reason they quote is valid for any application not just oracle applications. I am going to raise an SR with Oracle but wanted to understand what are your thoughts on this (Also the note was updated on Aug 2010 which makes me think they still think this is valid recommendation )
7) Should dbms_stats.gather_system_stats be used with E-Business 11i or R12?
The use of dbms_stats.gather_system_stats to collect system stats is not recommended with Apps E-Business Suite 11i or R12.
Not collecting system stats will make the CBO (in DB 10g onwards) to use what are called “noworkload_stats”, which have quite sensible defaults.
The main reason for this recommendation is the desire for stability and the potential problems related to large plan variances from system to system and even within one system, which would depend on the workload when these stats were gathered
Thank you
Kumar
Comment by Kumar — December 21, 2010 @ 3:09 am GMT Dec 21,2010 |
See my reply to John Hurley.
If you follow my “further reading” links you’ll note in the “System Stats Strategy” that I’ve made similar comments.
Possibly the SR makes the point it does because it is a benefit to the support team if they don’t have to factor in different values for system stats whenever they’re trying to work out what’s going wrong with a client’s query.
Comment by Jonathan Lewis — December 21, 2010 @ 7:59 am GMT Dec 21,2010 |
Hi Jonathan
Thanks for the reply.
But this argument is true for any application (not just oracle apps). In that case, is not the use of system statistics not justified in shops which have different environments (and the dev and qa boxes would not be on the same type of storage or speed as production boxes).
– Kumar
Comment by Kumar — December 21, 2010 @ 12:06 pm GMT Dec 21,2010 |
Kumar,
I’m not entirely sure of the point you want to make, but I think by “this argument” you mean the comment on the metalink note. Two things then: (a) the fact that the metalink note says it is a good argument for EBS is not the same as saying it’s a bad argument for everything else and (b) the blog I referenced has already made the point that I think that gathering system statistics is generally a bad idea.
Be careful that you distinguish between “gathering” system statistics and “using” system statistics. From 10g onwards you use system statistics by default, even if you haven’t gathered them.
The dev/qa question is interesting – if you are trying to check the probable performance of the production code on a box that is significantly different from the test box then you have to worry about much more than just the system statistics: but it make sense to set them to the values used on the production box if you want to get the best chance of consistent execution plans.
Comment by Jonathan Lewis — December 22, 2010 @ 12:10 pm GMT Dec 22,2010 |
[…] (there’s a pdf of an article of mine from Oracle magazine in the 9i / 10g timeline linked at this URL) . In this case, applying the “cal > 0″ predicate first luckily eliminates the […]
Pingback by Lunchtime quiz | Oracle Scratchpad — November 29, 2014 @ 12:28 pm GMT Nov 29,2014 |
[…] – is (probably) one that I first addressed in an article in Oracle Magazine just over eleven years ago: with CPU costing enabled Oracle can change the order in which it applies filter predicates to a […]
Pingback by Predicate Order | Oracle Scratchpad — June 2, 2015 @ 7:11 pm BST Jun 2,2015 |
[…] oddities that might get a mention is that the optimizer seems to ignore the hint if you disable CPU costing. (not that anyone should be doing that since […]
Pingback by Subquery Order | Oracle Scratchpad — September 5, 2018 @ 1:09 pm BST Sep 5,2018 |