使用hint来调优sql语句(72天)

最近生产发现有一个sql语句运行耗时达5000多秒。 抓出来sql_id一看,sql倒不是一个很长的语句。结构也很简单。如下。

select company_code, sap_company_id
  from data_company_code
 where company_code not in
       (SELECT distinct l9_company_code
          FROM detailed_data_info_v a, refund_request b
         WHERE a.financial_activity = 'RFND'
           and a.refund_method = 'AP'
           AND a.refund_id = b.refund_id
           AND b.refund_status = 'P'
           AND b. REVERSAL_TRANS_ID is null
           AND posting_date = TO_DATE(20140511, 'YYYYMMDD'))

执行计划如下:

Execution Plan                                                                                                           
-----------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
-----------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT              |                       |       |       |  2696K(100)|          |       |       |  
|   1 |  FILTER                       |                       |       |       |            |          |       |       |  
|   2 |   MAT_VIEW ACCESS FULL        | DATA_COMPANY_CODE     |     5 |    35 |     3   (0)| 00:00:01 |       |       |  
|   3 |   NESTED LOOPS                |                       |       |       |            |          |       |       |  
|   4 |    NESTED LOOPS               |                       |     1 |    68 |  1078K  (2)| 03:35:43 |       |       |  
|   5 |     PARTITION RANGE ALL       |                       |     1 |    59 |  1078K  (2)| 03:35:43 |     1 |   366 |  
|   6 |      TABLE ACCESS FULL        | DETAILED_DATA         |     1 |    59 |  1078K  (2)| 03:35:43 |     1 |   366 |  
|   7 |     INDEX UNIQUE SCAN         | REFUND_REQUEST_PK     |     1 |       |     1   (0)| 00:00:01 |       |       |  
|   8 |    TABLE ACCESS BY INDEX ROWID| REFUND_REQUEST        |     1 |     9 |     1   (0)| 00:00:01 |       |       |  
-----------------------------------------------------------------------------------------------------------------------  

查看最后的输出表data_company_code,发现是一个数据字典表,里面的数据很少。只有5条。 表 detailed_data_info_v是一个视图,里面参照的基表只有1个. detailed_data_info 它是一个历史表,里面有3亿多条数据。而且对应的主键在查询条件中也没有,这也是这个sql执行慢的主要原因。 表 REFUND_REQUEST 是一个应用表,里面的数据就几百条。 明白了大概的情况之后。 首先从视图下手。看看 a.refund_method , a.refund_id,company_code都运用了大量的decode,可以看到都是基于financial_activity来做的过滤,所以直接可以提出其他的条件过滤,直接使用基表来去得所需的条件。

DECODE( FINANCIAL_ACTIVITY,
'RFND',
 DATA_FIELD_1) as REFUND_ID 

DECODE( FINANCIAL_ACTIVITY,
'RFND',
 DATA_FIELD_3) as REFUND_METHOD

DECODE( FINANCIAL_ACTIVITY,
'RFND',
 DATA_FIELD_10 ,
 'WERRE',
 DATA_FIELD_10 ,
。。。。。。。
 DATA_FIELD_19 ,
 'BCK',
 DATA_FIELD_20 ,
 'DSPCAN',
 DATA_FIELD_7 ,
 'DSPREJ',
 DATA_FIELD_7 ,
 'WER',
 DATA_FIELD_7 ,
 'WWER',
 DATA_FIELD_7 ,
 'DD',
 DATA_FIELD_7 ,
 'reer',
 DATA_FIELD_7 ,
 'tttt',
 DATA_FIELD_8 ,
 'xxxx',
 DATA_FIELD_9) as COMPANY_CODE 

所以把查询可以构造成几个子查询。黄色是做了变化的部分。

select a.DATA_FIELD_10 l9_company_code
           from DETAILED_DATA a
           where a.financial_activity = 'RFND'
           and a.DATA_FIELD_3 = 'AP'
           and a.posting_date = TO_DATE(20140511, 'YYYYMMDD')

另外一个子查询。

select refund_id
               from ar1_refund_request b
               where b.refund_status='P'
           and b.reversal_trans_id is null  
            /
  no rows selected

结果已查询,让我大跌眼镜,竟然没有匹配的值。但是sql语句还是会不断的去做无用功。查了半天,结果返回了一个Null。 找到了基本的方向,如果查询条件中没有匹配的值,至少可以不用再从3亿多条记录的表里去全表扫描了。 在测试下面的查询时,如果屏蔽掉条件a.financial_activity = 'RFND',查询就会直接先进入refund_request了。

SQL> select 
            distinct a.DATA_FIELD_10 l9_company_code  
              from DETAILED_DATA a
         where a.DATA_FIELD_3 = 'AP'
           --and a.financial_activity = 'RFND'
           and a.posting_date = TO_DATE(20140512, 'YYYYMMDD')
           and exists (select 1
                  from refund_request b
                 where b.refund_id = a.DATA_FIELD_1
                   and b.refund_status = 'P'
                   and b.reversal_trans_id is null) 
   /
no rows selected
Elapsed: 00:00:00.01

如果没有数据,马上就返回了。类似于这样的方式 select xxx from huge_table where 1!=1 但是已加入条件financial_activity就开始扫描大表,看来只能使用Hint来强制指定表的访问顺序了。当然了使用hint也是玩不得以而为之。不建议一开始调就考虑hint.

SQL> select company_code, sap_company_id
      from ar9_company_code
     where company_code not in
       (select /*+leading(b,a)*/
        distinct a.DATA_FIELD_10 l9_company_code --,financial_activity,DATA_FIELD_3,posting_date 
          from AR1_GL_DETAILED_DATA a
         where a.DATA_FIELD_3 = 'AP'
           and a.financial_activity = 'RFND'
           and a.posting_date = TO_DATE(20140512, 'YYYYMMDD')
           and exists (select 1
                  from ar1_refund_request b
                 where b.refund_id = a.DATA_FIELD_1
                   and b.refund_status = 'P'
                   and b.reversal_trans_id is null))
SQL> /
COMPAN SAP_COMPANY_ID
------ --------------
AE                1010
XX                1068
XXE               1067
DS                1027
EER               1019
Elapsed: 00:00:00.01

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

探索ASH 第一篇

老是在用ASH,对它的依赖感觉已经大于AWR,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。 先看看v$active_sessi...

3758
来自专栏数据之美

修改 mysql/oracle/bash/vimrc/cmd 提示符格式与颜色

(1)修改mysql提示符: MySQL 客户端的默认提示符是 "mysql>",基本上没什么实际作用。其实可以修改这个提示符,让它显示一些有用的信息,例如当前...

20710
来自专栏乐沙弥的世界

ORA-01652: unable to extend temp segment by 8192...

      最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错...

681
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(18)-权限管理系统-表数据

这一节,我们插入数据来看看数据流,让各位同学,知道这个权限表交互是怎么一个流程,免得大家后天雾里来雾里去 首先我再解释一些表,SysUser和SysRole表不...

21410
来自专栏青枫的专栏

day43_Oracle学习笔记_02

512
来自专栏Netkiller

数据库恢复方案

数据库恢复方案 摘要 这里所谈的内容是对备份数据的恢复,不是对损坏数据表的恢复,或者说灾难恢复。 目录 1. 背景 2. 备份方式分析 3. 恢复方案 3.1....

2825
来自专栏小鹏的专栏

在mac上安装Xgboost Python库

最近在mac上用到xgboost库,安装时遇到颇多大坑,网上查了很多答案几乎都是win上的问题,没遇到理想的,自己也就摸着石头把几个大坑给填了,总结一下,给后...

21610
来自专栏Java学习123

MyEclipse10.7安装jad反编译插件

2747
来自专栏大数据学习笔记

Spark2.x学习笔记:15、Spark SQL的SQL

15、 Spark SQL的SQL 15.1 Spark SQL所支持的SQL语法 select [distinct] [column names]|[wild...

2238
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

3174

扫码关注云+社区