GPS_Query_Performance

Something as necessary as measuring the impacts of a change In our database platform, whether by a version upgrade, or changes in parameterization or changes in architecture, it suppose an important task for the database and development teams. Minimizing risks, anticipating problems, measuring impact, and of course, reducing the timing of this assessment are the object of this tool. *
Our purpose is to test, measure and compare, the behavior of a set of Queries (work) in a given environment, and the Behavior of the same work in another possible environment or future.

What does?

Our tool will collect the current system data, its statistics, times and photos necessary to compare the before and after. It will measure in the final environment proposed the behavior of the Querys, showing execution plans and times. This will avoid surprises in the production pass, allowing in a quickly and agilely way to identify problems, to solve them and to anticipate improvements or degradations in the service.

We will show the difference in impact (positive or negative) for each of the queries and whether there are changes in the execution plans. It will allow to verify if all the Querys are executed correctly in the new environment, and otherwise, we will be able to make the adjustments (in the queries or in the environment) that allow the correct execution and avoid degradations caused by those changes.

How does it do it?

Work-The Epicenter:

GPS Query Performance

The GPS Query Perfomance tool will capture a set of queries to the database from different sources (memory, AWR, STS,…) as well as their statistics. This will be our starting point, from here we’ll establish the comparison of that photo of results (snapshot) in front of which we get executing in the new or future environment, the set of queries extracted from the Work

GPS Query Performance

The piece on which the tool is articulated is the Work. Under this body we will be able to find queries and statistics of execution of the queries at a specific moment in time.
The queries that make up the Work (together with the execution variables if they are used) we will obtain them from one of our database environments and if they are valid for our purpose we can save them as snapshot, being a photo of the behavior of those queries in a given environment or time. Once this photo is obtained, we can relaunch the set of queries against the environment to be tested and make a new photo with its execution statistics.

GPS Query Performance
If the test environment is more limited than the original, what we can do is get the queries from the source environment (usually production) and make a first photo in the test environment by relaunching the queries to study. Once we have done this first measurement, we can test it in the test environment with the modifications we need (version upgrade, parameter change, etc) with a new execution. In this way we will have two snapshots with a similar environment that will allow us to carry out the comparison, being able to make better conclusions about the behavior of the Work.
GPS Query Performance

Once we have the photos to compare, we will generate a report that will allow us to check in a graphical way as it can affect the change to our query set.

  • Currently only available for Oracle