Tirando um RAIO-X do desempenho do código PL/SQL

Hierarchical Profiler

Você escreveu seu código PL/SQL (Procedure, Function ou Package) e na hora de testar descobre que o desempenho não esta satisfatório, e agora? o que fazer? A versão do banco Oracle 11g dispõe do recurso Hierarchical Profiler que permite medir o desempenho de cada linha de código do PL/SQL, com a utilização desse recurso fica fácil identificar o que precisa ser melhorado no seu código.

Neste artigo vamos ver um exemplo prático de utilização do recurso Hierarchical Profiler utilizando a ferramenta gráfica SQLDeveloper da Oracle.

Para realizar esta simulação vamos:

1) Configurar o usuário para utilizar o recurso HPROF
2) Executar a procedure utilizando HPROF
3) Identificar a linha da procedure com baixo desempenho
4) Alterar o código da procedure com baixo desempenho
5) Verificar desempenho da procedure após alteração do código

1) Configurar o usuário para utilizar o recurso HPROF

Para utilizar o recurso Hierarchical Profiler precisamos configurar um diretório no servidor do banco, atribuir privilégios de acesso neste diretório ao usuário no qual serão criados os códigos PL/SQL, permitir que este usuário execute o pacote DBMS_HPROF, criar as tabelas necessárias ao recurso Hierarchical Profiler e criar uma PROCEDURE para realizarmos a simulação.

A procedure utilizada nesta simulação foi criada somente para fins didáticos e não deve ser considerada como melhores práticas de programação PL/SQL.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
[oracle@odbsrv01 oracle]$ mkdir /oracle/SH
[oracle@odbsrv01 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 23 10:43:49 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> create directory PLSHPROF_DIR as '/oracle/SH';


Directory created.

SQL> -----------------------------------------------
SQL> -- Atribuir os privilégios para usuário SH
SQL> -----------------------------------------------
SQL>
SQL> grant read,write on directory PLSHPROF_DIR to SH;

Grant succeeded.

SQL> grant execute on DBMS_HPROF to SH;

Grant succeeded.

SQL> -----------------------------------------------
SQL> -- Criar as tabelas para HPROF no schema SH
SQL> -----------------------------------------------
SQL> conn sh/sh
Connected.
SQL> @?/rdbms/admin/dbmshptab.sql;

Table dropped.


Table dropped.


Table dropped.


Sequence dropped.


Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

SQL>
SQL> -----------------------------------------------
SQL> -- Criar as tabelas auxiliares
SQL> -----------------------------------------------
SQL>
SQL> CREATE TABLE SH.CUSTOMERS_COUNTRIES
  2     (       COUNTRY_NAME            VARCHAR2(40 BYTE)       NOT NULL ENABLE,
  3     COUNTRY_REGION          VARCHAR2(20 BYTE)       NOT NULL ENABLE,
  4     CUST_ID                 NUMBER                  NOT NULL ENABLE,
  5     CUST_GENDER             CHAR(1 BYTE)            NOT NULL ENABLE,
  6     CUST_STATE_PROVINCE     VARCHAR2(40 BYTE)       NOT NULL ENABLE,
  7     CONSTRAINT cust_countr_pk PRIMARY KEY (CUST_ID)
  8     USING INDEX TABLESPACE USERS )
  9     TABLESPACE USERS ;

Table created.

SQL>
SQL> CREATE TABLE SH.SALES_SUM
  2     (       CUST_ID                 NUMBER                  NOT NULL ENABLE,
  3     AMOUNT_SOLD             NUMBER(10,2)            NOT NULL ENABLE,
  4     CONSTRAINT sale_sum_pk PRIMARY KEY (CUST_ID)
  5     USING INDEX TABLESPACE USERS )
  6     TABLESPACE USERS ;

Table created.

SQL>
SQL> -----------------------------------------------
SQL> -- Criar uma procedure no schema SH
SQL> -----------------------------------------------
SQL>
SQL> create or replace PROCEDURE PRC_REPORT_01(
  2  in_country_name    IN varchar2
  3  ) IS
  4
  5  CURSOR c_cust_count IS
  6          Select cu.cust_id, co.country_name, co.country_region, cu.cust_gender, cu.cust_state_province
  7            From customers cu, countries co
  8           Where cu.country_id = co.country_id
  9             and co.country_name = in_country_name
 10        Order By cu.cust_id;
 11
 12  CURSOR c_sales_sum IS
 13          Select s.cust_id, sum(s.amount_sold) amount_sold
 14            From sales s
 15  --          From sales s, customers_countries cc
 16  --         Where s.cust_id = cc.cust_id
 17        Group By s.cust_id
 18        Order By s.cust_id;
 19
 20  CURSOR c_report IS
 21          Select cc.country_region, cc.country_name, cc.cust_state_province, cc.cust_gender, sum(ss.amount_sold) amount_sold
 22            From customers_countries cc,
 23                 sales_sum ss
 24           Where cc.cust_id = ss.cust_id
 25        Group By cc.country_region, cc.country_name, cc.cust_state_province, cc.cust_gender
 26        Order By cc.country_region, cc.country_name, cc.cust_state_province;
 27
 28
 29  reg_cust_count   c_cust_count%ROWTYPE;
 30
 31  reg_sales_sum    c_sales_sum%ROWTYPE;
 32
 33  reg_report       c_report%ROWTYPE;
 34
 35  BEGIN
 36
 37     EXECUTE IMMEDIATE 'truncate table customers_countries';
 38     EXECUTE IMMEDIATE 'truncate table sales_sum';
 39
 40     OPEN c_cust_count;
 41
 42        LOOP
 43            FETCH c_cust_count
 44             INTO reg_cust_count;
 45
 46            EXIT WHEN c_cust_count%NOTFOUND;
 47
 48            INSERT INTO CUSTOMERS_COUNTRIES (country_name, country_region, cust_id, cust_gender, cust_state_province)
 49                                      VALUES(reg_cust_count.country_name, reg_cust_count.country_region, reg_cust_count.cust_id, reg_cust_count.cust_gender, reg_cust_count.cust_state_province);
 50            COMMIT;
 51
 52        END LOOP;
 53
 54     CLOSE c_cust_count;
 55
 56     OPEN c_sales_sum;
 57
 58        LOOP
 59            FETCH c_sales_sum
 60             INTO reg_sales_sum;
 61
 62            EXIT WHEN c_sales_sum%NOTFOUND;
 63
 64            INSERT INTO SALES_SUM (cust_id, amount_sold)
 65                            VALUES(reg_sales_sum.cust_id, reg_sales_sum.amount_sold);
 66            COMMIT;
 67
 68        END LOOP;
 69
 70     CLOSE c_sales_sum;
 71
 72     OPEN c_report;
 73
 74        LOOP
 75            FETCH c_report
 76             INTO reg_report;
 77
 78            EXIT WHEN c_report%NOTFOUND;
 79
 80            dbms_output.put_line(reg_report.country_region||'_'||reg_report.country_name||'_'||reg_report.cust_state_province||'_'||reg_report.cust_gender||' => '|| reg_report.amount_sold);
 81
 82            COMMIT;
 83
 84        END LOOP;
 85
 86     CLOSE c_report;
 87
 88
 89
 90  EXCEPTION
 91     WHEN OTHERS THEN
 92
 93        DBMS_OUTPUT.PUT_LINE('ERRO :' || SQLCODE || ' - ' || SQLERRM);
 94
 95        CLOSE c_cust_count;
 96        CLOSE c_sales_sum;
 97        CLOSE c_report;
 98
 99  END PRC_REPORT_01;
100  /

Procedure created.

SQL>

 

2) Executar a procedure utilizando HPROF

Vamos abrir uma conexão no banco Oracle com o usuário SH utilizando o SQLDeveloper, na lista de objetos do usuário SH vamos clicar com o botão direito na PROCEDURE PRC_REPORT_01 e clicar em PROFILE… conforme imagem abaixo:

 

01_acionar_HPROF

 

Na sequência será aberta uma janela PERFIL PL/SQL onde vamos informar o parâmetro PAÍS igual a “Canada”, conforme imagem abaixo:

 

02_Passar_variavel

 

3) Identificar a linha da procedure com baixo desempenho

 

Após a execução da procedure algumas estatísticas serão exibidas, na imagem abaixo temos o tempo total de execução da procedure em micro-segundos indicado pela seta vermelha 01, ao clicarmos nesse valor aparecerá uma janela abaixo com as informações estatísticas de cada linha do código PL/SQL executado, para identificarmos a linha que esta consumindo mais tempo no código precisamos clicar na seta de ordenação descendente do campo FUNCTION % (seta vermelha 02), nesta simulação podemos ver que a linha 66 da procedure esta consumindo 70,2% do tempo total de execução da procedure e essa linha foi executada 7059 vezes.

 

03_Estatisticas

 
 

Clicando na aba CÓDIGO e localizando a linha 66, conforme indicado pelas setas vermelhas na imagem abaixo, podemos observar que a linha 66 corresponde ao comando COMMIT, este comando esta dentro de um LOOP que foi executado 7059 vezes pois o cursor C_SALES_SUM selecionou 7059 linhas.

 

04_Linha_codigo

 

Uma forma de tornar a procedure mais rápida seria diminuindo o numero de linhas selecionadas no cursor C_SALES_SUM, verificando a consulta utilizada neste cursor, conforme imagem abaixo, podemos observar que ela esta selecionando todos os registros da tabela SALES, pois não existe um filtro na cláusula WHERE. Como o relatório criado por esta procedure visa recuperar somente as informações referentes a um determinado PAÍS, podemos alterar esta consulta fazendo um JOIN com a tabela CUSTOMERS_COUNTRIES para selecionar na tabela SALES somente os registros referentes ao PAÍS especificado como parâmetro na procedure. Na procedure que foi executa o código deste JOIN estava comentado, conforme imagem abaixo:

 

05_Consulta

 

4) Alterar o código da procedure com baixo desempenho

Vamos alterar o código da procedure conforme imagem abaixo:

 

06_Consulta_alterada

 
 

Na sequência vamos compilar a procedure no banco, clicando no ícone de engrenagem e na opção COMPILAR, conforme imagem abaixo:

07_Compilar_procedure

 

5) Verificar o desempenho da procedure após alteração do código

Após a compilação da procedure vamos executá-la novamente repetindo os passos da etapa 2 e na sequência conferir as estatísticas conforme imagem abaixo:

 

08_Estatisticas2

 

Na imagem acima podemos verificar que o tempo de execução da procedure antes da alteração foi de 4.699.941 micro-segundos, ou 4,7 segundos. Na execução após a alteração da procedure o tempo de execução caiu para 122.611 micro-segundos, ou 12 centésimos de segundo.

 

Referências

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

2 comments

Deixe uma resposta para V. J.A. Cancelar resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *