On Feb 22, 2:50 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> 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 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
Received on Fri Feb 23 2007 - 08:46:52 CST
