Re: DB_FILE_MULTIBLOCK_READ_COUNT

In article <36350165.2E9C_at_concentric.net>,   alucus_at_concentric.net wrote:
> I have a PeopleSoft application running on Oracle 7.2 and NT 3.51.
>
> I have a very low hit ratio and have been playing around with increasing
> the DB_BLOCK_BUFFERS by using the DB_BLOCK_LRU_EXTENDED_STATISTICS
> method, but the expansion of the DB_BLOCK_BUFFERS seems to have little
> or no effect on the hit ratio.
>
> I also have determined we have no free blocks by querying the actual
> blocks and their status and have no free blocks. All appear read and
> altered.
>
> My next step is to look into the DB_FILE_MULTIBLOCK_READ_COUNT value.
> We have a 2K blocksize and our multiblock read count is set to 32. I
> think this may be a hog on my hit ratio as the application is primarily
> a OLTP application. I want to drop this, but want someone to verify my
> logic in doing so. Has anyone ever run into this hit ratio problem when
> running PeopleSoft HRMS?
>
> Thanks for any help or advice you could offer.
> --
> ===================================================================
> \\||// A fool and his money are soon partying
> / \
> []--[] Allen Lucas
> @ /\ @ Chicago Bridge & Iron
> {----} Plainfield, Il 60544
> ^^ alucus_at_concentric.net
> ===================================================================
>

Greetings, Allen

  I believe you might want to hunt around your NT Oracle documentation regarding DB_FILE_MULTIBLOCK_READ_COUNT. I am supporting a PeopleSoft 6.10.20 AP/GL application with Oracle 7.3.4 on AIX 4.3.1, and the Oracle documentation and tuning guides for AIX state:

    "DB_FILE_MULTIBLOCK_READ_COUNT - Used for multi-block read operations. It     sets the number of blocks to be read when doing I/O during a sequential     scan. This parameter is useful for performing a full table scan in which     the WHERE clause does not refer to an indexed column. This does not     improve performance beyond 8KB on AIX"

For UNIX, this parameter is set to a maximum of twice the number of CPU's; not having ever done any Oracle on NT, I'm not sure how NT handles this. From the stuff I've read, it seems that the operating system actually influences how this parameter works.

Other things influencing your hit ratio are SORT_AREA_SIZE, DB_BLOCK_SIZE, SHARED_POOL_SIZE and the SQL statements. I just started supporting PeopleSoft, and, unfortunately, I haven't been to any of their classes, but it seems that it's very difficult to find out just what their SQL is doing. I also find you have to watch the database from day to day; on some days I have hit ratios at 99.98%, other days, around 80%.

Yaca Attwood

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Oct 27 1998 - 17:29:32 CST