首页 / ORACLE / oracle优化-leading提示和ordered提示以及materialize提示

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle优化-leading提示和ordered提示以及materialize提示，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13122字，纯文字阅读大概需要19分钟。

内容图文

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

select 2 a.* 3 from (select 4 ssi.ID, 5 ‘small_station_info‘ TB, 6 (select sbi.name 7 from scene_base_info sbi 8 where sbi.id = ssi.antenna_selection) as antenna_selection, 9 ssi.antenna_height, 10 ssi.down_angle, 11 ssi.azimuth_angle, 12 ssi.ITI_ID, 13 sa.longitude, 14 sa.latitude, 15 sa.attach_id 16 from consolidation_demand cd 17 left join demand_test_info dti 18 on cd.id = dti.cd_id 19 left join demand_plan_info dpi 20 on dti.id = dpi.tdl_id 21 left join building_plan_info bpi 22 on dpi.id = bpi.dpi_id 23 left join NEAR_FAR_PLACE_INFO nfpi 24 on bpi.id = nfpi.bpi_id 25 left join SMALL_STATION_INFO ssi 26 on nfpi.id = ssi.nfpi_id 27 left join site_attachment sa 28 on TO_NUMBER(sa.longitude) is not null 29 AND TO_NUMBER(sa.latitude) > 26.074423 30 AND TO_NUMBER(sa.latitude) < 26.077573 31 AND TO_NUMBER(sa.longitude) > 119.191148 32 AND TO_NUMBER(sa.longitude) < 119.197649 33 AND sa.attach_name = 34 substr(ssi.AZIMUTH_ANGLE_PHOTO, 35 instr(ssi.AZIMUTH_ANGLE_PHOTO, ‘/‘, -1) + 1, 36 length(ssi.AZIMUTH_ANGLE_PHOTO))) a 37 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):
---------------------------------------------------

   2 - access("SBI"."ID"=:B1)
   4 - filter("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOTO",‘
              /‘,-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
   5 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
   6 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
   7 - access("DPI"."ID"="BPI"."DPI_ID"(+))
   8 - access("DTI"."ID"="DPI"."TDL_ID"(+))
   9 - access("CD"."ID"="DTI"."CD_ID"(+))
  11 - filter("SA"."LONGITUDE" IS NOT NULL)
  12 - 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。笛卡尔乘积的排序合并连接，这个需要耗费很长时间。

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

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

使用leading提示

1 select /*+ no_merge(a) no_push_pred(a) */
2  a.*
3   from (select
4         /*+ leading(cd dti dpi bpi ssi) */
5          ...) a
6  where a.longitude is not null
7 /

执行计划

已选择 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):
---------------------------------------------------

   2 - access("SBI"."ID"=:B1)
   4 - access("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PH
              OTO",‘/‘,-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
   5 - filter("SA"."LONGITUDE" IS NOT NULL)
   6 - 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)
   7 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
   9 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
  11 - access("DPI"."ID"="BPI"."DPI_ID"(+))
  13 - access("DTI"."ID"="DPI"."TDL_ID"(+))
  15 - 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优化-leading提示和ordered提示以及materialize提示

标签：car inf cart att 结果 logs no_merge number photo

本文系统来源：http://www.cnblogs.com/lzfhope/p/7133678.html

内容总结

以上是互联网集市为您收集整理的oracle优化-leading提示和ordered提示以及materialize提示全部内容，希望文章能够帮你解决oracle优化-leading提示和ordered提示以及materialize提示所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/485845.html

来源：【匿名】

【上一篇】oracle10 【下一篇】Oracle Faq(Oracle TAF的配置)

更多 ►

【oracle优化-leading提示和ordered提示以及materialize提示】教程文章相关的互联网学习教程文章

Oracle 优化器【代码】【图】

什么是优化器　　优化器是Oracle中的一个核心模块，它的作用是为用户输入的SQL选择一个它计算出来的最高效的执行计划。SQL语句在Oracle中的执行过程如下图所示：基于规则优化器RBO　　基于规则的优化器现在基本上已经不怎么用了，这里只做简单介绍。　　基于规则的优化器是将一系列规则固定在系统中，给每个执行路径定一个等级。最低是1最高是15 。如：等级1对应的是：single row by rowid (通过rowid访问数据)，等级15则对应的是...

Oracle优化器之基数反馈（Cardinality Feedback）功能【代码】【图】

概述在Oracle 11gR2的版本上推出了基数反馈（Cardinality Feedback 以后简称CFB）功能，通过这个特性，对于某些查询在第一次执行时，如果CBO发现根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大的情况发生时，在SQL下次执行时，会根据实际值调整基数，重新生成执行计划。另外，基数反馈（CFB）在12c版本上得到更进一步的扩展改称为统计反馈（Statistics Feedback），成为12c自动重新优化（Automatic ...

oracle优化技巧及实例（总结）

1.关于exists和inin是循环的方式，在内存中处理，exists是执行数据库查询， select tpd.personaccountid,sum(nvl(tpd.CREDIT_SUM, 0)) as bjsr, sum(nvl(tpd.INTEREST_INCOME, 0)) as bjsr, sum(nvl(tpd.DEBIT_SUM, 0)) as bjzc from TGP_PERSON_DETAIL tpd left join TAP_FUNDBUSINESS tfb on tpd.FUNDBUSINESS_ID = tfb.FUNDBUSINESS_IDwhere tpd.PERSONACCOUNTID in (select personaccountid from TGP_PERSONACCOUNT pa left jo...

ORACLE优化器RBO与CBO介绍总结【代码】【图】

RBO和CBO的基本概念 Oracle数据库中的优化器又叫查询优化器（Query Optimizer）。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。Oracle的优化器有两种，基于规则的优化器（RBO）与基于代价的优化器（CBO) RBO: Rule-Based Optimization 基于规则的优化器 CBO: Cost-Based Optimization 基于代价的优化器RBO自ORACLE 6以来被采用，一直沿用至ORACLE 9i. ORACLE 10g开始，ORACLE已经彻底丢弃了RBO，...

oracle 优化or 更换in、exists、union all几个字眼，测试没有问题！【代码】

oracle 优化or 更换in、exists、union几个字眼。测试没有问题！根据实际情况选择相应的语句是。假设指数,or全表扫描，in 和not in 应慎用。否则会导致全表扫描。 select * from T_Pro_Product where bar_code = nnnmmm or name = nnnmmm or no = nnnmmm;select *from T_Pro_Productwhere nnnmmm in (bar_code, name, no)--忧化select *from T_Pro_Product t1where exists(select 1from T_Pro_Product tt1where t1....

oracle优化辅助SQL语句

--查询正在执行的SQL语句 SELECT OSUSER 电脑登录身份, PROGRAM 发起请求的程序, USERNAME 登录系统的用户名, SCHEMANAME, B.Cpu_Time 花费cpu的时间, STATUS, B.SQL_TEXT 执行的sql, B. FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY b.cpu_time DESC; --查询比较耗CPU的...

ORACLE - 最热教程

Oracle11g 创建数据库中问题处理（必须...Oracle 查看索引表空间 Navicat报错-ORA-28547：connection to...oracle 查看最大连接数 Oracle中合并数据集(多行变一行)Navicat如何添加oracle的oci.dll插件，...oracle如何将am,pm时间字符串改为时间格...for update造成的Oracle锁表与解锁 phppdooracle中文乱码的快速解决方法_P...Oracle分区表新增主分区及子分区语句

首页 / ORACLE / oracle优化-leading提示和ordered提示以及materialize提示

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

内容导读

内容图文

内容总结

内容备注

内容手机端

【oracle优化-leading提示和ordered提示以及materialize提示】教程文章相关的互联网学习教程文章

Oracle 优化器【代码】【图】

Oracle优化器之基数反馈（Cardinality Feedback）功能【代码】【图】

oracle优化技巧及实例（总结）

ORACLE优化器RBO与CBO介绍总结【代码】【图】

oracle 优化or 更换in、exists、union all几个字眼，测试没有问题！【代码】

oracle优化辅助SQL语句

Oracle优化器【代码】

【Oracle 优化器】自适应执行计划(Adaptive Execution Plans)【代码】

Oracle优化笔记

Oracle的优化【图】

Oracle优化器的基础知识(二)

Oracle 优化相关

oracle优化-leading提示和ordered提示以及materialize提示【代码】

Oracle优化学习

oracle 优化之组合索引【图】

ORACLE - 相关标签

ORACLE - 最新教程

ORACLE - 最热教程