Re: dbms_stats.set_systems_stats anomaly
Date: Mon, 28 Sep 2009 10:33:56 -0700
Message-ID: <bf46380909281033v4763cdceh2b18a1583fc87f58_at_mail.gmail.com>
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 <breitliw_at_centrexcc.com>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: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 28 2009 - 12:33:56 CDT