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: 22 Feb 2007 06:50:13 -0800
Message-ID: <1172155810.563718.36550@v33g2000cwv.googlegroups.com>


On Feb 22, 5:29 am, Robert Klemme <shortcut..._at_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 autoallocate  (less logic involved). If you are using dictionary mangement that is a definite potential time consumption problem.

HTH -- Mark D Powell -- Received on Thu Feb 22 2007 - 08:50:13 CST

Original text of this message

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