Choosing a block size for the P6 PMDB database is not a difficult task. In fact, taking the default of 8k is going to be just fine. Block size is one of those things that is always hotly debated. Everyone has their personal preference and can sight plenty of good reasons for their choice. To add to the confusion, Oracle supports multiple block sizes withing the same instance. So how to decide and what is the justification?
Like most OLTP systems, Oracle Primavera P6 has a wide variety of data. A typical table's average row size may be less than 50 bytes or upwards of 500 bytes. There are also several tables with BLOB types but the LOB data tends not to be very large. It is likely that no single block size would be perfect for every table. So how to choose?
My preference is for the 8k (8192 bytes) block size. It is a good compromise that is not too small for the wider rows, yet not to big for the thin rows. It is also important to remember that database blocks are the smallest unit of change and caching. I prefer to have more, individual "working units" in my database. For an instance with 4gb of buffer cache, an 8k block will provide 524,288 blocks of cache.
The following SQL*Plus script returns the average, median, min, and max rows per block.
column "AVG(CNT)" format 999.99
set verify off
select avg(cnt), median(cnt), min(cnt), max(cnt), count(*)
from (
select
dbms_rowid.ROWID_RELATIVE_FNO(rowid)
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
, count(*) cnt
from &tab group by
dbms_rowid.ROWID_RELATIVE_FNO(rowid)
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) )
Running this for the TASK table, I get this result on a database with an 8k block size. Each activity, on average, has about 19 rows per block.
Enter value for tab: task
AVG(CNT) MEDIAN(CNT) MIN(CNT) MAX(CNT) COUNT(*)
-------- ----------- ---------- ---------- ----------
18.72 19 3 28 415917
I recommend an 8k block size for the P6 transactional database. All of our internal performance and scalability test are done with this block size. This does not mean that other block sizes will not work. Instead, like many other parameters, this is the safest choice.
