Re: Oracle Performance Degradation at Large Table sizes

On Feb 22, 2:50 pm, "Mark D Powell" <Mark.Pow...@xxxxxxx> wrote:
On Feb 22, 5:29 am, Robert Klemme <shortcut...@xxxxxxxxxxxxxx> 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/PSSGroup/SQLTraceAnalysis

.