Oracle-L: mbrc (was: buffer cache

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Tue, 13 May 2003 10:36:59 -0800
Message-ID: <F001.00597D2D.20030513103659@fatcity.com>

On somewhat related topic (well, not exactly and hence change of the subject line)

I was also playing with Steve's multiblock setting to see how far can I push it/how extent boundaries affect it/how CBO changes its mind with regard to execution plan etc - all the fun stuff (BTW somebody mentioned here that in 9i there's another parameter that compensates for the high multiblock values, so messing with optimizer_index_* is not the only alternative. Does anybody know what it is?)

What I see here is something that I can't seem to find any reasonable explanation for. With mbrc set at 16 it seems to work (according to statistics) twice faster than with mbrc set at 128 (well, actually set at 1000, but "correted" by Oracle to 128). Plans are the same - FTS for both (due to hint or lack of limiting clause).

Environment:
Oracle 9.2.0.2 on Mandrake 9.0 (kernel 2.4)

Tkprof-ed output:
mbrc=16

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 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.51 0.50 10608 10619 0 1
------- ------ -------- ---------- ----------
---------- ---------- total 4 0.51 0.50 10608 10620 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 47

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

Poor man's substitute for Cary's resorce profiler (I am pre-ordering my copy of the book now):

mbrc=16

---
                         Wait Event              
Duration         # Calls         Dur/Call      Wait
Times Max Wait


-----------------------------------

---------------------- ---------------

---------------- --------------- -------- 

SQL*Net message from client                   62.99s
99.0%               3       20.995993s               0
  40.13s
CPU time                                       0.51s 
0.8%           10620        0.000048s               0 
  0.00s
db file scattered read                         0.12s 
0.2%           10608        0.000011s             680 
  0.00s
SQL*Net message to client                      0.00s 
0.0%               3        0.000005s               0 
  0.00s




mbrc=128
---
                         Wait Event              
Duration         # Calls         Dur/Call      Wait
Times Max Wait


-----------------------------------

---------------------- ---------------

---------------- --------------- -------- 

SQL*Net message from client                    9.52s
85.8%               3        3.174709s               0
   4.91s
CPU time                                       1.01s 
9.1%           10619        0.000095s               0 
  0.00s
db file scattered read                         0.56s 
5.1%           10608        0.000053s              99 
  0.02s
SQL*Net message to client                      0.00s 
0.0%               3        0.000004s               0 
  0.00s



If you've read so far - thank you. I must be missing
something obvious here. Can somebody enlighten me?

Boris Dali

______________________________________________________________________ 
Post your free ad now! http://personals.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: boris_dali_at_yahoo.ca

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 - 13:36:59 CDT

mbrc (was: buffer cache - once again)