Prove that database is doing fine !!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Prove that database is doing fine !!

  1. #1
    Join Date
    Jul 2000
    Posts
    521
    Hello folks,

    Not sure whether this is typical scenario but still :

    You are a DBA. An huge commercial application is running against a reasonably big ORACLE database. Size close to a TB with 4 instances. On HP-UX ad XP arrays.

    You DO NOT have access to the application. It is a third party application.

    Issue is : things are running pretty slow.
    Task is : Improve them.

    The DBAs feel that the database is doing fine, the application is THE problem. The application guys feel exactly the other way.

    Now, can somebody give me an idea about what can I do to prove that database is doing fine ? There are no bottlenecks ? There are no performance issues from database side ?

    A list of such things is the thing I expect...
    svk

  2. #2
    Join Date
    Nov 2000
    Posts
    212
    look into those docs on metalink:

    1.
    Note:106285.1
    Subject: TROUBLESHOOTING GUIDE: Common Performance Tuning Issues

    2.
    UTLBSTAT / UTLESTAT (commonly known as bstat/estat reports)


    In general, pay attention to wait event interface as it gives a lot about database issues regarding concurency and not only.

    Other things are
    -to identify sessions consuming most of CPU (get it from v$sesstat);
    -identify work intensive SQL statements (get it from v$sql_area).

    I would start tuning with abnormaly long wait events as well as identifying with intensive SQL. Intensivce SQL is both frequently executed and with many block gets per execution.

    Yet another big issue is (fragmented) shared pool. Application misbehavour or shared pool size misconfiguration can cause dramatic effect.

    db_buffers pool sizing(not too small at least) is very important too, as caching is what really helps in multyuser environment.

    Of course, fresh start database or calculate delta values to have a picture of what is going on during specific time interval.

    Remember, that some slowlyness can be cause by Oracle optimizer bugs or failure to prepare production database for Oracle's Cost Based Optimizer (like missing, or, on contrary, too many or too accurate statistics - issue known as overlearning).

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    In that case I would try to figure out at the network level how efficient are the packets are sent and how many hops they do using tracert.

  4. #4
    You can use Quest Spotlight to verify "easily" if there aren't bottlenecks.
    UTLBSTAT/..ESTAT gives you the same but a little "harder"
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    as for database, I forgot one more tool:
    use SQL trace if you know what session is in problem to find exact statistics for every SQL statement and its wait statistics too.
    Best of all, enable SQL trace via 1046 events, Level 12.

    For more on SQL Trace read this article (find it on trivadis.com)

    Subject Tracing Bind Variables and Waits
    Author Christian Antognini (christian.antognini@trivadis.com)
    Type of Info Technical Info, Oracle8
    Source Practical experience


  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If you have Enterprize Manager or any other third party Tool to monitor Performance take performance overview and see where the problem and which component of the database is culprit. If nothing is shown over there, it will be the problem with application or SQL of application.

    Do the same on the network and server and run "vmstat" or sar and see whats going on with the system activity. If there is no problem with OS or Databse or network, where else will be the problem ??? Definitely with the application.

    Hope give some idea to go about...



    Reddy,Sam

Posting Permissions

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



Click Here to Expand Forum to Full Width