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.

Sorry the database is oracle 10g

On 21 feb, 10:24, "enigma" <maboun...@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.

On Feb 21, 10:35 am, "Frank van Bortel" <frank.van.bor...@gmail.com>
wrote:
> On 21 feb, 10:24, "enigma" <maboun...@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 --

On Feb 21, 1:24 am, "enigma" <maboun...@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

On 21.02.2007 19:41, Mark D Powell wrote:
> 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.

Since the difference between fast and slow is 1ms the variance could
also be caused by other activity (log writing, EM activity) going on
concurrently or just different access times on the disk, couldn't it?

Kind regards

robert

On Feb 22, 5:29 am, Robert Klemme <shortcut...@googlemail.com> wrote:
> On 21.02.2007 19:41, Mark D Powell wrote:
>
> > 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.
>
> Since the difference between fast and slow is 1ms the variance could
> also be caused by other activity (log writing, EM activity) going on
> concurrently or just different access times on the disk, couldn't it?
>
> Kind regards
>
> robert

Robert, you have a very good point. I do not know how I missed the
time difference being so small with only two posts between the OP and
my response. Other activity is a definite possibility as I think is
free space management overhead and buffer pool management overhead.
Some of these inserts are going to go into newly formatted blocks and
others are going to fill up these blocks. Some inserts are going to
require new extents be allocated to the target table/indexes, some are
not. Some of these inserts are going to have to wait for a free block
to be fetched into the buffer pool, some are not.

enigma, a series of statspack reports should help you determine if
part of what you are seeing are related to buffer pool management
related activities: DBWR make free requests, summed dirty queue
length, etc ....

You can monitor dba_segments to see how often the target table/indexes
are allocating extents. Provided you are using locally managed
tablespaces the cost should not be too high but I believe you will
find that uniform extent allocation has a small advantage over auto-
allocate (less logic involved). If you are using dictionary mangement
that is a definite potential time consumption problem.

HTH -- Mark D Powell --

On Feb 22, 2:50 pm, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
> On Feb 22, 5:29 am, Robert Klemme <shortcut...@googlemail.com> wrote:
>
> > On 21.02.2007 19:41, Mark D Powell wrote:
>
> > > 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.
>
> > Since the difference between fast and slow is 1ms the variance could
> > also be caused by other activity (log writing, EM activity) going on
> > concurrently or just different access times on the disk, couldn't it?
>
> > Kind regards
>
> > robert
>
> Robert, you have a very good point. I do not know how I missed the
> time difference being so small with only two posts between the OP and
> my response. Other activity is a definite possibility as I think is
> free space management overhead and buffer pool management overhead.
> Some of these inserts are going to go into newly formatted blocks and
> others are going to fill up these blocks. Some inserts are going to
> require new extents be allocated to the target table/indexes, some are
> not. Some of these inserts are going to have to wait for a free block
> to be fetched into the buffer pool, some are not.
>
> enigma, a series of statspack reports should help you determine if
> part of what you are seeing are related to buffer pool management
> related activities: DBWR make free requests, summed dirty queue
> length, etc ....
>
> You can monitor dba_segments to see how often the target table/indexes
> are allocating extents. Provided you are using locally managed
> tablespaces the cost should not be too high but I believe you will
> find that uniform extent allocation has a small advantage over auto-
> allocate (less logic involved). If you are using dictionary mangement
> that is a definite potential time consumption problem.
>
> HTH -- Mark D Powell --

I would also recommend reading up on freelist contention, if the slow
insert times are associated with multiple processes inserting
simultaneously. A good article by Howard Rogers on Dizwell.com:
http://www.dizwell.com/prod/node/541
You might also need to look at partitioning depending on what the
extended trace tells you. Which reminds me, have a look at the OraSRP
(Session Resource Profiler) https://twiki.cern.ch/twiki/bin/view...LTraceAnalysis