Home / server / Questions / 286335
In Process
sam
Asked: 2011-06-30 08:24:03 +0800 CST

Oracle database is very slow - Where to start and how to find the cause?

  • 772

Our Oracle database(11g) is very slow all of a sudden. I donno where to start the debugging process to find what actually is the problem?

I tried generating table stats and looked for locks..but i can't find any information that points me directly to the cause, or maybe i'm looking at the wrong place..

Please guide me on where to start..

Well, I'll define what's Slow :)

Inserts take hell lot of time..even a 100,000 record insert takes abt an hour

oracle oracle-11g

5 Answers

  1. I'd first look at the database server to see what if any bottlenecks there are -- e.g. disk, network, CPU, memory. If there is an underlying hardware problem, this might lead you to it. But if there is an application problem, this is likely to show you the effect but not the cause.

    The most thorough way to approach an Oracle performance problem is to do an extended SQL trace of one or more of the slow sessions, profile them, and see where they're actually spending their time. An excellent source of info on doing this is Cary Millsap's book which you can see the first chapter of here.

    But a less complete way of looking at the same information is to simply query the data dictionary to see what events the slow sessions are waiting on. In cases where something is badly wrong, this will often be just as effective as a complete trace. I would start with running this query several times and see where that leads you:

    select seq#, event, wait_time, seconds_in_wait, state
from v$session
where type='USER'
;

    Another approach to gathering similar info is Tanel Poder's Snapper script

    • 2

  2. If things have been working great for some time, and nothing major has changed (i.e. increased usage, modified indexes, heavy reporting, analytial processing etc.)

    I'd start by checking on the health and performance of your hardware/physical disks.

    Frankly, this is a pretty broad question and there are a lot of unknown's... but since your specific question is where do I start. That's where I would start.

    • 1

  3. Generally speaking, starting from the top level, successively break down where the time is spent. At some time you should reach a level where you see too much time spent where it shouldn't.

    For more detail I highly recommend Cary Millsap's paper Thinking Clearly About Performance.

    • 1
    • 0

  5. Did you license Oracle Diagnostic Pack? If yes, you have access to couple of useful tools: ADDM (Automatic Diagnostic Monitor) and AWR (Automatic Workload repository). The AWR reports may be difficult to understand at first, but they can give invaluable information on what is really happening in the database.

    If you don't have the Diagnostic pack, you'll have still access to Active Session History views.

    You're on the 11g, this means you can start the "DB Control", it is a limited version of Enterprise Manager (Or maybe you have fully licensed Enterprise manager). The DB Control provides web interface, it lets you see performance-related information among other things, it collects a lot of useful information. Again, there is a lot to learn but if there are performance issues, a quick look at the "Performance" tab can give a lot of information already.

    Regards

    • 0