terça-feira, 12 de dezembro de 2017

#DICA - Realizando o backup das estatísticas


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

segunda-feira, 4 de dezembro de 2017

Oracle ACE Associate


Em Novembro de 2014 inciei este blog com o intuito de compartilhar informações com a comunidade Oracle e como forma de estudo.
Hoje tive o privilégio de receber da Oracle a nomeação para o programa Oracle ACE como ACE Associate ACE Associate 
Este é o primeiro nível do programa Oracle ACE, que conta aproximadamente com 30 pessoas (em todo o programa) no Brasil. 
O programa Oracle ACE reconhece os profissionais de demonstram tanto proficiência Técnica e fortes credenciais como entusiastas e defensores da comunidade.
https://apex.oracle.com/pls/otn/f?p=19297:4:::NO:4:P4_ID:17460

Mais informações sobre o programa: http://www.oracle.com/technetwork/pt/community/oracle-ace/index.html

quarta-feira, 29 de novembro de 2017

DEFERRED SEGMENT CREATION


A partir do Oracle 11g R2 foi introduzida uma nova funcionalidade que permite que as tabelas, indices e lobs não sejam criados até que uma linha seja inserida na respectiva tabela. Esta funcionalidade é controlada pelo parâmetro DEFERRED_SEGMENT_CREATION, que por padrão vem definido como true (ativo) a partir desta versão.
Esta funcionalidade tem como principal objetivo reduzir o espaço em disco e minimizar o tempo de criação de tabelas em uma nova implementação, por exemplo.

Tive um atendimento a um determinado cliente onde a aplicação estava apresentando erro de tabelas inexistentes após um reorg realizado pelo mesmo. Na atividade de reorg, foram criadas novas tablespaces e os dados foram movidos para as mesmas. Posteriormente as tablespaces antigas foram removidas apos ficaram offline por um determinado período.

Nesta situação, pude constatar que o problema ocorreu justamente devido ao parâmetro DEFERRED_SEGMENT_CREATION estar como true. Uma vez que o cliente baseou-se na dba_segments/dba_extents, para realizar a consulta de objetos a serem movidos e as tabelas que ainda não tinham dados inseridos não constavam na mesma.

Estarei demonstrando esse cenário em um exemplo pratico no qual será criada uma tabela lamimtst1 sem registros e uma segunda tabela lamimtst2 com registros, para demonstrar como ela não constará na dba_segments/dba_extents até que o primeiro registro seja inserido, independente da transação sofrer commit ou rollback.
SQL> conn lamim/lamim
Connected.
SQL> create tablespace lamimtst force logging datafile '/u01/lamimtst/datafiles/lamimtst01.dbf' size 100m autoextend on next 100m maxsize 1g;

Tablespace created.

SQL> show parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL> create table lamimtst1(fone number(9)) tablespace lamimtst;

Table created.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

no rows selected

SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME from dba_extents where tablespace_name='LAMIMTST';

no rows selected

SQL> create table lamimtst2(nome varchar2(20)) tablespace lamimtst;

Table created.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

no rows selected

SQL> insert into lamimtst2 values ('Jhonata Lamim');

1 row created.

SQL> 
SQL> set lines 210
SQL> col segment_name for a45
SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME                                  TABLESPACE_NAME
------------------------------ --------------------------------------------- ------------------------------
LAMIM                          LAMIMTST2                                     LAMIMTST
SQL> select OWNER, SEGMENT_NAME,TABLESPACE_NAME from dba_extents where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
LAMIM                          LAMIMTST2            LAMIMTST

SQL>
SQL> rollback;

Rollback complete.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME                                  TABLESPACE_NAME
------------------------------ --------------------------------------------- ------------------------------
LAMIM                          LAMIMTST2                                     LAMIMTST

SQL>
O problema do cliente ocorreu apos mover todos os objetos listados na dba_segments/dba_extents. Porém é possível mesmo com o parametro DEFERRED_SEGMENT_CREATION em true, especificar para que o segmento seja criado no momento da criação da tabela através do comando segment creation immediate. Ou caso o parametro esteja como false, podemos usar segment creation deffered para que o segmento não seja criado mesmo com o parametro definido como false.
SQL> show parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
LAMIM                          LAMIMTST2            LAMIMTST

SQL> create table LSMIMTST3 (CPF number(11)) segment creation immediate tablespace lamimtst;

Table created.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
LAMIM                          LAMIMTST2            LAMIMTST
LAMIM                          LSMIMTST3            LAMIMTST

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> create table LSMIMTST4 (CPF number(11)) segment creation deferred tablespace lamimtst;

Table created.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
LAMIM                          LAMIMTST2            LAMIMTST
LAMIM                          LSMIMTST3            LAMIMTST

SQL> create table LSMIMTST5 (CPF number(11)) tablespace lamimtst;

Table created.

SQL> select owner, segment_name, tablespace_name from dba_segments where tablespace_name='LAMIMTST';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
LAMIM                          LAMIMTST2            LAMIMTST
LAMIM                          LSMIMTST3            LAMIMTST
LAMIM                          LSMIMTST5            LAMIMTST

SQL>
Fonte:
https://docs.oracle.com/cloud/latest/db112/REFRN/initparams075.htm#REFRN10307
http://www.dba-oracle.com/t_oracle_deferred_segment_creation.htm

domingo, 19 de novembro de 2017

Realizando o rollback das estatísticas de uma tabela - DBMS_STATS.restore_table_stats


Quando uma tabela tem sua estatística atualizada, as estatísticas antigas ficam armazenadas na SM/OPTSTAT pelo período de retenção definido no ambiente.
Esta opção pode ser bastante útil quando temos alterações nas estatísticas de alguma tabela que geram algum problema de performance. Desta forma é possível facilmente voltar as estatísticas que estavam antes do "problema" ter acontecido. É claro se este período estiver dentro da retenção definida para o ambiente.

Primeiramente, vamos verificar qual a retenção padrão para as estatísticas em nosso ambiente. A verificação pode ser feita através da procedure dbms_stats.get_stats_history_availability e dbms_stats.get_stats_history_availability. Vale ressaltar que a retenção padrão das estatísticas no Oracle é de 31 dias.
lamimdb1@SYS> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

lamimdb1@SYS> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
12-OCT-17 08.49.56.000000000 AM -02:00
Conforme verificado, nosso ambiente está configurado com a retenção padrão de 31 dias, com isso a data mais antiga para uma restauração de uma coleta é de 12/10/2017 as 08:48:56 horas. 
Caso necessário, a alteração da retenção pode ser alterada de acordo com a necessidade. Para isso, basta definir a nova retenção através da procedure dbms_stats.alter_stats_history_retention. 
Vamos alterar nossas estatísticas para 45 dias. É importante destacar que, quanto maior o período de retenção das estatísticas, mais área será necessária para armazenar essas informações na sysaux.
lamimdb1@SYS> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

lamimdb1@SYS> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         45

lamimdb1@SYS>
Para demonstrar o processo, será criada uma tabela de testes STATISTICSTST cópia da dba_objects.  Posteriormente será executada a coleta de estatísticas da mesma, de 2 formas. Uma com size auto e outra com size 1. Esta etapa é realizada apenas para fins de demonstração.
lamimdb1@SYS> create table statisticstst as select * from dba_objects;

Table created.

lamimdb1@SYS> execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS',TABNAME =>'STATISTICSTST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS  SIZE AUTO',degree => 5 ,granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);


PL/SQL procedure successfully completed.

lamimdb1@SYS> lamimdb1@SYS> lamimdb1@SYS> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

lamimdb1@SYS> select last_analyzed from dba_tables where table_name='STATISTICSTST';

LAST_ANALYZED
-------------------
19/11/2017 21:52:03

lamimdb1@SYS>  execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS',TABNAME =>'STATISTICSTST',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS  SIZE 1',degree => 5 ,granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);

PL/SQL procedure successfully completed.

lamimdb1@SYS>  select last_analyzed from dba_tables where table_name='STATISTICSTST';

LAST_ANALYZED
-------------------
19/11/2017 22:12:46

lamimdb1@SYS>
Agora vamos considerar que a ultima coleta realizada (22:12:46 horas) ocasionou uma degradação e um processo e será preciso realizar um rollback das estatísticas. Vamos consultar o histórico de estatísticas para a tabela STATISTICSTST e ver quais informações temos para poder realizar o rollback.
lamimdb1@SYS> set lines 210
select a.table_name, a.stats_update_time from DBA_TAB_STATS_HISTORY a
where a.table_name='STATISTICSTST';lamimdb1@SYS>   2

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
STATISTICSTST                  19-NOV-17 09.52.03.131616 PM -02:00
STATISTICSTST                  19-NOV-17 10.12.46.653555 PM -02:00
É possível validar que temos no histórico as 2 coletas que realizamos. Estaremos realizando a restauração das estatísticas para as realizada as 21:52:03 horas, através da procedure dbms_stats.restore_table_stats onde passaremos o owner, table_name e data, conforme abaixo.
lamimdb1@SYS> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed from dba_tables
where table_name='STATISTICSTST';
  2
TABLE_NAME                     LAST_ANALYZED
------------------------------ ----------------
STATISTICSTST                  19/11/2017 22:12

lamimdb1@SYS>  exec dbms_stats.restore_table_stats('SYS', 'STATISTICSTST','19-NOV-17 09.52.03.131616 PM -02:00');

PL/SQL procedure successfully completed.

lamimdb1@SYS> select table_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi') last_analyzed from dba_tables
where table_name='STATISTICSTST';
  2
TABLE_NAME                     LAST_ANALYZED
------------------------------ ----------------
STATISTICSTST                  19/11/2017 21:52

Fonte: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#i1048566

segunda-feira, 30 de outubro de 2017

Teste de carga no Oracle 12c com o swingbench


Para quem ainda não conhece, o swingbench é um software livre desenvolvido em Java por Dominic Giles, para execução e monitoramento de cargas de trabalho em Banco de Dados Oracle 10g, 11g e 12c. Pode ser utilizado para demonstrar e testar tecnologias como Oracle Real Application Clusters, online table rebuilds, standby databases, backup e recovery, etc.
O swingbench pode ser executado em ambiente Unix/Linux e Windows, local ou remotamente. Atualmente está na versão 2.6 e tem como pré requisito a instalação do java 8 64 bits.

O download da ferramenta pode ser realizado em http://dominicgiles.com/downloads.html. Vale destacar que não há necessidade de instalação da ferramenta. Porém existe a necessidade de criação de um schema no banco de dados, onde os objetos para o teste de carga serão realizados. 

Em nosso exemplo, o swingbench será executado a partir de um ambiente windows, fazendo uma conexão em um banco de dados 12.1.0.2 singles com ASM.

Inicialmente estaremos criando uma tablespace própria para o schema que será utilizados no teste.
[oracle@lamimtst01: cdblamim1 ~] $ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 30 21:18:49 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

cdblamim1@CDB$ROOT> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 LAMIM                      READ WRITE NO

cdblamim1@CDB$ROOT> alter session set container=LAMIM;

Session altered.

cdblamim1@CDB$ROOT> create tablespace swingbanch datafile '+DGDATA/cdblamim1/swingbench01.dbf' size 100m autoextend on next 100m maxsize 10g;

Tablespace created.

cdblamim1@CDB$ROOT>
Apos realizado o download e descompactado o arquivo, vamos acessar o diretório winbin e executar o arquivo oewizard. Esta arquivo nada mais é do que a execução do arquivo JAVA, que abrirá o Oracle Entry Intall Wizard.
Clicando em next, teremos a possibilidade de escolher a versão 1 ou a 2. Como recomendado na própria instalação, estaremos executando a versão 2.
Nesta etapa, teremos a opção de criação do repositório que será utilizado nos testes ou de remoção do mesmo. Neste caso, estaremos criando o repositório.
Agora vamos selecionar as credenciais de acesso ao ambiente para criação do repositório.
Apos entrar com as credenciais, vamos entrar com o usuário, senha e tablespace onde os objetos serão criados. Nesta etapa, caso o usuário não exista, será criado. 
Tendo em vista que a ideia é apenas realizar um teste de carga, não estaremos alterando as options utilizadas. Porém, podem ser alteradas de acordo com o licenciamento existe.
Nesta etapa vamos selecionar o tamanho do schema que será criado. Neste exemplo estarei utilizando a menor opção padrão que é de 1 GB. Porém pode ser definido o valor deseja no campo User Defined Scale.
Vamos definir o paralelismo que será utilizado para a criação do schema. O valor padrão apresentado corresponde a 2x o parâmetro cpucount. 
Clicando em finish, o processo de criação dos objetos será iniciado. Poderemos em real time acompanhar o progresso do processo de criação.
Concluído o processo de criação dos objetos. Podemos fechar o Oracle Entry Install Wizard, voltar no diretório swingbench\winbin e executar o swingbench. Na execução seremos questionados qual o arquivo de configuração utilizar. Estaremos utilizando o Stress_Test.
Na tela aberta, vamos entrar com o usuário e senha criados anteriormente e então disparar o processo de carga. Existem varia opções que podem ser exploradas, neste exemplo deixaremos todas as padrões.
Eis o nosso teste de carga iniciando.
É uma ferramenta bastante interessante para comparação de workloads, stress test, etc.


Fonte:http://dominicgiles.com

sexta-feira, 20 de outubro de 2017

Estatísticas incrementais para tabelas e índices particionadas


A coleta de estatísticas de forma incremental foi introduzida a partir da versão 11g, para melhorar a performance da coleta de estatística de grandes tabelas particionadas.

Nas tabelas particionadas, geralmente os novos dados são inseridos em uma nova partição. Quando uma nova partição é adicionada e os dados inseridos, as estatísticas da nova partição deve ser coletada e as estatísticas globais também devem ser atualizadas. Esse processo em grandes tabelas particionadas pode ser de grande impacto no ambiente uma vez que quanto mais partições são inseridas maior será o tempo de coleta devido ao full table scan realizado para atualização das estatísticas globais.

Com a coleta incremental ativada (ativação a nível de tabela) e a coleta é realizada com o parâmetro GRANULARITY AUTO, o Oracle realizada a coleta de estatística da nova partição e atualizará a estatística global apenas com as informações das partições modificadas, ao invés de atualizar as informações de todas as partições existentes (realizando um full table scan).

Vamos a prática..

Estarei realizando uma demonstração utilizando uma tabela com 38 partições existentes, e uma tamanho de 41,5 MB.
LAMIMTST1@SYS>  select table_owner, table_name, count(1) from dba_tab_partitions where table_name='TABLE_PART_LAMIM' group by table_name, table_owner;

TABLE_OWNER                    TABLE_NAME                       COUNT(1)
------------------------------ ------------------------------ ----------
LAMIM                          TABLE_PART_LAMIM             38


LAMIMTST1@SYS> select sum(bytes/1024/1024) from dba_segments where segment_name='TABLE_PART_LAMIM';

SUM(BYTES/1024/1024)
--------------------
                41.5

Elapsed: 00:00:00.14

Primeiramente, vamos verificar através do comando dbms_stats.get_prefs se a tabela TABLE_PART_LAMIM está com o parâmetro FALSE (desativado) ou TRUE (ativado) para as coletas incrementais.
LAMIMTST1@SYS> SELECT dbms_stats.get_prefs(ownname=>'LAMIM',pname=>'INCREMENTAL',tabname=>'TABLE_PART_LAMIM') FROM DUAL;

DBMS_STATS.GET_PREFS(OWNNAME=>'LAMIM',PNAME=>'INCREMENTAL',TABNAME=>'TABLE_PART_LAMIM
--------------------------------------------------------------------------------
FALSE

Conforme podemos contatar acima, a coleta desta tabela não executará de forma incremental. Posteriormente, executamos uma coleta de estatística da tabela para mensurar o tempo de execução, que foi de 21,28 segundos.
LAMIMTST1@SYS> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => 'LAMIM',
  4  TABNAME =>'TABLE_PART_LAMIM',
  5  estimate_percent => dbms_stats.auto_sample_size,
  6  method_opt => 'FOR ALL COLUMNS  SIZE AUTO',
  7  degree => 5 ,
  8  granularity => 'ALL',
  9  cascade => TRUE,
 10  no_invalidate => FALSE);
 11  end;
 12  /


PL/SQL procedure successfully completed.

Elapsed: 00:00:21.28
LAMIMTST1@SYS> LAMIMTST1@SYS>

Agora vamos definir o parâmetro incremental como TRUE e verificar o tempo de execução para que possamos comparar.
exec dbms_stats.set_table_prefs('LAMIM','TABLE_PART_LAMIM','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

LAMIMTST1@SYS> SELECT dbms_stats.get_prefs(ownname=>'LAMIM',pname=>'INCREMENTAL',tabname=>'TABLE_PART_LAMIM') FROM DUAL;

DBMS_STATS.GET_PREFS(OWNNAME=>'DW',PNAME=>'INCREMENTAL',TABNAME=>'FATOCLASSIFICA
--------------------------------------------------------------------------------
TRUE
Agara que definimos a opção INCREMENTAL para nossa tabela particionada, vamos efetuar uma nova coleta de estatísticas para mensurar novo tempo de execução.
LAMIMTST1@SYS> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => 'LAMIM',
  4  TABNAME =>'TABLE_PART_LAMIM',
  5  estimate_percent => dbms_stats.auto_sample_size,
  6  method_opt => 'FOR ALL COLUMNS  SIZE AUTO',
  7  degree => 5 ,
  8  granularity => 'ALL',
  9  cascade => TRUE,
 10  no_invalidate => FALSE);
 11  end;
 12  /


PL/SQL procedure successfully completed.

Elapsed: 00:00:6.31
LAMIMTST1@SYS> LAMIMTST1@SYS>
Conforme pode ser validado na execução acima, o ganho no tempo de execução com a coleta incremental é bastante expressivo, representando um ganho de 70,35%.

Vale ressaltar alguns pontos importantes sobre esse processo:

  • Para que o processo de coleta execute de modo incremental, o valor do parâmetro estimate_percent deve estar definido como dbms_stats.auto_sample_size.
  • Sempre que uma tabela particionada é definida para execução da coleta incremental, sempre que houver uma alteração DML em uma partição ou subpartição a mesma será marcada para que a mesma seja coletada na próxima execução da rotina.Ou seja, não é levado em consideração o valor do stale_percent.
  • A partir da versão 12c já é possível utilizar a coleta incremental junto com a definição de uma porcentagem de modificações (stale_percent).
Fonte:
https://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i37048
https://blogs.oracle.com/optimizer/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-1

sexta-feira, 13 de outubro de 2017

Compressão de índices usando o Index Key Compression - Oracle 11g


Quando o assunto é compressão, as primeiras options que vem em mente são table compression e advanced row compression (que ajudam a reduzir o tamanho da base em um fator de 2 a 5 vezes, variando de ambiente para ambiente). 
Porém não podemos esquecer que muitas vezes os índices podem representar um tamanho superior do que os dados, uma vez que para cada tabela podemos ter N índices.
Algumas vezes os índices podem representar mais de 50% do tamanho da base, por isso é importante lembrar que desde a versão 8.1.3 (antes mesmo da table compression lançada na versão 9.2) temos a feature index key compression.
A feature index key compression é uma feature Enterprise Edition (sem custo adicional) que atua na redução dos valores repetidos, comprimindo os dados e dividindo  o índice em 2 partes  o principal grupo de colunas, denominado entrada de prefixo (potencialmente compartilhado em vários valores de chave) e as colunas de sufixo (que é exclusivo para cada chave de índice). Como os prefixos são potencialmente compartilhados em várias chaves de um bloco, estes podem ser armazenados de forma mais otimizada (ou seja, apenas uma vez) e compartilhados em múltiplas entradas de sufixo, resultando na compactação dos dados de um índice. Além da compactação o armazenamento de mais chaves para cada bloco de índice implica na melhora do desempenho uma vez que causará menos I/O.

A figura abaixo, ilustra a diferença estrutural entre um índice não comprimido e um índice comprimido.
Porém para que esta opção traga benefício ao ambiente, precisamos validar se o índice que estamos analisando é um índice que terá ganhos caso com a feature index key compression.
Para descobrir esta informação, precisamos realizar um analyze do indice e então consultar se o OPT_CMPR_COUNT é >= 1.

Estarei criando uma tabela chamada lamimtst, copia da dba_objects e um índice para as colunas ower e status, para podermos demonstrar na pratica o processo de compressão do índice e os ganhos do mesmo.
SQL> conn lamim/lamim
Connected.
SQL>
SQL> conn lamim
Enter password:
Connected.
SQL> create table lamimtst as select * from dba_objects;

Table created.
SQL>
SQL> create index ixlamimtst on lamimtst(OWNER,STATUS);

Index created.

Realizada a criação da tabela do índice para demonstração, vou verificar o tamanho do indice e gerar um trace de um select que utilize o mesmo antes de aplicarmos a compressão.
SQL> select segment_name, sum(bytes/1024) from user_segments where segment_name='IXLAMIMTST' group by segment_name;

SEGMENT_NAME                                  SUM(BYTES/1024)
--------------------------------------------- ---------------
IXLAMIMTST                                               4096

 select * from lamimtst
 where STATUS='VALID'
 and OWNER='LAMIM'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05         96         82          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.05         97         87          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID LAMIMTST (cr=5 pr=1 pw=0 time=217 us cost=1 size=207 card=1)
         1          1          1   INDEX RANGE SCAN IXLAMIMTST (cr=4 pr=0 pw=0 time=50 us cost=1 size=0 card=1)(object id 268044528)

Agora vamos realizar um analyze do índice para verificar se o mesmo é elegível para compressão.
SQL> set lines 210
SQL> select name, height, blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE FROM index_stats;

NAME                               HEIGHT     BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- ---------- -------------- ----------------
IXLAMIMTST                              3        512              2               53

No analyze realizado acima, podemos constatar que utilizando um fator de compressão 2 (OPT_CMPR_COUNT) teremos uma redução de aproximadamente 53% (OPT_CMPR_PCTSAVE) na área utilizada  pelo mesmo.
Agora, vamos comprimir o índice e realizar ma nova validação do seu tamanho e gerar um novo trace da mesma consulta para verificar os ganhos obtidos.
SQL> alter index IXLAMIMTST rebuild compress 2;

Index altered.

SQL> select segment_name, sum(bytes/1024) from user_segments where segment_name='IXLAMIMTST' group by segment_name;

SEGMENT_NAME                                                                      SUM(BYTES/1024)
--------------------------------------------------------------------------------- ---------------
IXLAMIMTST                                                                                   2048


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05         22         89          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.05         22         63          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID LAMIMTST (cr=4 pr=0 pw=0 time=82 us cost=1 size=207 card=1)
         1          1          1   INDEX RANGE SCAN IXLAMIMTST (cr=3 pr=0 pw=0 time=53 us cost=1 size=0 card=1)(object id 268044528)

É possível verificar que nosso índice que inicialmente possuía um tamanho de 4096 kB após a compressão passou a ter 2048 kB. Uma redução de 50% no tamanho do mesmo.
Vale destacar também que nosso se observarmos a coluna disk do trace gerado, temos uma redução de aproximadamente 77% no I/O gerado pela consulta. 

Fonte:
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf
http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1177
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317
https://blogs.oracle.com/dbstorage/compressing-your-indexes:-index-key-compression-part-1

Proxima → Página inicial