Link to home
Start Free TrialLog in
Avatar of chongchian
chongchianFlag for Singapore

asked on

Disable Caching in Oracle 10g

Hi All,

I was wondering if there's anyway to turn off at instance/machine level, caching in Oracle10g? I'm trying to run a series of data simulations, which requires performance analysis without caching or other optimization from Oracle.

I was wondering besides caching, is there any other things that I should disable?

Cheers
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

are you sure your analysis will be valid without caching?

In normal execution, caching is regular and expected occurence.
If you artificially wipe it out, you may be creating an "even playing field" but still skewing your results.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Data cache flush could also be done:

alter system flush buffer_cache;

Another  cleanup statements:

alter system flush buffer_cache;

alter system switch logfile;




>>  alter system flush buffer_cache;

I stand corrected, that's better  than trying to fill it with dummy data

I've never wanted to flush my buffer cache, so never bothered to look it up.

If anything I've wanted to seed the cache for my tests.
The only time I've ever flushed buffer cache is when I'm wanting to test variations in SQL (different hints/indexes/functions/???) and don't want any table blocks in memory between runs.

This gets even tricker in 11g with results caching.  Flushing that isn't an 'alter system'.
if result_cache_mode init param is set to MANUAL (the default)  nothing will be cached automatically.

and for individual queries you can use /*+ NO_RESULT_CACHE */ hint
also  result_cache_max_result  can bet set to 0, which should also effectively disable result caching since 0% of memory would be usable