加hint改变执行计划访问顺序优化sql

最新推荐文章于 2021-05-31 12:16:00 发布
最新推荐文章于 2021-05-31 12:16:00 发布
阅读量226 收藏
点赞数
文章标签： 数据库
今天又捕获到一个执行代价比较高的sql，下面是对其优化的大致步骤
sql语句如下
SELECT *
  FROM (SELECT row_.*, rownum rn
          FROM (select /*+ leading(eu,sh) */ sh.*,
                       pm.PAYMENT_TYPE,
                       pm.PAYMENT_NAME as ORDER_justin_method_NAME
                  from v_justin sh
                  left join justin_user eu
                    on eu.id = sh.justin_user_ID
                  left join justin_method pm
                    on pm.id = sh.ORDER_justin_method_ID
                 where sh.IS_LEAF = :g
                   and sh.mc_site_id in (:a)
                   and eu.justin_user_NAME like :b
                   and trunc(sh.ORDER_CREATE_TIME) >=
                       trunc(to_date(:c, 'yyyy-mm-dd'))
                   and trunc(sh.ORDER_CREATE_TIME) <=
                       trunc(to_date(:d, 'yyyy-mm-dd'))
                 order by sh.ORDER_CREATE_TIME desc) row_
         WHERE rownum <= :e)

通过set autotrace查看执行计划和consistent gets
SQL> var a number;
SQL> var b varchar2(200);
SQL> var c varchar2(20);
SQL> var d varchar2(20);
SQL> var e number;
SQL> var f number;
SQL> var g number;
SQL> exec :a := 1;

PL/SQL procedure successfully completed.

SQL> exec :b :='% gui_8@126.com%';

PL/SQL procedure successfully completed.

SQL> exec :c := '2010-10-31';

PL/SQL procedure successfully completed.

SQL> exec :d := '2011-01-20';

PL/SQL procedure successfully completed.

SQL> exec :e := 20;

PL/SQL procedure successfully completed.

SQL> exec :f := 0;

PL/SQL procedure successfully completed.

SQL> exec :g := 1;

PL/SQL procedure successfully completed.


查看原语句执行计划，可以看到consisten gets值非常高，达到七位数
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |    98 |   537K| 12576   (1)| 00:02:31 |
|*  1 |  VIEW                                |                 |    98 |   537K| 12576   (1)| 00:02:31 |
|*  2 |   COUNT STOPKEY                      |                 |       |       |            |          |
|   3 |    VIEW                              |                 |    98 |   535K| 12576   (1)| 00:02:31 |
|*  4 |     SORT ORDER BY STOPKEY            |                 |    98 |   536K| 12576   (1)| 00:02:31 |
|*  5 |      HASH JOIN RIGHT OUTER           |                 |    98 |   536K| 12575   (1)| 00:02:31 |
|   6 |       TABLE ACCESS FULL              | justin_method   |    77 |  1694 |     2   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                   |                 |    98 |   534K| 12573   (1)| 00:02:31 |
|   8 |        VIEW                          | v_justin        |  1952 |    10M|  8665   (1)| 00:01:44 |
|   9 |         UNION-ALL                    |                 |       |       |            |          |
|* 10 |          FILTER                      |                 |       |       |            |          |
|* 11 |           TABLE ACCESS BY INDEX ROWID| justin_a        |   151 | 71725 |   447   (1)| 00:00:06 |
|* 12 |            INDEX RANGE SCAN          | IDX_CREATE_TIME |  1089 |       |    13   (0)| 00:00:01 |
|* 13 |          FILTER                      |                 |       |       |            |          |
|* 14 |           TABLE ACCESS BY INDEX ROWID| justin_b        |  1801 |   851K|  8218   (1)| 00:01:39 |
|* 15 |            INDEX RANGE SCAN          | IDX_CREATE_DATE | 13007 |       |   765   (1)| 00:00:10 |
|* 16 |        TABLE ACCESS BY INDEX ROWID   | justin_user     |     1 |    25 |     2   (0)| 00:00:01 |
|* 17 |         INDEX UNIQUE SCAN            | PK1             |     1 |       |     1   (0)| 00:00:01 --------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
    2804436  consistent gets
          0  physical reads
        124  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

可优化部分主要集中于黑体字区域，其中view v_justin部分是访问视图，暂不考虑优化，剩下的只有justin_user表了，该sql访问了justin_user表里的justin_user_name字段，但是执行计划中并未出现；
通过justin_user_name可以顾虑很大一部分条件，并且以该列为引导列建有复合的unique index，可以改变执行计划的访问顺序，让它用到该索引，且提早执行
考虑添加hint改变访问顺序，
SQL> SELECT *
  2    FROM (SELECT row_.*, rownum rn
  3            FROM (select /*+ leading(eu,sh) */ sh.*,
  4                         pm.PAYMENT_TYPE,
  5                         pm.PAYMENT_NAME as ORDER_justin_method_NAME
  6                    from v_justin sh
  7                    left join justin_user eu
  8                      on eu.id = sh.justin_user_ID
  9                    left join justin_method pm
10                      on pm.id = sh.ORDER_justin_method_ID
11                   where sh.IS_LEAF = :g
12                     and sh.mc_site_id in (:a)
13                     and eu.justin_user_NAME like :b
14                     and trunc(sh.ORDER_CREATE_TIME) >=
15                         trunc(to_date(:c, 'yyyy-mm-dd'))
16                     and trunc(sh.ORDER_CREATE_TIME) <=
17                         trunc(to_date(:d, 'yyyy-mm-dd'))
18                   order by sh.ORDER_CREATE_TIME desc) row_
19           WHERE rownum <= :e)
20   WHERE RN > :f;
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    98 |   537K|       | 54186   (1)| 00:10:51 |
|*  1 |  VIEW                                |                        |    98 |   537K|       | 54186   (1)| 00:10:51 |
|*  2 |   COUNT STOPKEY                      |                        |       |       |       |            |          |
|   3 |    VIEW                              |                        |    98 |   535K|       | 54186   (1)| 00:10:51 |
|*  4 |     SORT ORDER BY STOPKEY            |                        |    98 |   536K|       | 54186   (1)| 00:10:51 |
|*  5 |      HASH JOIN RIGHT OUTER           |                        |    98 |   536K|       | 54185   (1)| 00:10:51 |
|   6 |       TABLE ACCESS FULL              | justin_method          |    77 |  1694 |       |     2   (0)| 00:00:01 |
|*  7 |       HASH JOIN                      |                        |    98 |   534K|  9088K| 54183   (1)| 00:10:51 |
|   8 |        TABLE ACCESS BY INDEX ROWID   | justin_user               |   251K|  6137K|       | 44557   (1)| 00:08:55 |
|*  9 |         INDEX RANGE SCAN             | IDX_justin_user_NAME_UNIQ | 45248 |       |       |   271   (1)| 00:00:04 |
|  10 |        VIEW                          | v_justin               |  1952 |    10M|       |  8665   (1)| 00:01:44 |
|  11 |         UNION-ALL                    |                        |       |       |       |            |          |
|* 12 |          FILTER                      |                        |       |       |       |            |          |
|* 13 |           TABLE ACCESS BY INDEX ROWID| justin_a                     |   151 | 71725 |       |   447   (1)| 00:00:06 |
|* 14 |            INDEX RANGE SCAN          | IDX_CREATE_TIME        |  1089 |       |       |    13   (0)| 00:00:01 |
|* 15 |          FILTER                      |                        |       |       |       |            |          |
|* 16 |           TABLE ACCESS BY INDEX ROWID| justin_b               |  1801 |   851K|       |  8218   (1)| 00:01:39 |
|* 17 |            INDEX RANGE SCAN          | IDX_CREATE_DATE        | 13007 |       |       |   765   (1)| 00:00:10
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     161888  consistent gets
          0  physical reads
          0  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看出consistent gets变成了6位数，有所好转，但依然很高； 同时黄体部分，执行计划采用了hash join，并把justin_user当成驱动表；
继续尝试添加hint
SQL> SELECT *
  2    FROM (SELECT row_.*, rownum rn
  3            FROM (select /*+ use_nl(eu,sh) leading(eu,sh) */ sh.*,
  4                         pm.PAYMENT_TYPE,
  5                         pm.PAYMENT_NAME as ORDER_justin_method_NAME
  6                    from v_justin sh
  7                    left join justin_user eu
  8                      on eu.id = sh.justin_user_ID
  9                    left join justin_method pm
10                      on pm.id = sh.ORDER_justin_method_ID
11                   where sh.IS_LEAF = :g
12                     and sh.mc_site_id in (:a)
13                     and eu.justin_user_NAME like :b
14                     and trunc(sh.ORDER_CREATE_TIME) >=
15                         trunc(to_date(:c, 'yyyy-mm-dd'))
16                     and trunc(sh.ORDER_CREATE_TIME) <=
17                         trunc(to_date(:d, 'yyyy-mm-dd'))
18                   order by sh.ORDER_CREATE_TIME desc) row_
19           WHERE rownum <= :e)
20   WHERE RN > :f;
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    98 |   537K|  5077K  (1)| 16:55:36 |
|*  1 |  VIEW                                |                        |    98 |   537K|  5077K  (1)| 16:55:36 |
|*  2 |   COUNT STOPKEY                      |                        |       |       |            |          |
|   3 |    VIEW                              |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|*  4 |     SORT ORDER BY STOPKEY            |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|*  5 |      HASH JOIN RIGHT OUTER           |                        |    98 |   535K|  5077K  (1)| 16:55:36 |
|   6 |       TABLE ACCESS FULL              | justin_method          |    77 |  1694 |     2   (0)| 00:00:01 |
|   7 |       NESTED LOOPS                   |                        |    98 |   533K|  5077K  (1)| 16:55:36 |
|   8 |        TABLE ACCESS BY INDEX ROWID   | justin_user               |   251K|  6137K| 44557   (1)| 00:08:55 |
|*  9 |         INDEX RANGE SCAN             | IDX_justin_user_NAME_UNIQ | 45248 |       |   271   (1)| 00:00:04 |
|  10 |        VIEW                          | v_justin               |     1 |  5553 |    20   (0)| 00:00:01 |
|  11 |         UNION ALL PUSHED PREDICATE   |                        |       |       |            |          |
|* 12 |          FILTER                      |                        |       |       |            |          |
|* 13 |           TABLE ACCESS BY INDEX ROWID| justin_a                     |     1 |   475 |     8   (0)| 00:00:01 |
|* 14 |            INDEX RANGE SCAN          | IDX_SO_justin_user_ID     |     5 |       |     3   (0)| 00:00:01 |
|* 15 |          FILTER                      |                        |       |       |            |          |
|* 16 |           TABLE ACCESS BY INDEX ROWID| justin_b               |     1 |   484 |    12   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN          | IDX_SH_justin_user_ID     |     9 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      30074  consistent gets
          0  physical reads
          0  redo size
      11145  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
这次效果就非常明显了，consistent gets变成了5位数，此时对表justin_user走了基于justin_user_name的索引，但是使用的谓词为like且绑定变量有前后都有通配符%%，所以其代价应该比较大
验证一下
SQL> select id from justin_user where justin_user_name like :b;


Execution Plan
----------------------------------------------------------
Plan hash value: 4183727034

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |   251K|  6137K| 44557   (1)| 00:08:55 |
|   1 |  TABLE ACCESS BY INDEX ROWID| justin_user               |   251K|  6137K| 44557   (1)| 00:08:55 |
|*  2 |   INDEX RANGE SCAN          | IDX_justin_user_NAME_UNIQ | 45248 |       |   271   (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("justin_user_NAME" LIKE :B)
       filter("justin_user_NAME" LIKE :B)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      30053  consistent gets
          0  physical reads
          0  redo size
        580  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
此条sql的Consistent gets就有30053，而优化后的sql只有30074，可见优化后的sql应属于当前最优

[ 本帖最后由 myownstars 于 2011-1-26 18:40 编辑 ]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-688365/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-688365/

congdiku5262
关注
收获不止SQL优化
08-07
4.2.2　非HINT方式的执行计划改变 72 4.2.3　执行计划的固定 100 4.3　本章习题、总结与延伸 102 第5章　且慢，感受体系结构让SQL飞 103 5.1　体系结构知识 104 5.1.1　组成 104 5.1.2　原理 104 5.1.3　体会 105 ...
跟着官方文档学HINT（六）连接顺序
道行尚浅（老道）的博客
11-03 851
--LEADING 指定生成的执行计划的驱动表 SQL> SELECT * 2 FROM DEPT D, DAO_OBJECT1 DO, EMP E 3 WHERE D.DEPTNO = DO.OBJECT_ID 4 AND E.DEPTNO = D.DEPTNO; 14 rows selected. Execution Plan ------------
Hint 使用--leading
lyu1026的博客
05-31 1884
Oracle hint -- leading 的作用是提示优化器某张表先访问，可以指定一张或多张表，当指定多张表时，表示按指定的顺序访问这几张表。而 Postgresql leading hint的功能与oracle不同，leading 后面必须跟两张或多张表，如果是两张，表示这两张表先进行连接，但两张表的访问顺序不定。如果要严格控制表的访问顺序，还必须使用双括号，具体用法以例子形式进行介绍。 ...
SQL hint中的ORDER与Leading(zt)
cmhaqkcrx827119593的博客
03-27 448
关于LEADING、ORDERED： /*+ORDERED*/提示按照from后表的顺序执行。 /*+LEADING(table1 table2 ...)*/提示按照指定顺序执行。 通常在sql中出现了join的时...
如何修改mysql执行计划_几种改变执行计划的方法
weixin_42629815的博客
02-03 1144
一、使用Hint1、可以从v$sql_hint视图中查询Oracle所支持的所有的hint2、Hint分为6种：1)初始化参数hint(可以覆盖在系统级或者会话级定义的初始化参数)，如：all_rows,first_rows,cursor_sharing_exact, dynamic_sampling, gather_plan_statisitcs,no_cpu_costing,optimizer...
修改oracle执行计划顺序,ORACLE执行计划的步骤和顺序
weixin_39524703的博客
04-08 303
阅读oracle执行计划的方法：先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行：对于并列的，靠上的先执行。即并列的缩进块，从上往下执行，非并列的缩进块，从下往上执行。如下示例：Execution Plan----------------------------------------------------------0 SELECT STATEMENT O...
Oracle执行计划介绍与测试.pdf
09-13
#### 控制与改变执行计划 控制执行计划主要通过使用Oracle的提示（HINT）或OUTLINE来实现，以适应不同场景下的性能需求。 - **使用HINT**：HINT允许开发者直接干预Oracle优化器的决策过程，例如： - 改变表的连接...
oracle执行计划
05-17
#### 三、控制与改变执行计划的方法 1. **使用Oracle的HINTs** - HINTs是一种特殊的注释语法，用于指导Oracle优化器选择特定的执行计划。 - 例如： ```sql SELECT /*+ INDEX(emp) */ count(*) FROM emp; ``` ...
Oracle中hint的理解篇
10-17
- **调整表的连接顺序**：改变表的连接顺序可能会对执行计划产生重大影响。 - **设置语句的并行度**：可以控制SQL语句执行时的并行程度。 #### 三、Hint的使用方法 1. **语法格式**：`{DELETE|INSERT|SELECT|...
收获，不止SQL优化--抓住SQL的本质
12-21
4.2.2　非HINT方式的执行计划改变 72 4.2.3　执行计划的固定 100 4.3　本章习题、总结与延伸 102 第5章　且慢，感受体系结构让SQL飞 103 5.1　体系结构知识 104 5.1.1　组成 104 5.1.2　原理 104 5.1.3　体会 105 ...
MySQL hint用法解析
11-23
我们可以对MySQL的对象（表、索引、触发器、自建函数、存储过程等）做注释（comment），这样做的目的是标识该对象的作用等以增强代码的可读性、方便其他同事快速读懂我们写的代码或某个数据库对象的作用，说白了，comment就是对我们人的一个提示。在MySQL中还有一种提示，叫做hint，hint是对数据库的提示，我们都知道， 在执行一条SQL语句的时候，MySQL都会生成一个执行计划，而hint就是用来告诉优化器按照我们告诉它的方式生成执行计划。Hint可以基于表连接的顺序、表连接的方法、访问路径、并行度等规则对DML语句、或者查查询语句产生作用，通过它我们可以实现： 1) 使用的优化器的类型 2) 基于代价的优化器的优化目标，是all_rows还是first_rows。 3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。 4) 表之间的连接类型 5) 表之间的连接顺序 6) 语句的并行程度
几种改变执行计划的方法
weixin_30906701的博客
10-29 135
一、使用Hint 1、可以从v$sql_hint视图中查询Oracle所支持的所有的hint 2、Hint分为6种： 1）初始化参数hint(可以覆盖在系统级或者会话级定义的初始化参数)，如： all_rows, first_rows,cursor_sharing_exact, dynamic_sampling, gather_plan_statis...
使用hint改变SQL执行计划的方法
aaron8219's Oracle&Linux Technology Blog
03-30 1301
使用hint改变SQL执行计划的使用方法
16.表关联顺序的HINT
weixin_34005042的博客
11-20 475
三、表关联顺序的HINT1、leading hint在多表关联中，这个hint指定由哪个表作为驱动表，告诉优化器先访问哪个表的数据。SQL> create table t1 as select 1 id,object_name name from dba_objects;Table created.SQL> create index ind_t1 on t1(id...
(ZT) 修改表连接顺序的hint
cuantiaowei3015的博客
03-11 270
Full HintingFiled under: Execution plans, Hints — Jonathan Lewis @ 9:17 pm UTC Jan 16,2007 This question came up...
Oracle hint执行计划控制：OUTLINE、SQL PROFILE与SQL BASELINE详解
"Oracle中基于hint的3种执行计划控制方法——OUTLINE、SQL PROFILE、SQL BASELINE的详细介绍，包括其原理和使用方法，适用于需要精细控制SQL执行计划的场景。" 在Oracle数据库中，执行计划的控制对于优化SQL性能至...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值