Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1915
  • Last Modified:

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
0
chongchian
Asked:
chongchian
  • 5
  • 2
  • 2
3 Solutions
 
schwertnerCommented:
Oracle level caches:

Oracle caches (placed in System Global area) can not be turned off. You can only increase or decrease the size of these caches.
Also you can command the size of online redo log files.


OS level caches:

On Unix the OS caches in the "free" RAM disk extents. This can not be command by you.
0
 
sdstuberCommented:
you can flush the shared pool

for data caching,  you can't disable or flush it, but you can fill the cache with dummy data,  create a dummy table and read from it until your cache is filled with nothing but blocks from that table.

of course, restarting the database will flush everything too
0
 
sdstuberCommented:
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
slightwv (䄆 Netminder) Commented:
As mentioned earlier, bouncing the database is the best way to get a pristine memory structure.  Depending on exactly what you are wanting to test, in addition to flushing the shared pool, flush the buffer cache.

I agree that a cleared cache might skew your results but for testing variations of SQL performance, I flush my caches.

alter system flush shared_pool;
alter system flush buffer_cache;
0
 
schwertnerCommented:
Data cache flush could also be done:

alter system flush buffer_cache;

Another  cleanup statements:

alter system flush buffer_cache;

alter system switch logfile;




0
 
sdstuberCommented:
>>  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.
0
 
slightwv (䄆 Netminder) Commented:
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'.
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
also  result_cache_max_result  can bet set to 0, which should also effectively disable result caching since 0% of memory would be usable
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now