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:
- 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 Tuning. Eu, 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:
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:
-------------------------------------------------------------------------------- ------------------------------
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'));
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
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:
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:
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:
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:
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;
[]s
Referências:
Referências:
Ótimo post, Fábio. Muito legal esta ferramenta.
ResponderExcluirAbraço,
Camilla
Obrigado Camilla!
Excluir