{"id":11443,"date":"2022-01-14T20:32:55","date_gmt":"2022-01-14T19:32:55","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=11443"},"modified":"2023-11-08T16:36:24","modified_gmt":"2023-11-08T15:36:24","slug":"finding-wrong-hints","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2022\/01\/14\/finding-wrong-hints\/","title":{"rendered":"Finding Wrong Hints"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

I have used the Oracle Database for many years. And I use hints. For experiments, but also in production code. There are cases when you know more than the Oracle Database. For example about the cardinality of a data source or the number of result rows to process or the number of expected executions of a statement. Hints are a way to provide additional information, limit the solution space and enable the database to do a better job. That’s a good thing.<\/p>\n\n\n\n

Hints Are Instructions<\/h2>\n\n\n\n

Hints are passed as special comments at a certain position in SQL statements. They are comments, but they are also instructions. They have to be followed. However, there are cases when hints are not applicable. For example when you request the optimizer to use an index when there is no index defined for the underlying table. In such a case the Oracle Database has basically two options. Either throw an error or ignore the invalid instruction and find another solution. The Oracle Database does the latter.<\/p>\n\n\n\n

Hint Report<\/h2>\n\n\n\n

Starting with version 19c you can produce a hint report that reveals unused hints. Here’s an example:<\/p>\n\n\n\n

Unused hint<\/span><\/path><\/path><\/svg><\/span>
create<\/span> <\/span>table<\/span> <\/span>t<\/span> (c1 <\/span>integer<\/span>, c2 <\/span>varchar2<\/span>(<\/span>20<\/span>));<\/span><\/span>\ninsert into<\/span> t <\/span>values<\/span> (<\/span>1<\/span>, <\/span>'one'<\/span>);<\/span><\/span>\ninsert into<\/span> t <\/span>values<\/span> (<\/span>2<\/span>, <\/span>'two'<\/span>);<\/span><\/span>\nselect<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> dbms_xplan.display_cursor(format => <\/span>'basic +hint_report'<\/span>);<\/span><\/span>\n<\/span>\nEXPLAINED <\/span>SQL<\/span> <\/span>STATEMENT<\/span>:<\/span><\/span>\n------------------------<\/span><\/span>\nselect<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span><\/span>\n <\/span><\/span>\nPlan <\/span>hash<\/span> <\/span>value<\/span>: <\/span>1601196873<\/span><\/span>\n <\/span><\/span>\n------------------------------------------<\/span><\/span>\n| Id  | Operation                 | <\/span>Name<\/span> |<\/span><\/span>\n------------------------------------------<\/span><\/span>\n|   <\/span>0<\/span> | <\/span>SELECT<\/span> <\/span>STATEMENT<\/span>          |      |<\/span><\/span>\n|   <\/span>1<\/span> |  <\/span>TABLE<\/span> ACCESS STORAGE FULL| T    |<\/span><\/span>\n------------------------------------------<\/span><\/span>\n <\/span><\/span>\nHint Report (identified <\/span>by<\/span> operation id \/ Query <\/span>Block<\/span> <\/span>Name<\/span> \/ <\/span>Object<\/span> Alias):<\/span><\/span>\nTotal hints <\/span>for<\/span> <\/span>statement<\/span>: <\/span>1<\/span> (U - Unused (<\/span>1<\/span>))<\/span><\/span>\n---------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span> \/ <\/span>"T"<\/span>@<\/span>"SEL$1"<\/span><\/span>\n         U -  <\/span>index<\/span>(t)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The hint index(t)<\/code> defined on line 4 is valid, but it’s reported as unused on line 25. No wonder. There is no index defined on table t<\/code>.<\/p>\n\n\n\n
Let’s create an index and rerun the query.<\/p>\n\n\n\n
Used hint<\/span><\/path><\/path><\/svg><\/span>
create<\/span> <\/span>unique index<\/span> <\/span>t_c1_i<\/span> <\/span>on<\/span> t(c1);<\/span><\/span>\nselect<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> dbms_xplan.display_cursor(format => <\/span>'basic +hint_report'<\/span>);<\/span><\/span>\n<\/span>\nEXPLAINED <\/span>SQL<\/span> <\/span>STATEMENT<\/span>:<\/span><\/span>\n------------------------<\/span><\/span>\nselect<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span><\/span>\n <\/span><\/span>\nPlan <\/span>hash<\/span> <\/span>value<\/span>: <\/span>2704710798<\/span><\/span>\n <\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n| Id  | Operation                           | <\/span>Name<\/span>   |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n|   <\/span>0<\/span> | <\/span>SELECT<\/span> <\/span>STATEMENT<\/span>                    |        |<\/span><\/span>\n|   <\/span>1<\/span> |  <\/span>TABLE<\/span> ACCESS <\/span>BY<\/span> <\/span>INDEX<\/span> ROWID BATCHED| T      |<\/span><\/span>\n|   <\/span>2<\/span> |   <\/span>INDEX<\/span> <\/span>RANGE<\/span> SCAN                  | T_C1_I |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nHint Report (identified <\/span>by<\/span> operation id \/ Query <\/span>Block<\/span> <\/span>Name<\/span> \/ <\/span>Object<\/span> Alias):<\/span><\/span>\nTotal hints <\/span>for<\/span> <\/span>statement<\/span>: <\/span>1<\/span><\/span>\n---------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span> \/ <\/span>"T"<\/span>@<\/span>"SEL$1"<\/span><\/span>\n           -  <\/span>index<\/span>(t)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now the hint index(t)<\/code> defined on line 2 is reported as used on line 24.<\/p>\n\n\n\n
Mixing Hints and Comments<\/h2>\n\n\n\n
What happens if we mix hints and comments? It depends on where you place the comment. Let’s look at the next example.<\/p>\n\n\n\n
Hint followed by unknown tokens<\/span><\/path><\/path><\/svg><\/span>
select<\/span> <\/span>\/*+ index(t) forcing unnecessary index access *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> dbms_xplan.display_cursor(format => <\/span>'basic +hint_report'<\/span>);<\/span><\/span>\n<\/span>\nEXPLAINED <\/span>SQL<\/span> <\/span>STATEMENT<\/span>:<\/span><\/span>\n------------------------<\/span><\/span>\nselect<\/span> <\/span>\/*+ index(t) forcing unnecessary index access *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> <\/span><\/span>\nc1 > <\/span>0<\/span><\/span>\n <\/span><\/span>\nPlan <\/span>hash<\/span> <\/span>value<\/span>: <\/span>2704710798<\/span><\/span>\n <\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n| Id  | Operation                           | <\/span>Name<\/span>   |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n|   <\/span>0<\/span> | <\/span>SELECT<\/span> <\/span>STATEMENT<\/span>                    |        |<\/span><\/span>\n|   <\/span>1<\/span> |  <\/span>TABLE<\/span> ACCESS <\/span>BY<\/span> <\/span>INDEX<\/span> ROWID BATCHED| T      |<\/span><\/span>\n|   <\/span>2<\/span> |   <\/span>INDEX<\/span> <\/span>RANGE<\/span> SCAN                  | T_C1_I |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nHint Report (identified <\/span>by<\/span> operation id \/ Query <\/span>Block<\/span> <\/span>Name<\/span> \/ <\/span>Object<\/span> Alias):<\/span><\/span>\nTotal hints <\/span>for<\/span> <\/span>statement<\/span>: <\/span>4<\/span> (E - Syntax error (<\/span>3<\/span>))<\/span><\/span>\n---------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span><\/span>\n         E -  forcing<\/span><\/span>\n         E -  <\/span>index<\/span> <\/span><\/span>\n         E -  unnecessary<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span> \/ <\/span>"T"<\/span>@<\/span>"SEL$1"<\/span><\/span>\n           -  <\/span>index<\/span>(t)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The comment forcing unnecessary index access<\/code> on line 1 is interpreted as a series of hints and reported as errors on lines 24 to 26. The token access<\/code> was not reported. However, the hint index(t)<\/code> was reported as used on line 29.<\/p>\n\n\n\n
What happens if we move the comment to the beginning?<\/p>\n\n\n\n
Unknown tokens followed by hint<\/span><\/path><\/path><\/svg><\/span>
select<\/span> <\/span>\/*+ forcing unnecessary index access index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> dbms_xplan.display_cursor(format => <\/span>'basic +hint_report'<\/span>);<\/span><\/span>\n<\/span>\nEXPLAINED <\/span>SQL<\/span> <\/span>STATEMENT<\/span>:<\/span><\/span>\n------------------------<\/span><\/span>\nselect<\/span> <\/span>\/*+ forcing unnecessary index access index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> <\/span><\/span>\nc1 > <\/span>0<\/span><\/span>\n <\/span><\/span>\nPlan <\/span>hash<\/span> <\/span>value<\/span>: <\/span>2704710798<\/span><\/span>\n <\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n| Id  | Operation                           | <\/span>Name<\/span>   |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n|   <\/span>0<\/span> | <\/span>SELECT<\/span> <\/span>STATEMENT<\/span>                    |        |<\/span><\/span>\n|   <\/span>1<\/span> |  <\/span>TABLE<\/span> ACCESS <\/span>BY<\/span> <\/span>INDEX<\/span> ROWID BATCHED| T      |<\/span><\/span>\n|   <\/span>2<\/span> |   <\/span>INDEX<\/span> <\/span>RANGE<\/span> SCAN                  | T_C1_I |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nHint Report (identified <\/span>by<\/span> operation id \/ Query <\/span>Block<\/span> <\/span>Name<\/span> \/ <\/span>Object<\/span> Alias):<\/span><\/span>\nTotal hints <\/span>for<\/span> <\/span>statement<\/span>: <\/span>3<\/span> (E - Syntax error (<\/span>3<\/span>))<\/span><\/span>\n---------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span><\/span>\n         E -  forcing<\/span><\/span>\n         E -  <\/span>index<\/span> <\/span><\/span>\n         E -  unnecessary<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The same invalid hints are reported as before on lines 24 to 26. However, the hint index(t)<\/code> was used but not reported as such. This seems to be a limitation of the current hint report in the Oracle Database 21c.<\/p>\n\n\n\n
Anyways, it clearly shows that you should not mix comments and hints. Instead, you should write it like this:<\/p>\n\n\n\n
Distinguish between comments and hints<\/span><\/path><\/path><\/svg><\/span>
select<\/span> <\/span>\/* forcing unnecessary index access *\/<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span>where<\/span> c1 > <\/span>0<\/span>;<\/span><\/span>\nselect<\/span> * <\/span>from<\/span> dbms_xplan.display_cursor(format => <\/span>'basic +hint_report'<\/span>);<\/span><\/span>\n<\/span>\nEXPLAINED <\/span>SQL<\/span> <\/span>STATEMENT<\/span>:<\/span><\/span>\n------------------------<\/span><\/span>\nselect<\/span> <\/span>\/* forcing unnecessary index access *\/<\/span> <\/span>\/*+ index(t) *\/<\/span> * <\/span>from<\/span> t <\/span><\/span>\nwhere<\/span> c1 > <\/span>0<\/span><\/span>\n <\/span><\/span>\nPlan <\/span>hash<\/span> <\/span>value<\/span>: <\/span>2704710798<\/span><\/span>\n <\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n| Id  | Operation                           | <\/span>Name<\/span>   |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n|   <\/span>0<\/span> | <\/span>SELECT<\/span> <\/span>STATEMENT<\/span>                    |        |<\/span><\/span>\n|   <\/span>1<\/span> |  <\/span>TABLE<\/span> ACCESS <\/span>BY<\/span> <\/span>INDEX<\/span> ROWID BATCHED| T      |<\/span><\/span>\n|   <\/span>2<\/span> |   <\/span>INDEX<\/span> <\/span>RANGE<\/span> SCAN                  | T_C1_I |<\/span><\/span>\n------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nHint Report (identified <\/span>by<\/span> operation id \/ Query <\/span>Block<\/span> <\/span>Name<\/span> \/ <\/span>Object<\/span> Alias):<\/span><\/span>\nTotal hints <\/span>for<\/span> <\/span>statement<\/span>: <\/span>1<\/span><\/span>\n---------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   <\/span>1<\/span> -  SEL$<\/span>1<\/span> \/ <\/span>"T"<\/span>@<\/span>"SEL$1"<\/span><\/span>\n           -  <\/span>index<\/span>(t)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now the hint index(t)<\/code> is reported as used. All good, right?<\/p>\n\n\n\n
The Problem<\/h2>\n\n\n\n
I like statically-type languages. Mainly because errors are reported at compile time whenever possible. However, to check hints I need to produce an explain plan. This is possible for a single statement only. This is cumbersome especially when you write code in PL\/SQL. As far as I know, there is no option to produce a compile error for invalid hints.<\/p>\n\n\n\n
I recently reviewed a system and found a lot of invalid hints. Here are some real-life hints copied from a production code base:<\/p>\n\n\n\n