Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Performance Degradation at Large Table sizes

Re: Oracle Performance Degradation at Large Table sizes

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 21 Feb 2007 10:41:49 -0800
Message-ID: <1172083309.484649.254720@v33g2000cwv.googlegroups.com>


On Feb 21, 10:35 am, "Frank van Bortel" <frank.van.bor..._at_gmail.com> wrote:
> On 21 feb, 10:24, "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.
>
> Table layout? # of indexes?
>
> > Can you point me to a study on oracle performance?
>
> tpm.org
>
>
>
> > Does RAID have to do with this?
>
> Sure - software based RAID5 would have a severe negative impact.
> What do you have?
>
>
>
>
>
> > I appreciate any initial direction that could help me start narrowing
> > down the problem.
>
> > Thank you.- Hide quoted text -
>
> - Show quoted text -

You need to look at the explain plans for the SQL operations that are having a problem. Is the Oracle provided gather statistics routine in use or was it modified? Have you verified that the statistics are current and being updated properly? You could be having issued related to bind variable peeking or SQL that has been hinted to run a certain way that really should be ran how the CBO wants to run it. You need the plans to know.

It is very possible that the issues your site is experiencing are application design issues that are not noticable with small data quantities. I have seen this a few times.

A few statspack runs along with the plans should prove helpful.

HTH -- Mark D Powell -- Received on Wed Feb 21 2007 - 12:41:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US