Re: Performance tuning approach for connection pooling apps !

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Thu, 9 Feb 2012 11:58:44 -0800 (PST)
Message-ID: <1328817524.59530.YahooMailNeo_at_web83604.mail.sp1.yahoo.com>

Dear List,
Not sure whether this has been mentioned by someone else and I just missed it. Apologies if this is being repeated -

Oracle 10g introduced a new feature called "End-to-End Monitoring" just for this very purpose. It has been a few months since I have had a need to use this, but here are the basic high-level steps:

1. Map the App Login_ID for an application session to a Client_ID -- This is done on the App Tier, this is manual, vendor-specific and this requires some work. Should be available for most App Servers  - Weblogic, WebSphere etc. For the purposes of this discussion, let's say the relevant Client_ID is resolved "client123".
2. Once the Client_ID is determined, EXEC DBMS_MONITOR.client_id_trace_enable (client_id => 'client123') in any privileged session, to turn on trace on a given application session (client_ID). If certain MODULE(s) or ACTION(s) need to be traced that can be done too with the relevant procedures, but the application needs to be instrumented using DBMS_APPLICATION_INFO for that.
3. In a connection pooling environment, we know that a given application session (Client_ID) can/will use different Oracle sessions, so #2 will create multiple trace files in USER_DUMP_DEST.
4. Wait the required time to trace/capture the workload of interest.
5. Turn off tracing for the given Client_ID - EXEC DBMS_MONITOR.client_id_trace_disable(client_id => 'client123');  
6. Run the utility "trcsess" inside USER_DUMP_DEST for the Client_ID that was traced -- trcsess will parse through the trace files in USER_DUMP_DEST and find all trace files that have the Client_ID. With this you will create an "aggregate trace file", like this :

$ trcsess output=aggr.trc * clientid=client123 * * * *      -- The "*" indicates no filter/value for the given positional parameter, so this command is for all sessions, for clientid=client123, for all services, actions, modules and trace file names. -- Full syntax - trcsess

[output=<file_name>] [session=<sid>] [clientid=<clientid>]
[service=<service_name>] [action=<action_name>]
[module=<module_name>] <tracefile_names>

7) Run tkprof on the aggregated trace file - tkprof aggr.trc tune.txt sys=no sort=prsela,exeela,fchela

8) Enjoy your findings...rinse and repeat as necessary :)  
It will be good to know if this still works :). Hope this helps!

Cheers,

Gaja

Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle Insights:Tales of the Oak Table - http://www.apress.com/book/bookDisplay.html?bID14 Co-author:Oracle Performance Tuning 101 - http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766

---

 From: Lei Zeng <leizeng2003_at_yahoo.com> To: "fuzzy.graybeard_at_gmail.com" <fuzzy.graybeard_at_gmail.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>; "raja.s28_at_gmail.com" <raja.s28_at_gmail.com> Sent: Wednesday, February 8, 2012 3:13 PM Subject: Re: Performance tuning approach for connection pooling apps !  

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-l
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 09 2012 - 13:58:44 CST