Re: Performance tuning approach for connection pooling apps !
Date: Wed, 8 Feb 2012 15:13:59 -0800 (PST)
Message-ID: <1328742839.94043.YahooMailNeo_at_web114705.mail.gq1.yahoo.com>
Hi:
For performance data in AWR or ASH, there are many directions to check (I call them ‘dimensions’).
- Session, application (username, module, program), or service - Wait event - Sql - Sql plan - Object - Io stats - GC stats (if it is RAC database)
For application using connection pooling, you may not be able to drill down to a specific user session. however, you still have options to go down to application level or service level. For example, if you know the connection pooling is using a specific username or service name to connect to database, you can easily find out ‘top sql’, ‘top sql plan’,’top object’ for that username or service by filtering them out of ASH.
The ASH is ‘session level’ data, in which the ‘session’ means several things such as user, module, action, program, service, etc.
The AWR is ‘instance level’ data, and it tells you what the overall database performance is. You will not able to drill down to specific session, however, if you get some ideas from ASH of how your application behaves, you can compare that with the AWR to see how it contributes to the overall performance. For example, one of the ‘top sql’ in AWR could also show up in the ‘top sql’ of your application in ASH. Plus, the database load profile in AWR can be broke down into service level, which is a benefit if your application uses service.
Please feel free to try DBspeed and it is good at dimensional analysis of AWR and ASH data. If you are currently having performance issue, you have more options like tracing – you can trace by sql id, or module, etc.
Lei
DBspeed http://www.dbspeed.com/product.html
From: Hans Forbrich <fuzzy.graybeard_at_gmail.com> To: oracle-l_at_freelists.org
Sent: Saturday, February 4, 2012 11:03 AM Subject: Re: Performance tuning approach for connection pooling apps !
Based on the information detailed, including the performance goals, the platform, the application technique, and versions information provided, my current recommend action would be to rewrite the application.
Barring that, I'd use standard techniques to ensure the database is healthy and statistics are up to date, followed by a standard analysis of critical statements and their relative wait and execution information.
Step 1 - get Diagnostic Pack and use the AWR reports.
/Hans
On 04/02/2012 7:43 AM, Raja Subramaniyan wrote:
> Hi,
> Some one please share your ideas / methods for performance tuning approach
> for Oracle database ( specifically connection pooling application ) . i.e,
> If we are not able to identify the specific session, and the application
> using connection pooling mechanism, what is the best way to resolve
> performance issues.
>
> Thanks
> Raja.S
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 08 2012 - 17:13:59 CST