Testando o Desempenho de Consultas SQL no OCI Autonomous Database Utilizando SQL Performance Analyzer (SPA)

Introdução

O Oracle SQL Performance Analyzer (SPA) é das funcionalidades do Oracle Real Application Testing (RAT), dividindo espaço com a funcionalidade Database Replay.

Este recurso permite capturar os planos e métricas de execução de um conjunto de instruções SQL em um banco de dados Oracle e depois testar a execução dessas instruções SQL no mesmo banco de dados, mas utilizando uma configuração diferente (parâmetros, índices, particionamento, etc). Uma outra possibilidade é capturar as métricas de execução em um banco de dados, transportá-las e executar um teste em um banco de dados diferente.

Diferentemente do Database Replay, o SQL Performance Analyzer (SPA) visa apenas testar e comparar os planos de execução de uma ou algumas instruções SQL específicas, de forma individual, sem considerar concorrência ou a ordem exata em que esses SQL são executados pela aplicação.

Este post demonstra como utilizar o SPA para capturar os planos e as métricas de execução dos SQL executados por uma determinada a aplicação em um banco de dados Oracle não-Autonomous e testar o seu comportamento no Autonomous Database. Essa técnica pode ser útil para realizar testes de peformance mais simplificados antes de uma migração.

Pré Requisitos para o Autonomous

Esses são pré requisitos específicos do Autonomous Database. Opcionalmente, se você já tem os itens abaixo criados em outra ocasião, pode reaproveitá-los agora e pular para a Fase 2.

• Download da Wallet do Autonomous
• Criar Bucket no Object Storage
• Criar Auth Token para o Usuário OCI
• Criar Credencial no Autonomous
• Descompactar Wallet do Autonomous

A) Download da Wallet

Acesse a página do Autonomous Database onde executará o teste e faça o download da Wallet de conexão:

B) Bucket no Object Storage

Crie um Bucket para servir de área temporára para o DUMP do schema/tabelas da aplicação + o DUMP da tabela de staging do SQL Tuning Set:

Especifique um nome amigável, as configurações padrão do Bucket são suficientes.

C) Gerando Auth Token para o usuário OCI.

Clique no icone da conta do usuário no canto superior direito, depois clique em “My profile”:

Na página do perfil do usuário, vá até “Resources” e clique em “Auth tokens”, clique em “Generate token”, informe uma descrição amigável e prossiga.

Com o token gerado, copie e salve em algum lugar para uso posterior, pois ele não poderá ser exibido novamente.

Este token será usado para criar uma Credencial no Autonomous, que por sua vez será usada pelo DataPump imdp.

D) Descompactando Wallet do Autonomous Para Usar com SQLPLUS e DataPump

Copie o arquivo zip para a máquina onde executará o impdp. No meu caso, utilizo o próprio servidor de banco de dados de origem.

O diretório escolhido neste exemplo para armazenar os arquivos de tns do Autonomous é “/home/oracle/tns_adb”.

[oracle@lab01 ~]$ mkdir -P ~/tns_adb
[oracle@lab01 ~]$ unzip Wallet_test.zip -d ~/tns_adb/
[oracle@lab01 ~]$ export TNS_ADMIN=/home/oracle/tns_adb/

Edite o arquivo sqlnet.ora e altere o parâmetro WALLET_LOCATION para usar o diretório correto do TNS_ADMIN:

[oracle@lab01 ~]$ cat ~/tns_adb/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN")))
SSL_SERVER_DN_MATCH=yes

1) Carga Inicial no Autonomos

Nesta etapa carregamos no Autonomous o schema completo ou as tabelas que são utilizadas pelas instruções SQL que serão testadas.

1.1) Gerando DUMP do Schema ou Tabelas na Origem

1.1.1) Criando um diretório no filesystem para gerar o export com DataPump:

mkdir -p /backup/export/pdbsoe

1.1.2) No banco de Dados, crie o Oracle Directory que será usado no DataPump

CREATE OR REPLACE DIRECTORY EXPORT_ADB AS '/backup/export/pdbsoe/';

1.1.3) Executando o export do schema da aplicação:

expdp system@pdbsoe \
 exclude=cluster,indextype,db_link \
 directory=EXPORT_ADB \
 parallel=4 \
 schemas=soe_user \
 dumpfile=adbsoe_spa%u.dmp

1.2) Upload do DUMP para OCI

Você pode usar opções como OCI CLI ou RCLONE. Neste exemplo, estou usando um script shell customizado upload_to_oci.sh.

./upload_to_oci.sh /backup/export/pdbsoe/

O resultado deve ser similar a este:

1.3) Import do SCHEMA/Tabelas no Autonomous Database

1.3.1) Crie a credencial no Autonomous:

sqlplus admin@adbsoe_high

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'CRED_ADB_RAT',
    username => 'Default/SuaContaOCI@exemplo.com',
    password => 'SeuTokenAqui'
  );
END;
/

1.3.2) Iniciando o import com DataPump impdp:

impdp admin@adbsoe_high \
 directory=DATA_PUMP_DIR \
 credential=CRED_ADB_RAT \
 dumpfile=https://objectstorage.sa-vinhedo-1.oraclecloud.com/n/axai3hjvkzzf/b/oracle-rat-spa/o/pdbsoe/adbspa%u.dmp \
 parallel=4 \
 encryption_pwd_prompt=yes \
 exclude=cluster,indextype,db_link,statistics \
 remap_tablespace=%:DATA

Note o formato da URL, onde o formato informado em <DUMP_FILE> deve ser o mesmo utilizado durante o export:

https://objectstorage.<REGION>.oraclecloud.com/n/<TENANCY_NAMESPACE>/b/<BUCKET_NAME>/o/<FOLDER_NAME>/<DUMP_FILE>

Neste ponto, o Autonomous Database já está pronto para ser testado.

2) Criando e Transportando o SQL Tuning Set

Esta etapa é executada na origem e serve para capturar as métricas das queries que serão testadas e avaliadas no destino. Neste contexto, o SQL Tuning Set representará o workload a ser testado.

2.1) Criando o SQL Tuning Set na Origem

2.1.1) Na origem, crie o SQL Tuning Set com as queries que deseja testar no destino (Autonomous):

BEGIN 
 DBMS_SQLTUNE.CREATE_SQLSET (
  sqlset_name  => 'STS_SPA1' , 
  description  => 'Teste da aplicacao SOE com SQL Performance Analyzer' 
 );
END;
/

2.1.2) Importando as queries para o STS.

Neste exemplo estou filtrando na Shared Pool todas as queries executadas pelo usuário SOE_USER. Você poderia utilizar um filtro diferente como MODULE, ou até mesmo criar um SQL Tuning Set importando as queries do AWR.

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
  SELECT VALUE(p) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(' parsing_schema_name in (''SOE_USER'') ') ) p;
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'STS_SPA1', populate_cursor =>  cur );
END;
/

2.2) Exportando o SQL Tuning Set na Origem

2.2.1) Crie uma tabela de STAGING para exportar o SQL Tuning Set e transportá-lo para a OCI:

exec dbms_sqltune.create_stgtab_sqlset(table_name => 'TB_STS_STAGE');

2.2.2) Coloque o SQL Tuning Set na tabela de Staging:

BEGIN
dbms_sqltune.pack_stgtab_sqlset(
 sqlset_name => 'STS_SPA1', 
 sqlset_owner => 'SYSTEM', 
 staging_table_name => 'TB_STS_STAGE'
);
END;
/

2.2.3) Crie um Oracle DIRECTORY para gerar o export da tabela TB_STS_STAGE contendo o SQL Tuning Set. O dump gerado costuma ter menos de 1 GB.

Este segundo DIRECTORY específico é opcional, você pode utilzar o que já foi criado para o export anterior.

create or replace directory STS_EXP_DIR as '/home/oracle/';

2.2.4) Exporte a tabela TB_STS_STAGE com DataPump (expdp), esse export não requer parâmetros especiais:

expdp system@pdbsoe tables=TB_STS_STAGE directory=STS_EXP_DIR dumpfile=sts_stage_soe.dmp

2.3) Importando o SQL Tuning Set no destino

2.3.1) Faça upload do DUMP da tabela de staging para o Bucket na OCI:

./upload_to_oci.sh /home/oracle/sts_stage_soe.dmp

O resultado deve ser algo similar a este:

2.3.2) Execute o import com impdp:

impdp admin@adbsoe_high \
 directory=DATA_PUMP_DIR \
 credential=CRED_ADB_RAT \
 dumpfile=https://objectstorage.sa-vinhedo-1.oraclecloud.com/n/axai3hjvkzzf/b/oracle-rat-spa/o/oracle/sts_stage_soe.dmp \
 remap_schema=SYSTEM:ADMIN

2.3.3) Após importar a tabela de staging no Autonomous, importe o SQL Tuning Set a partir dela:

sqlplus admin@adbsoe_high

-- exec DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'STS_SPA1', sqlset_owner => 'SYSTEM');
BEGIN
   dbms_sqltune.unpack_stgtab_sqlset(
    sqlset_name => 'STS_SPA1',
    sqlset_owner => 'SYSTEM',
    replace => TRUE,
    staging_schema_owner => 'ADMIN',
    staging_table_name => 'TB_STS_STAGE'
   );
END;
/

3) Executando o SQL Performance Analyzer (SPA)

3.1) Conecte-se novamente no Autonomous:

sqlplus admin@adbsoe_high

3.2) Crie uma tarefa no SQL Performance Analyzer:

DECLARE
 t_name VARCHAR2(100);
BEGIN
 t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sqlset_name => 'STS_SPA1', 
  sqlset_owner => 'SYSTEM', 
  task_name => 'SPA_TASK1'
  );
END;
/

3.3) Crie uma primeira execução de teste convertendo as métricas contidas no SQL Tuning Set que foi gerado no banco de dados de origem. Essa execução simulada será usada posteriormente para comparar o resultado com a execução real executada no Autonomous:

BEGIN 
dbms_sqlpa.execute_analysis_task(
   task_name => 'SPA_TASK1',
   execution_type => 'convert sqlset',
   execution_name => 'onpremise_before',
   execution_params => DBMS_ADVISOR.ARGLIST('sqlset_name', 'STS_SPA1', 'sqlset_owner', 'SYSTEM')
   );
END;
/

3.4) Inicia uma execução real de teste no Autonomous:

BEGIN
dbms_sqlpa.execute_analysis_task(
   task_name => 'SPA_TASK1',
   execution_type => 'test execute',
   execution_name => 'autonomous_after'
   );
END;
/

3.5) Esta etapa executa uma comparação entre a primeira execução que representa o ambiente de origem (Onpremise), e a segunda execução que representa o teste real realizado no destino (Autonomous Database):

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

3.6) Gerando Relatórios

Após executar o SQL Performance Analyzer para testar o comportamento dos SQL, podemos gerar relatórios comparando o ANTES e DEPOIS do workload como todo, assim como o desempenho indvidual de cada SQL testado.

Opção 1) Gerando um relatório interativo para visualizar o resultado do teste:

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 spa_active_report.html

SELECT DBMS_SQLPA.report_analysis_task('SPA_TASK1', 'ACTIVE', 'ALL') FROM dual;
SPOOL OFF

Exemplo do relatório gerado:

Eu particularmente gosto desse relatório para ter uma visão global do resultado do teste e poder nevegar de forma interativa entre as queries que apresentam regressão de performance.

Opção 2: Gerando um relatório HTML estático.

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 spa_static_report.html

SELECT DBMS_SQLPA.report_analysis_task('SPA_TASK1', 'HTML', 'TYPICAL', 'ALL') FROM DUAL;
SPOOL OFF

Exemplo do relatório estático:

Eu particularmente gosto mais desse relatório para fazer a análise individual dos planos de execução de cada query.

Próximos Passos

Após executar uma rodada de testes e avaliar o primeiro relatório, você pode considerar aplicar alguns ajustes no ambiente como mudança do SERVICE_NAME utilizado na conexão do Autonomus (influencia o paralelismo), criação de SQL Profiles, SQL Plan Baselines ou qualquer outro ajuste nos objetos da aplicação. Por fim, executar uma nova rodada de testes seguindo os passo 3.4 a 3.6.

Para executar novas rodadas, recomendo alterar o nome da “execution_name” nas etapas 3.4 e 3.5 para facilitar a identificação de cada rodada e do relatório gerado.

Conclusão

Este post demonstrou como utilizar o SPA para testar o desempenho de instruções SQL no Autonomous Database. Na prática a única coisa que muda no passo a passo são os detalhes específicos para realizar o export e import usando o Bucket OCI.

A parte funcional do SPA no Autonomous é exatamente igual ao que temos nos bancos de dados Oracle não-Autonomous. Tirando essa especificidade do Export/Import, você deve conseguir seguir esse mesmo passo a passo para realizar testes em outros banco de dados Oracle.

Em algumas etapas poderíamos utilizar outras abordagens utilizando o ferramental OCI como Cloud Shell ou o SQL Worksheet da console OCI, mas tentei deixar esse passo a passo o mais próximo possível do que seria executado em qualquer outro banco de dados sem ser o Autonomous, de modo que possa ser reaproveitado.