Re: Optimal DB_BLOCK_SIZE ??

I hope that I can be of help.

We are running Solaris 2.4, Oracle 7.1.6 on a SUN 12-way. We too, are supporting a decision support database that exceeds 200Gb. We have 1Gb memory and use slightly more than 25% of it for the SGA. We have only 100 tables but, the window to the customer comes in the form of a view usually encompassing joins between as many as 10 tables, some of which exceed 15Gb or hundreds of millions of rows. We are Successfull as access times are absolutely critical and customers are happy.

Under Solaris, there are only two OS blocks sizes to choose from 4k or 8k. Since you speak of 16k OS block sizes I have to assume you are not using Solaris. We are currently setup under the default which is 8k and having a fragment block size of 1k.

In all honesty, I was completely unaware that Oracle 7.1.x permitted 16k blocks, however we have configured our database to use 8k blocks via DB_BLOCK_SIZE upon database creation. If the 16k database block size were not a myth, and we were not in a Production environment, we certainly would experiment with that combination. Another consideration to make which has not been mentioned, is what your memory constraints are. I question the feasibility of setting a database block size so large that you find your SGA paging everytime one of your hundreds of customers executes a query. As you know PAGING=I/O and this is not the effect I believe you're looking for.

In short, 2k database blocks sounds like it is far too small for your implementation. I have spoke with individuals having much smaller instances, utilizing 8k database blocks and the only issue they complain about is technical support, or should I say Lack of but then again, who does'nt

On the issue of sorting, once again I ask what are your memory constraints ? The SORT_AREA_SIZE of your SGA will truly be the hero in this task as well as striping your TEMPORARY tablespaces. As an aside, we have utilized the Parallel functionality for recreating indexes and have seen the time required cut in half. V7.2 promises to be even more exciting.

I certainly hope this has been of help Received on Wed Jul 26 1995 - 00:00:00 CEST