DBA Hub

📋Steps in this guide1/12

Cost-Based Optimizer (CBO) And Database Statistics

Lists the available mechanisms for gathering database statistics that are used by the cost-based optimizer.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Introduction

If you put 10 Oracle performance gurus in the same room they will all say database statistics are vital for the cost-based optimizer to choose the correct execution plan for a query, but they will all have a different opinion on how to gather those statistics. A couple of quotes that stand out in my mind are: - "You don't necessarily need up to date statistics. You need statistics that are representative of your data." - Graham Wood. Meaning, the age of the statistics in your system is not a problem as long as they are still representative of your data. So just looking at the column of the view is not an indication of valid stats on your system. "You don't necessarily need up to date statistics. You need statistics that are representative of your data." - Graham Wood. Meaning, the age of the statistics in your system is not a problem as long as they are still representative of your data. So just looking at the column of the view is not an indication of valid stats on your system. - "Do you want the optimizer to give you the best performance, or consistent performance?" - Anjo Kolk Meaning, regularly changing your stats potentially introduces change. Change is not always a good thing. "Do you want the optimizer to give you the best performance, or consistent performance?" - Anjo Kolk Meaning, regularly changing your stats potentially introduces change. Change is not always a good thing. Neither of these experts are suggesting you never update your stats, just pointing out that in doing so you are altering information the optimizer uses to determine which execution plan is the most efficient. In altering that information it is not unlikely the optimizer may make a different decision. Hopefully it will be the correct decision, but maybe it wont. If you gather statistics for all tables every night, your system will potentially act differently every day. This is the fundamental paradox of gathering statistics. So what should our statistics strategy be? Here are some suggestions. Which one of these approaches you take should be decided on a case-by-case basis. Whichever route you take, you will be using the package to manage your stats. Regardless of the approach you take, you need to consider system and fixed object statistics for every database, as these are not gathered by the automatic job.
2

DBMS_STATS

The package was introduced in Oracle 8i and is Oracle's preferred method of gathering statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. The functionality of the DBMS_STATS package varies greatly between database versions, as do the default parameter settings and the quality of the statistics they generate. It is worth spending some time checking the documentation relevant to your version.
3

Table and Index Stats

Table statistics can be gathered for the database, schema, table or partition. The parameter was often used when gathering stats from large segments to reduce the sample size and therefore the overhead of the operation. In Oracle 9i upwards, we also had the option of letting Oracle determine the sample size using the constant, but this got a bad reputation because the selected sample size was sometimes inappropriate, making the resulting statistics questionable. In Oracle 11g, the constant is the preferred (and default) sample size as the mechanism for determining the actual sample size has been improved. In addition, the statistics estimate based on the auto sampling are near to 100% accurate and much faster to gather than in previous versions, as described here . The parameter determines if statistics should be gathered for all indexes on the table currently being analyzed. Prior to Oracle 10g, the default was FALSE, but in 10g upwards it defaults to , which means Oracle determines if index stats are necessary. As a result of these modifications to the behavior in the stats gathering, in Oracle 11g upwards, the basic defaults for gathering table stats are satisfactory for most tables. Index statistics can be gathered explicitly using the procedure. The current statistics information is available from the data dictionary views for the specific objects (DBA, ALL and USER views). Some of these view were added in later releases. - DBA_TABLES - DBA_TAB_STATISTICS - DBA_TAB_PARTITIONS - DBA_TAB_SUB_PARTITIONS - DBA_TAB_COLUMNS - DBA_TAB_COL_STATISTICS - DBA_PART_COL_STATISTICS - DBA_SUBPART_COL_STATISTICS - DBA_INDEXES - DBA_IND_STATISTICS - DBA_IND_PARTITIONS - DBA_IND_SUBPARTIONS Histogram information is available from the following views. - DBA_TAB_HISTOGRAMS - DBA_PART_HISTOGRAMS - DBA_SUBPART_HISTOGRAMS Table, column and index statistics can be deleted using the relevant delete procedures.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMP_PK');

EXEC DBMS_STATS.delete_dictionary_stats;
4

System Stats

Introduced in Oracle 9iR1, the procedure gathers statistics relating to the performance of your systems I/O and CPU. Giving the optimizer this information makes its choice of execution plan more accurate, since it is able to weigh the relative costs of operations using both the CPU and I/O profiles of the system. There are two possible types of system statistics: - Noworkload: All databases come bundled with a default set of noworkload statistics, but they can be replaced with more accurate information. When gathering noworkload stats, the database issues a series of random I/Os and tests the speed of the CPU. As you can imagine, this puts a load on your system during the gathering phase. Noworkload: All databases come bundled with a default set of noworkload statistics, but they can be replaced with more accurate information. When gathering noworkload stats, the database issues a series of random I/Os and tests the speed of the CPU. As you can imagine, this puts a load on your system during the gathering phase. - Workload: When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system. If workload statistics are present, they will be used in preference to noworkload statistics. Workload: When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system. If workload statistics are present, they will be used in preference to noworkload statistics. Your current system statistics can be displayed by querying the table. If you are running 11.2.0.1 or 11.2.0.2 then check out MOS Note: 9842771.8 . The procedure will delete all workload stats and replace previously gathered noworkload stats with the default values. You only need to update your system statistics when something major has happened to your systems hardware or workload profile. There are two schools of thought about system stats. One side avoid the use of system statistics altogether, favoring the default noworkload stats. The other side suggests providing accurate system statistics. The problem with the latter, is it is very difficult to decide what represents an accurate set of system statistics. Most people seem to favor investigation of systems using a variety of methods, including gathering system stats into a stats table, then manually setting the system statistics using the procedure. The available parameter names can be found here . I would say, if in doubt, use the defaults.

Code/Command (click line numbers to comment):

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
EXEC DBMS_STATS.gather_system_stats;

-- Manually start and stop to sample a representative time (several hours) of system activity.
EXEC DBMS_STATS.gather_system_stats('start');
EXEC DBMS_STATS.gather_system_stats('stop');

-- Sample from now until a specific number of minutes.
DBMS_STATS.gather_system_stats('interval', interval => 180);

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                           1074
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

SQL>

EXEC DBMS_STATS.delete_system_stats;

EXEC DBMS_STATS.set_system_stats('iotfrspeed', 4096);
5

Fixed Object Stats

Introduced in Oracle 10gR1, the procedure gathers statistics on the tables, which sit underneath the dynamic performance views. The tables are not really tables at all, but a window on to the memory structures in the Oracle kernel. Fixed object stats are not gathered automatically, so you need to gather them manually at a time when the database is in a representative level of activity. Major changes to initialization parameters or system activity should signal you to gather fresh stats, but under normal running this does not need to be done on a regular basis. The stats are removed using the procedure.

Code/Command (click line numbers to comment):

1
2
3
EXEC DBMS_STATS.gather_fixed_objects_stats;

EXEC DBMS_STATS.delete_fixed_objects_stats;
6

Locking Stats

To prevent statistics being overwritten, you can lock the stats at schema, table or partition level. If you need to replace the stats, they must be unlocked. Locking stats can be very useful to prevent automated jobs from changing them. This is especially useful with tables used for ETL processes. If the stats are gathered when the tables are empty, they will not reflect the real quantity of data during the load process. Instead, either gather stats each time the data is loaded, or gather them once on a full table and lock them.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
7

Transfering Stats

It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA. This table can then be transfered to another server using your preferred method (Export/Import, SQL*Plus COPY etc.) and the stats imported into the data dictionary as follows.

Code/Command (click line numbers to comment):

1
2
3
4
5
EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');
8

Setting Preferences

Since Oracle 10g, many of the default values of parameters for the procedures have changed from being hard coded to using preferences. In Oracle 10g, these preferences could be altered using the procedure. - : Used to set global preferences, including some specific to the automatic stats collection job. - : Sets preferences for the whole database. - : Sets preferences for a specific schema. - : Sets preferences for a specific table. The available preferences are listed below, along with the available scope (G=Global, D=Database, S=Schema, T=Table). The following shows their basic usage. Global preferences can be reset and the other layers of preferences deleted using the following procedures.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
EXEC DBMS_STATS.set_param('DEGREE', '5');

EXEC DBMS_STATS.set_global_prefs('AUTOSTATS_TARGET', 'AUTO');
EXEC DBMS_STATS.set_database_prefs('STALE_PERCENT', '15');
EXEC DBMS_STATS.set_schema_prefs('SCOTT','DEGREE', '5');
EXEC DBMS_STATS.set_table_prefs('SCOTT', 'EMP', 'CASCADE', 'FALSE');

EXEC DBMS_STATS.reset_global_pref_defaults;
EXEC DBMS_STATS.delete_database_prefs('CASCADE');
EXEC DBMS_STATS.delete_schema_prefs('SCOTT','DEGREE');
EXEC DBMS_STATS.delete_table_prefs('SCOTT', 'EMP', 'CASCADE');
9

Setting Stats Manually

The package provides several procedures for manually setting statistics. - The current stats can be returned using the following procedures. - Be careful when setting stats manually. Possibly the safest approach is to get the current values, amend them as required, then set them. An example of setting column statistics is shown below.

Code/Command (click line numbers to comment):

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
SET SERVEROUTPUT ON
DECLARE
  l_distcnt  NUMBER; 
  l_density  NUMBER;
  l_nullcnt  NUMBER; 
  l_srec     DBMS_STATS.StatRec;
  l_avgclen  NUMBER;
BEGIN
  -- Get current values.
  DBMS_STATS.get_column_stats (
    ownname => 'SCOTT', 
    tabname => 'EMP', 
    colname => 'EMPNO', 
    distcnt => l_distcnt, 
    density => l_density,
    nullcnt => l_nullcnt, 
    srec    => l_srec,
    avgclen => l_avgclen);

  -- Amend values.
  l_srec.minval := UTL_RAW.cast_from_number(7369);
  l_srec.maxval := UTL_RAW.cast_from_number(7934);

  -- Set new values.
  DBMS_STATS.set_column_stats (
    ownname => 'SCOTT', 
    tabname => 'EMP', 
    colname => 'EMPNO', 
    distcnt => l_distcnt, 
    density => l_density,
    nullcnt => l_nullcnt, 
    srec    => l_srec,
    avgclen => l_avgclen);
END;
/
10

Analyze Statement

The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows.

Code/Command (click line numbers to comment):

1
2
3
4
5
ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
11

DBMS_UTILITY

The package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);
12

Scheduling Stats

Prior to Oracle 10g, scheduling the gathering of statistics using the package ws the easiest way to make sure they were always up to date. The above code sets up a job to gather statistics for SCOTT for the current time every day. You can list the current jobs on the server using the and views. Existing jobs can be removed using the following. Where 'X' is the number of the job to be removed. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET SERVEROUTPUT ON
DECLARE
  l_job  NUMBER;
BEGIN
  DBMS_JOB.submit(l_job,
                  'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
                  SYSDATE,
                  'SYSDATE + 1');
  COMMIT;
  DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

EXEC DBMS_JOB.remove(X);
COMMIT;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!