ORACLE執(zhí)行計(jì)劃和日常注意事項(xiàng)

1、內(nèi)容安排： 其他相關(guān) 執(zhí)行計(jì)劃第一章 執(zhí)行計(jì)劃1.2 執(zhí)行計(jì)劃前傳之 概念介紹可選擇性(selectivity)：列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。 唯一鍵的數(shù)量/表中的行數(shù) - 1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進(jìn)行查詢時(shí)，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。得到執(zhí)行計(jì)劃：共有三種方法得到執(zhí)行計(jì)劃。1、 sql set autotrace on sql set autotrace traceonly2、用explain plan命令此方法時(shí)，并不執(zhí)行sql語句，所以只會(huì)列出執(zhí)行計(jì)劃，不會(huì)列出統(tǒng)計(jì)信息，并且執(zhí)行

2、計(jì)劃只存在plan_table中。所以該語句比起set autotrace traceonly可用性要差。需要用下面的命令格式化輸出。執(zhí)行計(jì)劃步驟： 在了解執(zhí)行計(jì)劃步驟之前認(rèn)知性的了解下sql的執(zhí)行步驟。sql處理的基本過程。主要包括： * 查詢語句處理 * dml語句處理(insert, update, delete) * ddl 語句處理(create . , drop . , alter . , ) * 事務(wù)控制(commit, rollback) 主要講dml語句的處理過程， 第1步: create a cursor 創(chuàng)建游標(biāo) 第2步: parse the statement 分析語句

3、 第3步: bind any variables 綁定變量 第4步: run the statement 運(yùn)行語句 第5步: close the cursor 關(guān)閉游標(biāo)select ename, job, sal, dnamefrom emp, deptwhere emp.deptno = dept.deptnoand not exists( select * from salgradewhere emp.sal between losal and hisal );對(duì)第3步返回的每一行，oracle實(shí)現(xiàn)這些步驟：oracle實(shí)現(xiàn)步驟5，并將結(jié)果rowid返回給第4步。oracle實(shí)現(xiàn)步驟4，并

4、將結(jié)果行返回給第2步。oracle實(shí)現(xiàn)步驟2，將接受來自第3步的一行和來自第4步的一行，并返回給第1步一行。oracle實(shí)現(xiàn)步驟6，如果有結(jié)果行的話，將它返回給第1步。oracle實(shí)現(xiàn)步驟1，如果從步驟6返回行，oracle將來自第2步的行返回給發(fā)出sql語句的用戶。第二章 sql日常注意事項(xiàng)2.1前情回顧1. where子句中的連連接順順序oracle采用自下而上的順序解析where子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他where條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在where 子句的末尾.(低效低效) select from emp e where sal 50000

5、and job = manager and 25 (select count(*) from emp where mgr=e.empno); (高效高效) select from emp e where 25 50000 and job = “manager”; 2.減減少訪問數(shù)訪問數(shù)據(jù)庫(kù)庫(kù)的次數(shù)數(shù)當(dāng)執(zhí)行每條sql語句時(shí), oracle在內(nèi)部執(zhí)行了許多工作: 解析sql語句, 估算索引的利用率, 綁定變量, 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫(kù)的次數(shù), 就能實(shí)際上減少oracle的工作量.例如：以下有三種方法可以檢索出雇員號(hào)等于0342或或0291的職員的職員. 方法1 (最低效) se

6、lect emp_name , salary , grade from emp where emp_no = 342; select emp_name , salary , grade from emp where emp_no = 291; 方法2 (次低效) declare cursor c1 (e_no number) is select emp_name,salary, grade from emp where emp_no = e_no; begin open c1(342); fetch c1 into ,.,. ; . open c1(291); fetch c1 into ,.

7、,. ; close c1; end; 方法3 (高效) select a.emp_name , a.salary , a.grade, b.emp_name , b.salary , b.grade from emp a,emp b where a.emp_no = 342 and b.emp_no = 291; 3.使用decode函數(shù)來減數(shù)來減少處處理時(shí)間時(shí)間使用decode函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.select count(*)，sum(sal) from emp where dept_no = 0020 and ename like smith%; select

8、 count(*)，sum(sal) from emp where dept_no = 0030 and ename like smith%; 你可以用decode函數(shù)高效地得到相同結(jié)果select count(decode(dept_no,0020,x,null) d0020_count, count(decode(dept_no,0030,x,null) d0030_count, sum(decode(dept_no,0020,sal,null) d0020_sal, sum(decode(dept_no,0030,sal,null) d0030_sal from emp where en

9、ame like smith%; 4.刪刪除重復(fù)記錄復(fù)記錄最高效的刪除重復(fù)記錄方法( 因?yàn)槭褂昧藃owid) delete from emp e where e.rowid (select min(x.rowid) from emp x where x.emp_no = e.emp_no);5.用truncate替代delete 當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息. 如果你沒有commit事務(wù),oracle會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用truncate時(shí), 回滾段不再存放

10、任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. 備注(ps: truncate只在刪除全表適用只在刪除全表適用,truncate是是ddl不是不是dml)6.盡盡量多使用commit 只要有可能,在程序中盡量多使用commit, 這樣程序的性能得到提高,需求也會(huì)因?yàn)閏ommit所釋放的資源而減少: commit所釋放的資源: a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息. b. 被程序語句獲得的鎖 c. redo log buffer 中的空間 d. oracle為管理上述3種資源中的內(nèi)部花費(fèi)(ps: 在使用commit時(shí)必須要注意到事務(wù)的完整性,現(xiàn)實(shí)中效率和事

11、務(wù)完整性往往是魚和熊掌不可得兼) 7.計(jì)計(jì)算記錄條數(shù)記錄條數(shù)和一般的觀點(diǎn)相反, count(*) 比count(1)稍快, 當(dāng)然如果可以通過索引檢索, 對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如count(empno) 。在某論壇（屏蔽廣告）中,有熱烈的討論, 可能我這里的觀點(diǎn)并不十分準(zhǔn)確,通過實(shí)際的測(cè)試,上述三種方法并沒有顯著的性能差8.用where子句替換換having子句避免使用having子句, having 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過where子句限制記錄的數(shù)目,那就能減少這方面的開銷. 低效: select region，avg

12、(log_size) from location group by region having region region != sydney and region != perth 高效：select region，avg(log_size) from location where region region != sydney and region != perth group by region (ps: having 中的條件一般用于對(duì)一些集合函數(shù)的比較,如count() 等等. 除此而外,一般的條件應(yīng)該寫在where子句中)9.減減少對(duì)對(duì)表的查詢查詢?cè)诤凶硬樵兊膕ql語句中,要特別

13、注意減少對(duì)表的查詢.低效：select tab_name from tables where tab_name = ( select tab_name from tab_columns where version = 604) and db_ver= ( select db_ver from tab_columns where version = 604) 高效：select tab_name from tables where (tab_name,db_ver) = ( select tab_name,db_ver) from tab_columns where version = 604) update 多個(gè)column 例子:低效: update emp set emp_cat = (select max(category) from emp_categorie