Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jul 2 '11 at 2:37

This question came from our site for professional and enthusiast programmers.

    
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

4 Answers 4

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
thanks JK0124, i'll go thru the link –  sam Jun 29 '11 at 16:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.