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

1、Oracle使用及性能調(diào)優(yōu)2015-10Contents目錄OS內(nèi)核參數(shù)和資源限制數(shù)據(jù)庫優(yōu)化調(diào)整概念SQL語句調(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ù)庫優(yōu)化調(diào)整02關(guān)閉數(shù)據(jù)文件自動擴(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 上面查詢出來的數(shù)據(jù)文件名 AUTOEXTEND OFF;取消口令過期限制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文件配置為每個實例配置3組REDO文件，每組2個成員，建議大小為1G。ASM實例參數(shù)調(diào)整數(shù)據(jù)庫實例參數(shù)調(diào)整Part Three概念03Oracle數(shù)據(jù)庫Oracle數(shù)據(jù)庫=實例+數(shù)據(jù)庫實例：是一個非固定的、基于內(nèi)存的基本進(jìn)程與內(nèi)存結(jié)構(gòu)。當(dāng)服務(wù)

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

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

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

7、loops 工作方式是從一張表中讀取數(shù)據(jù)，訪問另一張表（通常是索引）來做匹配，nested loops適用的場合是當(dāng)一個關(guān)聯(lián)表比較小的時候，效率會更高。Hash join的工作方式是將一個表（通常是小一點(diǎn)的那個表）做hash運(yùn)算，將列數(shù)據(jù)存儲到hash列表中，從另一個表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配。Sort Merge Join 是先將關(guān)聯(lián)表的關(guān)聯(lián)列各自做排序，然后從各自的排序表中抽取數(shù)據(jù)，到另一個排序表中做匹配，因為merge join需要做更多的排序，所以消耗的資源更多。 通常來講，能夠使用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)基本上，簇索引就是將多個表的相同列放在一起，而對該列使用用一個簇索引。這種索引在實際應(yīng)用中比較少，因為還有各種

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

10、GB級的表來說，構(gòu)建索引非常耗費(fèi)資源而且還要占用大量時間。13)、其他的索引類型Oracle數(shù)據(jù)庫還提供了很多其他類型的索引，例如用來為字符型大型二進(jìn)制對象(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)計數(shù)值得知進(jìn)行全Oracle索引掃描比進(jìn)行全表掃描更有

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

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

13、yclebin;來查詢回收站的狀態(tài)。作用：找回drop掉的表。閃回查詢1）、數(shù)據(jù)意外更改，找到表在刪除時間之前的所有數(shù)據(jù)2）、閃回表如果是存儲過程、函數(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#;兩個經(jīng)典sql-兩個日期之間的所有日期（豎表）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是昆明,大理,麗江中逗號個數(shù)+1（豎表）SELECT REGEXP_SUBSTR(昆

16、明,大理,麗江, ,+, 1, ROWNUM) AS IDS FROM DUAL CONNECT BY ROWNUM =替代用=替代避免select *select * 避免在索引列上使用計算在索引列上使用計算避免在索引列上使用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 會隱含的去除重復(fù)類型一致sql中變量的類型必須

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

18、看執(zhí)行計劃方法：在explain plan window中查看執(zhí)行計劃原則：最右最上原則外鍵必須建索引方法：在外鍵上建立索引作用：減少鎖表、減少sql的執(zhí)行時間未使用綁定變量的sql-查未使用綁定變量的sql，執(zhí)行次數(shù)多的展示在最上面，-最需要被優(yōu)化（執(zhí)行之后根據(jù)FORCE_MATCHING_SIGNATURE在v$sql中-就能查出sql語句）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;存儲過程、函數(shù)中動態(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