Optimizing SQL Execution Plans in Oracle Using SQL Profiles

In the world of database administration, performance issues can appear unexpectedly, impacting critical routines and frustrating end-users. Recently, I encountered a performance issue where a routine was running slowly. After analyzing and tracing the session, I successfully optimized the query execution by applying a SQL Profile with a more efficient execution plan. Here’s how it went.

Step-by-Step Analysis and Optimization Process

1. Identifying the Issue
   After receiving a complaint about the slow performance of a specific routine (routine 146), I identified the sessions associated with the affected user. I found two active sessions for the same user, one on each node in our Oracle RAC environment.
2. Enabling Tracing
   To gather detailed information, I enabled tracing on both sessions. I observed that the session on node 2 had been active for over 4.53 minutes executing the same SQL query (SQL ID: fvckht837rq3q).
3. Examining the Execution Plan
   Using the @x script, I analyzed the execution plan to check for any missing indexes or high-cost operations. I found that the query was running without any index-related issues, so I proceeded to investigate if other execution plans could offer better performance.
4. Reviewing Available Plans with AWR Data
   To see historical execution plans, I used the @coe2 script, specifying the SQL ID fvckht837rq3q and Child Number 2. This script listed all available plans in AWR for this query, along with their average execution times. Here’s an example of the output I received:

   AVG_ET_SECS_MEM | AVG_ET_SECS_AWR | PLAN_HASH_VALUE | EXECUTIONS_MEM | EXECUTIONS_AWR
   --------------- | --------------- | --------------- | -------------- | --------------
         0.111595  |       836027571 |           2
         0.117445  |       777303881 |          16     |               1
         8.413716  |       413235616 |           6     |               5
       254.156695  |       807204715 |          13     |               -
       378.836647  |      1200825706 |           4     |

Among the plans, I identified one with a significantly lower average execution time of 0.111 seconds, associated with the Plan Hash Value 836027571.

5. Applying the SQL Profile
   Using the @coe2 script, I generated a SQL Profile for the optimal Plan Hash Value. This created a file named coe_xfr_sql_profile_fvckht837rq3q_836027571.sql. I executed this script, applying the SQL Profile to enforce the more efficient plan.
6. Testing and Verification
   After applying the SQL Profile, I advised the user to close their session and re-run the routine. The result was immediate—the routine that had previously taken several minutes to execute completed almost instantaneously.

Conclusion

This experience highlights the power of SQL Profiles in Oracle Database for improving query performance without changing the SQL code. By enforcing a more efficient execution plan, we can address performance issues in production without interrupting ongoing operations.