[Oracle] Hinting a SQL without adding a hint
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.
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).
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’);
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:
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.
thanks for sharing this procedure! I didn't know that it is relatively easy to add a SQL profile with such a custom hint to some SQL statement.
I just wonder why you chose this approach instead of the simple Oracle way:
Enter SQL_ID a6uscshbqytpk and check whether the
SQL Tuning Advisor also comes to the same conclusion as you. For more complex statements the SQL Tuning Advisor might even find better solutions than oneself, or at least find solutions faster.
> I just wonder why you chose this approach instead of the simple Oracle way
Well, in my experience the SQL Tuning Advisor doesn't always suggest the right thing. I have seen many times, that it suggest an additional index instead of some hints or additional statistics. So i like to do it manually, so i am sure that it does the right thing :-))
Maybe it does the right decision in this particular simple case, but i have seen it otherwise many times.
needless to say that this blog is well written and edited and also interesting (well done :-))
However, I really don't envy the DB support person (or the DBA with the tendency to forget stuff...) that has to analyze such a statement.
With the standard analysis approach employed in SAP systems, you'll barely figure out that there are SQL Profiles used.
And without that there's no way to explain a query execution path.
It's just another way to make stuff even more complicated than it already is (although I'd prefer this over manipulating CBO stats...).
i am not a friend of manipulating statistics, because of you can't guess the impact on all the other SQLs - so it's some kind of fixing one problem and maybe getting some others .. but all depends on the situation of course.
Well i always use DBMS_XPLAN to get the execution plan and so you will see always the footnote ... DBMS_XPLAN can also be used via SAP (DBACOCKPIT) as i have shown in my example.
This approach should also be the exception, but it can be useful sometimes ...
i just want to give a short update on this topic with Oracle 11g R2.
You can perform these steps easier with Oracle 11gR2 and SQL Baselines.
Jonathan Lewis wrote a blog about it - check it out:
here is another "official" possibility on Oracle 11g R2 using SQL patch: