Home » RDBMS Server » Performance Tuning » How I can remove the table from cache? Which command I should execute
How I can remove the table from cache? Which command I should execute [message #132401] Fri, 12 August 2005 10:11 Go to next message
lanag
Messages: 2
Registered: August 2005
Junior Member
I found the answer myself. The command is:
alter table XXXX nocache.

[Updated on: Fri, 12 August 2005 12:36]

Report message to a moderator

Re: How I can remove the table from cache? Which command I should execute [message #132621 is a reply to message #132401] Mon, 15 August 2005 04:43 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Just to clarify for yourself.

The alter table .... nocache WILL NOT remove the table from cache.

The caching clause for a table specifies that when a table is brought into memory during a Full Table Scan oracle will place its blocks into the middle of the "least recently used" (LRU) list for the Buffer Cache.

This is contrary to its normal behaviour which is, only blocks retrieved through an index lookup are brought to the middle of the LRU list, full table scans blocks are placed at the bottom as so are aged out of memory faster.

If you have a small lookup table that is accessed regularly but is being retrieved through a FTS then the cache clause can help to retain the block in the buffer cache for longer so that the next time the query runs your server process doesn't have to go to disk.

Hope that helped a bit.

PS In 10g there is a alter system flush buffer_cache; but not in earlier releases.

Allie
Re: How I can remove the table from cache? Which command I should execute [message #132704 is a reply to message #132621] Mon, 15 August 2005 15:42 Go to previous messageGo to next message
lanag
Messages: 2
Registered: August 2005
Junior Member
Allie,
Thank you very much for your explanation and time you've spent on it!

Lana
Re: How I can remove the table from cache? Which command I should execute [message #136217 is a reply to message #132401] Tue, 06 September 2005 14:41 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
"alter system flush buffer_cache" requires certain priviledges which the regular user may not have. Moreover, it is only present in Oracle 10. Is there any other way to flush cache to disk - direct/indirect(using some oepration on table which forces a flush)?
Re: How I can remove the table from cache? Which command I should execute [message #136227 is a reply to message #132401] Tue, 06 September 2005 15:49 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Nile,

You just posted this question in another forum. Please don't cross post. Refer to the anser in the performance tuning forum.
Previous Topic: index ignored problem
Next Topic: How to stop the Cache Effect ?
Goto Forum:
  


Current Time: Tue Dec 06 22:44:10 CST 2022