[Oracle] DB Optimizer Part IV – What the heck … Troubleshooting why hints are not considered
Today i received an e-mail from one of my clients about an issue with Oracle hints. I get asked similar questions from time to time, so i think it would be a good next topic in my “DB optimizer” series. This blog is focused on how to verify that an Oracle hint is considered by an execution plan compilation. It will provide a brief introduction into Oracle hints as well, but there is already a lot of information out there about hints and i don’t want to spend too much time with it. This blog is not about providing recommendations for using hints or any particular hint syntax.
The Oracle Hint
Let’s check the official Oracle documentation first.
A hint is an instruction to the optimizer. When writing SQL code, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost.
Optimizer hints are grouped into the following categories:
- Hints for Optimization Approaches and Goals
- Hints for Enabling Optimizer Features
- Hints for Access Paths
- Hints for Join Orders
- Hints for Join Operations
- Hints for Online Application Upgrade
- Hints for Parallel Execution
- Hints for Query Transformations
- Additional Hints
The important word in the previous text is “decision”, but unfortunately it seems like it is interpreted wrong or skipped at all. However what does it mean?
Let’s assume that you want to use a full table scan on a table and you provide the FULL hint for that. This does not mean “I want to use a full table scan, so search and generate an execution plan with it”. It basically means “If you have to decide between an index and a full table scan – use the full table scan”. So you are able to influence the optimizer with Oracle hints, if it has a valid choice and not in general.
You can query the view V$SQL_HINT, if you want to get a list of the available hints (with Oracle 11g R1 or newer).
The following demo is run on an Oracle 220.127.116.11 database with OEL 6.2. I will use a pretty simple example for demonstrating and troubleshooting Oracle hints.
Basically i will create a table with 1 column (null able) and an index on it, so that the optimizer has a valid choice (except IS NULL predicate).
SQL> create table HTAB (a number); SQL> create index HTABI on HTAB(a); SQL> begin for i in 1 .. 300 loop insert into HTAB values(i); end loop; end; / commit;
So let’s run a simple SQL without any hint and check the execution plan.
… so far so good. The index HTABI is used, but we want to “force” a full table scan. So let’s use the FULL hint for that.
… perfect. We run a full table scan now. But how can we be sure, that this execution plan was compiled due to our provided hint and not due to object changes like dropped indexes (just think about BW environments), new collected statistics or anything else.
The answers are:
- CBO trace (Oracle event 10053)
- SQL plan dumping (Oracle event 10132)
- PL/SQL procedure DBMS_SQLDIAG.DUMP_TRACE
shell> oerr ora 10053 10053, 00000, "CBO Enable optimizer trace" // *Cause: // *Action: shell> oerr ora 10132 10132, 00000, "dump plan after compilation" // *Cause: // *Action: set this event only under the supervision of Oracle development
Now let’s flush the shared pool and use one of these possibilities. (the flush of the shared pool is needed for hard parsing of the same SQL)
SQL> alter system flush shared_pool; SQL> alter session set events '10053 trace name context forever, level 1'; SQL> select /*+ FULL(HTAB) */ * from HTAB where a = 100; SQL> alter session set events '10053 trace name context off'; SQL> oradebug setmypid SQL> oradebug tracefile_name /oracle/T11/oratrace/diag/rdbms/t11/T11/trace/T11_ora_1565.trc
Search for “atom_hint” in this trace file and you will find the following section.
You can verify the following things in that trace file:
- If a hint was detected (otherwise it is treated like an usual comment section and you will find no “atom_hint” for it all)
- If its syntax was ok
- If it was used
In our previous case we can see that the hint was detected, that the syntax was alright and that it was used.
Now let’s add a conflicting INDEX hint to the SQL and verify it once again.
SQL> alter session set events '10053 trace name context forever, level 1'; SQL> select /*+ FULL(HTAB) INDEX (HTAB HTABI) */ * from HTAB where a = 100; SQL> alter session set events '10053 trace name context off'; SQL> oradebug setmypid SQL> oradebug tracefile_name /oracle/T11/oratrace/diag/rdbms/t11/T11/trace/T11_ora_1780.trc
… so we maybe think our hint has worked (and the FULL hint was disabled / overridden, because it was provided first) as we run an index range scan, but let’s check the trace file for verification.
So in this case the execution plan was compiled without any hint influence (like the first example without any hint at all), because of the optimizer was not able to interpret the conflicting instructions as reasonable and so it does not use any of these hints.
There are so many possibilities why hints are ignored or not considered, but now you are able to find the root cause with that troubleshooting procedure and trace files.
If you have any further questions – please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database issues.