Re: Surprising Performance Changes with Oracle (Long Post)

From: Charles Hooper <>
Date: Fri, 11 Sep 2009 05:11:01 -0700 (PDT)
Message-ID: <>

No direct I/O was disabled, I left the FILESYSTEMIO_OPTIONS parameter unset, which should have caused Oracle to use file system buffered I/O if I understand the documentation correctly.

If this post makes it out to Usenet (the posts were failing yesterday through Google's interface), I will post the results of 3 test runs of the second script (suggested by Gerard) with direct I/O and with a reboot between each test run. It is interesting to see Oracle aging out the blocks read during the test run shortly after the test run completes, even with no other load on the system and the test run should not have encountered an AWR collection.

Also interesting is that on with the SGA_TARGET set to 8000M, the DB_KEEP_CACHE_SIZE set to 6G, and the table and its index using the default buffer pool, I am seeing somewhat unexpected values for a couple of the hidden parameters at the end of the run with the full index access path (1 to 400) with direct I/O enabled and asychronous I/ O enabled. __DB_CACHE_SIZE was set to 536,870,912 (512MB). __SHARED_POOL_SIZE was set to 1,140,850,688 (1,088MB). It would seem that Oracle should have at the least set __DB_CACHE_SIZE to a value larger than the __SHARED_POOL_SIZE value as it did on (Windows 64 bit), (Windows 64 bit), and (Linux 64 bit). This might explain why the test run required slightly longer on

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Sep 11 2009 - 07:11:01 CDT

Original text of this message