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: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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: 1 2 3 4 5 6 7 8 9 10 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: 1 2 3 4 5 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! https://blog.dbi-services.com/sql-patch-another-way-to-change-the-plan-without-changing-the-code-1/