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_at_yahoo.com> To: "Christo Kutrovsky" <kutrovsky.oracle_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> 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