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