| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: db_file_multiblock_read_count and performance
On Tue, 7 Dec 2004 06:58:25 -0600, Post, Ethan <Ethan.Post_at_ps.net> wrote:
> This kind of brings up an interesting thought. There is the script on
> Ixora to test the largest MBR size and then you are suppose to set the
> value to that, maybe it would be a better practice to generate a huge
> table, run tests at different sizes then set. In theory the largest
> size possible would be fastest but it would be interesting to find out
> if this always was true. =20
>
The result of the script multiblock_read_test.sql will tell you the size of the IO that is being performed by your OS. This is not necessarily what you would set DBFMBRC to.
On my linux box at work the 'ideal' would be 128. Keep in mind that the value of DBFMBRC will influence the CBO. You may find it favoring FTS more often than you would like.
Below are the results of doing a full table scan on a box here at home that does 32 k block reads. This test was done on raw disk.
Note the difference in "physical read IO requests and "physical reads
cache prefetch"
for each run. The first run is for DBFMBRC = 1, the second is for 32.
23:44:29 sherlock - jkstill_at_ts11 SQL> @th
49.234465 secs
47.86633 secs
PL/SQL procedure successfully completed.
23:46:10 sherlock - jkstill_at_ts11 SQL> @run_stats
NAME RUN1 RUN2 DIFF ---------------------------------------- ---------- ---------- ---------- STAT...opened cursors current 2 3 1 STAT...user commits 0 1 1 STAT...session logical reads 25353 25352 -1 STAT...consistent gets 24633 24632 -1 STAT...change write time 1 2 1 STAT...calls to kcmgas 0 1 1 STAT...deferred (CURRENT) block cleanout 5 4 -1applications
STAT...cleanout - number of ktugct calls 10 9 -1 STAT...heap block compress 5 4 -1 LATCH.ASM network background latch 9 10 1 LATCH.KWQMN job cache list latch 0 1 1 LATCH.compile environment latch 0 1 1 LATCH.job_queue_processes parameter latc 1 0 -1h
LATCH.library cache lock allocation 0 1 1 LATCH.FAL request queue 1 0 -1 LATCH.archive control 1 0 -1 LATCH.Consistent RBA 9 8 -1 LATCH.session idle bit 0 1 1 STAT...IMU Flushes 1 0 -1 STAT...active txn count during cleanout 4 3 -1 STAT...calls to get snapshot scn: kcmgss 85 84 -1 STAT...calls to kcmgcs 9 8 -1 STAT...messages sent 2 3 1 STAT...consistent gets from cache 24633 24632 -1 STAT...consistent gets - examination 10 9 -1 STAT...enqueue releases 0 2 2 LATCH.dml lock allocation 1 3 2 LATCH.session allocation 0 2 2 LATCH.active checkpoint queue latch 21 18 -3 LATCH.archive process latch 18 15 -3 LATCH.sort extent pool 4 1 -3 LATCH.parallel query alloc buffer 4 8 4 LATCH.redo writing 75 70 -5 STAT...hot buffers moved to head of LRU 6 0 -6 STAT...free buffer inspected 21254 21248 -6 STAT...Cached Commit SCN referenced 24102 24096 -6 LATCH.active service list 81 73 -8 STAT...commit cleanouts 0 9 9 STAT...commit cleanouts successfully com 0 9 9pleted
LATCH.channel operations parent latch 160 150 -10 LATCH.file cache latch 12 0 -12 LATCH.Memory Management Latch 240 225 -15 LATCH.redo allocation 42 27 -15 LATCH.threshold alerts latch 16 1 -15 LATCH.messages 346 328 -18 LATCH.library cache lock 37 18 -19 LATCH.In memory undo latch 16 36 20 LATCH.undo global data 35 12 -23 LATCH.shared pool 69 44 -25 LATCH.cache buffers chains 74245 74273 28 LATCH.library cache pin 109 78 -31 NAME RUN1 RUN2 DIFF ---------------------------------------- ---------- ---------- ---------- LATCH.checkpoint queue latch 324 288 -36 LATCH.JS queue state obj latch 360 324 -36 LATCH.cache buffers lru chain 21276 21313 37 STAT...redo entries 662 708 46 LATCH.library cache 153 106 -47 STAT...recursive cpu usage 177 243 66 STAT...physical reads 21226 21295 69 STAT...physical reads cache 21226 21295 69 LATCH.SQL memory manager workarea list l 1078 1009 -69atch
STAT...free buffer requested 21232 21301 69 STAT...undo change vector size 46344 46256 -88 STAT...user I/O wait time 10 100 90 LATCH.object queue header operation 42528 42632 104 LATCH.row cache objects 143 15 -128 LATCH.enqueues 854 716 -138 LATCH.enqueue hash chains 865 727 -138 LATCH.simulator hash latch 6169 5948 -221 STAT...redo size 197020 198124 1104 LATCH.simulator lru latch 2670 1340 -1330 LATCH.multiblock read objects 0 1346 1346 STAT...IMU undo allocation size 0 14956 14956 STAT...IMU Redo allocation size 0 20444 20444 STAT...physical read IO requests 21226 673 -20553 STAT...physical reads cache prefetch 0 20622 20622 LATCH.OSM map operation hash table 42460 1688 -40772 STAT...session pga memory -65536 327680 393216
77 rows selected.
05:56:42 sherlock - jkstill_at_ts11 SQL> select blocks from user_tables 05:58:01 2 where table_name = 'DBFMBRC' 05:58:07 3 /
BLOCKS
24741
1 row selected.
05:58:07 sherlock - jkstill_at_ts11 SQL>
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 07 2004 - 08:06:01 CST
 |
 |