SQL调优技术与实践

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函数表示满