Oracle使用大全及性能調(diào)優(yōu)

1、Oracle使用及性能調(diào)優(yōu)2015-10Contents目錄OS內(nèi)核參數(shù)和資源限制數(shù)據(jù)庫(kù)優(yōu)化調(diào)整概念SQL語(yǔ)句調(diào)優(yōu)Part OneOS內(nèi)核參數(shù)和資源限制01OS內(nèi)核參數(shù)OS內(nèi)核參數(shù)（序）HugePagesLinux 64位+SGA大于8G，oracle強(qiáng)烈建議使用hugepages資源限制Part Two數(shù)據(jù)庫(kù)優(yōu)化調(diào)整02關(guān)閉數(shù)據(jù)文件自動(dòng)擴(kuò)展屬性SQL select file_name from dba_data_files where autoextensible= YES;SQL select file_name from dba_temp_files where autoextensib

2、le= YES;alter database datafile 上面查詢出來(lái)的數(shù)據(jù)文件名 AUTOEXTEND OFF;取消口令過(guò)期限制SQL SELECT * FROM dba_profiles s WHERE file=DEFAULT;ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME unlimited; ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME unlimited;

3、ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX unlimited; ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS unlimited; ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 1/1440;修改REDO文件配置為每個(gè)實(shí)例配置3組REDO文件，每組2個(gè)成員，建議大小為1G。ASM實(shí)例參數(shù)調(diào)整數(shù)據(jù)庫(kù)實(shí)例參數(shù)調(diào)整Part Three概念03Oracle數(shù)據(jù)庫(kù)Oracle數(shù)據(jù)庫(kù)=實(shí)例+數(shù)據(jù)庫(kù)實(shí)例：是一個(gè)非固定的、基于內(nèi)存的基本進(jìn)程與內(nèi)存結(jié)構(gòu)。當(dāng)服務(wù)

4、器關(guān)閉后，實(shí)例也就不存在了。數(shù)據(jù)庫(kù)：指的是固定的、基于磁盤的數(shù)據(jù)文件、控制文件、日志文件、參數(shù)文件和歸檔日志文件等。實(shí)例結(jié)構(gòu)SGA組件Database buffer cache數(shù)據(jù)緩存區(qū)：用于存儲(chǔ)從數(shù)據(jù)文件（datafile）讀出的數(shù)據(jù)塊（data block）副本。所有并發(fā)地連接到實(shí)例上的用戶進(jìn)程（user process）都將共享同一個(gè)數(shù)據(jù)緩存區(qū)。SGA組件redo log buffer重做日志緩沖區(qū)（redo log buffer）是 SGA 內(nèi)一塊被循環(huán)使用的緩沖區(qū)，用于記錄數(shù)據(jù)庫(kù)內(nèi)的數(shù)據(jù)變化信息。這些信息以重做條目（redo entry）的形式進(jìn)行存儲(chǔ)。Oracle 利用重做條目?jī)?nèi)的

5、信息就可以重做由 INSERT，UPDATE，DELETE，CREATE，ALTER，及 DROP 等操作對(duì)數(shù)據(jù)庫(kù)進(jìn)行的修改。重做條目可以被用于進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)（database recovery）。SGA組件shared poolshared pool=library cache+dictionary cache庫(kù)緩存（library cache）中包含共享 SQL 區(qū)（shared SQL area），私有 SQL 區(qū)（private SQL area）（當(dāng)系統(tǒng)運(yùn)行在共享服務(wù)器模式下時(shí)），PL/SQL 過(guò)程和包，以及用于系統(tǒng)控制的各種內(nèi)存結(jié)構(gòu)，例如鎖（lock）及庫(kù)緩存句柄（library

6、cache handle）等。數(shù)據(jù)字典是一系列保存了數(shù)據(jù)庫(kù)參考信息（例如數(shù)據(jù)庫(kù)結(jié)構(gòu)，數(shù)據(jù)庫(kù)用戶等）的表和視圖。Oracle 在解析的 SQL 語(yǔ)句的過(guò)程中需要頻繁地訪問(wèn)數(shù)據(jù)字典。數(shù)據(jù)字典信息對(duì) Oracle 能否正常運(yùn)行至關(guān)重要。SGA組件large pool大型池（large pool）：供一次性大量的內(nèi)存分配使用交互過(guò)程表類型1、堆表2、臨時(shí)表3、分區(qū)表4、外部表5、索引組織表1、索引聚簇表2、散列聚簇表3、有序散列聚簇表4、嵌套表5、對(duì)象表常用不常用表類型分區(qū)表范圍分區(qū)、列表分區(qū)、hash分區(qū)、組合分區(qū)表類型外部表外部表可以把一個(gè)操作系統(tǒng)文件當(dāng)作一個(gè)只讀的數(shù)據(jù)庫(kù)表表連接方式Nested

7、loops 工作方式是從一張表中讀取數(shù)據(jù)，訪問(wèn)另一張表（通常是索引）來(lái)做匹配，nested loops適用的場(chǎng)合是當(dāng)一個(gè)關(guān)聯(lián)表比較小的時(shí)候，效率會(huì)更高。Hash join的工作方式是將一個(gè)表（通常是小一點(diǎn)的那個(gè)表）做hash運(yùn)算，將列數(shù)據(jù)存儲(chǔ)到hash列表中，從另一個(gè)表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配。Sort Merge Join 是先將關(guān)聯(lián)表的關(guān)聯(lián)列各自做排序，然后從各自的排序表中抽取數(shù)據(jù)，到另一個(gè)排序表中做匹配，因?yàn)閙erge join需要做更多的排序，所以消耗的資源更多。 通常來(lái)講，能夠使用merge join的地方，hash join都可以發(fā)揮更好的

8、性能。索引類型1)、b-tree索引Create index、create unique index、復(fù)合索引2)、基于函數(shù)的索引UPPER(first_name) = JOHN3)、反轉(zhuǎn)關(guān)鍵字索引4)、位圖索引(bitmap index)5)、位圖連接索引(bitmap join index)6)、壓縮索引7)、分區(qū)索引(partitioned index)全局分區(qū)索引(global)和單獨(dú)分區(qū)的本地分區(qū)索引(local)8)、索引組織表索引9)、簇索引(cluster index)基本上，簇索引就是將多個(gè)表的相同列放在一起，而對(duì)該列使用用一個(gè)簇索引。這種索引在實(shí)際應(yīng)用中比較少，因?yàn)檫€有各種

9、有待解決的性能問(wèn)題存在。10)、域索引(domain index)當(dāng)我們創(chuàng)建為用戶自定義數(shù)據(jù)類型(datatype)創(chuàng)建用戶自定義索引類型(indextype)時(shí)就要使用域索引。11)、隱藏索引(invisible index)這是Oracle 11g中推出的新特性。其創(chuàng)建過(guò)程和標(biāo)準(zhǔn)索引一樣，但創(chuàng)建后對(duì)于基于代價(jià)的優(yōu)化器(CBO)是不可見(jiàn)的。這可以讓你對(duì)性能進(jìn)行大型測(cè)試查詢，而不會(huì)影響現(xiàn)有的正在運(yùn)行的應(yīng)用程序。12)、虛擬索引(virtual index)這是為測(cè)試人員和開(kāi)發(fā)人員準(zhǔn)備的又一個(gè)工具。虛擬索引(不分配段空間)可以讓你在不需要實(shí)際創(chuàng)建索引的情況下，測(cè)試新索引及其對(duì)查詢計(jì)劃的影響。對(duì)于

10、GB級(jí)的表來(lái)說(shuō)，構(gòu)建索引非常耗費(fèi)資源而且還要占用大量時(shí)間。13)、其他的索引類型Oracle數(shù)據(jù)庫(kù)還提供了很多其他類型的索引，例如用來(lái)為字符型大型二進(jìn)制對(duì)象(CLOB)或其他大型文本數(shù)據(jù)構(gòu)建索引的Oracle TEXT，Oracle Spatial等。索引掃描方式（1）index unique scan： 查詢結(jié)果返回一行記錄（2）index range scan： 查詢結(jié)果返回多行記錄。（3）index full scan：可能進(jìn)行全Oracle索引掃描而不是范圍掃描，需要注意的是全Oracle索引掃描只在CBO模式下才有效。CBO根據(jù)統(tǒng)計(jì)數(shù)值得知進(jìn)行全Oracle索引掃描比進(jìn)行全表掃描更有

11、效時(shí)，才進(jìn)行全Oracle索引掃描，而且此時(shí)查詢出的數(shù)據(jù)都必須從索引中可以直接得到。（4）index fast full scan： 與 index full scan很類似，但是一個(gè)顯著的區(qū)別就是它不對(duì)查詢出的數(shù)據(jù)進(jìn)行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。（5）index skip scan： INDEX SKIP SCAN，發(fā)生在多個(gè)列建立的復(fù)合索引上，如果SQL中謂詞條件只包含索引中的部分列，并且這些列不是建立索引時(shí)的第一列時(shí)，就可能發(fā)生INDEX SKIP SCAN。這里SKIP的意思是因?yàn)椴樵儣l件

12、沒(méi)有第一列或前面幾列，被忽略了。創(chuàng)建索引的策略（1）小表不要建索引（2）有限取值的字段不要建索引（如男女）（3）如果可以的話，盡量建唯一索引（4）反轉(zhuǎn)鍵索引是消除熱塊（5）經(jīng)常作為where條件的字段、作為表連接的字段（6）索引建在與數(shù)據(jù)不同的表空間（主要是維護(hù)、邏輯備份方便、提高性能；使用ASM后，性能上無(wú)差異）（7）減少索引冗余復(fù)合索引順序選擇1、最有選擇性的條件放在前面2、根據(jù)實(shí)際情況delete和truncate的區(qū)別1、高水位線2、一個(gè)dml語(yǔ)句一個(gè)是ddl語(yǔ)句回收站回收站默認(rèn)開(kāi)啟，也可以通過(guò)SELECT Value FROM V$parameter WHERE Name = rec

13、yclebin;來(lái)查詢回收站的狀態(tài)。作用：找回drop掉的表。閃回查詢1）、數(shù)據(jù)意外更改，找到表在刪除時(shí)間之前的所有數(shù)據(jù)2）、閃回表如果是存儲(chǔ)過(guò)程、函數(shù)、select * from dba_source as of timestamp to_date(2012-08-02 11:00:00,yyyy-mm-dd hh24:mi:ss)where type=PROCEDURE AND name=CCMOS_GHGL_XZFP如果是jobselect * from DBA_jobs as of timestamp to_date(2013-03-16 09:00:00,yyyy-mm-dd hh2

14、4:mi:ss檢查死鎖并殺死sessionselect /*+ rule */ s.username,decode(l.type,TM,TABLE LOCK,TX,ROW LOCK,NULL) LOCK_LEVEL,o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,gram,s.osuser from v$session s,v$lock l,dba_objects o where l.sid = s.sid and l.id1 = o.object_id(+) and s.usernam

15、e is not null;在command window殺掉sessionSQL alter system kill session sid,serial#;兩個(gè)經(jīng)典sql-兩個(gè)日期之間的所有日期（豎表）SELECT to_date(2014-04-30,yyyy-mm-dd)+level-1 FROM dualCONNECT BY LEVEL = to_date(2014-05-12,yyyy-mm-dd) - to_date(2014-04-30,yyyy-mm-dd) + 1;-ROWNUM =3其中3是昆明,大理,麗江中逗號(hào)個(gè)數(shù)+1（豎表）SELECT REGEXP_SUBSTR(昆

16、明,大理,麗江, ,+, 1, ROWNUM) AS IDS FROM DUAL CONNECT BY ROWNUM =替代用=替代避免select *select * 避免在索引列上使用計(jì)算在索引列上使用計(jì)算避免在索引列上使用is null和is not null在索引列上使用is null和is not null避免在索引列上使用not在索引列上使用not避免like %value%select col1,col2 from tbl where col1 like %val%用union all 替換union ( 如果有可能的話)union 會(huì)隱含的去除重復(fù)類型一致sql中變量的類型必須

17、與字段定義一致盡量避免排序distinct,union,minus,intersect,order by盡量少使用hint需保證語(yǔ)句中只有一個(gè)hint，且緊跟在select后面，表如果使用了別名，hint中也使用別名PLSQL編寫小技巧1、寫注釋2、減少循環(huán)迭代的次數(shù)3、盡量使用rowid更新表記錄4、變量運(yùn)算要使用同一類型，避免隱式轉(zhuǎn)換5、有很多if elsif 把頻率高的放在最前面6、緩存sysdate1、With語(yǔ)句2、Multitable insert3、Merge語(yǔ)句4、Forall5、bulk collect6、分析函數(shù)Part Four調(diào)優(yōu)05使用PLSQL Developer查

18、看執(zhí)行計(jì)劃方法：在explain plan window中查看執(zhí)行計(jì)劃原則：最右最上原則外鍵必須建索引方法：在外鍵上建立索引作用：減少鎖表、減少sql的執(zhí)行時(shí)間未使用綁定變量的sql-查未使用綁定變量的sql，執(zhí)行次數(shù)多的展示在最上面，-最需要被優(yōu)化（執(zhí)行之后根據(jù)FORCE_MATCHING_SIGNATURE在v$sql中-就能查出sql語(yǔ)句）select FORCE_MATCHING_SIGNATURE, count(1) from v$sql where FORCE_MATCHING_SIGNATURE 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) 10 order by 2;存儲(chǔ)過(guò)程、函數(shù)中動(dòng)態(tài)sql1、方法select * from dba_source a where a.owner=ISS_TRACK and upper(a.TEXT) like%IMMEDIATE%2、優(yōu)化方法及作用方法：使用execute immediate sqlString u