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

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Tue, 29 Mar 2005 01:19:22 -0800 (PST)
Message-ID: <20050329091922.29034.qmail@web52603.mail.yahoo.com>

Thanks a lot Jonathan.

Your pointers are always great.
I agree fully with your observation about the algorithms used here.
possible latch spinning problem and ownership of special blocks in RAC may be the Christos problem. Will test out of PL/SQL, but need some time to do that. Anyway I assume a lots of people are using PL/SQL anyway :)

But lets back to the CPU usage again.

I did the test with IOT table now.
For the start I did it only with 1 session. Again 10g with ASM on Solaris 9.
I did the test with bulk DML (inserts) from PL/SQL and got this timing:

CPU used when call started
CPU used by this session (both timing are the same) ASSM - 2,25
Non-ASSM 5,36

The difference is now huge.
The difference in statistics when you remove all non-important statistics are in latching. It was less latching with ASSM.

This is becoming more interesting now.

For clarity I am showing the test I did:

CREATE TABLE "XXX" ("N" VARCHAR2(1000 byte) NOT NULL,

    "COL1" DATE NOT NULL, "COL2" CHAR(100 byte) NOT NULL,
    CONSTRAINT "SYS_C0051159" PRIMARY KEY("N", "COL1", "COL2")
    USING INDEX)
    TABLESPACE "USERS" ; execute runstats_pkg.rs_start('ASSM 1'); declare

        TYPE ntab IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
        TYPE markettab IS TABLE OF DATE INDEX BY BINARY_INTEGER;

	nn ntab;
	n_loop number := 10;
	n_array_size number := 1000;
begin
	for i in 1..n_loop loop
		for j in 1..n_array_size loop
			nn(j) :=
to_char(i)||to_char(j)||'ffffffffffffffffffffffffffffffffffffffffffffffff';
		end loop;
		begin
			forall k in 1..n_array_size save exceptions
				insert into xxx values
(nn(k),trunc(sysdate),'sssssssssssssss');
		exception
			when others then 
				null;
		end;
		commit;
	end loop;

end;
/
execute runstats_pkg.rs_stop('ASSM 1');

Regards,
Zoran

• Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
  >
  > 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
  

  ---

Do you Yahoo!?
Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/

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

Received on Tue Mar 29 2005 - 04:23:05 CST