 |
Newsgroups: comp.databases.oracle.server
From: Charles Hooper <hooperc2...@yahoo.com>
Date: Sun, 6 Sep 2009 08:55:46 -0700 (PDT)
Local: Sun, Sep 6 2009 11:55 am
Subject: Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
On Sep 6, 11:14 am, Robert Klemme <shortcut...@googlemail.com> wrote:
> On 05.09.2009 22:26, Charles Hooper wrote: That is a good question. The machine in this setup had 12GB of > > * Direct I/O and Asynch I/O, which seem to be frequently recommended > Wouldn't you have to increase SGA target when switching to direct IO for memory. The SGA_TARGET was set to 8GB, the PGA_AGGREGATE_TARGET was set to 1.8GB, and the KEEP buffer pool was set to 6GB. Since the actual table which the test table mimics will likely be infrequently queried in full, such as a report which attempts to indicate the change in the size measured for the left side of the cylinder wall over the production lifetime of the part, it probably would not be a good idea to optimize the instance and operating system performance for this one query. Other data from other tables would likely be occupying the KEEP buffer pool, which means that in production the available RAM for caching of data blocks at the operating system level might be quite limited. It might be interesting to test what happens when the KEEP buffer pool is fully utilized - will Linux start swapping other memory out to disk to buffer the Oracle blocks in the file system cache? What happens when a 512MB (or larger) redo log needs to be archived, will it hinder the effects of the operating system level caching of Oracle blocks? It might come down to how closely the test environment is able to mimic the production environment. > > So, should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower This test case is a light-weight example from the book "Expert Oracle > > number to (quoting from a posting on the Internet) “immediately tune > > all of the SQL in your database to favor index scans over full-table > > scans”? :-) > I am by far not as expert as Jonathan but I have a bad gut feeling about > Charles, thank you for sharing this! > Kind regards > robert Practices: Oracle Database Administration from the Oak Table". The test case took a different twist while testing on 11.2.0.1. Charles Hooper You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||