Measuring formspider within database?

Hi Tomas,

You may use DBMS_PROFILER to trace your performance issues.

How to install DBMS_PROFILER:

• open $ORACLE_HOME/rdbms/admin folder. Find profload.sql and proftab.sql scripts in this folder.
• run profload.sql in SYS schema and run proftab.sql in schema where you will use the profiler.

Using DBMS_PROFILER:

• start and stop the profiler (Add dbms_profiler.start_profiler and dbms_profiler.stop_profiler commands to your procedure where you want to start tracing performance and stop it.):

  procedure your_procedure_name is begin dbms_profiler.start_profiler('your_procedure_name'); -- your existing methods or Formspider APIs dbms_profiler.stop_profiler; end;

• collect the data on tables:

Get the "runid" parameter from followinq query:

select runid, run_owner, run_date, run_comment
from plsql_profiler_runs;

Use "runid" parameter in followind query to see objects used:

select runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp
from plsql_profiler_units
where  runid = &runid;

Use "runid" and "unit_number" parameters to see details on specific object:

select pd.unit_number, pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &runid
and pd.unit_number = &unit_number
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION')
and pd.total_time > 0;

Regards