If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I am trying to do performance tuning in our SAP sysetm, which uses Oracle version 7.3.4. The problem is we cannot test modifications to data base selects because the first pass fills Oracle's buffers. We want to force selects to always retrieve data directly from the tables.

Is there a way to temporarily either stop Oracle from using buffers or flush the buffers between each test run of the application?

Be advised, I am not a DBA or Oracle guru. Answers will be passed to our resident DBA/guru.

Great question. You're asking Oracle to do something it really, really doesn't want to do, and does only when it has to (read from disk).

You can force the data to one of three buffers: keep, recycle, and default. Frequently used, small lookup tables are good candidates for the keep pool. Recycle is good for large amount of data. By default, everything else goes to the default buffer.

What you can experiment with is this: Set the db_block_buffers size to a small value, and have the keep and recycle sizes set to fill up the majority of the space, thereby leaving little space in the default buffer. Set the storage clause of your table of interest to the default buffer (assuming you're working with one table, you can bounce the instance to reset the db_block_buffers value, and the amount of data you are retrieving won't be able to fit in the default buffer).

Are you going to flush the shared pool? If you are doing this performance test where the query is supposed to start from scratch, then flush the shared pool so Oracle will have to do the parse-execute-fetch thing in addition to reading from disk.

I don't know of any command to flush data block buffers. There are workarounds - one as mentioned is to age out the blocks of interest by causing Oracle to read in newer and different blocks. Another way is to bounce the instance.

The explain about the 3 buffers was correct, but unusable in the situation because the poster is in 7.3.4 - in this version only one data buffer exists.
The only way to remove blocks from the data cache in 7.3.4 is putting the tablespace where these data blocks reside offline - ALL the blocks from this tablespace will be removed from the cache. But the question remains : exactly FOR WHAT he wants to make physical I/Os ?? With this info maybe we can say anything more ....

Our buffer cache, I understand, is something on the order of 60Gig (!!). I am waiting on a reply from our DBA to confirm this. It is virtually impossible to fill it up between test runs with garbage selections.

The reason for wishing to force selections from the data tables is that this is typical of what happens when our users run certain report programs in SAP. They run it for the first time that day and, of course, it must get it's data from the tables and that's where the biggest delay occurs. Once it's been run, subsequent runs get the data from the buffers and the response improves greatly. But the report is not one that is typically run repeatedly. So we are trying to find the best way to get data directly from the tables in the least amount of time.

My appologies on buffer cache. That's what I get for asking the wrong person and not being savvy enough to catch (cache?) it. The db_block_buffers is set at 60,000 according to one of our resident DBAs. Data base block size is set at 8K.

Restarting Oracle, of course, would be the easiest way, but it would also force us to kill SAP and drop other users from our test system. Not desireable.

In nearly all situations, we must retrieve data from multiple tables which are related by foreign keys. There are various ways to do this in SAP. We are trying to determine the best way. However, once you run a query and the buffers fill, subsequent runs to access the same data just gets it from the buffers and messes up our tests.