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


quarta-feira, 23 de abril de 2008

Abordando o SQL Tuning Advisor no Oracle - DBMS_SQLTUNE

Por Eduardo Legatti

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.



16 comentários:

Rafael Yera Barchi disse...

Olá Eduardo.
Aqui estou eu mais uma vez babando no blog. Parabéns pelo sensacional trabalho apresentado aqui!
Sobre esse post, tenho algumas dúvidas.
Com o comando "DBMS_STATS", não devo utilizar o "analyze... compute statistics"?
Gostaria de saber também como fica a tablespace "SYSAUX" com a execução de ambos os comandos ("DBMS_STATS" e "analyze... compute statistics"). No meu banco, essa tabela ficou totalmente inchada quando passei a utilizar o segundo comando.
Sobre o DBMS_STATS, gostaria de obter maiores detalhes sobre como aplicá-lo em minha base.
Desde já obrigado pela atenção.
Abraço!

Eduardo Legatti disse...

Olá Rafael,

Obrigado pelo comentário. Bom, não acredito que a coleta de estatísticas realizada tanto pacote DBMS_STATS quanto pelo comando ANALYZE tenham inchado o tablespace SYSAUX, mesmo porque as informações de estatísticas das tabelas e índices ficam armazenadas nas tabelas base do dicionário de dados TAB$, SEG$, IND$ que estão no tablespace SYSTEM. Talvez o AWR (Automatic Workload Repository) tenha gerado tais informações no tablespace SYSAUX? Na verdade, o tablespace SYSAUX introduzido no Oracle 10g, é um tablespace auxiliar para o tablesace SYSTEM utilizado para armazenar dados de diversos componentes do Oracle que exigem tablespaces próprios ou usavam o tablespace SYSTEM em versões anteriores do Oracle. Esses componentes incluem o Enterprise Manager Repository antigamente no tablespace OEM_REPOSITORY (se não me engano), assim como componentes do Oracle Text, Oracle Spatial, LogMiner, informações de estatísticas do AWR, etc...

Portanto, acredito que você deva dar uma olhada na view V$SYSAUX_OCCUPANTS para saber os atuais ocupantes na tablespace SYSAUX que estão "inchando" o tablespace ... Caso seja necessário, você poderá mover o ocupante para outra tablespace utilizando a procedure identificada na coluna MOVE_PROCEDURE.

Em relação ao pacote DBMS_STATS e o comando ANALYZE, a Oracle recomenda utilizar o DBMS_STATS ao invés do ANALYZE desde a versão 8i (pelo fato de a mesma ter sido aprimorada) ... mas nada impede você de utilizar o comando ANALYZE para tarefas não relacionadas a estatísticas, como VALIDATE STRUCTURE ou LIST CHAINED ROWS.

Mesmo hoje ainda costumo utilizar o comando ANALYZE ... ;-) mas acredito que você achará muito artigo na internet sobre DBMS_STATS vs. ANALYZE ....

Para maiores informações, vale a pena dar uma olhada na documentação oficial em http://tahiti.oracle.com

Abraços e até mais ...

Sidney França disse...

Parabéns Eduardo, mais um artigo esclarecedor!!!

Eduardo Legatti disse...

Olá Sidney,

Obrigado pela visita e volte sempre ;-)

Abraços

Legatti

Alexander Burbello disse...

Olá Eduardo,

Como sempre seus posts excelentes!! Parabéns :D:D

Porém, tenho uma duvida com relação aos Profiles, se eu não puder alterar nada na query (App fechada) nem em outras opções como estatísticas (supondo que já está ok), mas identifiquei que com um hint eu consigo ter uma ótima performance.
Como faço pra que dada tal query (ou sql_id), ao inves de executar esse X, execute o X mais o meu hint ??

Desde já agradeço esforço.
Alex

Eduardo Legatti disse...

Olá Alex,

Na package DBMS_SQLTUNE tem uma procedure chamada IMPORT_SQL_PROFILE que acredito ser muito mal documentada, mas que pode te ajudar. Dá uma pesquisada sobre ela.

Uma outra coisa que eu acho que poderia ser muita mais bacana, seria você utilizar a package DBMS_ADVANCED_REWRITE. Bom, nunca usei ela, mas nesta package você escolhe uma instrução SQL de origem (SOURCE_STMT) e uma instrução SQL de destino (DESTINATION_STMT), aí sempre que um SQL executado no banco for igual ao SQL de origem, ele será trocado pelo SQL de destino que foi definido por você, ou seja, você reescreveu o SQL, e a aplicação nem ficou sabendo disso ;-) Bom, acho interessante você dar uma pesquisa nisso também.

Abraços e até mais ...

Legatti

Anônimo disse...

Olá Legatti, parabéns pelo o artigo.

Estou com uma duvida, sobre o plano de execução de tabelas particionadas.
Tenho a seguinte situação: Na query, é informado no filtro um between de datas, que é o campo chave do particionamento, é informado, por exemplo, 01/09/2014 a 19/11/2014 e com isso o Oracle faz um partition range iterator, e nas colunas partition_start e partition_stop aparece exatamente quais são as partições que serão acessadas, porém ao informar um dia a mais, por exemplo, 01/09/2014 a 20/11/2014, o Oracle não informa exatamente quais são as partições que serão acessadas, ou seja, ao invés de aparecer os valores nas colunas partition_start e partition_stop, aparece a palavra Key, o Oracle descobrirá as partições no momento de execução, porém isso, está causando uma grande lentidão, você sabe me dizer, se há algum hint para que o Oracle informe as partições antes da execução? que apareça no plano de execução, os valores reais nas colunas partition_start e partition_stop?
Muito obrigada.

Eduardo Legatti disse...

Olá Anônimo,

Não conheço nenhum hint que faça isso. Realmente, acho que não tem jeito. Quando o otimizador não sabe informar as partições é exatamente porque só em tempo de execução (compile time) é que as mesmas serão conhecidas.

Isso costuma ocorrer quando se utiliza "bind variables" na passagem dos parâmetros. Agora, mesmo passando as datas como literal o otimizador não estar conseguindo informar no plano de execução as partições que serão acessadas, então pode ser o caso de você tentar habilitar algum trace na sessão para identificar o que pode estar ocorrendo.

Se está ocorrendo lentidão, possivelmente o plano de execução escolhido não foi o mais adequado e ele não esteja fazendo pruning de partições. É possível que esteja ocorrendo um PARTITION RANGE ALL. Tente avaliar as possibilidades como atualização de estatísticas, criação de histogramas, índices.

Pode ser que esta consulta SQL talvez precise de algum ajuste.

Neste caso tem que analisar com calma mesmo ;-)

Abraços e até mais...


Legatti

Anônimo disse...

Mais uma vez obrigada Legatti!
É estou desconfiada que o problema é estatísticas, pois as estatísticas locais estão atualizadas, porém as estatísticas globais não estão, estão totalmente desatualizadas.
Legatti, qual é o percentual minimo para atualizar as estatísticas? Obrigada.

Eduardo Legatti disse...

Olá Anônimo,

O Oracle coleta estatísticas automaticamente durante a janela de manutenção que entre 22:00 até 06:00. As estatísticas são coletadas para aqueles objetos que não possuem ainda qualquer estatística ou que estejam com as estatísticas defasadas (stale).

As estatísticas de uma tabela se tornam defasadas quando mais de 10% das linhas foram modificadas. Por exemplo, se em uma tabela de 1 milhão de registros, mais de 100 mil foram modificados (INSERT, UPDATE, DELETE) então as estatísticas dessa tabela e seus índices serão atualizados automaticamente durante a janela de manutenção através do job GATHER_STATS_JOB.

De vez em quando eu prefiro eu mesmo coletar as estatísticas das tabelas de forma manual ;-)

Abraços

Legatti

Anônimo disse...

Olá Legatti, ótimo artigo, parabéns!
Tenho uma duvida: A partir do Oracle 11gR2, a Oracle criou o uso do hint parallel auto, qual é a diferença do hint parallel onde é especificado um inteiro e parallel auto? Qual é o impacto e ganho da utilização do hint parallel auto? Só conheço o hint parallel onde é especificado um inteiro.
Muito obrigada.

Eduardo Legatti disse...

Olá Anônimo,

Basicamente, a diferença é que quando eu informo um valor inteiro, estou dizendo ao Oracle para utilizar o grau de paralelismo (DOP - Degree of Paralellism) que foi informado.

exemplo:

select /*+ parallel (sales,2) */ * from sales;
alter table sales parallel 2;

No AUTO, o Oracle vai determinar qual o melhor grau de paralelismo para a tabela ou instrução em questão de acordo com o hardware disponível, entre outros fatores.

Particularmente, nunca usei essa opção. ;-(

Sobre impacto e ganho de utilização, acredito que somente realizando testes no ambiente em questão e um estudo mais profundo sobre o assunto para poder tirar alguma conclusão. ;-)

Abraços

Legatti

Valter disse...

Muito bom seu post Eduardo, obrigado por compartilhar.

Eduardo Legatti disse...

Olá Valter,

Obrigado pela visita ;-)

Abraços

Legatti

Hudson disse...

Olá Eduardo,

Estou estudando sobre dbms_advanced_rewrite que mencionou acima, é uma solução que pode me ajudar em muitos casos aqui, pois não tenho acesso para reescrever as consultas, porém quando o sql usa variáveis bind ele não aceita, sabe algo a respeito?

Eduardo Legatti disse...

Olá Hudson,

Pelo que sei, ainda não tem suporte a bind variables :-(

Abraços

Legatti

Postagens populares