DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: anyone using DBMS_STATS.GATHER_SYSTEM_STATS?

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    anyone using DBMS_STATS.GATHER_SYSTEM_STATS?

    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
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width