Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: mbrc (was: buffer cache - once again)

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

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Wed, 14 May 2003 12:07:07 -0800
Message-ID: <F001.00598C15.20030514120707@fatcity.com>


Here's what I am seeing for the different multi block counts ranging from 1 to 128 (sorry lines are probably wrapped):

dfmbrc cpu LIO (cr+cu) elapsed disk #I/O calls
(times waited) Max. Wait

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

------------------------- ---------
  1    1.32  21,238      1.32    20,969        
scattered - 1,329      0.10
					        sequential-10,482      0.12
  2    0.64  10,619      0.62    10,551        
scattered - 5,270      0.11
					        sequential-    11      0.00
  8    0.50  10,619      0.49    10,488        
scattered - 1,329      0.10
					        sequential-     1      0.00
 16    0.51  10,620      0.50    10,608        
scattered -   680      0.12
 32    0.97  10,619      0.96    10,512        
scattered -   345      0.50
 64    1.04  10,619      1.02    10,544        
scattered -   183      0.56
 96    1.03  10,619      1.00    10,576        
scattered -   170      0.54
128    1.01  10,619      0.99    10,608        
scattered -    99      0.56

or in the form of resource profile:

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

dfmbrc=2

SQL*Net message from client                   48.12s
98.5%               3       16.038591s               0
  36.73s
CPU time                                       0.64s 
1.3%           10619        0.000060s               0 
  0.00s
db file scattered read                         0.12s 
0.2%           10540        0.000011s            5270 
  0.00s
SQL*Net message to client                      0.00s 
0.0%               3        0.000014s               0 
  0.00s
db file sequential read                        0.00s 
0.0%              11        0.000018s              11 
  0.00s

dfmbrc=8

SQL*Net message from client                   14.51s
96.0%               3        4.836885s               0
   8.47s
CPU time                                       0.51s 
3.4%           10619        0.000048s               0 
  0.01s
db file scattered read                         0.10s 
0.7%           10487        0.000010s            1329 
  0.00s
SQL*Net message to client                      0.00s 
0.0%               3        0.000005s               0 
  0.00s
db file sequential read                        0.00s 
0.0%               1        0.000019s               1 
  0.00s

dfmbrc=16

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

dfmbrc=32

SQL*Net message from client                   51.02s
97.2%               3       17.006373s               0
  42.52s
CPU time                                       0.97s 
1.8%           10619        0.000091s               0 
  0.00s
db file scattered read                         0.50s 
1.0%           10512        0.000048s             345 
  0.00s
SQL*Net message to client                      0.00s 
0.0%               3        0.000004s               0 
  0.00s

dfmbrc=64

SQL*Net message from client                    7.48s
82.4%               3        2.493831s               0
   3.84s
CPU time                                       1.04s
11.5%           10619        0.000098s               0
   0.00s
db file scattered read                         0.56s 
6.2%           10544        0.000053s             183 
  0.01s
SQL*Net message to client                      0.00s 
0.0%               3        0.000004s               0 
  0.00s

dfmbrc=96

SQL*Net message from client                    7.53s
82.7%               3        2.508483s               0
   4.08s
CPU time                                       1.03s
11.3%           10619        0.000097s               0
   0.00s
db file scattered read                         0.54s 
6.0%           10576        0.000051s             170 
  0.01s
SQL*Net message to client                      0.00s 
0.0%               3        0.000005s               0 
  0.00s

dfmbrc=128

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

So, even though Oracle can execute multiblock I/O in batches of up to 128 blocks at a time here (on cooked fs), from this output is seems that 8-16 blocks is the optimal? Or is there some other criteria I am missing? Two things to note. One is 1,329 scattered reads, resulted in 10,487 blocks read with dfmbrc=1. How's that? Shouldn't number of I/Os and number of blocks be the same in this case? (or Why there are scattered reads at all?)
Second thing is the latency of the PIO calls - they are quite cheap at dfmbrc 1-16 (~10 mcsec), but than become some 5 times more expensive with higher dfmbrc.

Also there is something a bit unusual I think about tkprof output for dfmbrc=1 is (different from the rest) as it shows 2 parse, 2 execute and 4 fetch counts, as well as # of rows returned (everything is doubled). Is this something to do with sqlplus arraysize?



select /*+ full(t) nocache(t) noparallel(t) */ count(1)
from
 t_source$ t
call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2      0.00       0.00          0        
 0          0           0
Execute      2      0.00       0.00          0        
 0          0           0
Fetch        4      1.32       1.32      20969     
21238          0           2

------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        8      1.32       1.32      20969     
21238          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 47 (BORIS)

Rows Row Source Operation



      1 SORT AGGREGATE (cr=10619 r=10481 w=0 time=823332 us)
 124979 TABLE ACCESS FULL T_SOURCE$ (cr=10619 r=10481 w=0 time=695494 us)

Rows Execution Plan



      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)

 124979 TABLE ACCESS (FULL) OF 'T_SOURCE$' Elapsed times include waiting on following events:
  Event waited on                             Times  
Max. Wait Total Waited

Thank you,
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 Wed May 14 2003 - 15:07:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US