無語的index hint：手工分配雜湊區，5小時不出結果，最佳化後20分鐘，hint

無語的index hint：手工分配雜湊區，5小時不出結果，最佳化後20分鐘，hint

同事發來一個語句，說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。。下面是執行計畫：<img src="http://img.blog.csdn.net/20141025102001913?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ2RtemxoajE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" /> /*涉及的表大小：OWNERSEGMENT_NAMESEGMENT_TYPESize(Mb)SUMMARY_SJZ_GZTB_COMM_BA_MSDISC_TEMPTABLE40SUMMARY_SJZ_GZRPT_COMM_CM_SERV_HISTTABLE PARTITION9016.1875SUMMARY_SJZ_GZRPT_COMM_BA_SUBS_HISTTABLE PARTITION67330.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設為接近最大<p>5小時不出結果，最佳化後20分鐘不到出結果，就是這麼神奇。</p><p>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</p>

</pre><pre>

Oracle HINT 具體含義

在SQL語句最佳化過程中，經常會用到hint,
以下是在SQL最佳化過程中常見Oracle中"HINT"的30個用法1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳輸送量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳回應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/
表明如果資料字典中有訪問表的統計資訊,將基於開銷的最佳化方法,並獲得最佳的輸送量;
表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的最佳化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/
表明對語句塊選擇基於規則的最佳化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/
表明對錶選擇全域掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的存取方法,它只對簇對象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
......餘下全文>>
 
Oracle Hint的用法

1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳輸送量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳回應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

3. /*+CHOOSE*/
表明如果資料字典中有訪問表的統計資訊,將基於開銷的最佳化方法,並獲得最佳的輸送量;
表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的最佳化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

4. /*+RULE*/
表明對語句塊選擇基於規則的最佳化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

5. /*+FULL(TABLE)*/
表明對錶選擇全域掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的存取方法,它只對簇對象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

10. /*+INDEX_COMBINE*/
為指定表選擇位元影像訪問路經,如果INDEX......餘下全文>>