Profiling an Application

I have a Delphi application connecting through BDE to Oracle 10g. The application is running very slowly in certain areas and I would like to use the RDMS_PROFILER package to help analyze the problem. I'm not very experienced with Oracle, but I did the following steps to get this to happen:

1. I ran profload.sql in SQL*Plus logged in as SYS.
2. I ran proftab.sql logged in as the user that connects to the app's schema, APP_USER.
3. I logged into SQL*Plus as APP_USER and ran the following command:

DECLARE
  l_result BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.start_profiler(run_comment => 'test: ' || SYSDATE);
END; 4. I started the app and recreated the slow performance. 5. I went back to SQL*Plus and ran the following command:

DECLARE
  l_result BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.stop_profiler; END; The problem is that I only get a few rows of data in plsql_profiler_data. There are six lines all attributed to the <anonymous> user. The app is doing a ton of stuff so I would think there would be lots of entries under the APP_USER user.

It seems to work fine if I run a dummy script instead of the app in between the start_profiler and stop_profiler commands, like this:

DECLARE
  l_result BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.start_profiler(run_comment => 'test: ' || SYSDATE);
  call dummy_procedure;
  l_result := DBMS_PROFILER.stop_profiler; END; Is it because I'm calling the start_profiler and stop_profiler methods inside different BEGIN...END blocks? How do you get the activity happening within an application to log to the profiler tables?

Thanks,
Dave Received on Thu Mar 06 2008 - 17:24:54 CST