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

  • Pretty broad question... google searching "oracle performance" will give you a similarly broad answer. – lazyDBA Jun 29 '11 at 16:37
  • yes, i agree..but you can ask me question to narrow down the problem..i donno what info would be sufficient to solve this problem – sam Jun 29 '11 at 16:39
  • lets take your one example: how are these inserts performed? via sqlldr? via some other external program? Also, what do your DBAs have to say (I assume you aren't a dba) – tbone Jun 29 '11 at 18:25

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

  • And if the problem is CPU, start looking for awful SQL - causing TONS of conversions during joins. CPU generally is the part of a db that is "hardly used". – TomTom May 26 '16 at 17:11

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.


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.


Check out this link: http://www.dba-oracle.com/t_top_reasons_poor_performance.htm


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy