Re: db_file_multiblock_read_count and performance
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Ethan.Post@xxxxxx
- Date: Tue, 7 Dec 2004 06:04:51 -0800
On Tue, 7 Dec 2004 06:58:25 -0600, Post, Ethan <Ethan.Post@xxxxxx> 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@ts11 SQL> @th
49.234465 secs
47.86633 secs
PL/SQL procedure successfully completed.
23:46:10 sherlock - jkstill@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 -1
applications
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 -1
h
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 9
pleted
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 -69
atch
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@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@ts11 SQL>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
