Buffer cache statistics

Job details

Name:	Buffer cache statistics
Platform:	Oracle
Category:
Description:	Gets data cache statistics for buffer cache in SGA.
Long description:	Task gets data cache statistic for buffer cache in SGA.
Version:	2.31
Default schedule:	1,11,21,31,41,51 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’]

Name	Default value	Description
keep data for	14	The amount of days the statistics are kept in the history table.
graph time range	2	This parameter determines how many days back from the current date the data should be visualized in the report graphs regardless of how many days statistics are collected for.

Purpose: The purpose of this job is to gather and report on buffer cache statistics in Oracle’s System Global Area (SGA).
Why: This job is important because buffer cache statistics provide insights into the database cache’s efficiency, which can directly affect the performance of the Oracle database. By monitoring these statistics, database administrators can optimize database performance and preempt potential issues caused by inefficient cache usage.
Manual Checking: To manually check buffer cache statistics in the database, you can use the following SQL command:

select * from v$buffer_pool_statistics;

“Name:” Buffer cache statistics
“Description:” Gets data cache statistics for buffer cache in SGA.
“Version:” 2.31
“Company:” dbwatch.com
“Default Schedule:” Every 10 minutes
“SQL Implementation:” Inserts and fetches data from dynamic performance views and custom tables to compute statistics regarding buffer gets, physical reads, and historical data.

The job relies on multiple database objects including several tables and views that store historical data, buffer statistics, and their metrics.
Objects include:
- dbw_cache_#instance~id#
- dbw_cache_name_#instance~id#
- dbw_cache_histr_#instance~id#

These dependencies are critical for collecting and processing the required data for generating reports on buffer cache efficiency.

The job generates several reports and visualizations:
- “Buffer cache statistics” showing the number of buffer gets and physical reads from buffer cache.
- “Physical reads/writes for temporary tablespaces” displaying read and write statistics.
- “Data cache hit ratio” showing the cache hit ratios for buffer cache indicating the IO efficiency.
- Other detailed analysis includes “Logical reads – aggregated (working hours)” and “Increasing logical reads periods”.

Metric	Description
Physical Reads	Number of reads that were satisfied from the buffer cache.
Buffer Gets	Number of requests for blocks in the buffer cache.
Hit Ratio	Measure of the efficiency of the buffer cache usage.

Various SQL scripts are involved in setting up necessary database structures:
- Creating tables such as dbw_cache_#instance~id# for capturing real-time buffer cache metrics.
- Views like dbv_logic_gets_#instance~id# and dbv_phy_reads_#instance~id# to aggregate and present data efficiently.

Stored procedures are used for populating data and analyzing trends, which help in proactive monitoring and optimizing the buffer cache performance.

The job includes scripts for upgrading existing procedures if there are changes in the database structure or logic.
An emphasis is placed on handling potential errors and ensuring data consistency during upgrades.

Maintaining an efficient buffer cache is crucial for database performance. This job allows for continuous monitoring and provides actionable insights through detailed reports, which help in maintaining optimal database performance.