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


segunda-feira, 8 de agosto de 2016

Abordando a package DBMS_SQLTUNE para recomendar melhorias em SQL IDs específicos (SQL Tuning Advisor)

Por Eduardo Legatti

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.

Google+

Nenhum comentário:

Postagens populares