RUNNING GATHERSTATS

RUNNING  GATHERSTATS

To gather stats in oracle we require to use the DBMS_STATS package.It will collect the statistics in parallel with collecting the global statistics for partitioned objects.The DBMS_STATS package specialy used only for optimizer statistics. As i explained in first paragraph the dbms_stats is very vital for good SQL performance. We require to gather the stats before adjusting or setting up any optimizer parameters in oracle.

The less the query cost the execution time of query is fast. We must have to gather the statistics on regular basis for database object to give the best information to oracle optimizer to run queries in best possible time.Using the analyze statement is traditional way of checking the cost of query. But now a days to gather stats in oracle we need to use DBMS_STATS package.

 

Gather STATS

 

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO : You can specify one column between “” instead of all columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS : Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : Collect histograms for columns have skewed value should test skewness first
FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.

 

DATABASE Level
         Gathering statistics for all objects in database, cascade will include indexes  

 

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

 

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

 

SQL> exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

 

SCHEMA level

 

Gathering statistics for all objects in a schema, cascade will include indexes. If not used Oracle will determine whether to collect it or not.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 

PL/SQL procedure successfully completed.

 

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

PL/SQL procedure successfully completed.

 

SQL> set timing on

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);

Enter value for schema_name: vbt

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.19

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');

Enter value for schema_name: vbt

 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

TABLE Level

          The CASCADE parameter determines whether or not statistics are gathered for the indexes on a table.

 

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');

Enter value for schema_name: vbt

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.09

SQL> SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

Enter value for tname: vignesh

old   1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME'

new   1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='vignesh'

 

no rows selected

 

Index Statistics

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);