Re: Oracle Performance Degradation at Large Table sizes

On Feb 21, 1:24 am, "enigma" <maboun..._at_gmail.com> wrote:
> Hi all,
>
> Environment: HP Proliant. Red Hat AS 3. RAID 5 (3 HD 330 GB each). 8
> GB RAM.
>
> Background: The DB is installed for a GSM operator of a big country
> and has records for all the subscribers in that country. The number of
> subscribers is increasing and we now have some tables that contain
> about 13 million entries. We are experiencing unstable performance on
> insert on all tables. sometimes an insert takes about 1.5 ms and other
> times it goes down to 500 micro seconds.
>
> Does Oracle have problems with large table sizes? Since this problem
> was not there when the number of subscribers was significantly less.
> Can you point me to a study on oracle performance?
>
> Does RAID have to do with this?
>
> I appreciate any initial direction that could help me start narrowing
> down the problem.
>
> Thank you.

To narrow down the problem, you must follow a performance tuning methodology. There are a number of them, including Oracle's own. A lot of people like Cary Milsap's http://www.hotsos.com/e-library/oop.php

Some people might skip all that and start hooting about RAID-5. It might very well be your problem, if you are doing something that causes contention among writers, in particular if you have redo, undo, regular database writes and archiving all going to the same device. Redo and archiving in particular are sensitive to that disk architecture, especially with just 3 disks and a single controller path.

Since your problem is intermittent, it may be due to periodic system activities possibly combined with application inefficiencies (in general, most performance problems are application oriented, but some really thoughtless system configuration can override that). It is not as likely due to the simple size of the tables, unless you have some strange deletion routines (the former was less true in earlier versions of Oracle, but that may also depend on if you've upgraded in certain ways and which exact [ie 10.1.2.0] version of Oracle you use - so post that!). If you have many tens of thousands of extents in the tables there may be more issues so post that, too. You say it is on all tables - how have you determined that?

The first thing you should do is look at the alert log and see if it is complaining about anything. A millisecond doesn't strike me as much of a variance on an insert, so are you really talking about a lot of inserts adding up? Are your cpu's pegged? What does your swapinfo look like? Where exactly are all these inserts coming from?

As Mark suggested, some statspacks may be informative. They will give some clues about what the system is waiting on. Also see the Oracle manual on Performance Tuning to see some other things to look at. Post your init.ora parameters that are non-default. Look for system tables named something like advisor.

jg

--
@home.com is bogus.
http://www.freud.org.uk/indexdream.html