Enhancing Oracle SQL Performance: A Journey with SQL Baselines

As we all know, the performance of Oracle SQL queries plays a critical role in the overall performance of applications running on top of the Oracle Database. Performance tuning is essential, and while the Oracle Database Optimizer generally selects the best execution plan for SQL queries, it's not always perfect. This is where performance tuning becomes crucial.

One common approach to tuning a SQL query is to add a hint, guiding the optimizer to follow a specific path. However, adding a hint to an existing SQL query, even without changing the query's logic, still alters the application code. In today’s ERP world, changing production application code is not a trivial task. Moreover, from an optimizer's perspective, it’s better to avoid adding explicit hints to the application code. So, how can we handle this effectively?

The Solution: Oracle SQL Baselines

This is where Oracle SQL Baselines come to our rescue. Oracle provides a feature that allows you to force the optimizer to adopt the execution plan of a hinted statement for the non-hinted SQL statement. This approach helps us avoid the complexities of modifying application code to include hints and is considered a better practice than explicitly adding hints.

By using SQL Baselines, we can ensure that our SQL queries perform optimally without the need for disruptive code changes. This method leverages the database's capability to manage and optimize execution plans efficiently, providing a robust solution to performance tuning challenges.

Before Getting into SQL Baselines, Lets understand a common problem which we face with the execution plan generated by Oracle Database optimizer. Let's first understand it with an example.

Example:

Let's say we need to find Number of students who have completed 15 Years of Formal education with following criteria

• CGPA in class 10 > 80%
• CGPA in class 12 > 70%
• CGPA in UG > 65%
• Number of students Completed 10th - 500000
• Number of students Completed 12th - 400000
• Number of students Completed UG - 250000

Execution Plan to find Number of students who have completed 15 Years of Formal with above mentioned criteria

Plan 1:

• 10th Data: Retrieve the data of students who completed 10th and filter those with a CGPA > 80. Let's assume this results in 100,000 students.
• 12th Data: Retrieve the data of students who completed 12th and filter those with a CGPA > 70. Let's assume this results in 100,000 students. Compare this with the 10th grade data (100,000 students) and retrieve matching records. Assume we end up with 75,000 students.
• UG Data: Retrieve the data of students who completed UG and filter those with a CGPA > 65. Let's assume this results in 50,000 students. Compare this with the 12th grade data (75,000 students) and retrieve matching records. Assume we end up with 40,000 students.

Rows Retrieved in Each Step:

• 100,000
• 75,000
• 40,000

Plan 2:

• UG Data: Retrieve the data of students who completed UG and filter those with a CGPA > 65. Let's assume this results in 75,000 students.
• 12th Data: Retrieve the data of students who completed 12th and filter those with a CGPA > 70. Let's assume this results in 100,000 students. Compare this with the 75,000 UG students and retrieve matching records. Assume we end up with 60,000 students.
• 10th Data: Retrieve the data of students who completed 10th and filter those with a CGPA > 80. Let's assume this results in 100,000 students. Compare this with the 60,000 12th students and retrieve matching records. Assume we end up with 40,000 students.

Rows Retrieved in Each Step:

• 75,000
• 60,000
• 40,000

which tells us that we should be reaching the place where the sample space is low and go ahead with processing from there. This approach ensures that we start with a smaller, more manageable data set and gradually filter it through subsequent stages, reducing the overall processing load and increasing efficiency.

By starting with the smallest subset (in this case, the UG data in Plan 2) and moving backward through the filtering process, we minimize the number of comparisons and streamline the data retrieval process. This method is especially useful when dealing with large datasets, as it helps to manage computational resources effectively and ensures that only the most relevant records are processed at each step.

Now Just think Grade and Students as Tables and Rows respectively will give an idea how it works in terms of the Oracle Database.

Ok, now let's jump back to Oracle SQL Baselines and their role in influencing the optimizer path.

Suppose an Oracle SQL Developer adds a SQL hint to an existing SQL statement, significantly reducing its execution time from a long duration to just a few seconds. However, the actual production application code does not include this hint. As DBAs, our task is to guide the optimizer to use the SQL execution plan of the hinted SQL statement for the non-hinted SQL statement.

How do we do that? I have covered the detailed steps below.

• Capture the SQL Baseline of the Non-Hinted SQL Statement and Disable it.

DECLARE
RET BINARY_INTEGER;
L_SQL_ID VARCHAR2(13);
 
L_PLAN_HASH_VALUE NUMBER;
L_FIXED VARCHAR2(3);
L_ENABLED VARCHAR2(3);
BEGIN
L_SQL_ID := '<NON_HINTED_SQL_ID>';
L_PLAN_HASH_VALUE := TO_NUMBER('<PLAN_HASH_VALUE_NON_HINTED>');
L_FIXED := 'YES';
L_ENABLED := 'NO';
RET := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
     SQL_ID=>L_SQL_ID,
     PLAN_HASH_VALUE=>L_PLAN_HASH_VALUE,
     FIXED=>L_FIXED,
     ENABLED=>L_ENABLED);
END;
/        

• Now Run the SQL with Hints and Capture the SQL_ID and Plan Hash Values of the Hinted SQL Statements

SELECT  SQL_ID , PLAN_HASH_VALUE FROM GV$SQL WHERE SQL_TEXT LIKE '%<SQL_TEXT>%'        

• Capture the SQL Baseline of the Hinted SQL Statement with its SQL_ID and PLAN_HASH_VALUE and Associate it with the SQL_HANDLE of the Non-Hinted SQL Statement.

DECLARE
RET BINARY_INTEGER;
L_SQL_ID VARCHAR2(13);
 
L_PLAN_HASH_VALUE NUMBER;
L_FIXED VARCHAR2(3);
L_ENABLED VARCHAR2(3);
L_SQL_HANDLE VARCHAR2(20);
BEGIN
L_SQL_ID := '<HINTED_SQL_ID>';
L_PLAN_HASH_VALUE := TO_NUMBER('<PLAN_HASH_VALUE_HINTED>');
L_FIXED := 'YES';
L_ENABLED := 'YES';
L_SQL_HANDLE := '<SQL_HANDLE_NON_HINTED>';
RET := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
     SQL_ID=>L_SQL_ID,
     PLAN_HASH_VALUE=>L_PLAN_HASH_VALUE,
     FIXED=>L_FIXED,
     ENABLED=>L_ENABLED,
	 SQL_HANDLE=>L_SQL_HANDLE);
END;
/        

• Now you should see two SQL Baselines with same SQL Handle, but different Plan Name and the Non-Hinted Plan should be disabled and Hinted Base Line should be enabled, verify it with the Following Query.

SELECT SS.SQL_ID,SBL.SQL_HANDLE,SBL.PLAN_NAME,SBL.CREATED, SBL.LAST_MODIFIED,SBL.ORIGIN,SBL.LAST_EXECUTED,SBL.LAST_VERIFIED, SBL.ENABLED, SBL.ACCEPTED, SBL.FIXED, SBL.REPRODUCED, SBL.ADAPTIVE, SBL.OPTIMIZER_COST FROM GV$SQLSTATS SS ,DBA_SQL_PLAN_BASELINES SBL WHERE SS.EXACT_MATCHING_SIGNATURE=SBL.SIGNATURE ORDER BY SBL.LAST_MODIFIED DESC AND SS.SQL_ID='<SQL_ID>';        

Now the Oracle Database Optimizer while executing the query will take the Baseline of Hinted SQL Statement for the Non-Hinted SQL Statement Execution Since the Non-Hinted SQL Baseline is disabled and Hinted SQL Baseline is created with Same SQL Handle as Non-Hinted.

This approach allows us to avoid the bottlenecks associated with adding hints to the application code while still achieving faster query run times. As mentioned earlier, it is always better not to hard-code explicit hints into the SQL query.

Happy learning! If you have any suggestions or questions, feel free to reach out. Your feedback and insights are always welcome as we strive to continuously improve our understanding and practices. Thank you for reading!

Prakashvel M

Oracle Database Administrator / Infosys Limited

References:

SQL Plan Management in Oracle Database 19c