Re: dbms_stats.set_systems_stats anomaly

From: Jared Still <>
Date: Mon, 28 Sep 2009 10:33:56 -0700
Message-ID: <>

I had more time to read this today, a bit rushed last week.

Comments inline

On Fri, Sep 25, 2009 at 1:43 PM, Wolfgang Breitling <>wrote:

> 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.

I actually wanted to use the set_system_stats, as the stats are being derived.

import_system_stats may work, but I first need to see how it handles nulls in the stattable, as there may be a null. If it replaces the current value with a
null, that probably would not be good.

This is not the behavior I would expected, but one never knows until one tests.

I am going to explore a bit more with set_system_stats, as it doesn't seem to
use the statid/stattab/statown values at all.

 ...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.

Yeah, I did discover that.

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.

Yes, that is exactly what it is doing, as I just tested it.

It doesn't help that the documentation is incomplete, but also incorrect in some cases. If you look at the docs in the link I posted, you will see that some of the set_table_stats parameters are shown in the section for set_system_stats.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: Home Page:

Received on Mon Sep 28 2009 - 12:33:56 CDT

Original text of this message