无语的index hint：手工分配哈希区，5小时不出结果，优化后20分钟
来源：互联网 发布：京东秒杀软件有用吗 编辑：程序博客网 时间：2024/04/30 03:59
同事说,有个语句5个小时不出结果，叫我帮忙看看，于是叫同事发过来。不看不知道，一看吓一跳，3个表关联，强制使用了2个index hint，其中一个表9g，一个表67g，还有一个小表40Mb。开发人员，总以为走index就是快的，所以使用了index hint，最终导致走得非常慢。 下面是同事发来的语句： select /*+ parallel(t,4) index(a,IDX_COMMBASUBSHIST_1) index(b,IDX_COMMCMSERVHIST_1)*/ 1, t.DISC_ID, t.DISC_LEV, to_date(20140117082042, 'yyyymmddhh24miss'), t.MSINFO_ID, t.ORG_ID, t.SERV_ID, t.SUBS_ID, t.OBJ_GRP_ID, a.SUBS_CODE, a.SUBS_STAT, a.SUBS_STAT_REASON, a.SUBS_STAT_DATE, a.ACTION_ID, a.ACTION_TYPE, a.ACTION_EX_TYPE, a.ACT_DATE, a.REQ_ID, a.STAFF_ID, a.CMMS_CUST_CODE, a.SPEED_VALUE, b.ACC_NBR, b.CUST_ID, b.SERV_NBR, b.CONSUME_GRADE, b.SERV_LEV, b.ACCOUNT_NBR, b.CITY_VILLAGE_ID, b.SERV_CHANNEL_ID, b.SERV_STAT_ID, b.CUST_CLASS_DL, b.CUST_TYPE_ID, b.USER_TYPE, b.USER_CHAR, b.PAYMENT_TYPE, b.BILLING_TYPE, b.PROD_ID, b.PROD_CAT_ID, b.EXCHANGE_ID, b.SERV_COL1, b.SERV_COL2, b.AREA_ID, b.SUBST_ID, b.BRANCH_ID, b.STOP_TYPE, b.CUST_MANAGER_ID, b.CREATE_DATE, b.ADDRESS_ID, b.SUBS_DATE, b.OPEN_DATE, b.MODI_STAFF_ID, b.CMMS_CUST_ID, b.CUST_NAME, b.SALES_ID, b.SALES_TYPE_ID, b.SERV_ADDR_ID, t.HIST_CREATE_DATE, b.ARREAR_MONTH, b.ARREAR_MONTH_LAST, t.SALESTAFF_ID, t.EHOME_TYPE, t.EHOME_CLASS, b.strat_grp_dl, b.sale_org1, b.sale_org2, b.sale_org3, b.location_type, b.region_flag, b.terminal_id, b.pstn_id, b.fee_id, b.payment_id, b.billing_id, b.strat_grp_xl, b.fld1, b.fld3, b.cust_level, b.group_cust_type, b.cust_region, b.group_cust_grade, b.control_level, b.net_connect_type, b.trade_type_id, b.acc_nbr2, b.cdma_class_id, b.phone_number_id, b.develop_channel, b.online_time, t.wireless_type, b.new_serv_stat_id, b.is_phs_tk, b.serv_grp_type, b.state, t.cdma_disc_type, b.mix_disc, b.is_3g, t.add_disc_type, to_number(nvl(b.business_type, '-1')), nvl(t.label_num, -1), b.is_mix_prod, t.price_id, t.disc_item_id, b.STD_SUBST_ID, b.STD_BRANCH_ID, t.DISC_ITEM_ID_OP, t.PRICE_ID_OP, t.business_type, b.new_prod_id, b.BOARD_SUBST_ID, b.BOARD_BRANCH_ID from AAA a, BBB b, CCC t where a.subs_id = t.subs_id and b.serv_id = t.serv_id --同事说开销比较大。有450W。。下面是执行计划：
涉及的表大小： OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb) SUMMARY_SJZ_GZ CCC TABLE 40 SUMMARY_SJZ_GZ BBB TABLE PARTITION 9016.1875 SUMMARY_SJZ_GZ AAA TABLE PARTITION 67330.25 以下是优化思路： 强制使用索引，导致其中9g的表走了index full scan，然后回表。因为除了index fast scan以外，其他索引扫描都是单块读，回表又是单块读。导致速度非常慢。优化时考虑使用哈希连接，40Mb的小表作为驱动表，连接9g的表，最后连接超大的67G的表。 优化时使用的技术： 1. use_hash（a，b），使用哈希表关联方式 2. /*+parallel(a 5)*/;并行处理 3. db_file_multiblock_read_count多块读参数设置为最大 4. workarea_size_policy设置为手工管理 5. sort_area_size设为接近最大 6. hash_area_size设为接近最大 alter session enable parallel dml; alter session set workarea_size_policy=manual; alter session set sort_area_size=2100000000; alter session set hash_area_size=2100000000; alter session set db_file_multiblock_read_count=128; select /*+parallel(a,5) parallel(b,5) parallel(t,5) leading(t) use_hash(t,b) user_hash(b,a)*/ 1, t.DISC_ID, t.DISC_LEV, to_date(20140117082042, 'yyyymmddhh24miss'), t.MSINFO_ID, t.ORG_ID, t.SERV_ID, t.SUBS_ID, t.OBJ_GRP_ID, a.SUBS_CODE, a.SUBS_STAT, a.SUBS_STAT_REASON, a.SUBS_STAT_DATE, a.ACTION_ID, a.ACTION_TYPE, a.ACTION_EX_TYPE, a.ACT_DATE, a.REQ_ID, a.STAFF_ID, a.CMMS_CUST_CODE, a.SPEED_VALUE, b.ACC_NBR, b.CUST_ID, b.SERV_NBR, b.CONSUME_GRADE, b.SERV_LEV, b.ACCOUNT_NBR, b.CITY_VILLAGE_ID, b.SERV_CHANNEL_ID, b.SERV_STAT_ID, b.CUST_CLASS_DL, b.CUST_TYPE_ID, b.USER_TYPE, b.USER_CHAR, b.PAYMENT_TYPE, b.BILLING_TYPE, b.PROD_ID, b.PROD_CAT_ID, b.EXCHANGE_ID, b.SERV_COL1, b.SERV_COL2, b.AREA_ID, b.SUBST_ID, b.BRANCH_ID, b.STOP_TYPE, b.CUST_MANAGER_ID, b.CREATE_DATE, b.ADDRESS_ID, b.SUBS_DATE, b.OPEN_DATE, b.MODI_STAFF_ID, b.CMMS_CUST_ID, b.CUST_NAME, b.SALES_ID, b.SALES_TYPE_ID, b.SERV_ADDR_ID, t.HIST_CREATE_DATE, b.ARREAR_MONTH, b.ARREAR_MONTH_LAST, t.SALESTAFF_ID, t.EHOME_TYPE, t.EHOME_CLASS, b.strat_grp_dl, b.sale_org1, b.sale_org2, b.sale_org3, b.location_type, b.region_flag, b.terminal_id, b.pstn_id, b.fee_id, b.payment_id, b.billing_id, b.strat_grp_xl, b.fld1, b.fld3, b.cust_level, b.group_cust_type, b.cust_region, b.group_cust_grade, b.control_level, b.net_connect_type, b.trade_type_id, b.acc_nbr2, b.cdma_class_id, b.phone_number_id, b.develop_channel, b.online_time, t.wireless_type, b.new_serv_stat_id, b.is_phs_tk, b.serv_grp_type, b.state, t.cdma_disc_type, b.mix_disc, b.is_3g, t.add_disc_type, to_number(nvl(b.business_type, '-1')), nvl(t.label_num, -1), b.is_mix_prod, t.price_id, t.disc_item_id, b.STD_SUBST_ID, b.STD_BRANCH_ID, t.DISC_ITEM_ID_OP, t.PRICE_ID_OP, t.business_type, b.new_prod_id, b.BOARD_SUBST_ID, b.BOARD_BRANCH_ID from SUMMARY_SJZ_GZ.AAA a, SUMMARY_SJZ_GZ.BBB b, SUMMARY_SJZ_GZ.CCC t where a.subs_id = t.subs_id and b.serv_id = t.serv_id ; PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 382646192 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 781K| 453M| 260K (1)| 01:18:03 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 781K| 453M| 260K (1)| 01:18:03 | | | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 781K| 453M| 260K (1)| 01:18:03 | | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 781K| 379M| 26771 (2)| 00:08:02 | | | Q1,02 | PCWP | | | 5 | PX SEND BROADCAST | :TQ10001 | 781K| 379M| 26771 (2)| 00:08:02 | | | Q1,01 | P->P | BROADCAST | |* 6 | HASH JOIN | | 781K| 379M| 26771 (2)| 00:08:02 | | | Q1,01 | PCWP | | | 7 | PX RECEIVE | | 781K| 73M| 327 (2)| 00:00:06 | | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 781K| 73M| 327 (2)| 00:00:06 | | | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | 781K| 73M| 327 (2)| 00:00:06 | | | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| CCC | 781K| 73M| 327 (2)| 00:00:06 | | | Q1,00 | PCWP | | | 11 | PX BLOCK ITERATOR | | 23M| 9096M| 26427 (2)| 00:07:56 | 1 | 17 | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL | BBB | 23M| 9096M| 26427 (2)| 00:07:56 | 1 | 17 | Q1,01 | PCWP | | | 13 | PX BLOCK ITERATOR | | 204M| 18G| 233K (1)| 01:09:58 | 1 | 20 | Q1,02 | PCWC | | | 14 | TABLE ACCESS FULL | AAA | 204M| 18G| 233K (1)| 01:09:58 | 1 | 20 | Q1,02 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."SUBS_ID"="T"."SUBS_ID") 6 - access("B"."SERV_ID"="T"."SERV_ID") --5小时不出结果，优化后最终20分钟不到就出结果。 1 0
- 无语的index hint：手工分配哈希区，5小时不出结果，优化后20分钟
- 优化一个小时不出结果的SQL
- 极速搞定1小时不出结果SQL的优化（SQL Tuning）
- 巨幅SQL优化（SQL Tuning）——秒杀十几个小时不出结果的SQL
- Index hint 和 Index 的区别
- Index和Index Hint的区别
- hbase开启ttl后每个小时的20和50分钟都会有hbase健康报警
- 从15小时优化到1分钟的SQL
- 六小时也de不出的bug系列
- Spring-Task实现订单前5分钟,后30分钟,后1个小时短信通知
- Oracle 12C 某电信公司 优化一个12小时跑不出来的SQL，最后6分钟就跑完了
- 为什么运行不出结果？
- 重大喜讯，AppStore审核变快啦！真的有可能24小时内出结果
- App Store 24小时内出结果的加急审核内幕
- flash builder中trace不出结果的方法
- PIX停住，不出结果窗口的解决
- mybatis有符合条件的结果但是查询不出
- loopback find查询不出想要的结果
- op 之间的电源隔离
- Shell脚本入门（十六） - printf
- Debug流程控制工具条不见了怎么处理
- 用gcc去编译c程序
- iOS block的用法
- 无语的index hint：手工分配哈希区，5小时不出结果，优化后20分钟
- linux下minicom的使用方法
- 第九周项目四乘法口诀表
- 基于Velocity开发自己的模板引擎
- android应用跳转至市场给软件评分功能
- Cookie大总结(来自网易)
- Ubuntu 10.04 开启 framebuffer
- iOS开发基础知识
- UVA - 348 Optimal Array Multiplication Sequence
