Nuestros DBA conocen la dedicación que se requiere para realizar cambios de parametrización en un entorno productivo, y el número de pruebas manuales que se requieren para lograr un mínimo de garantías antes de abordar una migración de la base de datos o una actualización de la versión. Más allá de las costosas soluciones de software de algunos fabricantes, en GPS entendimos que contar con una herramienta que nos permita reproducir y analizar consultas en diferentes entornos, simulando la respuesta y el rendimiento antes de implementar los cambios, sería como mínimo un AHORRO de tiempo para nosotros y pronto para nuestros clientes. Por esta razón presentamos 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-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 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).
¿Qué compara y cómo?
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.
¿Cómo funciona y que necesita?
Nuestra herramienta necesita obtener las consultas de la base de datos Oracle, después puede guardar las queries y sus métricas, o lanzarlas nuevamente contra ese u otro entorno Oracle. Con la información obtenida tras la ejecución, podremos generar un informe detallado comparando el comportamiento de esas queries en los dos escenarios.
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.