User memory statistics
Job details
| Name: |
User memory statistics |
| Platform: |
Oracle |
| Category: |
Performance |
| Description: |
Collects user memory statistics. |
| Long description: |
Task collects user memory statistics. |
| Version: |
2.4 |
| Default schedule: |
7,17,27,37,47,57 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’] |
Parameters
| Name |
Default value |
Description |
| keep data for |
14 |
The amount of days the statistics are kept in the history table. |
Job Summary
- Purpose: The purpose of this job is to collect and monitor user memory statistics for Oracle database instances.
- Why: Monitoring user memory statistics is critical to ensure that database performance is optimized and to prevent issues related to memory allocation. It helps in identifying potential memory-related bottlenecks and ensuring that the database is functioning within its operational parameters.
- Manual checking: You can check these statistics manually in the database by issuing SQL commands on the relevant views and tables that hold memory statistics data.
Job Description and Logic
- The job “User memory statistics” targets Oracle database instances that are active (‘hasengine=YES‘). It is tasked with collecting memory usage data, specifically focusing on user memory, which includes both UGA (User Global Area) and PGA (Program Global Area).
- The statistics are kept for a default period of 14 days, which can be adjusted as per user requirements.
- Dependencies include several tables and views that need to be created and maintained, such as “dbw_usermem_#instance~id#”, “dbw_usermem_name_#instance~id#”, and several others which hold historical data and metadata about memory usage.
Implementation Details
- The primary activities include creating necessary tables and views to store and manage the memory statistics.
- A procedural script is run to collect data from dynamic performance views like V_$SESSTAT, V_$SYSSTAT, and V_$SESSION, which are critical for fetching real-time memory stats at the session and system level.
- The collected data is periodically inserted into predefined tables, and historical records beyond the retention period are purged to manage space and ensure efficiency.
Output and Reporting
- The job includes a report template configured to visualize the collected data through several charts and tables. These visualizations provide insights into different aspects of memory usage, such as session memory, sorts in memory versus disk, and current logon details, spanning the last 48 hours.
- Examples of visual representations include:
- Category chart plotting memory utilization over time
- Dual-axis chart comparing sorts performed in memory against those on disk
- Tabular data showing memory sorts, usage by session, and logon counts.
Dependencies and Cleanup
| Dependency Object |
Object Type |
Description |
| user_memory_#instance~id# |
2 |
Task has a self-dependency. |
| dbw_usermem_#instance~id# |
0 |
Dependency table. |
| dbw_usermem_name_#instance~id# |
0 |
Dependency table. |
| DBW_USERMEM_HISTR_#instance~id# |
0 |
History table for user memory. |
| dbv_sort_history_#instance~id# |
1 |
Base view for sort history. |
| dbv_sort_disk_#instance~id# |
1 |
Supporting view for disk sorts. |
| dbv_sort_mem_#instance~id# |
1 |
Supporting view for memory sorts. |
| dbv_usermem_histr_#instance~id# |
1 |
Base view for user memory history. |
| dbv_logons_current_histr_#instance~id# |
1 |
Base view for current logons history. |
- “Cleanup-on-fail” is enabled for all dependencies, ensuring that any operation failure triggers a cleanup to maintain system integrity and prevent data corruption.
Conclusion
This dbWatch Control Center job plays a crucial role in the proactive monitoring and management of memory resources in Oracle database instances, helping database administrators maintain system performance and stability through continuous observation and detailed reporting.