Locking and Unlocking Optimizer Statistics

Locking stats are sometimes beneficial where table data is too volatile and changes too frequently.
After statistics are locked, you cannot make modifications to the statistics until the statistics have been unlocked.
Locking procedures are useful in a static environment when you want to guarantee that the statistics and resulting plan never change

When you lock statistics on a table, all dependent statistics are locked.

The locked statistics include table statistics, column statistics, histograms, and dependent index statistics.

Locking Statistics 

The DBMS_STATS package provides procedures for locking statistics: LOCK_SCHEMA_STATS,LOCK_PARTITION_STATS and LOCK_TABLE_STATS.

DEMONSTRATION

We will lock stats for Highly volatile table as mentioned below and do some testing.

=> Creating Sample Table and gathering Statistics post Populating data 

SQL> create table lock_unlock_Stats (id number,detail varchar2(80));
Table created.

SQL> INSERT INTO lock_unlock_Stats
SELECT level+1000 AS id,'Description of ' || level AS detail
FROM   dual
CONNECT BY level <= 1000; 
1000 rows created.

SQL> commit;
Commit complete.


SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: LOCK_UNLOCK_STATS
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'LOCK_UNLOCK_STATS', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME,COLUMN_NAME,LAST_ANALYZED,HISTOGRAM,NOTES FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='LOCK_UNLOCK_STATS';


TABLE_NAME	     COLUMN_NAME	  LAST_ANAL HISTOGRAM	    NOTES
-------------------- -------------------- --------- --------------- ---------------------------------------
LOCK_UNLOCK_STATS    ID 		  22-MAY-22 NONE
LOCK_UNLOCK_STATS    DETAIL		  22-MAY-22 NONE



SQL> SELECT COUNT(*) FROM LOCK_UNLOCK_STATS;
  COUNT(*)
----------
      1000
=> Let we LOCK TABLE Stats 
SQL> BEGIN
DBMS_STATS.LOCK_TABLE_STATS ('ABHI_TEST','LOCK_UNLOCK_STATS');
END;
/
PL/SQL procedure successfully completed.
=> If we try to gather statistics for Locked statististics we will get error as below
SQL> 
SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: LOCK_UNLOCK_STATS
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'LOCK_UNLOCK_STATS', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 2

=> But if we use Force=> True we can gather stats as below.
SQL> exec dbms_stats.gather_table_stats(ownname=>'abhi_test',tabname=>'LOCK_UNLOCK_STATS',cascade=>TRUE,degree =>4,force=>TRUE);
PL/SQL procedure successfully completed.
SQL>

=> Let we Delete few records and see if Optimizer use correct Rows

SQL> DELETE FROM LOCK_UNLOCK_STATS WHERE ROWNUM<100;
99 rows deleted.

SQL> COMMIT;
Commit complete.

SQL> SELECT COUNT(*) FROM LOCK_UNLOCK_STATS;
  COUNT(*)
----------
       901

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID	7v433g5udh2fh, child number 0
-------------------------------------
SELECT COUNT(*) FROM LOCK_UNLOCK_STATS
Plan hash value: 4068741660

--------------------------------------------------------------------------------
| Id  | Operation	   | Name	       | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE    |		       |     1 |	    |	       |
|   2 |   TABLE ACCESS FULL| LOCK_UNLOCK_STATS |  1000 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------
14 rows selected.
As we can see optimizer still uses 1000 rows due to Locked statistics.
Now let we check STATTYPE_LOCKED COlumn value which is ALL ,Means 'ALL dependent statistics is locked for this table'.

SQL> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,NOTES,STATTYPE_LOCKED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='LOCK_UNLOCK_STATS'
SQL> /

TABLE_NAME	       NUM_ROWS     BLOCKS NOTES		     STATTYPE_LOCKED
-------------------- ---------- ---------- ------------------------- ------------
LOCK_UNLOCK_STATS	   1000 	 5			     ALL


Unlocking Statistics

The DBMS_STATS package provides procedures for unlocking statistics: UNLOCK_SCHEMA_STATS,UNLOCK_PARTITION_STATS and UNLOCK_TABLE_STATS.

Now let we unlock above mentioned tables stats as below.

SQL> BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS ('ABHI_TEST','LOCK_UNLOCK_STATS');
END;
/
PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,NOTES,STATTYPE_LOCKED FROM USER_TAB_STATISTICS WHERE TABLE_NAME='LOCK_UNLOCK_STATS';     

TABLE_NAME	       NUM_ROWS     BLOCKS NOTES		     STATTYPE_LOCKED
-------------------- ---------- ---------- ------------------------- ------------------------------
LOCK_UNLOCK_STATS	   1000 	 5

SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: LOCK_UNLOCK_STATS
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'LOCK_UNLOCK_STATS', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM LOCK_UNLOCK_STATS;

  COUNT(*)
----------
       901

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID	7v433g5udh2fh, child number 0
-------------------------------------
SELECT COUNT(*) FROM LOCK_UNLOCK_STATS
Plan hash value: 4068741660

--------------------------------------------------------------------------------
| Id  | Operation	   | Name	       | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE    |		       |     1 |	    |	       |
|   2 |   TABLE ACCESS FULL| LOCK_UNLOCK_STATS |   901 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------
14 rows selected.

SQL> COMMIT;
Commit complete.
SQL>