Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: What is the approach to solve Database Performance Issues.

  1. #1
    Oracle Administrator
    Join Date
    Feb 2012
    Location
    New Delhi, India
    Posts
    53

    What is the approach to solve Database Performance Issues.

    Answer PLZ.

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    When your database Applications are running slow , first ask the users to be clear on the kind of performance issues that are being faced. The user itself may not give you an accurate detail of the problem , so get into a conference / shared session to see exactly what problem he is complaining about. If there are multiple users involved , get their details of the problem being faced.

    Is the issue specific to a parcticular session , or a specific application or all users are complaining.

    Here is a generic guideline of what you should check :

    1) Check the OS Utilization particularly memory and CPU : use top , vmstat , sar ..
    Get the OS Process Id if a process consumes too much CPU / memory , and check what that process is about.

    2) If you are having some Utilities / Graphical tools for performance monitoring check them for the period when you are encountering the problem.
    If you are using OEM , the graphs and advisors on OEM can help.

    3) If its a particular session , get the SID for the session.

    4) Examine the session waits on the database eg : there could be latch / enqueues / archival / redo /library cache / row cache ..or other issues which can be diagnosed from session waits.
    If its a particular session , examine the session waits for the session - it could be that it is waiting on enqueue or waiting on latches ..or some other issue

    5) Examine the sql for the session and get its execution plan. Check the plan and costs involved to diagnose if the execution plan is proper.
    See when the stats for the relevant tables /schemas were gathered.

    6) If its a particular session , take the trace of the session and then the tkprof

    7) Check AWR Reports for the period where you had performance issues - Sometimes just a view of the AWR report will tell you exactly what the problem is
    Use advisors based on the problem. Eg : if the problem is with an SQL , use the SQL Tuning Advisor.

    8) It could be that some tables are fragmented , or some indexes are unusable , or stats are stale / missing.

    9) Are you having proper steps in place for regular gathering of stats / purging / reorg .

    10) Check the invalids in your database
    If the problem is releted to a newly deployed application , give it to the developers to check.

    11) If the problem is new , assess the recent modifications changes made - any new code deployment / patch which could have caused the problem in the given scenario.

  3. #3
    Oracle Administrator
    Join Date
    Feb 2012
    Location
    New Delhi, India
    Posts
    53
    Thanks....

+ Reply to Thread

Similar Threads

  1. Oracle Database Performance Tuning Training in Delhi or Online (Advanced)
    By Hemant in forum Oracle Training Modules - Database RAC RMAN DataGuard GoldenGate Apps FMW Weblogic OAM OIM ODS ..
    Replies: 0
    Last Post: 04-20-2013, 09:51 AM
  2. How to use hints in Oracle sql for performance
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-25-2012, 03:19 PM
  3. Tools & commands for analyzing server performance.
    By ajaychandi in forum OS for Oracle Administration
    Replies: 0
    Last Post: 03-12-2012, 06:50 PM
  4. RAC Specific Performance Tuning Techniques
    By Hemant in forum RAC Performance,LoadBalancing,TAF,FAN,Troubleshooting
    Replies: 0
    Last Post: 01-09-2012, 06:17 PM
  5. Log Files for Troubleshooting Oracle RAC issues
    By Hemant in forum RAC Performance,LoadBalancing,TAF,FAN,Troubleshooting
    Replies: 0
    Last Post: 12-15-2011, 06:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics