Tag Archives: ora-20005

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