Home » SQL & PL/SQL » SQL & PL/SQL » Hints in explain plans (19)
| Hints in explain plans [message #690501] |
Mon, 31 August 2026 10:53  |
 |
Darth Waiter
Messages: 100
Registered: October 2020
|
Senior Member |
|
|
Coming from MS SQL world where the plan XML may contain a hints section if it's sub-optimal, I am looking for information on whether Oracle explain plans ever contain such. If yes, where can I read about it?
Much appreciated!
|
|
|
|
| Re: Hints in explain plans [message #690502 is a reply to message #690501] |
Mon, 31 August 2026 11:44  |
John Watson
Messages: 9005
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Do you mean this, which show the full set of hints to produce the plan -orclz>
orclz> explain plan for select * from emp natural join dept;
Explained.
orclz> select * from table (dbms_xplan.display(format=>'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 826 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 546 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_MERGE(@"SEL$58A6D7F6" "EMP"@"SEL$1")
LEADING(@"SEL$58A6D7F6" "DEPT"@"SEL$1" "EMP"@"SEL$1")
FULL(@"SEL$58A6D7F6" "EMP"@"SEL$1")
INDEX(@"SEL$58A6D7F6" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE_LEAF(@"SEL$58A6D7F6")
ALL_ROWS
OPT_PARAM('optimizer_dynamic_sampling' 0)
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
39 rows selected.
orclz> |
|
|
|
|
|
| Re: Hints in explain plans [message #690504 is a reply to message #690503] |
Mon, 31 August 2026 12:18 |
 |
Michel Cadot
Messages: 68785
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Maybe with "DBMS_SQLTUNE" containing "Recommendation" sections and tries different plans (here using all default options and 11gR2):
SQL> var id varchar2(100)
SQL> begin
2 :id := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from emp natural join dept');
3 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:id);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK(:id) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:ID)
--------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_99809
Tuning Task Owner : MICHEL
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 08/31/2026 19:13:31
Completed at : 08/31/2026 19:13:41
-------------------------------------------------------------------------------
Schema Name: MICHEL
SQL ID : a5tgz30vvv9s8
SQL Text : select * from emp natural join dept
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "MICHEL"."DEPT" and its indices were not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'MICHEL', tabname =>
'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Statistics Finding
---------------------
Table "MICHEL"."EMP" and its indices were not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'MICHEL', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 35.71%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_99809',
task_owner => 'MICHEL', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .00227 .000142 93.74 %
CPU Time (s): 0 0
User I/O Time (s): 0 0
Buffer Gets: 14 9 35.71 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 14 14
Fetches: 14 14
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 686 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 686 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 72 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 434 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
2- Using SQL Profile
--------------------
Plan hash value: 2125045483
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 686 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 686 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 72 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 434 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 434 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
-------------------------------------------------------------------------------
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK(:id)
PL/SQL procedure successfully completed.
[Updated on: Mon, 31 August 2026 12:21] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Hints in explain plans [message #690529 is a reply to message #690527] |
Fri, 11 September 2026 08:25 |
Solomon Yakobson
Messages: 3317
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
It is a bit strange how DBMS_XPLAN shows hints. By default, it shows hints only when hint isn't used or has syntax errors:
SQL> explain plan for select /*+ use_hash(emp,dept) */ * from emp natural join dept;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$58A6D7F6 / DEPT@SEL$1
U - use_hash(emp,dept)
22 rows selected.
SQL> explain plan for select /*+ use_hj(emp,dept) */ * from emp natural join dept;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$2
E - use_hj
25 rows selected.
SQL>
SQL> explain plan for select /*+ no_index(dept pk_dept) */ * from emp natural join dept;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
15 rows selected.
SQL>
SQL> explain plan for select /*+ no_index(dept pk_dept) */ * from emp natural join dept;
Explained.
SQL> select * from dbms_xplan.display(format=>'advanced');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$58A6D7F6 / DEPT@SEL$1
3 - SEL$58A6D7F6 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$58A6D7F6" "EMP"@"SEL$1")
LEADING(@"SEL$58A6D7F6" "DEPT"@"SEL$1" "EMP"@"SEL$1")
FULL(@"SEL$58A6D7F6" "EMP"@"SEL$1")
FULL(@"SEL$58A6D7F6" "DEPT"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE_LEAF(@"SEL$58A6D7F6")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "DEPT"."DEPTNO"[NUMBER,22],
"DEPT"."LOC"[VARCHAR2,13], "DEPT"."DNAME"[VARCHAR2,14],
"EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22]
2 - (rowset=256) "DEPT"."DEPTNO"[NUMBER,22],
"DEPT"."DNAME"[VARCHAR2,14], "DEPT"."LOC"[VARCHAR2,13]
3 - (rowset=256) "EMP"."EMPNO"[NUMBER,22],
"EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9],
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22],
"EMP"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
2 - SEL$58A6D7F6 / DEPT@SEL$1
- no_index(dept pk_dept)
Query Block Registry:
---------------------
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DEPT]]></t><s><![CDAT
A[SEL$1]]></s></h><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]></s></h><
/f></q>
<q o="18" f="y" h="y"><n><![CDATA[SEL$58A6D7F6]]></n><p><![CDATA[SEL$2]]
></p><i><o><t>VW</t><v><![CDATA[SEL$1]]></v></o></i><f><h><t><![CDATA[DE
PT]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[EMP]]></t><s><![CDAT
A[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]
></t><s><![CDATA[SEL$2]]></s></h></f></q>
80 rows selected.
SQL>
[Updated on: Fri, 11 September 2026 08:28] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Sep 15 19:26:49 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
