In the following example we show the impact on queries during testing for an Oracle upgrade from version 11g to 19c using Query-Performance. For the test, we need an environment from which obtain the queries in version 11 and another with the same data in version 19 in order to test the execution of the queries, and validate that their behaviour is as expected.
Steps for testing in a migration from 11g to 19c
The first thing we are going to do for the tests is to create a work in the application that stores the execution data of the queries in both environments so that we can compare them. To do this, we created a work called Test upgrade 11 to 19:
To start filling in the data, we perform a capture of queries from Oracle’s AWR repository in version 11g that we will store in a snapshot called awr11:
During the process, we can filter the type of queries, in this example only the select and from a specific schema are captured:
In the next step, we select the AWR intervals we are interested in and name the snapshot:
In the summary of the screenshot we can see that we have collected 31 queries:
The next step is to re-run these 31 queries in version 19c and see how they perform:
We can use the Schema remap functionality if the schemas have different names in the two environments. This can happen if we use for example a pre-production machine with a TESTREPO2 schema instead of the original TESTREPO:
When reviewing the executed queries in the new snapshot, we can see for example that one of them has failed, and we can see the details of the error to correct it:
With the two snapshots generated we can now create a report that allows us to see the performance of the queries in the new environment. In the example we are going to see, we are going to compare by Buffer Gets (reads to disk or memory) which can be an indicator that queries are not being performed correctly. We have a number of other metrics (Runtime, CPU, etc.) that can also indicate performance problems.
When we have the report generated, it will give us a series of information that will allow us to quickly and easily see in which queries we may have problems during the Oracle migration from 11g to 19c. In this case, the data returned are:
We can see that the overall performance has been better with fewer reads in the second snapshot (19c) than in the first, but we can fine-tune the migration by reviewing the queries that have had a different plan and higher number of reads. In this case, you can see them by clicking on the yellow column in the regressed section:
We see that there are 10 worsening Queries and we can navigate over them to see the problem. If we focus on one of them, we see that the plan is different and reads to disk in memory increase:
Clicking on the plan in both environments shows that it changes:
This allows us to identify performance changes in the queries to avoid surprises/problems after the Oracle upgrade from 11g to 19c. In addition, it can provide us with all the information about possible errors. In this way, we can anticipate their correction by modifying the Queries with errors (permissions, syntax, unsupported functions, etc.).
So much for the Oracle upgrade from 11g to 19c. You can see other use cases here.
Contact us