Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


segunda-feira, 29 de janeiro de 2018

SQL*Plus 12c - SET FEEDBACK ONLY

Por Eduardo Legatti

Olá,

No SQL*Plus do Oracle 12c, especificamente na variável de sistema FEEDBACK foi introduzida uma novo parâmetro na qual podemos executar uma instrução SELECT em uma tabela de forma que apenas o resultado do número de linhas seja retornado. Neste caso, seria o equivalente a executar um SELECT COUNT(*) na tabela.



C:\>sqlplus scott/tiger@ORCL11

SQL*Plus: Release 12.2.0.1.0 Production on Seg Jan 29 12:43:35 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 select rownum rn from dual connect by rownum <=10;

SQL> commit;

Commit concluído.

SQL> show feedback;
FEEDBACK ON for 6 or more rows

SQL> select rowid,id from t1;

ROWID                      ID
------------------ ----------
AAOifoAAIAAAPhPAAA          1
AAOifoAAIAAAPhPAAB          2
AAOifoAAIAAAPhPAAC          3
AAOifoAAIAAAPhPAAD          4
AAOifoAAIAAAPhPAAE          5
AAOifoAAIAAAPhPAAF          6
AAOifoAAIAAAPhPAAG          7
AAOifoAAIAAAPhPAAH          8
AAOifoAAIAAAPhPAAI          9
AAOifoAAIAAAPhPAAJ         10

10 linhas selecionadas.

SQL> set feedback ONLY;
SQL> show feedback;
feedback ONLY

SQL> select rowid,id from t1;

10 linhas selecionadas.

quinta-feira, 28 de dezembro de 2017

Enquete - 2017 (Agosto/Dezembro)

Por Eduardo Legatti

Olá,

Sobre a enquete realizada entre os meses de Agosto e Dezembro/2017, segue o resultado abaixo.
Estarei analisando as possibilidades!!!

Feliz Ano Novo!!!!

Legatti

terça-feira, 21 de novembro de 2017

Otimização: Cursor Sharing, Histogramas, Cursores no Oracle

Por Eduardo Legatti

Olá,

O objetivo deste artigo é demonstrar através de um exemplo prático como um histograma pode ajudar o otimizador do Oracle a encontrar um plano de execução melhor para instruções SQL que acessam tabelas que possuem registros distribuídos de forma não uniforme (skewed distribution) em uma ou mais colunas. Algumas variáveis podem influenciar como o Oracle enxerga uma instrução SQL, tais como o valor atual do parâmetro cursor_sharing, Configurações de NLS da sessão, se a instrução SQL utiliza literal ou bind variables, além de o otimizador fazer uso do Adaptive Cursosr Sharing de forma a avaliar se existe um plano de execução melhor de acordo com os valores das bind variables utilizadas. Essas variações serão tratadas em artigos futuros. Abaixo foi criada uma tabela T1 com cerca de 17 milhões de registros. É possível notar que a coluna ID possui 3 valores distintos (1, 2 e 3) e que os mesmos estão distribuídos de forma não uniforme. Vale a pena salientar que a coluna ID está indexada. Irei coletar estatísticas da tabela T1 sem coletar histogramas para as colunas conforme abaixo.

SQL> select id,count(*) from t1 group by id order by 1;

        ID   COUNT(*)
---------- ----------
         1   16777216
         2     262144
         3         64

SQL> exec dbms_stats.gather_table_stats(
 2   ownname=>'SCOTT',
 3   tabname=>'T1',
 4   cascade => true,
 5   METHOD_OPT => 'for all columns size 1');

Procedimento PL/SQL concluído com sucesso.

Abaixo, podemos ver que o parâmetro cursor_sharing está configurado para EXACT (default) o que significa que somente instruções SQL com textos idênticos serão compartilhadas para reutilização.

SQL> show parameter cursor_sharing;

NAME                         TYPE          VALUE
--------------------------- ------------- ---------------------
cursor_sharing              string        EXACT

Após coletar estatísticas da tabela (sem histogramas), irei executar três consultas abaixo. Vale a pena salientar que as instruções se diferenciam textualmente pelo valor da coluna ID que é passada na cláusula WHERE, ou seja, elas não são idênticas.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Consultando a view dinâmica de desempenho V$SQLAREA que mostra os PARENT CURSORS de todas as instruções SQL executadas, podemos ver que cada instrução SQL possui um SQL_ID/HASH_VALUE diferentes, exatamente porque os literais passados na cláusula WHERE na coluna ID são diferentes. Podemos ver também que a coluna VERSION_COUNT mostra o valor 1  o que significa que cada PARENT CURSOR criou apenas um CHILD CURSOR para cada consulta. É importante lembrar que cada PARENT CURSOR (V$SQLAREA) deverá sempre ter no mínimo 1 CHILD CURSOR (V$SQL).

SQL> SELECT sql_id,
  2         hash_value,
  3         version_count,
  4         executions,
  5         parsing_schema_name,
  6         module,
  7         last_active_time,
  8         is_bind_sensitive,
  9         is_bind_aware,
 10         sql_profile,
 11         sql_text
 12    FROM V$SQLAREA
 13   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 14         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE VERSION_COUNT EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025             1          1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Consultando a view dinâmica de desempenho V$SQL que mostra os CHILD CURSORS de todas as instruções SQL executadas, podemos ver que o Oracle gerou o mesmo plano de execução (PLAN_HASH_VALUE) para as 3 consultas.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 0000000092188610      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 000000009F85B2D8      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 00000000944BC930      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Irei gerar um explain plan da consulta abaixo de forma que verificar qual plano de execução foi gerado para as consultas SQL.

SQL> explain plan for select count(object_name) from t1 where id=1;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 | 55703   (1)| 00:11:09 |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5679K|    54M| 55703   (1)| 00:11:09 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=1)

14 linhas selecionadas.

Pelo plano de execução mostrado acima, podemos ver que o otimizador resolveu fazer um Full Table Scan na tabela T1. Para o valor de ID=1 está ótimo, porque este valor está em 98% dos registros da tabela. No entanto, esse plano de execução é horrível para os valores 2 e 3 que teria melhor performance se fizesse uso do índice criado na coluna ID. A questão é saber porque o Oracle não utilizou o índice? A resposta é porque o Oracle não sabe a distribuição dos valores na coluna ID. Por exemplo, ele não sabe que 98% dos valores são 1 e que os outros 2% são 1 e 2. Pelo resultado da consulta abaixo é possível perceber que o valor da coluna DENSITY é 33%, ou seja, o Oracle sabe que existem 3 valores distintos na coluna ID (NUM_DISTINCT=3), no entanto, ele enxerga de forma uniforme a distribuição dos valores na coluna, ou seja, cerca de 5,6 milhões de linhas para cada ID.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               1              3
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               0              1

Agora irei criar um histograma para a coluna ID de forma que o otimizador possa enxergar a não uniformidade dos valores armazenadas na coluna.

SQL> exec dbms_stats.gather_table_stats(
  2  ownname=>'SCOTT',
  3  tabname=>'T1',
  4  cascade => true,
  5  METHOD_OPT => 'for columns ID');

Procedimento PL/SQL concluído com sucesso.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5262              1
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5343              3
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5342              2

Após executada a coleta de estatísticas incluindo a criação de histograma (FREQUENCY), irei executar novamente as 3 consultas SQL.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Fazendo novamente a consulta na view V$SQL, é possível observar que o plano de execução (PLAN_HASH_VALUE) das consultas que utilizam os IDs 2 e 3 mudaram.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 000000009F8DED10      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 0000000098978A30      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:22 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 000000009F8CA890      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:14 N N            select count(object_name) from t1 where id=1

Para finalizar, irei gerar um novo explain plan da consulta que utiliza o ID=3 de forma a verificar qual plano de execução foi gerado pelo otimizador do Oracle.

SQL> explain plan for select count(object_name) from t1 where id=3;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1284813898

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    10 |    52   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |  3189 | 31890 |    52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_ID |  3189 |       |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"=3)

15 linhas selecionadas.

Pronto. Pelo resultado acima podemos ver que o otimizador escolheu o acessar os dados através do índice IDX_T1_ID, ou seja, o histograma foi responsável por ajudar o otimizador a verificar que os valores da coluna ID não estavam com a distribuição uniforme e encontrou um plano de acesso mais performático para a instrução SQL com ID=3.


quinta-feira, 26 de outubro de 2017

Obtendo o tamanho dos segmentos por schemas de banco de dados: (Tabelas, Índices, LOBs)

Por Eduardo Legatti

Olá,

No artigo de Maio/2011 eu compartilhei uma instrução SQL que retorna o tamanho de todos os segmentos (Tabelas, Índices, LOBs, etc.) pertencentes a um um schema (o schema atual), através das views de dicionário de dados USER_*. O objetivo dessa instrução SQL é mostrar não só o tamanho de uma tabela como também o tamanho dos objetos dependentes dela como índices e LOBs. Agora irei compartilhar uma consulta SQL que utiliza as views DBA_* de forma a retornar o tamanho dos segmentos de tabelas, índices e LOBs por schema de banco de dados, conforme a seguir.
 
SQL> SELECT owner,
  2         data_mb,
  3         indx_mb,
  4         lob_mb,
  5         total_mb
  6      FROM (SELECT data.owner,
  7                   NVL(data_mb,0) data_mb,
  8                   NVL(indx_mb,0) indx_mb,
  9                   NVL(lob_mb,0) lob_mb,
 10                   NVL(data_mb,0) + NVL(indx_mb,0) + NVL(lob_mb,0) total_mb
 11              FROM (  SELECT owner,
 12                             ROUND(SUM(data_mb),2) data_mb
 13                        FROM (SELECT owner, data_mb
 14                                FROM (SELECT a.owner,
 15                                             b.bytes/1024/1024 AS data_mb
 16                                        FROM dba_tables a, dba_segments b
 17                                       WHERE a.owner = b.owner and a.table_name = b.segment_name))
 18                    GROUP BY owner) data,
 19                   (  SELECT a.owner,
 20                             ROUND(SUM(b.bytes/1024/1024),2) AS indx_mb
 21                        FROM dba_indexes a, dba_segments b
 22                       WHERE a.owner = b.owner and a.index_name = b.segment_name
 23                    GROUP BY a.owner) indx,
 24                   (  SELECT a.owner,
 25                             ROUND(SUM(b.bytes/1024/1024),2) AS lob_mb
 26                        FROM dba_lobs a, dba_segments b
 27                       WHERE a.owner = b.owner and a.segment_name = b.segment_name
 28                    GROUP BY a.owner) lob
 29             WHERE
 30             data.owner = indx.owner(+)
 31             AND data.owner = lob.owner(+))
 32  WHERE owner in ('SCHEMA01',
 33                  'SCHEMA02',
 34                  'SCHEMA03',
 35                  'SCHEMA04',
 36                  'SCHEMA05',
 37                  'SCHEMA06',
 38                  'SCHEMA07')
 39  ORDER BY owner;

OWNER                       DATA_MB    INDX_MB     LOB_MB   TOTAL_MB
------------------------ ---------- ---------- ---------- ----------
SCHEMA01                   16069.38   58428.25  174381.44  248879.07
SCHEMA02                      11618   43081.31    9064.94   63764.25
SCHEMA03                   93944.63     529311  206722.38  829978.01
SCHEMA04                       78.5     107.63      98.56     284.69
SCHEMA05                    2814.25     9761.5    14573.5   27149.25
SCHEMA06                    3211.88   13447.94     555.75   17215.57
SCHEMA07                    9777.44   41685.13  258100.44  309563.01

7 rows selected.

segunda-feira, 25 de setembro de 2017

Analisando o RMAN no que se refere à varredura dos arquivos de dados em backups incrementais

Por Eduardo Legatti


Olá,

Com o lançamento do recurso de backups incrementais  à partir do RMAN Oracle 9i, é possível ter uma economia grande de espaço no que se refere ao tamanhos dos backups já que um backup incremental é gerado à partir dos blocos alterados desde o último backup base, seja ele cumulativo ou diferencial.
 
Mas, independente do tipo de backup incremental realizado, seja ele cumulativo ou diferencial, uma maneira de melhorar o desempenho dos mesmos é ativar o rastreamento de alterações de blocos (Block Change Tracking). Em um backup incremental tradicional, o RMAN tem que inspecionar cada bloco do arquivo de dados no qual será feito o backup para verificar se o mesmo foi alterado desde o último backup efetuado. Dependendo do tamanho do banco de dados, isso poderá levar muito tempo. Portanto, habilitando o BCT o mesmo irá registrar em um arquivo especial os blocos que mudaram e, durante a realização do backup incremental NIVEL-1, apenas estes blocos serão lidos, ou seja, o RMAN não precisará varrer todos os blocos do arquivo de dados.

Vale a pena salientar que o BCT é uma feature do Oracle Enterprise Edition, ou seja, a versão Standard não possui este recurso.
 
Na imagem abaixo é possível observar backups incrementais NIVEL-0 ocorrendo nos dias 10/09 e 17/09. A linha azul mostra os dados lidos para os backup sets e a linha vermelha mostra esses mesmos dados  já com a compressão utilizada pelo RMAN. A linha verde mostra o tempo gasto na realização do backup. Nos dias de semana é possível perceber uma diminuição do tamanho e do tempo dos backups, pelo fato de estar sendo realizado o backup incremental NIVEL-1.


A imagem baixo mostra eventos de backups incrementais em um Oracle Standard Edition.O importante a se notar é a linha azul se mantém igual durante a execução dos backups, independente se o backup é do NIVEL-0 ou NIVEL-1.




Segue abaixo o gráfico mostrando os dados em uma janela de tempo menor.




Por fim, posso concluir que as informações de input_bytes e output_bytes mostradas pelo RMAN através da view  V$RMAN_BACKUP_JOB_DETAILS estão atreladas ao que o RMAN está lendo no momento da varredura dos arquivos de dados, e não dos blocos que apenas sofreram modificações como eu imaginava.

segunda-feira, 21 de agosto de 2017

Resolvendo o erro ORA-08104 com a procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN ao criar um índice ONLINE

Por Eduardo Legatti

Olá,

Durante a criação de um índice ONLINE em um banco de dados Oracle, ocorreu um problema de conectividade fazendo com que o Oracle emitisse o erro ORA-08104 ao retomar a atividade de rebuild do índice. Não era possível dropar ou recriar o índice.

SQL> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;

ORA-03113: end-of-file on communication channel

SQL> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;
alter index idx_emp_name rebuild online
*
ERRO na linha 1:
ORA-08104: este objeto de índice 248352 está sendo construído ou reconstruído on-line

SQL> alter index idx_emp_name rebuild online;
alter index idx_emp_name rebuild online
*
ERRO na linha 1:
ORA-08104: este objeto de índice 248352 está sendo construído ou reconstruído on-line

Para resolver o problema, utilizei a procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN encontrada no site psoug.org conforme demonstrado abaixo.


Segue abaixo a execução da procedure.

SQL> set timing on
SQL> DECLARE
  2   isClean BOOLEAN;
  3  BEGIN
  4    isClean := FALSE;
  5    WHILE isClean=FALSE
  6    LOOP
  7      isClean := dbms_repair.online_index_clean(
  8      dbms_repair.all_index_id, dbms_repair.lock_wait);
  9      dbms_lock.sleep(10);
 10    END LOOP;
 11  END;
 12  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:00.01

sql> create index idx_emp_name on emp (emp_name) tablespace indx_tbs_01 online;

Índice criado.

Decorrido: 00:00:03.08

No mais, este procedimento também está disponível no documento Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1] no My Oracle Support (Metalink).

sábado, 29 de julho de 2017

LogMiner - Analisando o conteúdo de um ARCHIVED REDO LOG através da package DBMS_LOGMNR

Por Eduardo Legatti

Olá,

Quando precisamos visualizar o conteúdo dos arquivos de redo log, utilizamos um recurso presente no banco de dados Oracle chamado LogMiner. O objetivo deste artigo é apresentar de forma simples como podemos visualizar o conteúdo de um ou mais arquivos de redo log arquivados através da package DBMS_LOGMNR.

Para facilitar a visualização física dos archived logs gerados, irei alterar o destino da criação dos mesmos conforme abaixo.

SQL> alter system set log_archive_dest_1='LOCATION=/oradata/archivelog';

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/archivelog
Oldest online log sequence     49
Next log sequence to archive   51
Current log sequence           51

De acordo com a documentação, existem dois tipos de logs sumplementares (supplemental logging) que podem ser habilitados no banco de dados:

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables.

Por enquanto não existe nenhum tipo de log suplementar ativado para os arquivos de redo log conforme demonstrado abaixo.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

O próximo passo é simular algumas operações no banco de dados conforme a seguir.

SQL> create table scott.emp (id number, data date);

Table created.

SQL> insert into scott.emp values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (2,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (4,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (5,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (6,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

A seguir irei habilitar o log suplementar mínimo (Minimal Supplemental Logging) que não causa nenhuma overhead e realizar mais algumas operações no banco de dados.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

SQL> insert into scott.emp values (7,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (8,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into scott.emp values (9,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (10,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

Após a execução dos comandos acima, os seguintes arquivos de redo log arquivados abaixo foram gerados.

[oracle@linux1 /]$ ls -lh /oradata/archivelog/
total 7,0M
-rw-r----- 1 oracle oinstall 2,9M Jul 29 12:55 1_51_804786092.dbf
-rw-r----- 1 oracle oinstall 1,9M Jul 29 13:26 1_52_804786092.dbf
-rw-r----- 1 oracle oinstall 1,0K Jul 29 13:26 1_53_804786092.dbf
-rw-r----- 1 oracle oinstall 2,2M Jul 29 14:16 1_54_804786092.dbf
-rw-r----- 1 oracle oinstall 111K Jul 29 14:24 1_55_804786092.dbf

SQL> select name,sequence#,first_time,next_time,completion_time
  2  from v$archived_log
  3  where name like '%1_5__804786092.dbf%';

NAME                                    SEQUENCE# FIRST_TIME          NEXT_TIME           COMPLETION_TIME
--------------------------------------- ---------- ------------------- ------------------- -------------------
/oradata/archivelog/1_51_804786092.dbf          51 29/07/2017 12:01:48 29/07/2017 12:55:52 29/07/2017 12:55:53
/oradata/archivelog/1_52_804786092.dbf          52 29/07/2017 12:55:52 29/07/2017 13:26:18 29/07/2017 13:26:19
/oradata/archivelog/1_53_804786092.dbf          53 29/07/2017 13:26:18 29/07/2017 13:26:26 29/07/2017 13:26:27
/oradata/archivelog/1_54_804786092.dbf          54 29/07/2017 13:26:26 29/07/2017 14:16:33 29/07/2017 14:16:33
/oradata/archivelog/1_55_804786092.dbf          55 29/07/2017 14:16:33 29/07/2017 14:24:57 29/07/2017 14:24:57

Como demonstrado acima, foi verificado que foram gerados arquivos de redo log arquivados da sequencia 51 à 55. Portanto, irei analisar o conteúdo dos mesmos com a package DBMS_LOGMNR conforme demonstrado abaixo.

SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_51_804786092.dbf',OPTIONS => DBMS_LOGMNR.NEW);
  3   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_52_804786092.dbf');
  4   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_53_804786092.dbf');
  5   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_54_804786092.dbf');
  6   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_55_804786092.dbf');
  7   DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
  8  END;
  9  /

PL/SQL procedure successfully completed.

Depois de adicionar os arquivos de redo log arquivados acima, irei visualizar o conteúdo dos mesmos através da view V$LOGMNR_CONTENTS.

SQL> SELECT rbasqn,
  2         timestamp,
  3         operation,
  4         seg_owner,
  5         table_name,
  6         table_space,
  7         username,
  8         os_username,
  9         machine_name,
 10         session#,
 11         serial#,
 12         session_info,
 13         sql_redo,
 14         sql_undo
 15  FROM v$logmnr_contents
 16  WHERE seg_owner IN ('SCOTT')
 17  ORDER BY timestamp;

    RBASQN TIMESTAMP           OPERATION      SEG_OWNER    TABLE_NAME   TABLE_SPACE  USERNAME   OS_USERNAME   MACHINE_NAME        SESSION#    SERIAL# SESSION_INFO                                                                                                                                                                      SQL_REDO                                                                                                      SQL_UNDO
---------- ------------------- -------------- ------------ ------------ ------------ ---------- ------------- ------------------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
        51 29/07/2017 12:11:31 DDL            SCOTT        EMP                       UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           create table scott.emp (id number, data date);
        51 29/07/2017 12:12:23 INSERT         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           insert into "SCOTT"."EMP"("ID","DATA") values ('1',TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '1' and "DATA" = TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAA';
        51 29/07/2017 12:35:19 DELETE         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAE';
        54 29/07/2017 14:15:37 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('7',TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '7' and "DATA" = TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAF';
        54 29/07/2017 14:16:19 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('8',TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '8' and "DATA" = TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAG';
        55 29/07/2017 14:22:06 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('9',TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '9' and "DATA" = TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:22:08 DELETE         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:24:42 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('10',TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss')); delete from "SCOTT"."EMP" where "ID" = '10' and "DATA" = TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';

8 linhas selecionadas.

Pelo resultado acima é possível perceber que as instruções realizadas antes de habilitar o log suplementar mínimo não estão completas ou ausentes e que somente após habilitar o log suplementar mínimo é que as informações e instruções SQL executadas foram logadas de forma mais completa.

Por fim, apra desabilitar o recurso LogMiner, basta apenas executar o comando abaixo.

SQL> exec DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

Postagens populares