From oracle-l-bounce@freelists.org Tue Mar 29 08:14:42 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2TEEgEM006258 for ; Tue, 29 Mar 2005 08:14:42 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2TEEdem006238 for ; Tue, 29 Mar 2005 08:14:39 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3DB5B89BAE; Tue, 29 Mar 2005 08:12:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11789-04; Tue, 29 Mar 2005 08:12:45 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ACB6E89C53; Tue, 29 Mar 2005 08:12:44 -0500 (EST) Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=HI9fO8ULjVuJLLFrBk76UGqYXoyOVcqV5pmdI1/ieyWQS5S76yP5K9N1LxMEvXk3T3921fjY1rLyojxO5FPB4DZ+VIpBHVAcYSeV4YwXFk86UUHfYUTx79OGtxetpQ6CgGuItUI1kEHh8qcL+orVHFmikhhZE9frxSoEfx+htng= ; Message-ID: <20050329131056.21473.qmail@web52606.mail.yahoo.com> Date: Tue, 29 Mar 2005 05:10:56 -0800 (PST) From: Martic Zoran Subject: Re: ASSM in 10g RAC doesnt seem work that well To: Jonathan Lewis , Christo Kutrovsky Cc: oracle-l In-Reply-To: 6667 MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 17773 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: zoran_martic@yahoo.com Precedence: normal Reply-To: zoran_martic@yahoo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.5 required=5.0 tests=AWL,FORGED_YAHOO_RCVD autolearn=no version=2.60 X-Spam-Level: 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 *********** 1) 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 2) 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 :) 3) 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 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 > 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