Temp cache statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and monitor temporary cache usage statistics for a Sybase database, focusing on the differences in logical reads, physical reads, and physical writes over time.
• Why: This job is crucial for performance analysis and ensuring efficient cache utilization. Monitoring these statistics helps in detecting performance issues early and optimizing database operations on the Sybase platform.
• Manual checking: To manually check these metrics in the database, you can issue the following SQL command:

select sum(LogicalReads) as Total_Logical_Reads, sum(PhysicalReads) as Total_Physical_Reads, sum(PhysicalWrites) as Total_Physical_Writes from master..monTempdbActivity

Table Structures

Dependencies

• dbw_temp_cache_stat_proc: Self-dependent. It relies on its own data to populate historical data table.
• dbw_temp_cache_histr_tab: Utilizes historic data for trend analysis and to maintain a record of cache usage over time.
• dbw_temp_cache_tab: Aids in tracking current snapshots of cache statistics for recent transactions.

Cleanup Mechanism

In the event of job failure, the following cleanup actions will be executed:

drop procedure dbw_temp_cache_stat_proc

drop table dbw_temp_cache_histr_tab

Implementation Details

The main procedure involved in this job, dbw_temp_cache_stat_proc, performs several actions to monitor and analyze cache usage:

• It computes current stats and compares them to the last recorded stats in dbw_temp_cache_tab.
• If current readings are higher than the last, it logs the difference and computes rates per second before saving into dbw_temp_cache_histr_tab.
• Older records beyond the specified retention period are deleted to maintain data relevance and manage storage.
• The procedure concludes by calculating and updating the cache hit ratio, providing an efficiency metric related to cache advantages during operations.

Visualization and Reporting

The job provides a chart with a historical view of the cache usage:

select histr_date "Date", logical_reads_dif/time_interval_sec "Logical reads", physical_reads_dif/time_interval_sec "Physical reads", physical_writes_dif/time_interval_sec "Physical writes" from dbw_temp_cache_histr_tab where time_interval_sec > 0 order by histr_date
* The above SQL feed populates a category chart showing temporal alterations in cache statistics, which aids DB administrators in visual performance monitoring.

Considerations for Installation

• Express Installation: False
• Force Installation: True
• Compatibility Query:

[type='instance' & databasetype='sybase']/instance[maj_version > '12' & hasengine='YES' & monitoring_statistics_enabled = '1']
* This job is only compatible with Sybase database instances meeting specific version and configuration requirements as detailed above.