Olá,
Desde o 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. No artigo de Abril/2008 eu demonstrei através de exemplos práticos como fazer para o Oracle analisar instruções SQL específicas e sugerir recomendações para melhorar seu desempenho. Foi usado o Supervisor de Ajuste SQL (SQL Tuning Advisor) através da package DBMS_SQLTUNE. Agora irei abordar novamente o uso do DBMS_SQLTUNE só que, em vez de passar como parâmetro uma instrução SQL para ser analisada, irei passar como parâmetro o SQL_ID de uma instrução SQL que já tinha sido executada anteriormente.
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Seg Ago 8 09:01:22 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> var id number;
SQL> exec :id := 2500;
Procedimento PL/SQL concluído com sucesso.
SQL> select count(*) from t1 where id=:id;
COUNT(*)
----------
8
1 linha selecionada.
Após a execução da instrução SQL acima, irei procurar através da view dinâmica de desempenho V$SQLAREA qual o SQL_ID associado à instrução SQL executada.
SQL> select sql_id,parsing_schema_name
2 from v$sqlarea
3 where sql_fulltext like 'select count(*) from t1 where id=:id%';
SQL_ID PARSING_SCHEMA_NAME
------------- -------------------------
1x6vk95kg44wb SCOTT
1 linha selecionada.Uma vez encontrada o SQL_ID, irei criar uma tarefa de SQL Tuning passando o SQL_ID 1x6vk95kg44wb e executar a mesma conforme demonstrado a seguir. Para essa tarefa irei limitar a sua execução até o máximo de 5 minutos (300 seg).
SQL> DECLARE
2 l_sql_tune_task_id VARCHAR2(100);
3 BEGIN
4 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
5 sql_id => '1x6vk95kg44wb',
6 scope => DBMS_SQLTUNE.scope_comprehensive,
7 time_limit => 300,
8 task_name => 'SCOTT_1x6vk95kg44wb',
9 description => 'SCOTT Tuning task for statement 1x6vk95kg44wb.');
10 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
11 END;
12 /
l_sql_tune_task_id: SCOTT_1x6vk95kg44wb
Procedimento PL/SQL concluído com sucesso.
SQL> select task_id,task_name,status from user_advisor_log;
TASK_ID TASK_NAME STATUS
---------- ------------------------------ -----------
33150 SCOTT_1x6vk95kg44wb INITIAL
1 linha selecionada.
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'SCOTT_1x6vk95kg44wb');
Procedimento PL/SQL concluído com sucesso.
Pronto. Após a execução será necessário apenas analisar e verificar as recomendações que foram encontradas pelo otimizador. Vale a pena salientar que além de ser mostrado a instrução SQL, também será mostrado o valor da bind variable usada na instrução SQL.
SQL> set long 1000000;
SQL> set longchunksize 100000
SQL> set pagesize 10000
SQL> set linesize 1000
SQL> select dbms_sqltune.report_tuning_task('SCOTT_1x6vk95kg44wb') as recomendacoes from dual;
RECOMENDACOES
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SCOTT_1x6vk95kg44wb
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status : COMPLETED
Started at : 08/08/2016 09:13:45
Completed at : 08/08/2016 09:13:56
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 1x6vk95kg44wb
SQL Text : select count(*) from t1 where id=:id
Bind Variables :
1 - (NUMBER):2500
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
A tabela "SCOTT"."T1" não foi analisada.
Recommendation
--------------
- Considere a coleta de estatísticas do otimizador para esta tabela
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', 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- 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: 99.93%)
------------------------------------------
- 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 SCOTT.IDX$$_817E0001 on SCOTT.T1("ID");
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: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1503 (3)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T1 | 73 | 949 | 1503 (3)| 00:00:19 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:ID)
2- Using New Indices
--------------------
Plan hash value: 809122696
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX$$_817E0001 | 8 | 104 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:ID)
-------------------------------------------------------------------------------
1 linha selecionada.
Nenhum comentário:
Postar um comentário