DBMS_PROFILER: Investigando a performance de objetos PL/SQL.
Hoje vamos falar de monitoramento e performance de objetos PL/SQL, pois quando estamos fazendo análise de performance desses objetos as coisas mudam um pouco se comparado com analise de performance de instruções SQL puramente, pois instruções SQL podemos fazer a análise de performance através dos planos de execução, estatísticas de execução e etc, mas quando temos procedures, functions, packges o procedimento é um pouco diferente. Então vamos lá!
Para análise de desempenho de objetos PL/SQL usamos a package nativa do Oracle chamada DBMS_PROFILER. Para fazer a instalação dessa package o procedimento é bem simples vejamos abaixo:
1 – Com o usuário SYS execute o script abaixo para criação da package (a partir da versão 10g essa package já vem instalada por defualt):
2 – Para executar este procedimento é legal que se crie um usuário dedicado para este fim dentro da sua instância. Para nosso exemplo criaremos um usuário chamado DEBUGER conforme os procedimentos:
3 – Crie (opcionalmente) os public synonyms para os objetos do usuário DEBUGER:
4 – Dê grant de execute na package DBMS_PROFILER ao usuário que vai executar os objetos alvo de sua análise. No nosso caso (como exemplo) será o usuário HR:
5 – Agora conecte-se com o usuário DEBUGER e vamos criar as tabelas que vão armazenar os dados das execuções dos objetos PL/SQL.
Para isso execute o script @?/rdbms/admin/proftab.sql
Quando você executar este script pela primeira vez, aparecerão alguns erros ORA-00942 e ORA-02289. Não se assuste, pois, este script na primeira etapa tenta dropar os objetos que ele vai criar de antemão.
6 – Dê os grants necessários para que as tabelas dos dados de execução dos objetos PL/SQL possam ser populadas por outros usuários:
A partir de agora nosso ambiente para analise de performance dos objetos PL/SQL está pronto. Sendo assim podemos iniciar as atividades, para isso vou usar o usuário HR (como exemplo) criando alguns objetos PL/SQL e fazendo a execução dos mesmos para coletar suas métricas para depois analisar.
7 – Conectado com o usuário HR vou criar uma cópia da tabela EMPLOYEES pois com essa tabela criarei uma procedure afim de fazer alguns experimentos e medir os tempos de execução.
8 – Agora que temos o ambiente pronto para coletar as informações de performance bem como uma tabela e uma procedure para o teste, vamos para o processo principal que é a chamada da package DBMS_PROFILER com as procedures START_PROFILER e STOP_PROFILER juntamente com a procedure que criamos a pouco, afim de verificar os resultados.
9 – Depois de executado o teste precisamos coletar os dados da execução, para isso vamos executar alguns selects. Conforme abaixo:
Toda execução com a DBMS_PROFILER automaticamente tem um ID, com base no valor que passamos no parâmetro RUN_COMMENT vamos capturar esse ID:
10 – Observe o valor da coluna RUNID pois este é o valor que será o nosso guia para as próximas consultas. Veja no select abaixo que temos um resumo do código que foi executado onde é evidenciado os seguintes dados:
11 – Já temos até então um resumo do que foi executado, mas agora precisamos saber em detalhes onde foi que cada parte do código gastou tempo de execução, pois se estamos falando de performance, e é esse tipo de informação que estamos buscando. Veja abaixo o select que vai nos trazer essas informações:
Agora veja o resultado, bem como sua interpretação:
Pois bem, com esse último select podemos ver exatamente a execução de um objeto PL/SQL linha a linha com dados de tempo de execução, afim de encontrar possíveis pontos de contenção bem como oportunidades de melhorias.
Por enquanto é isso aí, espero que seja útil e possa ajudar!
Forte abraço e até a próxima!