Stats for Table and Table Partitions

Backup, Restore, Copy, lock & unlock Stats for Table and Table Partitions

Stats will help the optimizer to choose the better execution plan for SQL Queries. Sometime change in stats may cause the performance issue in Database. You need to take backup of stats before any major task like Upgrade,patching, application deployment etc.

Using procedures in DBMS_STATS package you can backup and restore stats for table.

Assumes the user SCOTT already has access to execute DBMS_STATS

GRANT EXECUTE ON dbms_stats TO scott;

Note: SYS user give grant for execute the DBMS_STATS package to Scott user

Backup and restore for table stats

1. Following command will create table for backup the stats

execute dbms_stats.create_stat_table(ownname=> 'scott', stattab=> 'backup_stats');

2. Procedure to export statistics for a table

exec dbms_stats.export_table_stats(ownname=>'scott', tabname=>'test', statown=>'scott', stattab=>'backup_stats', cascade=>true);
Cascade is true means it will export table plus index stats.
3. Import table stats (Eg TEST to TEST1 table)

SQL> exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'test1', statown=>'scott', stattab=>'backup_stats', cascade=>true);

4. Drop statistics table

SQL> execute dbms_stats.drop_stat_table(ownname=> 'scott', stattab=> 'backup_stats');

Copy stats for a table partition to another partition
Example: Suppose i have test table having 5 partition, my SQL query is running fine in P4 partition but when it access P5 partition it used different execution plan rather than P4 plan. So in some case we need to copy the stats of one partition to next partition and lock the stats

exec dbms_stats.copy_table_stats('SCOTT', 'TEST',srcpartname=>'p4', dstpartname=>'p5');

Lock and Unlock the Stats for the Table
You can also locked and unlocked the stats manually, so that automatic job do not change the stats of the table

1. Check the lock stats status for the table
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

2. Lock the stats for the table

exec dbms_stats.lock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.

3. Unlock the stats for the table

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.

4. Error occurs if stats for table is locked,then you need to unlocked table stats

— gather statistics on locked table
SQL> exec dbms_stats.gather_table_stats('scott', 'test');

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

– gather statistics on the index using analyze
SQL> analyze index scott.test_idx compute statistics;

ERROR at line 1:
ORA-38029: object statistics are locked

Leave a Reply

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

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

Connecting to %s

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