STATISTIQUES : lock statistics

Le package DBMS_STATS offre la possibilité des verrouiller les statistiques d'une table ou de toutes les tables d'un schéma.

Ceci peut être utile lorsque l'on souhaite que la collecte automatique ne passe pas sur certaines tables dont on maîtrise le chargement et donc le moment opportun pour passer les statistiques.

Procédures de lock de statistiques :

DBMS_STATS.LOCK_TABLE_STATS (
  ownname VARCHAR2,tabname VARCHAR2);
DBMS_STATS.UNLOCK_TABLE_STATS ( 
  ownname VARCHAR2,tabname VARCHAR2);

DBMS_STATS.LOCK_SCHEMA_STATS (
  ownname VARCHAR2);
DBMS_STATS.UNLOCK_SCHEMA_STATS (
  ownname VARCHAR2);

Exemples de comportement en LOCK ou UNLOCK :

SQL> drop table couuk8.toto;

Table dropped.

SQL> CREATE TABLE couuk8.toto (
  2     a NUMBER
  3  );

Table created.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> -- test 1
SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> col owner for a8 trunc
SQL> col table_name for a10 trunc
SQL> SELECT owner, table_name, stattype_locked
  2  FROM dba_tab_statistics
  3  WHERE owner = 'COUUK8'
  4  AND stattype_locked IS NOT NULL
  5  ;


OWNER    TABLE_NAME STATT
-------- ---------- -----
COUUK8   TOTO       ALL

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('COUUK8');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> -- test 2
SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('COUUK8');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------
01/08/11

SQL> -- test 3
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO');
BEGIN DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO'); END;

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


SQL> -- test 4
SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------
01/08/11