导航：首页 > 数据库 >

Oracle Hint 学习之一

发表于：2024-10-08 作者：千家信息网编辑

千家信息网最后更新 2024年10月08日，APPEDND hint :用于控制insert 语句是否能以直接路径插入的方式插入数据。CACHE hint：用于控制目标sql在执行时是否将全表扫描目标表的数据块放到buffer cache的LR

千家信息网最后更新 2024年10月08日Oracle Hint 学习之一

APPEDND hint :用于控制insert 语句是否能以直接路径插入的方式插入数据。
CACHE hint：用于控制目标sql在执行时是否将全表扫描目标表的数据块放到buffer cache的LRU链表的热端。
MONITER hint：用于控制被执行的目标sql是否被sql monitor监控
Gather_plan_statistics hint:用于在目标sql执行时收集一些额外的统计信息：

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;     EMPNO ENAME      DNAME---------- ---------- --------------      7782 CLARK      ACCOUNTING，，，，14 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID4m81jub7yju91, child number 0-------------------------------------select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname fromemp t1,dept t2 where t1.deptno=t2.deptnoPlan hash value: 844388907-----------------------------------------------------------------------------------------------------------------------------| Id  | Operation     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |-----------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |       |      1 | |     14 |00:00:00.01 |      10 | | |    ||   1 |  MERGE JOIN     |       |      1 |     14 |     14 |00:00:00.01 |      10 | | |    ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |4 |00:00:00.01 |       4 | | |    ||   3 |    INDEX FULL SCAN     | PK_DEPT |      1 |      4 |4 |00:00:00.01 |       2 | | |    ||*  4 |   SORT JOIN     |       |      4 |     14 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)||   5 |    TABLE ACCESS FULL     | EMP     |      1 |     14 |     14 |00:00:00.01 |       6 | | |    |-----------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T1"."DEPTNO"="T2"."DEPTNO")       filter("T1"."DEPTNO"="T2"."DEPTNO")24 rows selected.

不加hint，看不到上面starts类似的执行计划：

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;     EMPNO ENAME      DNAME---------- ---------- --------------      7782 CLARK      ACCOUNTING      ，，，，14 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID4m81jub7yju91, child number 0-------------------------------------select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname fromemp t1,dept t2 where t1.deptno=t2.deptnoPlan hash value: 844388907-----------------------------------------------------------------------------------------------------------------------------| Id  | Operation     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |-----------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |       |      1 | |     14 |00:00:00.01 |      10 | | |    ||   1 |  MERGE JOIN     |       |      1 |     14 |     14 |00:00:00.01 |      10 | | |    ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |4 |00:00:00.01 |       4 | | |    ||   3 |    INDEX FULL SCAN     | PK_DEPT |      1 |      4 |4 |00:00:00.01 |       2 | | |    ||*  4 |   SORT JOIN     |       |      4 |     14 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)||   5 |    TABLE ACCESS FULL     | EMP     |      1 |     14 |     14 |00:00:00.01 |       6 | | |    |-----------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T1"."DEPTNO"="T2"."DEPTNO")       filter("T1"."DEPTNO"="T2"."DEPTNO")24 rows selected.

SQL> select /*+ full(scott.emp) */* from scott.emp where empno=7369; --错误的写法SQL> select /*+ full(emp) */* from scott.emp where empno=7369; --正确的写法

SQL> select /*+ full(t1) */* from scott.emp t1 where empno=7369; --HINT中指定别名，否则无效

针对query block，hint生效范围仅限于它本身所在的。

SQL> select /*+ full(t1) */t1.ename,t1.deptno from emp t1 where t1.deptno in (select /*+ full(t2) */t2.deptno from dept t2 where t2.loc='CHICAGO');

SQL> select /*+ full(t1) full(t2) */t1.ename,t1.deptno from t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO'); --该HINT对T2表不生效

HINT中出现query block其格式必须是"@query block名称"。

方法一：

SQL> select /*+ full(@sel$1 t1) full(@sel$2 t2) */t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO');

方法二：

SQL> select /*+ full(t1@sel$1) full(t2@sel$2) */t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO');

方法三：（自定义qb_name）

SQL> select /*+ full(t1@sel$1) full(@llc t2) */t1.ename,t1.deptno from emp t1 where t1.deptno in (select /*+ qb_name(llc) */t2.deptno from dept t2 where t2.loc='CHICAGO');

SQL> select /*+ full(t1@sel$1) full(t2@llc) */t1.ename,t1.deptno from emp t1 where t1.deptno in (select /*+ qb_name(llc) */t2.deptno from dept t2 where t2.loc='CHICAGO');

SQL> set autot off;SQL> select t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO');ENAME       DEPTNO---------- ----------ALLEN   30WARD   30MARTIN   30BLAKE   30TURNER   30JAMES   30ALLEN   30WARD   3024 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID3v4x69w2mvqgs, child number 0-------------------------------------select t1.ename,t1.deptno from t1 where t1.deptno in (select t2.deptnofrom dept t2 where t2.loc='CHICAGO')Plan hash value: 2392421419---------------------------------------------------------------------------------------------| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |    |    |    |  5 (100)|    ||*  1 |  HASH JOIN     |    | 19 |380 |  5   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1 | 11 |  2   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN     | IDX_DEPT_LOC |  1 |    |  1   (0)| 00:00:01 ||   4 |   TABLE ACCESS FULL     | T1    | 56 |504 |  3   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$5DA710D3   2 - SEL$5DA710D3 / T2@SEL$2   3 - SEL$5DA710D3 / T2@SEL$2   4 - SEL$5DA710D3 / T1@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')      DB_VERSION('11.2.0.4')      ALL_ROWS      OUTLINE_LEAF(@"SEL$5DA710D3")      UNNEST(@"SEL$2")      OUTLINE(@"SEL$1")      OUTLINE(@"SEL$2")      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."LOC"))      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")      USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$1")      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."DEPTNO"="T2"."DEPTNO")   3 - access("T2"."LOC"='CHICAGO')Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=1) "T1"."DEPTNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10]   2 - "T2"."DEPTNO"[NUMBER,22]   3 - "T2".ROWID[ROWID,10]   4 - "T1"."ENAME"[VARCHAR2,10], "T1"."DEPTNO"[NUMBER,22]59 rows selected.

上述执行计划中：T2@SEL$2 和T1@SEL$1 query block ，而SEL$5DA710D3是一次查询转换（包含子查询展开，视图合并，连接谓词推入）而形成的新的query block。

Outline data，是用来固定执行计划的内部hint组合，非常全面的组合，比一般hint更加可靠：

在emp deptno建立索引，让sql走NL:

SQL> select t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO');6 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 902326130----------------------------------------------------------------------------------------------| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |      |   5 | 100 |   3   (0)| 00:00:01 ||   1 |  NESTED LOOPS      |      |   5 | 100 |   3   (0)| 00:00:01 ||   2 |   NESTED LOOPS      |      |   5 | 100 |   3   (0)| 00:00:01 ||   3 |    TABLE ACCESS BY INDEX ROWID| DEPT     |   1 |  11 |   2   (0)| 00:00:01 ||*  4 |     INDEX RANGE SCAN      | IDX_DEPT_LOC |   1 |     |   1   (0)| 00:00:01 ||*  5 |    INDEX RANGE SCAN      | IDX_EMP_DEPT |   5 |     |   0   (0)| 00:00:01 ||   6 |   TABLE ACCESS BY INDEX ROWID | EMP     |   5 |  45 |   1   (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T2"."LOC"='CHICAGO')   5 - access("T1"."DEPTNO"="T2"."DEPTNO")Statistics----------------------------------------------------------  0  recursive calls  0  db block gets  7  consistent gets  0  physical reads  0  redo size714  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  6  rows processed

如果把hash 连接outline data加入hint，讲不会使用新建的索引，走出hash连接：

select /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')      DB_VERSION('11.2.0.4')      ALL_ROWS      OUTLINE_LEAF(@"SEL$5DA710D3")      UNNEST(@"SEL$2")      OUTLINE(@"SEL$1")      OUTLINE(@"SEL$2")      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("DEPT"."LOC"))      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")      USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$1")      END_OUTLINE_DATA 16    */t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='CHICAGO');6 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2711458306---------------------------------------------------------------------------------------------| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |    |  5 |100 |  5   (0)| 00:00:01 ||*  1 |  HASH JOIN     |    |  5 |100 |  5   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1 | 11 |  2   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN     | IDX_DEPT_LOC |  1 |    |  1   (0)| 00:00:01 ||   4 |   TABLE ACCESS FULL     | EMP    | 14 |126 |  3   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."DEPTNO"="T2"."DEPTNO")   3 - access("T2"."LOC"='CHICAGO')Statistics----------------------------------------------------------  1  recursive calls  0  db block gets  9  consistent gets  0  physical reads  0  redo size714  bytes sent via SQL*Net to client523  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  6  rows processed

所有hint由_optimizer_ignore_hints决定（system或者session级别），默认false，不忽略hint，设置成ture将会忽略掉所有的hint。

SQL> alter system set "_optimizer_ignore_hints"=true;System altered.SQL> select /*+ full(emp) */ * from emp where empno=7369;Execution Plan----------------------------------------------------------Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |     |   1 |  38 |   1   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   1   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN    | PK_EMP |   1 |     |   0   (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=7369)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets  2  consistent gets  0  physical reads  0  redo size889  bytes sent via SQL*Net to client512  bytes received via SQL*Net from client  1  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processed

很赞哦！