PL/SQL, TUNING

Roberto Sobrinho

08/07/2024

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

Recentemente, enfrentei um desafio com um código PL/SQL extremamente lento em um cliente. O desempenho estava prejudicando operações críticas, e eu precisava identificar rapidamente onde estava o gargalo. Para isso utilizei uma ferramenta poderosa chamada DBMS_TRACE que pode nos ajudar a rastrear e identificar os pontos lentos no código PL/SQL.

1. Instalar o Pacote `DBMS_TRACE`

Você pode baixar o script completo para instalação do pacote DBMS_TRACE aqui. A instalação básica envolve apenas a execução do @?/rdbms/admin/tracetab.sql. No entanto, eu criei novos objetos para facilitar o processo, além de conceder permissões de execução de forma pública.

ora18c:XEPDB1\sys = OPEN > @dbasobrinho.com.br_PLSQL_TRACE_01-instalar.sql
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > set lines 300 pages 100 timing off echo off


@?/rdbms/admin/tracetab.sql



Table dropped.


Table dropped.


Sequence dropped.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.


no rows selected


OWNER            OBJECT_NAME                    OBJECT_TYPE
---------------- ------------------------------ ------------------------------
PUBLIC           PLSQL_TRACE_EVENTS             SYNONYM
SYS              PLSQL_TRACE_EVENTS             TABLE
SYS              PLSQL_TRACE_RUNNUMBER          SEQUENCE
PUBLIC           PLSQL_TRACE_RUNS               SYNONYM
SYS              PLSQL_TRACE_RUNS               TABLE
PUBLIC           VW_PLSQL_TRACE_EVENTS          SYNONYM
SYS              VW_PLSQL_TRACE_EVENTS          VIEW

7 rows selected.

ora18c:XEPDB1\sys = OPEN > select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to '||GRANTEE ||
  2         decode(grantable, 'YES', ' with admin option') || ';' xxxxxxxxxxx
  3    from dba_tab_privs
  4   where table_name in ('PLSQL_TRACE_EVENTS', 'DBMS_TRACE');

XXXXXXXXXXX
----------------------------------------------------------------------
grant EXECUTE on SYS.DBMS_TRACE to PUBLIC;

1 row selected.

ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > create or replace view SYS.vw_plsql_trace_events as
  2  SELECT sou.owner OBJ_owner
  3        ,sou.name  OBJ_NAME
  4        ,trc.EVENT_SEQ id
  5        ,trc.EVENT_TIME DT_TIME
  6            --LEAD(trc.EVENT_TIME) OVER (PARTITION BY sou.name ORDER BY trc.EVENT_SEQ) - trc.EVENT_TIME AS TIME_TAKEN
  7            ,ROUND((CAST(LEAD(trc.EVENT_TIME) OVER (PARTITION BY sou.name ORDER BY trc.EVENT_SEQ) AS DATE) - CAST(trc.EVENT_TIME AS DATE)) * 24 * 60 * 60, 2) AS SEGUNDOS
  8        ,(SUBSTR(sou.text,1,100)) COMMAND_LINE
  9    FROM plsql_trace_events trc, dba_source sou
 10   WHERE sou.owner  = sou.owner
 11     AND sou.name   = sou.name
 12     AND sou.owner = trc.event_unit_owner
 13     AND sou.name   = trc.event_unit
 14     AND sou.line   = trc.event_line
 15     AND trc.runid  = (select max(runid) from plsql_trace_runs)
 16     AND trc.event_unit_owner <> 'SYS'
 17   ORDER BY trc.EVENT_SEQ
 18  /

View created.

ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > grant select on SYS.PLSQL_TRACE_EVENTS to public;

Grant succeeded.

ora18c:XEPDB1\sys = OPEN > grant select on SYS.PLSQL_TRACE_RUNS to public;

Grant succeeded.

ora18c:XEPDB1\sys = OPEN > grant select on SYS.VW_PLSQL_TRACE_EVENTS to public;

Grant succeeded.

ora18c:XEPDB1\sys = OPEN > create or replace public synonym PLSQL_TRACE_EVENTS for SYS.PLSQL_TRACE_EVENTS;

Synonym created.

ora18c:XEPDB1\sys = OPEN > create or replace public synonym PLSQL_TRACE_RUNS for SYS.PLSQL_TRACE_RUNS;

Synonym created.

ora18c:XEPDB1\sys = OPEN > create or replace public synonym VW_PLSQL_TRACE_EVENTS for SYS.VW_PLSQL_TRACE_EVENTS;

Synonym created.

ora18c:XEPDB1\sys = OPEN > grant EXECUTE on SYS.DBMS_TRACE to PUBLIC;

Grant succeeded.

ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > set echo off;
ora18c:XEPDB1\sys = OPEN >

Download Script

2. Criando Tabela e Dados para Teste

Para realizar um teste real, vamos criar uma tabela e popular com dados aleatórios:

sqlplus sys/manager@pdb1 as sysdba

ora18c:XEPDB1\sys = OPEN > @dbasobrinho.com.br_PLSQL_TRACE_02-tabela_teste.sql
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > begin execute immediate 'drop table SH.TX'; exception when others then null; end;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
ora18c:XEPDB1\sys = OPEN > create table SH.TX  (EMPNO PRIMARY KEY, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
  2  as
  3  SELECT level                                                            as EMPNO
  4        ,trim(DBMS_RANDOM.string ('X', TRUNC (DBMS_RANDOM.value (3,10)))) as ENAME
  5        ,trim(DBMS_RANDOM.string ('X', TRUNC (DBMS_RANDOM.value (5,9))))  as JOB
  6            ,DBMS_RANDOM.value(1,ROWNUM)                                      as MGR
  7            ,SYSDATE - DBMS_RANDOM.value(0,366)                               as HIREDATE
  8            ,round(DBMS_RANDOM.value(1200,50000),2)                           as SAL
  9            ,trunc(DBMS_RANDOM.value(1,100))                                  as COMM
 10            ,trunc(DBMS_RANDOM.value(1,50))                                   as DEPTNO
 11  FROM   dual
 12  CONNECT BY level <= 300000;

Table created.

Elapsed: 00:00:32.86
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH',tabname  => 'TX');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50

Download Script

3. Criando Procedure Function para Teste

Vamos criar uma procedure e uma função para simular uma rotina comum:

ora18c:XEPDB1\sys = OPEN > @dbasobrinho.com.br_PLSQL_TRACE_03-procedure.sql

Grant succeeded.

Elapsed: 00:00:00.04
ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > CREATE OR REPLACE FUNCTION SH.FNC_PAUSA(p1 IN NUMBER) RETURN NUMBER AS
  2  BEGIN
  3    sys.DBMS_LOCK.SLEEP(p1);
  4    RETURN 1;
  5  END FNC_PAUSA;
  6  /

Function created.

ora18c:XEPDB1\sys = OPEN >
ora18c:XEPDB1\sys = OPEN > CREATE OR REPLACE PROCEDURE SH.PRC_TX
  2  IS
  3     v_SUM         NUMBER;
  4  BEGIN
  5          SELECT /*SQL##0001.1##DBASOBRINHO*/
  6                 SUM(T1.SAL + T2.SAL)
  7            INTO v_SUM
  8            FROM
  9               (SELECT SUM(T.SAL) SAL
 10                  FROM SH.TX T
 11             WHERE T.JOB LIKE '%MANAGER%'
 12               ) T1,
 13               (SELECT SUM(T.SAL) SAL
 14                  FROM SH.TX T
 15             WHERE T.JOB LIKE '%CLERK%'
 16               ) T2
 17                  where FNC_Pausa(TRUNC(DBMS_RANDOM.VALUE(0, 16)) ) = 1;
 18      --/
 19      SELECT /*SQL##0001.2##DBASOBRINHO*/
 20            SUM(T.SAL)
 21        INTO v_SUM
 22        FROM SH.TX T
 23       WHERE T.DEPTNO = 10
 24           AND FNC_Pausa(TRUNC(DBMS_RANDOM.VALUE(0, 16)) ) = 1;
 25      --/
 26          FOR C1 in(SELECT /*SQL##0001.3##DBASOBRINHO*/
 27                           EMPNO, ENAME, SAL, ROWID RW
 28                              FROM SH.TX
 29                                  WHERE ENAME LIKE 'A%'
 30                                    AND ROWNUM < 30)
 31          LOOP
 32               update  /*SQL##0001.4##DBASOBRINHO*/  SH.TX
 33                   set JOB = UPPER(JOB)
 34                   WHERE ROWID = C1.RW
 35                   AND  FNC_Pausa(TRUNC(DBMS_RANDOM.VALUE(0, 3)) ) = 1;
 36
 37      END LOOP;
 38  END PRC_TX;
 39  /

Procedure created.

ora18c:XEPDB1\sys = OPEN > set timing on
ora18c:XEPDB1\sys = OPEN >

Download Script

4. Executando sem o `PLSQL_TRACE`

Antes de ativarmos o PLSQL_TRACE, vamos executar a procedure para termos uma referência do tempo de execução sem qualquer rastreamento habilitado:

ora18c:XEPDB1\sys = OPEN > 

set timing on

begin
    SH.PRC_TX;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.29

ora18c:XEPDB1\sys = OPEN >

5. Executando com o `DBMS_TRACE.trace_all_lines`

Vamos utilizar o DBMS_TRACE.trace_all_lines para rastrear todas as linhas executadas no código PL/SQL. Essa abordagem é útil para obter uma visão detalhada de cada linha de código que está sendo executada, permitindo identificar pontos específicos onde o tempo de execução pode ser melhorado.

ora18c:XEPDB1\sys = OPEN > 

set timing on

begin
    dbms_trace.set_plsql_trace(dbms_trace.trace_all_lines);
    SH.PRC_TX;
    dbms_trace.clear_plsql_trace;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:43.99

ora18c:XEPDB1\sys = OPEN >

Para visualizar os resultados do rastreamento, utilizamos a visão VW_PLSQL_TRACE_EVENTS:

ora18c:XEPDB1\sys = OPEN >

set lines 700 pages 500
col OBJ_OWNER      for a12;
col OBJ_NAME       for a20;
col ID             for 999999
col DT_TIME        for a20;
col COMMAND_LINE   for a100;
col SEGUNDOS       for 999999
SELECT * FROM VW_PLSQL_TRACE_EVENTS order by id
/

6. Executando com o DBMS_TRACE.trace_all_calls

Agora vamos ativar o rastreamento de todas as chamadas de procedimentos e funções usando DBMS_TRACE.trace_all_calls. Este método rastreia cada chamada de procedimento e função dentro do código PL/SQL.

ora18c:XEPDB1\sys = OPEN > set timing on

begin
    DBMS_TRACE.set_plsql_trace(DBMS_TRACE.trace_all_calls);
    SH.PRC_TX;
    DBMS_TRACE.clear_plsql_trace;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:42.52

ora18c:XEPDB1\sys = OPEN >

Para visualizar os resultados do rastreamento, utilizamos a visão VW_PLSQL_TRACE_EVENTS:

set lines 700 pages 500
col OBJ_OWNER      for a12;
col OBJ_NAME       for a20;
col ID             for 999999
col DT_TIME        for a20;
col COMMAND_LINE   for a100;
col SEGUNDOS       for 999999
SELECT * FROM VW_PLSQL_TRACE_EVENTS order by id
/

7. Executando com o DBMS_TRACE.trace_all_sql

Finalmente, vamos ativar o rastreamento de todas as instruções SQL executadas dentro do bloco PL/SQL usando DBMS_TRACE.trace_all_sql.

ora18c:XEPDB1\sys = OPEN > set timing on

begin
    DBMS_TRACE.set_plsql_trace(DBMS_TRACE.trace_all_sql);
    SH.PRC_TX;
    DBMS_TRACE.clear_plsql_trace;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.21

ora18c:XEPDB1\sys = OPEN >

Para visualizar os resultados do rastreamento, utilizamos a visão VW_PLSQL_TRACE_EVENTS:

ora18c:XEPDB1\sys = OPEN >

set lines 700 pages 500
col OBJ_OWNER      for a12;
col OBJ_NAME       for a20;
col ID             for 999999
col DT_TIME        for a20;
col COMMAND_LINE   for a100;
col SEGUNDOS       for 999999
SELECT * FROM VW_PLSQL_TRACE_EVENTS order by id
/

Diferenças entre os Métodos de Rastreamento

DBMS_TRACE.trace_all_sql:

Rastreia todas as instruções SQL executadas dentro do bloco PL/SQL.
Útil para identificar consultas SQL específicas que podem estar causando lentidão.

DBMS_TRACE.trace_all_calls:

Rastreia todas as chamadas de procedimentos e funções dentro do bloco PL/SQL.
Útil para entender o fluxo de chamadas dentro do bloco PL/SQL e identificar procedimentos ou funções problemáticos.

DBMS_TRACE.trace_all_lines:

Rastreia a execução linha a linha do bloco PL/SQL.
Fornece o nível mais detalhado de rastreamento, útil para depuração detalhada de blocos PL/SQL complexos.

Rastrear a execução de procedimentos PL/SQL com o DBMS_TRACE é essencial para identificar e solucionar problemas de desempenho em códigos complexos. Com as opções de rastreamento trace_all_sql, trace_all_calls e trace_all_lines, você pode obter uma visão detalhada do comportamento do código, identificar gargalos e otimizar o desempenho de maneira eficaz.

O trace_all_sql é útil para diagnosticar consultas SQL específicas, o trace_all_calls ajuda a entender o fluxo de chamadas de procedimentos e funções, e o trace_all_lines oferece um rastreamento mais granular, ideal para depuração detalhada.

🚬🍺 #DBAProblemático #GuinaNãoTinhaDó #SeReagirBúmViraPó #CaceteDeAgulha #ClienteSemNoção #AjudaODBA #DBAHerói #FitaLoka #MetaTomarSeuLugar #QueFita #TicTac09e40 #VidaBandida #OMetrôVaiPassar #1000Fita #ZePovinho #Dimas #1000Faces #9M #GuinaTaEmCana #SemMiséria #DinheiroNaMão #SeguroVaiCobrir #ChicoteEstrala 🍺🚬

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.

Categorias

DATAGUARD (2)
DBA (32)
LINUX (5)
PL/SQL (9)
RAC (5)
RMAN (5)
SCRIPTS (7)
SQL (5)
TUNING (14)

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

Categorias

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

1. Instalar o Pacote `DBMS_TRACE`

2. Criando Tabela e Dados para Teste

3. Criando Procedure Function para Teste

4. Executando sem o `PLSQL_TRACE`

5. Executando com o `DBMS_TRACE.trace_all_lines`

6. Executando com o DBMS_TRACE.trace_all_calls

7. Executando com o DBMS_TRACE.trace_all_sql

Diferenças entre os Métodos de Rastreamento

Compartilhe

Pesquisar

Roberto Sobrinho

Oracle ACE Associate

Specialist

Professional

Professional

Professional

Professional

Professional

Associate

Associate

Categorias

Categorias

Tags

CHECK_INTERVAL [1] no ora.asm gerando milhões de arquivos .aud

HugePages no Oracle: Configuração Passo a Passo no Red Hat 9.5

Oracle Unified Auditing: habilitar auditoria de logins malsucedidos e DDL passo a passo

Script para identificar discos ASM, ASMLIB e SO rapidamente

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

Categorias

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

1. Instalar o Pacote DBMS_TRACE

2. Criando Tabela e Dados para Teste

3. Criando Procedure Function para Teste

4. Executando sem o PLSQL_TRACE

5. Executando com o DBMS_TRACE.trace_all_lines

6. Executando com o DBMS_TRACE.trace_all_calls

7. Executando com o DBMS_TRACE.trace_all_sql

Diferenças entre os Métodos de Rastreamento

Compartilhe

Pesquisar

Roberto Sobrinho

Oracle ACE Associate

Categorias

Categorias

Tags

Tags

PL/SQL Lento? Aprenda a Identificar Comandos Problemáticos

1. Instalar o Pacote `DBMS_TRACE`

4. Executando sem o `PLSQL_TRACE`

5. Executando com o `DBMS_TRACE.trace_all_lines`