[Oracle] Hinting a SQL without adding a hint

stefan_koehler · ‎11-23-2010

Introduction

Almost every SAP basis guy or DBA knows that situation - you got a piece of code by a software vendor and the executed SQLs are very slow. You know the cause of the problem, but you can not fix the code, because of you don't have access or you don't want / aren't allowed to modify it by your own ... or maybe the problem is caused by an optimizer bug.

In this case SQL Profiles can be your escape out of this situation. It is a quick "fix" of the problem until you get a final solution. SQL Profiles are part of the additional Oracle Enterprise Option "Tuning Pack". It is availabe since Oracle 10g - for more information about its licensing - please check sapnote #740897.

The situation

Let's imagine the following simple example: An ABAP program executes a query on MSEG with a selection criteria on column WERKS. Due some reasons it performs an "INDEX SKIP SCAN" on index "MSEG~M" instead of a full table scan, which would be your preferred execution plan, because of column WERKS has a low selectivity.

My test SQL has the following syntax: "SELECT /* TEST-SQL */ * FROM MSEG WHERE WERKS = :A0"

Now let's check the SQL statement with transaction DBACOCKPIT (or ST04old) and take a look at its execution plan.

Let's stop at this point for a moment and summarize - this particular SQL has the SQL ID "a6uscshbqytpk" (with child number 0) and is performing an "INDEX SKIP SCAN" on index "MSEG~M". Bring it to your mind (in a simple way) again - the SQL ID represents the SQL text itself and the child (cursor) number includes the execution plan, that is used by that SQL execution. This is an important part to know for the next steps.

The procedure with SQL Profiles

Now let's do it our own way - we want to access the data via FULL TABLE SCAN. The easiest way to achieve this goal is to add a hint to that SQL. You can now rewrite that particular SQL statment with a hint and execute it - if the performance is fine get the SQL ID and the child cursor number of your hinted SQL.

Select now some additional information to that SQL ID and child cursor with the following SQL:

You will always find a section called "Outline Data" in the output of the previous SELECT. The "Outline Data" is something like a stored instruction for the optimizer what to do (and in which way). You can see, that the hinted SQL has an instruction called "FULL(@"SEL$1" "MSEG"@"SEL$1")" - this instruction is needed to tell the optimizer what to do with the original statement. In this case it is very simple, but if you have a complex query you need the whole outline data to reconstruct that particular execution plan for the original SQL safely.

Now you can create a SQL profile for the original statement and add that optimizer instruction to it. You have to use the undocumented PL/SQL procedure DBMS_SQLTUNE.IMPORT_SQL_PROFILE for that. Run the following anonymous PL/SQL block as SYSDBA (this is important, because of the SAP schema user has not the needed privileges).

DECLARE
   p_sqltext     v$sqlarea.sql_fulltext%type;
BEGIN
     select sql_fulltext into p_sqltext from v$sqlarea where sql_id = 'a6uscshbqytpk';

     DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => p_sqltext,
                                     profile => sqlprof_attr('FULL(@"SEL$1" "MSEG"@"SEL$1")'),
                                     name => 'a6uscshbqytpk_MSEG',
                                     force_match => TRUE);
     DBMS_SQLTUNE.ALTER_SQL_PROFILE('a6uscshbqytpk_MSEG', 'STATUS', 'DISABLED');
     DBMS_SQLTUNE.ALTER_SQL_PROFILE('a6uscshbqytpk_MSEG', 'STATUS', 'ENABLED');
END;

Review of the impact

Now let's check the original (not hinted/modified) SQL and its execution plan again.

Woohoo - what's that? We did not change the original SQL or manipulated any statistics, which can cause unwanted side effects, but the SQL is performing a FULL TABLE SCAN now (like we wanted).

This particular SQL has the same SQL ID "a6uscshbqytpk" as before (which means it has not changed its syntax), but got a new child cursor (= execution plan). Let's take a closer look what's the reason for this beavhiour.

If you check the cursor with DBMS_XPLAN, you will see a note at the bottom of the query result. It tells you, that this particular SQL is using an SQL profile now - the one that we manipulated with DBMS_SQLTUNE.IMPORT_SQL_PROFILE before.

To get an overview of the created SQL Profiles you can use this query:

Summary

In my opinion this a very good (and easy) way to manipulate execution plans without changing the SQL code or any unwanted side effects on other SQLs by changing statistics, etc.. It is completely transparent to the application and works smoothly.

I have used it a few times on non-SAP and SAP oracle databases to work around a problem until i get a solution.

Keep in mind that this procedure only works, if the SQL text is static. You can also easily hint complex queries by using the whole outline data of your hinted SQL.

If you have any further questions - please feel free to contact me.