Query Performance Gallery
Welcome to the Query Performance Gallery. In it, you can see some screenshots of the application’s functionalities. From the main page you can access Works, Reports, Capture and Replay.
As these methods can obtain a large volume of queries, advanced filters are available before capturing them. In this way we can optionally save only the information we want. Once the desired information has been selected, the capture begins and the information is saved in a new Snapshot or an existing Snapshot.
Within the Works, Snapshots and Query information collected through Captuare and Replay are stored.
Queries can be captured via AWR, STS, Memory or a trace file.
What information is stored in snapshots?
Detailed information on the execution of the queries is stored in the snapshot. The database parameters are also saved at execution.
This information is used to compare the performance of queries before implementing changes in production.
For example, we can see if changing a parameter in the database will make queries run faster. Or on the contrary, it will worsen its performance or give error. In either case, we will be able to obtain information that will help us to solve the problem.
Execution plan information and performance metrics for each query are stored for each execution. It also collects information on the variables used in its execution and the type they are.
Anticipate problems and make better decisions
By capturing the execution plan we can compare the plan of two executions of the same query in different environments. This information will give us clues as to why performance has been better or worse after the migration of the Oracle environment. Or why a query failed after changing a parameter in the database.
By saving the queries in the Works and Snapshots, they can later be executed against the environment. Suppose we have captured a set of queries and their execution information in Oracle 12c version. We want to test their performance on Oracle 19 before upgrading the Oracle version. In this way we can avoid migration-induced errors in production.
For this check we will need to run a Replay, which re-executes the queries already captured in another work or snapshot. Subsequently, the result of both runs can be compared.
In addition to version changes, Replay can help us to test, for example, whether an application running on another Oracle or PostgreSQL database would fail or get worse if moved to this server.
All this information can be seen more clearly through the Reports. With them, two snapshots or works can be compared. In addition, we can see at a glance which queries are improving, which are getting worse, which have failed and so on.
On our YouTube channel you can see these features in action, which we have covered in the gallery.