Re: Profiling an Application

On Mar 6, 6:24 pm, headware <david.k.l..._at_gmail.com> wrote:
> 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

One of the best used tools in looking at oracle application performance issues is a low level 10046 trace. Cary Millsap has written an excellent book "Optimizing Oracle Performance" that you may want to purchase and read immediately.

While a 10046 trace can be digested and interpreted thru a free oracle provided tool called tkprof there is also an excellent open source resource profiler orasrp along with a commercial product from hotsos ( Cary Millsap's company ).

When you use something like a 10046 trace is is at once comprehensive ( well if you start and stop the tracing at appropriate times ) and can be used for in-depth analysis of the work that the database engine was called to perform ... without having to make any changes to the application or extra calls. Received on Thu Mar 06 2008 - 17:56:08 CST