| The Oracle DB_BLOCK_SIZE parameter is the like page size in Rdb. According to
the Oracle V6 dba manual, this parameter can have a value of 512-8096 depending
on the operating system. Specifically (and quoting from the DBA guide):
"The size in bytes of Oracle database blocks. Typical values are 2048
and 4096. The value for DB_BLOCK_SIZE in effect at CREATE DATABASE
time determines the size of the blocks; at all other times the value
must be set to the original value."
On VMS, all customers that I know use 2048 (4 vms disk blocks). Oracle
usually refers to these as oracle blocks. Many of the other parameters
are a function of some multiplier and DB_BLOCK_SIZE. As you are probably
finding out, tuning Oracle is tough. One of the reasons is that on VMS,
there are four different values you must use on different parameters. For
example, a parameter can require Oracle blocks, VMS blocks, total bytes,
or megabytes. My customer who has been using Oracle V6 for over a year
STILL gets confused. Maybe this is why they have a dba staff of 5 for an
$80m company !!!
So, basically DB_BLOCK_SIZE is roughly equivalent (I'd never say the
same) to Rdb's page size. To answer your question, I'm not even sure
Oracle would run under VMS with anything under 2048 and I don't think
there is any relationship between disk cluster size and Oracle cluster
size. That would be a good question for the Boys from Belmont. Based
on the way you asked the question, I would guess your problem with i/o
is caused by simply not having enough disk arms, disk controller too
slow (as in MV2000), or simply not enough horsepower. Keep in mind
that, unlike Rdb, Oracle needs lots of ALL of these. (Even though
Oracle does not use RMS, the disk can still be fragmented so I woulnd't
ignore this obvious performance hit.)
Here's what Oracle recommends as a tuning sequence taken directly from the V6
Performance Tuning Guide. (Note the order that Oracle recommends and you can
get a feel for where they are weak.)
Step 1 - Tuning SQL statements and applications
This chapter discuss tuning the SQL statements to avoid parsing, one
of the biggest Oracle performance pigs and the reason PL/SQL was
invented. Indexes, PL/SQL, clusters (mixed pages not RMS), array
processing (hhmmmm, repeating groups are not allowed by the
relational model !!) are all mentioned as areas of interest. One that
you may be interested in is DB_FILE_MULTIBLOCK_READ_COUNT parameter
which specifies how many blocks to read during one I/O IF and only IF
Oracle is doing a sequential scan of the table(s).
Step 2 - Tuning memory allocation
What a suprise !!! This deals with the SGA area, buffers, data
dictionary, and per-process work areas called context areas. They
again mention parsing of SQL statements. BTW, my customer tried SGA
areas of 1mb, 3mb, and 15mb and did not see significant performance
increases. This was because his application kept the SGA shared
cache dirty all the time.
Step 3 - Tuning i/o
Reduce disk contention by spreading individual files around,
especially the database files and redo log files. BTW, redo log
files can be a bottleneck because unlike the database pages, the
redo logs ARE written to at each commit AND there's only one active
at any given time. Other items mentioned include separating, tables
and indexes, altering the % free and % used parameters, avoiding
dynamic space management (within a storage area).
Step 4 - Tuning contention
The big thing here is tuning the rollback segments which are similar
to Rdb's RUJ and SNP combined. Because RUJ and SNP data can be spread
over many rollback segments during a read_only transaction, it is really
tough to predict where your dios are going to be if you have created
rollback segments on many disks. There are a whole host of timing
and value parameters that have to do with how often and how large a
data structure can be before flushing to disk. Most of these apply
to redo logs and database blocks.
Hope this helps. -- gerry
|
| I'm not familiar with Oracle, but assuming DB_BLOCK_SIZE is the same as
the database page size, I'll try to share my understanding of the
relationship between disk cluster size and database page size.
For example if the page size is 4096 and the cluster size is 4096, then
for every I/O to the disk a full DB page will be accessed. This
situation in general reduces I/O and improves performance.
An important factor in taking advantage of this scenario is to put DB
pages on one disk and programs etc. on another disk (with a smaller
cluster size).
|
|
.3, thank you for your reply.
My understanding is that there is no relationship between the
db_block_size and the disk cluster_size, as far as disk reads and
performance are concerned. The cluster_size parameter comes into
play with file extensions/creations, not read operations.
However, ORACLE has convinced my customer otherwise, and the customer
feels his performance problems are due to the fact that we have
disk cluster_size set to 3, not 4. Always a battle.....
Kathy
|