Just add-on.
The script down is for normal heap table with PK and
not IOT as I said.
I also made the mistake by producing 900 duplicates,
so the difference was not that huge as it should be.
I changed the script and do it again without
duplicates that are making things bad for both ASSM
and non-ASSM :)
I have got this on 1GHz Sun with 10.1.0.3:
CPU used by this session
CPU used when call started
ASSM - 164
non-ASSM - 731
Almost 5 times more CPU when doing bulks
On HP-UX 11.00, 360MHz 9.2.0.5:
CPU used by this session
CPU used when call started
ASSM - 361
non-ASSM - 518
On Solaris 7, 450MHz 9.2.0.5:
CPU used by this session
CPU used when call started
ASSM - 194
non-ASSM - 324
Solaris 9, 450MHz, 10.1.0.3 RAC with ASM:
CPU used by this session
CPU used when call started
ASSM - 312
non-ASSM - 1521
Solaris 9, 450MHz, 10.1.0.3 RAC with ASM:
CPU used by this session
CPU used when call started
2 processes running on different instances
ASSM - 440
non-ASSM - 2359
1 process
ASSM - 369
non-ASSM - 1562
CONCLUSION
- ASSM is using less CPU over non-ASSM in all tests
Different tests, the same behaviour, ASSM is just
spending less CPU.
I did, where I pushed it with the fastest bulk inserts
and so removing other bottlenecks causing this
difference to be deminished
- 10g improved ASSM or worsen something else, because
the difference in 10g is 4-5 times while in 9i is not
even 2 :)
Also probably because of additional statistics
overhead in 10g it is slower (using more CPU) then 9i
as we all know as true :)
- In high concurrent environments (as nicely Jonathan
said earlier) more cpu spinning causing CPU time to
increase. It increased for me as coming from 1 to
multi session environment. But it increased for both
ASSM and non-ASSM.
I could not correlate any statistics consistently to
why is this the case when comparing ASSM and non ASSM.
When comparing 1 sessiong against multiple I can find
out some diffferences. Basically I had worse
statistics for ASSM in most cases as expected per
Jonathan's mail telling about the algorithm behind
(like buffers, ...).
do not forget that these numbers in your special case
should be evaluated.
But I learned the lesson how drastically these two
things can change the performances (CPU at least) of
high speed DML:
1) ASSM
2) spinning (thanks Jonathan, I totally forget to get
this into account while thinking about CPU time even
reading that small Internal's book a few times :)
Regards,
Zoran
- Martic Zoran <zoran_martic_at_yahoo.com> wrote:
> 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
>
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 29 2005 - 08:14:42 CST
