讲述CBO And Database Statistics

When a valid SQL statment is sent to the server for the first time,Oracle produces an execution plan that describles how to retrieve the necessary data. the execution plan could be generated using one of two optimizers.

• Rule-Based Optimizer(RBO)
• Cost-Based Optimizer(CBO)

DBMS_STATS

The DBMS_STATS package 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.

Table statistics can be gathered for the database:

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 28 15:51:41 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> EXEC DBMS_STATS.gather_database_stats;
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 

Table statistics can be gathered for the schema:

SQL> 
SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT',estimate_percent=> 15);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT',estimate_percent=> 15,cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 

Table statistics can be gathered for the table:

SQL> 
SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP', estimate_percent => 15);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP', estimate_percent => 15, cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 

Table statistics can be gathered for the partition:

SQL> 
SQL> EXEC DBMS_STATS.gather_dictionary_stats;
 
PL/SQL procedure successfully completed.
 
SQL> 

Index statistics can be gathered explicitly using the GATHER_INDEX_STATS procedure.

 
 
SQL> EXEC DBMS_STATS.gather_index_stats('SCOTT', 'PK_EMP');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_index_stats('SCOTT', 'PK_EMP', estimate_percent => 15);
 
PL/SQL procedure successfully completed.
 
SQL> 

The current statistics information is available from the data dictionary views for the specific objects (DBA, ALL and USER views).

• 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_INDEXS
• 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.

SQL> 
SQL> EXEC DBMS_STATS.delete_database_stats;
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.delete_schema_stats('SCOTT');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.delete_index_stats('SCOTT', 'PK_EMP');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.delete_dictionary_stats;
 
PL/SQL procedure successfully completed.
 
SQL>

System Stats

The GATHER_SYSTEM_STATS 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.

  SQL> show user
  USER is "SYS"
  SQL> EXEC DBMS_STATS.gather_system_stats;
   
  PL/SQL procedure successfully completed.
   
  SQL> 
  

• 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.
   

  -- Manually start and stop to sample a representative time (several hours) of system activity.
   
  SQL> 
  SQL> EXEC DBMS_STATS.gather_system_stats('start');
   
  PL/SQL procedure successfully completed.
   
  SQL> EXEC DBMS_STATS.gather_system_stats('stop');
   
  PL/SQL procedure successfully completed.
   
  SQL> show user;
  USER is "SYS"
  SQL> 
   
   
   
  -- Sample from now until a specific number of minutes.
  DBMS_STATS.gather_system_stats('interval', interval => 180); 
  

  current system statistics can be displayed by querying the AUX_STATS$ table.

SQL> 
SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
 
PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                           2121
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                               .2
MREADTIM                             .224
CPUSPEED                             2122
MBRC                                   10
MAXTHR
SLAVETHR
 
9 rows selected.
 
SQL> 

 The DELETE_SYSTEM_STATS procedure will delete all workload stats and replace previously gathered noworkload stats with the default values.

SQL> 
SQL> EXEC DBMS_STATS.delete_system_stats;
 
PL/SQL procedure successfully completed.
 
SQL> 

You only need to update your system statistics when something major has happened to your systems hardware or workload profile.

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

Fixed Object Stats

the GATHER_FIXED_OBJECTS_STATS procedure gathers statistics on the X$ tables, which sit underneath the V$ dynamic performance views. The X$ 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.

SQL> EXEC DBMS_STATS.gather_fixed_objects_stats;
 
PL/SQL procedure successfully completed.
 
SQL> 

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 DELETE_FIXED_OBJECTS_STATS procedure.

SQL> 
SQL> EXEC DBMS_STATS.delete_fixed_objects_stats;
 
PL/SQL procedure successfully completed.
 
SQL> 

Locking Stats

To prevent statistics being overwritten, you can lock&unlock the stats at schema, table or partition level.

SQL> 
SQL> EXEC DBMS_STATS.lock_schema_stats('SCOTT');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> 
SQL> EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
 
PL/SQL procedure successfully completed.
 
SQL> 

EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

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.

EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'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.

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

Setting Preferences

EXEC DBMS_STATS.set_param('DEGREE', '5');

the SET_PARAM procedure was deprecated in favor of a layered approach to preferences. The four levels of preferences are amended with the following procedures.

• SET_GLOBAL_PREFS: Used to set global preferences, including some specific to the automatic stats collection job.
• SET_DATABASE_PREFS: Sets preferences for the whole database.
• SET_SCHEMA_PREFS: Sets preferences for a specific schema.
• SET_TABLE_PREFS: Sets preferences for a specific table.