System Stats

A quick collation – and warning – for 11.2

• MOS (Metalink): Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID 9842771.8] (needs MOS account)
• Comment from Sokrates in a Charles Hooper blog
• Blog item by Christian Antognini
• Blog item by Randolf Geist

Bottom line – be careful about what you do with system stats on 11.2

Footnote: the MOS link is a search string  producing a list of references. I set it up like that because one of the articles referencing the bug is called “Things to consider before upgrade to 11.2.0.2″ and it’s worth reading.

Addendum: one of the people on the two-day course I’ve just run in Berlin sent me a link for a quick note on how to set your own values for the system stats if you hit this bug. It’s actually quite a reasonable thing to do whether or not you hit the bug given the way that gathering the stats can produce unsuitable figures anyway:  setting system stats. (I’ve also added their company blog to the links on the right, they have a number interesting items and post fairly regularly.)

System Statistics

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.

[More on System Statistics]

System Statistics 3

In a recent thread on one of the Oracle Forums, someone asked the question:

So, in general, if systems statistics are in effect, would you or would you not make any adjustments to the optimizer_index_cost_adj and optimizer_index_caching parameters?

Under what circumstance?

This is my reply:

System Stats strategy

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).

System Statistics

In chaper 2 of Cost Based Oracle – Fundamentals, I made the following comment about system statistics.

“… you could simply calibrate your hardware (or at least the I/O subsystem) for absolute performance figures …”

ORA-01722: upgrade error

I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 9.2.0.6 to 10.2.0.2.  After the upgrade, the following SQL statement (shown here with its original format – not according to my standards) started failing with Oracle error: ORA-01722: invalid number.

Optimizer_index_cost_adj

[Updated 28th November 2011] – just after the fifth anniversary – to mark this as the first post of the Oracle Scratchpad.

A recent post on one of the OTN Database General forum pages asked about the effect of having the parameter optimizer_index_cost_adj set when you enable system statistics (also known as CPU costing).