oracle hint (體系)了解

1、.精品.寫hint目的 手工指定sql語句的執(zhí)行計(jì)劃 hints是oracle提供的一種機(jī)制，用來告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計(jì)劃。我們可以用hints來實(shí)現(xiàn)： 1) 使用的優(yōu)化器的類型 2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。 3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。 4) 表之間的連接類型 5) 表之間的連接順序 6) 語句的并行程度 2、hint可以基于以下規(guī)則產(chǎn)生作用 表連接的順序、表連接的方法、訪問路徑、并行度 3、hint應(yīng)用范圍 dml語句 查詢語句 4、語法 delete|insert|select

2、|update /*+ hint text hinttext. */ or delete|insert|select|update -+ hint text hinttext. 如果語（句）法不對，則oracle會自動忽略所寫的hint，不報(bào)錯(cuò) 1. /*+all_rows*/ 表明對語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化. 例如: select /*+all_rows*/ emp_no,emp_nam,dat_in from bsempms where emp_no=scott; 2. /*+first_rows*/ 表明對語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳響

3、應(yīng)時(shí)間,使資源消耗最小化. 例如: select /*+first_rows*/ emp_no,emp_nam,dat_in from bsempms where emp_no=scott; 3. /*+choose*/ 表明如果數(shù)據(jù)字典中有訪問表的統(tǒng)計(jì)信息,將基于開銷的優(yōu)化方法,并獲得最佳的吞吐量; 表明如果數(shù)據(jù)字典中沒有訪問表的統(tǒng)計(jì)信息,將基于規(guī)則開銷的優(yōu)化方法; 例如: select /*+choose*/ emp_no,emp_nam,dat_in from bsempms where emp_no=scott; 4. /*+rule*/ 表明對語句塊選擇基于規(guī)則的優(yōu)化方法. 例如:

4、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)*/ 提示明確表明對指定表根據(jù)rowid進(jìn)行訪問. 例如: select /*+rowid(bsempms)*/ * from bsempms where rowid=aaaaaaaaaaaaaa

5、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 becau

6、se 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*/ 為指定表選擇位圖訪問路經(jīng),如果index_combine中沒有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式. 例如: select /*+index_combine(

7、bsempms sal_bmi hiredate_bmi)*/ * from bsempms where sal5000000 and hiredate 11. /*+index_join(table index_name)*/ 提示明確命令優(yōu)化器使用索引作為訪問路徑. 例如: select /*+index_join(bsempms sal_hmi hiredate_bmi)*/ sal,hiredate from bsempms where salv.avg_sal; 20. /*+no_merge(table)*/ 對于有可合并的視圖不再合并. 例如: select /*+no_merg

8、e(v) */ a.emp_no,a.emp_nam,b.dpt_no from bsempms a (select dpt_no,avg(sal) as avg_sal from bsempms b group by dpt_no) v where a.dpt_no=v.dpt_no and a.salv.avg_sal; 21. /*+ordered*/ 根據(jù)表出現(xiàn)在from中的順序,ordered使oracle依此順序?qū)ζ溥B接. 例如: select /*+ordered*/ a.col1,b.col2,c.col3 from table1 a,table2 b,table3 c whe

9、re a.col1=b.col1 and b.col1=c.col1; 22. /*+use_nl(table)*/ 將指定表與嵌套的連接的行源進(jìn)行連接,并把指定表作為內(nèi)部表. 例如: select /*+ordered use_nl(bsempms)*/ bsdptms.dpt_no,bsempms.emp_no,bsempms.emp_nam from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 23. /*+use_merge(table)*/ 將指定的表與其他行源通過合并排序連接方式連接起來. 例如: select /*+u

10、se_merge(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 24. /*+use_hash(table)*/ 將指定的表與其他行源通過哈希連接方式連接起來. 例如: select /*+use_hash(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 25. /*+driving_site(table)*/ 強(qiáng)制與oracle所選擇的位置不同的表進(jìn)行查詢執(zhí)行. 例如: s

11、elect /*+driving_site(dept)*/ * from bsempms,deptbsdptms where bsempms.dpt_no=dept.dpt_no; 26. /*+leading(table)*/ 將指定的表作為連接次序中的首表. 27. /*+cache(table)*/ 當(dāng)進(jìn)行全表掃描時(shí),cache提示能夠?qū)⒈淼臋z索塊放置在緩沖區(qū)緩存中最近最少列表lru的最近使用端 例如: select /*+full(bsempms) cahe(bsempms) */ emp_nam from bsempms; 28. /*+nocache(table)*/ 當(dāng)進(jìn)行全表掃

12、描時(shí),cache提示能夠?qū)⒈淼臋z索塊放置在緩沖區(qū)緩存中最近最少列表lru的最近使用端 例如: select /*+full(bsempms) nocahe(bsempms) */ emp_nam from bsempms; 29. /*+append*/ 直接插入到表的最后,可以提高速度. insert /*+append*/ into test1 select * from test4 ; 30. /*+noappend*/ 通過在插入語句生存期內(nèi)停止并行模式來啟動常規(guī)插入. insert /*+noappend*/ into test1 select * from test4 ; 31.

13、no_index: 指定不使用哪些索引 /*+ no_index ( table index index. ) */ select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal300; 32. parallel select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal300; 另：每個(gè)select/insert/update/delete命令后只能有一個(gè)/*+ */，但提示內(nèi)容可以有多個(gè)，可以用逗號分開，

14、空格也可以。 如：/*+ ordered index() use_nl() */ - 類似如下的一條語句：insert into xxxx select /*+parallel(a) */ * from xxx a;數(shù)據(jù)量大約在75g左右，這位兄弟從上午跑到下午還沒跑完，過來問我咋回事，說平常2hrs能跑完的東西跑了好幾個(gè)小時(shí)還撒動靜。查看系統(tǒng)性能也比較 正常，cpu，io都不繁忙，平均read速度在80m/s左右（勉強(qiáng)湊合），但平均寫速度只有10m不到。等待事件里面大量的 px deq credit: send blkd，這里能看出并行出了問題，從而最后得知是并行用法有問題，修改之后20分鐘

15、完成了該操作。正確的做法應(yīng)該是： alter session enable dml parallel； insert /*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a; 因?yàn)閛racle默認(rèn)并不會打開pdml，對dml語句必須手工啟用。 另外不得不說的是，并行不是一個(gè)可擴(kuò)展的特性，只有在數(shù)據(jù)倉庫或作為dba等少數(shù)人的工具在批量數(shù)據(jù)操作時(shí)利于充分利用資源，而在oltp環(huán)境下使用并行 需要非常謹(jǐn)慎。事實(shí)上pdml還是有比較多的限制的，例如不支持觸發(fā)器，引用約束，高級復(fù)制和分布式事務(wù)等特性，同時(shí)也會帶來額外的

16、空間占用，pddl同 樣是如此。有關(guān)parallel excution可參考官方文檔，在thomas kyte的新書expert oracle database architecture也有精辟的講述。 - select count(*) from wid_serv_prod_mon_1100 a where a.acct_month = 201010 and a.partition_id = 10 and serv_state not in (2hb, 2hl, 2hj, 2hp, 2hf) and online_flag in (0) and incr_product_id in (2000020) and product_id in (2020966, 2020972, 2100297, 2021116) and billing_mode_id = 1 and exp_date to_date(201010, yyyymm) and not exists (select /*+no_index (b idx_w_cdr_mon_se