Re: dbms_stats.set_systems_stats anomaly

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

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