SQL调优技术与实践_第1页
SQL调优技术与实践_第2页
SQL调优技术与实践_第3页
SQL调优技术与实践_第4页
SQL调优技术与实践_第5页
已阅读5页,还剩19页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、SQL调优技术与实践技术创新,变革未来1、资源请求的最化2、平衡资源请求3、并执SQL优化法资源请求的最化选择执成本最低的执计划:1、预知执成本最低的执计划业务简单的SQL是可以预知的,但是业务复杂的SQL则较难预知2、执计划中的瓶颈步骤3、优化执计划中的瓶颈表、索引规划统计信息HINT、PROFILE、BASELINESQL等价改写优化器的查询转换执计划中的瓶颈步骤原态的执计划SQL Turning advisor等Oracle内置的SQL调优具Hint gather_plan_statisticsSQL Monitorv$active_session_history、dba_hist_ac

2、tive_sess_history(字段 sql_plan_line_id)SQLHC、SQLT案例调优实践-表、索引规划SELECT A.ATTRVALUE, B.TYPENAME FROM SUBS_MEMBER A,RES_TYPE BWHERE A.ATTRIDAND A.REGION AND B.ITEMID AND ROWNUM = res_type= :B1= A.ATTRVALUE 1SQLINFO信息:create or replace view SUBS_MEMBER asselect BE_ID REGION,CASEWHEN p_prop_inst_id is notn

3、ull then| EX_FIELD3 PROP_CODEPROP_CODE | _else end ATTRID,PROP_VALUE ATTRVALUE 。SQL平均执200多万逻辑读,需要300多秒,返回fr0om数G据ROUP_MVEiMeBwERR_ESP_;T(YAP表E数据有5亿多)select res_type_iditemid,。from restype (B表数据6万多)SQL语句总的Cost成本计算25, 全表扫描成本7,资源消耗却 较。优化器评估的cost为何这么低执计划的count stopkey1、在包含rownum的SQL语句中如果不为rownum伪列指定限定条件

4、,则优化器会使count操 作。2、如果对rownum伪列应个限定条件(如where rownum=1),则优化器会使countstopkey操作,这个表示优化器旦找到满条件的数据达到了rownum限制后,则会停止继续查询数据,这也就是常说的分查询。(CASEWHEN p_prop_inst_id is not null then_ | EX_FIELD3 PROP_CODEPROP_CODE |elseend) ATTRID Where ATTRID= res_type优化器认为只需要全表扫描GROUP_MEMBER_P相关单分区段的4340 数据就可满整个SQL的where条件。SQL M

5、onitor:1、如果串执的SQL,消耗的CPU时间或IO时间超过5秒,那么这些SQL 将会被监控到2、并执的SQL将全部被监控到3、增加HINT /*+ monitor */的SQL会立即开启SQL MONITORING功能。SQL Monitor执计划消耗资源的步骤1、 RESOURCE_TYPE B视图对应表做驱动表(B表没有别的where条件返回6万多),利关联条件 B.ITEMID = A.ATTRVALUE,ATTRVALUE对应的PROP_VALUE字段selectivy较2、GROUP_MEMBER_P A视图对应表做驱动表,优化全表扫描即可,条件WHERE A.ATTRID

6、= res_typeCASEWHEN p_prop_inst_id is not null thenPROP_CODE | _ | end ATTRIDelsePROP_CODEwhen函数索引EX_FIELD3创建复杂的case create indexind_casewhen_col on GROUP_MEMBER_P(CASE WHEN p_prop_inst_id is not null then PROP_CODE | _ | EX_FIELD3 else PROP_CODE end) local;1、了解到Counter Stopkey、分的原理和对COST成本估算的影响2、复杂的

7、case when函数也是可以创建索引 的案例总结案例 调优实践-子查询01and serv_code not inin (001102, 001103) andselect * from exd_base_sm where business_type = 3 (select serv_code from exd_prov_def where serv_type sp_attr = Lunion allnot inselect serv_code from exd_sp_def where 000005, 000401,001104, 001122,(select serv_code from

8、 exd_special_sm)serv_type in (001102, 001103,001126, 001322) and serv_code and serv_code 10658112)SQLINFO信息:SQL单次执需要1100多万逻辑读,执时间需要150多秒,返回10000多数据Cost成本只有 656,消耗却 是上千万的逻 辑读?执计划中包含了三个全表扫描,但是这些表都是表,逻辑读是怎么产的? 两个FILTER执计划:1、执计划Id 5的FILTER表示的只是结果校验过滤部分。2、执计划Id 1的FILTER表示的是子查询需要被反复执多次,然后进NOT EXISTS对案例调优实

9、践-子查询01查询转换-子查询展开子查询展开(subquery unnesting)是优化器不再将子查询作为独立的处理单元来单独执,是将子查询转换为它身和外部查询之间等价的表连接。子查询展开后,优化器可以选择将子查询的表和主查询的表按照Nested loops、Hash join、Mergejoin等执计划进表关联。子查询展开所对应的表连接必须满转换后SQL语句业务逻辑和原SQL业务逻辑致。子查询没有展开,出现filter执计划:NOT IN类型的子查询NOT IN类型的子查询在Oracle子查询和主查询的关联列没有NULL11GR2之前,子查询展开是需要定前提,必须 值。NULL-Aware

10、 Anti JoinOracle在11GR2推出了NULL-Aware Anti Join,对于关联列没有not null 约束的not in子查询,优化器依然能够将该子查询展开,HASH JOIN ANTI NA执计划,该特性由_optimizer_null_aware_antijoin隐含参数控制,默认为TRUE开启该特性。(该特性在Oracle 11GR2存在BUG,所以有些客户会选择将该特性关闭掉)1、添加notnull约束或者SQL条件(需要业务核实)exp_sp_def表已经有 serv_code not in () and serv_codevalue的条件,由于NULL做等值等

11、运算永 远不成立,所以已经限制了serv_code不能为null2、临时对该SQL启NULL-AwareAnti Join特性-hint/*+OPT_PARAM(优化器参数值)*/OPT_PARAM(_optimizer_ null_aware_antijointrue) 表示将该优化器参数置为true,可以通过上 述hint开启NULL-Aware Anti Join特性,绑定 SQL PROFILE3、将not in改写为not exists(需要特别注意的是not in和not exists并不是完成等价的)Not in和Not exists只有在关联 列上都没有NULL值时业务逻辑 才

12、是等价的。1、查询转换功能子查询展开的含义2、NOT IN的子查询展开是需要NULL-Aware Anti Join特性3、OPT_PARAM hint是可以手动为部分SQL 制定优化器环境 4、NOT IN和NOT EXISTS业务逻辑并不等价案例总结案例调优实践-子查询02SELECT WWO.IDFROM WORK_ORDER WWOWHERE WWO.BASE_ORDER_ID = :1 OR WWO.BASE_ORDER_ID IN(SELECT OO.ID FROM ORDER OO WHERE OO.PARENT_ORD_ID = :2)ORDER BY WWO.CREATE_D

13、ATE DESC;全表扫描问题 WORK_ORDER全表扫描,该表是个表,BASE_ORDER_ID字段 也有索引,WWO.BASE_ORDER_ID = :1条件没?FILTER执计划filter和sort order by执计划后,COST就从2377K转换为4367K案例调优实践-子查询021、从Global Stats信息得知:SQL单次执需要8G的逻辑读,还未执完就报出ORA-01555错误。2、Filter执计划出现:子查询没有展开案例调优实践-子查询02ORDER表已经访问了187M次,索引范围扫描2G次子查询展开的hint子查询展开的HINT:/*+unnest*/OO.ID1

14、、UNNEST hint 子查询强制展开2、NO_UNNEST hint 子查询强制不展开SELECT WWO.IDFROM WORK_ORDER WWOWHERE WWO.BASE_ORDER_ID = :1OR WWO.BASE_ORDER_ID IN (SELECTFROM ORDER OOWHERE OO.PARENT_ORD_ID = :2) ORDER BY WWO.CREATE_DATE DESC;强制unnest hint执计划 没有改变,执计划未发变化,子查询依然法展开Select * from (SELECT ID FROM WORK_ORDER WWO WHERE WWO.BASE_ORDER_ID = :1union allSELECT ID FROM WORK_ORDER WWOWHERE WWO.BASE_ORDER_ID IN (SELECT OO.ID FROM ORDER OOWHEREand lnnvl(WWO.BASE_ORDER_ID in (:1) order by create_date desc;Lnnvl函数表示满

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论