当前位置： 首页 > 调优 > 正文

SWAP_JOIN_INPUTS 修改HASH连接的驱动表

Nov092014
作者：huangtingzhong   发布：2014-11-09 22:54   分类：调优   阅读：502 views   抢沙发  

      下面是测试在HASH连接的时候，修改驱动表的顺序。HASH连接不能用于不等值连接。

1 环境介绍

www.cdhtz.com > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

www.cdhtz.com > !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

2 创建测试表

www.cdhtz.com > create table htz.htz1 as select * from dba_objects;

Table created.

www.cdhtz.com > create table htz.htz2 as select * from dba_objects;

Table created.

www.cdhtz.com > create table htz.htz3 as select * from dba_objects;

Table created.

3 原SQL语句执行计划

www.cdhtz.com > select /*+ huang*/count(*)

  2    from htz.htz1 a, htz.htz2 b, htz.htz3 c

  3   where a.object_id = b.object_id

  4     and a.object_id = c.object_id;

  COUNT(*)

———-

     74656

www.cdhtz.com > @find_sql

Enter value for sql_text: huang

Enter value for sql_id:

SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

——————————————————————————————

7uyt5873uq4av      0 3349877083  283048524          1           .09           .09 SYS

select /*+ huang*/count(*)   from htz.htz1 a, htz.htz2 b, htz.htz3 c  where a.object_id = b.object_id    and a.object_id = c.object_id

www.cdhtz.com > set lines 200

www.cdhtz.com > select * from table(dbms_xplan.display_cursor(‘7uyt5873uq4av’,null,’advanced’));

PLAN_TABLE_OUTPUT

——————————————————————————————

SQL_ID  7uyt5873uq4av, child number 0

————————————-

select /*+ huang*/count(*)   from htz.htz1 a, htz.htz2 b, htz.htz3 c

where a.object_id = b.object_id    and a.object_id = c.object_id

Plan hash value: 283048524

————————————————————————————-

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————-

|   0 | SELECT STATEMENT     |      |       |       |       |  1242 (100)|          |

|   1 |  SORT AGGREGATE      |      |     1 |    39 |       |            |          |

|*  2 |   HASH JOIN          |      | 61296 |  2334K|  1704K|  1242   (1)| 00:00:15 |

|   3 |    TABLE ACCESS FULL | HTZ3 | 69661 |   884K|       |   298   (1)| 00:00:04 |

|*  4 |    HASH JOIN         |      | 61296 |  1556K|  1504K|   750   (1)| 00:00:09 |

|   5 |     TABLE ACCESS FULL| HTZ1 | 61295 |   778K|       |   298   (1)| 00:00:04 |

|   6 |     TABLE ACCESS FULL| HTZ2 | 66602 |   845K|       |   298   (1)| 00:00:04 |

————————————————————————————-

 

Query Block Name / Object Alias (identified by operation id):

————————————————————-

 

   1 – SEL$1

   3 – SEL$1 / C@SEL$1

   5 – SEL$1 / A@SEL$1

   6 – SEL$1 / B@SEL$1

 

Outline Data

————-

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)

      DB_VERSION(‘11.2.0.3’)

      OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)

      OPT_PARAM(‘_optimizer_use_feedback’ ‘false’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “A”@”SEL$1”)

      FULL(@”SEL$1″ “B”@”SEL$1”)

      FULL(@”SEL$1″ “C”@”SEL$1”)

      LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

      USE_HASH(@”SEL$1″ “B”@”SEL$1”)

      USE_HASH(@”SEL$1″ “C”@”SEL$1”)

      SWAP_JOIN_INPUTS(@”SEL$1″ “C”@”SEL$1”)

      END_OUTLINE_DATA

  */

4 SWAP_JOIN_INPUTS更改驱动表

手动指定表的访问顺序为A,B,C，A为驱动表，AB的结果集再去驱动C

www.cdhtz.com > alter system flush shared_pool;

 

System altered.

这里直接COPY Outline Data中的提示信息来修改就可以了。

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1″) SWAP_JOIN_INPUTS(@”SEL$1” “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1” “C”@”SEL$1”) */

 count(*)

  from htz.htz1 a, htz.htz2 b, htz.htz3 c

 where a.object_id = b.object_id

  5     and a.object_id = c.object_id;

 

  COUNT(*)

———-

     74656

 

www.cdhtz.com > @find_sql.sql

Enter value for sql_text: LEADING

Enter value for sql_id:

 

SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

———————————————————————————————–

87cu5q9nkqgzk      0 1764442098  274524172          1           .09           .09 SYS

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1″) SWAP_JOIN_INPUTS(@”SEL$1” “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1” “C”@”SEL$1”) */  count(*)   from htz.htz1 a, htz.htz2 b,

htz.htz3 c  where a.object_id = b.object_id    and a.object_id = c.object_id

 

 

www.cdhtz.com > select * from table(dbms_xplan.display_cursor(’87cu5q9nkqgzk’,null,’advanced’));

 

PLAN_TABLE_OUTPUT

———————————————————————————————–

SQL_ID  87cu5q9nkqgzk, child number 0

————————————-

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

SWAP_JOIN_INPUTS(@”SEL$1″ “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1”

“C”@”SEL$1”) */  count(*)   from htz.htz1 a, htz.htz2 b, htz.htz3 c

where a.object_id = b.object_id    and a.object_id = c.object_id

 

Plan hash value: 274524172

 

————————————————————————————-

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————-

|   0 | SELECT STATEMENT     |      |       |       |       |  1242 (100)|          |

|   1 |  SORT AGGREGATE      |      |     1 |    39 |       |            |          |

|*  2 |   HASH JOIN          |      | 61296 |  2334K|  2280K|  1242   (1)| 00:00:15 |

|*  3 |    HASH JOIN         |      | 61296 |  1556K|  1504K|   750   (1)| 00:00:09 |

|   4 |     TABLE ACCESS FULL| HTZ1 | 61295 |   778K|       |   298   (1)| 00:00:04 |

|   5 |     TABLE ACCESS FULL| HTZ2 | 66602 |   845K|       |   298   (1)| 00:00:04 |

|   6 |    TABLE ACCESS FULL | HTZ3 | 69661 |   884K|       |   298   (1)| 00:00:04 |

————————————————————————————-

 

Query Block Name / Object Alias (identified by operation id):

————————————————————-

 

   1 – SEL$1

   4 – SEL$1 / A@SEL$1

   5 – SEL$1 / B@SEL$1

   6 – SEL$1 / C@SEL$1

 

Outline Data

————-

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)

      DB_VERSION(‘11.2.0.3’)

      OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)

      OPT_PARAM(‘_optimizer_use_feedback’ ‘false’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “A”@”SEL$1”)

      FULL(@”SEL$1″ “B”@”SEL$1”)

      FULL(@”SEL$1″ “C”@”SEL$1”)

      LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

      USE_HASH(@”SEL$1″ “B”@”SEL$1”)

      USE_HASH(@”SEL$1″ “C”@”SEL$1”)

      END_OUTLINE_DATA

  */

 

Predicate Information (identified by operation id):

—————————————————

 

   2 – access(“A”.”OBJECT_ID”=”C”.”OBJECT_ID”)

   3 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

 

Column Projection Information (identified by operation id):

———————————————————–

 

   1 – (#keys=0) COUNT(*)[22]

   2 – (#keys=1)

   3 – (#keys=1) “A”.”OBJECT_ID”[NUMBER,22]

   4 – “A”.”OBJECT_ID”[NUMBER,22]

   5 – “B”.”OBJECT_ID”[NUMBER,22]

   6 – “C”.”OBJECT_ID”[NUMBER,22]

如果提示没有生效，请注意_optimizer_ignore_hints参数是否配置成TRUE。如果配置成true，那么SQL中的所有提示都将被忽略。

本文固定链接: http://www.htz.pw/?p=834 | 认真就输

该日志由 huangtingzhong 于2014年11月09日发表在 调优 分类下， 通告目前不可用，你可以至底部留下评论。
原创文章转载请注明: SWAP_JOIN_INPUTS 修改HASH连接的驱动表 | 认真就输
关键字:
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！