Flushing the Buffer Cache

Page 23

New Table-Monitoring Behaviors

Prior versions of Oracle Database allowed you to monitor a table's usage. The monitoring process keeps track of differential changes to the table. The dbms_stats package can be used to apply those differential statistics to the table's dictionary statistics, allowing the Cost Based Optimizer to generate plans based on more current statistics.

Oracle Database 10g enables global table monitoring by default. This feature is controlled via the statistics_level parameter (statistics_level was available in Oracle9i Database). When statistics_level is set to TYPICAL (which is the default setting) or ALL, then global monitoring is enabled. When the statistics_level parameter is set to BASIC, global monitoring is disabled.

Note that if global monitoring is enabled, you cannot disable it for specific tables. The nomonitoring clause of the alter table command will appear to complete successfully, but will have no effect in reality. Also, the monitoring clause of the alter table command no longer has any impact on monitoring of tables. It's kind of an all or nothing deal these days!

Flushing the Buffer Cache

Prior to Oracle Database 10g, the only way to flush the database buffer cache was to shut down the database and restart it. This was perhaps not the most graceful way of performing this activity because it required shutting down applications and disconnecting users, creating all sorts of mayhem (not that flushing the buffer cache in and of itself can't cause some short-term mayhem of its own!).

Oracle Database 10g now allows you to flush the database buffer cache with the alter system command using the flush buffer_cache parameter, as shown in this example:

 Alter system flush buffer_cache; 

NOTE
There is a huge difference of opinion in the Oracle world if this functionality is really all that worthwhile. I'd be interested in hearing from you if it has made a difference in your life as a DBA.