anyone using DBMS_STATS.GATHER_SYSTEM_STATS?

[stmontgo]

I am looking at trying this package.

Here's what I'm thinking of doing.

collect OLTP stats from 9-5 each day
collect night batch stats from midnight to 6am.

Now for this to work do I understand that I have to then import the stats back?
if this is the case do aocollect stats each day and then the following day import them?

tks

steve

[jmodic]

Normaly you would only collect system statistics once. Of course, if your system changes drasticaly, for example if you upgrade it with faster CPUs or faster disks, then you would normaly recollect them. But there is certanly no need to recollect them repeatedly every day.

You would store those statistics in a dedicated table, created by DBMS_STATS.CREATE_STAT_TABLE procedure.

Now you only need to flip-flop the OLTP/batch system statistics from that table into the dictionary by using DBMS_STATS.IMPORT_SYSTEM_STATS procedure at the desired intervals.

[stmontgo]

Originally posted by jmodic
Normaly you would only collect system statistics once. Of course, if your system changes drasticaly, for example if you upgrade it with faster CPUs or faster disks, then you would normaly recollect them. But there is certanly no need to recollect them repeatedly every day.

You would store those statistics in a dedicated table, created by DBMS_STATS.CREATE_STAT_TABLE procedure.

Now you only need to flip-flop the OLTP/batch system statistics from that table into the dictionary by using DBMS_STATS.IMPORT_SYSTEM_STATS procedure at the desired intervals.

cool, that's what i was looking for - tks

steve

[stmontgo]

I can't seem to get stats collected...any ideas.

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(interval => 10, stattab => 'mystats', statid => 'TEST', statown => 'SYSTEM');

and then 20 mins later i get nothing

SQL> select count(*) from mystats;

COUNT(*)
----------
0

job_queue_processes =4

tks

[OracleDoc]

You must gather system statistics with dbms_stats.gather_system_stats during a typical load on your system. You can simply run the following:

SQL> exec dbms_stats.gather_system_stats('START');

After an hour or several hours run:

SQL> exec dbms_stats.gather_system_stats('STOP');

Your system stats will be stored in sys.aux_stats$. Check the values in this table whether the value for MREADTIM is bigger than SREADTIM. If this is not the case than system statistics will not be used.