Re: Performance tuning approach for connection pooling apps !

  • From: Lei Zeng <leizeng2003@xxxxxxxxx>
  • To: "fuzzy.graybeard@xxxxxxxxx" <fuzzy.graybeard@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "raja.s28@xxxxxxxxx" <raja.s28@xxxxxxxxx>
  • Date: Wed, 8 Feb 2012 15:13:59 -0800 (PST)

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@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx 
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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>

--
//www.freelists.org/webpage/oracle-l

--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2012