【文章內容簡介】 利用索引。在下面的查詢中索 引得到了使用： select * from employee where last_name like 39。c%39。,用Where子句替換HAVING子句,避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結果 集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限 制記錄的數(shù)目,那就能減少這方面的開銷. 例如: 低效: SELECT REGION，AVG(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 順序 WHERE GROUP HAVING,用NOT EXISTS 替代 NOT IN,在子查詢中,NOT IN子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況 下,NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 使用NOT EXISTS 子句可以有效地利用索引。盡可能使用NOT EXISTS 來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度）， NOT EXISTS要比NOT IN查詢效率更高。 例如: 語句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp)。 語句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno)。 2要比1的執(zhí)行性能好很多。 因為1中對emp進行了full table scan,這是很浪費時間的操作。而且1中沒有用到emp的index， 因為沒有where子句。而2中的語句對emp進行的是縮小范圍的查詢。,用索引提高效率,索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使 用了一個復雜的自平衡Btree結構. 通常,通過索引查詢數(shù)據(jù)比全表 掃描要快. 當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結多個表時使用索引也可以 提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key) 的唯一性驗證。 通常, 在大型表中使用索引特別有效. 當然,你也會發(fā)現(xiàn), 在掃描小 表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提 高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要 定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也 會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為 此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理, 那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引 是有必要的。,避免在索引列上使用計算,WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不 使用索引而使用全表掃描． 低效： SELECT … FROM DEPT WHERE SAL * 12 25000。 高效: SELECT … FROM DEPT WHERE SAL 25000/12。,用= 替代 ,如果DEPTNO上有一個索引。 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3,通過使用=、=等，避免使用NOT命令,例子： select * from employee where salary 3000。 對這個查詢，可以改寫為不使用NOT： select * from employee where salary3000。 雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。,如果有其它辦法，不要使用子查詢。,外部聯(lián)接“+“的用法,外部聯(lián)接“+“按其在“=“的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶“+“運算符的表中的一個行不直接匹配于帶“+“預算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯(lián)接“+“，可以替代效率十分低下的 not in 運算，大大提高運行速度。例如，下面這條命令執(zhí)行起來很慢： select a.empno from emp a where a.empno not in (select empno from emp1 where job=39。SALE39。)。 利用外部聯(lián)接，改寫命令如下: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=39。SALE39。 這樣運行速度明顯提高.,盡量多使用COMMIT,事務是消耗資源的，大事務還容易引起死鎖 COMMIT所釋放的資源: 回滾段上用于恢復數(shù)據(jù)的信息. 被程序語句獲得的鎖 redo log buffer 中的空間 ORACLE為管理上述3種資源中的內部花費,用TRUNCATE替代DELETE,當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有 COMMIT事務,ORACLE會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準 確地說是恢復到執(zhí)行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的 信息.當命令運行后,數(shù)據(jù)不能被恢復.因此很少的資源被調用, 執(zhí)行時間也會很短.,計算記錄條數(shù),和一般的觀點相反, count(*) 比count(1)稍快 , 當然如果可 以通過索引檢索,對索引列的計數(shù)仍舊是最快的. 例如 COUNT(EMPNO),字符型字段的引號,比如有的表PHONE_NO字段是CHAR型,而且創(chuàng)建有索引， 但在WHERE條件中忘記了加引號，就不會用到索引。 WHERE PHONE_NO=‘13920202022’ WHERE PHONE_NO=13920202022,優(yōu)化EXPORT和IMPORT,使用較大的BUFFER(比如10MB , 10,240,000)可以提高 EXPORT和IMPORT的速度。 ORACLE將盡可能地獲取你所指定的內存大小,即使在內存 不滿足,也不會報錯.這個值至少要和表中最大的列相當,否則 列值會被截斷。,** 優(yōu)化 Tools **,SQL 語句的執(zhí)行步驟,語法分析 ，分析語句的語法是否符合規(guī)范，衡量語句中各表達式的意義。 語義分析 ，檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在，且用戶有相應的權限。 視圖轉換，將涉及視圖的查詢語句轉換為相應的對基表查詢語句。 表達式轉換， 將復雜的 SQL 表達式轉換為較簡單的等效連接表達式。 選擇優(yōu)化器，不同的優(yōu)化器一般產(chǎn)生不同的“執(zhí)行計劃” 選擇連接方式， ORACLE 有三種連接方式，對多表連接 ORACLE 可選擇適當?shù)倪B接方式。 選擇連接順序， 對多表連接 ORACLE 選擇哪一對表先連接，選擇這兩表中哪個表做為源數(shù)據(jù)表。 選擇數(shù)據(jù)的搜索路徑， 根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑，如是選用全表搜索還是利用索引或是其他的方式。 運行“執(zhí)行計劃”,優(yōu)化器與執(zhí)行計劃,Oracle在執(zhí)行一個SQL之前,首先要分析一下語句的執(zhí)行計 劃,然后再按執(zhí)行計劃去執(zhí)行。分析語句的執(zhí)行計劃的工作 是由優(yōu)化器(Optimizer)來完成的 Oracle的優(yōu)化器共有兩種的優(yōu)化方式,即基于規(guī)則的優(yōu)化方 式(RuleBased Optimization,簡稱為RBO)和基于代價的優(yōu) 化方式(CostBased Optimization,簡稱為CBO)。 A、RBO方式：優(yōu)化器在分析SQL語句時,所遵循的是Oracle內部預定 的一些規(guī)則。比如我們常見的,當一個where子句中的一列有索引時去走 索引。 B、CBO方式：是看語句的代價(Cost)了,這里的代價主要指Cpu和內存 。優(yōu)化器在判斷是否用這種方式時,主要參照的是表及索引的統(tǒng)計信息， 很多的時侯過期統(tǒng)計信息會令優(yōu)化器做出一個錯誤的執(zhí)行計劃在 Oracle8及以后的版本,Oracle推薦用CBO的方式。 在Oracle10g中,取消了RBO的支持。,優(yōu)化器與執(zhí)行計劃,Rule:即走基于規(guī)則的方式 Choose:默認的情況下Oracle用的便是這種方式。當一個表或或索引有 統(tǒng)計信息,則走CBO的方式,如果表或索引沒統(tǒng)計信息,表又不是特別的小, 而且相應的列有索引時,那么就走索引,走RBO的方式 First Rows:它與Choose方式是類似的,所不同的是當一個表有統(tǒng)計信息 時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時 間 All Rows:all_rows是oracle優(yōu)化器默認的模式,它將選擇一種在最短時 間內返回所有數(shù)據(jù)的執(zhí)行計劃,它將基于整體成本的考慮. first_rows_n:first_rows_n是根據(jù)成本而不是基于硬編碼的規(guī)則來選擇 執(zhí)行計劃.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意 正數(shù).這里的n是我們想獲取結果集的前n條記錄,這種需求在很多分頁語 句的需求中會碰到.,用EXPLAIN PLAN 分析SQL語句,EXPLAIN PLAN 是一個很好的分析SQL語句的工具,它甚至 可以在不執(zhí)行SQL的情況下分析語句. 通過分析,我們就可以 知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃 描或全表掃描)以及使用到的索引名稱. 你需要按照從里到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN分析的結果是用縮進的格式排列的, 最內部 的操作將被最先解讀, 如果兩個操作處于同一層中,帶有最小 操作號的將被首先執(zhí)行. NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作, 正確的 執(zhí)行路徑是檢查對NESTED LOOP提供數(shù)據(jù)的操作,其中操 作號最小的將被最先處理.,Autotrace 解讀,Current mode: 對于修改的數(shù)據(jù)從數(shù)據(jù)段中讀 Readconsistent mode: 讀一致性模式 Physical block: 物理塊（如8192字節(jié)） Recursive calls: 嵌套調用次數(shù),使用TKPROF 工具,SQL trace 工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到 一個跟蹤文件中. 這個跟蹤文件提供了許多有用的信息,例如解 析次數(shù).執(zhí)行次數(shù),CPU使用時間等.這些數(shù)據(jù)將可以用來優(yōu)化你 的系統(tǒng). 設置SQL TRACE在會話級別: 有效 ALTER SESSION SET SQL_TRACE TRUE 設置SQL TRACE 在整個數(shù)據(jù)庫有效, 你必須將SQL_TRACE 參數(shù)在init.ora中設為TRUE, USER_DUMP_DEST參數(shù)說明了 生成跟蹤文件的目錄 再使用TKPROF對TRACE文件進行分析 分析結果更加準確、清楚,在SQLPLUS 配置AUTOTRACE,在SQLPLUS 配置AUTOTRACE, 首先創(chuàng)建PLUSTRACE角色并且賦給public： Sql @$ORACLE_HOME/sqlplus/admin/plustrce.sql 賦權限給用戶 Sql grant plustrace to public（預賦權的用戶名）。 以SYSTEM用戶創(chuàng)建PLAN_TABLE表 Sql @$ORACLE_HOME/rdbms/admin/utlxplan.sql Sql create public synonym plan_table for plan_table。 Sql grant all on plan_table to public。 在每個用戶下設置AUTOTRACE可顯示其執(zhí)行計劃。,優(yōu)化器與執(zhí)行計劃,SQL select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in (39。ACCOUNTING39。,39。RESEARCH39。,39。SALES39。,39。OPERATIONS39。)。 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 39。EMP39。 3 1 TABLE ACCESS (BY INDEX ROWID) OF 39。DEPT39。 4 3 INDEX (UNIQUE SCAN) OF 39。PK_DEPT39。 (UNIQUE) 最起碼要解決全表掃描問題 改變where條件的次序一般沒有用,目錄,優(yōu)化基礎知識 性能調整綜述 有效的應用設計 SQL語句的處理過程 Oracle的優(yōu)化器 Oracle的執(zhí)行計劃 注意事項,性能調整綜述,誰來調整系統(tǒng)? 什么時候調整? 建立有效調整的目標 在設計和開發(fā)時的調整,誰來調整系統(tǒng),應用設計人員必須傳達應用系統(tǒng)的設計，使得每個人都清楚應用中的數(shù)據(jù)流動. 應用開發(fā)人員必須傳達他們選擇的實現(xiàn)策略，使得語句調整的過程中能快速、容易地識別有問題的應用模塊和可疑的SQL語句. 數(shù)據(jù)庫管理人員必須仔細地監(jiān)控系統(tǒng)活動并提供它們的資料，使得異常的系統(tǒng)性能可被快速得識別和糾正. 硬件/軟件管理人員必須傳達系統(tǒng)的硬件、軟件配置并提供它們的資料，使得相關人員能有效地設計和管理系統(tǒng)。,誰來調整系統(tǒng),與系統(tǒng)涉及的每個人都在調整過程中起某些作用，當上面提及的那些人員傳達了系統(tǒng)的特性并提供了它們的資料，調整就能相對的容易和更快一些。 事實上的結果是：數(shù)據(jù)庫管理員對調整負有全部或主要