Realizando o backup das estatísticas - Oracle 11g/12c

A ideia deste post é de forma simples e objetiva demonstrar como podemos realizar um backup das estatísticas, sejam as estatísticas de uma coluna, tabela, schema, database ou as estatísticas de sistema.
Quero ressaltar que a ideia deste post não é aprofundar sobre o tema e sim demonstrar de forma pratica e rápida como podemos realizar um backup e restore das estatísticas.

Inicialmente, vamos verificar as tabelas que estão abaixo do schema LAMIM e a data da última estatística realizada, como evidencia para nossa simulação.
SET LINES 210;
COL OWNER FOR A10;
COL TABLE_NAME FOR A20;

SELECT
 owner,
 table_name,
 avg_row_len,
 blocks,
 empty_blocks,
 num_rows,
 TO_CHAR(last_analyzed, 'DD/MM/YYYY HH24:MI:SS') as last_analyzed
FROM
 DBA_TABLES
WHERE
 OWNER = 'LAMIM';SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12

OWNER      TABLE_NAME           AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- -------------------- ----------- ---------- ------------ ---------- -------------------
LAMIM      LAMIMDBA_OBJECTS              98        593            0     117836 12/12/2017 00:00:55
LAMIM      LAMIMSEGMENTS                124        189            0      38245 12/12/2017 00:00:56
LAMIM      LAMIMTABLE                   248        104            0      19464 12/12/2017 00:00:56
LAMIM      LAMIMINDEXES                 230        148            0      19924 12/12/2017 00:00:55
LAMIM      LAMIMTST2                      0          0            0          0 12/12/2017 00:00:56
LAMIM      LSMIMTST3                      0          0            0          0 12/12/2017 00:00:56
LAMIM      LSMIMTST5                      0          0            0          0 12/12/2017 00:00:56
LAMIM      STATSBACKUP                  103        874            0      55209 12/12/2017 00:00:57
LAMIM      LSMIMTST4                      0          0            0          0 12/12/2017 00:00:56
LAMIM      LAMIMTST1                      0          0            0          0 12/12/2017 00:00:56

10 rows selected.
O primeiro passo para realizarmos é a criação da tabela que irá armazenar as informações das estatísticas através da procedure dbms_stats.create_stat_table. No exemplo abaixo, criarei a tabela STATSBACKUP abaixo do owner LAMIM na tablespace LAMIMTST.
--Sintaxe
dbms_stats.create_stat_table(
ownname          IN VARCHAR2,               -- nome do schema a ser exportado 
stattab          IN VARCHAR2,               -- tabela de estatística criada
tblspace         IN VARCHAR2 DEFAULT NULL,  -- Tablespace que a tabela de estatistica ser armazenada
global_temporary IN BOOLEAN  DEFAULT FALSE);
SQL> exec dbms_stats.create_stat_table(ownname => 'LAMIM',stattab => 'STATSBACKUP',tblspace => 'LAMIMTST');

PL/SQL procedure successfully completed.

SQL>
Antes de realizar qualquer alteração na coleta de estatística, vamos realizar um backup das estatísticas. Faremos alguns tipos diferentes de backup (tabela, schema, database,system e dictionary) para demonstração de algumas possibilidades.
Primeiro estarei exportando as estatísticas do schema LAMIM e todos os objetos contidos no mesmo. Para isso, será utilizada a procedure dbms_stats.export_schema_stats.
dbms_stats.export_schema_stats(
ownname IN VARCHAR2,               -- nome do schema a ser exportado
stattab IN VARCHAR2,               -- tabela de estatística criada
statid  IN VARCHAR2 DEFAULT NULL,  -- Definicao de um identificador - OPCIONAL
statown IN VARCHAR2 DEFAULT NULL); -- schema da tabela de estatistica criada
SQL> exec dbms_stats.export_schema_stats(ownname=>'LAMIM',stattab=>'STATSBACKUP',statown=>'LAMIM');

PL/SQL procedure successfully completed.

SQL>
É possível exportar as estatísticas de uma tabela específica (com ou sem seus respectivos indices), através da procedure dbms_stats.export_table_stats.
dbms_stats.export_table_stats(
ownname  IN VARCHAR2,                -- Nome do Schema
tabname  IN VARCHAR2,                -- Nome da Tabela
partname IN VARCHAR2 DEFAULT NULL,   -- Nome da particao
stattab  IN VARCHAR2,                -- tabela de estatística criada
statid   IN VARCHAR2 DEFAULT NULL,   -- Definicao de um identificador - OPCIONAL
cascade  IN BOOLEAN  DEFAULT TRUE,   -- TRUE = Exporta as estatísticas dos indices tammbém
statown  IN VARCHAR2 DEFAULT NULL);  -- schema da tabela de estatística criada
SQL> exec dbms_stats.export_table_stats(ownname => 'LAMIM',tabname => 'LAMIMSEGMENTS',partname => NULL, stattab => 'STATSBACKUP',statid => NULL, cascade => TRUE, statown =>'LAMIM');

PL/SQL procedure successfully completed.

SQL>
É sempre importante termos também uma copia das estatísticas de sistema e do dicionario de dados. Ambas podem ser exportadas através das procedures dbms_stats.export_system_stats e dbms_stats.export_column_stats.
dbms_stats.export_schema_stats(
ownname IN VARCHAR2,               -- Nome do Schema
stattab IN VARCHAR2,               -- tabela de estatística criada
statid  IN VARCHAR2 DEFAULT NULL,  -- Definição de um identificador - OPCIONAL
statown IN VARCHAR2 DEFAULT NULL); -- schema da tabela de estatistica criada

dbms_stats.export_dictionary_stats(
stattab IN VARCHAR2,                 -- tabela de estatística criada
statid  IN VARCHAR2  DEFAULT NULL,   -- Definição de um identificador - OPCIONAL
statown IN VARCHAR2  DEFAULT NULL);  -- schema da tabela de estatistica criada
SQL> exec dbms_stats.export_system_stats(stattab=>'STATSBACKUP',statown=>'LAMIM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_dictionary_stats(stattab=>'STATSBACKUP',statown=>'LAMIM');

PL/SQL procedure successfully completed.
Também podemos realizar o backup das estatísticas de todos os objetos da base, através da procedure dbms_stats.export_database_stats, conforme exemplo abaixo.
dbms_stats.export_database_stats(
stattab IN VARCHAR2             ,   -- tabela de estatística criada
statid  IN VARCHAR2 DEFAULT NULL,   -- optional identifier
statown IN VARCHAR2 DEFAULT NULL);  -- schema da tabela de estatistica criada
SQL> exec dbms_stats.export_database_prefs(stattab=>'STATSBACKUP',statown=>'LAMIM');

PL/SQL procedure successfully completed.

SQL>
Agora que efetuamos o backup das estatísticas, vamos alterar a estatística da tabela LAMIMDBA_OBJECTS de size 254 para size auto. E depois vamos voltar estas estatísticas para demonstração. Vale ressaltar que a sintaxe do comando para importar as estatísticas é a mesma, onde devemos apenas alterar dbms_stats.export_* para dbms_stats.import_* conforme exemplo abaixo.
set pages 2000
col data_type for a10
set lines 210
select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM,to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss') from dba_tab_columns where table_name='LAMIMDBA_OBJECTS';
SQL> SQL> SQL>
OWNER      TABLE_NAME           COLUMN_NAME                    DATA_TYPE     DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM       TO_CHAR(LAST_ANALYZ
---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------- --------------- -------------------
LAMIM      LAMIMDBA_OBJECTS     OWNER                          VARCHAR2   .043478261          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_NAME                    VARCHAR2    .00001315          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     SUBOBJECT_NAME                 VARCHAR2   .005555556     117251           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_ID                      NUMBER     8.4872E-06         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     DATA_OBJECT_ID                 NUMBER      .00002533      78324           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_TYPE                    VARCHAR2   .023809524          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     CREATED                        DATE       .000354862          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     LAST_DDL_TIME                  DATE       .000487567         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     TIMESTAMP                      VARCHAR2   .000337268         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     STATUS                         VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     TEMPORARY                      VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     GENERATED                      VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     SECONDARY                      VARCHAR2            1          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     NAMESPACE                      NUMBER     .058823529         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     EDITION_NAME                   VARCHAR2            0     117836           0 NONE            12/12/2017 00:00:55

15 rows selected.

SQL>

execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LAMIM',TABNAME =>'LAMIMDBA_OBJECTS',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS  SIZE 254',degree => 5 ,granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);
SQL> SQL>

PL/SQL procedure successfully completed.

SQL> SQL>
set pages 2000
col data_type for a10
set lines 210
select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM,to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss') from dba_tab_columns where table_name='LAMIMDBA_OBJECTS';
SQL> SQL> SQL> SQL>
OWNER      TABLE_NAME           COLUMN_NAME                    DATA_TYPE     DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM       TO_CHAR(LAST_ANALYZ
---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------- --------------- -------------------
LAMIM      LAMIMDBA_OBJECTS     OWNER                          VARCHAR2   .000004239          0          26 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     OBJECT_NAME                    VARCHAR2   .000013631          0         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     SUBOBJECT_NAME                 VARCHAR2    .00331768     117334         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     OBJECT_ID                      NUMBER     .000008479         12         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     DATA_OBJECT_ID                 NUMBER     .000025499      78349         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     OBJECT_TYPE                    VARCHAR2   .000004239          0          43 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     CREATED                        DATE       .001513805          0         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     LAST_DDL_TIME                  DATE       .001591924         12         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     TIMESTAMP                      VARCHAR2   .001511717         12         254 HEIGHT BALANCED 12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     STATUS                         VARCHAR2   .000004239          0           2 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     TEMPORARY                      VARCHAR2   .000004239          0           2 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     GENERATED                      VARCHAR2   .000004239          0           2 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     SECONDARY                      VARCHAR2   .000004239          0           1 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     NAMESPACE                      NUMBER     4.2395E-06         12          18 FREQUENCY       12/12/2017 21:47:19
LAMIM      LAMIMDBA_OBJECTS     EDITION_NAME                   VARCHAR2            0     117951           0 NONE            12/12/2017 21:47:19

15 rows selected.

SQL> exec dbms_stats.import_table_stats(ownname => 'LAMIM',tabname => 'LAMIMDBA_OBJECTS',partname => NULL, stattab => 'STATSBACKUP',statid => NULL, cascade => TRUE, statown =>'LAMIM');

PL/SQL procedure successfully completed.

SQL> set pages 2000
col data_type for a10
set lines 210
select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM,to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss') from dba_tab_columns where table_name='LAMIMDBA_OBJECTS';
SQL> SQL> SQL>
OWNER      TABLE_NAME           COLUMN_NAME                    DATA_TYPE     DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM       TO_CHAR(LAST_ANALYZ
---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------- --------------- -------------------
LAMIM      LAMIMDBA_OBJECTS     OWNER                          VARCHAR2   .043478261          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_NAME                    VARCHAR2    .00001315          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     SUBOBJECT_NAME                 VARCHAR2   .005555556     117251           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_ID                      NUMBER     8.4872E-06         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     DATA_OBJECT_ID                 NUMBER      .00002533      78324           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     OBJECT_TYPE                    VARCHAR2   .023809524          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     CREATED                        DATE       .000354862          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     LAST_DDL_TIME                  DATE       .000487567         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     TIMESTAMP                      VARCHAR2   .000337268         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     STATUS                         VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     TEMPORARY                      VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     GENERATED                      VARCHAR2           .5          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     SECONDARY                      VARCHAR2            1          0           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     NAMESPACE                      NUMBER     .058823529         12           1 NONE            12/12/2017 00:00:55
LAMIM      LAMIMDBA_OBJECTS     EDITION_NAME                   VARCHAR2            0     117836           0 NONE            12/12/2017 00:00:55

15 rows selected.

SQL>
É possível constatar que apos importar as estatísticas da tabela LAMIMDBA_OBJECTS o last_analyzed está com a data anterior a alterção (12/02/2017 00:00 horas).
As estatísticas podem ser exportadas de uma base e importadas em outra. Basta gerar um export da tabela de estatísticas STATSBACKUP que criamos no inicio do processo.

Fonte:
http://psoug.org/reference/dbms_stats.html
Statistics Best Practices: How to Backup and Restore Statistics (Doc ID 464939.1)
http://www.dba-oracle.com/t_export_import_cbo_optimizer_statistics_dbms_stats.htm

Comentários