用10053trace检查hint的使用情况

设置10053 trace，指定trace文件的标识名，便于从udump中快速识别到

SQL> alter session set max_dump_file_size=unlimited;

SQL> alter session set tracefile_identifier='MYDUMP_FILE';

SQL> alter session set events '10053 trace name context forever,level 1';

SQL> show parameter user_dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      G:\ORACLE\ADMIN\UDUMP

产生的trace文件为：
Dump file g:\oracle\admin\udump\ora10g_ora_14084_mydump_file.trc

以下执行3条简单的SQL，1条为普通SQL，1条为带1个hint的SQL，1条为带2个存在冲突的hint
观察在trace文件中关于hint的记录

1.普通SQL，CBO选择了HASH JOIN。
SQL> select a.name,b.loc from tab11 a,tab22 b where a.id=b.id;

============
Plan Table
============
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   440M|    18G|       |  4543  (58)| 00:01:18 |
|*  1 |  HASH JOIN         |       |   440M|    18G|    10M|  4543  (58)| 00:01:18 |
|   2 |   TABLE ACCESS FULL| TAB22 |   427K|  5431K|       |   449   (2)| 00:00:08 |
|   3 |   TABLE ACCESS FULL| TAB11 |   433K|    12M|       |   439   (2)| 00:00:08 |
------------------------------------------------------------------------------------

2.带一个hint的SQL，强制执行计划走nested loops方式。
SQL> select /*+ use_nl(a b) */a.name,b.mark,a.loc from tab1 a,tab2 b where a.id=b.id;

============
Plan Table
============
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   440M|    18G|   185M  (1)|882:36:00 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB11   |  1030 | 31930 |   433   (0)| 00:00:08 |
|   2 |   NESTED LOOPS              |         |   440M|    18G|   185M  (1)|882:36:00 |
|   3 |    TABLE ACCESS FULL        | TAB22   |   427K|  5431K|   449   (2)| 00:00:08 |
|*  4 |    INDEX RANGE SCAN         | TAB11ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

从trace文件中可以看到使用了该hint。used=1，表示使用了该hint。
Dumping Hints
=============
  atom_hint=(@=0BE4FC84 err=0 resol=1 used=1 token=924 org=1 lvl=3 txt=USE_NL ("B") )
  atom_hint=(@=0BE466EC err=0 resol=1 used=1 token=924 org=1 lvl=3 txt=USE_NL ("A") )

 

3.带2个相互冲突hint的SQL，可以看到该SQL的执行计划和不带hint的时候一样。
SQL> select /*+ use_nl(a b) */a.name,b.loc from tab11 a,tab22 b where a.id=b.id;

============
Plan Table
============
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   440M|    18G|       |  4543  (58)| 00:01:18 |
|*  1 |  HASH JOIN         |       |   440M|    18G|    10M|  4543  (58)| 00:01:18 |
|   2 |   TABLE ACCESS FULL| TAB22 |   427K|  5431K|       |   449   (2)| 00:00:08 |
|   3 |   TABLE ACCESS FULL| TAB11 |   433K|    12M|       |   439   (2)| 00:00:08 |
------------------------------------------------------------------------------------

从trace文件中可以看到，当使用某个hint的时候出现了错误，err的错误代码为4，表明该hint和其他hint存在冲突。
used=0表明没有使用该hint，所以执行计划和之前没有加hint的情况一致。

Dumping Hints
=============
  atom_hint=(@=0BE46400 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE ("B") )
  atom_hint=(@=0BE4FC84 err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("B") )
  atom_hint=(@=0BE46554 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE ("A") )
  atom_hint=(@=0BE466EC err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("A") )
********** WARNING: SOME HINTS HAVE ERRORS *********