Flushing Database-Buffer??

[Orca777]

Hello;
When i tune SQL's i want to see their initial Performance not there cached performance.

For example a query lasts 20 seconds
and then by repeating 0,2 Seconds.

If i change the SQL for tuning many Datablocks are already wating for and smiling at me in the buffer cache.
I do not want to restart tje database.
Refreshing SHARED-POOL will not help too;

Orca

[sprabhakar]

Use the following command to flush the shared pool and then try running the query again.

Alter system flush shared pool;

Regards
S.Prabhakar

[jmodic]

As original poster allready said, using "ALTER SYSTEM FLUSH SHARED POOL" has no impact on database buffer cache whatsoever. There is no such command to flush buffers from buffer cache.

I can't verify the following right now, but I seem to remember to read somewhere that if you take a tablespace offline, all the buffers that belongs to segments from that tablespace are freed from buffer cache. So if you can afford it, take all the tablespaces that are involved in your query offline, put them back online and benchmark your query. However verify first that the above trick realy works, as I never tested it.

[Orca777]

Thank you all;

The Db needs to be online, but you could be right this could work.
Ok; i wait for Oracle 10 ii with

ALTER SYSTEM FLUSH BUFFER_POOL [ IMMEDIATE ];
;-))
Orca

[BV1963]

Look into recycle pool (BUFFER_POOL RECYCLE).Allocate enough blocks so your query runs same time as in a regular pool. Assign your tables to recycle pool. Run your query. Assign object that is not in a query to the recycle pool as well. Run another query against the object which is not in your original query. I assume it will overwrite cached blocks in a pool and if you run your original query it will take same time.

[mrvajrala]

Why dont you consider a test database , which is a replica of production, and you can bounce it , when ever you wanted .

[patnams]

By your post, you are obviousely testing your queries.
I dont know if this works ..
1. Reduce the the Buffer Size
2. Create a table large enuf with junk data that can fill up your Data Buffer.
3. Run your Query
4. Run a Query on the large table, buffers are replaced
5. Run you Query

[mrvajrala]

Originally posted by patnams
By your post, you are obviousely testing your queries.
I dont know if this works ..
1. Reduce the the Buffer Size
2. Create a table large enuf with junk data that can fill up your Data Buffer.
3. Run your Query
4. Run a Query on the large table, buffers are replaced
5. Run you Query

You want HIM to do it on a production box ?????

[Calvin_Qiu]

it will not degrade performance of other table.