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:
- Cartesian merge join – always popular, but a bit of a jump from Mar 2008
- Bind Variables – no significant change since original publication
- dbms_xplan in 10g – sharp jump from Mar 2008
- Not In – very sharp jump from Mar 2008
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 ?
Oracle Scratchpad 
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?
Greetings
Comment by Robert Klemme — October 13, 2008 @ 12:06 pm BST Oct 13,2008 |
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 |
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 |
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 |
Jonathan, thanks for fixing the comment! Yes, this was with an unpatched 10.2.0.2 IIRC.
Comment by Robert Klemme — October 19, 2008 @ 1:54 pm BST Oct 19,2008 |