安科网

Oracle优化-leading提示和ordered提示以及materialize提示

lmrdaydayup

lmrdaydayup

2017-08-03

关注 关注

以下内容适用于Oracle 10.2.0.5及其以上版本

一个查询很慢，原始SQL如下：

select
 a.*
  from (select       
        ssi.ID,
        'small_station_info' TB,
        (select sbi.name
            from scene_base_info sbi
          where sbi.id = ssi.antenna_selection) as antenna_selection,
        ssi.antenna_height,
        ssi.down_angle,
        ssi.azimuth_angle,
        ssi.ITI_ID,
        sa.longitude,
        sa.latitude,
        sa.attach_id
          from consolidation_demand cd
          left join demand_test_info dti
            on cd.id = dti.cd_id
          left join demand_plan_info dpi
            on dti.id = dpi.tdl_id
          left join building_plan_info bpi
            on dpi.id = bpi.dpi_id
          left join NEAR_FAR_PLACE_INFO nfpi
            on bpi.id = nfpi.bpi_id
          left join SMALL_STATION_INFO ssi
            on nfpi.id = ssi.nfpi_id
          left join site_attachment sa
            on TO_NUMBER(sa.longitude) is not null
          AND TO_NUMBER(sa.latitude) > 26.074423
          AND TO_NUMBER(sa.latitude) < 26.077573
          AND TO_NUMBER(sa.longitude) > 119.191148
          AND TO_NUMBER(sa.longitude) < 119.197649
          AND sa.attach_name =
              substr(ssi.AZIMUTH_ANGLE_PHOTO,
                      instr(ssi.AZIMUTH_ANGLE_PHOTO, '/', -1) + 1,
                      length(ssi.AZIMUTH_ANGLE_PHOTO))) a
 where a.longitude is not null

表都不大，执行计划如下：

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 1917963167

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                        |    1 |  253 |  519  (2)| 00:00:07 |
|  1 |  TABLE ACCESS BY INDEX ROWID        | SCENE_BASE_INFO        |    1 |    14 |    1  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN                | SCENE_BASE_INFO_PK      |    1 |      |    0  (0)| 00:00:01 |
|  3 |  VIEW                              |                        |    1 |  253 |  519  (2)| 00:00:07 |
|*  4 |  FILTER                            |                        |      |      |            |      |
|*  5 |    HASH JOIN OUTER                  |                        |    1 |  251 |  519  (2)| 00:00:07 |
|*  6 |    HASH JOIN OUTER                |                        |    83 |  8134 |  505  (1)| 00:00:07 |
|*  7 |      HASH JOIN OUTER                |                        |    83 |  7304 |  501  (1)| 00:00:07 |
|*  8 |      HASH JOIN OUTER              |                        |    83 |  6391 |  493  (1)| 00:00:06 |
|*  9 |        HASH JOIN OUTER              |                        |    83 |  5478 |  271  (1)| 00:00:04 |
|  10 |        MERGE JOIN CARTESIAN        |                        |    36 |  2052 |    21  (0)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT        |    1 |    53 |    16  (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD |    1 |      |    15  (0)| 00:00:01 |
|  13 |          BUFFER SORT                |                        |  6725 | 26900 |    5  (0)| 00:00:01 |
|  14 |          INDEX FAST FULL SCAN      | PK_CONSOLIDATION_DEMAND |  6725 | 26900 |    5  (0)| 00:00:01 |
|  15 |        TABLE ACCESS FULL          | DEMAND_TEST_INFO        | 15459 |  135K|  249  (1)| 00:00:03 |
|  16 |        TABLE ACCESS FULL            | DEMAND_PLAN_INFO        |  8787 | 96657 |  221  (1)| 00:00:03 |
|  17 |      TABLE ACCESS FULL            | BUILDING_PLAN_INFO      |  3244 | 35684 |    8  (0)| 00:00:01 |
|  18 |      TABLE ACCESS FULL              | NEAR_FAR_PLACE_INFO    |  389 |  3890 |    3  (0)| 00:00:01 |
|  19 |    TABLE ACCESS FULL              | SMALL_STATION_INFO      |  594 | 90882 |    13  (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- access("SBI"."ID"=:B1)
- filter("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOTO",'
              /',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
- access("NFPI"."ID"="SSI"."NFPI_ID"(+))
- access("BPI"."ID"="NFPI"."BPI_ID"(+))
- access("DPI"."ID"="BPI"."DPI_ID"(+))
- access("DTI"."ID"="DPI"."TDL_ID"(+))
- access("CD"."ID"="DTI"."CD_ID"(+))
- filter("SA"."LONGITUDE" IS NOT NULL)
- access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
              TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
      filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
              TO_NUMBER("LATITUDE")>26.074423)

这个执行计划，看起来无比正常，应该要left join的都有。

但问题的关键在于10 步骤-- MERGE JOIN CARTESIAN。笛卡尔乘积的排序合并连接，这个需要耗费很长时间。

等待这个结果要耗费几十秒，甚至要更久！
如何解决这样的问题，有以下几个方法：

  1. 重新收集每个表的统计数据--这个没有实验过，但即使那么做，可能也无效。不过从本例看，很有可能是这个导致的。
  2. 启用leading提示，结合其它提示
  3. 使用materialize提示

使用leading提示

select /*+ no_merge(a) no_push_pred(a) */
 a.*
  from (select
        /*+ leading(cd dti dpi bpi ssi) */
        ...) a
 where a.longitude is not null
/

执行计划

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 1844304918

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                        |    1 |  253 |  520  (2)| 00:00:07 |
|  1 |  TABLE ACCESS BY INDEX ROWID  | SCENE_BASE_INFO        |    1 |    14 |    1  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN          | SCENE_BASE_INFO_PK      |    1 |      |    0  (0)| 00:00:01 |
|  3 |  VIEW                        |                        |    1 |  253 |  520  (2)| 00:00:07 |
|*  4 |  HASH JOIN                  |                        |    1 |  251 |  520  (2)| 00:00:07 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT        |    1 |    53 |    16  (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD |    1 |      |    15  (0)| 00:00:01 |
|*  7 |    HASH JOIN RIGHT OUTER      |                        | 23606 |  4564K|  503  (2)| 00:00:07 |
|  8 |    TABLE ACCESS FULL        | SMALL_STATION_INFO      |  594 | 90882 |    13  (0)| 00:00:01 |
|*  9 |    HASH JOIN RIGHT OUTER    |                        | 15459 |  679K|  490  (2)| 00:00:06 |
|  10 |      TABLE ACCESS FULL        | NEAR_FAR_PLACE_INFO    |  389 |  3890 |    3  (0)| 00:00:01 |
|* 11 |      HASH JOIN RIGHT OUTER    |                        | 15459 |  528K|  486  (2)| 00:00:06 |
|  12 |      TABLE ACCESS FULL      | BUILDING_PLAN_INFO      |  3244 | 35684 |    8  (0)| 00:00:01 |
|* 13 |      HASH JOIN RIGHT OUTER  |                        | 15459 |  362K|  477  (1)| 00:00:06 |
|  14 |        TABLE ACCESS FULL      | DEMAND_PLAN_INFO        |  8787 | 96657 |  221  (1)| 00:00:03 |
|* 15 |        HASH JOIN OUTER        |                        | 15459 |  196K|  255  (1)| 00:00:04 |
|  16 |        INDEX FAST FULL SCAN  | PK_CONSOLIDATION_DEMAND |  6725 | 26900 |    5  (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL    | DEMAND_TEST_INFO        | 15459 |  135K|  249  (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- access("SBI"."ID"=:B1)
- access("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PH
              OTO",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
- filter("SA"."LONGITUDE" IS NOT NULL)
- access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
              TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
      filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
              TO_NUMBER("LATITUDE")>26.074423)
- access("NFPI"."ID"="SSI"."NFPI_ID"(+))
- access("BPI"."ID"="NFPI"."BPI_ID"(+))
- access("DPI"."ID"="BPI"."DPI_ID"(+))
- access("DTI"."ID"="DPI"."TDL_ID"(+))
- access("CD"."ID"="DTI"."CD_ID"(+))

没有笛卡尔的merge join .步骤4还是一个hash join 。
执行很快，大概可以0.17秒

使用materialize提示

1  WITH A AS
2   (select /*+MATERIALIZE */
3     .....)
4  select a.* from A WHERE a.longitude is not null

执行计划

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 3536941173

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             | 23606 |  5832K|   700   (2)| 00:00:09 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               |                             |       |       |            |       |
|*  3 |    HASH JOIN RIGHT OUTER       |                             | 23606 |  5786K|   520   (2)| 00:00:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT             |     1 |    53 |    16   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD     |     1 |       |    15   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER      |                             | 23606 |  4564K|   503   (2)| 00:00:07 |
|   7 |      TABLE ACCESS FULL         | SMALL_STATION_INFO          |   594 | 90882 |    13   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER     |                             | 15459 |   679K|   490   (2)| 00:00:06 |
|   9 |       TABLE ACCESS FULL        | NEAR_FAR_PLACE_INFO         |   389 |  3890 |     3   (0)| 00:00:01 |
|* 10 |       HASH JOIN RIGHT OUTER    |                             | 15459 |   528K|   486   (2)| 00:00:06 |
|  11 |        TABLE ACCESS FULL       | BUILDING_PLAN_INFO          |  3244 | 35684 |     8   (0)| 00:00:01 |
|* 12 |        HASH JOIN RIGHT OUTER   |                             | 15459 |   362K|   477   (1)| 00:00:06 |
|  13 |         TABLE ACCESS FULL      | DEMAND_PLAN_INFO            |  8787 | 96657 |   221   (1)| 00:00:03 |
|* 14 |         HASH JOIN OUTER        |                             | 15459 |   196K|   255   (1)| 00:00:04 |
|  15 |          INDEX FAST FULL SCAN  | PK_CONSOLIDATION_DEMAND     |  6725 | 26900 |     5   (0)| 00:00:01 |
|  16 |          TABLE ACCESS FULL     | DEMAND_TEST_INFO            | 15459 |   135K|   249   (1)| 00:00:03 |
|* 17 |   VIEW                         |                             | 23606 |  5832K|   180   (2)| 00:00:03 |
|  18 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D68A2_721EF047 | 23606 |  4103K|   180   (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------

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

   3 - access("SA"."ATTACH_NAME"(+)=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOT
              O",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
   5 - access(TO_NUMBER("LONGITUDE"(+))>119.191148 AND TO_NUMBER("LATITUDE"(+))>26.074423 AND
              TO_NUMBER("LONGITUDE"(+))<119.197649 AND TO_NUMBER("LATITUDE"(+))<26.077573)
       filter(TO_NUMBER("LONGITUDE"(+)) IS NOT NULL AND TO_NUMBER("LATITUDE"(+))<26.077573 AND
              TO_NUMBER("LATITUDE"(+))>26.074423)
   6 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
   8 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
  10 - access("DPI"."ID"="BPI"."DPI_ID"(+))
  12 - access("DTI"."ID"="DPI"."TDL_ID"(+))
  14 - access("CD"."ID"="DTI"."CD_ID"(+))
  17 - filter("A"."LONGITUDE" IS NOT NULL)

也很快，大约0.19~0.2左右。
之所以慢，主要是因为要先生成gt表 SYS_TEMP_0FD9D68A2_721EF047。

总结

1.最好先收集统计数据

2.在收集统计数据无效的情况下，考虑使用leading提示，其次materialize提示也会破坏oracle优化器一些自以为明智的计划（优化器的不足，oracle已经提到了，这就是hint的由来)

3.dba要优化一个库，不是一个很容易的事情，需要做很多工作。

oracle执行计划 oracle

lmrdaydayup

lmrdaydayup

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

关于ORACLE索引的几种扫描方式

一条sql执行的效率因执行计划的差异而影响，经常说这条sql走索引了，那条sql 全表扫了。索引是怎么走的呢，说说我了解到的几种索引走的方式。具体来说，仅当查询谓词使用等号运算符引用唯一索引键中的所有列时，数据库才执行唯一扫描.数据库从索引条目中获取行标识

娜娜 2020-07-18

疑问？ Oracle普通的B树索引是升序还是降序？

Oracle普通的B树索引是升序还是降序？SQL> create table a as select * from dba_objects;SQL> select object_name from a where object_id in or

talkingDB 2020-07-05

oracle执行计划打印

下班逗猫闲得无聊，写写自己对几种oracle执行计划打印的认识。废话少说开始吧方式一：explain plan for使用explain plan for，这种查询方式适合查看单条执行SQL的执行计划。explain plan for的查看方式也有两种：1

zhangchaoming 2020-06-21

Oracle绑定变量优缺点

因为oracle对于sql语句的首先的解析逻辑，会先匹配是否可以软解析，不行则会进行硬解析，相对而言 硬解析比较麻烦。select * from table where id = ‘1‘ 和select * from table where id = ‘2

zhangchaoming 2020-05-27

Oracle里的执行计划

第二章：Oracle里的执行计划2.1 什么是执行计划Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。可以看到Oracle在执行目标SQL时所用的内部执行步骤，这些步骤的执行顺序，所对应的的谓词信息、列信息，优化器评估出来执行这些步骤后

LuoXinLoves 2020-05-04

面试篇三：数据库MySQL、Oracle

SELECT * FROM admin_company ORDER BY id LIMIT 0,10;limit后参数代表什么意思？LIMIT[位置偏移量,]行数，第一个参数是从哪个序号加上，初始是0，第二个参数是查询多少行记录。MySQL在创建表时就会创

康慧欣 2020-05-02

[ORACLE]自适应游标共享Adaptive Cursor Sharing

v$SQL_CS_SELECTIVITY --查看SQL游标的各个变量选择范围,它包含着各个条件谓词,绑定变量值以及它的最大值和最小值等信息。select IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE,SQL_T

Justdoit00 2020-04-26

oracle数据库应用开发经验

一般应用都会有一些表用来记录用户操作日志，数据变更记录，交易流水等日志型的库表。这些表最好按时间字段做分区，这样在迁移或者清理历史记录时会比较方便，借助oracle的分区交换清理特性，效率比delete高很多。Oracle在创建序列可以指定cache参数，

oraclemch 2019-12-29

Oracle调优之no_unnest和unnest用法简介

本博客介绍Oracle SQL调优的一种常用也是很实用的方法，也即/*+no_unnest */和/*+ unnest*/，介绍Oracle 的 /*+unnest */ 、 /*+ no_unnest */之前，先介绍一下Hint。Hint对于开发人员来

talkingDB 2020-01-11

Oracle查看SQL执行计划1--explain plan

SQL> set linesize 800SQL> select * from table;Oracle10g\11g中，如果我们对目标SQL执行explain plan命令，则oracle就将解析目标SQL所产生的执行计划的具体执行步骤写入P

Streamoutput 2020-01-10

数据库-Oracle通过执行计划查看查询语句是否使用索引【转】

select * from t_call_records where t_bjhm=‘123456‘。如上图所示，index range scan为索引范围扫描;索引范围扫描，当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWE

Streamoutput 2019-12-02

Oracle Proc编程性能优化经验

Proc 是Oracle提供的一种数据库操作的AP。它是基于ESql技术的，需要预编译后才可以变成普通c代码，非常不直观，使用起来不太方便，阅读也存在困难。因为这些问题导致程序员平时开发中会出现一些Proc操作存在效率低下的情况，本文介绍一些Proc一些编

oraclemch 2019-12-01

记一次Oracle数据库实验--索引的常见执行计划

INDEX FULL SCAN：索引的全扫描，单块读，有序INDEX RANGE SCAN：索引的范围扫描INDEX FAST FULL SCAN：索引的快速全扫描，多块读，无序INDEX FULL SCAN：针对MAX(),MIN()函数的查询INDEX

der 2019-11-27

Oracle笔记

utm_source=blogxgwz14

zgxzowen 2015-07-01

分享一份Oracle官方文档学习路线图

概述今天主要分享下Oracle数据库的官方文档学习路线图。重点如下：1、sql、pl/sql. 作为oracle的基本功，需要大家对sql和plsql非常的熟悉。特别是sql里面的多表连接、子查询、各种新版本的函数，以及plsql里面的所有语法。不要认为这

jbossllx 2019-11-05

SQL Server,MySQL,Oracle三者的区别

完全支持所有的工业标准。对开发商全力支持，Oracle并行服务器通过使一组结点共享同一簇中的工作来扩展Windows NT的能力，提供高可用性和高伸缩性的簇的解决方案。Oracle的并行服务器对各种UNIX平台的集群机制都有着相当高的集成度。　Oracle

静思苑 2019-01-15

Oracle中游标Cursor使用实例

Oracle数据库中的cursor分为2中类型：shared cursor，session cursor. 会缓存存储目标sql的sql文本、解析树、该sql所涉及的对象定义、该sql所使用的绑定变量类型和长度，以及改sql的执行计划等信息。分别在V$SQ

人勤阡陌绿 2019-03-27

Oracle SQL性能分析之10053事件

优化器生成正确执行计划的前提条件是要有正确的统计信息，不准确的统计信息往往会导致错误的执行计划。当通过SQL和基数推断出的执行计划和实际执行计划不同时，就可以借助10053事件。10053事件是用来诊断优化器如何估算成本和选择执行计划的，用它产生的trac

Cavlier 2019-03-19

Oracle高并发系列1：DML引起的常见问题及优化思路

王鹏冲，平安科技数据库技术专家，浸淫数据库行业十多年，对Oracle数据库有浓厚兴趣，也对MySQL、MongoDB、Redis等数据库有一定架构和运维经验，目前正沉迷在PostgreSQL数据库与Oracle数据库的PK之中，重点在关系型数据库的分布式架

EricRay 2017-05-26

oracle--如何分析执行计划

当这个操作完成之后，产生的rowsource中的数据被送往下一步骤进行处理，在此例中，SELECTSTATEMENT操作是这个查询语句的最后一步。决定该语句使用何种优化器的唯一方法是看后面的cost部分。例如，如果给出的是下面的形式，则表明使用的是CBO优

javamao00 2011-08-15
lmrdaydayup

lmrdaydayup

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号