terça-feira, 29 de abril de 2008

Obtendo informações dos relacionamentos existentes (Foreign Keys) entre tabelas no Oracle ...

Olá,
Dando prosseguimento ao que foi escrito no artigo de Fevereiro sobre os estados das constraints, neste artigo irei mostrar como obter as informações dos relacionamentos existentes entre as tabelas no schema do usuário. Sabemos que uma restrição de PRIMARY KEY é uma coluna ou um conjunto de colunas que identifica exclusivamente cada linha de uma tabela e que uma restrição de FOREIGN KEY, ou restrição de integridade referencial, designa uma coluna ou combinação de colunas com uma chave estrangeira e estabelece um relacionamento entre uma chave primária ou uma chave exclusiva na mesma tabela ou em uma tabela diferente. Como obter do dicionário de dados as informações referentes aos relacionamentos entre estas tabelas? A resposta está nas views de dicionário de dados *_CONSTRAINTS e *_CONS_COLUMNS, onde o * [asterisco] deverá ser substituído por USER_ (objetos de propriedade do usuário), ALL_ (objetos acessíveis ao usuário) ou DBA_ (objetos restritos para usuários com atribuição DBA).

Abaixo irei demonstrar um pequeno exemplo prático de como obter algumas dessas informações via consulta SQL:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Seg Abr 28 13:31:40 2008

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

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user scott identified by tiger
  2  default tablespace users quota unlimited on users;

Usuário criado.

SQL> grant connect,resource to scott;

Concessão bem-sucedida.

SQL> connect scott/tiger
Conectado.

SQL> create table tabela_A (id number constraint pk_a primary key);

Tabela criada.

SQL> create table tabela_B (id2 number constraint fk_b_a references tabela_a);

Tabela criada.

SQL> create table tabela_C (id3 number constraint fk_c_a references tabela_a);

Tabela criada.

-- Obtendo informações de quais tabelas fazem referência à tabela_A
SQL> select
  2     r.owner,
  3     r.table_name,
  4     r.constraint_name
  5  from
  6     user_constraints r,
  7     user_constraints o
  8  where
  9     r.r_owner = o.owner and
 10     r.r_constraint_name = o.constraint_name and
 11     o.constraint_type in ('P','U') and
 12     r.constraint_type = 'R' and
 13     o.table_name = 'TABELA_A';

OWNER                TABLE_NAME               CONSTRAINT_NAME
-------------------- ------------------------ --------------------------
SCOTT                TABELA_B                 FK_B_A
SCOTT                TABELA_C                 FK_C_A

-- Obtendo informações das tabelas, colunas e seus relacionamentos
SQL> select
  2     a.table_name||' ('||
  3     rtrim(max(decode(c.position,1,c.column_name))||','||
  4     max(decode(c.position,2,c.column_name))||','||
  5     max(decode(c.position,3,c.column_name))||','||
  6     max(decode(c.position,4,c.column_name)),',')||') referencia '||
  7     b.table_name||' ('||
  8     rtrim(max(decode(d.position,1,d.column_name))||','||
  9     max(decode(d.position,2,d.column_name))||','||
 10     max(decode(d.position,3,d.column_name))||','||
 11     max(decode(d.position,4,d.column_name)),',')||')' relacionamentos
 12  from
 13     user_constraints  a,
 14     user_constraints  b,
 15     user_cons_columns c,
 16     user_cons_columns d
 17  where
 18     a.r_constraint_name=b.constraint_name and
 19     a.constraint_name=c.constraint_name and
 20     b.constraint_name=d.constraint_name and
 21     a.constraint_type='R' and
 22     b.constraint_type in ('P', 'U')
 23  group by a.table_name, b.table_name
 24  order by 1;

RELACIONAMENTOS
------------------------------------------------------------
TABELA_B (ID2) referencia TABELA_A (ID)
TABELA_C (ID3) referencia TABELA_A (ID)
 
Em resumo, para obter informações de quaisquer tipos de restrições (constraints) existentes no schema, as views de dicionário de dados abaixo deverão ser consultadas:

SQL> desc user_constraints
Nome                          Nulo?    Tipo
----------------------------- -------- --------------------
OWNER                         NOT NULL VARCHAR2(30)
CONSTRAINT_NAME               NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE                        VARCHAR2(1)
TABLE_NAME                    NOT NULL VARCHAR2(30)
SEARCH_CONDITION                       LONG
R_OWNER                                VARCHAR2(30)
R_CONSTRAINT_NAME                      VARCHAR2(30)
DELETE_RULE                            VARCHAR2(9)
STATUS                                 VARCHAR2(8)
DEFERRABLE                             VARCHAR2(14)
DEFERRED                               VARCHAR2(9)
VALIDATED                              VARCHAR2(13)
GENERATED                              VARCHAR2(14)
BAD                                    VARCHAR2(3)
RELY                                   VARCHAR2(4)
LAST_CHANGE                            DATE
INDEX_OWNER                            VARCHAR2(30)
INDEX_NAME                             VARCHAR2(30)
INVALID                                VARCHAR2(7)
VIEW_RELATED                           VARCHAR2(14) 


SQL> desc user_cons_columns
Nome                          Nulo?    Tipo
----------------------------- -------- --------------------
OWNER                         NOT NULL VARCHAR2(30)
CONSTRAINT_NAME               NOT NULL VARCHAR2(30)
TABLE_NAME                    NOT NULL VARCHAR2(30)
COLUMN_NAME                            VARCHAR2(4000)
POSITION                               NUMBER
 

quarta-feira, 23 de abril de 2008

Abordando o SQL Tuning Advisor no Oracle - DBMS_SQLTUNE

Olá,
À partir do Oracle 10g, os supervisores (advisors) são ferramentas especializadas que ajudam o DBA a analisar o desempenho do banco de dados, identificar possíveis problemas e gargalos e ajustar os vários componentes do banco de dados. Alguns dos supervisores, como o supervisor ADDM e o supervisor de Segmento por exemplo, executam tarefas em seu banco de dados. Toda vez que um supervisor executa uma tarefa, ele executa sua análise e fornece os resultados dessa análise. No Oracle Enterprise Manager Database Control, os supervisores disponíveis no Supervisor Central são listados abaixo:
  • ADDM fornece acesso aos recursos Automatic Database Diagnostic Monitor do Banco de Dados Oracle. Supervisor de Ajuste SQL Permite acesso a Top SQL, Conjuntos de Ajuste SQL, Snapshots e Linhas de Base, sendo que todos ajudam no ajuste de código SQL.
  • Supervisor de Acesso SQL sugere maneiras de melhorar consultas SQL e a forma como essas consultas acessam dados subjacentes por meio do uso de índices e views materializadas. O desempenho das consultas SQL muitas vezes pode ser melhorado, criando-se estruturas adicionais, como Índices e Views Materializadas, que ajudam na recuperação de dados. O Supervisor de Acesso SQL também avalia as instruções SQL em uma carga de trabalho e pode sugerir índices e views materializadas que melhorarão o desempenho da carga de trabalho como um todo.
  • Supervisor de Memória permite definir parâmetros de memória SGA e PGA é um grupo de estruturas de memória compartilhadas que contém dados e informações de controle para um sistema de banco de dados Oracle. A PGA é um buffer de memória que contém dados e informações de controle para um processo servidor.
  • Supervisor MTTR fornece acesso à página Configurar Definições de Recuperação, em que é possível ajustar a definição MTTR (Mean Time to Recover, Tempo Médio de Recuperação) para o banco de dados.
  • Gerenciamento de Undo permite definir detalhes de configuração para tablespace e retenção e permite acesso ao Supervisor de Undo que avalia o impacto de uma nova definição de retenção de undo.
  • Supervisor de Desempenho avalia os objetos em relação ao uso do bloco e do espaço e determina se é possível obter espaço no tablespace compactando segmentos.
  • Supervisor de Ajuste SQL (SQL Tuning Advisor) é utilizado para analisar instruções SQL individuais e fazer recomendações para melhorar seu desempenho.
Embora o Oracle EM Database Control possua uma interface que facilita o acesso ao SQL Tuning Advisor, às vezes executar um código PL/SQL via linha de comando também pode ser uma boa opção para o DBA e até mesmo para um desenvolvedor. Portanto, neste artigo abordarei especificamente o Supervisor de Ajuste SQL e como proceder para obter recomendações do otimizador utilizando o pacote DBMS_SQLTUNE via SQL*PLUS, já que o Oracle Express Edition não fornece uma interface de acesso a esta feature como o OEM Database Console oferece. Em resumo, utilizarei o Oracle Express Edititon para geração das tarefas de otimização de um SQL específico. Vamos então a um exemplo prático:

-- Irei preparar o ambiente de demonstração criando um schema de teste
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Qui Abr 17 22:22:37 2008

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

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user legatti identified by manager quota unlimited on users;

Usuário criado.

SQL> grant connect,resource,advisor to legatti;

Concessão bem-sucedida.

-- Irei conectar com o usuário criado anteriormente e criar duas tabelas
SQL> connect legatti/manager
Conectado.

-- Criação da tabela EMP (Empregados)
SQL> create table emp (
  2    empno    number(4) not null,
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2),
 10    constraint pk_emp primary key (empno)
 11  );

Tabela criada.

-- Criação da tabela DEPT (Departamentos)
SQL> create table dept (
  2    deptno number(2) not null,
  3    dname  varchar2(14),
  4    loc    varchar2(13)
  5  );

Tabela criada.

-- Carregando dados para a tabela EMP
SQL> insert into emp values (7369,'SMITH','CLERK',7902,'17/12/1980',800,NULL,20);

1 linha criada.

SQL> insert into emp values (7499,'ALLEN','SALESMAN',7698,'20/02/1981',1600,300,30);

1 linha criada.

SQL> insert into emp values (7521,'WARD','SALESMAN',7698,'20/02/1981',1250,500,30);

1 linha criada.

SQL> insert into emp values (7566,'JONES','MANAGER',7839,'20/02/1981',2975,NULL,20);

1 linha criada.

SQL>insert into emp values (7654,'MARTIN','SALESMAN',7698,'20/02/1981',1250,1400,30);

1 linha criada.

SQL> insert into emp values (7698,'BLAKE','MANAGER',7839,'20/02/1981',2850,NULL,30);

1 linha criada.

SQL> insert into emp values (7782,'CLARK','MANAGER',7839,'20/02/1981',2450,NULL,10);

1 linha criada.

SQL> insert into emp values (7788,'SCOTT','ANALYST',7566,'20/02/1981',3000,NULL,20);

1 linha criada.

SQL> insert into emp values (7839,'KING','PRESIDENT',NULL,'20/02/1981',5000,NULL,10);

1 linha criada.

SQL> insert into emp values (7844,'TURNER','SALESMAN',7698,'20/02/1981',1500,0,30);

1 linha criada.

SQL> insert into emp values (7876,'ADAMS','CLERK',7788,'20/02/1981',1100,NULL,20);

1 linha criada.

SQL> insert into emp values (7900,'JAMES','CLERK',7698,'20/02/1981',950,NULL,30);

1 linha criada.

SQL> insert into emp values (7902,'FORD','ANALYST',7566,'20/02/1981',3000,NULL,20);

1 linha criada.

SQL> insert into emp values (7934,'MILLER','CLERK',7782,'20/02/1981',1300,NULL,10);

1 linha criada.

-- Carregando dados para a tabela DEPT
SQL> insert into dept values (10,'ACCOUNTING','NEW YORK');

1 linha criada.

SQL> insert into dept values (20,'RESEARCH','DALLAS');

1 linha criada.

SQL> insert into dept values (30,'SALES','CHICAGO');

1 linha criada.

SQL> insert into dept values (40,'OPERATIONS','BOSTON');

1 linha criada.

SQL> commit;

Commit concluído.

-- SIMULAÇÃO 01 --

-- Irei simular a ausência de estatísticas para a tabela EMP
SQL> exec dbms_stats.delete_table_stats('LEGATTI','EMP');

Procedimento PL/SQL concluído com sucesso.

-- Irei simular a ausência de estatísticas para a tabela DEPT
SQL> exec dbms_stats.delete_table_stats('LEGATTI','DEPT');

Procedimento PL/SQL concluído com sucesso.

-- Irei criar uma tarefa de tuning SQL
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2   l_sql               VARCHAR2(500);
  3   l_sql_tune_task_id  VARCHAR2(100);
  4  BEGIN
  5   l_sql := 'SELECT emp.*,dept.* '||
  6            'FROM emp '||
  7            'INNER JOIN dept ON emp.deptno = dept.deptno '||
  8            'CROSS JOIN dept e '||
  9            'WHERE '||
 10            '(NVL(empno,''0'') = :empno) and (dept.deptno = :deptno) '||
 11            'and (job LIKE ''MA_%'' or job LIKE ''CL_%'' or job = ''EAD'') '||
 12            'ORDER BY emp.job,emp.deptno';
 13   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
 14                      sql_text    => l_sql,
 15                      bind_list   => sql_binds(anydata.ConvertNumber(4),
 16                                               anydata.ConvertNumber(2)),
 17                      user_name   => 'LEGATTI',
 18                      scope       => DBMS_SQLTUNE.scope_comprehensive,
 19                      time_limit  => 60,
 20                      task_name   => 'sql_emp_dept_tuning_task',
 21                      description => 'Tuning de SQL para a query contendo EMP e DEPT.');
 22   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 23  END;
 24  /
l_sql_tune_task_id: sql_emp_dept_tuning_task

Procedimento PL/SQL concluído com sucesso.

-- Irei checar o status da tarefa de tuning SQL
SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
       271 sql_emp_dept_tuning_task       INITIAL

-- Irei executar a tarefa de tuning SQL
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_emp_dept_tuning_task');

Procedimento PL/SQL concluído com sucesso.

-- Irei checar novamente o status da tarefa para ver se a mesma já foi finalizada
SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
       271 sql_emp_dept_tuning_task       COMPLETED

-- Irei executar o procedimento report() para visualizar as recomendações
SQL> set long 10000;
SQL> set longchunksize 1000
SQL> set pagesize 10000
SQL> set linesize 100

SQL> select dbms_sqltune.report_tuning_task('sql_emp_dept_tuning_task')
  2  as recomendacoes from dual;

RECOMENDACOES
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_emp_dept_tuning_task
Tuning Task Owner                 : LEGATTI
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 04/17/2008 19:07:44
Completed at                      : 04/17/2008 19:07:45
Number of Statistic Findings      : 2
Number of SQL Profile Findings    : 1
Number of SQL Restructure Findings: 2

-------------------------------------------------------------------------------
Schema Name: LEGATTI
SQL ID     : brvwtnz5ztms9
SQL Text   : SELECT emp.*,dept.* FROM emp INNER JOIN dept ON emp.deptno =
             dept.deptno CROSS JOIN dept e WHERE (NVL(empno,'0') = :empno)
             and (dept.deptno = :deptno) and (job LIKE 'MA_%' or job LIKE
             'CL_%' or job = 'EAD') ORDER BY emp.job,emp.deptno

-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  A tabela "LEGATTI"."DEPT" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'LEGATTI', tabname =>
            'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

2- Statistics Finding
---------------------
  A tabela "LEGATTI"."EMP" e seus índices não foram analisados.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela e seus
    índices.
    execute dbms_stats.gather_table_stats(ownname => 'LEGATTI', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

  Rationale
  ---------
    O otimizador de execução requer estatísticas para a tabela e seus índices
    ao selecionar um plano de execução adequado.

3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  Foi encontrado um plano de execução potencialmente melhor para esta
  instrução.

  Recommendation (estimated benefit<=10%)
  ---------------------------------------
  - Considere a aceitação do perfil SQL recomendado.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'sql_emp_dept_tuning_task', replace => TRUE);

4- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  O predicado NVL("EMP"."EMPNO",0)=:B1 usado no ID de linha 4 do plano de
  execução contém uma expressão na coluna indexada "EMPNO". Essa expressão
  impede o otimizador de selecionar índices na tabela "LEGATTI"."EMP".

  Recommendation
  --------------
  - Reescreva o predicado em um formato equivalente para tirar proveito de
    índices. Como alternativa, crie um índice baseado na expressão.

  Rationale
  ---------
    O otimizador não pode usar um índice quando o predicado é uma condição de
    desigualdade ou quando há uma expressão ou uma conversão de tipo de dados
    implícita na coluna indexada.

5- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Foi encontrada uma operação de produto cartesiano na linha de ID 2 do plano
  de execução.

  Recommendation
  --------------
  - Considere a remoção da tabela ou view desconectada dessa instrução ou
    adicione uma condição de instrução que faça referência a ela.

  Rationale
  ---------
    Deve-se evitar um produto cartesiano sempre que possível porque ele é uma
    operação de alto custo que pode produzir um grande volume de dados.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3377505029

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     3 |   147 |    11  (19)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     3 |   147 |    11  (19)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     3 |   147 |    10  (10)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    49 |     7  (15)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL | EMP  |     1 |    31 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | DEPT |     1 |    18 |     3   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     4 |       |     8  (25)| 00:00:01 |
|   7 |     TABLE ACCESS FULL | DEPT |     4 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   4 - filter(NVL("EMP"."EMPNO",0)=:EMPNO AND "EMP"."DEPTNO"=:DEPTNO
              AND ("EMP"."JOB"='EAD' OR "EMP"."JOB" LIKE 'MA_%' OR "EMP"."JOB" LIKE
              'CL_%'))
   5 - filter("DEPT"."DEPTNO"=:DEPTNO)

2- Using SQL Profile
--------------------
Plan hash value: 3641068790

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     3 |   147 |    11  (19)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     3 |   147 |    11  (19)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     3 |   147 |    10  (10)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    49 |     7  (15)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL | DEPT |     1 |    18 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | EMP  |     1 |    31 |     3   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     4 |       |     8  (25)| 00:00:01 |
|   7 |     TABLE ACCESS FULL | DEPT |     4 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   4 - filter("DEPT"."DEPTNO"=:DEPTNO)
   5 - filter(NVL("EMP"."EMPNO",0)=:EMPNO AND "EMP"."DEPTNO"=:DEPTNO
              AND ("EMP"."JOB"='EAD' OR "EMP"."JOB" LIKE 'MA_%' OR "EMP"."JOB" LIKE
              'CL_%'))

-------------------------------------------------------------------------------

-- Irei dropar a tarefa de tuning SQL
SQL> exec dbms_sqltune.drop_tuning_task (task_name => 'sql_emp_dept_tuning_task');

Procedimento PL/SQL concluído com sucesso.

-- SIMULAÇÃO 02 --

-- Irei dropar a chave primária da tabela EMP para ver se haverá alguma recomendação
SQL> alter table emp drop primary key;

Tabela alterada.

-- Irei criar uma nova tarefa de tuning SQL
SQL> DECLARE
  2    l_sql               VARCHAR2(500);
  3    l_sql_tune_task_id  VARCHAR2(100);
  4  BEGIN
  5   l_sql := 'SELECT a.*, b.* ' ||
  6            'FROM emp a INNER JOIN dept b ON b.deptno = a.deptno ' ||
  7            'WHERE empno = :empno';
  8
  9   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
 10                      sql_text    => l_sql,
 11                      bind_list   => sql_binds(anydata.ConvertNumber(4)),
 12                      user_name   => 'LEGATTI',
 13                      scope       => DBMS_SQLTUNE.scope_comprehensive,
 14                      time_limit  => 60,
 15                      task_name   => 'sql_emp_dept_tuning_task',
 16                      description => 'Tuning de SQL para a query contendo EMP e DEPT.');
 17   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 18  END;
 19  /
l_sql_tune_task_id: sql_emp_dept_tuning_task

Procedimento PL/SQL concluído com sucesso.

-- Irei checar o status da tarefa de tuning SQL
SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
       272 sql_emp_dept_tuning_task       INITIAL


-- Irei executar a tarefa de tuning SQL
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_emp_dept_tuning_task');

Procedimento PL/SQL concluído com sucesso.

-- Irei checar novamente o status da tarefa para ver se a mesma já foi finalizada
SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
       272 sql_emp_dept_tuning_task       COMPLETED

-- Irei verificar se existe alguma recomendação
SQL> select dbms_sqltune.report_tuning_task('sql_emp_dept_tuning_task')
  2  as recomendacoes from dual;

RECOMENDACOES
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_emp_dept_tuning_task
Tuning Task Owner                 : LEGATTI
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 04/17/2008 19:14:26
Completed at                      : 04/17/2008 19:14:27
Number of Statistic Findings      : 2
Number of Index Findings          : 1

-------------------------------------------------------------------------------
Schema Name: LEGATTI
SQL ID     : 05n88kxuc6raw
SQL Text   : SELECT a.*, b.* FROM emp a INNER JOIN dept b ON b.deptno =
             a.deptno WHERE empno = :empno

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  A tabela "LEGATTI"."DEPT" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'LEGATTI', tabname =>
            'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

2- Statistics Finding
---------------------
  A tabela "LEGATTI"."EMP" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'LEGATTI', tabname =>
            'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');


  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

3- Index Finding (see explain plans section below)
--------------------------------------------------
  O plano de execução dessa instrução pode ser melhorado com a criação de um
  ou mais índices.

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Considere a execução do Supervisor de Acesso para aumentar o projeto de
    esquema físico ou a criação do índice recomendado.
    create index LEGATTI.IDX$$_01120001 on LEGATTI.EMP('EMPNO');

  Rationale
  ---------
    Criar os índices recomendados melhora significativamente o plano de
    execução dessa instrução. No entanto, talvez seja preferível executar o
    "Supervisor de Acesso" utilizando uma carga de trabalho SQL representativa
    em oposição a uma única instrução. Isso permitirá a obtenção de
    recomendações de índice abrangentes que levam em consideração o overhead
    de manutenção de índice e um consumo de espaço adicional.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   117 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |   117 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("B"."DEPTNO"="A"."DEPTNO")
   2 - filter("A"."EMPNO"=:EMPNO)

2- Using New Indices
--------------------
Plan hash value: 3550937623

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |   117 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    87 |     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                |     1 |   117 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT           |     4 |   120 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX$$_01120001 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - filter("B"."DEPTNO"="A"."DEPTNO")
   4 - access("A"."EMPNO"=:EMPNO)

-------------------------------------------------------------------------------

SQL> exec dbms_sqltune.drop_tuning_task (task_name => 'sql_emp_dept_tuning_task');

Procedimento PL/SQL concluído com sucesso. 

Uma outra forma de capturar as recomendações fornecidas é executar a instrução SQL abaixo:

  select 
     a.execution_end, 
     b.type, 
     b.impact,
     d.rank,
     d.type,
     'Message           : '||b.message MESSAGE,
     'Command to correct: '||c.command COMMAND,
     'Action Message    : '||c.message ACTION_MESSAGE
  from 
     user_advisor_tasks a,
     user_advisor_findings b,
     user_advisor_actions c, 
     user_advisor_recommendations d
  where 
     a.task_id=b.task_id
     and b.task_id=d.task_id and b.finding_id=d.finding_id
     and a.task_id=c.task_id and d.rec_id=c.rec_id
     and a.task_name like 'sql%' and a.status='COMPLETED'
    order by b.impact, d.rank;




Outras procedures que podem ser executadas pelo DBMS_SQLTUNE:

-- Interromper e continuar a execução da tarefa
SQL> exec dbms_sqltune.interrupt_tuning_task (task_name => 'sql_emp_dept_tuning_task');
SQL> exec dbms_sqltune.resume_tuning_task (task_name => 'sql_emp_dept_tuning_task');

-- Cancelar a execução da tarefa
SQL> exec dbms_sqltune.cancel_tuning_task (task_name => 'sql_emp_dept_tuning_task');

-- Resetar a execução da tarefa permitindo a sua reinicialização
SQL> exec dbms_sqltune.reset_tuning_task (task_name => 'sql_emp_dept_tuning_task');

Usando o dicionário de dados Oracle:

 
As principais views de dicionário de dados utilizadas para recuperar informações do ADDM são:
  • DBA_ADVISOR_TASKS: contém informações específicas sobre cada tarefa. A execução de uma análise ADDM é uma tarefa isolada (outras tarefas incluem execução dos vários consultores que serão apresentados em artigos posteriores);
  • DBA_ADVISOR_RECOMMENDATIONS: esta view fornece as recomendações associadas com a execução específica de uma tarefa ADDM;
  • DBA_ADVISOR_FINDINGS: esta view fornece os laudos associadas com uma execução específica de uma tarefa ADDM;
  • DBA_ADVISOR_RATIONALE: esta view fornece as razões associadas com as recomendações específicas do ADDM.

sexta-feira, 18 de abril de 2008

É possível saber o número de linhas por tabela dentro de um arquivo dump de exportação do Oracle?

Olá,

Na verdade, esta questão foi feita no Forum de Discussão da Oracle por um usuário que exportou um schema de banco de dados utilizando o utilitário de exportação (exp), mas não gerou um arquivo de log de saída de tela (utilizando a cláusula LOG). Então como fazer para verificar esta informação sem ter que importar o arquivo novamente para o banco de dados? A boa notícia é que o arquivo dump armazena estas informações possibilitando assim verificar a quantidade de registros exportados de cada tabela. Embora possa ser possível visualizar o conteúdo do arquivo de exportação usando a cláusula SHOW=Y ao importá-lo, o mesmo não irá mostrar as informações de número de registros exportados por tabela. A solução então é criar um arquivo de índice (texto) utilizando a cláusula INDEXFILE como demonstrado no exemplo abaixo:

-- Geração do arquivo dump de exportação
C:\>exp scott/tiger file=emp.dmp tables=emp statistics=none

Export: Release 10.1.0.2.0 - Production on Sex Abr 18 08:32:16 2008

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

Conectado a: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Exportação executada no conjunto de caracteres de WE8PC850  e no conjunto de caracteres
de AL16UTF16 NCHAR o servidor usa WE8ISO8859P1 conjunto de caracteres (conversão de
conjunto de caracteres possível)

Sobre exportar tabelas especificadas ... via Caminho Convencional ...
. . exportando tabela                         EMP        498 linhas exportadas
Exportação encerrada com sucesso, sem advertências.


-- Geração do arquivo de índice
C:\>imp scott/tiger file=emp.dmp indexfile=sql.txt

Import: Release 10.1.0.2.0 - Production on Sex Abr 18 08:33:07 2008

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

Conectado a: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Arquivo de exportação criado por EXPORT:V10.01.00 via caminho convencional
importação realizada nos conjuntos de caracteres WE8PC850 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres WE8ISO8859P1 (conversão de
charset possível) . . saltando a tabela "EMP"

Importação encerrada com sucesso, sem advertências.

Verificando o arquivo de índice gerado ...

C:\>findstr "rows" sql.txt
REM  ... 498 rows

quinta-feira, 10 de abril de 2008

Selecionando registros entre intervalos de linhas no Oracle com o ROWNUM

Olá,

Neste artigo, irei demonstrar como fazer uso da pseudo-coluna ROWNUM para obter os dados desejados dentro de um intervalo específico de um resultado de uma consulta SQL.


A técnica consiste em usar a estrutura SQL abaixo:

SELECT [[colunas]]
  FROM (SELECT [[colunas]], rownum rn
          FROM (SELECT [[colunas]]
                  FROM [[sua query]]
                  ORDER BY [[colunas]]) 
         WHERE rownum <= [[max row]])
  WHERE rn >= [[min row]]

onde
 

[[min row]] e [[max row]] são os números das linhas do intervalo desejado. Para demonstrar esta técnica, irei criar uma tabela de teste como demonstrado a seguir:

SQL> create table teste as select level id,
  2  substr(dbms_random.string('U',10),1,10) valor
  3  from dual connect by level <= 10;

Tabela criada.

SQL> select * from teste order by valor;

        ID VALOR
---------- ----------
         5 EMXMUNCOSZ
         9 GBXLYTGPPI
         2 MQIFNZHVZC
         6 NZPBZIGWXM
         8 SMHWOOIMQF
         3 TVKBNGOYVW
         4 TXJTLIPJGT
        10 VMPFTOKWKK
         1 VZIYRALBQQ
         7 WFZZJYYYPT

10 linhas selecionadas.


De acordo com o resultado acima, o que deverá ser feito para que possamos obter a quarta (4ª) e a quinta (5ª) linha da tabela (ID 6 e 8)?

SQL> SELECT id,valor,rownum
  2    FROM (SELECT id,valor
  3            FROM (SELECT id,valor
  4                  FROM teste
  5                  ORDER by valor));

        ID VALOR          ROWNUM
---------- ---------- ----------
         5 EMXMUNCOSZ          1
         9 GBXLYTGPPI          2
         2 MQIFNZHVZC          3
         6 NZPBZIGWXM          4
         8 SMHWOOIMQF          5
         3 TVKBNGOYVW          6
         4 TXJTLIPJGT          7
        10 VMPFTOKWKK          8
         1 VZIYRALBQQ          9
         7 WFZZJYYYPT         10

10 linhas selecionadas.


Então, de acordo com o resultado acima, sabemos que as linhas de ID 6 e 8 são as linhas que queremos obter. A seguir, irei aplicar a técnica como demonstrado abaixo:

SQL> set verify off
SQL> SELECT id,valor
  2        FROM (SELECT id,valor, rownum rn
  3                FROM (SELECT *
  4                       FROM (select id,valor from teste)
  5                       ORDER BY valor)
  6               WHERE rownum <= &maxrow)
  7        WHERE rn >= &minrow;
Informe o valor para maxrow: 5
Informe o valor para minrow: 4

        ID VALOR
---------- ----------
         6 NZPBZIGWXM
         8 SMHWOOIMQF

2 linhas selecionadas.