Re: ASSM in 10g RAC doesnt seem work that well

A thought about CPU usage and ASSM.

If you are doing single row inserts - and not using
pl/sql to emulate high throughput - then an insert
against a freelist has to:
    acquire the segment header block to check
    the head of the freelist

    acquire the data block at the head of the
    free list

If you are using ASSM, and insert has to:
    acquire the segment header block to identify the
    'hinted' L2 bitmap block

    acquire the L2 bitmap block to identify the next
    L1 bitmap block
   
    acquire the L1 bitmap block to identify the data
    block
    
    acquire the data block.

As far as CPU is concerned, things could go either way.
If you get contention on the segment header and data
blocks, you could burn CPU on latch spins.  On the
other hand ASSM has to grab four latches one after
the other, rather than two, which is a built-in penalty.


The comment about pl/sql is because there is an
'cursor call' optimisation which keeps various 
blocks pinned for the duration of the call - I did some
tests on 9.2 some time ago, and I think the segment 
header and L2 block were pinned until the PL/SQL
completed.  This may have changed with 10; it may
be different on RAC; it may help to explain some
of Christo's observations about L1 ownership.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






----- Original Message ----- 
From: "Martic Zoran" <zoran_martic@xxxxxxxxx>
To: "Christo Kutrovsky" <kutrovsky.oracle@xxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 29, 2005 9:21 AM
Subject: Re: ASSM in 10g RAC doesnt seem work that well


going to check this thing because it is going to
overcalculate/underestimate all my future capacity
sizing figures, that are going to be affected if this
ASSM/non-ASSM is so different in CPU timing.

Regards,
Zoran

--
http://www.freelists.org/webpage/oracle-l

Other related posts: