Oracle Scratchpad

System Stats

Here’s a quick and dirty script I wrote some years ago to report the current settings of System Stats. The code comes in two versions, one (commented out at the end) is the original version that simply queries explicitly for the known values of the system stats parameters; the other is a newer version that queries the underlying table (sys.aux_stats$) for the list of parameter names relevant to the currently accepted system stats and uses a cursor loop to report them:


rem
rem     Script:         get_system_stats.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2002
rem

declare
        m_value         number;
        m_status        varchar2(64);
        m_start         date;
        m_stop          date;
begin
        for r1 in (
                select  rownum rn, pname 
                from    sys.aux_stats$
                where   sname = 'SYSSTATS_MAIN'
        ) loop
                dbms_stats.get_system_stats(m_status, m_start, m_stop, r1.pname, m_value);
                if r1.rn = 1 then
                        dbms_output.put_line('System Stats');
                        dbms_output.put_line('============');
                        dbms_output.put_line('Status: ' || m_status);
                        dbms_output.put_line(
                                'Timed: ' || 
                                to_char(m_start,'dd-Mon-yyyy hh24:mi:ss') ||
                                ' - ' || 
                                to_char(m_stop ,'dd-Mon-yyyy hh24:mi:ss')
                        );
                        dbms_output.put_line('--------------------------------------------------');
                end if;
                dbms_output.put_line(rpad(r1.pname,15) || ': ' || m_value);
        end loop;
end;
/


set doc off
doc


declare
        m_value         number;
        m_status        varchar2(64);
        m_start         date;
        m_stop          date;
begin
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MBRC', m_value);
        dbms_output.put_line('MBRC       :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MREADTIM', m_value);
        dbms_output.put_line('MREADTIM   :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SREADTIM', m_value);
        dbms_output.put_line('SREADTIM   :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEED', m_value);
        dbms_output.put_line('CPUSPEED   :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEEDNW', m_value);
        dbms_output.put_line('CPUSPEEDNW :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOSEEKTIM', m_value);
        dbms_output.put_line('IOSEEKTIM  :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOTFRSPEED', m_value);
        dbms_output.put_line('IOTFRSPEED :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MAXTHR', m_value);
        dbms_output.put_line('MAXTHR     :' || m_value);
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SLAVETHR', m_value);
        dbms_output.put_line('SLAVETHR   :' || m_value);
end;
/

#


You will need to have privileges to access sys.aux_stats$ to run the loop, but it may be sufficient to have the DBA role as a (very old) note in my script says that the DBA is granted the gather_system_statistics role (defined in dbmsstat.sql) which includes the object privileges to select, insert, update and delete on the underlying table.

Reading the script notes it looks like I didn’t test (or didn’t comment on) what happens if you run the script while stats are currently in flux due to an ongoing call to gather them.

Powered by WordPress.com.