Re: Is "_db_block_cache_protect = true" killing my throughput?

From: Christopher Allen <progplusSPAMBEGONE_at_gte.net>
Date: Thu, 12 Aug 1999 20:53:55 GMT
Message-ID: <DvGs3.525$Bt3.19750@dfiatx1-snr1.gtei.net>

Thanks for the reply. My extremely simple throughput test inserts groups of 10,000 records, following by a single record indicating the number of records inserted per second.

I start by creating a table:
 CREATE TABLE T1 (

        c1                   NUMBER NOT NULL,
        c2                   VARCHAR2(30) NULL,
        c3                   DATE NULL
 )
        PCTFREE 10
        PCTUSED 40
        INITRANS 1
        MAXTRANS 255
        STORAGE

               INITIAL 8092
               NEXT 8092
               PCTINCREASE 1
        )

 ;
 CREATE INDEX T1_XIE1 ON T1
 (

        C1
 );

Then I create the following procedure:

 CREATE OR REPLACE PROCEDURE TEST_INSERT IS   maxloops NUMBER := 10000;
  loopcount NUMBER(6,0) := 0;
  starttime CHAR(5) ;
  endtime CHAR(5) ;
  runtime NUMBER;
  processrate NUMBER(20,10);
 BEGIN
  starttime := TO_CHAR(SYSDATE,'SSSSS');   LOOP
    loopcount := loopcount +1;
    INSERT INTO T1 (C1, C2,C3) VALUES (

      loopcount,
      'TEST ENTRY',
      SYSDATE);

    IF loopcount >= maxloops THEN EXIT; END IF;     END LOOP;
  COMMIT;
  endtime := TO_CHAR(SYSDATE,'SSSSS');
  runtime := TO_NUMBER(endtime)-TO_NUMBER(starttime);   processrate := maxloops / runtime;
  INSERT INTO T1 (C1, C2,C3) VALUES (

      loopcount+1,
      TO_CHAR(processrate, '9999999999')||' records per second',
      SYSDATE);

  END TEST_INSERT;
  /

I then run a simple script that executes the TEST_INSERT procedure numerous times. In the case that I wrote about, the script executed TEST_INSERT 50 times, thereby creating a half-million records.

On the NT server (Oracle 8.0.5, dual 450MHz Pentium IIIs, 320MB RAM, NT SP4), the insert rate starts at 2,000 records per second and gradually decreases as the number of records in the table gets larger; the 50th batch of 10,000 inserts happens at a rate of about 450 records per second.

On the Unix box (Sun 450, dual 400MHz SPARC chips, 512MB RAM), the insert rate starts at 200 records per second, and ends up at around 90.

The parameters you asked about are:
db_block_buffers = 3200
log_buffer = 163840

I tried disabling the $ORACLE_HOME/dbs/init$ORACLE_SID.ora lines for db_block_cache_protect and the four event lines. After bouncing the database, performance was a touch better: 213 records per second, instead of 200. Still, pretty poor. Even my laptop with Personal Oracle does better.

I appreciate all assistance with this!

Christopher

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:934486087.12060.2.nnrp-04.9e984b29_at_news.demon.co.uk...
>
> It is possible, but I didn't think that the Sun had
> the necessary features in the O/S to do anything
> with _db_block_cache_protect or 10049 so they
> shouldn't have any effect.
>
> Since the problem is INSERT time, the 10210 and 10211
> also shouldn't be a problem - these would consume CPU
> only as a block was due to be written (I think).
>
> I would go for the 10235 as the guilty party. Even so a
> factor of 10 seems rather extreme. can you give us an
> idea of the overall scale of the operation -
> inserts per second
> db_block_buffers
> redo parameters
> number of indexes per table
>
> I would knock out all the lines you have told us about, though,
> I don't think 7.3.4 can really qualify as a beta release any more.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Christopher Allen wrote in message ...
> >I learned yesterday that my Sun 450 server's Oracle 7.3.4.0.1
installation
> >has about 1/10th the throughput (with simple INSERT statements) of the NT
> >server sitting next to it running 8.0.5. This surprised me greatly, and
> >I've started investigating why the Sun is so much slower. One thing I
> >noticed was in the $ORACLE_HOME/dbs/init$ORACLE_SID.ora was this section:
> >
> > # FOR BETA RELEASE ONLY. Enable debugging modes. Note that these can
> > # adversely affect performance. On some non-VMS ports the
> db_block_cache_*
> > # debugging modes have a severe effect on performance.
> > _db_block_cache_protect = true # memory protect
> >buffers
> > event = "10210 trace name context forever, level 2" # data block
checking
> > event = "10211 trace name context forever, level 2" # index block
checking
> > event = "10235 trace name context forever, level 1" # memory heap
checking
> > event = "10049 trace name context forever, level 2" # memory protect
> >cursors
> >
> >"Severe effect on performance" is certainly the symptom I'm seeing.
Before
> >commenting out the _db_block_cache_protect parameter, I want to know more
> >about what it does. The Oracle online docs returned no matches to the
> >phrase "db_block_cache". Can anyone tell me what this does, and what the
> >ramifications of disabling it will be?
> >
> >Thanks,
> >Christopher
> >
> >
>
>
Received on Thu Aug 12 1999 - 15:53:55 CDT