When a valid SQL statement is sent to the server for the first time, Oracle produces an execution plan that describes how to retrieve the necessary data. In older versions of the database this execution plan could be generated using one of two optimizers:
- Rule-Based Optimizer (RBO) - This was the original optimization method and as the name suggests, was essentially a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced, this method was used if the server had no internal statistics relating to the objects referenced by the statement, or if explicitly requested by a hint or instance/session parameter. This optimizer was made obsolete, then deprecated in later versions of the database.
- Cost-Based Optimizer (CBO) - The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation
what should our statistics strategy be? Here are some suggestions.
- Automatic Optimizer Statistics Collection: From 10g onward the database automatically gathers statistics on a daily basis. The default statistics job has come under a lot of criticism over the years, but its value depends on the type of systems you are managing. Most of that criticism has come from people discussing edge cases, like large data warehouses. If you are managing lots of small databases that have relatively modest performance requirements, you can pretty much let Oracle do its own thing where stats are concerned. If you have any specific problems, deal with them on a case by case basis.
- Mixed Approach: You rely on the automatic job for the majority of stats collection, but you have specific tables or schemas that have very specific stats requirements. In these cases you can either set the preferences for the objects in question, or lock the stats for the specific tables/schemas to prevent the job from changing them, then devise a custom solution for those tables/schemas.
- Manual: You disable the automatic stats collection completely and devise a custom solution for the whole of the database.
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 DBMS_STATS 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.
DBMS_STATS
The DBMS_STATS 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.
Table and Index Stats
Table statistics can be gathered for the database, schema, table or partition.
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 16 08:52:54 2023
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> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> ALTER SESSION SET CONTAINER=PDB1;
Session altered.
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> EXEC DBMS_STATS.gather_database_stats;
PL/SQL procedure successfully completed.
SQL>
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>
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>
SQL> EXEC DBMS_STATS.gather_table_stats('HR', 'EMPLOYEES');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats('HR', 'EMPLOYEES', estimate_percent => 15);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats('HR', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> EXEC DBMS_STATS.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL>
The ESTIMATE_PERCENT 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 AUTO_SAMPLE_SIZE constant, but this got a bad reputation because the selected sample size was sometimes inappropriate, making the resulting statistics questionable.
In Oracle 11g, the AUTO_SAMPLE_SIZE 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
The CASCADE 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 AUTO_CASCADE, 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 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> show user;
USER is "SYS"
SQL> 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.
SQL> show user;
USER is "SYS"
SQL> desc DBA_TABLES
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(128)
IOT_NAME VARCHAR2(128)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(128)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(30)
DROPPED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
RESULT_CACHE VARCHAR2(7)
CLUSTERING VARCHAR2(3)
ACTIVITY_TRACKING VARCHAR2(23)
DML_TIMESTAMP VARCHAR2(25)
HAS_IDENTITY VARCHAR2(3)
CONTAINER_DATA VARCHAR2(3)
INMEMORY VARCHAR2(8)
INMEMORY_PRIORITY VARCHAR2(8)
INMEMORY_DISTRIBUTE VARCHAR2(15)
INMEMORY_COMPRESSION VARCHAR2(17)
INMEMORY_DUPLICATE VARCHAR2(13)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
EXTERNAL VARCHAR2(3)
HYBRID VARCHAR2(3)
CELLMEMORY VARCHAR2(24)
CONTAINERS_DEFAULT VARCHAR2(3)
CONTAINER_MAP VARCHAR2(3)
EXTENDED_DATA_LINK VARCHAR2(3)
EXTENDED_DATA_LINK_MAP VARCHAR2(3)
INMEMORY_SERVICE VARCHAR2(12)
INMEMORY_SERVICE_NAME VARCHAR2(1000)
CONTAINER_MAP_OBJECT VARCHAR2(3)
MEMOPTIMIZE_READ VARCHAR2(8)
MEMOPTIMIZE_WRITE VARCHAR2(8)
HAS_SENSITIVE_COLUMN VARCHAR2(3)
ADMIT_NULL VARCHAR2(3)
DATA_LINK_DML_ENABLED VARCHAR2(3)
LOGICAL_REPLICATION VARCHAR2(8)
SQL> DESC DBA_TAB_STATISTICS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
PARTITION_POSITION NUMBER
SUBPARTITION_NAME VARCHAR2(128)
SUBPARTITION_POSITION NUMBER
OBJECT_TYPE VARCHAR2(12)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
AVG_CACHED_BLOCKS NUMBER
AVG_CACHE_HIT_RATIO NUMBER
IM_IMCU_COUNT NUMBER
IM_BLOCK_COUNT NUMBER
IM_STAT_UPDATE_TIME TIMESTAMP(9)
SCAN_RATE NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
STATTYPE_LOCKED VARCHAR2(5)
STALE_STATS VARCHAR2(7)
NOTES VARCHAR2(25)
SCOPE VARCHAR2(7)
SQL> DESC DBA_TAB_PARTITIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(128)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(30)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
IS_NESTED VARCHAR2(3)
PARENT_TABLE_PARTITION VARCHAR2(128)
INTERVAL VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(4)
INDEXING VARCHAR2(4)
READ_ONLY VARCHAR2(4)
INMEMORY VARCHAR2(8)
INMEMORY_PRIORITY VARCHAR2(8)
INMEMORY_DISTRIBUTE VARCHAR2(15)
INMEMORY_COMPRESSION VARCHAR2(17)
INMEMORY_DUPLICATE VARCHAR2(13)
CELLMEMORY VARCHAR2(24)
INMEMORY_SERVICE VARCHAR2(12)
INMEMORY_SERVICE_NAME VARCHAR2(1000)
MEMOPTIMIZE_READ VARCHAR2(8)
MEMOPTIMIZE_WRITE VARCHAR2(8)
SQL>
SQL> DESC DBA_TAB_COLUMNS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)
SQL> DESC DBA_TAB_COL_STATISTICS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(128)
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
NOTES VARCHAR2(99)
AVG_COL_LEN NUMBER
HISTOGRAM VARCHAR2(15)
SCOPE VARCHAR2(7)
SQL> DESC DBA_PART_COL_STATISTICS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
NOTES VARCHAR2(81)
AVG_COL_LEN NUMBER
HISTOGRAM VARCHAR2(15)
SQL> DESC DBA_SUBPART_COL_STATISTICS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
SUBPARTITION_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
NOTES VARCHAR2(63)
AVG_COL_LEN NUMBER
HISTOGRAM VARCHAR2(15)
SQL> DESC DBA_INDEXES
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
INDEX_NAME NOT NULL VARCHAR2(128)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(13)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(128)
ITYP_NAME VARCHAR2(128)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
VISIBILITY VARCHAR2(9)
DOMIDX_MANAGEMENT VARCHAR2(14)
SEGMENT_CREATED VARCHAR2(3)
ORPHANED_ENTRIES VARCHAR2(3)
INDEXING VARCHAR2(7)
AUTO VARCHAR2(3)
CONSTRAINT_INDEX VARCHAR2(3)
SQL> DESC DBA_IND_STATISTICS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
TABLE_OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
PARTITION_POSITION NUMBER
SUBPARTITION_NAME VARCHAR2(128)
SUBPARTITION_POSITION NUMBER
OBJECT_TYPE VARCHAR2(12)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
NUM_ROWS NUMBER
AVG_CACHED_BLOCKS NUMBER
AVG_CACHE_HIT_RATIO NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
STATTYPE_LOCKED VARCHAR2(5)
STALE_STATS VARCHAR2(3)
SCOPE VARCHAR2(7)
SQL> DESC DBA_IND_PARTITIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(128)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER
STATUS VARCHAR2(8)
TABLESPACE_NAME VARCHAR2(30)
PCT_FREE NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENT NUMBER
MAX_EXTENT NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(7)
COMPRESSION VARCHAR2(13)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
PCT_DIRECT_ACCESS NUMBER
GLOBAL_STATS VARCHAR2(3)
DOMIDX_OPSTATUS VARCHAR2(6)
PARAMETERS VARCHAR2(1000)
INTERVAL VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
ORPHANED_ENTRIES VARCHAR2(3)
SQL>Histogram information is available from the following views.
SQL>
SQL> DESC DBA_TAB_HISTOGRAMS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
ENDPOINT_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(4000)
ENDPOINT_ACTUAL_VALUE_RAW RAW(2000)
ENDPOINT_REPEAT_COUNT NUMBER
SCOPE VARCHAR2(7)
SQL> DESC DBA_PART_HISTOGRAMS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
BUCKET_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(4000)
ENDPOINT_ACTUAL_VALUE_RAW RAW(2000)
ENDPOINT_REPEAT_COUNT NUMBER
SQL> DESC DBA_SUBPART_HISTOGRAMS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
SUBPARTITION_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
BUCKET_NUMBER NUMBER
ENDPOINT_VALUE NUMBER
ENDPOINT_ACTUAL_VALUE VARCHAR2(4000)
ENDPOINT_ACTUAL_VALUE_RAW RAW(2000)
ENDPOINT_REPEAT_COUNT NUMBER
SQL>Table, column and index statistics can be deleted using the relevant delete procedures.
SQL>
SQL> show user;
USER is "SYS"
SQL> EXEC DBMS_STATS.delete_database_stats;
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');
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
Introduced in Oracle 9iR1, 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:
- No workload: 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>
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.
SQL>
SQL> -- Manually start and stop to sample a representative time (several hours) of system activity.
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>
SQL>
SQL> -- Sample from now until a specific number of minutes.
SQL>
SQL> EXEC DBMS_STATS.gather_system_stats('interval', interval => 180);
PL/SQL procedure successfully completed.
SQL> Your current system statistics can be displayed by querying the AUX_STATS$ table.
SQL> show user;
USER is "SYS"
SQL>
SQL> SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';
PNAME PVAL1
------------------------------------------------------------------------------------------ ----------
CPUSPEEDNW 3732
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM .008
MREADTIM .02
CPUSPEED 3733
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> show user;
USER is "SYS"
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.
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 SET_SYSTEM_STATS procedure.
EXEC DBMS_STATS.set_system_stats('iotfrspeed', 4096);Fixed Object Stats
Introduced in Oracle 10gR1, 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>
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>
SQL> EXEC DBMS_STATS.delete_fixed_objects_stats;
PL/SQL procedure successfully completed.
SQL>
Locking Stats
To prevent statistics being overwritten, you can lock 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.lock_table_stats('SCOTT', 'EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
PL/SQL procedure successfully completed.If you need to replace the stats, they must be unlocked.
SQL>
SQL> EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
PL/SQL procedure successfully completed.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.
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
Since Oracle 10g, many of the default values of parameters for the DBMS_STATS procedures have changed from being hard coded to using preferences. In Oracle 10g, these preferences could be altered using the SET_PARAM procedure.
EXEC DBMS_STATS.set_param('DEGREE', '5');In 11g, 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.
The available preferences are listed below, along with the available scope (G=Global, D=Database, S=Schema, T=Table).
| Preference | Description | Default (11gR2) | Scope | Version |
|---|---|---|---|---|
| CASCADE | Determines if index stats should be gathered for the current table (TRUE, FALSE, AUTO_CASCADE). | DBMS_STATS.AUTO_CASCADE | G, D, S, T | 10gR1+ |
| DEGREE | Degree of parallelism (integer or DEFAULT_DEGREE). | DBMS_STATS.DEFAULT_DEGREE | G, D, S, T | 10gR1+ |
| ESTIMATE_PERCENT | Percentage of rows to sample when gathering stats (0.000001-100 or AUTO_SAMPLE_SIZE). | DBMS_STATS.AUTO_SAMPLE_SIZE | G, D, S, T | 10gR1+ |
| METHOD_OPT | Controls column statistics collection and histogram creation. | FOR ALL COLUMNS SIZE AUTO | G, D, S, T | 10gR1+ |
| NO_INVALIDATE | Determines if dependent cursors should be invalidated as a result of new stats on objects (TRUE, FALSE or AUTO_INVALIDATE). | DBMS_STATS.AUTO_INVALIDATE | G, D, S, T | 10gR1+ |
| AUTOSTATS_TARGET | Determines which objects have stats gathered (ALL, ORACLE, AUTO). | AUTO | G | 10gR2+ |
| GRANULARITY | The granularity of stats to be collected on partitioned objects (ALL, AUTO, DEFAULT, GLOBAL, 'GLOBAL AND PARTITION', PARTITION, SUBPARTITION). | AUTO | G, D, S, T | 10gR2+ |
| PUBLISH | Determines if gathered stats should be published immediately or left in a pending state (TRUE, FALSE). | TRUE | G, D, S, T | 11gR2+ |
| INCREMENTAL | Determines whether incremental stats will be used for global statistics on partitioned objects, rather than generated using table scans (TRUE, FALSE). | FALSE | G, D, S, T | 11gR2+ |
| CONCURRENT | Should objects statistics be gathered on multiple objects at once, or one at a time (MANUAL, AUTOMATIC, ALL, OFF). | OFF | G | 12cR1+ |
| GLOBAL_TEMP_TABLE_STATS | Should stats on global temporary tables be session-specific or shared between sessions (SHARED, SESSION). | SESSION | G, D, S | 12cR1+ |
| INCREMENTAL_LEVEL | Which level of synopses should be collected for incremental partitioned statistics (TABLE, PARTITION). | PARTITION | G, D, S, T | 12cR1+ |
| INCREMENTAL_STALENESS | How is staleness of partition statistics determined (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL). | NULL | G, D, S, T | 12cR1+ |
| TABLE_CACHED_BLOCKS | The number of blocks cached in the buffer cache during calculation of index cluster factor. Jonathan Lewis recommends "16" as a sensible value. | 1 | G, D, S, T | 12cR1+ |
| OPTIONS | Used for the OPTIONS parameter of the GATHER_TABLE_STATS procedure (GATHER, GATHER AUTO). | GATHER | G, D, S, T | 12cR1+ |
The following shows their basic usage.
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');Global preferences can be reset and the other layers of preferences deleted using the following procedures.
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');Setting Stats Manually
The DBMS_STATS package provides several procedures for manually setting statistics.
SET_SYSTEM_STATSSET_TABLE_STATSSET_COLUMN_STATSSET_INDEX_STATS
The current stats can be returned using the following procedures.
GET_SYSTEM_STATSGET_TABLE_STATSGET_COLUMN_STATSGET_INDEX_STATS
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.
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;
/Issues
- Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
- Prior to 10g, gathering stats for the SYS schema can make the system run slower, not faster.
- Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
- Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.
Legacy Methods for Gathering Database Stats
The information in this section is purely for historical reasons. All statistics management should now be done using the DBMS_STATS package.
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.
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;DBMS_UTILITY
The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement.
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);Refreshing Stale Stats
This involves monitoring the DML operations against individual tables so statistics are only gathered for those tables whose data has changed significantly. This is the default action for the automatic optimizer statistics collection in 10g and above, but if you are using an older version of the database, you may want to read more about this here.
Scheduling Stats
Prior to Oracle 10g, scheduling the gathering of statistics using the DBMS_JOB package ws the easiest way to make sure they were always up to date.
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;
/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 DBA_JOBS and DBA_JOBS_RUNNING views.
Existing jobs can be removed using the following.
EXEC DBMS_JOB.remove(X);
COMMIT;
4308
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
