Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Gather or Not? Eng.

Gather or not?

It certainly is not about potatoes, but about the optimizer system statistics of the Oracle RDBMS. With potatoes all clear, has matured means it’s time to gather, but what about the system statistics?

What are the optimizer system statistics and why is it necessary? Optimizer System Statistics is a set of parameters defining the various system properties such as processor speed, the average number of simultaneous read blocks (multiblock read count), etc. As well as such factors as the time of reading single block from the disk and simultaneous readout of the set of blocks (multiblock read time).

Oracle query optimizer uses these statistics in his calculations of the optimal plan for executing queries, it uses the values of these parameters of the system for calculating the I/O Cost and CPU Cost of the plan, that affects the final query execution plan. Such indicators as a small bandwidth of the I/O subsystem, a great multiblock read time or the low value of the average multiblock read count increase the cost of plans using a full table scan or leads to choosing nested loops rather than hash joins in optimizer decisions. In contrast high values of these parameters will lead to lower prices of the full scan operations and using hash joins.

And now about how and when to collect system statistics, and what if it is not collected. Oracle does not automatically collect system statistics, but he has a set of default values, which he uses in the absence of the collected statistics. But not all system parameters have default values, but the main ones. Collect, edit and view system statistics can be done using the system package for statistical data management DBMS_STATS under schema SYS. You must be assigned the DBA  role or GATHER_SYSTEM_STATISTICS role to work with system statistics.

How often to collect statistics depends on the stability of your system. Before you will gather statistics for the first time, if you’ve never done it before or have forgotten when you did it last time, it is strongly recommended to gather information about current execution plans for queries. That will help you to identify plan changes after collecting the statistics, if they occur. It is also necessary to keep the current values of optimizer system parameters, if they exists, how to do this is discussed below. If your database version is 10g or above you can use AWR  repository which stores a data for the last 7 days by default, including the query execution plans. Changing the underlying systems properties and/or the type and amount of load on the database system lead to the need to re-collect optimizer system statistics, as Oracle itself didn’t know about these changes, we had to inform about it. The number of such changes may include, for example, such as upgrade of your CPU’s to the more efficient ones, transferring database files to the high-speed data carriers, change the storage method, for example from the file system to ASM or any other physical changes to the underlying system that you believe may affect any of the optimizer system parameters. As well as factors such as increased load on the system that also will increase the contention for the storage medium or database role change from OLTP to DSS or vice versa. In a word collection of system statistics is the way of informing Oracle RDBMS about the changes occurring in the system to these changes can be adequately taken into account by optimizer in computing the optimal way to access the data.

About Optimizer System Statistics Parameters

First and foremost it should be noted that the system statistics can be of two kinds:

  • Workload Statistics, taking into account the actual load on the system
  • Noworkload Statistics, collected without actual load on the system

Each of this types has its own set of parameters.

Workload statistics, this type of statistic takes into account the actual load on the system at a certain time interval. To collect this type of statistics gathering  mode INTERVAL, START and STOP is used by procedure DBMS_STATS.GATHER_SYSTEM_STATS.

INTERVAL – using this mode, statistics will be collected within a certain time interval. Time during which data will be collected is starting with the launch time of the procedure and lasts for a given parameter interval in minutes. For example, to collect statistics from the current time and with duration of 12 hours, we should run the following command:

SQL> begin
  2  sys.dbms_stats.gather_system_stats(
  3                                     gathering_mode=>'INTERVAL',
  4                                     interval=>720
  5                                    );
  6  end;
  7  /

interval parameter is set in minutes, by default it is 60 minutes. After running the command control is returned to the user, and you can close the session if you want.

START / STOP – this method as you’ve guessed, first requires running the procedure with the parameter START and then setting STOP completes the collection of statistics. In this case you will have to measure the time required for complete statistics by yourself, for example:

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

-- After some time

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

These commands are not required to perform within a single session, i.e. you can run the START command and close the session, after some time open a new session and run the STOP command to complete the collection. Incidentally, the command STOP, can also be used to terminate the current statistics collection launched by INTERVAL mode if you think that was enough time and you do not want to wait until the specified interval will finish.

  • mbrc
  • mreadtim
  • sreadtim
  • cpuspeed
  • maxthr
  • slavethr

mbrc (multi block read count) this parameter determine the average number of blocks which the system reads in one I/O operation at a full table or index scans. Optimizer uses this parameter to calculate the cost of the full scans. And, if this parameter value would be large enough, the price of a full scan might be cheaper than access by index!

mreadtim (multi block read time) this parameter determine the average time in milliseconds required by the system to reading multiple blocks at a time. If this time turns out to be quite short, the cost of a full scan might be cheaper.

sreadtim (single block read time) this parameter determine the average time to read in milliseconds of single block, for example when accessing the index on the key.

cpuspeed as the name implies, this parameter keeps the average speed of the processor that was in the period of statistics collection, it is displayed in million/sec.

maxthr (maximum throughput) this parameter determine the maximum amount of bandwidth that I/O subsystem can deliver, the number of bytes per second.

slavethr (slave throughput) and this indicates the average throughput of the parallel execution slave process, in bytes per second.

With Workload statistics the counters is created in the buffer cache that incremented after each read operation. Since these counters are in the buffer cache, the calculation of time spent for reading the block(s) i.e. sreadtim and mreadtim also will consider buffer cache latch waits and the time required for cache internal operations with the block. Therefore, if the collection of this type of statistical information was in a state of overload and high contention for the blocks in the buffer cache, then it will affect the value of these parameters and consequently the optimizer that will lead, after gathering statistics, to choose plans using the least amount of I/O operations . You should also take into account the fact that, for the collection of workload statistics, in particular parameters mbrc and mreadtim at least one complete full scan must take place in the period of gathering. Otherwise, these parameters will have no values. Keep in mind that in parallel full table scan blocks bypass the buffer cache, they are read into the PGA of parallel slaves. And as the counter resides in the cache buffer, these data are not counted. To ensure that the necessary statistics can be assembled in a period of gathering time, manually run the full table scan.

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

SQL> alter session disable parallel query;

SQL> select /*+ FULL(a) */ count(*) from bigtable;

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

Yet it must be noted that the process of gathering system statistics does not affect system performance!

Nowrokload statistics, this type of statistics used in the absence of load on the system. The system will simulate the load by conducting random I/O operations across all database files.

Optimizer system statistics parameters computed by Noworkload collection are

  • cpuspeedNW
  • ioseektim
  • iotfrspeed

cpuspeedNW parameter determines the speed of the processor, the default value for this parameter is determined at the very first startup of the database instance. And is not changed until you initiate a new collection.

ioseektim this parameter indicates the speed of the disk head seek time, its default value is 10 milliseconds (ms).

iotfrspeed (io transfer speed) is the speed of disk reads per read operation, the default 4096 bytes/ms, it is fairly low value, many systems are capable of more.

If there is collected Workload statistics then Noworkload statistics are automatically ignored by the query optimizer. Noworkload statistics is collected by GATHER_SYSTEM_STATS procedure with no parameters:

SQL> exec sys.dbms_stats.gather_system_stats();

Using Noworkload statistics is not recommended.

Useful Tips

In all subsequent examples we assume that our database system is of mixed type, i.e. at daytime it operates as OLTP and at nighttime as a DSS to generate reports and load/unload data.

First of all, let’s look at the ways you can see the current values of the optimizer system statistics parameters. You can use the procedure GET_SYSTEM_STATS of the DBMS_STATS package, but if you’re not a masochist then this information can be shown using the following query from system table:

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

PL/SQL procedure successfully completed.

SQL> set linesize 200
SQL> set pagesize 200
SQL> col pname format a15
SQL> col pval2 format a15
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     MANUALGATHERING
SYSSTATS_INFO                  DSTART                     12-28-2010 10:48
SYSSTATS_INFO                  DSTOP                      12-28-2010 10:48
SYSSTATS_INFO                  FLAGS                    1
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             7.699
SYSSTATS_MAIN                  MREADTIM             8.601
SYSSTATS_MAIN                  CPUSPEED              1050
SYSSTATS_MAIN                  MBRC                    13
SYSSTATS_MAIN                  MAXTHR               66560
SYSSTATS_TEMP                  SBLKRDS              38141
SYSSTATS_TEMP                  SBLKRDTIM           232620
SYSSTATS_TEMP                  MBLKRDS              51853
SYSSTATS_TEMP                  MBLKRDTIM           286600
SYSSTATS_TEMP                  CPUCYCLES          2454144
SYSSTATS_TEMP                  CPUTIM             2424414
SYSSTATS_TEMP                  JOB                      0
SYSSTATS_TEMP                  CACHE_JOB                5
SYSSTATS_TEMP                  MBRTOTAL            664098

22 rows selected.

As seen from the listing, we start collecting Workload statistics, in manual mode, i.e. using START/STOP method. Let us examine the contents of the sys.aux_stats$ table. Column SNAME, divides the contents of the table into 3 parts, it is SYSSTATS_INFO, SYSSTATS_MAIN and SYSSTATS_TEMP.

SYSSTATS_INFO displays the current status of system statistics


COMPLETED – statistics gathering successfully completed

AUTOGATHERING – statistics gathering is in the process of automatic collection (INTERVAL)

MANUALGATHERING – statistics gathering is in the process of manual collection (START/STOP)

BADSTATS – system statistics is in the corrupted, incorrect state, recollection or deletion is needed

DSTART – start time of the statistics gathering

DSTOP – time when the gathering was completed when STATUS has a value of COMPLETED, a time when the collection will be completed if STATUS is set to AUTOGATHERING. When STATUS is MANUALGATHERING then this field is equal to the value of DSTART field, since the system didn’t know when you will stop statistics gathering. Once you run gather_system_stats (‘STOP’) status changes to COMPLETED, and DSTOP will be set to the complete time.

FLAGS – This parameter is internal and does not represent any interest for us

SYSSTATS_MAIN contains actual system parameters and their values, they will be changed only after the current gathering will be completed, while the gathering  is not finally completed, this parameters indicate the old values.

SYSSTATS_TEMP this part of the table contains temporary information, the intermediate values used by the system for the final count. This information will be automatically removed after completion of the gathering process.

Thus, as seen from our example above, STATUS is MANUALGATHERING as our manual collection is underway and DSTOP equals to DSTART, also there is the temporal statistics. Lets stop collecting statistics:

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

PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     COMPLETED
SYSSTATS_INFO                  DSTART                     12-28-2010 10:48
SYSSTATS_INFO                  DSTOP                      12-28-2010 11:54
SYSSTATS_INFO                  FLAGS                    0
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             9.562
SYSSTATS_MAIN                  MREADTIM            13.158
SYSSTATS_MAIN                  CPUSPEED              1048
SYSSTATS_MAIN                  MBRC                     8
SYSSTATS_MAIN                  MAXTHR               66560

13 rows selected.

We have completed the gathering. So what changed? STATUS now has a value of COMPLETED and DSTOP indicates the completion time of gathering process. We also see that some system parameters have also changed, for example sreadtim and mreadtim changed from 7,699 and 8,601 respectively to 9,562 and 13,158, and mbrc from 13 blocks to 8. This means that during the gathering process system was in a heavy loaded state. Therefore, the system statistics should be collected at the time when your system is in the normal load state. For example, if you collect statistics from 7 am to 9 am, in our case it will not show the real capabilities of the system, as an overnight processes has been completed and the daily workload has not yet begun. In our case, we need to collect two statistics one for the daytime and one for the nighttime, as our system of mixed type, how to do it will discuss later.

Yet we see that the parameter slavethr have no value, it’s because we have been collecting statistics on the daytime when our system operates in OLTP mode, and do not use parallel operations, if we collect statistics on the nighttime, dominated by reports in the parallel mode, this parameter will have an appropriate value.

If you are lucky user of the Oracle version 10g or above, to compare the query execution plans, you can use the AWR repository, i.e., DBA_HIST_% views. The overall scheme should look like this:

1. Start the gathering of statistics
2. Check the state of the system to detect changes in execution plans, if they occur

So, the first step:

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') "Current Time" from dual; 

Current Time
28.12.2010 10:00:48

Start the collection of statistics for a duration of 4 hours, i.e. 240 minutes.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 240);

PL/SQL procedure successfully completed.

By two o’clock our statistics gathering should be completed, check:

SQL> set linesize 200
SQL> set pagesize 200
SQL> col pname format a15
SQL> col pval2 format a15
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     COMPLETED
SYSSTATS_INFO                  DSTART                     12-28-2010 10:01
SYSSTATS_INFO                  DSTOP                      12-28-2010 14:01
SYSSTATS_INFO                  FLAGS                    0
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             6.001
SYSSTATS_MAIN                  MREADTIM             9.788
SYSSTATS_MAIN                  CPUSPEED              1050
SYSSTATS_MAIN                  MBRC                    15
SYSSTATS_MAIN                  MAXTHR              176560

13 rows selected.

Statistics gathering is completed.

The second step is to check the system on a possible changes in the query execution plans. To do this we need to use the AWR repository, which in turn consists of snapshots of the system. By default, system takes snapshots every hour. According to the value of DSTOP filed our statistics collection is completed in just a minute after the last snapshot of the system. A new plan may take effect only after 14:01, as at this time the new statistics will take effect. Process MMON dropped the 14:00 hour collected information about the system to disk as a snapshot, now it began to accumulate new information, and as you understand it is impossible to collect much in one minute. So we either have to wait an hour before the next auto snapshot or, if we hurry we can wait at least half an hour and with hope that enough information about the queries interested to us is collected, use dynamic views to access current AWR information directly from SGA or to manually take snapshot and use the repository. For integrity of the example, I use the second option.

If you have a very critical queries that may change the execution plan for the worse, I advise you to keep them on hand and immediately after the collection of system statistics to verify their execution plans for changes.

So, passed half an hour, lets take a manual snapshot:

SQL> var snap_id number;
SQL> exec :snap_id:=dbms_workload_repository.create_snapshot();
SQL> print :snap_id


Now snapshot 789 keeps queries and plans generated using new system statistics, i.e. our group of risk.

Now that our repository contains enough information, we need to identify those queries that interest us. Query text for which information is contained in the repository can be selected from the DBA_HIST_SQLTEXT view. This can be done either visually viewing field SQL_TEXT or by query. For example, I am interested in queries to the CUSTOMERS table that is one of the most critical for my applications. By the way, do not use queries that accesses the remote object via database links, information about their plans is not preserved in repository.

SQL> select sql_id, sql_text from dba_hist_sqltext where upper(sql_text) like '%CUSTOMERS%';

------------- --------------------------------------------------------------------------------

We defined a query, SQL_ID for it is “dcxm4kr1urg93”, now we need to compare the execution plans of this query from snapshot 789 with plans from earlier snapshots, for example, we take a snapshot 786. To do this we will use the DBA_HIST_SQLSTAT view, there is a field PLAN_HASH_VALUE, this field contains the result of the hash function of the query plan, i.e. plan displayed as just a single digit. Instead of bulky compare plans line by line to determine the changes, you can compare only two digits, and if they differ it will mean a change of execution plan. And only if the change is present, we will examine plans itself. So:

select   snap_id, plan_hash_value
    from dba_hist_sqlstat
   where snap_id in (789, 786) and sql_id = 'dcxm4kr1urg93'
order by snap_id desc;

---------- ---------------
       789      2018760100
       786      1582437882

As we can see the hash value of our plans are different, it means that the plan was changed. Now we need to define is these changes for the better or not, if the system is configured correctly then the changes, if any, should be improved. To view the execution plan of this query we can use directly DBA_HIST_SQL_PLAN view which displays execution plans of queries present in the repository. However, there is a more convenient way to take advantage of the remarkable procedure in the package DBMS_XPLAN, which displays the query plan and its implementation in a readable form, it is DISPLAY_AWR procedure. This procedure is designed specifically for formatting and displaying plans from the AWR repository. It will be necessary just to run this procedure twice, it takes two parameters sql_id and plan_hash_value

select * from table(dbms_xplan.display_awr('dcxm4kr1urg93',2018760100));

select * from table(dbms_xplan.display_awr('dcxm4kr1urg93',1582437882));

Now you can visually identify changes in plan and make your decisions.

Also can be useful next query. It will help you determine all queries for which the plan has changed between any two snapshots. In our example, we derive the difference between snapshots 789 and 786:

variable snap_id_1 number;
variable snap_id_2 number;
exec :snap_id_1:=789;
exec :snap_id_2:=786;
select   a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
    from dba_hist_sqlstat a, dba_hist_sqlstat b
   where (a.snap_id = :snap_id_1 and b.snap_id = :snap_id_2)
     and (a.sql_id = b.sql_id)
     and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;

------------- -------------- --------------
dcxm4kr1urg93     2018760100     1582437882
dgyj6z7xacxjm     2654526844     4032830135
cf621qmts91wf     1366804877     1860467754
7ng34ruy5awxq     3691521353     3984801583
31a13pnjps7j3     1921616694     1196813614
0dwr2vd6vbqzs     3812733207     4046359532

Here’s another useful query that you may need, it extracts the queries whose execution plans ever changed, the request includes all the snapshots that make up the AWR repository:

select distinct sql_id, plan_hash_value, f snapshot,
                (select begin_interval_time
                   from dba_hist_snapshot
                  where snap_id = f) snapdate
           from (select sql_id, plan_hash_value,
                        first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
                   from (select   sql_id, plan_hash_value, snap_id,
                                  count (distinct plan_hash_value) over (partition by sql_id) a
                             from dba_hist_sqlstat
                            where plan_hash_value > 0
                         order by sql_id)
                  where a > 1)
       order by sql_id, f;

------------- --------------- ---------- ---------------------------
0cjngzmtm4yv1      2832338232        826 30-DEC-10 AM
0cjngzmtm4yv1      3190648541        826 30-DEC-10 AM
0dwr2vd6vbqzs      4046359532        679 23-DEC-10 PM
0dwr2vd6vbqzs      3812733207        800 28-DEC-10 PM
0fr8zhn4ymu3v      3355330683        655 22-DEC-10 PM
0fr8zhn4ymu3v      2692826851        836 30-DEC-10 AM
0vwa9n600yvgm      3485788549        814 29-DEC-10 PM
0vwa9n600yvgm      3865505343        814 29-DEC-10 PM
130dvvr5s8bgn      2800640262        654 22-DEC-10 PM
130dvvr5s8bgn       464519793        726 25-DEC-10 PM
1b28hzmjun5t0      2418697239        677 23-DEC-10 PM
1b28hzmjun5t0      3693292387        709 25-DEC-10 AM
1gu8t96d0bdmu        17605035        654 22-DEC-10 PM
1gu8t96d0bdmu      2959582498        702 24-DEC-10 PM
2syvqzbxp4k9z      3223879427        701 24-DEC-10 PM
2syvqzbxp4k9z       105727130        822 29-DEC-10 PM
2xyb5d6xg9srh      1783251294        701 24-DEC-10 PM
2xyb5d6xg9srh        78148816        822 29-DEC-10 PM

From the query results can be seen that, for example, the query “0dwr2vd6vbqzs” in the snapshot 679 has an execution plan 4046359532 and in the snapshot 800, it changed to 3812733207. Use the package DBMS_XPLAN to view plans for this query, the example given above.

If it turns out that the execution plan has not changed for the better, you should take the following measures, primarily to return the system to a previous state so that applications do not suffer while you figure out the problem, then do clarifying the problem, check how long ago was statistics gathered for the database objects, check out some initialization parameters.

How to recover a previous state? First, let’s consider such necessary procedures of the DBMS_STATS package as:

  • get_stats_history_retention (function)
  • get_stats_history_availability (function)
  • restore_system_stats
  • alter_stats_history_retention
  • delete_system_stats
  • create_stat_table
  • export_system_stats
  • import_system_stats

DBMS_STATS package procedures responsible for the collection of statistics, not necessarily the system or database objects before update statistics preserve the old statistics in a special repository, which is similar to the AWR repository (SM/AWR) is called SM/OPTSTAT and also stored in the tablespace SYSAUX, this repository also has a retention time of data, by default equal to the 31 days. This allows you at any time to restore the statistics for any database object or system statistics to its former state or condition at any point in the time.

get_stats_history_retention – this function returns current value of the old optimizer statistics retention time

get_stats_history_availability – this function returns the maximum available date to witch statistics can be restored

SQL> select dbms_stats.get_stats_history_retention from dual;


SQL> select dbms_stats.get_stats_history_availability from dual;

26-NOV-10 PM +04:00

As can be seen from listing retention time for the old statistics is the 31 days and the maximum date for which statistics can be restored 26th November. With a query from the V$SYSAUX_OCCUPANTS dynamic view we can determine how much space is occupied by old optimizer statistics in the SYSAUX tablespace

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name=’SM/OPTSTAT’;

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History                          192576

190 MB is a occupied in tablespace SYSAUX. Retention period of statistics can be changed using the procedure alter_stats_history_retention, for example, set this value to 40 days:

SQL> exec dbms_stats.alter_stats_history_retention(40);

PL/SQL procedure successfully completed.

Restore_system_stats procedure restores system statistics on the state at the specified time

procedure restore_system_stats(as_of_timestamp timestamp with time zone);

For example to restore the system statistics on the condition that it was on the 10th December 09:00 am:

SQL> exec dbms_stats.restore_system_stats(to_date('10.12.2010 09:00:00','dd.mm.yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

delete_system_stats deletes system statistics.

create_stat_table this procedure is necessary to create a table to store the statistics, here’s specification:

procedure create_stat_table(
             ownname varchar2,
             stattab varchar2,
             tblspace varchar2 default null,
             global_temporary boolean default false);
  • ownname – table owner name
  • stattab – table name
  • tblspace – tablespace name, by default owners tablespace
  • global_temporaryallows you to create a table in the form of a global temporary table, the information in it will be automatically cleared at the end of the session. This feature allows you to not clutter up the tablespace. Since the old statistics are already stored in a special repository it doesn’t make sense to store this information manually. If the table will be used for intermediate results then it is better to create a global temporary table.

export_system_stats this procedure allows you to export the current statistics in to the user table created by the procedure create_stat_table

procedure export_system_stats (
                  stattab  varchar2,
                  statid   varchar2 default null,
                  statown  varchar2 default null);
  • stattab – target table name
  • statid – statistics identification value, it is useful to uniquely identify exported set of statistics
  • statown – table owner name

import_system_statsthis procedure allows you to import statistics from the user table back into the system

procedure import_system_stats (
   stattab  varchar2,
   statid   varchar2 default null,
   statown  varchar2 default null);
  • stattab – source table name
  • statid – identification value of the statistics that you want to import
  • statown – table owner name

So, going back to how to restore the statistics for the situation when the query plan has changed to the bad side.

  • If you first time collect system statistics, i.e. before that it did not exist, then use the procedure delete_system_stats
  • If there already was statistics then use restore_system_stats or procedures export_system_stats and import_system_stats


Lets delete optimizer system statistics

SQL> exec dbms_stats.delete_system_stats();

Example of restoring statistics, see above. Consider the option of export/import existing statistics. To start, create a table for temporary storage of the statistics, we call it TEMPSYSSTAT in SYS schema:

SQL> exec dbms_stats.create_stat_table('SYS','TEMPSYSSTAT',null,true);

Now, before we start gathering, export current statistics and identify it as a BEFORE_GATHER

SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

Collect system statistics, we will not close the session as at the closing session our exported statistics will be lost. After gathering, check query plans, and if there are problems import the previous statistics back to the system

SQL> exec dbms_stats.import_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

If the execution plan generated by the optimizer is not correct, and you know that there is an option in which the query performance is better, do not rush to use hints. Using a hint will bypass the problem associated with a particular query, but what if there are other queries with invalid execution plans, because if the optimizer has generated an incorrect plan for one query then it can do the same for any other! It is necessary to solve the problem. There are many factors that can influence the choice of plan. We must first check the freshness of the statistics of all objects in the database, check the histograms on the columns involved in the query, gather statistics as required. Also check state of the indexes, the clustering factor, the ratio of the number of unique keys to the total number of rows. Also check out the initialization parameter optimizer_index_cost_adj, it greatly affects the choice of how to access the data. Again, you could choose not the best time to collect system statistics, recollect it in another time.

Since the load on the system in our example database is different in nighttime and daytime that we appropriate to have two sets of system optimizer statistics. One of them gathered at nighttime and another one in the afternoon. At the daytime will be activated daytime statistics and at the night nighttime. So we collect two sets of statistics, we call them DAYSTATS and NIGHTSTATS. But to start, create a table for permanent storage of our statistics, we will name it PERMSYSSTAT

SQL> exec dbms_stats.create_stat_table('SYS','PERMSYSSTAT');

Now we need to collect daytime statistics

-- Daytime statistics, gathered from 10am to 4pm
SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 360);

PL/SQL procedure successfully completed.

Now, if all goes well, export statistics into a permanent table

SQL> exec dbms_stats.export_system_stats('PERMSYSSTAT','DAYSTATS','SYS');

PL/SQL procedure successfully completed.

Repeat same steps for nighttime statistics

-- Night time statistics, gathered from 9pm to 4am
SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 420);

PL/SQL procedure successfully completed.

Export this stats to permanent table

SQL> exec dbms_stats.export_system_stats('PERMSYSSTAT','NIGHTSTATS','SYS');

PL/SQL procedure successfully completed.

Now we have two sets of statistics, the day we will activate the daytime and at night the nighttime statistics

-- Activate from 8am
SQL> exec dbms_stats.import_system_stats('PERMSYSSTAT','DAYSTATS','SYS');

PL/SQL procedure successfully completed.

-- Activate from 8pm
SQL> exec dbms_stats.import_system_stats('PERMSYSSTAT','NIGHTSTATS','SYS');

PL/SQL procedure successfully completed.

Of course it would be better if you automate this process, either through a package DBMS_SCHEDULER or other means of operating system, for example crontab on UNIX-based systems.

In some cases, you can change any of the parameters of the system statistics manually. For example, after gathering nighttime statistics you found that some queries do not work on the optimal plan, such as using index access to large tables and nested loops to their union. However, full scan and hash joins would be more optimal. After reviewing the gathered system statistics you see that the parameter mbrc lower than you expected, let’s say 23. In order not to rebuild the statistics again, you can manually change this setting using the procedure set_system_stats, for example

SQL> exec sys.dbms_stats.set_system_stats('mbrc', 64);

Alternatively you can increase the value of the optimizer_index_cost_adj initialization parameter, thereby raise the price to access the index, but by the early day cycle value should be returned.

If you have any questions, leave them in the comments.

(c) Aychin Gasimov, 12/2010, Azerbaijan Republic

Собирать или не собирать?

Речь конечно же идет не о картошке, а о системной статистике СУРБД Oracle. С картошкой все ясно созрела значит пора собирать, а что насчет системной статистики?

Что такое системная статистика (system statistics) и для чего она нужна? Системная статистика это набор системных показателей определяющих различные параметры системы, такие как скорость процессора, максимальное количество одновременно считываемых блоков и т. д. А также такие показатели как время считывания одного блока с диска (single block read time) и одновременного считывания множества блоков (multiblock read time).


7 responses to “Gather or Not? Eng.

  1. Chinar March 26, 2011 at 20:00

    Hi Aychin.
    Yes it is very important and interesting topic in query optimizer.And need some clarification also.
    So you said “mbrc (multi block read count) this parameter determine the average number of blocks which the system reads in one I/O operation at a full table or index scans”
    I think your “index scans”`s mean there is INDEX FAST FULL SCAN,am i right?(because other index scans do not use this parameter).In additionally ff you use NOWORKLOAD statistics then optimizer will calculate mreadtim and sreadtim as below

    sreadtim = ioseektim + db_block_size/iotfrspeed
    mreadtim = ioseektim + db_block_size*db_file_multiblock_read_count/iotfrspeed

    and MBRC will set value of db_file_multiblock_read_count parameter.When we use system statistics then only FTS cost will change(will effect) and these parameter will not effect calculating cost of index scan(IFS ,we can enable and disable cost calculation method using _optimizer_cost_model parameter then we will there will not effect for index scans(can be small extra cost)).Before oracle 9i there is not available system statistics (or CPU costing) and optimizer can not be compare(automatically) cost of multiblock read and singleblock reads.Therefore there was need using optimizer_index_cost_adj parameter.However this parameter still available in 11g but do not need change it(because actually there are system statistics).

    • aychin March 27, 2011 at 12:53

      I glad that it was interesting for You!
      About parameter optimizer_index_cost_adj, it was introduced with CBO in Oracle 8i version, the only cost model for CBO in this version was optimization for best throughput, from 9i there is optimization by best response time also available. But there is no 100% guaranty that optimizer will generate best plan at any point of time in both optimizer cost models, especially in huge databases like data ware house systems, there is many reasons for that. And this optimizer_index_cost_adj parameter is useful and can be adjusted in some circumstances as temporary as well as permanent solution, irrespective in which cost model optimizer works.

      • Chinar March 27, 2011 at 15:07

        Yes this parameter was introduce in oracle 8i version.
        But why optimizer_index_cost_adj parameter introduced in oracle 8i? because in this version CBO had problem related calculation cost(in this release CBO consider only physical reads and it think against block never was request) and to solving problem there introduced some couple of parameter also ptimizer_index_cost_adj.But beginning 9i there are available system statistics and also oracle collect cache statistics(logical block requests and physical reads) at object level.So this problem actually solved with prefect method.
        Implementing above parameter still available but its generally do not need(rarely situation need).

  2. aychin March 27, 2011 at 18:30

    I never said that you MUST use this parameter. As much as possible you must avoid the use of optimizer_index_cost_adj or others that can affect optimizer behavior, also it is not recommended using the optimizer hints to force plan changes. And it must be our goal to perform our system in such a way that it can function well without any intervention.

    Every optimizer in every version of Oracle, or any other database system, have some problems. Sure in every new version of product optimizer improves, but they never can provide 100% correct plans for every situation. Optimizers calculate just estimated costs, as much information optimizer have about object or environment as near to 100% will be the estimated cost. And if some object will have statistics that are gathered with estimate percent equal to 100, then optimizer can very correct estimate the number of IO operations in which it can retrieve requested data, but it can’t with 100% estimate the response time, yes it will be very near to 100% but not 100%.

    In the OLTP systems with mostly relatively small tables it is possible to gather statistics with estimate percent equal to 100 and gather the system statistics and theoretically be sure that every generated plan will be the best.
    But in the data ware house systems, where the tables is terabytes or hundreds of megabytes in size, and with very huge select statements with many joins, sorts and filters it is very difficult to be sure that execution plan will be best of possible. First of all it is very difficult to maintain statistics with 100% estimation percent in such systems, and it means that we can’t be 100% sure that plans will be best and they will be stable. As we know in data ware house systems the full table scans is more preferable in 90% of cases, I suggest to set optimizer_index_cost_adj parameter to more than 100, just to be sure that in cases when cost of index scan will be not significantly lower than full table scan, optimizer will choose full table scan. And be sure that this technique is tested and proofed its usefulness in 10g and 11g versions.
    Some experts also recommend to set optimizer_index_cost_adj to lower values in the OLTP systems, I can’t 100% agree with this, but I didn’t tested it, there was no need.

    Also I want to note that there was situation in my practice when Oracle Support was recommended to adjust optimizer_index_cost_adj parameter to achieve some plan costing and performance problems on 10g system.

    But as I always say, before implementing something it must be tested and many times, because every system is different with different users.

    • Chinar March 27, 2011 at 19:00

      Yes i agree with you about implementing optimizer_index_cost_adj parameter and i also want say that actually.This is important point when we talking about system statistics.
      I hope understood each other. Thanks anyway for this good point 🙂 .

      • aychin March 27, 2011 at 19:17

        Thank You for Your interest and for extending the scope of this article. 🙂 But I think this point needs separate topic and it can be not one page long 🙂

      • Chinar March 27, 2011 at 19:30

        🙂 Of course i agree with you and when you write about CBO then i will also comment your posts 😉

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: