Solved

Disable Caching in Oracle 10g

Posted on 2011-02-11
9
1,710 Views
Last Modified: 2012-05-11
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
Comment
Question by:chongchian
  • 5
  • 2
  • 2
9 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 170 total points
Comment Utility
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 170 total points
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 160 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>  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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now