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.
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
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
Postar um comentário