FAQ
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

Search Discussions

  • Guillermo Alan Bort at Feb 4, 2012 at 4:07 pm
    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.-

    On Sat, Feb 4, 2012 at 11: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
  • Hans Forbrich at Feb 4, 2012 at 7:04 pm
    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
  • Lei Zeng at Feb 8, 2012 at 11:14 pm
    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
    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
  • Gaja Krishna Vaidyanatha at Feb 9, 2012 at 8:00 pm
    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
    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
  • Sriram Kumar at Feb 6, 2012 at 6:03 am
    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


    On Sat, Feb 4, 2012 at 8:13 PM, 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
  • Subodh Deshpande at Feb 6, 2012 at 6:56 am
    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
    On 4 February 2012 20:13, 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


    --
    =============================================
    Love me or Hate me both are in my Favour.
    Love me, I am in your Heart. Hate me, I am in your Mind.
    =============================================


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 4, '12 at 2:44p
activeFeb 9, '12 at 8:00p
posts7
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase