Oracle Scratchpad

August 14, 2019

gather_system_stats

Filed under: CBO,Exadata,Oracle,Statistics,System Stats — Jonathan Lewis @ 2:20 pm BST Aug 14,2019

What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata’ option ?

Here’s what my system stats look like (12.2.0.1 test results) after doing so. (The code to generate the two different versions is at the end of the note).


System Stats
============
Status: COMPLETED
Timed: 13-Aug-2019 15:00:00 - 13-Aug-2019 15:00:00
--------------------------------------------------
CPUSPEED        :
CPUSPEEDNW      :          918
IOSEEKTIM       :           10
IOTFRSPEED      :      204,800
MAXTHR          :
MBRC            :          128
MREADTIM        :
SLAVETHR        :
SREADTIM        :

PL/SQL procedure successfully completed.

MBRC       :          128
MREADTIM   :
SREADTIM   :
CPUSPEED   :
CPUSPEEDNW :          918
IOSEEKTIM  :           10
IOTFRSPEED :      204,800
MAXTHR     :
SLAVETHR   :

PL/SQL procedure successfully completed.

All the code does is set the MBRC, IOSEEKTIM, and IOTFRSPEED to fixed values and the only real gather is the CPUSPEEDNW. The parameters showing blanks are deliberately set null by the procedure – before I called the gather_system_stats() every parameter had a value. You could also check the SQL trace file (with bind captured enabled) to see the statements that deliberately set those parameters to null if you want more proof.

What are the consequences of this call (assuming you haven’t also done something with the calibrate_io() procedure? Essentially Oracle now has information that says a single (8KB) block read request will take marginally over 10 milliseconds, and a multiblock read request of 1MB will take just over 15 milliseconds: in other words “tablescans are great, don’t use indexes unless they’re really precisely targetted”. To give you a quantitative feel for the numbers: given the choice between doing a tablescan of 1GB to pick 1,500 randomly scattered rows and using a perfect index the optimizer would choose the index.

To explain the time calculations: Oracle has set an I/O seek time of 10 ms, and a transfer rate of 204,800 bytes per ms (200 MB/s), with the guideline that a “typical” multiblock read is going to achieve 128 blocks. So the optimizer believes a single block read will take 10 + 8192/204800 ms = 10.04ms, while a multiblock read request for 1MB will take 10 + 1048576/204800 ms = 15.12 ms.

It’s also important to note that Oracle will use the 128 MBRC value in its calculation of the cost of the tablescan – even if you’ve set the db_file_mulitblock_read_count parameter for the session or system to something smaller; and if you have set the db_file_multiblock_read_count that’s the maximum size of multiblock read that the run-time engine will use.

Code

Here are the two procedures I used to report the values above. You will only need the privilege to execute the dbms_stats package for the second one, but you’ll need the privilege to access the SYS table aux_stats$ to use the first. The benefit of the first one is that it can’t go out of date as versions change.


rem
rem     Script:         get_system_stats.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2002
rem
rem     Last tested
rem             18.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0.4
rem

set linesize 180
set trimspool on
set pagesize 60
set serveroutput on

spool get_system_stats

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) ||  ' : ' || to_char(m_value,'999,999,999'));
        end loop;
end;
/

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       : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MREADTIM', m_value);
        dbms_output.put_line('MREADTIM   : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SREADTIM', m_value);
        dbms_output.put_line('SREADTIM   : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEED', m_value);
        dbms_output.put_line('CPUSPEED   : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEEDNW', m_value);
        dbms_output.put_line('CPUSPEEDNW : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOSEEKTIM', m_value);
        dbms_output.put_line('IOSEEKTIM  : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOTFRSPEED', m_value);
        dbms_output.put_line('IOTFRSPEED : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MAXTHR', m_value);
        dbms_output.put_line('MAXTHR     : ' || to_char(m_value,'999,999,999'));
        dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SLAVETHR', m_value);
        dbms_output.put_line('SLAVETHR   : ' || to_char(m_value,'999,999,999'));
end;
/

spool off

6 Comments »

  1. I’m confused by the title of this post – I’m not seeing any discussion of the difference made by the “exadata” option.

    Comment by Jeff — August 14, 2019 @ 2:43 pm BST Aug 14,2019 | Reply

  2. Sorry, I should have said “subject”, not “title”. First line of the post is “what happens when you execute…with the ‘Exadata’ option.” But I don’t see any discussion of the difference between “not exadata” and “exadata”. Isn’t all of the discussion of the effect of the statistics the same whether it’s exadata or not? Or are these particular statistics only gathered when you use “exadata”? What if we’re not on exadata systems? Do the system statistics behave differently?
    .

    Comment by Jeff — August 14, 2019 @ 2:47 pm BST Aug 14,2019 | Reply

    • Jeff,

      Thanks for the comment. It’s a case of: “It was so obvious to me what I meant that I didn’t notice the lack of proper explanation.”. I’ve added a sentence to point out that only the CPUSPEEDNW appears to be gathered, on the call, everything else is set to predetermined fixed values.

      Comment by Jonathan Lewis — August 14, 2019 @ 2:50 pm BST Aug 14,2019 | Reply

  3. Another simple script to display auxiliary stats is


    COLUMN sname HEADING "Stat Type" FORMAT a15
    COLUMN pname HEADING "Stat Name" FORMAT a15
    COLUMN value HEADING "Value" FORMAT a30

    SELECT sname
    , pname
    , TRIM(NVL(TO_CHAR(pval1,'999,999,999'),pval2)) value
    FROM sys.aux_stats$
    ;

    Stat Type Stat Name Value
    --------------- --------------- ------------------------------
    SYSSTATS_INFO STATUS COMPLETED
    SYSSTATS_INFO DSTART 03-23-2019 18:33
    SYSSTATS_INFO DSTOP 03-23-2019 18:33
    SYSSTATS_INFO FLAGS 0
    SYSSTATS_MAIN CPUSPEEDNW 2,981
    SYSSTATS_MAIN IOSEEKTIM 10
    SYSSTATS_MAIN IOTFRSPEED 4,096
    SYSSTATS_MAIN SREADTIM
    SYSSTATS_MAIN MREADTIM
    SYSSTATS_MAIN CPUSPEED
    SYSSTATS_MAIN MBRC
    SYSSTATS_MAIN MAXTHR
    SYSSTATS_MAIN SLAVETHR

    Comment by Vishal Gupta — August 14, 2019 @ 9:35 pm BST Aug 14,2019 | Reply

  4. System Stats History

    /*
    *
    *  Author  : Vishal Gupta
    *  Purpose : Display system statistics history
    *
    *  Revision History:
    *  ===================
    *  Date       Author        Description
    *  ---------  ------------  -----------------------------------------
    *  29-Dec-14  Vishal Gupta  Created
    *
    */
    
    
    COLUMN savtime          HEADING "SaveTime"         FORMAT a25
    COLUMN sname            HEADING "Stat Type"        FORMAT a15
    COLUMN pname            HEADING "Stat Name"        FORMAT a15
    COLUMN value            HEADING "Value"            FORMAT a30
    
    SELECT NVL(TO_CHAR(savtime,'DD-MON-YYYY HH24:MI:SS'),'Current') savtime
         , pname
         , value
    FROM
    (
    SELECT savtime
         , pname
    	    , TRIM(NVL(TO_CHAR(pval1,'999,999,999'),pval2)) value
      FROM sys.wri$_optstat_aux_history
    UNION ALL
    SELECT NULL
         , pname
         , TRIM(NVL(TO_CHAR(pval1,'999,999,999'),pval2)) value
    FROM  sys.aux_stats$
    WHERE sname = 'SYSSTATS_MAIN'
    ) a
    ORDER BY a.savtime NULLS LAST, pname
    ;
    
    

    Comment by Vishal Gupta — August 14, 2019 @ 9:40 pm BST Aug 14,2019 | Reply

    • Output of auxillary stats history

      @stats_aux_history
      
      SaveTime                  Stat Name       Value
      ------------------------- --------------- ------------------------------
      23-MAR-2019 18:32:43      CPUSPEED
      23-MAR-2019 18:32:43      CPUSPEEDNW      3,365
      23-MAR-2019 18:32:43      IOSEEKTIM       10
      23-MAR-2019 18:32:43      IOTFRSPEED      4,096
      23-MAR-2019 18:32:43      MAXTHR
      23-MAR-2019 18:32:43      MBRC
      23-MAR-2019 18:32:43      MREADTIM
      23-MAR-2019 18:32:43      SLAVETHR
      23-MAR-2019 18:32:43      SREADTIM
      23-MAR-2019 18:33:08      CPUSPEED
      23-MAR-2019 18:33:08      CPUSPEEDNW      2,981
      23-MAR-2019 18:33:08      IOSEEKTIM       10
      23-MAR-2019 18:33:08      IOTFRSPEED      4,096
      23-MAR-2019 18:33:08      MAXTHR
      23-MAR-2019 18:33:08      MBRC
      23-MAR-2019 18:33:08      MREADTIM
      23-MAR-2019 18:33:08      SLAVETHR
      23-MAR-2019 18:33:08      SREADTIM
      23-MAR-2019 18:33:10      CPUSPEED
      23-MAR-2019 18:33:10      CPUSPEEDNW      2,981
      23-MAR-2019 18:33:10      IOSEEKTIM       0
      23-MAR-2019 18:33:10      IOTFRSPEED      4,096
      23-MAR-2019 18:33:10      MAXTHR
      23-MAR-2019 18:33:10      MBRC
      23-MAR-2019 18:33:10      MREADTIM
      23-MAR-2019 18:33:10      SLAVETHR
      23-MAR-2019 18:33:10      SREADTIM
      23-MAR-2019 18:33:20      CPUSPEED
      23-MAR-2019 18:33:20      CPUSPEEDNW      2,981
      23-MAR-2019 18:33:20      IOSEEKTIM       10
      23-MAR-2019 18:33:20      IOTFRSPEED      4,096
      23-MAR-2019 18:33:20      MAXTHR
      23-MAR-2019 18:33:20      MBRC
      23-MAR-2019 18:33:20      MREADTIM
      23-MAR-2019 18:33:20      SLAVETHR
      23-MAR-2019 18:33:20      SREADTIM
      Current                   CPUSPEED
      Current                   CPUSPEEDNW      2,981
      Current                   IOSEEKTIM       10
      Current                   IOTFRSPEED      4,096
      Current                   MAXTHR
      Current                   MBRC
      Current                   MREADTIM
      Current                   SLAVETHR
      Current                   SREADTIM
      

      Comment by Vishal Gupta — August 14, 2019 @ 9:42 pm BST Aug 14,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.