Turn off/flush Oracle buffers?

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.

Thank you,
-Jay

you cannot flush data buffers, you can only flush shared pool which does NOT store data

How big is your buffer cache ?
Between the tests, you can always flood it with a real bad query !!

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 ....

[]s

Chiappa

Thanks all for your replies.

SVK,

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.

Any other information is greatly appreciated.

-Jay

7.3.4 and 60gig buffer cache ? something is missing somewhere.....

But, looks like what you are looking at is optimizing your report. That basically will mean tunning the queries of that report. So, focus on that. Look at the query plans.

Quote:

---

Originally posted by svk
7.3.4 and 60gig buffer cache ? something is missing somewhere.....

---

Forget 7.3.4, what is this 60 Gig ??

The hard drive might get rusted :)

Quote:

---

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

---

restart oracle

SVK, and others,

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.

Hope this helps. Thanks.
-Jay

Doesn't checkpointing flush dirty blocks to disk? Page 68, Expert One-on-One Oracle by Kyte.

Logfile switch causes a checkpoint, as does alter system checkpoint. If your test makes any changes, this may work. If the test is query only, I think you are still stuck with the on/off switch or aging out the blocks.

Hello;
Sorry there is no way in Oracle -7, 8, 9, 10 ??

A checkpoint causes that the dirty blocks are written to the datafiles, but the blocks stay cached.

A FULL-Table-Scan can NOT effect a flushing of the Buffer-Cache. A long full-tablescan get a low percentage of the buffercache and all blocks of the table are acessed thru this Memory-Window.

In Applications specially when data and entities are a lot, Reports can cause heavy load, the first time. The Data of a Report has to pass the buffercache.
So you have to tune the SQL. ( probably bitmap-indexes )
In 7 that's it,
no partitioned Talbes, no materliazed views. no other tricks.
you can tune the BusinessDemand on the Query. Need all record to be seen,
can the size of the resultset reduced?

Orca




[Edited by Orca777 on 06-16-2002 at 12:21 PM]

Quote:

---

Originally posted by Orca777
A FULL-Table-Scan can NOT effect a flushing of the Buffer-Cache. A long full-tablescan get a low percentage of the buffercache.
[Edited by Orca777 on 06-16-2002 at 12:21 PM]

---

This is true. I remember it is at most about 10% of the cache that is used for FTS.

FTS using only 10% of BC is part of algorithm change in BC use that was implemented by Oracle in 8i.

This is a 7.3.4 db....

Quote:

---

Originally posted by svk
FTS using only 10% of BC is part of algorithm change in BC use that was implemented by Oracle in 8i.

This is a 7.3.4 db....

---

This is far from the truth. FTS using only a limited portion of buffer cache is nothing new in 8i, nor in 8.0 - this has been implemented at least since the beginning of Oracle7. So the whole thing applies to 7.3.4 without a doubt.

And BTW, the limit is not 10% of the buffer cache - it is much smaller. The limit is set with the hidden parameter _SMALL_TABLE_THRESHOLD, whose default value has changed from release to release, but I think from later releases of Oracle7 on it has a steady default value of 2% of db_block_buffers.

The 10% figure probably comes from the default value of CACHE_SIZE_THRESHOLD, but this parameter does not determine the portion of cache used for FTS - it only determines which tables can be cached and which ones are to big so that Oracle will not cache them even if you explicitely tell it to cache them.

Parameter CACHE_SIZE_THRESHOLD was from Oracle 7.3.x up to 8.0.6.
and had default value = 2%
now this parameter is:
_SMALL_TABLE_THRESHOLD and it has default value = MAX(4, db_block_buffers/50 )
If u have 50000 buffers in db cache then 50000/50 = 1000
Oracle will determinate tables that has size <= 1000 blocks as small

Quote:

---

Originally posted by Shestakov
Parameter CACHE_SIZE_THRESHOLD was from Oracle 7.3.x up to 8.0.6.

---

That's true.

Quote:

---

and had default value = 2%

---

This is not true. It's default value has allways been 10%.

Quote:

---

now this parameter is:
_SMALL_TABLE_THRESHOLD and it has default value = MAX(4, db_block_buffers/50 )

---

_SMALL_TABLE_THRESHOLD parameter is not a substitution for depreciated CACHE_SIZE_THRESHOLD. They coexisted untill 8i, and _SMALL_TABLE_THRESHOLD still has its original meaning, which is quite different of the purpose of CACHE_SIZE_THRESHOLD. And it's current default is still 2% of block buffers (with a minimum of 4 buffers).

Quote:

---

Originally posted by jmodic
_SMALL_TABLE_THRESHOLD parameter is not a substitution for depreciated CACHE_SIZE_THRESHOLD. They coexisted untill 8i, and _SMALL_TABLE_THRESHOLD still has its original meaning, which is quite different of the purpose of CACHE_SIZE_THRESHOLD. And it's current default is still 2% of block buffers (with a minimum of 4 buffers).
[/B]

---

_SMALL_TABLE_THRESHOLD = MAX(4,db_block_buffers/50)

is -->
min = 4 blocks
db_block_buffers/50 = exactly 2% of db cache

cache_size_threshold was in 716, 733, 734, 803,804,805,806. and describe
how many db blocks MAX may be place (IN DB_BLOCKS not in %)
_small_table_threshold was and still in 716, 733, 734, 803, ...920 ...
and describe what kind of tables oracle will describe as "small tables" and can
place in db_cache DURING FST before its will be overwritten by new blocks.
but:
in 901 and 920 _small_table_threshold -> max size of table for DIRECT READ OPERATIONS.