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.
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?
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
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.
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