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:
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!
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 ...
Parabéns Eduardo, mais um artigo esclarecedor!!!
Olá Sidney,
Obrigado pela visita e volte sempre ;-)
Abraços
Legatti
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
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
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.
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
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.
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
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.
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
Muito bom seu post Eduardo, obrigado por compartilhar.
Olá Valter,
Obrigado pela visita ;-)
Abraços
Legatti
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?
Olá Hudson,
Pelo que sei, ainda não tem suporte a bind variables :-(
Abraços
Legatti
Postar um comentário