同事发来一个语句，说5个小时不出结果，我滴个神呀，想看看到底是什么垃圾语句造成的。于是叫同事发过来。不看不知道，一看吓一跳，3个表关联，强制使用了2个index hint，其中一个表9g，一个表67g，还有一个小表40mb。无知的开发人员，以为走index就是快的，
同事发来一个语句，说5个小时不出结果，我滴个神呀，想看看到底是什么垃圾语句造成的。于是叫同事发过来。不看不知道，一看吓一跳，3个表关联，强制使用了2个index hint，其中一个表9g，一个表67g，还有一个小表40mb。无知的开发人员，以为走index就是快的，哎。。。下面是同事发来的语句： 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 rpt_comm_ba_subs_hist a, rpt_comm_cm_serv_hist b, tb_comm_ba_msdisc_temp 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 tb_comm_ba_msdisc_temp table 40summary_sjz_gz rpt_comm_cm_serv_hist table partition 9016.1875summary_sjz_gz rpt_comm_ba_subs_hist 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设为接近最大5小时不出结果，优化后20分钟不到出结果，就是这么神奇。
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.rpt_comm_ba_subs_hist  a,          summary_sjz_gz.rpt_comm_cm_serv_hist  b,          summary_sjz_gz.tb_comm_ba_msdisc_temp t    where a.subs_id = t.subs_id      and b.serv_id = t.serv_id