Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
 
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Utilities Tips

Using dbms_profiler

Useful Scripts


To extract high-level data, including the length of a particular run, the script (profiler_runs.sql) below can be executed:

SEE CODE DEPOT

RUNID UNIT_NUMBER OBJECT_NAME TYPE SEC PCT
----- ----------- -------------------- --------------- --------- ------
1 1 <anonymous> .00 .0
1 2 <anonymous> 1.01 .0
1 3 BMC$PKKPKG PACKAGE BODY 6921.55 18.2
1 4 <anonymous> .02 .0
2 1 <anonymous> .00 .0
2 2 <anonymous> .01 .0

6 rows selected.



Note that anonymous PL/SQL blocks are also included in the profiler tables. Anonymous blocks are less useful from a tuning perspective since they cannot be tied back to a source object in user_source. Anonymous PL/SQL blocks are simply runtime source objects and do not have a corresponding dictionary object (package, procedure, function). For this reason, the anonymous blocks should be eliminated from most reports.

From the data displayed above, the next step is to focus on the lines within the package body, testproc, that are taking the longest. The script (profiler_top10_lines.sql) below displays the line numbers and their performance benchmarks of the top 10 worst performing lines of code.


select line#, total_occur,
decode (total_occur,null,0,0,0,total_time/total_occur/1000,0) as avg,
decode(total_time,null,0,total_time/1000) as total_time,
decode(min_time,null,0,min_time/1000) as min,
decode(max_time,null,0,max_time/1000) as max
from plsql_profiler_data
where runid = 1
and unit_number = 3 -- testproc
and rownum < 11 -- only show Top 10
order by total_time desc ;


LINE# TOTAL_OCCUR AVG TOTAL_TIME MIN MAX
---------- ----------- ---------- ---------- ---------- ----------
156 1 5008.457 5008.457 5008.457
27 1 721.879 721.879 721.879
2113 1 282.717 282.717 282.717
89 1 138.565 138.565 138.565
2002 1 112.863 112.863 112.863
1233 1 94.984 94.984 94.984
61 1 94.984 94.984 94.984
866 1 94.984 94.984 94.984
481 1 92.749 92.749 92.749
990 1 90.514 90.514 90.514

10 rows selected.


Taking it one step further, the query below (profiler_line_source.sql) will extract the actual source code for the top 10 worst performing lines.


select line#,
decode (a.total_occur,null,0,0,0,
a.total_time/a.total_occur/1000) as Avg,
substr(c.text,1,20) as Source
from plsql_profiler_data a, plsql_profiler_units b, user_source c
where a.runid = 1
and a.unit_number = 3
and a.runid = b.runid
and a.unit_number = b.unit_number
and b.unit_name = c.name
and a.line# = c.line
and rownum < 11
order by a.total_time desc ;

LINE# AVG SOURCE
---------- ---------- --------------------
156 5008.457 select sum(bytes) into reusable_var from dba_free_space;
27 721.879 execute immediate dml_str USING current_time
2113 282.717 select OBJ#, TYPE# from SYS.OBJ$;
89 138.565 OBJ_TYPES(BOBJ(I)) := BTYP(I);
2002 112.863 select count(*) into reusable_var from dba_objects
1233 94.984 delete from pkk_daily_activity
61 94.984 update_stats_table(33, reusable_var, null);
866 94.984 latest_executions := reusable_var - total_executions;
481 92.749 time_number := hours + round(minutes * 100/60/100,2);
990 90.514 update_stats_table(45, LOBS, null);

10 rows selected.



Notice from the output above that most of the information needed to diagnose and fix PL/SQL performance issues is provided. For lines containing SQL statements, the tuner can optimize the SQL perhaps by adding optimizer hints, eliminating full table scans, etc. Consult Chapter 5 for more details on using tkprof utility to diagnose SQL issues.

Other useful scripts that are hidden within the Oracle directory structure ($ORACLE_HOME/PLSQL/DEMO) include a few gems that help report and analyze profiler information.

• profdemo.sql -A demo script for collecting PL/SQL profiler data.

• profsum.sql - A collection of useful SQL scripts that are executed against profiler tables.

• profrep.sql – Creates views and a package (unwrapped) that populates the views based on the three underlying profiler tables
 


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.
 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.