Avoid mistakes when Gathering System Stats

Veröffentlicht von

1

Christian Antognini descibed in his book Troubleshooting Oracle Performance how to gather the system statistics in a very proficient way. The main idea of gathering system stats is to collect the system stats in a user’s table for a certain time period and to set the different system stats manually based on the gathered values. If you collect the system stats into the system table sys.aux_stats$ the optimizer will use the gathered values to „optimize“ the sql statements. Therefore if you gather the system stats into a user’s table the the optimizer will not be affected by these values – during the period of gathering system stats unless you set the values manually.

To implement the collection of gathered system stats you need four elements:

  1. a table you gather your system stats into
  2. a table you save all the gathered system stats
  3. a small pl/sql procedure called „gather“ to gather the system stats into a user table 1) and save the values to 2)
  4. a database job calling the procedure every hour (the code will not be shown here)

To create 1) and 2) you need to execute the following pl/sql block:

1
2
3
4
5
6
7
8
9

DECLARE
  c_tab      CONSTANT VARCHAR2(20) := 'SYSSTATS_1';
  c_tab_hist CONSTANT VARCHAR2(20) := c_tab||'_HIST';
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (ownname => USER, stattab => c_tab);
  DBMS_STATS.CREATE_STAT_TABLE (ownname => USER, stattab => c_tab_hist);
  EXECUTE immediate('alter table '||c_tab_hist||' add norecs number');
END;
/

The procedure 3) may look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

CREATE OR REPLACE PROCEDURE scott.gather
IS
 c_tab      CONSTANT VARCHAR2(20)   := 'SYSSTATS_1';
 c_tab_hist CONSTANT VARCHAR2(20)   := c_tab||'_HIST';
 c_cmd      CONSTANT VARCHAR2(4000) := 'insert into '||c_tab_hist||
                            ' select s.*, (select count(*) from '||
                            c_tab_hist||') NORECS '||
                            ' from ' ||c_tab ||' s';
BEGIN
  -- start gathering system stats
  dbms_stats.gather_system_stats(gathering_mode  => 'START'
                               , INTERVAL        => NULL
                               , statown         => 'SCOTT'
                               , stattab         => c_TAB);
 
  -- wait 57 Minutes
  dbms_lock.sleep( 60*60 - 3*60 );
 
  -- stop gathering system stats
  dbms_stats.gather_system_stats(gathering_mode => 'STOP'
                               , INTERVAL       => NULL
                               , statown        => 'SCOTT'
                               , stattab        => C_TAB);
 
  -- save / append all values from table 1) to table 2)
  EXECUTE immediate(c_cmd);
  commit;
EXCEPTION
  WHEN others THEN
    NULL; -- here you should have an exception handler to log all errors
END gather;
/

This procedure „gather“ will start the gathering of system statistics into the table SCOTT.SYSSTATS_1. Then it will wait for 57 Minuten and stop the gathering of system stats. Thereafter the gathered system stats will be inserted into the table SCOTT.SYSSTATS_1_HIST. The column NORECS will count the number of previous inserted rows.

NOTE: you need the privelege to execute the package dbms_lock

Some days before at customers site I did not had the privilege to execute this procedure. So, I had to change the procedure and used the INTERVAL method. Instead of execute first dbms_stats.gather_system_stats by gathering_mode => ‚START‘ I used gathering_mode => ‚INTERVAL‘ and specify the minutes the procedure has to gather the system stats. I changed my procedure inside to

1
2
3
4
5
6
7
8
9

BEGIN
  dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL'
                               , INTERVAL       => 57
                               , statown        => 'SCOTT'
                               , stattab        => c_TAB); 
  EXECUTE immediate(c_cmd);
  commit;
END gather;
/

But, this new procedure did not work! This procedure did not collect any values. All essential columns are NULL without the start and end time. My mistake was: I was thinking that the procedure will wait for 57 minutes to gather all values, however the procedure will not wait. When execute the procedure the gathering process would be started in background while the procedure finished after 1 or 2 seconds. Therefore the INSERT directly after the procedure cannot find any data.
So, I switched the order of the INSERT and the procedure: everything works fine. Every hour when the procedure is calling by a job scheduler of the database the table 2) is filled. The INSERT will transfer the data of the previous run to the HIST table. Executing the procedure thereafter will empty the SYSSTATS_1 table and the gathering of new records of system statistics started again. Here the final code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE OR REPLACE PROCEDURE scott.gather 
IS
  c_tab       CONSTANT VARCHAR2(20)   := 'SYSSTATS_1';
  c_tab_hist  CONSTANT VARCHAR2(20)   := c_tab||'_HIST';
  c_cmd       CONSTANT VARCHAR2(4000) := 'insert into '||c_tab_hist||
                                         ' select s.*, (select count(*) from '|| c_tab_hist||
                                         ') NORECS '|| ' from ' ||c_tab ||' s';
BEGIN
  -- save the data of the previous run
  EXECUTE immediate(c_cmd); 
  commit;
 
  -- start gathering again
  dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL'
                               , INTERVAL       => 57
                               , statown        => 'SCOTT'
                               , stattab        => c_TAB); 
EXCEPTION
 WHEN others THEN
   NULL; -- here you should have an exception handler to log all errors end gather; 
END gather;
/

Using the view „ALL_SYSSTATS_1“ you can see gathered values saved in SYSSTATS_1_HIST:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE VIEW ALL_SYSSTATS_1(
          INTERVAL
        , START_DATE
        , END_DATE
        , CPUSPEED
        , SREADTIM
        , MREADTIM
        , MBRC
        , MAXTHR
        , SLAVETHR)
AS 
SELECT 'sysstats_1'                          INTERVAL
      , C2                                   START_DATE
      , C3                                   END_DATE
      , round(N3,2)                          CPUSPEED
      , round(N1,2)                          SREADTIM
      , round(N2,2)                          MREADTIM
      , round(N11,2)                         MBRC
      , round((SELECT sa.N1 
               FROM SYSSTATS_1_HIST sa 
               WHERE sa.C4 = 'PARIO' 
               AND sa.NORECS = s.NORECS),2)  MAXTHR
      , round((SELECT sb.N2 
               FROM SYSSTATS_1_HIST sb 
               WHERE sb.C4 = 'PARIO'
               AND sb.NORECS = s.NORECS),2)  SLAVETHR 
FROM (SELECT * 
      FROM SYSSTATS_1_HIST s
      WHERE s.N1 IS NOT NULL
      AND s.N2 IS NOT NULL) s
WHERE s.c4 = 'CPU_SERIO'
AND N3 IS NOT NULL
AND N11 IS NOT NULL
ORDER BY s.NORECS DESC
;

Based on these values you choose the values you will use to set the different system statistics manully. A proposal will give you the view „SYSSTATS_AVG“:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE OR REPLACE VIEW SYSSTATS_AVG(
      NO_OF_RECS
    , CPUSPEED 
    , SREADTIM
    , MREADTIM
    , MBRC
    , MAXTHR
    , SLAVETHR)
AS
SELECT COUNT (*)                    NO_OF_RECS
     , ROUND (AVG (CPUSPEED), 2)    CPUSPEED
     , ROUND (MAX (SREADTIM), 2)    SREADTIM
     , ROUND (MAX (MREADTIM), 2)    MREADTIM
     , ROUND (MAX (MBRC), 2)        MBRC
     , ROUND (AVG (MAXTHR), 2)      MAXTHR
     , ROUND (AVG (SLAVETHR), 2)    SLAVETHR
FROM ALL_SYSTATS_1 s
;

 

Thanks to Christian Antognini, Franck Pachot and Iradj Atchatchloui for their support.

 

Rainer Hartwig

One thought on “Avoid mistakes when Gathering System Stats”

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.