Oracle hints profiles baselines explained
Ideally fix performance issues globally: that is, with database parameter changes and/or dbms_stats.set_prefs. But if you need to fix locally one specific sql, Oracle comes with 6 (count them, 6!) methods for doing that.
In order of preference:
1) Optimizer Hint
Written into sql code. Keeps the fix in place even through future application sql code changes. But does require a change to application sql code, so may be impractical for third party applications.
Is just a /* comment with a + added (no space between the first * and the +), e.g.:
SELECT /*+ index_rs_asc ( t1 ( table1.column1 ) ) * / … FROM … WHERE … ;
You can get a list of the optimizer hints to use that will make a particular plan happen with:
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ( format => ‘+outline’ ) ) ;
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( sql_id => ‘sql_id_here’ , cursor_child_no => NULL , format => ‘+outline’ ) ) ;
Normally you only need one of those listed hints, and the double quotes and @SEL$ syntax is usually superfluous, as is keeping everything shouty in uppercase.
Optimizer hints should be reviewed periodically for relevance, especially around database version upgrade time – Oracle may have by now fixed the problem your optimizer hint was in place to solve, possibly with a better fix, especially likely if your optimizer hint was in response to early adoption of a new feature. The Oracle cost based optimizer was especially buggy when first released in 1992 with Oracle v7: if you have any optimizer hints dating back to that time (/*+first_rows I’m looking at you, /*+rule also), then they likely would be better removed nowadays.
Included with Standard Edition, and Enterprise Edition without any additional licence packs.
2) Sql Plan Management Baseline
If you can’t (easily or quickly) have an optimizer hint added into sql code, but yet still have plan instability picking a good plan sometimes and a bad plan other times, a Sql Plan Management Baseline will force the optimizer to always pick the good plan.
A limitation of a Sql Plan Management Baseline is that it is associated with a particular string of sql_text. Any change to the code sql statement, no matter how minor, will result in a new sql_id with no connection to the existing Sql Plan Management Baseline – so watch out for previously ‘fixed’ performance issues suddenly coming back to bite you after an application code deployment.
Before 18c, Sql Plan Management Baselines required Enterprise Edition (without any additional licence packs). From 18c it is included with Standard Edition also (although with some restrictions in the case of 18c Standard Edition).
See with:
SELECT * FROM dba_sql_plan_baselines ; — esp. the ‘enabled’ and ‘accepted’ columns.
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( sql_id => ‘sql_id_here’ , cursor_child_no => NULL , format => ‘+note’ ) ) ;
3) Sql Patch
A limitation of the above Sql Plan Management Baselines is they require the good plan to already have executed at least once and still be available in a database (which could be a different database: you can copy Sql Plan Management Baselines from one database to another, so if you can somehow even fake a test database to produce the good plan, you can use that to apply a Sql Plan Management Baseline to production).
Sql Patches are the alternative fallback when a good plan is not available to create a Sql Plan Management Baseline from.
Sql Patches are essentially the automatic injection of your desired optimizer hint direct into the sql text.
Sql Patches share the same limitation as Sql Plan Management Baselines – rendered ineffective by any change in sql_text/sql_id.
Requires Enterprise Edition (without any additional licence packs).
See with:
SELECT * FROM dba_sql_patches ;
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( sql_id => ‘sql_id_here’ , cursor_child_no => NULL , format => ‘+note’ ) ) ;
4) Sql Profile
A set of correlated optimizer statistics for all tables and columns referenced by a particular sql statement.
The difference from regular correlated optimizer statistics/virtual columns/function based indexes is that those three only apply across a single table; and those three also impact all sql globally, unlike Sql Profiles.
Tuning Advisor tends to recommend creating Sql Profiles like they’re going out of fashion. Not necessarily a bad thing, but there may be an underlying dbms_stats issue or Sql Plan Management Baseline that could be used first. Microsoft Sql Server has a similar issue with its Database Engine Tuning Advisor recommending ineffectual ‘create statistics’ in huge numbers, almost as if it feels it needs to come up with something whenever it is invoked, instead of just being honest.
Sql Profiles share the same limitation as Sql Plan Management Baselines – rendered ineffective by any change in sql_text/sql_id.
Requires Enterprise Edition and the Diagnostic licence pack and the Tuning licence pack.
See with:
SELECT * FROM dba_sql_profiles ; — esp. the ‘enabled’ column.
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( sql_id => ‘sql_id_here’ , cursor_child_no => NULL , format => ‘+note’ ) ) ;
5) Stored Outline
The old (10g) version of Sql Plan Management Baselines. Deprecated from 11g onwards, from which point Sql Plan Management Baselines should be used instead. Stored Outlines still work in the newer versions so far though (to 18c at least).
Included with Standard Edition, and Enterprise Edition without any additional licence packs.
See with:
SELECT * FROM dba_outlines ; — esp. the ‘used’ column.
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( sql_id => ‘sql_id_here’ , cursor_child_no => NULL , format => ‘+note’ ) ) ;
6) Parameters set at session level
ALTER SESSION SET [some_parameter] = [some_value] ;
Not best practice, because difficult to spot: is not shown anywhere in DBMS_XPLAN output. Setting parameters at system level is more obvious, would then be seen with ‘show parameter’, SELECT * FROM v$parameter, and in spfile and pfile.
But the alter session can be done at logon for a particular batch job or microservice, either with direct code or via a logon trigger.
Included with Standard Edition, and Enterprise Edition without any additional licence packs.
See with:
SELECT * FROM v$ses_optimizer_env ;
SELECT * FROM v$sql_optimizer_env ;
Microsoft SQL Server has three equivalents:
1) Table/Query/Join Hints – equivalent to Oracle Optimizer Hints, but syntax is to add “WITH” after the table name (table hint), or “OPTION ( hint )” at the end of the sql statement (query hint), or specify join type before join keyword (join hint); instead of an oracle-style /*+ comment at the start.
E.g.:
SELECT * FROM dbo.table1 t1 WITH ( INDEX ( myIndex ) ) … WHERE … ;
SELECT * FROM dbo.table1 t1 HASH JOIN dbo.table2 t2 ON … WHERE … ;
SELECT * FROM … WHERE … WITH OPTION ( FAST 100 ) ;
2) Plan Guides – equivalent to Oracle Sql Plan Management Baselines. See with:
SELECT * FROM sys.plan_guides ;
3) Parameters set at session level – e.g.:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
All three are available with Standard Edition and Enterprise Edition without any additional licence packs.
Leave a Reply