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@xxxxxxxxxxxxxxxxxx>
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