Re: Operating System stripping and Parallel Query Option

> For large databases, Oracle recommends a DB_BLOCK_SIZE of 8K and a large
> DB_FILE_MULTIBLOCK_READ_COUNT (32 for example).
>
> 1 - What do you think would be the optimal size of each stripe? 64K? 512K?
> 1Mg? ...
>
> 2 - How does your choice impact on parallel query for a 30Mg table spread
> on 5 disks ?

Assuming you are on a Unix system:
Unless you set 'use_readv' to true, you will not get an effective DB_FILE_MULTIBLOCK_READ_COUNT greater than 8 if you have 8K blocks, the product of block size and block count is automatically limited by Oracle to 64K.

For stripe sizes: the only potential benefit when trying to pick a size is to help tablescans to be effective. Set your stripe size to be a smallish (8 say) of your actual scan size. Too large a stripe and you lose some of the benefit of O/S load balancing, too small a stripe and a single 'db file multi block read' has to jump across stripes more frequently than desirable.

For PQO, which currently supports only tablescans, I would try to set the system to use 5 PQ servers per tablescan, given that you have five discs in a stripe set - any more and you may find that PQ servers are queuing on discs because they reach the same stripe simultaneously.

---
Jonathan Lewis
ora_mail_at_jlcomp.demon.co.uk Received on Wed Jan 31 1996 - 17:54:07 CST