Tempo de leitura: menos de 1 minuto
Fala galera, quanto tempo? Saudades de vocês!
Esses dias eu e meu amigo Darian Beluzzo, vinhamos trabalhando bastante em melhoria de performance de alguns processos no sistema que trabalhamos. Em alguns desses vimos que o gargalo era justamente o banco de dados.
Nosso objetivo é alterar a implementação para esses processos afim de melhorar o acesso a banco, consecutivamente conseguir melhorar a performance do mesmo.
Testamos várias formas de fazer, e algumas delas foi necessário partir para uma solução PL/SQL, já que o sistema usa banco de dados Oracle.
No entanto deparamos com a dificuldade de medir o tempo e fazer profiling desses bloco PL/SQL. Es tão que o Darian surgiu com a solução, e gentilmente me deixou publicar aqui no blog.
Criando os objetos necessário para o profiling
Basicamente será necessário criar três tables e uma sequence. São elas:
1 – Tabela para armazenar as informações de profile:
[code language=”sql”]
create table plsql_profiler_runs
(
runid number primary key, — unique run identifier,
— from plsql_profiler_runnumber
related_run number, — runid of related run (for client/
— server correlation)
run_owner varchar2(32), — user who started run
run_date date, — start time of run
run_comment varchar2(2047), — user provided comment for this run
run_total_time number, — elapsed time for this run
run_system_info varchar2(2047), — currently unused
run_comment1 varchar2(2047), — additional comment
spare1 varchar2(256) — unused
) TABLESPACE USERS;
comment on table plsql_profiler_runs is
‘Run-specific information for the PL/SQL profiler’;
[/code]
2 – Tabela para armazenar as informações das bibliotecas usadas por unidade:
[code language=”sql”]
create table plsql_profiler_units
(
runid number references plsql_profiler_runs,
unit_number number, — internally generated library unit #
unit_type varchar2(32), — library unit type
unit_owner varchar2(32), — library unit owner name
unit_name varchar2(32), — library unit name
— timestamp on library unit, can be used to detect changes to
— unit between runs
unit_timestamp date,
total_time number DEFAULT 0 NOT NULL,
spare1 number, — unused
spare2 number, — unused
—
primary key (runid, unit_number)
) TABLESPACE USERS;
comment on table plsql_profiler_units is
‘Information about each library unit in a run’;
[/code]
3 – Tabela para armazenar as informações acumuladas de tudo que foi executado:
[code language=”sql”]
create table plsql_profiler_data
(
runid number, — unique (generated) run identifier
unit_number number, — internally generated library unit #
line# number not null, — line number in unit
total_occur number, — number of times line was executed
total_time number, — total time spent executing line
min_time number, — minimum execution time for this line
max_time number, — maximum execution time for this line
spare1 number, — unused
spare2 number, — unused
spare3 number, — unused
spare4 number, — unused
—
primary key (runid, unit_number, line#),
foreign key (runid, unit_number) references plsql_profiler_units
) TABLESPACE USERS;
comment on table plsql_profiler_data is
‘Accumulated data from all profiler runs’;[/code]
4 – Sequence usada nas tabelas:
[code language=”sql”]
create sequence plsql_profiler_runnumber start with 1 nocache;
[/code]
Execução da procedure
Após criado os tabelas e sequence, basta executar suas procedure ou bloco anonimo dentro do bloco abaixo no local indicado:
[code language=”sql”]
BEGIN
dbms_profiler.start_profiler (‘Test of procedure BLA’);
/***/
/* CALL YOUR PROCEDURE OR ANONYMOUS BLOCK */
/***/
–SAMPLE:
dbms_output.put_line(to_char((CURRENT_TIMESTAMP),’HH:MM:SS’));
—
dbms_profiler.flush_data();
dbms_profiler.stop_profiler();
END;
/
[/code]
Coletando os dados
Para procedure, use a seguinte consulta:
[code language=”sql”]
select line#,
a.total_occur total_hits,
ROUND((a.total_time/1000000)/1000,2) elapsed_time_in_sec,
substr(c.text,1,500) as source
from plsql_profiler_data a, plsql_profiler_units b, user_source c
where a.runid = (SELECT MAX(RUNID) FROM plsql_profiler_data) — LAST EXECUTION
and a.runid = b.runid
and a.unit_number = b.unit_number
and b.unit_name = c.name
and a.line# = c.line
–and ROUND((a.total_time/1000000)/1000,2) > 0 –NOT ZEROES
–and rownum < 11 –10 MORE SLOWER
order by a.total_time desc ;
[/code]
Nota: Traz inclusive o codigo fonte da procedure
Para bloco anônimo, use a seguinte consulta:
[code language=”sql”]
select line#,
a.total_occur total_hits,
ROUND((a.total_time/1000000)/1000,2) elapsed_time_in_sec
from plsql_profiler_data a, plsql_profiler_units b
where a.runid = (SELECT MAX(RUNID) FROM plsql_profiler_data) — ULTIMA EXECUCAO
and a.runid = b.runid
and a.unit_number = b.unit_number
–and ROUND((a.total_time/1000000)/1000,2) > 0 –NOT ZEROES
–and rownum < 11 –10 MORE SLOWER
order by a.total_time desc ;
[/code]
Nota: Não é possível trazer o código fonte, terá que ver o número da linha com maior lentidão e olhar no bloco anônimo
Limpando ambiente
Após a medição, se julgar necessário, pode-se apagar estas tabelas criadas e a sequence:
[code language=”sql”]
drop table plsql_profiler_data cascade constraints;
drop table plsql_profiler_units cascade constraints;
drop table plsql_profiler_runs cascade constraints;
drop sequence plsql_profiler_runnumber;
[/code]
Obrigado Darian, por deixar-me compartilhar a dica!
