Re: dbms_stats.set_systems_stats anomaly

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Fri, 25 Sep 2009 14:43:45 -0600

Jared,
I think you are getting mixed up between set_xxx_stats and import_xxx_stats. From your description I think you want to use import_system_stats, which incidentally doesn't have the pname/pvalue parameters.
The statid/stattab/statown trio in the dbms_stats procedure which gather or set statistics mean the target to where the current statistics are to be saved before being replaced with the newly gathered, or set, ones. There is, however, an "oddity" wrt. system statistics. In the dbms_stats.gather_system_stats the statid/stattab/statown trio behaves differently. Rather than saving the current statistics there and gathering the new statistics into the dictionary, the current statistics are left untouched and the new statistics go into the stattab table. I haven't tried that yet, but by extrapolation I assume the same is happening with the set_system_stats if you specify a stattab table. The new value gets stored in stattab under the statid. Of course if you use a value from the stattab table to begin with you wouldn't notice if it is replaced with the same value. 

At 12:03 PM 9/25/2009, Jared Still wrote:


OS: RH Linux 4
Database: 32bit 10gR2

While working out a method to set some reasonable system stats
on 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>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>http://jkstill.blogspot.com
Home Page: <http://jaredstill.com>http://jaredstill.com


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2009