[Oracle-l] Performance tuning approach for connection pooling apps !

That is way too broad a question to be answered.
What do you mean by "performance problems"? If you are looking for a
performance tuning guide for Oracle, there are several good books out
there:
Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach
by Sam Alapati et al. is a very good read but can be a bit too specific at
times.
And personally I have Troubleshooting Oracle Performance by Christian
Antognini in my wishlist for when I get my Kindle or Asus tablet.

There are also several websites dealing with Oracle Performance Tuning...
you just have to filter the crappy sites with useless posts entitled
"oracle performance" but really advertise some form of paid service...
which may prove to be somewhat of a challenge.

Now, whenever I get a "call" (ticket, e-mail, etc) from a dev or business
type saying "ZE DATABASE BE SLOW!" (yes, they are cats) I usually ask the
basic "when did the problem start and what changed?". Then I start digging
through AWR reports. We keep 3 months of snapshots so we can pretty much
compare the database load to a previous point where things "worked fast". I
usually look for Full Table Scans of the largest table (the advantage of
having relatively few databases is that I know them very well) and changes
in load (number of connections, time elapsed, etc).

If you wonder about forecasting (i.e. what would happen if we added another
AS with a 400 connections pool) that's a whole other question, but luckily
one that can be answered by Oracle documentation (look at sizing PROCESSES
and SESSIONS parameters in MOS, Oracle Forums, Tahiti and OTN)

hth
Alan.-

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

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@gmail.com>
To: oracle-l@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

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@yahoo.com>
To: "fuzzy.graybeard@gmail.com" <fuzzy.graybeard@gmail.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>; "raja.s28@gmail.com" <raja.s28@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@gmail.com>
To: oracle-l@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

Hi,
I am assuming that you are aware of the potenial operation that is slow
(say 2 out of 10) and you are looking for a mechanism to isolate and
trace/diagnose the operation but faced with the problem that you are not
able to identify the session that corresponds to the slow opertion because
the sessions/connections are pooled in the middle tier.

i can think of 2 strategies and you could use one of them

1) trace all sessions for a period of x mins....5 mins/10 mins and search
the trace files for the keywords of the SQL/table that is involved in the
slow operation and then tkprof the specific trace file
2) make an application change to use dbms_monitor api call for the specific
slow operation and trace

hope this helps

best regards

sriram kumar

hello Raja,
Just whether following thread can be of your use.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22140261281764


thanks..subodh