dbms_stats.set_systems_stats anomaly

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 25 Sep 2009 11:03:40 -0700
Message-ID: <bf46380909251103lc0f8cf9q1408ae81ef4d4a1b_at_mail.gmail.com>

OS: RH Linux 4
Database: 32bit 10gR2

While working out a method to set some reasonable system statson our databases, I find that dbms_stats.set_system_stats is a bit of an oddity.

According to the docs:

DBMS_STATS.SET_SYSTEM_STATS (

   pname          VARCHAR2,
   pvalue         NUMBER,

   stattab IN VARCHAR2 DEFAULT NULL,    statid IN VARCHAR2 DEFAULT NULL,    statown IN VARCHAR2 DEFAULT NULL);

Too much else to paste, so here the reference FYI: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i997707

I have the system stats in a table created with dbms_stats.create_stat_table.

When specifying the stattab, statid and statown values, the OS stats are not updated from the values in the table.

The arguments are a little odd in that if one were to specify the table to retrieve the data from, what is the point of pvalue being a required argument?

As it is, I have not seen this work properly unless only the pname and pvalue
argument are used, with me supplying the values as found in the stats table (SYSTEM_POPULATE) as created with create_stat_table.

Any insights on this?

It may be in Jonathan Lewis' CBO book, which I have unfortunately left at the office, and I am working from home.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 25 2009 - 13:03:40 CDT