OracleHint学习之一
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 from emp t1,dept t2 where t1.deptno=t2.deptno Plan 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 from emp t1,dept t2 where t1.deptno=t2.deptno Plan 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 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES 30 ALLEN 30 WARD 30 24 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.deptno from 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$1 Outline 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 size 714 bytes sent via SQL*Net to client 523 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 size 714 bytes sent via SQL*Net to client 523 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 size 889 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
网站栏目:OracleHint学习之一
URL网址:http://scyanting.com/article/ishpec.html