SQL Patch: Another way to change the plan without changing the code

Recently, at a customer site, I faced a performance issue. However, as often the statement is embedded in the application so it’s not possible to rewrite the query. In this blog post, we’ll change the execution plan to solve the problem without changing the code – thanks to SQL Patch.

The faulty statement was part of a daily job that was configured and started from the application during the night. Every night, the statement failed due to a lack of space in the TEMP tablespace.

Find the statement

So the first step is to find the statement because the sql_id was not present in the alert in the alert log.
ASH is very useful to have a look at what is running on the system at a specific time. And it tracks the amount of TEMP space allocated so a simple query in the interval is helpful:

SQL> select sample_time, session_id, sql_id, temp_space_allocated
 from v$active_session_history
 where sample_time > to_date('12-09-14 01:16:00','dd-mm-yy hh24:mi:ss')
 and sample_time < to_date('12-09-14 01:19:00','dd-mm-yy hh24:mi:ss')
 and temp_space_allocated > 0;
SAMPLE_TIME               SESSION_ID SQL_ID        TEMP_SPACE_ALLOCATED
 ------------------------- ---------- ------------- --------------------
 12-SEP-14 01.17.18.551 AM       1082 bn7zqwkfgtr38            487587840
 12-SEP-14 01.17.17.550 AM       1082 bn7zqwkfgtr38            434634752
 12-SEP-14 01.17.16.548 AM       1082 bn7zqwkfgtr38            380633088
 12-SEP-14 01.17.15.546 AM       1082 bn7zqwkfgtr38            324534272
 12-SEP-14 01.17.14.545 AM       1082 bn7zqwkfgtr38            271581184
 12-SEP-14 01.17.13.543 AM       1082 bn7zqwkfgtr38            226492416
 12-SEP-14 01.17.12.542 AM       1082 bn7zqwkfgtr38            175112192
 12-SEP-14 01.17.11.541 AM       1082 bn7zqwkfgtr38            120061952
 12-SEP-14 01.17.10.538 AM       1082 bn7zqwkfgtr38             70254592
 12-SEP-14 01.17.09.536 AM       1082 bn7zqwkfgtr38             18874368
 10 rows selected.

Good news, only one statement was using TEMP space at the moment of the error and it consumed all the 500 MB allocated to the TEMP tablespace.

The faulty statement has been identified. As 500 MB seems a little bit small, the first obvious try is to increase the TEMP tablespace and restart the statement.

Analyze the issue

However when the statement continue to fail with 2 GB of TEMP space, it’s time to have a deeper look at the execution plan.
The application uses a view based on user_extents to track object fragmentation. User_extent is itself a view on other dictionary views and in that case we experienced a problem, when using this view the plan needs a lot of TEMP space.

Using dba_extents provided a different execution plan and the same statement completes instantly and without the need of TEMP space. Changing the view user_extent (even if it has been suggested on some thread) is not an option and changing the application view is also not possible.

So how can we influence the Optimizer to change the execution plan?

I’m not a big fan of using hints but in such cases I’m glad they exist. We have several options but one of my first try is often using dynamic sampling.
If the Optimizer has more accurates statistics we obtain better execution plan.
I’ll skip the details but in that case using dynamic sampling solved my problem, the new execution plan allowed the query to return instantly without any TEMP space.

Second question, how can we provide the new execution plan for an existing query without changing the code?

Oracle provides three mechanism to deal with execution plans: SQL Profiles, SQL Baselines and SQL Patches.
Profiles are proposed by the Tuning Advisor and its mostly based on adapting the cardinalities to match the reality.
Baselines allows us to provide a list of accepted execution plan for a statement.
SQL Patches are part of the SQL Repair Advisor and adds hints to a specific statement. More details in the article SQL Repair Advisor

I chose to implement a SQL Patch: in that case, a Baseline is not helpful because the hint dynamic_sampling is not saved as part of the execution plan. The additional statistics needed by the Optimizer to allow the good plan are not kept.

Implement SQL Patch

Like Profiles, Patches should be proposed by an Oracle advisor, the SQL Repair Advisor and then accepted. In that specific case, we’ve tried to run the SQL Repair Advisor but there was no proposal. However for SQL Patches there is an article on the Oracle blog that explain how to add a hint to a statement.

The implementation by itself is pretty easy, the function i_create_patch allows to specify a statement, a text introduced as hint and a name for the SQL Patch:

declare
   v_sql CLOB;
begin
   select sql_text into v_sql from dba_hist_sqltext where sql_id='bn7zqwkfgtr38';
   sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => v_sql,
      hint_text => 'DYNAMIC_SAMPLING(4)',
      name      => 'user_extents_patch');
end;
/

Then it’s also very easy to check the status of the Patch in the system using the view DBA_SQL_PATCHES:

SQL> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch';

NAME               STATUS   CREATED                      SQL_TEXT
------------------ -------- ---------------------------- -------------------------------------------------------------------------------
user_extents_patch ENABLED  12-SEP-14 10.25.49.000000 AM select all tablespace_name, segment_name, segment_type, count(*) "numsegs" from

The SQL Patch feature has an advantage over Baselines and Profiles, it’s part of SQL Repair Advisor which is free of charge and can be used in both Standard Edition and in Enterprise Edition without Tuning Pack. Now that outlines are deprecated in 12c, it’s a good tuning option for Standard Edition.

As for Baselines and Profiles, I recommend keeping track of SQL Patches implemented on your systems. It’s always more a workaround than a solution but can be very useful.

Enjoy!