Test DML performance

Job details

Parameters

Job Summary

• Purpose: The purpose of this monitoring job is to evaluate the performance of DML operations (SELECT, INSERT, UPDATE, DELETE, TRUNCATE) on a test database schema to determine the efficiency and speed of these operations under different load conditions.
• Why: This job is crucial as it helps in assessing the underlying hardware’s capability to handle varied sizes of data transactions and operations efficiently. By performing these tests, database administrators can understand potential bottlenecks and performance degradation issues which might impact overall database responsiveness in production environments.
• Manual checking: You can check these performance metrics manually in the database by executing the similar SQL commands as those defined in the monitoring job for inserting, updating, deleting, selecting, and truncating operations on the test tables.

Detailed Description

This dbWatch job, named “Test DML performance,” targets Oracle database instances where the dbWatch engine is installed. It specifically focuses on whether these instances contain the engine and are categorized under ‘Performance’. The job schedules and carries out a series of SQL DML (Data Manipulation Language) activities such as SELECT, INSERT, UPDATE, DELETE, and TRUNCATE on predefined schema test tables. The scale of these operations is determined based on the predefined parameter “records number,” which dictates the number of records each operation will engage with, aligning the test to either fixed record numbers or varying them in a given sequence for thorough performance assessment.

Dependencies

The following dependencies are essential for carrying out the tests:

• dbw_test_tab: A dependency table.
• dbw_performance: A table storing performance metrics.
• dbw_performance_histr: Tracks performance history data specifically for DML and the DDL TRUNCATE statements.
• dbw_dml_perf_test, dbw_dml_min_test, dbw_dml_max_test: Procedures designed to perform and measure the DML tasks under different scenarios.

All dependencies have cleanup actions enabled to ensure no residual data impacts consecutive tests.

Reporting

Reports generated by this job provide insights on:

• DML Performance Testing: Overall statistics on how DML operations perform with varying number of rows.
• Performance Statistics: Dedicated sections in the report detail minimum, average, and maximum execution times, extrapolating them per 1,000 records to simplify interpretation.

Tables and graphs included in the report give a clear visualization of:

• Overall Times: Average times for SELECT, UPDATE, INSERT, and DELETE operations.
• Transactional Times: Detailed durations visualized across different loads to depict how added data volume impacts response times.

Implementation Details

The procedures and tables required for executing and storing results from the performance tests are outlined in the XML job script. Procedures have conditional flows depending on the test scale, either for limited smaller sets of records or extensive larger sets, as predefined by the “records number” parameter. On initialization, tables are created for storing test data and results, which are then filled by individual transactions performed by the procedures.

Conclusion

This monitoring job by dbWatch is structured to provide systematic analysis and visual feedback on the transactional efficiency and speed of Oracle databases, which is crucial for tuning and proactive management in any high-demand database environment. Through regular monitoring, it ensures that the database’s performance remains optimized in alignment with operational requirements and hardware capabilities.