ITPUB论坛－专业的IT技术社区

登录 注册
ITPUB论坛－专业的IT技术社区»论坛 › ≡ 大数据与数据库 ≡ › Oracle专题深入讨论 › 对USE_NL这个hint的一些疑问
1234
返回列表 发新帖
楼主: iso90000
收藏 | 打印 上一主题 下一主题

对USE_NL这个hint的一些疑问

[复制链接]
battleman
论坛徽章:
33
ITPUB元老 日期:2009-03-11 15:35:03咸鸭蛋 日期:2011-11-06 22:20:25紫蛋头 日期:2011-12-27 22:15:052012新春纪念徽章 日期:2012-01-04 11:49:542014年新春福章 日期:2014-02-18 16:41:11马上有车 日期:2014-02-18 16:41:11红宝石 日期:2014-06-03 13:13:19
31#
发表于 2009-1-7 12:10 | 只看该作者
原帖由 Yong Huang 于 2009-1-7 02:25 发表


Doesn't sound right. Can you show us a test case? And remember to tell us version.

Yong Huang



SQL>
SQL>
SQL> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi               
PL/SQL Release 10.2.0.4.0 - Production                                          
CORE        10.2.0.4.0        Production                                                      
TNS for Linux: Version 10.2.0.4.0 - Production                                 
NLSRTL Version 10.2.0.4.0 - Production                                          

SQL> create table t1 as select * from dba_objects where rownum<10001;

表已创建。

SQL> create table t2 as select * from dba_objects where rownum<1001;

表已创建。

SQL> create table t3 as select * from dba_objects where rownum<101;

表已创建。


SQL> explain plan for
  2  select * from t1,t2,t3 where t1.object_id=t2.object_id and t1.owner=t3.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1487401159                                                     
                                                                                
----------------------------------------------------------------------------   
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |      |   100K|    50M|    29   (7)| 00:00:01 |   
|*  1 |  HASH JOIN          |      |   100K|    50M|    29   (7)| 00:00:01 |   
|   2 |   TABLE ACCESS FULL | T3   |   100 | 17700 |     4   (0)| 00:00:01 |   
|*  3 |   HASH JOIN         |      |  1000 |   345K|    24   (5)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| T2   |  1000 |   172K|     5   (0)| 00:00:01 |   
|   5 |    TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |   

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
----------------------------------------------------------------------------   
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T3"."OWNER")                                       
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")                                
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择22行。

SQL> explain plan for
  2  select/*+no_swap_join_inputs(t3)*/ * from t1,t2,t3 where t1.object_id=t2.object_id and t1.owner=t3.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1184213596                                                     
                                                                                
----------------------------------------------------------------------------   
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |      |   100K|    50M|    29   (7)| 00:00:01 |   
|*  1 |  HASH JOIN          |      |   100K|    50M|    29   (7)| 00:00:01 |   
|*  2 |   HASH JOIN         |      |  1000 |   345K|    24   (5)| 00:00:01 |   
|   3 |    TABLE ACCESS FULL| T2   |  1000 |   172K|     5   (0)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |   
|   5 |   TABLE ACCESS FULL | T3   |   100 | 17700 |     4   (0)| 00:00:01 |   

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
----------------------------------------------------------------------------   
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T3"."OWNER")                                       
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")                                
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择22行。

SQL> explain plan for
  2  select * from t1,t2,t3 where t1.object_id=t3.object_id and t1.owner=t2.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 2467348796                                                     
                                                                                
----------------------------------------------------------------------------   
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |      | 33333 |    16M|    29   (7)| 00:00:01 |   
|*  1 |  HASH JOIN          |      | 33333 |    16M|    29   (7)| 00:00:01 |   
|*  2 |   HASH JOIN         |      |   100 | 35400 |    23   (5)| 00:00:01 |   
|   3 |    TABLE ACCESS FULL| T3   |   100 | 17700 |     4   (0)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |   
|   5 |   TABLE ACCESS FULL | T2   |  1000 |   172K|     5   (0)| 00:00:01 |   

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
----------------------------------------------------------------------------   
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T2"."OWNER")                                       
   2 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")                                
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择22行。

SQL> explain plan for
  2  select/*+swap_join_inputs(t2)*/ * from t1,t2,t3 where t1.object_id=t3.object_id and t1.owner=t2.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 3835162943                                                     
                                                                                
----------------------------------------------------------------------------   
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |      | 33333 |    16M|    29   (7)| 00:00:01 |   
|*  1 |  HASH JOIN          |      | 33333 |    16M|    29   (7)| 00:00:01 |   
|   2 |   TABLE ACCESS FULL | T2   |  1000 |   172K|     5   (0)| 00:00:01 |   
|*  3 |   HASH JOIN         |      |   100 | 35400 |    23   (5)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| T3   |   100 | 17700 |     4   (0)| 00:00:01 |   
|   5 |    TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |   

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
----------------------------------------------------------------------------   
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T2"."OWNER")                                       
   3 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")                                
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择22行。

SQL> drop table t2;

表已删除。

SQL> create table t2 as select * from dba_objects where rownum<10001;

表已创建。

SQL> select count(*) from t1;

  COUNT(*)                                                                     
----------                                                                     
     10000                                                                     

SQL> select count(*) from t2;

  COUNT(*)                                                                     
----------                                                                     
     10000                                                                     

SQL> explain plan for
  2  select * from t1,t2 where t1.owner=t2.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1838229974                                                     
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|*  1 |  HASH JOIN         |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T2   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T2"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。

SQL> explain plan for
  2  select/*+swap_join_inputs(t2)*/ * from t1,t2 where t1.owner=t2.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 2959412835                                                     
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|*  1 |  HASH JOIN         |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|   2 |   TABLE ACCESS FULL| T2   | 10000 |  1728K|    18   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
               
    这里swap_join_inputs起作用了，猜测是因为optimizer认为两个表cost完全一样，所以没有忽略hint                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T2"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。

   

SQL> explain plan for
  2  select/*+no_swap_join_inputs(t1)*/ * from t1,t2 where t1.owner=t2.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1838229974                                                     
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|*  1 |  HASH JOIN         |      |    12M|  4220M|   135  (74)| 00:00:03 |     
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T2   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                     
       这里no_swap_join_inputs没有起作用（原因不明）                                                  

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T2"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。



SQL> select count(*) from t1;

  COUNT(*)                                                                     
----------                                                                     
     10000                                                                     

SQL> select count(*) from t3;

  COUNT(*)                                                                     
----------                                                                     
       100                                                                     

SQL> explain plan for
  2  select * from t1,t3 where t1.owner=t3.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 740309051                                                      
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |   125K|    42M|    24   (9)| 00:00:01 |     
|*  1 |  HASH JOIN         |      |   125K|    42M|    24   (9)| 00:00:01 |     
|   2 |   TABLE ACCESS FULL| T3   |   100 | 17700 |     4   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T3"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。

SQL> explain plan for
  2  select/*+no_swap_join_inputs(t3)*/ * from t1,t3 where t1.owner=t3.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 740309051                                                      
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |   125K|    42M|    24   (9)| 00:00:01 |     
|*  1 |  HASH JOIN         |      |   125K|    42M|    24   (9)| 00:00:01 |     
|   2 |   TABLE ACCESS FULL| T3   |   100 | 17700 |     4   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T3"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。

SQL> explain plan for
  2  select/*+swap_join_inputs(t1)*/ * from t1,t3 where t1.owner=t3.owner;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 740309051                                                      
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT   |      |   125K|    42M|    24   (9)| 00:00:01 |     
|*  1 |  HASH JOIN         |      |   125K|    42M|    24   (9)| 00:00:01 |     
|   2 |   TABLE ACCESS FULL| T3   |   100 | 17700 |     4   (0)| 00:00:01 |     
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  1728K|    18   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - access("T1"."OWNER"="T3"."OWNER")                                       
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   

已选择19行。

    这里两个hint都失去效果了，因为两个表大小不一样，所以优化器聪明的选择了cost小的表为内表

使用道具 举报

回复
sam_gyg
论坛徽章:
12
2009新春纪念徽章 日期:2009-01-04 14:52:28优秀写手 日期:2014-06-20 06:00:12蛋疼蛋 日期:2012-02-22 08:52:48ITPUB十周年纪念徽章 日期:2011-11-01 16:24:04生肖徽章2007版:鸡 日期:2009-11-11 13:17:35祖国60周年纪念徽章 日期:2009-10-09 08:28:00ITPUB8周年纪念徽章 日期:2009-09-27 10:21:21生肖徽章2007版:龙 日期:2009-08-09 19:39:29生肖徽章2007版:鸡 日期:2009-08-03 23:22:352009日食纪念 日期:2009-07-22 09:30:00
32#
发表于 2009-1-7 14:38 | 只看该作者
学习了这个swap_join_intputs

使用道具 举报

回复
Yong Huang
论坛徽章:
47
蒙奇·D·路飞 日期:2017-03-27 08:04:23马上有车 日期:2014-02-18 16:41:112014年新春福章 日期:2014-02-18 16:41:11一汽 日期:2013-09-01 20:46:27复活蛋 日期:2013-03-13 07:55:232013年新春福章 日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章 日期:2012-10-09 18:03:322012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:20
33#
发表于 2009-1-8 11:42 | 只看该作者
Thanks, battleman. I can reproduce your test. Indeed something is not right with the swap_join_inputs hint. Normally Oracle should not ignore a hint unless there's syntax error or a bug. I checked my 10053 trace. The "Dumping Hints" part does say the hint is used ("used=1"); if it were not used, it would be "used=0" and/or "err=[a non-zero number]". My trace file for no_swap_join_inputs test has "used=0".

I don't know the answer. But I'll keep looking for one.

Yong Huang

[2008-01-08 addition]

According to the document searched here
http://www.google.com/search?hl= ... ch&aq=f&oq=
"used=0" does not necessarily mean it's not used. Anybody has an idea?

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-1-8 16:28 编辑 ]

使用道具 举报

回复
venee_lee
论坛徽章:
5
2009新春纪念徽章 日期:2009-01-04 14:52:28授权会员 日期:2009-01-17 15:10:222009日食纪念 日期:2009-07-22 09:30:002011新春纪念徽章 日期:2011-02-18 11:43:33ITPUB元老 日期:2012-08-14 18:34:13
34#
发表于 2009-5-8 10:31 | 只看该作者
正在看这部分内容，有地方不理解，过来瞧瞧，探讨得够仔细。
慢慢琢磨。

使用道具 举报

回复

1234
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　联系我们 未成年人举报专区　
京ICP备16024965号-8 　北京市公安局海淀分局网监中心备案编号：11010802021510　广播电视节目制作经营许可证：编号（京）字第1149号   
快速回复 返回顶部 返回列表