Partition stats for table in Oracle

Managed Partition stats for a table in Oracle

Enterprise edition has the feature of partitioning in oracle. So, for large table for better access we can break the table into partition such as monthly partition,quarterly partition, location partition etc.
Partition Stats in oracle is the stats of a particular partition for a table. We need to maintained the table global stats and partition stats for used by optimizer to provide the better execution plan.

I am taking example of RETAIL schema having SALES table which is partition table in database.

Check the partition and local stats for table

select last_analyzed,partition_name from dba_tab_partitions
where table_name='SALES' and partition_name in ('SALES201701','SALES201702','SALES201612');

Get more detail of partition for a table

select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, stattype_locked
from dba_tab_statistics
where table_name = 'SALES';

Gather the stats for a specific partition in table:

exec dbms_stats.gather_table_stats(OWNNAME => 'RETAIL',
TABNAME =>'SALES',
PARTNAME => 'SALES201701',
ESTIMATE_PERCENT => 10,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO',
DEGREE=>8,
CASCADE=>TRUE
);

Copy the stats from one partition to another partition:

Syntax:
exec dbms_stats.copy_table_stats(schema,tablename,partitionname01,partition02, force=>TRUE);

Note: Force true means if you partition stats is locked then it ignore it otherwise give stats locked error "ORA-20005: object statistics are locked (stattype = ALL)"

Example:
exec dbms_stats.copy_table_stats('RETAIL','SALES','SALES201701','SALES201702', force=>TRUE);

Lock the partition stats for a table:

exec dbms_stats.lock_partition_stats('RETAIL','SALES','SALES201702');

Unlock the partition stats for a table:

exec dbms_stats.unlock_partition_stats('RETAIL','SALES','SALES201702');

If error ORA-20005: object statistics are locked (stattype = ALL) occurred then unlock the stats of the partition table.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.