Test DML performance


Job details

Name: Test DML performance
Platform: Oracle
Category: Performance
Description: Runs performance test. Procedure executes SELECT, INSERT, UPDATE, DELETE and TRUNCATE statements on schema test tables.
Long description: Task runs performance test. Procedure executes SELECT, INSERT, UPDATE, DELETE and TRUNCATE statements on the dbWatch engine schema test tables.
Version: 1.9
Default schedule: 10 22 5 1,30
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’]

Parameters

Name Default value Description
records number -9 Number of rows to insert into the test table.

Job Summary

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:

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

Reporting

Reports generated by this job provide insights on:

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

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.