We tell you how to filter a large number of Queries and locate only those that change their execution plan thanks to Fast Replay.
We are going to present you an interesting functionality of our Queries analysis and comparison tool in Oracle. A functionality that saves time and effort when we need to analyze what will be the behavior of a large number of Queries. For example, in a database migration or upgrade.
Scenario:
We have a database migration or upgrade. We need to reduce performance risks and avoid any loss of service of our Oracle after the change. One of the tasks is to evaluate how our Queries will behave. We have our Query_Performance tool to capture Queries and their statistics and launch them against the new environment and compare the returns. The problem comes when it is not worth knowing what the behavior of a few Queries will be, or we do not know which ones to start with… or we cannot identify the heaviest, or the most recurrent. Or we simply do not know which may be the most affected in performance after the changes (migration, upgrade, patch …).
Proposal:
Faced with this scenario and knowing that a capture and execution of all the queries can be a heavy and slow work in itself due to the volume of the queries, we opted for a first beat of less impact.
This operation is called Fast Replay. In order to speed up the results, you will not run the queries bringing all your data and execution statistics. Simply, in a very fast way it will show us those Queries in which the “execution plan” varies.
We can generate a report on fast replay. It shows in a quick way which are the Queries that Oracle estimates change its execution plan. In this way, we can focus our next study only on these queries.
In this way, we will have a set of Queries whose plan changes. With it we will make a complete “Replay” to obtain and compare all the metrics offered by Oracle for this selection of Queries.
Snapshot Difference capture example with Fast Replay
Let’s see how the Application does it with a capture example called Snapshot Difference.
It is about capturing from the “current environment” (production) the largest number of Queries. Once captured launch them against the “new environment” (test or development where we will see the behavior that these queries will have) this set of large number of queries. These queries in the example that we show you have been saved in a snapshot called “test capture 11g” and has 901 queries that we want to review.
Running this number of queries in our test environment can be a problem. It can charge it or lengthen a lot in time. So to make a first filter we use the fast replay option. In which we will only check the execution plan of the captured queries. To do this we run the replay process with the following options:
This process is much faster and will allow us to know the execution plans in the new environment. It will also inform us if the query runs or generates errors. At the end of the process we will have two snapshots with the information we need:
If we want to focus exclusively on the queries that change their execution plan with the version upgrade, we use the capture type called “Snapshot difference”. To do this, we simply select the two previous snapshots and the criteria to filter. In this case all the queries of the snapshot executed with fast replay and whose plans are different from the capture in 11:
The selected option is:
At the end of this process we have generated a new snapshot with 54 queries on which to focus our study. With it we have discarded a large number of queries and it will help us focus our efforts on which you can have a change in behavior:
Contact us