Oracle Scratchpad

October 2, 2008


Filed under: CBO,Oracle,Troubleshooting,Upgrades — Jonathan Lewis @ 7:36 am BST Oct 2,2008

I posted a summary of page hits a little while ago when the WordPress statistics hit 750,000 page views, and commented at the time that a couple of the most popular items seemed to be related to the types of issue you get when you upgrade to 10g.

It occurred to me this morning that I could take a closer look at the stats, because WordPress has a page which gives a fairly comprehensive break down of the views made over time of each page. I’ve repeated the top three from the previous list and added one more, with a brief comment about their popularity over time:

It would be very easy to read too much into these numbers – but one of the side effects of an upgrade to 10g is the impact of “Cost Based Query Transformation” and the clever (sometimes too clever) things it does to execution plans; and three of the top four articles are likely to be of interest to people seeing these side effects.  On the other hand, 10g doesn’t really do anything dramatically different with bind variable issues and the views for that topic haven’t changed.

Would it be reasonable to suggest that this might be an indication of a surge of people migrating to 10g in the March time-frame ?


  1. It seems so – although I do not have any evidence to back this either way.

    While we’re at it: I am under the impression that system stats are somewhat underestimated. One can read frequently about table and schema stats but it is rarely suggested in public forums to check system stats although they can have a dramatic impact on execution plans (namely if MREADTIM < SREADTIM). Does anybody else have that impression of underestimation?


    Comment by Robert Klemme — October 13, 2008 @ 12:06 pm BST Oct 13,2008 | Reply

  2. Robert,

    I think system stats don’t get mentioned much because they aren’t automatic in 9i, and the defaults in 10g are generally adequate so few people notice that they are there.

    Certainly, though, there were some cases where odd things happened in the early days, in some versions, when people tried to use them in 9i and got odd values – so any trouble-shooting job should include a quick check on current values.

    Comment by Jonathan Lewis — October 18, 2008 @ 7:52 am BST Oct 18,2008 | Reply

  3. Thanks, Jonathan!

    We had a situation at a customer site where MREADTIM < SREADTIM. We then experimented with those system stats and table stats from the production DB in a test database. We never got the FTS back so I figured that the system stats must have had this effect. (I also found a comment with similar content somewhere.)

    Comment by Robert Klemme — October 18, 2008 @ 1:20 pm BST Oct 18,2008 | Reply

  4. Robert,
    I’ve inserted the “less than” sign the WordPress took out. (I assume that’s what it was).

    I suspect this must have been early in 9.2 or 10.2 – I’m fairly sure I remember seeing cases where the mreadtim was used in the arithmetic even when it was smaller than the sreadtim and the whole mreadtim / sreadtim adjustment should have been ignored.

    Comment by Jonathan Lewis — October 18, 2008 @ 4:41 pm BST Oct 18,2008 | Reply

  5. Jonathan, thanks for fixing the comment! Yes, this was with an unpatched IIRC.

    Comment by Robert Klemme — October 19, 2008 @ 1:54 pm BST Oct 19,2008 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: