db_file_multi_block_read_count

[fraze]

We've recently migrated from 7.3.4 to 8.1.7.2 on AIX and some of our batch jobs are taking up to 50% longer to run. We've got high avg waits on 'db file scattered read' and so are looking at db_file_multi_block_read_count.
This set to 16 on both 7.3.4 and 8.1.7.2. One of our DBA's swears that he heard at a User Group seminar that a large MBRC was good in 7.3.4 but should be smaller in 8.1.7.2 due to a change in the internal workings.
Does anyone have any views?

[pipo]

well, to optimize your performances, db_file_multiblock_read_count should be : max_io_size/db_block_size
but at the physical level in Unix, Oracle always reads a minimum of 64kb, so a good recommendation on how to choose db_file_multiblock_read_count is to take :
64kb/db_block_size (8 if you have a block of 8k)

[fraze]

So am I right in saying 8k would be a minimum on AIX (we have an 8k block size). I know the maximum for an 8k block size on 8i is 128K but that doesnt explain why we have such a difference in performance between versions 7 and 8.
I know full table scan reads is only one factor to take into account but was wondering if anyone had experienced significant performance differences between the 2 versions using a similar DB_FILE_MULTIBLOCK_READ_COUNT?

[pipo]

I personnaly haven't experienced performances problems because of db_file_multiblock_read_count, but I've always followed this 64 rule ... try setting it to 8, but I don't know if this will solve your problem (anyway 8 is more than enough providing you don't have 2000 simultaneous connections)
2 other points I could see are :
- carefully audit your SGA and see if it's correctly sized
- be careful that the optimization mode which was RULE by default on 73 is now CHOOSE, I have seen some instances where this "detail" was forgotten, and of course performances were amazingly poor

[fraze]

We did have the optimizer set to choose and you're right it was bad! So its now RULE based. Our SGA area is well sized - we've got decent cache hit ratios but our avg wait for 'db file scattered read' is around 4secs!! We havent changed any parameters in the database but are suffering a 50% performance reduction on some batch jobs after migrating from 7.3.4

[pipo]

if you have that much scattered reads, it might mean that you are doing a lot of full scans on your tables ... anyway I do not see any valuable reason for your performances to decrease by 50% after changing your RDBMS version :(


PS : you can keep the optimizer in CHOOSE mode, if you have no statistics at all computed, it will act exactly as the RULE optimizer, however it is better to be in CHOOSE mode AND to compute (or estimate the statistics)

[pando]

we had performance problem these couple of days after migrating from 7.3.4 to 8.1.7.2 on Tru64

the problem however was the OS kernel settings, so you may check into that

[sambavan]

Pando would you mind sharing the problem and solutions?

On the other hand setting the multi block read count to some higher value on an analyzed schema where the optimizer is choose, could lead to full table scans. Depending on the application, you would want to set the parameter. I.e if you have small tables, then oracle will choose full table scans over index scans, as the full tablescans for it sounded good.

Just curious
Sam

[pando]

it was because these parameters

shmsys:shminfo_shmmax
shmsys:shminfo_shmmin
shmsys:shminfo_shmseg
shmsys:shminfo_shmmni
semsys:seminfo_semmns
semsys:seminfo_semmni
semsys:seminfo_semmsl

the peeps forgot to change them for Oracle

[samdba]

Hi,

We are on 8.0.5/ NT 4.

In report.txt of utlbstat,estat, it shows foll. values.

Event Name Count Tot Time Avg Time
----------------------------------------------------------------------
db file sequential read 71517 54098 .76
db file scattered read 30456 22451 .74

Is if bad or OK?

Thanks,