20/06/2012
Managing Database Optimizer Statistics
Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best ex*****on plan for each SQL statement. Optimizer statistics include the following:
Table statistics
Number of rows
Number of blocks
Average row length
Table Column statistics
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
Extended statistics
Index statistics
Number of leaf blocks
Levels
Clustering factor
System/OS statistics
I/O performance and utilization
CPU performance and utilization
The optimizer statistics are stored in the data dictionary tables dba_tables, dba_indexes etc
Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.
The DBMS_STATS package also provides procedures for managing statistics. You can save and restore copies of statistics. You can export statistics from one system and import those statistics into another system. For example, you could export statistics from a production system to a test system.
Also refer to below url for more tips.
http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
Gather Schema Stats
begin
dbms_stats.gather_schema_stats(
ownname=> ‘SCOTT’,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
Gather Table Stats (Capturing table stats automatically captures all its index stats)
begin
dbms_stats.gather_table_stats(
ownname=> ‘SCOTT’,
tabname=> ‘EMP_BIG’ ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> 4,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
Gather Table Partition Stats
begin
dbms_stats.gather_table_stats(
ownname => ‘SCOTT’,
tabname => ‘EMP_PARTITIONED’,
partname => ‘M20120430′,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘PARTITION’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
Gather Database Stats
begin
dbms_stats.gather_database_stats(
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
Script to find whether Stats are STALE for SCOTT schema
A DBA needs to check whether Stats are STALE when user complains that suddenly the queries or Application started running slow.
Stale stats are the most common reason for queries suddently running slow without any changes to Schema/Tables/Indexes.
SET SERVEROUTPUT ON
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);
IF ObjList.FIRST is NOT NULL THEN
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
ELSE
dbms_output.put_line(‘No Objects with Stale Statistics found’);
END IF;
END;
/
If above PL/SQL block returns that certain tables stats are STALE run the below PL/SQL block to capture the stats to remove STALE stats.
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’,options=>’GATHER STALE’);
END;
Automatic Optimizer Statistics Collection
The recommended approach to gathering optimizer statistics is to allow Oracle Database to automatically gather the statistics. Oracle Database gathers optimizer statistics on all database objects automatically and maintains those statistics as an automated maintenance task. The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to run automatically in Oracle Scheduler windows known as maintenance windows. By default, one window is scheduled for each day of the week.
Automatic optimizer statistics collection gathers optimizer statistics by calling the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC is an internal procedure, but it operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option.
The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.
Automatic optimizer statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the risks of generating poor ex*****on plans due to missing or stale statistics.
If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
Automatic optimizer statistics collection relies on the modification monitoring feature. If this feature is disabled, then the automatic optimizer statistics collection job will not be able to detect stale statistics. This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. TYPICAL is the default value.
June 20th, 2012 | Category: Advanced Oracle
Comments are closed.
dbms_stats Oracle SQL performance
