SQL优化一例：巧用hint
来源：互联网 发布：oracle数据字典 报错 编辑：程序博客网 时间：2024/06/17 13:39
Oracle的hint为DBA必须掌握的一项技能，下文中记录了用hint来解决的一个sql优化案例。
问题描述：
从10gR2迁移到11gR2后，有条SQL运行变的很慢，大概要30s左右。
select prd.delevery_order_id, prd.sku_desc, prd.item_id, prd.product_name, prd.product_num, prd.sal_price, dlo.order_status, to_char(dlo.order_create_time, 'yyyy-MM-dd hh24:mi:ss') as order_create_time, dlo.order_substatus, prd.storage_name, prd.attributes, dlo.trans_usr_name, nvl(to_char(dlo.buyer_pay_time, 'yyyy-MM-dd hh24:mi:ss'), '') as buyer_pay_time, nvl(to_char(dlo.seller_ship_time, 'yyyy-MM-dd hh24:mi:ss'), '') as seller_ship_time, nvl(to_char(dlo.complete_time, 'yyyy-MM-dd hh24:mi:ss'), '') as complete_time, dlo.order_type, co.invoice_required, co.invoice_content, co.invoice_title, co.invoice_address from checkout_order_prd prd, delevery_order dlo, ctoc_order co where prd.delevery_order_id = dlo.delevery_order_id and dlo.esc_orderid = co.esc_orderid(+) and (not (dlo.order_status = 9 and dlo.cancel_type not in (904, 906, 910, 911, 912, 913))) and prd.merchant_onlyid = 2 and dlo.order_status in (4,5,6,7,8) order by dlo.buyer_pay_time desc nulls last, prd.delevery_order_id desc, prd.item_id desc;
查找原因：
查看11g中的执行计划：
执行计划----------------------------------------------------------Plan hash value: 903664453------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 41040 | 9137K| | 158K (3)| 00:31:39 || 1 | SORT ORDER BY | | 41040 | 9137K| 9960K| 158K (3)| 00:31:39 ||* 2 | HASH JOIN OUTER | | 41040 | 9137K| 8584K| 156K (3)| 00:31:15 ||* 3 | HASH JOIN | | 41040 | 8095K| 5416K| 100K (2)| 00:20:03 || 4 | TABLE ACCESS BY INDEX ROWID| CHECKOUT_ORDER_PRD | 41040 | 4929K| | 22363 (1)| 00:04:29 ||* 5 | INDEX RANGE SCAN | IDX_COP_MERCHANT_ONLYID | 41040 | | | 97 (2)| 00:00:02 ||* 6 | TABLE ACCESS FULL | DELEVERY_ORDER | 2327K| 175M| | 67511 (3)| 00:13:31 || 7 | TABLE ACCESS FULL | CTOC_ORDER | 3128K| 77M| | 49872 (4)| 00:09:59 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("DLO"."ESC_ORDERID"="CO"."ESC_ORDERID"(+)) 3 - access("PRD"."DELEVERY_ORDER_ID"="DLO"."DELEVERY_ORDER_ID") 5 - access("PRD"."MERCHANT_ONLYID"=2) 6 - filter(("DLO"."ORDER_STATUS"=4 OR "DLO"."ORDER_STATUS"=5 OR "DLO"."ORDER_STATUS"=6 OR "DLO"."ORDER_STATUS"=7 OR "DLO"."ORDER_STATUS"=8) AND ("DLO"."ORDER_STATUS"<>9 OR ("DLO"."CANCEL_TYPE"=904 OR "DLO"."CANCEL_TYPE"=906 OR "DLO"."CANCEL_TYPE"=910 OR "DLO"."CANCEL_TYPE"=911 OR "DLO"."CANCEL_TYPE"=912 OR "DLO"."CANCEL_TYPE"=913)))表的连接方式为PRD和DLO先进行hash join，然后得到的结果集再和CO进行hash join。三张表都较大，导致运行时间相当慢。解决问题：
尝试改用NESTED LOOPS来进行连接各张表。在sql中加入hint：/*+use_nl (prd,dlo,co)*/
查看新的执行计划：
执行计划----------------------------------------------------------Plan hash value: 1786358075------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 41040 | 9137K| | 217K (1)| 00:43:32 || 1 | SORT ORDER BY | | 41040 | 9137K| 9960K| 217K (1)| 00:43:32 || 2 | NESTED LOOPS OUTER | | 41040 | 9137K| | 215K (1)| 00:43:08 || 3 | NESTED LOOPS | | 41040 | 8095K| | 104K (1)| 00:20:55 || 4 | TABLE ACCESS BY INDEX ROWID| CHECKOUT_ORDER_PRD | 41040 | 4929K| | 22363 (1)| 00:04:29 ||* 5 | INDEX RANGE SCAN | IDX_COP_MERCHANT_ONLYID | 41040 | | | 97 (2)| 00:00:02 ||* 6 | TABLE ACCESS BY INDEX ROWID| DELEVERY_ORDER | 1 | 79 | | 2 (0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | PK_DELEVERY_ORDER | 1 | | | 1 (0)| 00:00:01 || 8 | TABLE ACCESS BY INDEX ROWID | CTOC_ORDER | 1 | 26 | | 3 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_CO_ESC_ORDERID1 | 1 | | | 2 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("PRD"."MERCHANT_ONLYID"=2) 6 - filter(("DLO"."ORDER_STATUS"=4 OR "DLO"."ORDER_STATUS"=5 OR "DLO"."ORDER_STATUS"=6 OR "DLO"."ORDER_STATUS"=7 OR "DLO"."ORDER_STATUS"=8) AND ("DLO"."ORDER_STATUS"<>9 OR ("DLO"."CANCEL_TYPE"=904 OR "DLO"."CANCEL_TYPE"=906 OR "DLO"."CANCEL_TYPE"=910 OR "DLO"."CANCEL_TYPE"=911 OR "DLO"."CANCEL_TYPE"=912 OR "DLO"."CANCEL_TYPE"=913))) 7 - access("PRD"."DELEVERY_ORDER_ID"="DLO"."DELEVERY_ORDER_ID") 9 - access("DLO"."ESC_ORDERID"="CO"."ESC_ORDERID"(+))从执行计划上来看，该SQL的Cost和Time都要比原SQL要高，但是实际运行的效果却截然不同：已选择628行。已用时间: 00: 00: 01.59实际运行时间为2s,比优化前时间缩短90%以上。
小结：
1、从10g迁移到11g，可能会造成有些SQL运行变慢的情况，怀疑是统计信息不准所致，建议重新收集统计信息。
2、执行计划并不是最准确的，可能跟实际的运行情况相差很远。
3、当CBO不能正确选择Access Paths和Join Type等时，需要用hint来进行强制指定。
- SQL优化一例：巧用hint
- 优化SQL HINT使用
- SQL优化 - hint - driving_site
- NO_EXPAND Hint性能优化一例
- ORACLE hint提示优化SQL
- sql优化---oracle hint样例
- oracle hint提示优化SQL
- ORACLE hint提示优化SQL
- SQL优化专题：Oracle HINT
- sql优化-hint的作用
- 使用unnest Hint优化SQL
- sql优化之hint运用
- sql优化之hint运用
- 使用hint优化 Oracle SQL语句方法30例
- Oracle常用SQL优化Hint语句
- 使用hint优化 Oracle SQL语句方法
- 性能优化：Sql语句中HINT不起作用
- 常见Oracle HINT的用法 SQL优化
- vc odbc
- 在Excel中利用VBA编写词组合，可用于起名或推广关键词
- 于IaaS的四个误解和四个猜想
- Android四大组件
- .net中进行时间对比
- SQL优化一例：巧用hint
- Jrebel 的配置及使用（实现WEB应用的热部署）
- Java经典算法集
- Ucenter的用户注册和登录分析
- Eclipse快捷键大全
- 数据库概论（实验三）MYSQL监视器
- linux设备驱动归纳总结（三）：2.字符型设备的操作open、close、read、write
- 在虚拟机中不能安装64位系统的问题
- 视频分析工具
