一梨与、慢SQL的排查與分析

1瓤狐、慢sql的定位
使用Druid監(jiān)控
oracle的ARW文件
mysql慢日志或使用命令show processlist

2措近、執(zhí)行計(jì)劃

以oracle為例
第一種方式：
1浇揩、使用explain plan for 執(zhí)行某sql

explain plan for
select ename,dname from emp, dept
where emp.deptno=dept.deptno
and dept.dname in
 ('ACCOUNTING','RESEARCH','SALES','OPERATIONS') ;

2熊昌、查詢執(zhí)行計(jì)劃
select * from table(dbms_xplan.display);

輸出結(jié)果為：

+----------------------------------------------------------------------------------------+
|PLAN_TABLE_OUTPUT                                                                       |
+----------------------------------------------------------------------------------------+
|Plan hash value: 844388907                                                              |
|                                                                                        |
|----------------------------------------------------------------------------------------|
|| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     ||
|----------------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 ||
||   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 ||
||*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 ||
||   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 ||
||*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 ||
||   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 ||
|----------------------------------------------------------------------------------------|
|                                                                                        |
|Predicate Information (identified by operation id):                                     |
|---------------------------------------------------                                     |
|                                                                                        |
|   2 - filter("DEPT"."DNAME"='ACCOUNTING' OR "DEPT"."DNAME"='OPERATIONS' OR             |
|              "DEPT"."DNAME"='RESEARCH' OR "DEPT"."DNAME"='SALES')                      |
|   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")                                           |
|       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")                                           |
+----------------------------------------------------------------------------------------+

第二種方式：
可以在plsql中新建解釋計(jì)劃窗口绽榛，將要查看執(zhí)行計(jì)劃的sql放入，執(zhí)行后查看結(jié)果

3婿屹、如何讀懂SQL Execution Plan
如果要了解執(zhí)行計(jì)劃和執(zhí)行順序灭美，必須理解執(zhí)行計(jì)劃的父子關(guān)系。執(zhí)行計(jì)劃是一個(gè)樹狀結(jié)構(gòu)昂利，頂層的STATEMENT是這棵樹的根届腐。父子關(guān)系按照如下的樹狀結(jié)構(gòu)組織：
PARENTFIRST CHILDSECOND CHILD
在這個(gè)例子里，F(xiàn)IRST CHILD最先執(zhí)行蜂奸，然后是SECOND CHILD犁苏，這兩個(gè)步驟執(zhí)行完畢后，執(zhí)行PARENT扩所。下面是一個(gè)更多層次的結(jié)構(gòu)：
PARENT1FIRST CHILDFIRST GRANDCHILDSECOND CHILD

注意這個(gè)執(zhí)行計(jì)劃的最左邊的兩個(gè)列围详，第一個(gè)列是步驟的ID，第二個(gè)列是父步驟的ID祖屏。執(zhí)行從ID=0的行開始：
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
這個(gè)步驟沒有父步驟助赞，有一個(gè)子步驟（ID=1），所以這個(gè)ID＝1的步驟必須在執(zhí)行步驟0之前執(zhí)行袁勺。繼續(xù)觀察ID=1的步驟：
1 0 HASH JOIN (Cost=3 Card=8 Bytes=248)
這個(gè)步驟是ID=0的步驟的子步驟雹食，該步驟有2個(gè)子步驟：ID=2和ID=3，因此ID=2和ID=3的步驟必須在ID=1的步驟之前執(zhí)行期丰。再來檢查ID=2的步驟：
2 1 TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=1 Card=3 Bytes=36)
這個(gè)步驟是ID=1的步驟的子步驟婉徘，并且該步驟沒有任何子步驟。因此該步驟是這個(gè)SQL語句第一個(gè)執(zhí)行的步驟咐汞，這個(gè)步驟產(chǎn)生的結(jié)果集會(huì)提供給ID=1的步驟。這個(gè)步驟是對(duì)表DEPT進(jìn)行全表掃描儒鹿，這個(gè)步驟的COST＝1化撕。
ID=1的步驟也依賴ID=3的步驟：
3 1 TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=16 Bytes=304)
這個(gè)步驟是ID=1的步驟的第二個(gè)子步驟，沒有任何子步驟约炎，在這個(gè)語句中植阴，是第二個(gè)被執(zhí)行的步驟蟹瘾。
ID=1的步驟將ID=3和ID=3的步驟的結(jié)果集進(jìn)行HASH 連接，然后把結(jié)果交給ID=0的步驟掠手，就完成了本語句的執(zhí)行憾朴。

二、常見SQL優(yōu)化方法

（一）創(chuàng)建合適的索引

（二）操作符優(yōu)化

1喷鸽、IN 操作符

用IN寫出來的SQL的優(yōu)點(diǎn)是比較容易寫及清晰易懂众雷，這比較適合現(xiàn)代軟件開發(fā)的風(fēng)格。但是用IN的SQL性能總是比較低的做祝，從Oracle執(zhí)行的步驟來分析用IN的SQL與不用IN的SQL有以下區(qū)別：

ORACLE試圖將其轉(zhuǎn)換成多個(gè)表的連接砾省，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄混槐，如果轉(zhuǎn)換成功則直接采用多個(gè)表的連接方式查詢编兄。由此可見用IN的SQL至少多了一個(gè)轉(zhuǎn)換的過程。一般的SQL都可以轉(zhuǎn)換成功声登，但對(duì)于含有分組統(tǒng)計(jì)等方 面的SQL就不能轉(zhuǎn)換了狠鸳。

推薦方案：在業(yè)務(wù)密集的SQL當(dāng)中盡量不采用IN操作符，用EXISTS 方案代替悯嗓。

2件舵、NOT IN操作符

此操作是強(qiáng)列不推薦使用的，因?yàn)樗荒軕?yīng)用表的索引绅作。

推薦方案：用NOT EXISTS 方案代替

3芦圾、IS NULL 或IS NOT NULL操作（判斷字段是否為空）

判斷字段是否為空一般是不會(huì)應(yīng)用索引的，因?yàn)樗饕遣凰饕罩档摹?/p>

推薦方案：用其它相同功能的操作運(yùn)算代替俄认，如：a is not null 改為 a>0 或a>’’等个少。不允許字段為空，而用一個(gè)缺省值代替空值眯杏，如申請(qǐng)中狀態(tài)字段不允許為空夜焦，缺省為申請(qǐng)。

4岂贩、> 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情況下是不用調(diào)整的茫经，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化萎津，如一個(gè)表有100萬記錄卸伞，一個(gè)數(shù)值型字段A，30萬記錄的A=0锉屈，30萬記錄的A=1荤傲，39萬記錄的A=2，1萬記錄的A=3颈渊。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了遂黍，因?yàn)锳>2時(shí)ORACLE會(huì)先找出為2的記錄索引再進(jìn)行比較终佛，而A>=3時(shí)ORACLE則直接找到=3的記錄索引焦除。

5达址、LIKE操作符

LIKE操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢歇式，但是如果用得不好則會(huì)產(chǎn)生性能上的問題芯咧，如LIKE ‘%5400%’ 這種查詢不會(huì)引用索引牙捉，而LIKE ‘X5400%’則會(huì)引用范圍索引。

一個(gè)實(shí)際例子：用YW_YHJBQK表中營(yíng)業(yè)編號(hào)后面的戶標(biāo)識(shí)號(hào)可來查詢營(yíng)業(yè)編號(hào) YY_BH LIKE ‘%5400%’ 這個(gè)條件會(huì)產(chǎn)生全表掃描唬党，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會(huì)利用YY_BH的索引進(jìn)行兩個(gè)范圍的查詢鹃共，性能肯定大大提高。

6驶拱、UNION操作符

UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄霜浴，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果蓝纲。實(shí)際大部分應(yīng)用中是不會(huì)產(chǎn)生重復(fù)的記錄阴孟，最常見的是過程表與歷史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果税迷，再用排序空間進(jìn)行排序刪除重復(fù)的記錄永丝，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會(huì)導(dǎo)致用磁盤進(jìn)行排序箭养。

推薦方案：采用UNION ALL操作符替代UNION慕嚷，因?yàn)閁NION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

二毕泌、SQL書寫的影響

1喝检、同一功能同一性能不同寫法SQL的影響。

如一個(gè)SQL在A程序員寫的為 Select * from zl_yhjbqk

B程序員寫的為 Select * from dlyx.zl_yhjbqk（帶表所有者的前綴）

C程序員寫的為 Select * from DLYX.ZLYHJBQK（大寫表名）

D程序員寫的為 Select * from DLYX.ZLYHJBQK（中間多了空格）

以上四個(gè)SQL在ORACLE分析整理之后產(chǎn)生的結(jié)果及執(zhí)行的時(shí)間是一樣的撼泛，但是從ORACLE共享內(nèi)存SGA的原理挠说，可以得出ORACLE對(duì)每個(gè)SQL 都會(huì)對(duì)其進(jìn)行一次分析，并且占用共享內(nèi)存愿题，如果將SQL的字符串及格式寫得完全相同损俭，則ORACLE只會(huì)分析一次，共享內(nèi)存也只會(huì)留下一次的分析結(jié)果潘酗，這不僅可以減少分析SQL的時(shí)間杆兵，而且可以減少共享內(nèi)存重復(fù)的信息，ORACLE也可以準(zhǔn)確統(tǒng)計(jì)SQL的執(zhí)行頻率仔夺。

2拧咳、WHERE后面的條件順序影響

WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會(huì)產(chǎn)生直接的影響。如：

Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

以上兩個(gè)SQL中dy_dj（電壓等級(jí)）及xh_bz（銷戶標(biāo)志）兩個(gè)字段都沒進(jìn)行索引囚灼，所以執(zhí)行的時(shí)候都是全表掃描骆膝，第一條SQL的dy_dj = '1KV以下'條件在記錄集內(nèi)比率為99%，而xh_bz=1的比率只為0.5%灶体，在進(jìn)行第一條SQL的時(shí)候99%條記錄都進(jìn)行dy_dj及xh_bz的比較阅签，而在進(jìn)行第二條SQL的時(shí)候0.5%條記錄都進(jìn)行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低蝎抽。

3政钟、查詢表順序的影響

在FROM后面的表中的列表順序會(huì)對(duì)SQL執(zhí)行性能影響，在沒有索引及ORACLE沒有對(duì)表進(jìn)行統(tǒng)計(jì)分析的情況下樟结，ORACLE會(huì)按表出現(xiàn)的順序進(jìn)行鏈接养交，由此可見表的順序不對(duì)時(shí)會(huì)產(chǎn)生十分耗服物器資源的數(shù)據(jù)交叉。（注：如果對(duì)表進(jìn)行了統(tǒng)計(jì)分析瓢宦，ORACLE會(huì)自動(dòng)先進(jìn)小表的鏈接碎连，再進(jìn)行大表的鏈接）

三、SQL語句索引的利用

1驮履、操作符優(yōu)化（同上）

2鱼辙、對(duì)條件字段的一些優(yōu)化

采用函數(shù)處理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’玫镐，優(yōu)化處理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)倒戏， 優(yōu)化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

進(jìn)行了顯式或隱式的運(yùn)算的字段不能進(jìn)行索引，如：ss_df+20>50恐似，優(yōu)化處理：ss_df>30

‘X’ || hbs_bh>’X5400021452’杜跷，優(yōu)化處理：hbs_bh>’5400021542’

sk_rq+5=sysdate，優(yōu)化處理：sk_rq=sysdate-5

hbs_bh=5401002554矫夷，優(yōu)化處理：hbs_bh=’ 5401002554’葛闷，注：此條件對(duì)hbs_bh 進(jìn)行隱式的to_number轉(zhuǎn)換，因?yàn)閔bs_bh字段是字符型口四。

條件內(nèi)包括了多個(gè)本表的字段運(yùn)算時(shí)不能進(jìn)行索引孵运，如：ys_df>cx_df，無法進(jìn)行優(yōu)化

qc_bh || kh_bh=’5400250000’蔓彩，優(yōu)化處理：qc_bh=’5400’ and kh_bh=’250000’

四治笨、其他

ORACLE的提示功能是比較強(qiáng)的功能，也是比較復(fù)雜的應(yīng)用赤嚼，并且提示只是給ORACLE執(zhí)行的一個(gè)建議旷赖，有時(shí)如果出于成本方面的考慮ORACLE也可能不會(huì)按提示進(jìn)行。根據(jù)實(shí)踐應(yīng)用更卒，一般不建議開發(fā)人員應(yīng)用ORACLE提示等孵，因?yàn)楦鱾€(gè)數(shù)據(jù)庫及服務(wù)器性能情況不一樣，很可能一個(gè)地方性能提升了蹂空，但另一個(gè)地方卻下降了俯萌，ORACLE在SQL執(zhí)行分析方面已經(jīng)比較成熟果录，如果分析執(zhí)行的路徑不對(duì)首先應(yīng)在數(shù)據(jù)庫結(jié)構(gòu)（主要是索引）、服務(wù)器當(dāng)前性能（共享內(nèi)存咐熙、磁盤文件碎片）弱恒、數(shù)據(jù)庫對(duì)象（表、索引）統(tǒng)計(jì)信息是否正確這幾方面分析棋恼。

http://www.cnblogs.com/eric_ibm/archive/2012/01/09/oracle_skill.html

ORACLE百萬記錄SQL語句優(yōu)化技巧

1.對(duì)查詢進(jìn)行優(yōu)化返弹，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引爪飘。

2.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷义起，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0师崎，確保表中num列沒有null值默终，然后這樣查詢：

select id from t where num=0

3.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描抡诞。

4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件穷蛹，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用昼汗，否則會(huì)導(dǎo)致全表掃描肴熏，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導(dǎo)致全表掃描：

select id from t where name like '%abc%'

若要提高效率顷窒，可以考慮全文檢索蛙吏。

7.如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描鞋吉。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量鸦做，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇谓着。然而泼诱，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的赊锚，因而無法作為索引選擇的輸入項(xiàng)治筒。如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描舷蒲。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作耸袜，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)='abc' // oracle總有的是substr函數(shù)牲平。

select id from t where datediff(day,createdate,'2005-11-30')=0 //查過了確實(shí)沒有datediff函數(shù)堤框。

應(yīng)改為:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' //

oracle 中時(shí)間應(yīng)該把char 轉(zhuǎn)換成 date 如： createdate >= to_date('2005-11-30','yyyy-mm-dd')

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引蜈抓。

11.在使用索引字段作為條件時(shí)启绰，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引资昧，否則該索引將不會(huì)被使用酬土，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12.不要寫一些沒有意義的查詢格带，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的刹枉，應(yīng)改成這樣：

create table #t(...)

13.很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引對(duì)查詢都有效叽唱，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)微宝，SQL查詢可能不會(huì)去利用索引棺亭，如一表中有字段sex，male蟋软、female幾乎各一半镶摘，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

15.索引并不是越多越好岳守，索引固然可以提高相應(yīng)的 select 的效率凄敢，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引湿痢，所以怎樣建索引需要慎重考慮涝缝，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)譬重，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要拒逮。

16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序臀规，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整滩援，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列塔嬉，那么需要考慮是否應(yīng)將該索引建為 clustered 索引玩徊。

17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型邑遏，這會(huì)降低查詢和連接的性能佣赖，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符记盒，而對(duì)于數(shù)字型而言只需要比較一次就夠了憎蛤。

18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間俩檬，其次對(duì)于查詢來說萎胰，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

19.任何地方都不要使用 select * from t 棚辽，用具體的字段列表代替“*”技竟，不要返回用不到的任何字段。

20.盡量使用表變量來代替臨時(shí)表屈藐。如果表變量包含大量數(shù)據(jù)榔组，請(qǐng)注意索引非常有限（只有主鍵索引）。

21.避免頻繁創(chuàng)建和刪除臨時(shí)表联逻，以減少系統(tǒng)表資源的消耗搓扯。

22.臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行О椋缦峭疲?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是公壤，對(duì)于一次性事件换可，最好使用導(dǎo)出表。

23.在新建臨時(shí)表時(shí)厦幅，如果一次性插入數(shù)據(jù)量很大沾鳄，那么可以使用 select into 代替 create table，避免造成大量 log 慨削，以提高速度洞渔；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源缚态，應(yīng)先create table磁椒，然后insert。

24.如果使用到了臨時(shí)表玫芦，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除浆熔，先 truncate table ，然后 drop table 桥帆，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定医增。

25.盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差老虫，如果游標(biāo)操作的數(shù)據(jù)超過1萬行叶骨，那么就應(yīng)該考慮改寫。

26.使用基于游標(biāo)的方法或臨時(shí)表方法之前祈匙，應(yīng)先尋找基于集的解決方案來解決問題忽刽，基于集的方法通常更有效天揖。

27.與臨時(shí)表一樣，游標(biāo)并不是不可使用跪帝。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法今膊，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快伞剑。如果開發(fā)時(shí)間允許斑唬，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好黎泣。

28.在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON 恕刘，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送 DONE_IN_PROC 消息抒倚。

29.盡量避免大事務(wù)操作雪营，提高系統(tǒng)并發(fā)能力。

30.盡量避免向客戶端返回大數(shù)據(jù)量衡便，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理洋访。

http://blog.csdn.net/ocean1010/article/details/5947580

（1）選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名镣陕，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理，在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表姻政。如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表. oracle首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并

（2） WHERE子句中的連接順序．：

ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾. 

（3） SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的過程中, 會(huì)將'*' 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間 

（4） 減少訪問數(shù)據(jù)庫的次數(shù)：

ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等呆抑； 

（5） 在SQLPlus , SQLForms和ProC中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量 ,建議值為200 （ARRAY[SIZE] {20(默認(rèn)值)|n} 置一批的行數(shù),是SQLPLUS一次從數(shù)據(jù)庫獲取的行數(shù),有效值為1至5000. 大的值可提高查詢和子查詢的有效性,可獲取許多行,但也需要更多的內(nèi)存.當(dāng)超過1000時(shí),其效果不大）

（6） 使用DECODE函數(shù)來減少處理時(shí)間：

使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表. （DECODE函數(shù)的作用：它可以將輸入數(shù)值與函數(shù)中的參數(shù)列表相比較，根據(jù)輸入值返回一個(gè)對(duì)應(yīng)值汁展。函數(shù)的參數(shù)列表是由若干數(shù)值及其對(duì)應(yīng)結(jié)果值組成的若干序偶形式鹊碍。當(dāng)然，如果未能與任何一個(gè)實(shí)參序偶匹配成功食绿，則函數(shù)也有默認(rèn)的返回值侈咕。

select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual

當(dāng)x等于1時(shí)，則返回‘x is 1’器紧。

當(dāng)x等于2時(shí)耀销，則返回‘x is 2’。

否則铲汪，返回others）

（7） 整合簡(jiǎn)單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問：

如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫查詢語句,你可以把它們整合到一個(gè)查詢中(即使它們之間沒有關(guān)系) 

（8） 刪除重復(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);

（9） 用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í), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 

（10） 盡量多使用COMMIT：

只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少: 

COMMIT所釋放的資源:

a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b. 被程序語句獲得的鎖

c. redo log buffer 中的空間

d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)

（11） 用Where子句替換HAVING子句：

避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷. (非oracle中)on熊尉、where、having這三個(gè)都可以加條件的子句中掌腰，on是最先執(zhí)行狰住，where次之，having最后齿梁，因?yàn)閛n是先把不符合條件的記錄過濾后才進(jìn)行統(tǒng)計(jì)催植，它就可以減少中間運(yùn)算要處理的數(shù)據(jù)，按理說應(yīng)該速度是最快的，where也應(yīng)該比having快點(diǎn)的查邢，因?yàn)樗^濾數(shù)據(jù)后才進(jìn)行sum蔗崎，在兩個(gè)表聯(lián)接時(shí)才用on的，所以在一個(gè)表的時(shí)候扰藕，就剩下where跟having比較了缓苛。在這單表查詢統(tǒng)計(jì)的情況下，如果要過濾的條件沒有涉及到要計(jì)算字段邓深，那它們的結(jié)果是一樣的未桥，只是where可以使用rushmore技術(shù)，而having就不能芥备，在速度上后者要慢如果要涉及到計(jì)算的字段冬耿，就表示在沒計(jì)算之前，這個(gè)字段的值是不確定的萌壳，根據(jù)上面寫的工作流程亦镶，where的作用時(shí)間是在計(jì)算之前就完成的，而having就是在計(jì)算后才起作用的袱瓮，所以在這種情況下缤骨，兩者的結(jié)果會(huì)不同。在多表聯(lián)接查詢時(shí)尺借，on比where更早起作用绊起。系統(tǒng)首先根據(jù)各個(gè)表之間的聯(lián)接條件，把多個(gè)表合成一個(gè)臨時(shí)表后燎斩，再由where進(jìn)行過濾虱歪，然后再計(jì)算，計(jì)算完后再由having進(jìn)行過濾栅表。由此可見笋鄙，要想過濾條件起到正確的作用，首先要明白這個(gè)條件應(yīng)該在什么時(shí)候起作用谨读，然后再?zèng)Q定放在那里

（12） 減少對(duì)表的查詢：

在含有子查詢的SQL語句中,要特別注意減少對(duì)表的查詢.例子： 

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13） 通過內(nèi)部函數(shù)提高SQL效率.：

復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的 

（14） 使用表的別名(Alias)：

當(dāng)在SQL語句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來,就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤. 

（15） 用EXISTS替代IN局装、用NOT EXISTS替代NOT IN：

在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS. 

例子：

（高效）SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16） 識(shí)別'低效執(zhí)行'的SQL語句：

雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個(gè)最好的方法： 

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

（17） 用索引提高效率：

索引是表的一個(gè)概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個(gè)復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率. 另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.劳殖。那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.铐尚。定期的重構(gòu)索引是有必要的.： 

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（18） 用EXISTS替換DISTINCT：

當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果. 例子： 

(低效): 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);

（19） sql語句用大寫的；因?yàn)閛racle總是先解析sql語句哆姻，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行

（20） 在java代碼中盡量少用連接符“＋”連接字符串宣增！

（21） 避免在索引列上使用NOT 通常，

我們要避免在索引列上使用NOT, NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的影響. 當(dāng)ORACLE”遇到”NOT,他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描. 

（22） 避免在索引列上使用計(jì)算．

WHERE子句中矛缨，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描． 舉例: 低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

（23） 用>=替代>

高效: 

SELECT * FROM EMP WHERE DEPTNO >=4 低效:

SELECT * FROM EMP WHERE DEPTNO >3 兩者的區(qū)別在于, 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄.

（24） 用UNION替換OR (適用于索引列)

通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒有被索引, 查詢效率可能會(huì)因?yàn)槟銢]有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫在最前面. 

（25） 用IN來替換OR

這是一條簡(jiǎn)單易記的規(guī)則爹脾，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)帖旨，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的．　 

低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

（26） 避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以為空的列灵妨，ORACLE將無法使用該索引．對(duì)于單列索引解阅，如果列包含空值，索引中將不存在此記錄. 對(duì)于復(fù)合索引泌霍，如果每個(gè)列都為空货抄，索引中同樣不存在此記錄.　如果至少有一個(gè)列不為空，則記錄存在于索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空朱转，ORACLE將認(rèn)為整個(gè)鍵值為空蟹地，而空不等于空. 因此你可以插入1000 條具有相同鍵值的記錄,當(dāng)然它們都是空! 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引. 

低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

（27） 總是使用索引的第一個(gè)列：

如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引. 這也是一條簡(jiǎn)單而重要的規(guī)則，當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引

（28） 用UNION-ALL 替換UNION ( 如果有可能的話)：

當(dāng)SQL語句需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會(huì)因此得到提高. 需要注意的是藤为，UNION ALL 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄. 因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性. UNION 將對(duì)結(jié)果集合排序,這個(gè)操作會(huì)使用到SORT_AREA_SIZE這塊內(nèi)存. 對(duì)于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的. 下面的SQL可以用來查詢排序的消耗量 

低效：

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 TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

（29） 用WHERE替代ORDER BY：

ORDER BY 子句只在兩種嚴(yán)格的條件下使用索引.

ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.

ORDER BY中所有的列必須定義為非空.

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

例如: 表DEPT包含以下列:

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

（30） 避免改變索引列的類型.:

當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換. 

假設(shè) EMPNO是一個(gè)數(shù)值類型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 實(shí)際上,經(jīng)過ORACLE類型轉(zhuǎn)換, 語句轉(zhuǎn)化為: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 幸運(yùn)的是,類型轉(zhuǎn)換沒有發(fā)生在索引列上,索引的用途沒有被改變. 現(xiàn)在,假設(shè)EMP_TYPE是一個(gè)字符類型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 這個(gè)語句被ORACLE轉(zhuǎn)換為: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到! 為了避免ORACLE對(duì)你的SQL進(jìn)行隱式的類型轉(zhuǎn)換, 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來. 注意當(dāng)字符和數(shù)值比較時(shí), ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型

（31） 需要當(dāng)心的WHERE子句:

某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子. 在下面的例子里, (1)‘!=' 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. (2) ‘||'是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引. (3) ‘+'是數(shù)學(xué)函數(shù). 就象其他數(shù)學(xué)函數(shù)那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會(huì)啟用全表掃描. 

（32） a. 如果檢索數(shù)據(jù)量超過30%的表中記錄數(shù).使用索引將沒有顯著的效率提高.

b. 在特定情況下, 使用索引也許會(huì)比全表掃描慢, 但這是同一個(gè)數(shù)量級(jí)上的區(qū)別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!

（33） 避免使用耗費(fèi)資源的操作:

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會(huì)啟動(dòng)SQL引擎 執(zhí)行耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強(qiáng)（Oracle查詢運(yùn)算：intersect和minus intersect運(yùn)算 返回查詢結(jié)果中相同的部分 exp:各個(gè)部門中有哪些相同的工種 select job from account intersect select job from research intersect select job from sales; minus運(yùn)算 返回在第一個(gè)查詢結(jié)果中與第二個(gè)查詢結(jié)果不相同的那部分行記錄怪与。 有哪些工種在財(cái)會(huì)部中有，而在銷售部中沒有）

（34） 優(yōu)化GROUP BY:

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多. 

低效:

SELECT JOB , AVG(SAL)

FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER' GROUP JOB

三缅疟、常見鎖表情況

1分别、鎖表介紹

鎖表發(fā)生在insert 、update 存淫、delete 中

鎖表的原理是 數(shù)據(jù)庫使用獨(dú)占式封鎖機(jī)制茎杂，當(dāng)執(zhí)行上面的語句時(shí)，對(duì)表進(jìn)行鎖住纫雁，直到發(fā)生commite 或者 回滾 或者退出數(shù)據(jù)庫用戶

2、鎖表的原因

1倾哺、操作相同記錄時(shí)轧邪，對(duì)臨界資源的訪問順序錯(cuò)誤，例如：A線程update tableA羞海，update tableB忌愚；B線程 update table，update tableA

2却邓、db2硕糊、sybase的鎖升級(jí)

附 oralce的一些常用工具或腳本

1、降低水位線

高水位（HIGH WARTER MARK腊徙，HWM）好比水庫中儲(chǔ)水的水位简十，用于描述數(shù)據(jù)庫中段的擴(kuò)展方式。高水位對(duì)全表掃描方式有著至關(guān)重要的影響撬腾。當(dāng)使用DELETE刪除表記錄時(shí)螟蝙，高水位并不會(huì)下降，隨之導(dǎo)致的是全表掃描的實(shí)際開銷并沒有任何減少民傻。

例如胰默，首先新建一張空表场斑，大小占用64K，然后插入數(shù)據(jù)直到表大小變?yōu)?0G牵署，此時(shí)使用DELETE刪除所有的數(shù)據(jù)并且提交漏隐，這個(gè)時(shí)候查詢表的大小的時(shí)候依然是50G，這就是因?yàn)楸淼母咚粵]有釋放的緣故奴迅，而在這時(shí)如果使用“SELECT * FROM TABLE_NAME;”語句來查詢數(shù)據(jù)的話青责，那么查詢過程就會(huì)很慢，因?yàn)镺racle要執(zhí)行全表掃描半沽，從高水位下所有的塊都得去掃描爽柒，直到50G的所有塊全部掃描完畢。曾遇到一個(gè)同事使用DELETE刪除了一個(gè)很大的分區(qū)表者填，然后執(zhí)行SELECT查詢很久都沒有結(jié)果浩村，以為是數(shù)據(jù)庫HANG住了，其實(shí)這個(gè)問題是由于高水位的緣故占哟。所以心墅，表執(zhí)行了TRUNCATE操作，再次SELECT的時(shí)候就可以很快返回結(jié)果了榨乎。

--索引重建怎燥、降低水位線，在命令窗口中執(zhí)行
begin
  dbms_output.enable(200000);
  for a in ( select t.table_name from user_tables t where t.TABLE_NAME not in ('GAP_ELE_VALUESET_SETTER','SYS_TEMP_FBT') order by table_name asc) loop
     --dbms_output.put_line( a.TABLE_NAME );
     execute immediate 'alter table '|| a.table_name || ' move';
  end loop;
  
  for x in (select u.index_name from user_indexes u where index_type not in ('LOB','IOT - TOP')) loop
    --dbms_output.put_line('alter index ' || x.index_name || ' rebuild');
    execute immediate 'alter index ' || x.index_name || ' rebuild online nologging';
    --alter index dou_idx rebuild online nologging;
  end loop;
  
  for x in( select table_name from user_tables order by table_name asc) loop
    --dbms_output.put_line('dbms_stats.gather_table_stats(ownname=>user,tabname=>'''||x.table_name||''',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>''FOR ALL INDEXED COLUMNS'',DEGREE=>4,CASCADE=>TRUE);');
    dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => x.table_name,
                                estimate_percent => 100,
                                method_opt       => 'for all indexed columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 2,
                                cascade          => TRUE);
  end loop;
end;
/

2蜜暑、AWR文件

Oracle可以產(chǎn)生兩種類型的AWR報(bào)告：文本格式和HTML 格式铐姚。HTML格式的報(bào)告界面更加友好。AWR報(bào)告用于顯示兩個(gè)快照或者兩個(gè)時(shí)間點(diǎn)之間捕捉到的數(shù)據(jù)肛捍。AWR報(bào)告其實(shí)就是一張數(shù)據(jù)庫健康體檢表隐绵，它顯示了數(shù)據(jù)庫健康的各項(xiàng)指標(biāo)。

注意：在生成AWR報(bào)告的時(shí)候拙毫，所選擇的兩個(gè)快照不能跨越數(shù)據(jù)庫重啟時(shí)間點(diǎn)依许。
產(chǎn)生整個(gè)數(shù)據(jù)庫的AWR報(bào)告，運(yùn)行腳本awrrpt.sql：
SQL> @?/rdbms/admin/awrrpt.sql
產(chǎn)生某個(gè)實(shí)例的AWR報(bào)告缀蹄，運(yùn)行腳本awrrpti.sql:
SQL> @?/rdbms/admin/awrrpti.sql
產(chǎn)生某條SQL語句的AWR報(bào)告峭跳，運(yùn)行腳本awrsqrpt.sql:
SQL> @?/rdbms/admin/awrsqrpt.sql

3、事務(wù)沒提交缺前，如何查詢是哪些sql導(dǎo)致了該問題

How to gather info about uncommitted transactions (Doc ID 786472.1)

DBA's often need to know information about a transaction that is currently either 'in flight' or has completed but has not yet committed

Possible uses for this query

 * It is suspected that a transaction is blocking other transactions
 * Queries are running slowly and it is suspected that this is due to consistent reads against a modified data source
 * The undo tablespace is full and the DBA would like to see what is causing the space to be used

The following procedure will assist in this

CREATE OR REPLACE PROCEDURE GET_XACTION_INFO AS
 
V_OUT VARCHAR2(250);
V_SID NUMBER;
V_USED_UBLK NUMBER;
 
CURSOR GET_OPEN_XACTIONS IS
     SELECT oracle_username, o.owner, o.object_name, l.session_id
     FROM v$locked_object l, dba_objects o
     where l.object_id = o.object_id
     order by l.session_id;
 
BEGIN
 
     V_SID := 0;
 
     DBMS_OUTPUT.PUT_LINE('.................OPEN TRANSACTIONS.................');
     DBMS_OUTPUT.PUT_LINE('===================================================');
 
     FOR V_RECORD IN GET_OPEN_XACTIONS LOOP
 
          IF V_SID = 0 THEN
               V_SID := V_RECORD.session_id;
          END IF;
 
          IF V_RECORD.session_id <> V_SID THEN
 
               SELECT distinct(used_ublk) into V_USED_UBLK
               FROM V$TRANSACTION T, V$LOCKED_OBJECT L
               WHERE V_SID = L.SESSION_ID
               AND L.XIDUSN = T.XIDUSN
               AND L.XIDSLOT = T.XIDSLOT
               AND L.XIDSQN = T.XIDSQN;
 
               DBMS_OUTPUT.PUT_LINE('. FOR A TOTAL OF '||TO_CHAR(v_USED_UBLK)||' UNDO BLOCKS');
               DBMS_OUTPUT.PUT_LINE('======');
 
               V_SID := V_RECORD.session_id;
 
          END IF;
 
          DBMS_OUTPUT.PUT_LINE('USER : '||V_RECORD.oracle_username||' ('||V_RECORD.session_id||') '||' has an open transaction on '||V_RECORD.owner||'.'||V_RECORD.object_name);
 
     END LOOP;
 
     IF V_SID <> 0 THEN
 
           SELECT distinct(used_ublk) into V_USED_UBLK
           FROM V$TRANSACTION T, V$LOCKED_OBJECT L
           WHERE V_SID = L.SESSION_ID
           AND L.XIDUSN = T.XIDUSN
           AND L.XIDSLOT = T.XIDSLOT
           AND L.XIDSQN = T.XIDSQN;
 
          DBMS_OUTPUT.PUT_LINE('. FOR A TOTAL OF '||TO_CHAR(v_USED_UBLK)||' UNDO BLOCKS');
 
     END IF;
 
END;
/