Wednesday, October 06, 2010

using "plshprof"

Hierarchical Profiling Concepts - start in Oracle Database 11g, the hierarchical profiler help to identify the hotspots and performance tuning opportunities in your PL/SQL applications.

plshprof is a command-line utility to generate HTML reports from raw profiler data generated by the data collection component after running the DBMS_HPROF.ANALYZE (we can generate directly from the raw profiler data).
SQL> exec DBMS_HPROF.START_PROFILING('TEMP_DIR','prf_test.txt');

PL/SQL procedure successfully completed.
-- TEMP_DIR is directory, this sample = /tmp
-- prf_test.txt is file name.

SQL> exec my_procedure01;

PL/SQL procedure successfully completed.
-- my_procedure0e is procedure, that we need to collect data

SQL> exec DBMS_HPROF.STOP_PROFILING;

PL/SQL procedure successfully completed.

SQL> !less /tmp/prf_test.txt
P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 11
P#C PLSQL."".""."__anonymous_block"
P#X 107
P#C PLSQL."SYS"."DBMS_OUTPUT"::11."GET_LINES"#660bd56a1b1640db #180
P#X 29
P#C PLSQL."SYS"."DBMS_OUTPUT"::11."GET_LINE"#8440831613f0f5d3 #129
P#X 6
.
.
.
then use "plshprof" command-line.
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Usage: plshprof [<option>...] <tracefile1> [<tracefile2>]
Options:
-trace <symbol> (no default) specify function name of tree root
-skip <count> (default=0) skip first <count> invokations
-collect <count> (default=1) collect info for <count> invokations
-output <filename> (default=<symbol>.html or <tracefile1>.html)
-summary print time only
go to TEMP_DIR path and...:
$ cd /tmp
$ plshprof -output prf_test prf_test.txt
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
[9 symbols processed]
[Report written to 'prf_test.html']
then we will see files (*.html).
and then open file (prf_test.html)
we will see summary report.
read more
other else using plshprof :
$ plshprof -summary prf_test.txt
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Total subtree time: 5765 microsecs (elapsed time)
However, we use DBMS_HPROF to collect data, so we have to grant EXECUTE privilege on the DBMS_HPROF package to user.

2 comments:

Ittichai Chammavanijakul said...

This is very cool utility. Definitely it is on the list for me to check it out. Thanks for sharing.

Ittichai

Surachart Opun said...

yes, I'd like to check more on 11g about Profiling and Tracing PL/SQL Code.