一·、前言：這篇博文內容非原創，是我們公司的架構師給我們做技術培訓的時候講的內容，我稍微整理了下，借花獻佛。這篇博文只是做一個大概的科普介紹，畢竟SQL優化的知識太大了，幾乎可以用一本書來介紹。另外，博主對SQL優化也是剛剛接觸，也有很多不了解的地方，說的不對的地方，還請大家指正，共勉！ 二、oracle服務器，所謂oracle服務器指的是一個數據庫管理系統，它包括一個oracle實例（動態）和一個oracle數據庫（靜態）。 oracle實例是一個運行的概念，提供了一種訪問數據庫的方式，由SGA和一些后臺服務進程組成，DBWn PMON CKPT LGWR SMON是必備的后臺進程，而ad queue，rac，shared?server，ad replication則是可選的。連接到oracle實例有三種途徑: ?1、如果用戶登陸到運行oracle實例的操作系統上，則通過進程間通信進行訪問 ?2、C/S結構訪問 ?3、三層結構 oracle數據庫是一個被統一處理的數據的集合，從物理角度來說包括三類文件，數據文件、控制文件、重做日志文件。 PMON監控其他后臺進程，并且在服務器進程或者轉發器進程異常終止之后執行恢復。pmon負責清理數據庫的buffer cache，并且釋放客戶端進程使用的資源。比如說pmon重置當前活動的事務表，釋放不需要的locks，清理進程id（隱式回滾） SMON負責系統級別的清理工作 1.執行實例恢復。 2.恢復異常的transaction（實例恢復期間 file or tablespace被置為offline狀態），smon會在他們置為online的時候執行恢復。 3.清理不使用的臨時segments。比如當創建index的時候需要分配臨時extent，如果操作失敗，smon負責清理這些臨時空間。 4.在使用字典管理表空間的時候合并連續的空閑extent。smon為定期監控。其他進程如果需要的話也會通知smon。 Database Writer Process (DBWn)負責將更改的buffer 從db buffer cache中寫到datafile中去，通過一個dbwn進程（dbw0）就足夠了，但是也可以配置更多額外的dbwr進程，它可以提升頻繁更改的數據庫系統的性能。當然額外的dbw進程對于單處理器系統是沒有任何用處的。 Log Writer Process (LGWR)管理這redo log buffer。lgwr寫buffer中連續的部分到online redo log 中。因為分離了更改數據庫buffer的任務：dbwn散列寫buffer到disk中，執行快速的順序寫到redo，所以數據庫提升了性能。 1.用戶提交了一個事務。 2. redo log switch 發生 3. 從上一次lgwr寫操作開始已經過去了3秒 4. redo log buffer 三分之一滿或者已經存儲了1mb的數據量 5. dbwn必須寫更改的數據到磁盤上面。 CKPT更新控制文件以及數據文件頭部的檢查點信息，并且給dbwn信號去寫數據塊到磁盤上面。檢查點信息包括：檢查點位置，scn，恢復時開始的redo log 位置，類似這樣的信息。 Recoverer Process (RECO)在分布式數據庫中，reco進程自動的解決分布式事務發生錯誤的情況。 三、分析語句階段優化 硬解析:SQL語句從用戶進程提交到oracle，經過分析裝載到共享SQL區域（shared pool）。如果SQL語句不在shared pool，需要進行語句解析，即硬解析。 軟解析:如果SQL語句在shared pool，就可以直接進入執行階段。 優化技巧1:語法分析需要耗費很多資源，要盡量避免進行語法分析，即硬解析。 優化技巧2:即軟解析時，當Shared pool沒有空間時，oracle會根據LRU算法(最近最少使用頁面置換算法)更新SQL區域，所以適當增加shared_pool,可以存放更多解析后的SQL來提高效率。 Oracle Optimizer(查詢優化器)：是Oracle在執行SQL之前分析語句的工具,Oracle在執行一個SQL之前,首先要分析一下語句的執行計劃,然后再按執行計劃去執行，主要有以下兩種方式:

• RBO(rule-base optimizer):優化器遵循Oracle內部預定的規則,句法驅動和數據字典驅動。
• CBO(cost-based optimizer):依據語句執行的代價，主要指對CPU和內存的占用，優化器在判斷是否使用CBO時，要參照表和索引的統計信息統計表驅動，統計信息要在對表做analyze后才會有。

優化技巧3:Oracle8及以后版本，推薦用CBO方式，Oracle10G此功能已經很強大。 Oracle優化器的優化模式主要有五種：?

• Choose：默認模式。根據表或索引的統計信息，如果有統計信息，則使用CBO方式；如果沒有統計信息，相應列有索引，則使用RBO方式。
• Rule：基于規則優化，忽略任何統計信息
• First rows：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢結果，以獲得最佳響應時間。
• First_rows_n：與Choose類似。不同的是如果表有統計信息，它將以最快的方式返回查詢的前幾行，以獲得最佳響應時間。
• All rows：完全基于CBO的模式。當一個表有統計信息時，以最快方式返回表所有行，以獲得最大吞吐量。沒有統計信息則使用RBO方式。

Oracle Optimizer 運行級別怎么修改？

• Instance級:修改啟動參數在init<SID>.ora文件中設定OPTIMIZER_MODE，需要數據庫重啟
• Session級:（JDBC或者Hibernate或者一次連接），通過alter session set optimizer_mode = value修改，忽略instance級
• Statement級:通過在SQL語句中加如Hint(隱語)實現,表明對語句塊選擇基于開銷的優化方法,并獲得最佳響應時間,忽略instance級和session級

? ? ? ? ? ? ? ?eg:SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; ?……來設定

DBMS_STATS包工具做CBO代價分析 DBMS_STATS：dbms_stats包下面一共有40多個存儲過程. 對執行計劃的生成非常重要。常見的有： 分析數據庫（包括所有的用戶對象和系統對象）：gather_database_stats
分析用戶所有的對象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
刪除數據庫統計信息：delete_database_stats
刪除用戶方案統計信息：delete_schema_stats
刪除表統計信息：delete_table_stats
刪除索引統計信息：delete_index_stats
刪除列統計信息：delete_column_stats
設置表統計信息：set_table_stats
設置索引統計信息：set_index_stats
設置列統計信息：set_column_stats
可以查看表 DBA_TABLES來查看表是否與被分析過，如：
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
通常使用的比較多的主要是DBMS_STATS.GATHER_TABLE_STATS和 DBMS_STATS.GATHER_INDEX_STATS。 使用步驟: 1、首先創建一個分析表，該表是用來保存之前的分析值。 SQL> begin
2 dbms_stats.create_stat_table(ownname => 'scott',stattab => 'STAT_TABLE');
3 end;
4 / 2、分析表信息。可以參考這篇博客 exec?dbms_stats.gather_schema_stats(
ownname??????????=>?'SCOTT',
options??????????=>?'GATHER?AUTO',
estimate_percent?=>?dbms_stats.auto_sample_size,
method_opt???????=>?'for?all?columns?size?repeat',
degree???????????=>?15
) 3、將執行計劃導入到STAT_TABLE中 exec?dbms_stats.export_schema_stats(ownname?=>?'scott',stattab?=>?'stat_table')?;? 4、查看執行計劃表 select * from ?stat_table; 四、執行計劃階段優化 全表掃描(Full Table Scans) Oracle讀取表中所有的行，并檢查每一行是否滿足語句的WHERE限制條件,采用多塊讀的方式使一次I/O能讀取多塊數據塊，而不是只讀取一個數據塊，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描，注意,只有全表掃描才能使用多塊讀的操作。 優化技巧4:通過設置db_block_multiblock_read_count和db_block_size來適當增加一次I/O可讀的數據塊。 優化技巧5:避免使用select * from 減少物理讀，邏輯讀（* 要走系統字典表，查看這張表有哪些字段）,最好制定需要返回的字段。 優化技巧6:較小的表使用全表掃描，效率更高；較大的表應避免全表掃描，除非涉及全表記錄10%以上的查詢；避免給記錄數少的表建立索引，避免索引開銷。 優化技巧7:指定過濾謂詞 where，盡可能縮小查詢范圍（能過濾掉大部分記錄的字段應該放在右邊,因為sql語句是從右至左執行的）。 通過ROWID的表存取(Table Access by ROWID)

ROWID記錄了記錄行所在的數據文件、數據塊以及行在該塊中的位置，所以通過ROWID來存取數據可以快速定位到目標數據上，可以說是整個數據庫都在用的索引，是Oracle存取單行數據的最快方法。這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個數據塊。

select empno from emp where rowid='AAAR3sAAEAAAACXAAA';

?

索引掃描(Index Scan) 通過index查找到數據對應的rowid值(對于非唯一索引可能返回多個rowid值)，然后根據rowid直接從表中得到具體的數據，這種查找方式稱為索引掃描或索引查找(index lookup)。

索引唯一掃描(index unique scan)

通過唯一索引查找一個數值經常返回單個ROWID,如果存在UNIQUE 或PRIMARY KEY 約束（約束只有一行記錄匹配），Oracle實現索引唯一性掃描。

select empno from emp where empno=7369;

?

索引范圍掃描(index range scan)

使用一個索引存取多行數據，在唯一索引上使用索引范圍掃描的典型情況下是在謂詞(where限制條件)中使用了范圍操作符，有以下三種情況會導致引起索引范圍掃描:

• 在唯一索引列上使用了range操作符(> < <> >= <= between)
• 在組合索引上，只使用部分列進行查詢，導致查詢出多行
• 對非唯一索引列上進行的任何查詢。

select empno from emp where empno>7369;

?

索引全掃描(index full scan)

什么時候會引起索引全掃描呢?當不使用謂詞邏輯where;所有查詢結果數據都必須從索引中可以直接得到;需要排序操作，比如order by。

select empno from emp order by empno;

index full scan使用單塊讀方式有序讀取索引塊，產生db file sequential reads事件，當采用該方式讀取大量索引全掃描，效率低下

?

索引快速掃描(index fast full scan)

與索引全掃描很相似，只是不涉及排序動作。

select /*+ index_ffs(emp pk_emp) */empno from emp; //對指定的表執行快速全索引掃描,而不是全表掃描的辦法. ?

index fast full scan使用多塊讀的方式讀取索引塊，產生db file scattered reads 事件，讀取時高效，但為無序讀取

?

?

優化技巧7:對于只從表中查詢出總行數的2%到4%行的表時，可以考慮創建索引。
優化技巧8:不要將那些頻繁修改的列作為索引列，頻繁修改會導致不必要的索引開銷。
優化技巧9:不要使用包含函數或操作符放入WHERE從句中的關鍵字作為索引，會導致索引失效，可以考慮使用函數索引。
優化技巧10:在組合索引中，沒有按照建立時的索引關鍵字順序描述，比如xyz變成了yxz，也會導致索引失效。
優化技巧11:如果在表中要建立索引的一列或多列上使用了函數或表達式，則創建的是基于函數的索引。基于函數的索引預先計算函數或表達式的值，并將結果存儲在索引中。B樹索引和bitmap索引也是函數索引
優化技巧12:排序動作能不做就不做，增加系統開銷的同時還會使快速索引失效。

?備注:

函數索引
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
則可以在查詢語句中使用函數索引：
SELECT a
FROM table_1
WHERE a + b * (c - 1) < 100;

五、多表關聯查詢操作 任何N（N大于2）張表之間的操作都將轉化為兩張表之間的關聯操作，查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數據的集合，也可以是表的部分行數據的集合，或者說集合篩選后的集合都成為row source。無論連接操作符如何，典型的連接類型共有3種：

排序合并連接(Sort Merge Join (SMJ))

select aa.CREATEPERSONNAME, bb.CREATEPERSON from tbl_comm_commonticket aa, tbl_ybgz_ticket bb where aa.CREATEPERSONNAME = bb.CREATEPERSON order by aa.CREATEPERSONNAME, bb.CREATEPERSON

排序屬于代價很高的操作，特別對于大表。因此經常避免使用排序合并連接方法，但是如果2個row source都已經預先排序（比如primary Key索引），則這種連接方法可以選用。

?

嵌套循環(Nested Loops (NL))

分為驅動表(OUTER TABLE)和內層表(INNER TABLE)。因為嵌套循環，所以外層循環的次數越少越好，因此一般將數據量較小表或滿足條件的row source較小的表作為驅動表(用于外層循環)的理論依據。

select /*+USE_NL(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

嵌套循環返回已經連接的行，而不必等待所有的連接操作處理完才返回數據 ，所以提高了響應速度。如果OUTER TABLE比較小，并且在INNER TABLE上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。另外，這種連接方式，是在RBO優化器中。

哈希連接(Hash Join)

散列基本原理是：使用一個下標范圍比較大的數組來存儲元素。可以設計一個函數（哈希函數，也叫做散列函數），使得每個元素的關鍵字都與一個函數值（即數組下標，hash值）相對應，于是用這個數組單元來存儲這個元素；但是，不能夠保證每個元素的關鍵字與函數值是一一對應的，因此極有可能出現對于不同的元素，卻計算出了相同的函數值，這樣就產生了“沖突”，換句話說，就是把不同的元素分在了相同的“類”之中。 總的來說，“直接定址”與“解決沖突”是哈希表的兩大特點。

散列連接是CBO 做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配的行。

hash join只有在CBO方式下可以使用；Oracle初始化參數HASH_JOIN_ENABLED決定是否啟用hash join；pga_aggregate_target指定散列連接可用的內存大小；盡量使內層表生成的散列表最小，最好能夠全部載入內存；主要用于等值連接。

select /*+USE_HASH(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

?

六、其他

優化技巧13:避免使用不確定操作符<>,!= 或者?where 子句中使用 or 來連接條件，因為會引起全表掃描; or 可以用union或者(union all) 代替。

select * from emp where empno>7369 union select * from emp where empno<7369;

優化技巧14:Where子句中出現IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執行全表掃描。可以在設計表時，對索引列設置為NOT NULL。這樣就可以用其他操作來取代判斷NULL的操作。
優化技巧15:當通配符“%”或者“_”作為查詢字符串的第一個字符時，索引不會被使用 。
優化技巧16:對數據類型不同的列進行比較時，會使索引失效。
優化技巧17:UNION操作符會對結果進行篩選，消除重復，數據量大的情況下可能會引起磁盤排序。如果不需要刪除重復記錄，應該使用UNION ALL。

SQL> select deptno from emp union select deptno from dept; SQL> select deptno from emp union all select deptno from dept;

優化技巧18:Order By語句中的非索引列會降低性能，可以通過添加索引的方式處理。嚴格控制在Order By語句中使用表達式。
優化技巧19:相同的Sql語句，要保證查詢字符完全相同，大小寫，空格位置，利用shared_pool，防止相同的Sql語句被多次分析，使用變量綁定。
優化技巧20:調整SQL語句的目的是為了在執行中使資源的使用減少到最小。除了選擇使用不同的SQL語法來優化執行代價，還可以通過調整執行順序優化SQL。
優化技巧21:Oracle在執行IN子查詢時，首先執行子查詢，將查詢結果放入臨時表再執行主查詢。而EXIST則是首先檢查主查詢，然后運行子查詢直到找到第一個匹配項。因此NOT EXISTS比NOT IN效率稍高，相應更快。但是(NOT) EXISTS 不等于(NOT) IN。

SQL>select * from emp where deptno in (select deptno from dept); SQL>select * from emp where exists (select deptno from dept where emp.deptno=dept.deptno);

優化技巧22:可以多使用視圖進行軟解析，視圖只是把你要用的sql進行保存而已，你需要擔心的是視圖中的sql會不會效率太低，而不用擔心視圖的耗時。
優化技巧23:適當的時候強制使用rule會獲得更高效率；調試SQL時關注執行計劃和執行代價。
優化技巧24:避免視圖嵌套使用，尤其是針對視圖排序，篩選等操作。
優化技巧25:不同版本數據庫的執行計劃差別可能很大。
優化技巧26:不是只有select..是查詢，所有的DML操作都含有查詢過程。

??

七、SQL分析工具 EXPLAIN PLAN 使用步驟： 1、SQL> explain plan for select * from emp,dept where emp.deptno=dept.deptno; 2、select * from table(dbms_xplan.display); 3、

?

AUTOTRACE 使用步驟: 1、set autotrace on (可能會報Cannot SET AUTOTRACE的錯誤，參考這篇博客解決) 2、select * from emp,dept where emp.deptno=dept.deptno; 3、

?

轉載于:https://www.cnblogs.com/jmcui/p/7353874.html

總結

以上是生活随笔為你收集整理的SQL优化二(SQL性能调优)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。