sql还有优化的方法吗

laishiwan · 发表于 2018-1-17 14:08

感谢各位专家们发表的意思。如下执行计划。可以分析了吗？
确实是保密问题。
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2643809999

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                   | 25 |  5500 | 115K  (1)| 00:23:07 |
| 1 |  HASH GROUP BY                   |                   | 25 |  5500 | 115K  (1)| 00:23:07 |
|*  2 | HASH JOIN OUTER                |                   | 25 |  5500 | 115K  (1)| 00:23:07 |
|*  3 | HASH JOIN                      |                   | 25 |  5400 | 115K  (1)| 00:23:07 |
|*  4 |    TABLE ACCESS FULL             | G3_DM_GY_NSRZT    |    1 |    8 |    3 (0)| 00:00:01 |
| 5 |    NESTED LOOPS                |                   |    |    |          |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 |    NESTED LOOPS                |                   | 201 | 41808 | 115K  (1)| 00:23:07 |
|*  7 |    HASH JOIN                   |                   | 203 | 28623 | 115K  (1)| 00:23:02 |
| 8 |       INLIST ITERATOR          |                   |    |    |          |       |
| 9 |       TABLE ACCESS BY INDEX ROWID| G3_DM_GY_SWJG       | 13 | 559 | 18 (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN       | G3_DM_GY_SWJG_SWJGMC | 13 |    |    9 (0)| 00:00:01 |
|  11 |       NESTED LOOPS             |                   |    |    |          |       |
|  12 |       NESTED LOOPS             |                   |  4474 | 428K| 115K  (1)| 00:23:02 |
|* 13 |       TABLE ACCESS FULL       | G3_DM_GY_GK       | 44 |  1980 |    5 (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN       | IDX_G3_ZS_JKS_SKGK | 70348 |    | 243 (1)| 00:00:03 |
|* 15 |       TABLE ACCESS BY INDEX ROWID| G3_ZS_JKS          | 103 |  5459 | 19178 (1)| 00:03:51 |
|* 16 |    INDEX UNIQUE SCAN          | PK_G3_ZS_JKS_NSR    |    1 |    |    1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 |    TABLE ACCESS BY INDEX ROWID | G3_ZS_JKS_NSR       |    1 | 67 |    2 (0)| 00:00:01 |
|  18 | TABLE ACCESS FULL             | G3_DM_GY_HYML       |  1646 |  6584 |    9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

2 - access("A"."HY_DM"="B1"."HY_DM"(+))
3 - access("C"."NSRZT_DM"="D"."NSRZT_DM")
4 - filter("D"."NSRZTMC"='正常')
7 - access("A"."ZGSWSKFJ_DM"="B14"."SWJG_DM")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  10 - access("B14"."SWJGMC"='单位名称1' OR "B14"."SWJGMC"='单位名称2' OR
            "B14"."SWJGMC"='单位名称3' OR "B14"."SWJGMC"='单位名称4' OR
            "B14"."SWJGMC"='单位名称5' OR "B14"."SWJGMC"='单位名称6' OR
            "B14"."SWJGMC"='单位名称7' OR "B14"."SWJGMC"='单位名称8' OR
            "B14"."SWJGMC"='单位名称9' OR "B14"."SWJGMC"='单位名称10' OR
            "B14"."SWJGMC"='单位名称11')
  13 - filter("B5"."SKGKMC" LIKE '%销售%' AND "B5"."SKGKMC" IS NOT NULL)
  14 - access("A"."SKGK_DM"="B5"."SKGK_DM")
  15 - filter("A"."SJJE">=1000 AND DECODE(TO_NUMBER("A"."LYBZ"),0,"A"."RKRQ",1,"A"."RKRQ2",3,"A"."R
            KRQ2")>=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
            DECODE(TO_NUMBER("A"."LYBZ"),0,"A"."RKRQ",1,"A"."RKRQ2",3,"A"."RKRQ2")<TO_DATE(' 2018-01-01

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  16 - access("C"."DJXH"="A"."DJXH")
  17 - filter("C"."NSRZT_DM" IS NOT NULL)

已选择47行。

newkid · 发表于 2018-1-18 01:14

你的查询隔着视图，已经和计划完全对不上号，比如你说的那个YJSF 730万，在计划里竟然无影无踪。
你应该从过滤谓词入手，看看过滤后的数据集合和计划里的估计行数是不是有很大偏差。大范围的数据扫描（我看你是要读一年的数据）往往是HASH JOIN更优的，所以那些NESTED LOOP就很可疑，好好看看下是不是行数估计错了。

laishiwan · 发表于 2018-1-18 09:43

newkid 发表于 2018-1-18 01:14
你的查询隔着视图，已经和计划完全对不上号，比如你说的那个YJSF 730万，在计划里竟然无影无踪。
你应该从 ...

返回的数量确实与执行计划相差较大。这个要怎样处理呢。

sqysl · 发表于 2018-1-18 11:13

laishiwan 发表于 2018-1-18 09:43
返回的数量确实与执行计划相差较大。这个要怎样处理呢。

1、优化SQL语句，需要SQL语句，执行计划，关键是数据环境，也就是该SQL相关表的数据量，条件列的选择性和数据分布，相关表上的索引情况，甚至表的结构等，楼主别说我毛病多，没办法，优化就是这么繁琐，需要了解很多细节信息，所以，优化有时需要连接到具体数据环境，做不到，那么就只能一点点反馈，效率比较低；
2、怀疑楼主给出的视图定义和执行计划不对应，因为执行计划里只出现了一个大数据量表G3_Z_JKS，而G3_ZS_YJSF没见着；
3、因为很多信息不掌握，我只能给出建议，不保证正确和一定解决问题，楼主可以试试，首先，消除计划中的NL，再就是，注意有没有更高效的索引。

SELECT /*+ use_hash(VW_G3_ZS_JKS.G3_DM_GY_GK) */ VW_G3_ZS_JKS.SHXYDM_NSRSBH SHXYDM_NSRSBH,
            VW_G3_ZS_JKS.TAX_PAYER_CODE TAX_PAYER_CODE,
            VW_G3_ZS_JKS.NSRMC NSRMC,
            sum(VW_G3_ZS_JKS.SJJE) SJJE,
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC ZGSWSKFJ_DM_MC
      FROM VW_G3_ZS_JKS
      WHERE 0 = 0
         AND (VW_G3_ZS_JKS.SJJE >= 1000 AND
            VW_G3_ZS_JKS.SKGK_DM_MC like '%销售%' AND
            VW_G3_ZS_JKS.RKRQ >= date
            '2017-01-01' AND VW_G3_ZS_JKS.RKRQ < date
            '2017-12-31' + 1 AND VW_G3_ZS_JKS.NSRZTMC = '正常' AND
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC in
            ('单位名称1',
               '单位名称2'，
                              '单位名称3'，
                              '单位名称4'，
                              '单位名称5'，
                              '单位名称6'，
                              '单位名称7'，
                              '单位名称8'，
                              '单位名称9'，
                              '单位名称10'，
               '单位名称11'))
      group by VW_G3_ZS_JKS.SHXYDM_NSRSBH,
               VW_G3_ZS_JKS.TAX_PAYER_CODE,
               VW_G3_ZS_JKS.NSRMC,
               VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC;
或
SELECT /*+ use_hash(VW_G3_ZS_JKS.G3_ZS_JKS) */ VW_G3_ZS_JKS.SHXYDM_NSRSBH SHXYDM_NSRSBH,
            VW_G3_ZS_JKS.TAX_PAYER_CODE TAX_PAYER_CODE,
            VW_G3_ZS_JKS.NSRMC NSRMC,
            sum(VW_G3_ZS_JKS.SJJE) SJJE,
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC ZGSWSKFJ_DM_MC
      FROM VW_G3_ZS_JKS
      WHERE 0 = 0
         AND (VW_G3_ZS_JKS.SJJE >= 1000 AND
            VW_G3_ZS_JKS.SKGK_DM_MC like '%销售%' AND
            VW_G3_ZS_JKS.RKRQ >= date
            '2017-01-01' AND VW_G3_ZS_JKS.RKRQ < date
            '2017-12-31' + 1 AND VW_G3_ZS_JKS.NSRZTMC = '正常' AND
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC in
            ('单位名称1',
               '单位名称2'，
                              '单位名称3'，
                              '单位名称4'，
                              '单位名称5'，
                              '单位名称6'，
                              '单位名称7'，
                              '单位名称8'，
                              '单位名称9'，
                              '单位名称10'，
               '单位名称11'))
      group by VW_G3_ZS_JKS.SHXYDM_NSRSBH,
               VW_G3_ZS_JKS.TAX_PAYER_CODE,
               VW_G3_ZS_JKS.NSRMC,
               VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC;
或
SELECT /*+ use_hash(VW_G3_ZS_JKS.G3_DM_GY_GK,VW_G3_ZS_JKS.G3_ZS_JKS) */ VW_G3_ZS_JKS.SHXYDM_NSRSBH SHXYDM_NSRSBH,
            VW_G3_ZS_JKS.TAX_PAYER_CODE TAX_PAYER_CODE,
            VW_G3_ZS_JKS.NSRMC NSRMC,
            sum(VW_G3_ZS_JKS.SJJE) SJJE,
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC ZGSWSKFJ_DM_MC
      FROM VW_G3_ZS_JKS
      WHERE 0 = 0
         AND (VW_G3_ZS_JKS.SJJE >= 1000 AND
            VW_G3_ZS_JKS.SKGK_DM_MC like '%销售%' AND
            VW_G3_ZS_JKS.RKRQ >= date
            '2017-01-01' AND VW_G3_ZS_JKS.RKRQ < date
            '2017-12-31' + 1 AND VW_G3_ZS_JKS.NSRZTMC = '正常' AND
            VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC in
            ('单位名称1',
               '单位名称2'，
                              '单位名称3'，
                              '单位名称4'，
                              '单位名称5'，
                              '单位名称6'，
                              '单位名称7'，
                              '单位名称8'，
                              '单位名称9'，
                              '单位名称10'，
               '单位名称11'))
      group by VW_G3_ZS_JKS.SHXYDM_NSRSBH,
               VW_G3_ZS_JKS.TAX_PAYER_CODE,
               VW_G3_ZS_JKS.NSRMC,
               VW_G3_ZS_JKS.ZGSWSKFJ_DM_MC;

总之，照着以上思路多试试吧。

laishiwan · 发表于 2018-1-18 12:12

1、感谢感谢感谢两位！！！
2、VW_G3_ZS_JKS视图的定义就是一个：760W的实体表跟10多个的维度表做左关联。G3_ZS_YJSF是其中一个维度表730W条记录。其它都是小表。而这次SQL语句是不用到       730W这个表的。视图内部的执行计划都是HJ连接方式了。
3、  用你【sqysl】提供的方法，不能修改为HJ连接。还是走NL 三种方法都尝试过了。
   /*+ use_hash(VW_G3_ZS_JKS.G3_DM_GY_GK,VW_G3_ZS_JKS.G3_ZS_JKS)
ps:你的“毛病”都是好毛病。说得多，对于我们学得更多。谢谢。

sqysl · 发表于 2018-1-18 12:29

laishiwan 发表于 2018-1-18 12:12
1、感谢感谢感谢两位！！！
2、VW_G3_ZS_JKS视图的定义就是一个：760W的实体表跟10多个的维度表做左关联。 ...

没帮到你，不好意思。
给视图起个别名，hint和查询中引用别名，再试试，现在外面。

laishiwan · 发表于 2018-1-18 14:54

laishiwan 发表于 2018-1-18 12:12
1、感谢感谢感谢两位！！！
2、VW_G3_ZS_JKS视图的定义就是一个：760W的实体表跟10多个的维度表做左关联。 ...

加hint后。HJ的Cost比NL代价还高。
NL COST 115524; HJ COST 144592
这能说明执行的时间会更长吗？
cost代价是与执行时间成正比吗？
正式环境又不能清空buffer cache吧。有缓存了，好像没有办法验证了。

sqysl · 发表于 2018-1-18 15:20

laishiwan 发表于 2018-1-18 14:54
加hint后。HJ的Cost比NL代价还高。
NL COST 115524; HJ COST 144592
这能说明执行的时间会更长吗？

不用看这个，跑跑试试。

sqysl · 发表于 2018-1-18 15:25

laishiwan 发表于 2018-1-18 14:54
加hint后。HJ的Cost比NL代价还高。
NL COST 115524; HJ COST 144592
这能说明执行的时间会更长吗？

不用清空啊，hash和nl都跑一下，不就比出来了？

piliskys · 发表于 2018-1-18 16:48

楼主你哪个公司的

，哈，金三业务我熟，感觉这视图就是一个笨重的东西，最好是用原始表，不要什么都图方便

[PL/SQL] sql还有优化的方法吗