[20160730]hint 冲突.txt

[20160730]hint 冲突.txt

--昨天别人优化加提示无效,问我为什么无效?我一般认为这种情况称为hint 冲突.
--通过例子来说明,我测试会使用ordered,我一般不喜欢使用ordered提示,通过例子来说明.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

set autot traceonly
select * from emp,dept where emp.deptno=dept.deptno;

--不加提示,缺省执行计划如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

2.测试1:
--如果你使用use_nl提示,里面仅仅包含1个表,按照文档介绍,作为被驱动表:
select /*+ use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
--------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

select /*+  use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3500 |   198K|     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  3500 |   198K|     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

--可以看出如果use_nl()里面包含1个表的时候,如果你仔细看2个执行计划都没有走nested loop.第1个使用MERGE JOIN,
--而第2个使用HASH JOIN,明显不对.提示无效.

3.测试2:
select /*+ use_nl(dept emp) */ * from emp,dept where emp.deptno=dept.deptno;
select /*+ use_nl(emp dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan
---------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--2者执行计划一样?可以发现这样写确实走nested loop.感觉这样写,内部有规则控制那个做驱动与被驱动表.

4.测试3:
select /*+ ordered use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--这样才是正确的,而且使用了2次.实际上你看文档:use_nl(dept) 里面的表作为被驱动表.
--再看看如下执行计划:

select /*+ ordered use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;
select /*+ use_nl(emp) ordered */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3500 |   198K|     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  3500 |   198K|     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
--可以发现ordered放在前面后面,执行计划都一样.但是执行计划是hash join而不是nested loop.

5.我一般不喜欢使用ordered,而是喜欢leading.
select /*+ leading(dept,emp) use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3500 |   198K|  1360   (1)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |  3500 |   198K|  1360   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
--你可以发现这个cost=1360太高了,这个也许是前面使用/*+ use_nl(dept emp) */,/*+ use_nl(emp dept) */不选择的原因.

select /*+ leading(dept,emp) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--这个提示是错误,或者存在冲突的,use_nl() 里面的表作为被驱动表.可以发现执行计划走的MERGE JOIN.

select /*+ leading(emp dept) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
---------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--这样写正确.

--总之要控制执行计划,最好使用leading,use_nl()里面的表作为被驱动表.
--最后做一个例子:

select /*+ leading(dept emp) use_merge(emp) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

select /*+ leading(dept emp) use_merge(dept) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4260967074
------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |  3500 |   198K|     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN                           |         |  3500 |   198K|     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                   | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL                  | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

--//其中的细节还是自己体会,感觉提示ordered,leading作为提示有优先级.要自己多做练习才行.

时间： 2024-09-20 14:38:18

[20160730]hint 冲突.txt的相关文章

[20150427]tmux与INPUTRC定义冲突.txt

[20150427]tmux与INPUTRC定义冲突.txt --我前一阵子发现在tmux里面一些按键与INPUTRC冲突. 我的/etc/inputrc定义如下: "\e[1~": beginning-of-line    # home "\e[2~": yank-last-arg        # insert "\e[3~": delete-char          # delete "\e[4~": end-of-l

[20140807]hash_value sql_id冲突.txt

[20140807]hash_value sql_id冲突.txt --9i下使用v$sql仅仅有hash_value值,sql_id是到10g下才出现. --理论讲hash_values是sql_id的子集,发生冲突的可能性比使用sql_id,看了链接重复测试: http://externaltable.blogspot.com/2012/06/hash-collisions-sql-signatures-and.html SCOTT@test> @ver BANNER -----------

[20160318]push_pred hint使用疑惑.txt

[20160318]push_pred hint使用疑惑.txt --前几天看的帖子,链接如下: http://www.itpub.net/thread-2054898-1-1.html --当时的第一感觉,就是闭包传递的问题,做1个记录: 1.环境: book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------

[20120527]视图与hint.txt

这段时间一直在看,发现yangtingkun的一篇关于视图与hint的文章.oracle的hint可以强制制定sql的执行计划.如果查询对象是视图,使用hint就很麻烦.自己重复测试了一下,实际上dbms_xplan.display_cursor的advanced或者outline参数,很容易知道如何写这个提示. 例子如下: 1.建立测试例子: $cat /home/oracle11g/sqllaji/dpc.sql select * from table(dbms_xplan.display_

如何解决php中并发读写文件冲突的问题

对于日IP不高或者说并发数不是很大的应用,一般不用考虑这些!用一般的文件操作方法完全没有问题.但如果并发高,在我们对文件进行读写操作时,很有可能多个进程对进一文件进行操作,如果这时不对文件的访问进行相应的独占,就容易造成数据丢失. 例如:一个在线聊天室(这里假定把聊天内容写入文件),在同一时刻,用户A和用户B都要操作数据保存文件,首先是A打开了文件,然后更新里面的数据,但这里B也正好也打开了同一个文件,也准备更新里面的数据.当A把写好的文件保存时,这里其实B已经打开了文件.但当B再把文件保存回去

将SQL Server中的表变成txt文件

方法一:用BCP命令 bcp 实用工具 bcp 实用工具在 Microsoft? SQL Server? 2000 实例和数据文件之间以用户指定的格式复数据. 语法 bcp {[[database_name.][owner].]{table_name | view_name} | "query"} {in | out | queryout | format} data_file [-m max_errors] [-f format_file] [-e err_file] [-F fir

git命令添加 修改 删除 冲突解决办法

  如果对git命令行不熟悉的话,用git图形界面工具,就比较合适了.建议使用tortoisegit这样的工具,命令行从学习到灵活掌握的时间成本比较高的. 1,添加 # git clone git@192.168.10.202:develop/test.git # cd test # touch test.txt //测试文件 # git add test.txt //git添加文件 # git commit -m 'test' //添加到本地版本库 # git push //push到远程 如

如何禁止修改ip地址 防止ip冲突

在局域网中我们常常会遇到ip地址冲突的情况,其实是由于他人随意修改ip地址所造成的,本文为大家介绍一些禁止修改ip地址的方法. 怎样禁止修改ip地址第一招:切断改IP入口 修改ip一般是通过连接属性来修改的.我们可以禁用非管理人员访问连接属性来实现禁止修改ip.开始菜单-运行-输入 "gpedit.msc"--> 管理模板 --> 网络 --> 网络连接,然后把"禁止访问LAN连接组件属性 ""为管理员启用 Windows 2000 网络

"爱说说"技术原理:与TXT交互及MDataTable对Json的功能扩展（二）

关于爱说说在技术选型的文章见:"爱说说"技术原理方案的定选思考过程   本篇将讲述"爱说说"比较重大的技术问题点及解决手段:   爱说说:http://speak.cyqdata.com/   杂说几句: 一开始,原本是打算在秋式开源团队找一JS人才来引导前台页面的呈现的,毕竟本人在JS还停留在原始的编码阶段,于是当时聊了一下. 再后来,接下来几天,由于秋式开源团队内部管理上有点小调整,还有新开任务小组的事,连续忙了几天,"爱说说"就也停了几天.