Re: Huge numbers in SYS,AUX_STAT$

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Wed, 7 Sep 2011 17:23:42 -0700 (PDT)
Message-ID: <935631df-13bf-4a92-b3c6-4def4cbfdfb9_at_h11g2000vbc.googlegroups.com>

On Sep 7, 7:32 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> Great note! I wish I saw it earlier, I would have known immediately. It
> does need an update, though. In Oracle11, you should collect stats for
> SYSMAN, not SYSADM.
>

Thanks for the feedback regarding the article. The slides and slide notes are from a presentation that I gave to Oracle DBAs for a specific ERP package - that ERP package places the ERP data into the SYSADM schema. I should have better clarified that point when I made the blog post.

---

I first noticed the system statistics bug roughly a year ago.  I was
testing Oracle Database 11.2.0.1 on a crazy expensive laptop with two
256GB SSD drives in RAID 0 - when I gathered the system statistics and
saw the roughly 2.5 second single block read time and roughly 9 second
multi-block read time, I thought of 3 possible causes:
* The laptop is so fast that the numbers wrapped around zero. :-)
* The scale of the numbers changed so that the numbers were no longer
presented in milliseconds.
* There is a potentially ugly bug in 11.2.0.1 (and carried through to
11.2.0.2).

I was running tests at the time comparing 10.2.0.x, 11.1.0.x and
11.2.0.x and wanted to make certain that the tests were reasonably
equal, so I borrowed the system statistics that were collected by
either 10.2.0.4 or 11.1.0.7 and simply forgot about the problem.  A
couple of months later, I read an article on Christian Antognini's
Blog ( http://antognini.ch/2010/11/workload-system-statistics-in-11g/
) that explained the bug that I simply brushed off.

Randolf Geist recently wrote an article that describes another change
that he had noticed in 11.2.0.2 that is related to execution plan
costing:
http://oracle-randolf.blogspot.com/2011/07/cost-is-time-next-generation.html

---

I am sorry to hear about the chapter 7 problem.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Received on Wed Sep 07 2011 - 19:23:42 CDT