We are going to show how a migration test from Oracle 11 to PostgreSQL 12.5 would be performed with the new version of the Query Performance tool. To perform the migration test it is necessary to replicate the data that we have in our Oracle instance in the Postgres instance, for this we have used the migrationtoolkit of EDB that allows us to migrate both the object structure and the data. Once this migration is done, we can test our Oracle database queries in Postgres.
Steps to follow for testing to migrate from Oracle to PostgreSQL:
1-. Creating a WORK for Oracle to PostgreSQL migration testing
From the WORKS window we generate a new one:
In the work data we enter:
Once the job has been created we proceed to give it content. What we will do is to generate a snapshot with the Oracle queries we want to test.
2-. Capture queries from memory for a particular schema:
We access our newly created job and launch a capture:
In the capture filter we simply put the original schema from which we want to extract the queries. We do not filter by query type or any other field to get all queries:
In this case, we capture the queries from memory and create a snapshot called Capture Oracle.
When we launch the capture we can see the progress on the main screen and the capture data:
Once the process is finished, we can see that we have captured 82 queries and that we have them in the snapshot:
3. On the 82 Oracle queries we are going to perform a replay in Postgres to see the sharing of the sentences in the new environment. To do this we select the replay option in the same work and select the connection to the Postgres server:
We select the snapshot replay option and a normal replay (not fast replay) as we want to measure the time spent by the queries and with the fast replay we only capture the execution plans. We select the Capture Oracle snapshot and we will save the execution in Replay Postgres:
We launch the replay and we can see the progression of the job until it is finished:
In the replay details we can see which queries have been executed successfully and which have failed:
When we go back to the detail of the work we can see the two snapshots with the number of queries of each one, in this case we can see that the same queries have been executed:
4. Once we have the two snapshots we can generate a report to see the behaviour we can foresee in the migration.
We choose the previous work and the two snapshots we have loaded with data. As we want to compare by time of the queries we choose the Elapsed time method and name the report:
Once generated, we can view the details of the report by choosing the report:
In the header of the report we can see the data of the report:
También tenemos una sección Summary con los datos de la comparativa:
We can see the number of successful and failed queries. We also have the data with the predicted improvement or regression of the queries that have been successfully executed. In the lower section we can see which queries have been executed well and which of them have been executed with errors, so we can navigate through them and correct the possible errors:
By clicking on the queries with error we can see what message has been produced, in this case it seems that the structure has not been migrated correctly:
We also have a section that tells us which consultations are improving, getting worse or staying with the same statistics:
If we check the Improved queries section we can see the improved queries and a comparison of the plans:
We can do the same for those that get worse:
As you can see, in a very quick way we can make a study of how the queries of our application will behave in a migration from Oracle to PostgreSQL, obtaining the queries that will fail as well as their performance in the new environment. We can also see which queries work best and which queries need database query optimisation for a smooth migration.
Contact us