Oracle 常见hint
Hints 应该慎用,收集相关表的统计信息,根据执行计划,来改变查询方式 只能在SELECT,?UPDATE,?INSERT,?MERGE,or?DELETE?关键字后面,只有insert可以用2个hint(append 跟parallel),其他只能only one—如果sql中使用了hint,则就意味着启用了CBO ? 1 /*+ gather_plan_statistics*/用于在目标sql执行时收集一些额外的统计信息 select /*+ gather_plan_statistics*/ t1.empno,t2.deptno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno; --查看该语句的详细执行计划 select * from table(dbms_xplan.display_cursor(null,null,‘allstats last‘)); SQL_ID bzymbk118976v,child number 0 ------------------------------------- select /*+ gather_plan_statistics*/ t1.empno,dept t2 where t1.deptno=t2.deptno Plan hash value: 351108634 --------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 23 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 14 |00:00:00.01 | 16 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 1 | 14 |00:00:00.01 | 2 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."DEPTNO"="T2"."DEPTNO") Starts—每一个具体执行步骤被重复执行的次数 E-Rows—cbo对每一个具体执行步骤的返回的cardinality的预估值 A-Rows—每一个步骤返回的实际值 A-Time—每一个步骤实际执行的时间 Buffers—每一个步骤的逻辑读 Reads—每一个步骤的物理读 SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ---------- ---------------- optimizer_mode string ALL_ROWS select * from v$parameter where name=‘optimizer_mode‘ SQL> alter session set optimizer_mode=‘RULE‘; Oracle的hint可以直接影响优化器解析目标sql产生执行计划 Hint:可以影响目标sql是否能够被查询改写,merge,unnest,use_concat等 :优化器对执行路径的选择,full,index等 :对表连接方法的选择,use_hash,use_nl等 :影响优化器对于执行计划中执行步骤返回结果集(cardinality),dynamic_sampling,cardinal SQL> select t1.empno,dept 2 t2 where t1.deptno=t2.deptno and t1.empno=7369; Execution Plan ---------------------------------------------------------- Plan hash value: 2385808155 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."DEPTNO" IS NOT NULL) 3 - access("T1"."EMPNO"=7369) 5 - access("T1"."DEPTNO"="T2"."DEPTNO") SQL> select/*+ full(t1)*/ t1.empno,dept 2 t2 where t1.deptno=t2.deptno and t1.empno=7369; Execution Plan----t1表 全表扫描 ---------------------------------------------------------- Plan hash value: 351108634 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 27 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."EMPNO"=7369 AND "T1"."DEPTNO" IS NOT NULL) 4 - access("T1"."DEPTNO"="T2"."DEPTNO") 2 Hint的用法/*+ xx xx*/第一个*跟+直接没有空格,多个hint直接用空格隔开 SQL> select /*+ full(emp)*/ * from emp where empno=7369; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 | 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 | 37 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | Hint中,sql文本中有别名,在hint就应该使用表的别名, 不能加schema名 3 hint被忽略的常见情形 1使用hint有语法或拼写错误 空格,加号等 select?/*+ index(emp emp_idx)*/?* from?emp?where?ename=‘hongquan1‘; 2使用的hint无效 --无效的hint --对于非分区索引而言,索引范围扫描或者索引全扫描都不可以并行执行,hint就没意义 User_hash(t),哈希连接,里面目标表是哈希的被驱动表,被驱动表应该是数据量多的那个表 3使用的hint自相矛盾 4使用的hint受到了查询转换的干扰 5使用的hint受到了保留关键字的干扰commint ,is 3 常见的hint3.1与优化器相关的hint?/*+ALL_ROWS*/?10g默认的模式 ?/*+FIRST_ROWS*/???/*+FIRST_ROWS(1)*/ 最小化. "ALL_ROWS Hint"?for additional information on the?FIRST_ROWS?hint and statistics SQL> select /*+ first_rows(2) */ empno,ename,sal from emp 2 where deptno=10; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 36 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 2 | 36 | 3 (0)| 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 快速响应返回头2条记录 :集合运算(union all,minus)等 :group by :for update :聚合函数(sum)等 :distinct :order by(对应的排序列上没有索引) 这里优化器会忽略first_rows(n),Oracle必须访问所有的行记录后才能返回满足条件的头n行记录 /*+ FIRST_ROWS(10) */ ?/*+ CHOOSE*/ ?/*+ RULE*/ ?/*+ FULL(TABLE)*/ 3.2与index相关的hint1 Index?–4种方式 Index(table pk_emp) Index(table pk_emp idx_emp) Index(table (empno) (mgr)) SQL> select /*+ index(c1 (empno))*/ c1.empno,c1.ename from emp c1; 15 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4170700152 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 165 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 15 | 165 | 3 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 15 | | 1 (0)| 00:00:01 | Index(table) 2 no_index No_index(emp) 3 index_desc 针对单个目标表的hint 优化器对目标表上的目标索引执行索引降序扫描操作,if 目标索引是asc,index_desc hint会使Oracle以降序的方式扫描该表,if目标索引是desc,会使asc来扫描该表。 SQL> select /*+ index_desc(emp pk_emp)*/ empno,sal,job from emp 2 where empno=7369 and mgr=7902 and deptno=20; Execution Plan ---------------------------------------------------------- Plan hash value: 2707196548 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 30 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN DESCENDING| PK_EMP | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------- Index_des(emp pk_emp) 4 index_combine 针对目标单个目标表上的多个目标index执行位图布尔运算 5 index_ffs 针对单个目标表,执行索引快速全扫描(select所查询的所有列都在目标index中) 6 index_join 目标表上的多个目标index执行index join,select所有列都在目标表的index中,可以避免回表 3.3与表连接顺序相关的hint1 ordered 在where条件中出现的顺序从左到右依次连接 2 leading 将leading中出现的表作为整个过程中的驱动表 select?/*+ leading (c1) use_hash(c2) */?c1.empno,c1.ename,c2.loc from?emp?c1,dept?c2 where?c1.deptno=c2.deptno; 3.4与表连接方法有关的hint1 use_merge—no_use_merge 所指定的表为排序合并连接中的被驱动表,通常与leading一起 2 use_nl—no_use_nl 嵌套连接中的被驱动表,通常与leading一起 select?/*+ leading (c1) use_nl(c2) */?c1.empno,dept?c2 where?c1.deptno=c2.deptno; 3 use_hash—no_use_hash Hash连接中的被驱动表,通常与leading一起 select?/*+ leading (c1) use_hash(c2) */?c1.empno,dept?c2 where?c1.deptno=c2.deptno; 4 merge_aj,nl_aj,hash_aj—not in 排序合并反连接,嵌套合并反连接,哈希反连接 5 merge_sj,nl_sj,hash_sj—exists 排序合并半连接 3.5与查询转换相关的hint1 Use_concat 是让sql使用in_list优化或者or扩展 select?/*+ USE_CONCAT */?c1.empno,c1.ename from?emp?c1 where?c1.empno in(7521,7566,7698)---CONCATEATION 2 NO_EXPAND USE_CONCAT的反义hint 3 merge—no_merge 对目标视图执行视图合并 4 unnest—un_unnest 对sql 的子查询进行展开 5 expand_table—no_expand_table 让优化器不考虑成本的情况下进行sql的目标表进行扩展 3.6与并行相关的hint1 parallel Parallel Parallel(auto) Parallel(manual) Parallel(table n/defult) Show parameter parallel_min_services; 2 no_parallel 3 parallel_Index 对分区index进行并行扫描 No-parallel_Index 3.7其他常见的hint1 driving_site 使用于dblink 2 append 让执行insert的语句绕开cache buffer,直接路径插入 3 append_values 11gr2开始的让带values字句的insert直接路径插入 4 push_pred—no_push_pred 对目标视图进行连接谓词推入—sql中处于该视图定义sql语句以外的谓词连接条件推入到该视图定义中去 5 push_subq—no_push_subq Sql中不能做子查询展开的子查询 6 opt_param 修改优化器的参数 7 cardinality 设置对目标表执行扫描操作后返回结果集的cardinality的值 select /*+ cardinality(emp 200)*/ empno,ename from emp | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 200 | 1600 | 3 (0)| 00:00:01 | 1 | TABLE ACCESS FULL| EMP | 200 | 1600 | 3 (0)| 00:00:01 8 swap_join_inputs 交换哈希连接的驱动表与被驱动表的顺序 ? ? ? ? 1. /*+ROWID(TABLE)*/ 2. /*+CLUSTER(TABLE)*/ 3. /*+INDEX(TABLE INDEX_NAME)*/ 4. /*+INDEX_ASC(TABLE INDEX_NAME)*/ 5. /*+INDEX_COMBINE*/ 6. /*+INDEX_JOIN(TABLE INDEX_NAME)*/ 7. /*+?INDEX_DESC(TABLE INDEX_NAME)*/ 8.?/*+INDEX_FFS(TABLE INDEX_NAME)*/ 9. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/ 15. /*+USE_CONCAT*/ or条件中有一个为子查询的情况,无法index scan 使用use_concat也无效 修改成union可应用index 10. /*+NO_EXPAND*/ 11. /*+NOWRITE*/ 12. /*+REWRITE*/ 13. /*+MERGE(TABLE)*/ 14. /*+NO_MERGE(TABLE)*/ 15. /*+ORDERED*/ 16. /*+USE_NL(TABLE)*/?别名 explain plan for SELECT /*+USE_NL(s,d)*/ s.USER_ID,D.ROWID AS RW FROM SUBSCRIBER_DETAIL D,SUBSCRIBER S WHERE D.USER_ID=S.USER_ID AND S.PROVIDER_ID=‘GLOBALROAM‘ SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 17. /*+USE_MERGE(TABLE)*/ SELECT /*+USE_MERGE(e,d)*/ e.ename,e.job,d.deptno,d.dname FROM emp e,dept1 d WHERE e.deptno=d.deptno order by 1; 18. /*+USE_HASH(TABLE)*/?别名 ?using a hash join explain plan for select /*+ leading(t) use_hash(s) */ t.number_id from bulk_numbers s,bulk_tab t where s.number_id=t.number_id t为外部表,s为内表 SELECT /*+leading(d) USE_HASH(e,dept1 d WHERE e.deptno=d.deptno order by 1; /*+ leading(CDR) use_hash(SUBSCRIBER_DETAIL)*/ /*+ leading(SUBSCRIBER_DETAIL) use_NL(CDR)*/ 将指定的表与其他行源通过哈希连接方式连接起来. 19. /*+DRIVING_SITE(TABLE)*/ 20. /*+LEADING(TABLE)*/ /*+ LEADING(e j) */ * 21. /*+CACHE(TABLE)*/ SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name ??FROM employees hr_emp; 22. /*+NOCACHE(TABLE)*/ 23. /*+APPEND*/ ALTER SESSION { ENABLE | FORCE } PARALLEL DML Note: If the database or tablespace is in?FORCE?LOGGING?mode,then direct path?INSERT?always logs,regardless of the logging setting. 24. /*+NOAPPEND*/ /*+ HASH_AJ */ ?/*+ NL_AJ */ /*+ DRIVING_SITE(T1) USE_HASH(T1 T2) USE_HASH(T3)? 25. NO_INDEX: 指定不使用哪些索引 ? ??/*+ NO_INDEX ( table [index [index]...] ) */ select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300; select /*+ NO_INDEX ( emp1 EMP1_PK )*/* from emp1 where empno=‘7369‘ 26. parallel alter table t01 parallel 4; ? /*+ PARALLEL ??( [ @ qb_name ] tablespec [ integer | DEFAULT?] ) */?加表名 select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal>300; SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp,5) */ last_name FROM employees hr_emp; SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp,DEFAULT) */ last_name FROM employees hr_emp PARALLEL_INDEX?hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes. SELECT /*+ PARALLEL_INDEX(table1,index1,3) */ ??另:每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/*+ */,但提示内容可以有多个,可以用逗号分开,空格也可以。 ??如:/*+ ordered index() use_nl() */ --------- alter session enable dml parallel; alter table t01 parallel 4; insert /*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |