| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Sizing Oracle Data Blocks
It is very ironic that the Oracle developer must choose a blocksize
when the data warehouse is initially created--a time when knowledge
of system performance is very limited. While it is possible to use
the Oracle import/export utility to change block sizes, too little
attention is given to the proper sizing of database blocks. The
physical block size is set with the DB_BLOCK_SIZE parameter in the
init.ora file. While the default is to have 4 K blocksizes, many
Oracle developers choose at least 8 K blocksizes for large,
distributed data warehouses. Some Remote DBAs believe that 16 K is the best
blocksize, even for OLTP systems that seldom perform full-table
scans. Depending upon the host platform and operating system, Oracle
block sizes may be set from 2 K up to 32 K. The Oracle OS manual
will provide the acceptable ranges for your operating system, but
the generally accepted wisdom is to create your database blocks as
large as your operating system will allow. Remember, minimizing disk
I/O is one of the most important factors in data warehouse tuning,
and the more data that can be read in a single I/O, the faster your
warehouse will perform.
Disk I/O is the single most expensive and time-consuming operation
within an Oracle database. As such, the more data that can be read
in a single I/O, the faster the performance of the Oracle database.
This principle is especially true for databases that have many
reports that read the entire contents of a table. For systems that
read random single rows from the database, blocksize is not as
important--especially with database clusters. An Oracle cluster is a
mechanism whereby an owner row will reside on the same database
block as its subordinate rows in other tables. For example, if we
cluster order rows on the same block as their customer owners,
Oracle will only need to perform a single I/O to retrieve the
customer and all of the order rows. Of course, in a distributed
database where joins take place across different Oracle instances,
clustering cannot be used. The additional I/O will be required to
read the rows individually.
Bear in mind that increasing the
block size of an Oracle database will also affect the number of
blocks that can be cached in the buffer pool. For example, if we set
the DB_BLOCK_BUFFERS init.ora parameter to 8 MB, Oracle will
be able to cache 1000 4K blocks, but only 500 8K blocks.
|
