This case is tuning a SQL using SQL Profile quickly!

Problem

A top SQL caused high load in production database.

Trouble shooting

Launch SQLBooster, connect to the target database, click "Top SQL", it showed as below.

Click OK to get the details of the SQL. Click "Var" to choose to check the captured bind variables and their running performance data,

The buffer gets is very high. Click "SQL Tree" then "Analyze" to analyze the bottlenect of the performance.

After a while, we got the analyzing report,

In the first high-consistent-gets subquery, we noticed the WITH subfactoring view INSTRCTOR_LP has only 72 rows, let force it as the leader of the joins using SQL hints.

It looks pretty good! The Consistent Gets was reduced from 247979 to 4575!

Let's apply these hints to the original SQL to see its performance.

The result is very positive!

However, we had a problem. This is urgent case, if we change the SQL structure, we need to create a new pacth for the application, then apply it to the production. This will take long time. Is there a was to accelarate this progress? Yes, we there is. We can simple apply the outline data generated by the tuned SQL as SQL profile to the existing original SQL.

Click "Outline" tab, then click edit, we can see the outline data of the tuned SQL,

Select the SQL ID of the original SQL in the right dropdown list, then click "Apply", a SQL profile is created and applied to the original SQL, very quickly!

 Let's run the original SQL again,

A new SQL plan was generated for this SQL. In the top-right dropdown list, choose the new plan hash value to check the performance of the new plan,

The perfomance statistics data is shown in the left-bottom table,

A SQL profile is working on this SQL, and its consistent gets is very small.