Measure the impact of changes on your Oracle environment with Query Performance

We present GPS Query Performance, a query analysis, comparison and execution tool on Oracle platforms.

Our DBAs know the dedication required to make parameterization changes in a production environment, and the number of manual tests that are required to achieve a minimum of guarantees before tackling 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 at least SAVE time for us and soon for our clients.

Query Performance Advantages


With the information obtained after relaunching the queries, possible errors. Avoiding surprises in production passes.


The behavior of the selected queries, showing their execution plans, times and other performance meters to be compared.


It allows to identify the queries with problems and to make the necessary adjustments that avoid degradations caused by the jumps of version or changes in the database.


Collect data and statistics from the current environment to compare before and after changes. Store this data for later use in new platform changes.

GPS Query Performance

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 Performance, a query analysis, comparison and execution tool on Oracle platforms.

GPS Query Performance is a tool that will centralize, relying on some native Oracle tools, and using simple software, the entire battery of tests necessary to certify the success of any change.

This tool will help evaluate and measurethe impact of these changes in our Oracle environment, for example:

  1. Application upgrade, Oracle version or Operating System.
  2. Code change in the application, due to new regulations or functionality
  3. Data model modifications: Indexes, partitioning, materialized views
  4. Hardware changes
  5. Patches
  6. Change of database parameters.

GPS Query Performance

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.