In the world of Oracle Database Management, gathering statistics plays a crucial role in optimizing query performance. Accurate statistics help the query optimizer make informed decisions about execution plans.
In this introduction let's explore how to gather statistics for Schemas , Tables , and Indexes using Oracle simple commands.
Table of Contents
Gather Schema Stats with GATHER_SCHEMA_STATS
To collect statistics for an entire schema, use the following command.
-- GATHER STATS FROM SCHEMA USING DEFAULT OPTIONS
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');
Considerations
- For partitioned tables, use
CASCADE=>TRUEto gather statistics for all partitions
Full Syntax of GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
Gather Table Stats with GATHER_TABLE_STATS
To gather statistics of individual tables use the following command.
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA_NAME',
tabname => 'TABLE_NAME'
);
Considerations
- Small tables: Use a larger sample size
- Large tables: Consider parallelism and incremental statistics
Full Syntax of GATHER_TABLE_STATS
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT get_param('OPTIONS'));
Gather Index Stats with GATHER_INDEX_STATS
To gather Indexes statistics use the following command.
EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCHEMA_NAME',
indname => 'INDEX_NAME'
);
Considerations
- Unique indexes: Gather statistics for uniqueness checks
- Bitmap indexes: Handle them differently due to their nature
Full Syntax of GATHER_INDEX_STATS
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
Syntax of GATHER_INDEX_STATS
Conclusion
Gathering statistics is not just a routine task—it’s a critical part of maintaining a healthy database. By following best practices and understanding the nuances, you’ll ensure optimal query performance.
For further details check the official documentation.
Remember to apply these techniques in your database environment, and happy querying! 🚀
Top comments (0)