1、ORACLE培訓-SQL性能優化.內容概述課程主要討論： SQL語句執行的過程、ORACLE優化器，表之間的關聯，如何得到SQL執行方案，如何分析執行方案等內容，從而由淺到深的方式了解SQL優化的過程，使大家逐漸掌握SQL優化。.目錄 優化根底知識 性能調整綜述 有效的運用設計 SQL語句的處置過程 Oracle的優化器 Oracle的執行方案 本卷須知.一、優化根底知識.概述性能管理性能問題調整的方法SQL優化機制運用的調整SQL語句的處置過程共享SQL區域SQL語句處置的階段共享游標SQL編碼規范Oracle 優化器引見SQL Tunning Tips優化Tools.性能管理盡早開場設立適

2、宜目的邊調整邊監控相關人員進展協作及時處置過程中發生的不測和變化80/20定律.SQL 優化衡量目的隨著軟件技術的不斷開展，系統性能越來越重要。系統性能主要用：系統呼應時間和并發性來衡量。呵斥SQL語句性能不佳大致有兩個緣由：開發人員只關注查詢結果的正確性，忽視查詢語句的效率。開發人員只關注SQL語句本身的效率，對SQL語句執行原理、影響SQL執行效率的主要要素不清楚。* 前者可以經過深化學習SQL語法及各種SQL調優技巧進展處理。 SQL調優是一個系統工程，熟習SQL語法、掌握各種內嵌函數、分 析函數的用法只是編寫高效SQL的必要條件。* 后者從分析SQL語句執行原理入手，指出SQL調優應在

3、優化SQL解 析和優化CBO上。.調優領域調優領域：運用程序級調優： * SQL語句調優 * 管理變化調優2. 實例級調優 * 內存 * 數據構造 * 實例配置3. 操作系統交互 * I/O * SWAP * Parameters本課程內容只講解討論運用程序級：Oracle SQL語句調優及管理變化調優.調整的方法調整業務功能調整數據設計調整流程設計調整SQL語句調整物理構造調整內存分配調整I/O調整內存競爭調整操作系統.不同調整產生相應性能收益.調整的角色.SQL語句優化是提高性能的重要環節開發人員不能只注重功能的實現，不論性能如何開發人員不能把Oracle當成一個黑盒子，必需了解其構造、處

4、置SQL和數據的方法必需遵守既定的開發規范未經過SQL語句優化的模塊不要上線.SQL語句優化的過程定位有問題的語句檢查執行方案檢查執行過程中優化器的統計信息分析相關表的記錄數、索引情況改寫SQL語句、運用HINT、調整索引、表分析有些SQL語句不具備優化的能夠，需求優化處置方式到達最正確執行方案.什么是好的SQL語句？盡量簡單，模塊化易讀、易維護節省資源內存CPU掃描的數據塊要少少排序不呵斥死鎖.SQL共享原理 ORACLE將執行過的SQL語句存放在內存的共享池(shared buffer pool)中，可以被一切的數據庫用戶共享。當他執行一個SQL語句(有時被稱為一個游標)時,假設它和之前的

5、執行過的語句完全一樣, ORACLE就能很快獲得曾經被解析的語句以及最好的 執行途徑. 這個功能大大地提高了SQL的執行性能并節省了內存的運用。.SQL共享原理為了不反復解析一樣的SQL語句，在第一次解析之后，Oracle將SQL語句存放在內存中。這塊位于系統全局區域SGA(systemglobal area)的共享池(shared buffer poo1)中的內存可以被一切的數據庫用戶共享。因此，當他執行一個SQL語句(有時被稱為一個游標)時，假設它和之前執行過的語句完全一樣，Oracle就能很快獲得曾經被解析的語句以及最好的執行方案。Oracle的這個功能大大地提高了SQL的執行性能并節省

6、了內存的運用。惋惜的是，Oracle只對簡單的表提供高速緩沖(cache bufferiIlg)，這個功能并不適用于多表銜接查詢。數據庫管理員必需在啟動參數文件中為這個區域設置適宜的參數，當這個內存區域越大，就可以保管更多的語句，當然被共享的能夠性也就越大了。當向Oracle提交一個SQL語句時，Oracle會首先在這塊內存中查找一樣的語句。.SQL共享的三個條件當前被執行的語句和共享池中的語句必需完全一樣 包括大小寫、空格、換行等兩個語句所指的對象必需完全一樣 同義詞與表是不同的對象兩個SQL語句中必需運用一樣的名字的綁定變量(bind variables) .共享SQL語句留意：Oracl

7、e對兩者采取的是一種嚴厲匹配戰略，要達成共享。SQL語句必須完全一樣(包括空格、換行等)。可以運用共享的語句必需滿足三個條件： 字符級的比較。當前被執行的語句和共享池中的語句必需完全一樣。例如： SELECT * FROM ATABLE；和下面每一個SQL語句都不同：SELECT *from ATABLESelect * From Atable； 語句所指對象必需完全一樣 即兩條SQL語句操作的數據庫對象必需同一。語句中必需運用一樣命名的綁定變量。如：第一組的兩個SQL語句是一樣的，可以共享；而第二組中兩個語句不同，即使在運轉時賦予不同的綁定變量以一樣的值： 第一組 select pin，na

8、me from people where pin = ：blk1.pin；select pin，name from people where pin =：blk1.pin；第二組 select pin，name from people where pin =：blk1.ot_jnd；select pin，name from people where pin = ：blk1.ov_jnd；.SQL語句的處置過程共享SQL區域.Sql 處置過程.SQL PARSE與共享SQL語句當一個Oracle實例接納一條sql后1、Create a Cursor 創建游標2、Parse the Stateme

9、nt 分析語句3、Describe Results of a Query 描畫查詢的結果集4、Define Output of a Query 定義查詢的輸出數據5、Bind Any Variables 綁定變量6、Parallelize the Statement 并行執行語句7、Run the Statement 運轉語句8、Fetch Rows of a Query 取查詢出來的行9、Close the Cursor 封鎖游標 .為什么要bind variables?字符級的比較:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 與SE

10、LECT * FROM USER_FILES WHERE USER_NO = 10004321;檢查：select name,executionsfrom v$db_object_cachewhere name like select * from user_files%.什么叫做重編譯問題什么叫做重編譯？下面這個語句每執行一次就需求在SHARE POOL 硬解析一次，一百萬用戶就是一百萬次，耗費CPU和內存，假設業務量大，很能夠導致宕庫假設綁定變量，那么只需求硬解析一次，反復調用即可select * from dConMsg where contract_no = 32021484095.綁

11、定變量處理重編譯問題未運用綁定變量的語句sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2);EXEC SQL EXECUTE IMMEDIATE :sqlstr ;EXEC SQL COMMIT; 運用綁定變量的語句 strcpy(sqlstr, insert into test (num1, num2) values (:v1, :v2);EXEC SQL PREPARE sql_stmt FROM :sqlstr;EXEC SQL EXECUTE sql_stmt USING

12、 :n_var1, :n_var2;EXEC SQL COMMIT; .綁定變量的本卷須知留意：1、不要運用數據庫級的變量綁定參數cursor_sharing來強制綁定，無論其值為 force 還是similar2、有些帶 0性能優于select count(*)from tab；盡量少嵌套子查詢，這種查詢會耗費大量的CPU資源；對于有比較多or運算的查詢，建議分成多個查詢，用union all結合起來；多表查詢的查詢語句中，選擇最有效率的表名順序。Oracle解析器對表解析從右到左，所以記錄少的表放在右邊。 盡量多用commit語句提交事務，可以及時釋放資源、解鎖、釋放日志空間、減少管理破費

13、；在頻繁的、性能要求比較高的數據操作中，盡量防止遠程訪問，如數據庫鏈等，訪問頻繁的表可以常駐內存：alter tablecache； 在Oracle中動態執行SQL，盡量用execute方式，不用dbms_sql包。.* SQL Tunning Tips *.sql 語句的編寫原那么和優化 隨著數據庫中數據的添加，系統的呼應速度就成為目前系統需求處理的最主要的問題之一。系統優化中一個很重要的方面就是SQL語句的優化。對于大量數據，劣質SQL語句和優質SQL語句之間的速度差別可以到達上百倍，對于一個系統不是簡單地能實現其功能就可，而是要寫出高質量的SQL語句，提高系統的可用性。在多數情況下，Or

14、acle運用索引來更快地遍歷表，優化器主要根據定義的索引來提高性能。假設在SQL語句的where子句中寫的SQL代碼不合理，就會呵斥優化器刪去索引而運用全表掃描，普通就這種SQL語句就是所謂的劣質SQL語句。.sql 語句的編寫原那么和優化 在編寫SQL語句時我們應清楚優化器根據何種原那么來運用索引，這有助于寫出高性能的SQL語句。SQL語句的編寫原那么和SQL語句的優化，請跟我一同窗習以下幾方面：.Tunning Tip的各個方面1.不要讓Oracle做得太多；2.給優化器更明確的命令； 3.減少訪問次數；4.細節上的影響；.1.不要讓Oracle做得太多.防止復雜的多表關聯select f

15、rom user_files uf, df_money_files dm, cw_charge_record ccwhere uf.user_no = dm.user_noand dm.user_no = cc.user_noand and not exists(select )?很難優化，隨著數據量的添加性能的風險很大。.防止運用 * 當他想在SELECT子句中列出一切的COLUMN時,運用動態SQL列援用 * 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實踐上,ORACLE在解析的過程中, 會將* 依次轉換成一切的列名, 這個任務是經過查詢數據字典完成的, 這意味著將耗費更多的時

16、間；只提取他所要運用的列；運用別名可以加快解析速度；.防止運用耗費資源的操作帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能. DISTINCT需求一次排序操作, 而其他的至少需求執行兩次排序.例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣, 每個查詢需求執行一次排序, 然后在執行UNION時, 又一個獨一排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入排序終了后才干開場執行. 嵌入的排序的深度會大

17、大影響查詢的效率.通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.用EXISTS交換DISTINCT例如:低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);.用UNION-ALL 交換UNION ( if possible)當S

18、QL語句需求UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然后在輸出最終結果前進展排序.舉例: 低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE

19、TRAN_DATE = 31-DEC-95 UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95.2. 給優化器更明確的命令.自動選擇索引假設表中有兩個以上包括兩個索引，其中有一個獨一性索引，而其他是非獨一性在這種情況下，ORACLE將運用獨一性索引而完全忽略非唯一性索引舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;這里，只需EMPNO上的索引是獨一性的，所以EMPNO索引將用來檢索記錄TABLE

20、 ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX.至少要包含組合索引的第一列假設索引是建立在多個列上, 只需在它的第一個列(leading column)被where子句援用時,優化器才會選擇運用該索引. SQL create table multiindexusage ( inda number , indb number , descr varchar2(10);Table created.SQL create index multindex on multiindexusage(inda,indb);Index created

21、.SQL set autotrace traceonlySQL select * from multiindexusage where inda = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE 2 1 INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE)SQL select * from multiindexusage where indb = 1;Execution Plan- 0 SEL

22、ECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明顯, 當僅援用索引的第二個列時,優化器運用了全表掃描而忽略了索引.防止在索引列上運用函數WHERE子句中，假設索引列是函數的一部分優化器將不運用索引而運用全表掃描舉例:低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;.防止運用前置通配符WHERE子句中, 假設索引列所對應的值的第一個字符由通配符(WILDCARD)開場, 索引將不被采用.

23、 SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO LIKE %109204421; 在這種情況下，ORACLE將運用全表掃描.防止在索引列上運用NOT通常，我們要防止在索引列上運用NOT, NOT會產生在和在索引列上運用函數一樣的影響. 當ORACLE遇到NOT,他就會停頓運用索引轉而執行全表掃描.舉例: 低效: (這里,不運用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (這里,運用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0;.防

24、止在索引列上運用 IS NULL和IS NOT NULL防止在索引中運用任何可以為空的列，ORACLE將無法運用該索引 對于單列索引，假設列包含空值，索引中將不存在此記錄. 對于復合索引，假設每個列都為空，索引中同樣不存在此記錄.假設至少有一個列不為空，那么記錄存在于索引中假設獨一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有一樣A,B值123,null的記錄(插入). 然而假設一切的索引列都為空，ORACLE將以為整個鍵值為空而空不等于空. 因此他可以插入1000條具有一樣鍵值的記錄,當然它們都是空!由于空值不存在于索

25、引列中,所以WHERE子句中對索引列進展空值比較將使ORACLE停用該索引.任何在where子句中運用is null或is not null的語句優化器是不允許運用索引的。.防止出現索引列自動轉換當比較不同數據類型的數據時, ORACLE自動對列進展簡單的類型轉換.假設EMP_TYPE是一個字符類型的索引列.SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO = 109204421這個語句被ORACLE轉換為:SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE TO_NU

26、MBER(USER_NO) = 109204421 由于內部發生的類型轉換, 這個索引將不會被用到! .在查詢時盡量少用格式轉換如用 WHERE a.order_no = b.order_no 不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1).3.減少訪問次數.減少訪問數據庫的次數當執行每條SQL語句時, ORACLE在內部執行了許多任務: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數據塊

27、等等. 由此可見, 減少訪問數據庫的次數 , 就能實踐上減少ORACLE的任務量.類比，工程實施.運用DECODE來減少處置時間例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%;他可以用DECODE函數高效地得到一樣結果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(

28、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_SALFROM EMP WHERE ENAME LIKE SMITH%;.減少對表的查詢在含有子查詢的SQL語句中,要特別留意減少對表的查詢.例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION =

29、604) ANDDB_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) .4. 細節上的影響.WHERE子句中的銜接順序ORACLE采用自下而上的順序解析WHERE子句,根據這個原理, 當在WHERE子句中有多個表聯接時，WHERE子句中排在最后的表該當是前往行數能夠最少的表，有過濾條件的

30、子句應放在WHERE子句中的最后。如：設從emp表查到的數據比較少或該表的過濾條件比較確定，能大大減少查詢范圍，那么將最具有選擇性部分放在WHERE子句中的最后：select * from emp e,dept d where d.deptno 10 and e.deptno =30 ; 假設dept表前往的記錄數較多的話，上面的查詢語句會比下面的查詢語句呼應快得多。select * from emp e,dept d where e.deptno =30 and d.deptno 10 ;.WHERE子句 函數、表達式運用最好不要在WHERE子句中運用函或表達式，假設要運用的話，最好一致運用

31、一樣的表達式或函數，這樣便于以后運用合理的索引。.Order by語句 ORDER BY語句決議了Oracle如何將前往的查詢結果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數參與列中象聯接或者附加等。任何在Order by語句的非索引項或者有計算表達式都將降低查詢速度。仔細檢查order by語句以找出非索引項或者表達式，它們會降低性能。處理這個問題的方法就是重寫order by語句以運用索引，也可以為所運用的列建立另外一個索引，同時應絕對防止在order by子句中運用表達式。.聯接列 對于有聯接的列，即使最后的聯接值為一個靜態值，優化器是不會運用索引的。 sele

32、ct * from employss where first_name|last_name =Beill Cliton; 系統優化器對基于last_name創建的索引沒有運用。 當采用下面這種SQL語句的編寫，Oracle系統就可以采用基于last_name創建的索引。 select * from employee where first_name =Beill and last_name =Cliton; .帶通配符%的like語句 通配符%在搜索詞首出現，Oracle系統不運用last_name的索引。select * from employee where last_name like

33、%cliton%; 在很多情況下能夠無法防止這種情況，但是一定要心中有底，通配符如此運用會降低查詢速度。然而當通配符出如今字符串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了運用： select * from employee where last_name like c%; .用Where子句交換HAVING子句防止運用HAVING子句, HAVING 只會在檢索出一切記錄之后才對結果集進展過濾. 這個處置需求排序,總計等操作. 假設能經過WHERE子句限制記錄的數目,那就能減少這方面的開銷.例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCA

34、TION 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子句將執行一個內部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (由于它對子查詢中的表執行了一個全表遍歷).

35、運用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的執行性能

36、好很多。由于1中對emp進展了full table scan,這是很浪費時間的操作。而且1中沒有用到emp的index， 由于沒有where子句。而2中的語句對emp進展的是減少范圍的查詢。.用索引提高效率索引是表的一個概念部分,用來提高檢索數據的效率，ORACLE使用了一個復雜的自平衡B-tree構造. 通常,經過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最正確途徑時, ORACLE優化器將運用索引. 同樣在結合多個表時運用索引也可以提高效率. 另一個運用索引的益處是,它提供了主鍵(primary key)的獨一性驗證。通常, 在大型表中運用索引特別有效.

37、當然,他也會發現, 在掃描小表時,運用索引同樣能提高效率. 雖然運用索引能得到查詢效率的提高,但是我們也必需留意到它的代價. 索引需求空間來存儲,也需求定期維護, 每當有記錄在表中增減或索引列被修正時, 索引本身也會被修正. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 由于索引需求額外的存儲空間和處置,那些不用要的索引反而會使查詢反響時間變慢.。定期的重構索引是有必要的。.防止在索引列上運用計算WHERE子句中，假設索引列是函數的一部分優化器將不運用索引而運用全表掃描 低效： SELECT FROM DEPT WHERE SAL

38、 * 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; 雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種

39、查詢方案更快些。第二種查詢允許Oracle對salary列運用索引，而第一種查詢那么不能運用索引。.假設有其它方法，不要運用子查詢。.外部聯接+的用法 外部聯接+按其在=的左邊或右邊分左聯接和右聯接。假設不帶+運算符的表中的一個行不直接匹配于帶+預算符的表中的任何行，那么前者的行與后者中的一個空行相匹配并被前往。利用外部聯接+，可以替代效率非常低下的 not in 運算，大大提高運轉速度。例如，下面這條命令執行起來很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);利用

40、外部聯接，改寫命令如下: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=SALE;這樣運轉速度明顯提高.盡量多運用COMMIT 事務是耗費資源的，大事務還容易引起死鎖 COMMIT所釋放的資源: 回滾段上用于恢復數據的信息. 被程序語句獲得的鎖 redo log buffer 中的空間 ORACLE為管理上述3種資源中的內部破費.用TRUNCATE替代DELETE當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復

41、的信息. 假設他沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的形狀(準確地說是恢復到執行刪除命令之前的情況)而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運轉后,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.計算記錄條數和普通的觀念相反, count(*) 比count(1)稍快 , 當然假設可以經過索引檢索,對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO).字符型字段的引號比如有的表PHONE_NO字段是CHAR型,而且創建有索引，但在WHERE條件中忘記了加引號，就不會用到索引。WHERE PHONE_NO=20202022WHER

42、E PHONE_NO=20202022.優化EXPORT和IMPORT運用較大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度;ORACLE將盡能夠地獲取他所指定的內存大小,即使在內存不滿足,也不會報錯.這個值至少要和表中最大的列相當,否那么列值會被截斷;.* 優化 Tools *.SQL 語句的執行步驟語法分析 ，分析語句的語法能否符合規范，衡量語句中各表達式的意義。 語義分析 ，檢查語句中涉及的一切數據庫對象能否存在，且用戶有相應的權限。 視圖轉換，將涉及視圖的查詢語句轉換為相應的對基表查詢語句。 表達式轉換， 將復雜的 SQL 表達式轉換為

43、較簡單的等效銜接表達式。 選擇優化器，不同的優化器普通產生不同的“執行方案 選擇銜接方式， ORACLE 有三種銜接方式，對多表銜接 ORACLE 可選擇適當的銜接方式。 選擇銜接順序， 對多表銜接 ORACLE 選擇哪一對表先銜接，選擇這兩表中哪個表做為源數據表。 選擇數據的搜索途徑， 根據以上條件選擇適宜的數據搜索途徑，如是選用全表搜索還是利用索引或是其他的方式。 運轉“執行方案.優化器與執行方案Oracle在執行一個SQL之前,首先要分析一下語句的執行計劃,然后再按執行方案去執行。分析語句的執行方案的任務是由優化器(Optimizer)來完成的 Oracle的優化器共有兩種的優化方式,即

44、基于規那么的優化方式(Rule-Based Optimization,簡稱為RBO)和基于代價的優化方式(Cost-Based Optimization,簡稱為CBO)。 A、RBO方式：優化器在分析SQL語句時,所遵照的是Oracle內部預定 的一些規那么。比如我們常見的,當一個where子句中的一列有索引時去走索引。B、CBO方式：是看語句的代價(Cost)了,這里的代價主要指Cpu和內存。優化器在判別能否用這種方式時,主要參照的是表及索引的統計信息，很多的時侯過期統計信息會令優化器做出一個錯誤的執行方案在Oracle8及以后的版本,Oracle引薦用CBO的方式。 在Oracle10g中

45、,取消了RBO的支持。.優化器與執行方案Rule:即走基于規那么的方式Choose:默許的情況下Oracle用的便是這種方式。當一個表或或索引有統計信息,那么走CBO的方式,假設表或索引沒統計信息,表又不是特別的小,而且相應的列有索引時,那么就走索引,走RBO的方式First Rows:它與Choose方式是類似的,所不同的是當一個表有統計信息時,它將是以最快的方式前往查詢的最先的幾行,從總體上減少了呼應時間All Rows:all_rows是oracle優化器默許的方式,它將選擇一種在最短時間內前往一切數據的執行方案,它將基于整體本錢的思索. first_rows_n:first_rows_

46、n是根據本錢而不是基于硬編碼的規那么來選擇執行方案.n可以是1,10,100,1000或者直接用first_rows(n) hint指定恣意正數.這里的n是我們想獲取結果集的前n條記錄,這種需求在很多分頁語句的需求中會碰到. .用EXPLAIN PLAN 分析SQL語句EXPLAIN PLAN 是一個很好的分析SQL語句的工具,它甚至可以在不執行SQL的情況下分析語句. 經過分析,我們就可以知道ORACLE是怎樣樣銜接表,運用什么方式掃描表(索引掃描或全表掃描)以及運用到的索引稱號.他需求按照從里到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN分析的結果是用縮進的格式陳列的, 最

47、內部的操作將被最先解讀, 假設兩個操作處于同一層中,帶有最小操作號的將被首先執行.NESTED LOOP是少數不按照上述規那么處置的操作, 正確的執行途徑是檢查對NESTED LOOP提供數據的操作,其中操作號最小的將被最先處置.Autotrace 解讀Current mode:對于修正的數據從數據段中讀Read-consistent mode: 讀一致性方式Physical block:物理塊如8192字節Recursive calls:嵌套調用次數.運用TKPROF 工具SQL trace 工具搜集正在執行的SQL的性能形狀數據并記錄到一個跟蹤文件中. 這個跟蹤文件提供了許多有用的信息,例

48、如解析次數.執行次數,CPU運用時間等.這些數據將可以用來優化他的系統.設置SQL TRACE在會話級別: 有效ALTER SESSION SET SQL_TRACE TRUE設置SQL TRACE 在整個數據庫有效, 他必需將SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數闡明了生成跟蹤文件的目錄再運用TKPROF對TRACE文件進展分析分析結果更加準確、清楚.在SQLPLUS 配置AUTOTRACEAUTOTRACE 參數解 釋SET AUTOTRACE OFF不能獲得AUTOTRACE報告. 這是默認的.SET AUTOTRACE ON EXPL

49、AIN僅僅顯示優化器執行計劃的AUTOTRACE報告SET AUTOTRACE ON STATISTICS僅僅顯示SQL語句執行的統計結果的AUTOTRACE報告SET AUTOTRACE ON包括上面兩項內容的AUTOTRACE報告SET AUTOTRACE TRACEONLY與SET AUTOTRACE ON類似,所有的統計和數據都在，但不可以打印.在SQLPLUS 配置AUTOTRACE1、 首先創建PLUSTRACE角色并且賦給public：Sql $ORACLE_HOME/sqlplus/admin/plustrce.sql2、 賦權限給用戶Sql grant plustrace t

50、o public預賦權的用戶名;3、以SYSTEM用戶創建PLAN_TABLE表 Sql $ORACLE_HOME/rdbms/admin/utlxplan.sqlSql create public synonym plan_table for plan_table;Sql grant all on plan_table to public;在每個用戶下設置AUTOTRACE可顯示其執行方案。.優化器與執行方案SQL select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in (ACCOUNT

51、ING,RESEARCH,SALES,OPERATIONS);Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE) 最起碼要處理全表掃描問題改動where條件的次序普通沒有用.目錄 優化根底知識 性能調整綜述 有效的運用設計 SQL語句的處置過程 Oracle的優化器 Oracle

52、的執行方案 本卷須知.性能調整綜述誰來調整系統? 什么時候調整? 建立有效調整的目的 在設計和開發時的調整 .誰來調整系統運用設計人員必需傳達運用系統的設計，使得每個人都清楚運用中的數據流動. 運用開發人員必需傳達他們選擇的實現戰略，使得語句調整的過程中能快速、容易地識別有問題的運用模塊和可疑的SQL語句. 數據庫管理人員必需仔細地監控系統活動并提供它們的資料，使得異常的系統性能可被快速得識別和糾正. 硬件/軟件管理人員必需傳達系統的硬件、軟件配置并提供它們的資料，使得相關人員能有效地設計和管理系統。 .誰來調整系統與系統涉及的每個人都在調整過程中起某些作用，當上面提及的那些人員傳達了系統的特

53、性并提供了它們的資料，調整就能相對的容易和更快一些。 現實上的結果是：數據庫管理員對調整負有全部或主要的責任。但是，數據庫管理員很少有適宜的系統方面的資料，而且，在很多情況下，數據庫管理員往往是在實施階段才介入數據庫，這就給調整任務帶來許多負面的影響，由于在設計階段的缺陷是不能經過DBA的調整而得以處理，而設計階段的缺陷往往對數據庫性能呵斥極大的影響。 在真正成熟的開發環境下，開發人員作為純代碼編寫人員時，對性能的影響最小，此時大部分的任務應由運用設計人員完成，而且數據庫管理員往往在前期的需求管理階段就介入，為設計人員提供必要的技術支持。 調整并不是數據庫管理員的專利，相反大部分應該是設計人員

54、和開發人員的任務，這就需求設計人員和開發人員詳細必要的數據庫知識，這樣才干組成一個高效的團隊，然而現實上往往并非如此。 .誰來調整系統與系統涉及的每個人都在調整過程中起某些作用，當上面提及的那些人員傳達了系統的特性并提供了它們的資料，調整就能相對的容易和更快一些。 現實上的結果是：數據庫管理員對調整負有全部或主要的責任。但是，數據庫管理員很少有適宜的系統方面的資料，而且，在很多情況下，數據庫管理員往往是在實施階段才介入數據庫，這就給調整任務帶來許多負面的影響，由于在設計階段的缺陷是不能經過DBA的調整而得以處理，而設計階段的缺陷往往對數據庫性能呵斥極大的影響。 在真正成熟的開發環境下，開發人員

55、作為純代碼編寫人員時，對性能的影響最小，此時大部分的任務應由運用設計人員完成，而且數據庫管理員往往在前期的需求管理階段就介入，為設計人員提供必要的技術支持。 調整并不是數據庫管理員的專利，相反大部分應該是設計人員和開發人員的任務，這就需求設計人員和開發人員詳細必要的數據庫知識，這樣才干組成一個高效的團隊，然而現實上往往并非如此。 .什么時候調整系統多數人以為當用戶覺得性能差時才進展調整，這對調整過程中運用某些最有效的調整戰略來說往往是太遲了。此時，假設他不情愿重新設計運用的話，他只能經過重新分配內存(調整SGA)和調整I/O的方法或多或少地提高性能。Oracle提供了許多特性，這些特性只需運用

56、到正確地設計的系統中時才可以很大地提高性能。 運用設計人員需求在設計階段設置運用的性能期望值。然后在設計和開發期間，運用設計人員應思索哪些Oracle 特性可以對系統有益處，并運用這些特性。 經過良好的系統設計，他就可以在運用的生命周期中消除性能調整的代價和波折。以下圖闡明在運用的生命周期中調整的相對代價和收益，最有效的調整 時間是在設計階段。在設計期間的調整能以最低的代價給他最大的收益。 .什么時候調整系統圖: 在運用生命周期中調整的代價 .什么時候調整系統圖: 在運用生命周期中調整的收益 .調整的目的不論正在設計或維護系統，應該建立專門的性能目的，它使他知道何時要作調整。調整他的系統的最有

57、效方法如下： 當設計系統時思索性能 調整操作系統的硬件和軟件 識別性能瓶頸 確定問題的緣由 采取糾正的動作 當他設計系統時，制定專門的目的；例如，呼應時間小于秒。當應用不能滿足此目的時，識別呵斥變慢的瓶頸例如，I/O競爭，確定緣由，采取糾正動作。在開發期間，他應測試運用研討，確定在采取運用之前能否滿足設計的性能目的。 .調整的目的調整通常是一系列開銷。一旦確定了瓶頸，能夠要犧牲一些其它方面的目的來到達所要的結果。例如，假設I/O有問題，他能夠需求更多內存或磁盤。假設不能夠買，他能夠要限制系統的并發性，來獲取所需的性能。假設他曾經明確地定義了性能的目的，那用什么來交換高性能的決策就變的很容易的，

58、由于曾經確定了哪些方面是最重要的，假設我的目的為高性能，能夠犧牲一些空間資源。 隨著運用的越來越龐大，硬件性能的提高，全面的調整運用逐漸變成代價高昂的行為，在這樣情況下，要獲得最大的投入/效率之比，較好的方法是調整運用的關鍵部分，使其到達比較高的性能，這樣從總體上來說，整個系統的性能也是比較高的。這也就是有名的20/80原那么，調整運用的20%(關鍵部分)，能處理80%的問題。 .SQL 調整的目的去掉不用要的大型表的全表掃描。緩存小型表的全表掃描。校驗優化索引的運用。檢驗優化的銜接技術。以上目的義務將占據SQL調整90%以上的任務。.在設計和開發時調整良好設計的系統可防止在運用生命周期中產生

59、性能問題。系統設計人員和運用開發人員必需了解Oracle的查詢處置機制以便寫出高效的SQL語句。“有效的運用設計討論了他的系統中各種可用的配置，以及每種配置更適宜哪種類型的運用。“優化器討論了Oracle的查詢優化器，以及如何寫語句以獲取最快的結果。 .在設計和開發時調整當設計他的系統時，運用以下優化性能的準那么： - 消除客戶機效力器運用中不用要的網絡傳輸。運用存儲過程。 - 運用適宜他系統的Oracle效力器選件例如，并行查詢或分布式數據庫。 - 除非他的運用有特殊的需求，否那么運用缺省的Oracle鎖。 - 利用數據庫記住運用模塊，以便能以每個模塊為根底來追蹤性能。 - 選擇他的數據塊的

60、最正確大小。 - 原那么上來說大一些的性能較好。 - 分布他的數據，使得一個節點運用的數據本地存貯在該節點中。 .目錄 優化根底知識 性能調整綜述 有效的運用設計 SQL語句的處置過程 Oracle的優化器 Oracle的執行方案 本卷須知.有效的運用設計將最常用的運用分為2種類型：聯機事務處置類型(OLTP)，決策支持系統(DSS)。 聯機事務處置(OLTP) 該類型的運用是高吞吐量，插入、更新、刪除操作比較多的系統，這些系統以不斷增長的大容量數據為特征，它們提供應成百用戶同時存取，典型的OLTP系統是訂票系統，銀行的業務系統，訂單系統。OTLP的主要目的是可用性、速度、并發性和可恢復性。