Páginas

27 de jun. de 2013

Entendendo o SQL Performance Analyzer

Olá pessoal,

     Conforme prometido no artigo Obtendo a Certificação Oracle Database 11g: Performance Tuning, vou apresentar hoje o SQL Performance Analyzer (SQLPA), uma ótima ferramenta para analisar de forma rápida e eficiente, o impacto de mudanças que ocorreram no Banco de Dados (BD). Entre as principais mudanças que podemos analisar com o SQLPA, podemos citar:
         - Upgrades ou alterações na configuração de hardware, SO ou BD;
         - Alterações nos parâmetros de inicialização do BD;
         - Alterações de schema, tais como: criação de índices, tabelas particionadas e visões materializadas;
         - Atualizações de estatísticas do otimizador;
         - A criação ou alterações de SQL profiles.

Importante: Para usar o SQLPA, é necessário adquirir a Option Real Application Testing, que incluiu outras ferramentas e tem o custo aprox. de 25% da licença do Oracle Database Enterprise Edition.

     O SQLPA possui integração com SQL Tuning Sets (STS), SQL Tuning Advisor e SQL Plan Management: outras ferramentas e recursos, que permitem analisar, otimizar (de forma limitada)  e/ou evitar a regressão de instruções SQL; que podem ser executadas de forma manual ou automática. 

     O SQLPA permite analisar mudanças no plano de execução ou estatísticas de execução de instruções SQL, contidas, por exemplo, em um STS, e exibir após a análise, um relatório contendo indicadores do desempenho antes e depois da(s) alteração(ões), informando o impacto dela(s), indicando se ela(s) otimizou(aram) ou degradou(aram) a performance de cada instrução SQL contida no STS. Se ocorrer degradação (regressão) da performance após uma determinada mudança, a ferramenta poderá ainda, fornecer recomendações para melhorar o seu desempenho. Este trabalho também pode ser feito manualmente por um DBA ou Desenvolvedor, mas seria muito menos produtivo e eficiente analisar dezenas ou milhares de instruções SQL, por exemplo, de um sistema inteiro após a migração de um BD. O SQLPA automatiza e simplifica este trabalho!
  
Curiosidade: STS juntamente com a package DBMS_SQLTUNE, foram introduzidos no Oracle 10G como parte das funcionalidades de Automatic SQL TuningEu, particularmente, não sou adepto ao uso das ferramentas de Tuning Automáticas, pois além de terem custos adicionais de licenciamento (Option Tuning Pack), elas são muito limitadas (o aluno percebe isso facilmente nos treinamentos de SQL Tuning que eu ministro). Em todo caso, é melhor usá-las do que não fazer nada para melhorar o desempenho de suas instruções SQL.

     Veja abaixo, na Imagem 01, uma visão geral do SQLPA, contemplando todos os passos de execução e análise desta ferramenta: 
        - Captura das instruções SQL (Capture SQL);
        - Execução das instruções SQL antes da alteração (Execute SQL);
        - Alteração (Make Change) que poderá impactar nas instruções SQL;
        - Execução das instruções SQL depois da alteração (Execute SQL);
        - Geração do relatório que mostra os resultados comparando a execução antes e depois da alteração (Compare Perf).

Imagem 01 - Visão geral do SQLPA


     O SQLPA pode ser executado através de blocos PL/SQL, chamando a package DBMS_SQLPA, ou através do Enterprise Manager. Segue abaixo um roteiro passo-a-passo que mostrará como usar esta ferramenta:

PASSO-A-PASSO

Passo 1: Criando uma tabela para testes
     Crie uma tabela com o nome "my_objects" para ser utilizada em instruções SQL nos próximos passos:
            CREATE TABLE HR.my_objects AS SELECT * FROM all_objects;

Passo 2: Coletando estatísticas da tabela
     Atualize as estatísticas da tabela para gerar um bom plano de execução nos próximos passos:
            EXEC DBMS_STATS.gather_table_stats('HR', 'MY_OBJECTS', cascade => TRUE);

Passo 3: Executando instruções SQL antes da alteração
     Execute diversas instruções SQL que irão compor no próximo passo um STS:
            SELECT * FROM HR.my_objects WHERE object_id <= 100;
            SELECT object_name FROM HR.my_objects WHERE object_id = 100;
            SELECT * FROM HR.my_objects WHERE object_id <= 1000;
            SELECT object_name FROM HR.my_objects WHERE object_id = 1000;
            SELECT * FROM HR.my_objects WHERE object_id 
                        BETWEEN 100 AND 1000;

Passo 4: Criando um SQL Tuning SET (STS)
     Crie um STS, formado pelas instruções SQL executadas no passo anterior:

        a) Criando um STS (ainda vazio) com o nome spa_test_sqlset_MYOBJECTS:
             BEGIN
                 DBMS_SQLTUNE.create_sqlset(SQLSET_OWNER => 'HR',
                                sqlset_name => '
spa_test_sqlset_MYOBJECTS');
             
             END;


        b) Populando o STS com as instruções SQL recuperadas da SGA, executadas no passo anterior:
             DECLARE
                  v_cursor  DBMS_SQLTUNE.sqlset_cursor;
             BEGIN
               OPEN v_cursor FOR
                  SELECT VALUE(a)
                  FROM    TABLE(
                           DBMS_SQLTUNE.select_cursor_cache(
                             basic_filter   => 'sql_text LIKE ''%my_objects%''',
                             attribute_list => 'ALL')
                         ) a;
                                               
                DBMS_SQLTUNE.load_sqlset(sqlset_name  => 'spa_test_sqlset_MYOBJECTS',
                           SQLSET_OWNER => 'HR',
                           populate_cursor => v_cursor);
             END;
             /

        c) Verifique se o STS foi criado, através da consulta abaixo. Ela deverá retornar no mínimo as instruções SQL executadas no Passo 3, que agora estão contidas no STS:
             SELECT    sql_text, sqlset_name
             FROM      dba_sqlset_statements
             WHERE   UPPER(sqlset_name) = UPPER('spa_test_sqlset_MYOBJECTS')
             AND         SQLSET_OWNER = 'HR';

Resultado:
SQL_TEXT                                                                SQLSET_NAME                  
-------------------------------------------------------------------------------- ------------------------------
SELECT VALUE(A) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( BASIC_FILTER => 's spa_test_sqlset_MYOBJECTS
SELECT object_name FROM HR.my_objects WHERE object_id = 1000                     spa_test_sqlset_MYOBJECTS
SELECT object_name FROM HR.my_objects WHERE object_id = 100                       spa_test_sqlset_MYOBJECTS
SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, cast(NU           spa_test_sqlset_MYOBJECTS
SELECT * FROM HR.my_objects WHERE object_id <= 1000                                  spa_test_sqlset_MYOBJECTS
SELECT * FROM HR.my_objects WHERE object_id <= 100                                    spa_test_sqlset_MYOBJECTS
SELECT * FROM HR.my_objects WHERE object_id BETWEEN 100 AND 1000        spa_test_sqlset_MYOBJECTS
 7 linhas selecionadas 

Passo 5: Criando uma tarefa para o STS
     Crie uma tarefa para analisar as instruções SQL contidas no STS. Anote o nome da tarefa que é gerado automaticamente e que será impresso no final da execução do bloco PL/SQL abaixo:
             SET SERVEROUTPUT ON
             DECLARE
                   v_task VARCHAR2(64);  
             BEGIN  
                   v_task :=  DBMS_SQLPA.create_analysis_task(
                                sqlset_name =>  'spa_test_sqlset_MYOBJECTS'
                                sqlset_owner => 'HR'));
                   DBMS_OUTPUT.PUT_LINE('v_task: ' || v_task);
             END;

Resultado:
v_task: TAREFA_11940

Passo 6: Analisando o STS antes das alterações
     Execute a procedure DBMS_SQLPA.EXECUTE_ANALYSIS_TASK para executar uma análise da tarefa criada no passo anterior. Passe como argumentos p/ a procedure os seguintes valores: o nome da tarefa (TAREFA_11940) criada no passo anterior, o tipo de execução (test execute) e um nome qualquer de execução (before_change). 

     O tipo de execução pode ser 1 entre 4 valores:
           - test execute: para gerar os planos e estatísticas de execução + executar as instruções SQL;
           - explain plan: para gerar somente os planos de execução;
           - compare: para analisar e comparar 2 execuções distintas (compara 2 execuções do tipo test execute ou explain plan);
           - convert sqlset: para ler as estatísticas capturadas em um STS.

     Segue abaixo o bloco PL/SQL para chamar a procedure EXECUTE_ANALYSIS_TASK:
             BEGIN
               DBMS_SQLPA.execute_analysis_task(
                 task_name       => 'TAREFA_11940',
                 execution_type  => 'test execute',
                 execution_name  => 'before_change');
             END;
             /

Passo 7: Efetuando alterações
     Crie um índice na coluna que é utilizada no filtro das instruções SQL pertencentes ao STS, para posteriormente verificarmos o índice irá otimizar aquelas instruções SQL:
             CREATE INDEX HR.IX_myobjects_objectid ON HR.my_objects(object_id);
   
     Após a criação do índice, atualize as estatísticas da tabela e seus respectivos índices executando o comando abaixo:
             EXEC DBMS_STATS.gather_table_stats('HR', 'MY_OBJECTS', cascade => TRUE);

Passo 8: Analisando o STS depois das alterações
     Execute uma análise similar a do Passo 6, onde iremos analisar novamente a execução das instruções SQL do STS, porém com uma pequena diferença, agora existe um índice (criado no passo anterior) que será utilizado para otimizar as consultas, e consequentemente, mudar os seus planos de execução e reduzir o tempo total de execução delas. Essa nova análise terá um nome de execução diferente: after_change.
             BEGIN
               DBMS_SQLPA.execute_analysis_task(
                 task_name       => 'TAREFA_11940',
                 execution_type  => 'test execute',
                 execution_name  => 'after_change');
             END;             
             /

Passo 9: Analisando o STS depois das alterações
     Agora executaremos um dos passos chave de todo este processo: a comparação das tarefas de análise que foram executadas nos passos anteriores, ou seja, iremos comparar a análise da execução das instruções SQL antes da criação do índice, com a análise executada depois da criação do índice. Repare que agora, ao chamar a procedure DBMS_SQLPA.EXECUTE_ANALYSIS_TASK, estamos passando o valor compare performance para o parâmetro execution_type:
             BEGIN
               DBMS_SQLPA.execute_analysis_task(
                 task_name        => 'TAREFA_11940',
                 execution_type   => 'compare performance', 
                 execution_params => dbms_advisor.arglist(
                                       'execution_name1', 
                                       'before_change', 
                                       'execution_name2', 
                                       'after_change')
                 );
             END;
             /

Passo 10: Verificando o relatório da análise
     Chegamos no passo final! Agora é só verificar o relatório que foi gerado como resultado da comparação executada no passo anterior:
             SELECT DBMS_SQLPA.report_analysis_task('TAREFA_11940', 'TEXT', 'ALL') 
             FROM   dual;

Resultado:

General Information
---------------------------------------------------------------------------------------------
 Task Information:                              Workload Information:                                    
 ---------------------------------------------  ---------------------------------------------            
  Task Name    : TAREFA_3156         SQL Tuning Set Name        : spa_test_sqlset_MYOBJECTS  
  Task Owner   : SYS                            SQL Tuning Set Owner       : HR
  Description  :                                      Total SQL Statement Count  : 8                          

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name    : EXEC_3202              Started             : 06/27/2013 16:52:43  
  Execution Type     : COMPARE PERFORMANCE    Last Updated        : 06/27/2013 16:52:43  
  Description            :                        Global Time Limit   : UNLIMITED            
  Scope                      : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED               
  Status                     : COMPLETED              Number of Errors    : 0                    
  Number of Unsupported SQL  : 1                                                                 

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                         After Change Execution:                       
 ---------------------------------------------           --------------------------------------------- 
  Execution Name      : before_change       Execution Name      : after_change           
  Execution Type      : TEST EXECUTE     Execution Type      : TEST EXECUTE           
  Scope               : COMPREHENSIVE        Scope               : COMPREHENSIVE          
  Status              : COMPLETED                  Status              : COMPLETED              
  Started             : 06/27/2013 16:52:24         Started             : 06/27/2013 16:52:37    
  Last Updated        : 06/27/2013 16:52:27   Last Updated        : 06/27/2013 16:52:39    
  Global Time Limit   : UNLIMITED            Global Time Limit   : UNLIMITED              
  Per-SQL Time Limit  : UNUSED                Per-SQL Time Limit  : UNUSED                 
  Number of Errors    : 0                                 Number of Errors    : 0                      
                                                                                              
 ---------------------------------------------                                                
 Comparison Metric: ELAPSED_TIME                                                              
 ------------------                                                                           
 Workload Impact Threshold: 1%                                                                
 --------------------------                                                                   
 SQL Impact Threshold: 1%                                                                     
 ----------------------                                                                       

Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
 Overall Impact             :  18.9% 
 Improvement Impact  :  18.9% 
 Regression Impact     :  0%    

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count 
 Overall                     8                  5 
 Improved                 6                  5 
 Unchanged             1                  0 
 Unsupported          1                  0 

Top 7 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before | After  | on SQL | Change |
-----------------------------------------------------------------------------------------
|        25 | 41550mdvaw2nc |     3.75% |         1 |   8769 |     79 |  99.1% | y      |
|        24 | fmz885rhxxfps |     3.61% |         1 |   8916 |    550 | 93.83% | y      |
|        26 | 6hkyg0bsh35vf |     3.54% |         1 |   8653 |    469 | 94.58% | y      |
|        20 | bhnvv7mq2f9a5 |     2.99% |         1 |   6947 |     28 |  99.6% | y      |
|        22 | cqk3aa1txdzsg |     2.92% |         1 |   6796 |     27 |  99.6% | y      |
|        23 | dp1t951p09p2t |     2.08% |         1 | 105609 | 100804 |  4.55% | n      |
|        27 | 9gdj496fjh42a |     -.51% |         1 |  85761 |  86942 | -1.38% | n      |
-----------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

     Interpretando o relatório acima, ele nos indica que tivemos um ganho de performance geral (considerando todas as instruções SQL do STS) de 18.9% após a criação do índice. Através deste passo-a-passo deu para verificar que o SQLPA é uma ferramenta muito boa para analisar o impacto de qualquer alteração no Banco de Dados. Se o impacto tivesse sido negativo, a ferramenta poderia ainda, interagir com outras ferramentas de Automatic SQL Tuning e dar algumas recomendações para tentar melhorar o desempenho das instruções SQL que sofreram regressão de performance!

     Para limpar tudo o que fizemos no roteiro passo-a-passo, execute as instruções SQL abaixo:
       EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('TAREFA_11940');
       EXEC DBMS_SQLTUNE.DELETE_SQLSET(
                    sqlset_name => 'spa_test_sqlset_MYOBJECTS', SQLSET_OWNER => 'HR');
       DROP TABLE HR.my_objects;


Bom pessoal, espero que tenham gostado! Por hoje é só! 

[]s


Referências:

2 comentários: