Our DBAs know the dedication required to make parameterization changes in a productive environment, and the number of manual tests required to achieve a minimum of assurances before addressing a database migration or version upgrade. Beyond the expensive software solutions of some manufacturers, in GPS we understood that having a tool that allows us to reproduce and analyze queries in different environments, simulating the response and performance before implementing the changes, would be at least a SAVING of time for us and soon for our clients. For this reason we present the performance of the GPS queries.
In all of them, the tool offers a detailed report on the impact that the changes would have on the set of chosen queries, thus minimizing risks for the service, and showing all the metrics that will help us make the necessary adjustments that result in a transition without surprises.
What is Query_Performance?
We present GPS_Query-Perfomance a tool for analysis, comparison and execution of queries on Oracle platforms.
GPS-QP is a tool that will centralize, supported by some native Oracle tools, and through simple software, all the battery of tests necessary to certify the success of any change.
This tool will help evaluate and measure the impact of these possible changes in our Oracle environment, for example:
- Application upgrade, Oracle version or Operating System.
- Code change in the application, due to new regulations or functionality
- Data model modifications: Indexes, partitioning, materialized views
- Hardware changes
- Change of database parameters.
A work is the first element that we must create in order to start working, and on it we will group / organize the rest of the elements of the application: Queries and snapshots mainly. To do this, we must define it by a name and a description.
A snapshot can be created by obtaining the queries from the sources that we have previously discussed (Capture process) and we can also generate it by executing the same queries from the previous snapshot against a different scenario (Replay process).
What does it compare and how?
We already know what it is, what it does and what it is for, let’s see now how it does it and is organized.
Snapshot: Consists of capturing the performance and statistics of a set of queries.
Work: It will be our “unit or set of tests” in which we will group queries and snapshot as their performances and statistics. A Work is nothing more than a set of queries with their variables and performances (snapshots), or a set of several of these Snapshots.
How does it work and what do you need?
Our tool needs to obtain the queries from the Oracle database, then you can save the queries and their metrics, or launch them again against this or another Oracle environment. With the information obtained after execution, we can generate a detailed report comparing the behavior of these queries in the two scenarios.
How do you collect queries and their returns? Well, GPS-QP can obtain the queries and the statistics of its executions from the following sources:
Memory. Queries that run in Oracle are stored in the memory of the database manager. It contains information such as the number of times they have been run, the time it has taken, the readings to disk, etc.
AWR. It is a functionality from the 10g version of Oracle that takes a photo of the state of the manager for a certain time and saves information on the heaviest queries that have been executed in it.
STS (SQL Tuning Set). It is a functionality from Oracle version 11g that allows saving a series of queries to carry out a tuning study on them.
Trace file. It allows us to collect information from an Oracle trace file that we generate from the database. If in addition to the normal trace file we add the information from the tkprof we will have a greater amount of information, such as the users that execute each query for example.
And finally, from other queries that we have stored in our GPS-QP application.