Oracle-L: Re: mbrc (was: buffer cache

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 May 2003 13:27:02 -0800
Message-ID: <F001.00598152.20030513132702@fatcity.com>

Points to note:

Table size = 10600 - ish

99 * 128 =12672
680 * 16 = 10880

So your 128 block reads were pretty
successful reads, averaging about 107
blocks each.

Is it possible that you have a system
striped as 128K per disk ? This might
be consistent with the side-effects of
contention when all parts of a 128 BLOCK read having to complete (making 8 discs
busy) before the whole read is complete, when a 16 block read need only wait for
one disc at a time.

Was this repeatable, and was there other activity in unrelated areas of the system at the time ?

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June 4th - 6th
____Australia_June 18th - 20th (Perth)
____Australia_June 23rd - 25th (t.b.a)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 13 May 2003 19:36

>
> Tkprof-ed output:
> mbrc=16
> ----
> Rows Row Source Operation
> -------
> ---------------------------------------------------
> 1 SORT AGGREGATE (cr=10619 r=10608 w=0
> time=503618 us)
> 124979 TABLE ACCESS FULL T_SOURCE$ (cr=10619
> r=10608 w=0 time=374988 us)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times
> Max. Wait Total Waited
> ---------------------------------------- Waited
> ---------- ------------
> SQL*Net message to client 2
> 0.00 0.00
> db file scattered read 680
> 0.00 0.12
> SQL*Net message from client 2
> 22.86 22.86
>

>
>
>
> mbrc=128
> ----
>

>
> select /*+ full(t) nocache(t) noparallel(t) */
> count(1)
> from
> t_source$
>
>
> call count cpu elapsed disk
> query current rows
> ------- ------ -------- ---------- ----------
> ---------- ---------- ----------
> Parse 1 0.00 0.00 0
> 0 0 0
> Execute 1 0.00 0.00 0
> 0 0 0
> Fetch 2 1.01 0.99 10608
> 10619 0 1
> ------- ------ -------- ---------- ----------
> ---------- ---------- ----------
> total 4 1.01 0.99 10608
> 10619 0 1
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 47
>
> Rows Row Source Operation
> -------
> ---------------------------------------------------
> 1 SORT AGGREGATE (cr=10619 r=10608 w=0
> time=993698 us)
> 124979 TABLE ACCESS FULL T_SOURCE$ (cr=10619
> r=10608 w=0 time=867098 us)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times
> Max. Wait Total Waited
> ---------------------------------------- Waited
> ---------- ------------
> SQL*Net message to client 2
> 0.00 0.00
> db file scattered read 99
> 0.02 0.56
> SQL*Net message from client 2
> 4.91 4.91
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue May 13 2003 - 16:27:02 CDT

Re: mbrc (was: buffer cache - once again)