Benchmarking Oracle User Procedures/Functions

views:

answers:

+2 Q:

Benchmarking Oracle User Procedures/Functions

Hi Guys!

I'm thinking of a way on how to benchmark an Oracle procedure. My idea is this, put a logging function on each part of the procedure you wish to benchmark. Store the time it entered that part. Then, create another app (using PHP) to compute the difference between each execution.

The PHP app is needed to make it as layman as possible.

This idea wont work though because it is too scattered and obstrusive. If I want to execute the function/procedure multiple times at the same time, the result will be wrong.

Algorithms are fine. Codes is better. The whole app is best. :)

+6 A:

Oracle introduced the DBMS_PROFILER package in 9i. It does pretty much exactly what you describe, only better. Find out more.

In 11g they introduced the hierarchical profiler, which does something similar but for the entire PL/SQL callstack, not just an individual program unit. Find out more.

APC 2010-08-31 02:35:44

Where does `tkprof` fall with respect to these?

OMG Ponies 2010-08-31 02:52:30

TKPROF does SQL, the profilers do PL/SQL. Very handy if you use PL/SQL for consuming web services, reading/writing files etc.

Gary 2010-08-31 03:21:33

And if you want inspiration for a front-end over the DBMS_PROFILER data, take a look at TOADs charts (bar/pie-charts of where the time is spent, let you drill down into the subsets, and eventually jump out to the source line). There's some real improvements that could be made there (like 'live' jumping around the source) and I'd love to see a simple web-based equivalent tool.

JulesLt 2010-08-31 06:51:50

ansaurus

tags:

views:

answers:

Benchmarking Oracle User Procedures/Functions

related questions