Analisando a performance de queries com o SQL Perfomance Analyzer

O SQL Performance Analyzer (SPA) é uma ferramenta presente na option Oracle Real Application Testing (RAT) que permite comparar o desempenho de um conjunto de instruções SQL antes e depois de uma mudança. É a ferramenta ideal para prever como os SQLs de um banco se comportam em um ambiente diferente, seja uma nova versão do banco, um ambiente com mais recursos computacionais ou simplesmente uma estrutura física diferente, com novos índices e tabelas particionadas.

Este post demonstra um dos vários casos de uso onde o SPA pode ser utilizado para validar o ganho (ou regressão) de performance após alterações no banco de dados.

O cenário

Recentemente, durante uma migração de MySQL para Oracle, esbarrei numa situação onde a aplicação, após apontar para o Oracle, começou a ter um tempo de resposta alto, e precisamos realizar o rollback da operação. Após o rollback, foi necessária uma análise dos relatórios ASH e AWR da base, já que o principal workload foi capturado durante o tempo em que a aplicação apontou para o banco Oracle.

Com essa análise, diversas melhorias foram encontradas, como particionamento de tabelas e a introdução de novos índices. O problema agora era como validar se de fato as melhorias encontradas seriam suficientes e como transmitir essa segurança para os envolvidos na migração, principalmente para o time de aplicação. E aqui foi onde o SPA caiu como uma luva.

Como funciona o SQL Perfomance Analyzer?

O fluxo do SPA segue os seguintes passos:

1. Captura do workload que será analisado, o armazenando em um SQL Tuning Set (STS);
2. Criação de uma tarefa do SPA;
3. Execução do workload no ambiente sem as alterações (ou utilização dos planos contidos no SQL Tuning Set, caso tenha sido gerado a partir do AWR, por exemplo). Este é o cenário antes das alterações;
4. Realização das alterações, como particionamento ou indexação;
5. Execução do workload no ambiente após as alterações;
6. Execução de uma tarefa de comparação entre os cenários antes e depois das alterações;
7. Geração do relatório, que irá apontar os ganhos de performance e regressões para análise;

Também é possível gerar o SQL Tuning Set em um ambiente de origem e executá-lo em outro ambiente, e para isso, o SQL Tuning Set precisa ser exportado do ambiente de origem e importado no ambiente de destino.

Abaixo, uma imagem que representa esse fluxo do SQL Perfomance Analyzer:

Fonte: Oracle

Criação da task

Antes de criar a task do SQL Perfomance Analyzer, é necessário ter o SQL Tuning Set com o workload que desejamos analisar. No meu caso, como o workload foi capturado pelos snapshots do AWR, utilizei eles para fazer a carga no SQL Tuning Set, filtrando pelos schemas que a aplicação utilizava:

SQL> exec DBMS_SQLTUNE.CREATE_SQLSET('STS_SPA', 'SYS');

PL/SQL procedure successfully completed.

SQL> DECLARE
    cur sys_refcursor;
BEGIN
    open cur for
    select value(p) from table(dbms_sqltune.select_workload_repository(
        begin_snap => 32557,
        end_snap => 32559,
        basic_filter => 'parsing_schema_name in (''APP_SCHEMA1'',''APP_SCHEMA2'')')) p;
        dbms_sqltune.load_sqlset('STS_SPA', cur);
    close cur;
END;
/

PL/SQL procedure successfully completed.

Com o SQL Tuning Set criado, passamos a usar a package DBMS_SQLPA para criar a task do SQL Perfomance Analyzer, passando o STS, e o owner e o nome da task por parâmetro :

SQL> DECLARE
 task VARCHAR2(100);
BEGIN
 task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sqlset_name => 'STS_SPA', 
  sqlset_owner => 'SYS', 
  task_name => 'SPA_TASK'
  );
END;
/

PL/SQL procedure successfully completed.

O próximo passo é criar a tarefa de análise do período pré melhorias:

SQL> BEGIN 
    dbms_sqlpa.execute_analysis_task(
        task_name => 'SPA_TASK',
        execution_type => 'convert sqlset',
        execution_name => 'MADRUGADA',
        execution_params => DBMS_ADVISOR.ARGLIST('sqlset_name', 'STS_SPA', 'sqlset_owner', 'SYS')
    );
END;
/

PL/SQL procedure successfully completed.

Nessa fase, usamos o valor convert sqlset no parâmetro execution_type para indicar ao SPA que o SQL Tuning Set já possui os dados de execução do workload, e estaremos usando eles para a análise. Para esse período pré melhorias dei o nome de “MADRUGADA” e repassei os dados do STS para a task.

A próxima etapa aqui, foi executar a tarefa de análise do período pós melhorias, uma vez que as alterações já haviam sido feitas no ambiente:

SQL> BEGIN
    dbms_sqlpa.execute_analysis_task(
        task_name => 'SPA_TASK',
        execution_type => 'test execute',
        execution_name => 'ATUAL'
    );
END;
/

PL/SQL procedure successfully completed.

Aqui o valor usado para execution_type foi test execute porque os SQLs presentes no SQL Tuning Set precisam ser executados contra o ambiente onde as melhorias foram aplicadas. Para essa execução dei o nome de “ATUAL”. Nesse momento os SQLs começam a ser executados no ambiente, o que pode demorar, dependendo do tamanho do workload.

Vale ressaltar que diferente do Database Replay, o SQL Performance Analyzer não roda o workload exato que foi executado na janela de snapshots que está sendo usada, mas roda cada SQL separadamente para realizar a comparação entre os dois períodos.

Após a execução da tarefa pós melhorias, realizamos a comparação entre os períodos, utilizando o execution_type igual a compare:

BEGIN
    dbms_sqlpa.execute_analysis_task(
        task_name => 'SPA_TASK',
        execution_type => 'compare',
        execution_params => DBMS_ADVISOR.ARGLIST('execution_name1', 'MADRUGADA', 'execution_name2', 'ATUAL', 'workload_impact_threshold', 0, 'sql_impact_threshold', 0)
    );
END;
/

Nos parâmetros de execução, repassamos os nomes das tasks que estão sendo comparadas, e como nesse caso eu desejava analisar todos os SQLs do workload, independente do impacto gerado, setei as opções workload_impact_threshold e sql_impact_threshold para 0 (zero).

Como gerar um relatório do SQL Perfomance Analyzer

Após a comparação ser feita, o relatório pode ser gerado em formato HTML utilizando o spool:

SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SET TRIM ON
SET TRIMSPOOL ON
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 5000000
SET LONGCHUNKSIZE 5000000

SPOOL report.html

SELECT DBMS_SQLPA.report_analysis_task('SPA_TASK', 'ACTIVE', 'ALL') FROM dual;

SPOOL OFF

Analisando o relatório

Já no início do relatório temos informações importantes, como a quantidade de SQLs onde a performance melhorou ou piorou, trocas de plano de execução e erros encontrados:

Também podemos ver os SQLs que foram analisados e o comparativo entre os Elapsed Times antes e após as alterações:

Clicando em um SQL ID podemos ter mais informações sobre os ganhos alcançados:

E também analisar a alteração do plano de execução:

Conclusão

O relatório gerado pelo SQL Performance Analyzer consegue trazer uma comparação bastante clara sobre o antes e o depois das execuções dos SQLs analisados, e guiar de uma forma mais acertiva o tuning de SQLs onde a performance regrediu.

No caso da migração citada, ele forneceu um direcionamento dos SQLs que precisavam ser melhorados, além de trazer uma segurança maior para uma segunda tentativa da migração.

Para quem deseja se aprofundar mais no SQL Performance Analyzer, deixo abaixo o excelente post do Maicon Carneiro, onde ele demonstra a execução do SPA envolvendo o transporte dos SQL Tuning Sets entre dois ambientes, e as referências da documentação Oracle sobre a ferramenta:

• Testando o Desempenho de Consultas SQL no OCI Autonomous Database Utilizando SQL Performance Analyzer (SPA) – Maicon Carneiro;
• SQL Performance Analyzer;
• DBMS_SQLPA;

Até a próxima pessoal!