Create Statistics

Analyze command

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied in DBMS_STATS.GATHER_SCHEMA_STATS.

The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options

Code examples 

ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;
DBMS_STATS.GATHER_SCHEMA_STATS

Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don’t always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements.

The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.

 

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Code examples:

exec  DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
                              ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
For reference check ORADEV link

Using for gather schema level stats

exec DBMS_STATS.gather_schema_stats(

ownname          => ‘FZAFAR’,

options          => ‘GATHER AUTO’,

estimate_percent => dbms_stats.auto_sample_size,

method_opt       => ‘for all columns size repeat’,

cascade          => true )

There are several values for the OPTIONS parameter that we need to know about:

  • GATHER_ reanalyzes the whole schema
  • GATHER EMPTY_ only analyzes tables that have no existing statistics
  • GATHER STALE_ only reanalyzes tables with more than 10 percent modifications (inserts, updates,   deletes)
  • GATHER AUTO_ will reanalyze objects that currently have no statistics and objects with stale statistics.  Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.

More Details read Art Builder DBMS_STATS

Oracle link for DBMS_STATS

Implications of Compute Statistics Option

Oracle provides estimate statistics option which bases the object’s statistics on a review of a portion of the data. Usually analyzing 20 percent of the table is sufficient.

Analyzing data can require large amounts of sort space. It may include full table scans as well so its better to change session settings prior to starting the analysis.The session settings to change are

SORT_AREA_SIZE

DB_FILE_MULTIBLOCK_READ_COUNT

The larger the sort area size is, the less likely you are to need to use the temporary tablespace for sort segments.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: