如何進(jìn)行oracle數(shù)據(jù)庫性能優(yōu)化

你最好買一本專門講ORACLE性能優(yōu)化的書，好好看看\x0d\x0a1、調(diào)整數(shù)據(jù)庫服務(wù)器的性能\x0d\x0aOracle數(shù)據(jù)庫服務(wù)器是整個(gè)系統(tǒng)的核心，它的性能高低直接影響整個(gè)系統(tǒng)的性能，為了調(diào)整Oracle數(shù)據(jù)庫服務(wù)器的性能，主要從以下幾個(gè)方面考慮： \x0d\x0a1.1、調(diào)整操作系統(tǒng)以適合Oracle數(shù)據(jù)庫服務(wù)器運(yùn)行\(zhòng)x0d\x0aOracle數(shù)據(jù)庫服務(wù)器很大程度上依賴于運(yùn)行服務(wù)器的操作系統(tǒng)，如果操作系統(tǒng)不能提供最好性能，那么無論如何調(diào)整，Oracle數(shù)據(jù)庫服務(wù)器也無法發(fā)揮其應(yīng)有的性能。 \x0d\x0a1.1.1、為Oracle數(shù)據(jù)庫服務(wù)器規(guī)劃系統(tǒng)資源 \x0d\x0a據(jù)已有計(jì)算機(jī)可用資源, 規(guī)劃分配給Oracle服務(wù)器資源原則是：盡可能使Oracle服務(wù)器使用資源最大化,特別在Client/Server中盡量讓服務(wù)器上所有資源都來運(yùn)行Oracle服務(wù)。 \x0d\x0a1.1.2、調(diào)整計(jì)算機(jī)系統(tǒng)中的內(nèi)存配置 \x0d\x0a多數(shù)操作系統(tǒng)都用虛存來模擬計(jì)算機(jī)上更大的內(nèi)存，它實(shí)際上是硬盤上的一定的磁盤空間。當(dāng)實(shí)際的內(nèi)存空間不能滿足應(yīng)用軟件的要求時(shí)，操作系統(tǒng)就將用這部分的磁盤空間對內(nèi)存中的信息進(jìn)行頁面替換，這將引起大量的磁盤I/O操作，使整個(gè)服務(wù)器的性能下降。為了避免過多地使用虛存，應(yīng)加大計(jì)算機(jī)的內(nèi)存。 \x0d\x0a1.1.3、為Oracle數(shù)據(jù)庫服務(wù)器設(shè)置操作系統(tǒng)進(jìn)程優(yōu)先級 \x0d\x0a不要在操作系統(tǒng)中調(diào)整Oracle進(jìn)程的優(yōu)先級，因?yàn)樵贠racle數(shù)據(jù)庫系統(tǒng)中，所有的后臺和前臺數(shù)據(jù)庫服務(wù)器進(jìn)程執(zhí)行的是同等重要的工作，需要同等的優(yōu)先級。所以在安裝時(shí)，讓所有的數(shù)據(jù)庫服務(wù)器進(jìn)程都使用缺省的優(yōu)先級運(yùn)行。 \x0d\x0a1.2、調(diào)整內(nèi)存分配\x0d\x0aOracle數(shù)據(jù)庫服務(wù)器保留3個(gè)基本的內(nèi)存高速緩存，分別對應(yīng)3種不同類型的數(shù)據(jù)：庫高速緩存，字典高速緩存和緩沖區(qū)高速緩存。庫高速緩存和字典高速緩存一起構(gòu)成共享池，共享池再加上緩沖區(qū)高速緩存便構(gòu)成了系統(tǒng)全程區(qū)(SGA)。SGA是對數(shù)據(jù)庫數(shù)據(jù)進(jìn)行快速訪問的一個(gè)系統(tǒng)全程區(qū)，若SGA本身需要頻繁地進(jìn)行釋放、分配，則不能達(dá)到快速訪問數(shù)據(jù)的目的，因此應(yīng)把SGA放在主存中，不要放在虛擬內(nèi)存中。內(nèi)存的調(diào)整主要是指調(diào)整組成SGA的內(nèi)存結(jié)構(gòu)的大小來提高系統(tǒng)性能，由于Oracle數(shù)據(jù)庫服務(wù)器的內(nèi)存結(jié)構(gòu)需求與應(yīng)用密切相關(guān)，所以內(nèi)存結(jié)構(gòu)的調(diào)整應(yīng)在磁盤I/O調(diào)整之前進(jìn)行。 \x0d\x0a1.2.1、庫緩沖區(qū)的調(diào)整 \x0d\x0a庫緩沖區(qū)中包含私用和共享SQL和PL/SQL區(qū)，通過比較庫緩沖區(qū)的命中率決定它的大小。要調(diào)整庫緩沖區(qū)，必須首先了解該庫緩沖區(qū)的活動(dòng)情況，庫緩沖區(qū)的活動(dòng)統(tǒng)計(jì)信息保留在動(dòng)態(tài)性能表v$librarycache數(shù)據(jù)字典中，可通過查詢該表來了解其活動(dòng)情況，以決定如何調(diào)整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數(shù)；Reloads列給出SQL 和PL/SQL塊的隱式分析或?qū)ο蠖x重裝載時(shí)在庫程序緩沖區(qū)中發(fā)生的錯(cuò)誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區(qū)的命中率合適；若sum(pins)/sum(reloads)1, 則需調(diào)整初始化參數(shù) shared_pool_size來重新調(diào)整分配給共享池的內(nèi)存量。 \x0d\x0a1.2.2、數(shù)據(jù)字典緩沖區(qū)的調(diào)整 \x0d\x0a數(shù)據(jù)字典緩沖區(qū)包含了有關(guān)數(shù)據(jù)庫的結(jié)構(gòu)、用戶、實(shí)體信息。數(shù)據(jù)字典的命中率，對系統(tǒng)性能影響極大。數(shù)據(jù)字典緩沖區(qū)的使用情況記錄在動(dòng)態(tài)性能表v$librarycache中，可通過查詢該表來了解其活動(dòng)情況，以決定如何調(diào)整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是對相應(yīng)項(xiàng)請求次數(shù)的統(tǒng)計(jì)；Getmisses 列是引起緩沖區(qū)出錯(cuò)的數(shù)據(jù)的請求次數(shù)。對于頻繁訪問的數(shù)據(jù)字典緩沖區(qū)，sum(getmisses)/sum(gets)10%～15%。若大于此百分?jǐn)?shù)，則應(yīng)考慮增加數(shù)據(jù)字典緩沖區(qū)的容量，即需調(diào)整初始化參數(shù)shared_pool_size來重新調(diào)整分配給共享池的內(nèi)存量。 \x0d\x0a1.2.3、緩沖區(qū)高速緩存的調(diào)整 \x0d\x0a用戶進(jìn)程所存取的所有數(shù)據(jù)都是經(jīng)過緩沖區(qū)高速緩存來存取，所以該部分的命中率，對性能至關(guān)重要。緩沖區(qū)高速緩存的使用情況記錄在動(dòng)態(tài)性能表v$sysstat中，可通過查詢該表來了解其活動(dòng)情況，以決定如何調(diào)整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是請求數(shù)據(jù)緩沖區(qū)中讀的總次數(shù)。physical reads的值是請求數(shù)據(jù)時(shí)引起從盤中讀文件的次數(shù)。從緩沖區(qū)高速緩存中讀的可能性的高低稱為緩沖區(qū)的命中率，計(jì)算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，則應(yīng)增大db_block_buffers的參數(shù)值。db_block_buffers可以調(diào)整分配給緩沖區(qū)高速緩存的內(nèi)存量，即db_block_buffers可設(shè)置分配緩沖區(qū)高速緩存的數(shù)據(jù)塊的個(gè)數(shù)。緩沖區(qū)高速緩存的總字節(jié)數(shù)=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數(shù)據(jù)塊大小的字節(jié)數(shù)，可查詢 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述數(shù)據(jù)庫的初始化參數(shù)以后，必須先關(guān)閉數(shù)據(jù)庫，在重新啟動(dòng)數(shù)據(jù)庫后才能使新的設(shè)置起作用。

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了天峻免費(fèi)建站歡迎大家使用！

如何提高oracle模糊查詢的性能？

1、使用兩邊加‘%’號的查詢，Oracle是不通過索引的，所以查詢效率很低。

例如：select count(*) from lui_user_base t where t.user_name like '%cs%';

2、like '...%'和 like'%...'雖然走了索引，但是效率依然很低。

3、有人說使用如下sql，他的效率提高了10倍，但是數(shù)據(jù)量小的時(shí)候

select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')

我拿100w跳數(shù)據(jù)做了測試，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like '%cs%' ? 這條sql執(zhí)行很快，那是相當(dāng)?shù)目欤欠诺絪elect count(*) from lui_user_base where rowid in（）里后，效率就會變的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0

這種查詢效果很好，速度很快，推薦使用這種。因?yàn)槲覍racle內(nèi)部機(jī)制不是很懂，只是對結(jié)果做了一個(gè)說明。

5、有人說了用全文索引，我看了，步驟挺麻煩，但是是個(gè)不錯(cuò)的方法，留著備用：

對cmng_custominfo 表中的address字段做全文檢索：

1，在oracle9201中需要?jiǎng)?chuàng)建一個(gè)分詞的東西：

BEGIN

ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');

--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用

end;

2，創(chuàng)建全文檢索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查詢時(shí)候，使用：

select * from cmng_custominfo where contains (address, '金色新城')1;

4，需要定期進(jìn)行同步和優(yōu)化：

同步：根據(jù)新增記錄的文本內(nèi)容更新全文搜索的索引。

begin

ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');

end;

優(yōu)化：根據(jù)被刪除記錄清除全文搜索索引中的垃圾

begin

ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');

end;

5，采用job做步驟4中的工作：

1)該功能需要利用oracle的JOB功能來完成

因?yàn)閛racle9I默認(rèn)不啟用JOB功能，所以首先需要增加ORACLE數(shù)據(jù)庫實(shí)例的JOB配置參數(shù):

job_queue_processes=5

重新啟動(dòng)oracle數(shù)據(jù)庫服務(wù)和listener服務(wù)。

2)同步 和 優(yōu)化

--同步 sync:

variable jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;

--優(yōu)化

variable jobno number;

begin

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');

commit;

END;

其中， 第一個(gè)job的SYSDATE + (1/24/4)是指每隔15分鐘同步一次，第二個(gè)job的SYSDATE + 1是每隔1天做一次全優(yōu)化。具體的時(shí)間間隔，可以根據(jù)應(yīng)用的需要而定。

6，索引重建

重建索引會刪除原來的索引，重新生成索引，需要較長的時(shí)間。

重建索引語法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

據(jù)網(wǎng)上一些用家的體會，oracle重建索引的速度也是比較快的，有一用家這樣描述：

Oracle 的全文檢索建立和維護(hù)索引要比ms sql server都要快得多，筆者的65萬記錄的一個(gè)表建立索引只需要20分鐘，同步一次只需要1分鐘。

因此，也可以考慮用job的辦法定期重建索引。

oracle性能調(diào)優(yōu)有哪些原則

任何事情都有它的源頭，要解決問題，也得從源頭開始，影響ORACLE性能的源頭非常多，主要包括如下方面：

數(shù)據(jù)庫的硬件配置：CPU、內(nèi)存、網(wǎng)絡(luò)條件

1. CPU：在任何機(jī)器中CPU的數(shù)據(jù)處理能力往往是衡量計(jì)算機(jī)性能的一個(gè)標(biāo)志，并且ORACLE是一個(gè)提供并行能力的數(shù)據(jù)庫系統(tǒng)，在CPU方面的要求就更高 了，如果運(yùn)行隊(duì)列數(shù)目超過了CPU處理的數(shù)目，性能就會下降，我們要解決的問題就是要適當(dāng)增加CPU的數(shù)量了，當(dāng)然我們還可以將需要許多資源的進(jìn)程 KILL掉；

2. 內(nèi)存：衡量機(jī)器性能的另外一個(gè)指標(biāo)就是內(nèi)存的多少了，在ORACLE中內(nèi)存和我們在建數(shù)據(jù)庫中的交換區(qū)進(jìn)行數(shù)據(jù)的交換，讀數(shù)據(jù)時(shí)，磁盤I/O必須等待物理 I/O操作完成，在出現(xiàn)ORACLE的內(nèi)存瓶頸時(shí)，我們第一個(gè)要考慮的是增加內(nèi)存，由于I/O的響應(yīng)時(shí)間是影響ORACLE性能的主要參數(shù)，我將在這方面 進(jìn)行詳細(xì)的講解

3. 網(wǎng)絡(luò)條件：NET*SQL負(fù)責(zé)數(shù)據(jù)在網(wǎng)絡(luò)上的來往，大量的SQL會令網(wǎng)絡(luò)速度變慢。比如10M的網(wǎng)卡和100的網(wǎng)卡就對NET*SQL有非常明顯的影響， 還有交換機(jī)、集線器等等網(wǎng)絡(luò)設(shè)備的性能對網(wǎng)絡(luò)的影響很明顯，建議在任何網(wǎng)絡(luò)中不要試圖用3個(gè)集線器來將網(wǎng)段互聯(lián)。

如何設(shè)置使oracle10g性能最優(yōu) 性能調(diào)優(yōu) 步驟

一、 磁盤方面調(diào)優(yōu)

1. 規(guī)范磁盤陣列

RAID 10比RAID5更適用于OLTP系統(tǒng)，RAID10先鏡像磁盤，再對其進(jìn)行分段，由于對數(shù)據(jù)的小規(guī)模訪問會比較頻繁，所以對OLTP適用。而RAID5,優(yōu)勢在于能夠充分利用磁盤空間，并且減少陣列的總成本。但是由于陣列發(fā)出一個(gè)寫入請求時(shí)，必須改變磁盤上已修改的塊，需要從磁盤上讀取“奇偶校驗(yàn)”塊，并且使用已修改的塊計(jì)算新的奇偶校驗(yàn)塊，然后把數(shù)據(jù)寫入磁盤，且會限制吞吐量。對性能有所影響，RAID5適用于OLAP系統(tǒng)。

2. 數(shù)據(jù)文件分布

分離下面的東西,避免磁盤競爭

? SYSTEM表空間

? TEMPORARY表空間

? UNDO表空間

? 聯(lián)機(jī)重做日志（放在最快的磁盤上）

? 操作系統(tǒng)磁盤

? ORACLE安裝目錄

? 經(jīng)常被訪問的數(shù)據(jù)文件

? 索引表空間

? 歸檔區(qū)域（應(yīng)該總是與將要恢復(fù)的數(shù)據(jù)分離）

例：

2 /: System

2 /u01: Oracle Software

2 /u02: Temporary tablespace, Control file1

2 /u03: Undo Segments, Control file2

2 /u04: Redo logs, Archive logs, Control file4

2 /u05: System, SYSAUX tablespaces

2 /u06: Data1 ,control file3

2 /u07: Index tablespace

2 /u08: Data2

通過下列語句查詢確定IO問題

select name ,phyrds,phywrts,readtim,writetim

from v$filestat a,v$datafile b

where a.file#=b.file# order by readtim desc;

3. 增大日志文件

u 增大日志文件的大小，從而增加處理大型INSERT,DELETE,UPDATE操作的比例

查詢?nèi)罩疚募顟B(tài)

select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#

查詢?nèi)罩厩袚Q時(shí)間

select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes

from v$log_history a ,v$log_history b

where a.RECID=b.RECID+1

order by a.FIRST_TIME desc

增大日志文件大小，以及對每組增加日志文件（一個(gè)主文件、一個(gè)多路利用文件）

u 增大LOG_CHECKPOINT_INTERVAL參數(shù)，現(xiàn)已不提倡使用它

如果低于每半小時(shí)切換一次日志，就增大聯(lián)機(jī)重做日志大小。如果處理大型批處理任務(wù)時(shí)頻繁進(jìn)行切換，就增大聯(lián)機(jī)重做日志數(shù)目。

alter database add logfile member ‘/log.ora’ to group 1;

alter database drop logfile member ‘/log.ora’;

4. UNDO表空間

修改三個(gè)初始參數(shù)：

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=CLOUDSEA_UNDO

UNDO_RETENTION=#of minutes

5. 不要在系統(tǒng)表空間中執(zhí)行排序

二、 初始化參數(shù)調(diào)優(yōu)

32位的尋址最大支持應(yīng)該是2的32次方，就是4G大小。但實(shí)際中32位系統(tǒng)（XP，windows2003等MS32位系統(tǒng), ubuntu等linux32 位系統(tǒng)）要能利用4G內(nèi)存，都是采用內(nèi)存重映射技術(shù)。需要主板及系統(tǒng)的支持。如果關(guān)閉主板BIOS的重映射功能，系統(tǒng)將不能利用4G內(nèi)存，可能只達(dá)3.5G.而在windows下看到的一般為3.25G。所以SGA設(shè)置為內(nèi)存的40%，但不能超過3.25G

1. 重要初始化參數(shù)

l SGA_MAX_SIZE

l SGA_TARGET

l PGA_AGGREGATE_TARGET

l DB_CACHE_SIZE

l SHARED_POOL_SIZE

2. 調(diào)整DB_CACHE_SIZE來提高性能

它設(shè)定了用來存儲和處理內(nèi)存中數(shù)據(jù)的SGA區(qū)域大小，從內(nèi)存中取數(shù)據(jù)比磁盤快10000倍以上

根據(jù)以下查詢出數(shù)據(jù)緩存命中率

select sum(decode(name,'physical reads',value,0)) phys,

sum(decode(name,'db block gets',value,0)) gets,

sum(decode(name,'consistent gets',value,0)) con_gets,

(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio

from v$sysstat;

一個(gè)事務(wù)處理程序應(yīng)該保證得到95%以上的命中率，命中率從90%提高到98%可能會提高500%的性能，ORACLE正在通過CPU或服務(wù)時(shí)間與等待時(shí)間來分析系統(tǒng)性能，不太重視命中率，不過現(xiàn)在的庫緩存和字典緩存仍將命中率作為基本的調(diào)整方法。

在調(diào)整DB_CACHE_SIZE時(shí)使用V$DB_CACHE_ADVICE

select size_for_estimate, estd_physical_read_factor, estd_physical_reads

from v$db_cache_advice

where name = 'DEFAULT';

如果查詢的命中率過低，說明缺少索引或者索引受到限制，通過V$SQLAREA視圖查詢執(zhí)行緩慢的SQL

3. 設(shè)定DB_BLOCK_SIZE來反映數(shù)據(jù)讀取量大小

OLTP一般8K

OLAP一般16K或者32K

4. 調(diào)整SHARED_POOL_SIZE以優(yōu)化性能

正確地調(diào)整此參數(shù)可以同等可能地共享SQL語句，使得在內(nèi)存中便能找到使用過的SQL語句。為了減少硬解析次數(shù)，優(yōu)化對共享SQL區(qū)域的使用，需盡量使用存儲過程、使用綁定變量

保證數(shù)據(jù)字典緩存命中率在95%以上

select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio

from v$rowcache

where gets+getmisses 0;

如果命中率小于 99％，就可以考慮增加shared pool 以提高library cache 的命中率

SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)

FROM V$LIBRARYCACHE;

通常規(guī)則是把它定為DB_CACHE_SIZE大小的50%－150%，在使用了大量存儲過程或程序包，但只有有限內(nèi)存的系統(tǒng)里，最后分配為150%。在沒有使用存儲過程但大量分配內(nèi)存給DB_CACHE_SIZE的系統(tǒng)里，這個(gè)參數(shù)應(yīng)該為10%－20%

5. 調(diào)整PGA_AGGREGATE_TARGET以優(yōu)化對內(nèi)存的應(yīng)用

u OLTP ：totalmemory*80%*20%

u DSS: totalmemory*80%*50%

6. 25個(gè)重要初始化參數(shù)

2 DB_CACHE_SIZE：分配給數(shù)據(jù)緩存的初始化內(nèi)存

2 SGA_TARGET：使用了自動(dòng)內(nèi)存管理，則設(shè)置此參數(shù)。設(shè)置為0可禁用它

2 PGA_AGGREGATE_TARGET：所有用戶PGA軟內(nèi)存最大值

2 SHARED_POOL_SIZE：分配給數(shù)據(jù)字典、SQL和PL／SQL的內(nèi)存

2 SGA_MAX_SIZE：SGA可動(dòng)態(tài)增長的最大內(nèi)存

2 OPTIMIZER_MODE：

2 CURSOR_SHARING：把字面SQL轉(zhuǎn)換成帶綁定變更的SQL，可減少硬解析開銷

2 OPTIMIZER_INDEX_COST_ADJ：索引掃描成本和全表掃描成本進(jìn)行調(diào)整，設(shè)定在1－10間會強(qiáng)制頻繁地使用索引，保證索引可用性

2 QUERY_REWRITE_ENABLED：用于啟用具體化視圖和基于函數(shù)的索引功能

2 DB_FILE_MULTIBLOCK_READ_COUNT：對于全表掃描，為了更有效執(zhí)行IO，此參數(shù)可在一次IO中讀取多個(gè)塊

2 LOG_BUFFER：為內(nèi)存中沒有提交的事務(wù)分配緩沖區(qū)（非動(dòng)態(tài)參數(shù)）

2 DB_KEEP_CACHE_SIZE：分配給KEEP池或者額外數(shù)據(jù)緩存的內(nèi)存

2 DB_RECYCLE_CACHE_SIZE：

2 DBWR_IO_SLAVES：如果沒有異步IO，參數(shù)等同于DB_WRITER_PROCESSES模擬異步IO而分配的從SGA到磁盤的寫入器數(shù)。如果有異步IO，則使用DB_WRITER_PROCESSES設(shè)置多個(gè)寫程序，在DBWR期間更快地寫出臟塊

2 LARGE_POOL_SIZE：分配給大型PLSQL或其他一些很少使用的ORACLE選項(xiàng)LARGET池的總塊數(shù)

2 STATISTICS_LEVEL：啟用顧問信息，并可選擇提供更多OS統(tǒng)計(jì)信息來改進(jìn)優(yōu)化器決策。默認(rèn)：TYPICAL

2 JAVA_POOL_SIZE：為JVM使用的JAVA存儲過程所分配的內(nèi)存

2 JAVA_MAX_SESSIONSPACE_SIZE：跟蹤JAVA類的用戶會話狀態(tài)所用內(nèi)存上限

2 MAX_SHARED_SERVERS：當(dāng)使用共享服務(wù)器時(shí)的共享服務(wù)器上限

2 WORKAREA_SIZE_POLICY：啟用PGA大小自動(dòng)管理

2 FAST_START_MTTR_TARGET：完成一次崩潰恢復(fù)的大概時(shí)間／S

2 LOG_CHECKPOINT_INTERVAL：檢查點(diǎn)頻率

2 OPEN_CURSORS：指定了保存用戶語句的專用區(qū)域大小，如此設(shè)置過高會導(dǎo)致ORA-4031

2 DB_BLOCK_SIZE：數(shù)據(jù)庫默認(rèn)塊大小

2 OPTIMIZER_DYNAMIC_SAMPLING：控制動(dòng)態(tài)抽樣查詢讀取的塊數(shù)量，對正在使用全局臨時(shí)表的系統(tǒng)非常有用

三、 SQL調(diào)優(yōu)1. 使用提示

1.1 改變執(zhí)行路徑

通過OPTIMIZER_MODE參數(shù)指定優(yōu)化器使用方法，默認(rèn)ALL_ROWS

? ALL_ROWS 可得最佳吞吐量執(zhí)行查詢所有行

? FIRST_ROWS(n) 可使優(yōu)化器最快檢索出第一行:

select /*+ FIRST_ROWS(1) */ store_id,… from tbl_store

1.2 使用訪問方法提示

允許開發(fā)人員改變訪問的實(shí)際查詢方式，經(jīng)常使用INDEX提示

? CLUSTER 強(qiáng)制使用集群

? FULL

? HASH

? INDEX 語法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….

當(dāng)不指定任何INDEX時(shí)，優(yōu)化器會選擇最佳的索引

SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE

? INDEX_ASC 8I開始默認(rèn)是升序，所以與INDEX同效

? INDEX_DESC

? INDEX_COMBINE 用來指定多個(gè)位圖索引，而不是選擇其中最好的索引

? INDEX_JOIN 只需訪問這些索引，節(jié)省了重新檢索表的時(shí)間

? INDEX_FFS 執(zhí)行一次索引的快速全局掃描，只處理索引，不訪問具體表

? INDEX_SS

? INDEX_SSX_ASC

? INDEX_SS_DESC

? NO_INDEX

? NO_INDEX_FFS

? NO_INDEX_SS

1.3 使用查詢轉(zhuǎn)換提示

對于數(shù)據(jù)倉庫非常有幫助

? FACT

? MERGE

? NO_EXPAND 語法：/*+ NO_EXPAND */ column1,…

保證OR組合起的IN列表不會陷入困境，/*+ FIRST_ROWS NO_EXPAND */

? NO_FACT

? NO_MERGE

? NO_QUERY_TRANSFORMATION

? NO_REWRITE

? NO_STAR_TRANSFORMATION

? NO_UNSET

? REWRITE

? STAR_TRANSFORMATION

? UNSET

? USE_CONCAT

1.4 使用連接操作提示

顯示如何將連接表中的數(shù)據(jù)合并在一起，可用兩提示直接影響連接順序。LEADING指定連接順序首先使用的表，ORDERED告訴優(yōu)化器基于FROM子句中的表順序連接這些表，并使用第一個(gè)表作為驅(qū)動(dòng)表（最行訪問的表）

ORDERED語法：/*+ ORDERED */ column 1,….

訪問表順序根據(jù)FROM后的表順序來

LEADING語法：/*+ LEADING(TABLE1) */ column 1,….

類似于ORDER，指定驅(qū)動(dòng)表

? NO_USE_HASH

? NO_USE_MERGE

? NO_USE_NL

? USE_HASH前提足夠的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET

通常可以為較大的結(jié)果集提供最佳的響應(yīng)時(shí)間

? USE_MERGE

? USE_NL 通常可以以最快速度返回一個(gè)行

? USE_NL_WITH_INDEX

1.5 使用并行執(zhí)行

? NO_PARALLEL

? NO_PARALLEL_INDEX

? PARALLEL

? PARALLEL_INDEX

? PQ_DISTRIBUTE

1.6 其他提示

? APPEND 不會檢查當(dāng)前所用塊中是否有剩余空間，而直接插入到表中，會直接將數(shù)據(jù)添加到新的塊中。

? CACHE 會將全表掃描全部緩存到內(nèi)存中，這樣可直接在內(nèi)存中找到數(shù)據(jù)，不用在磁盤上查詢

? CURSOR_SHARING_EXACT

? DRIVING_SITE

? DYNAMIC_SAMPLING

? MODEL_MIN_ANALYSIS

? NOAPPEND

? NOCACHE

? NO_PUSH_PRED

? NO_PUSH_SUBQ

? NO_PX_JOIN_FILTER

? PUSH_PRED

? PUSH_SUBQ 強(qiáng)制先執(zhí)行子查詢，當(dāng)子查詢很快返回少量行時(shí)，這些行可以用于限制外部查詢返回行數(shù)，可極大地提高性能

例：select /*+PUSH_SUBQ */ emp.empno,emp.ename

From emp,orders

where emp.deptno=(select deptno from dept where loc=’1’)

? PX_JOIN_FILTER

? QB_NAME

2. 調(diào)整查詢

2.1 在V$SQLAREA中選出最占用資源的查詢

HASH_VALUE：SQL語句的Hash值。

ADDRESS：SQL語句在SGA中的地址。

PARSING_USER_ID：為語句解析第一條CURSOR的用戶

VERSION_COUNT：語句cursor的數(shù)量

KEPT_VERSIONS：

SHARABLE_MEMORY：cursor使用的共享內(nèi)存總數(shù)

PERSISTENT_MEMORY：cursor使用的常駐內(nèi)存總數(shù)

RUNTIME_MEMORY：cursor使用的運(yùn)行時(shí)內(nèi)存總數(shù)。

SQL_TEXT：SQL語句的文本（最大只能保存該語句的前1000個(gè)字符）。

MODULE,ACTION：用了DBMS_APPLICATION_INFO時(shí)session解析第一條cursor時(shí)信息

SORTS: 語句的排序數(shù)

CPU_TIME: 語句被解析和執(zhí)行的CPU時(shí)間

ELAPSED_TIME: 語句被解析和執(zhí)行的共用時(shí)間

PARSE_CALLS: 語句的解析調(diào)用(軟、硬)次數(shù)

EXECUTIONS: 語句的執(zhí)行次數(shù)

INVALIDATIONS: 語句的cursor失效次數(shù)

LOADS: 語句載入(載出)數(shù)量

ROWS_PROCESSED: 語句返回的列總數(shù)

select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT

from v$sqlarea a ,dba_users b

where a.PARSING_USER_ID=b.user_id and a.DISK_READS100 order by a.DISK_READS desc;

2.2 在V$SQL中選出最占用資源的查詢

與V$SQLAREA類似

select * from

(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)

where rank_buffers 11

2.3 確定何時(shí)使用索引

2 當(dāng)查詢條件只需要返回很少的行（受限列）時(shí)，則需要建立索引，不同的版本中這個(gè)返回要求不同

V5:20% V7:7% V8i,V9i:4% V10g: 5%

查看表上的索引

select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner

from dba_ind_columns a

where a.table_owner='CLOUDSEA'

2 修正差的索引，可使用提示來限制很差的索引，如INDEX，F(xiàn)ULL提示

2 在SELECT 和WHERE中的列使用索引

如: select name from tbl where no=?

建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)

對于系統(tǒng)中很關(guān)鍵的查詢，可以考慮建立此類連接索引

2 在一個(gè)表中有多個(gè)索引時(shí)可能出現(xiàn)麻煩，使用提示INDEX指定使用索引

2 使用索引合并，使用提示INDEX_JOIN

2 基于函數(shù)索引，由于使用了函數(shù)造成查詢很慢.必須基于成本的優(yōu)化模式,參數(shù)：

QUERY_REWRITE_ENALED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)

create index test on sum(test);

2.4 在內(nèi)存中緩存表

將常用的相對小的表緩存到內(nèi)存中，但注意會影響到嵌套循環(huán)連接上的驅(qū)動(dòng)表

alter table tablename cache;

2.5 使用EXISTS 與嵌套子查詢 代替IN

SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);

(方法一: 高效)

SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’

(方法二: 最高效)

SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);

四、 使用STATSPACK和AWR報(bào)表調(diào)整等待和閂鎖

1. 10GR2里的腳本

在$ORACLE_HOME/RDBMS/ADMIN下

Spcreate.sql 通過調(diào)用spcusr.sql spctab.sql 和spcpkg.sql創(chuàng)建STATSPACK環(huán)境，使用SYSDBA運(yùn)行它

Spdrop.sql 調(diào)用sptab.sql和spdusr.sql刪除整個(gè)STATSPACK環(huán)境，使用SYSDBA運(yùn)行它

Spreport.sql 這是生成報(bào)表的主要腳本，由PERFSTAT用戶運(yùn)行

Sprepins.sql 為指定的數(shù)據(jù)庫和實(shí)例生成實(shí)例報(bào)表

Sprepsql.sql 為指定的SQL散列值生成SQL報(bào)表

Sprsqins.sql 為指定的數(shù)據(jù)庫和實(shí)例生成SQL報(bào)表

Spauto.sql 使用DBMS_JOB自動(dòng)進(jìn)行統(tǒng)計(jì)數(shù)據(jù)收集（照相）

Sprepcon.sql 配置SQLPLUS變量來設(shè)置像閾值這樣的內(nèi)容的配置文件

Spurge.sql 刪除給定數(shù)據(jù)庫實(shí)例一定范圍內(nèi)的快照ID，不刪除基線快照

Sptrunc.sql 截短STATSPACK表里所有性能數(shù)據(jù)

五、 執(zhí)行快速系統(tǒng)檢查1. 緩沖區(qū)命中率

查詢緩沖區(qū)命中率

select (1 - (sum(decode(name, 'physical reads',value,0)) /

(sum(decode(name, 'db block gets',value,0)) +

sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"

from v$sysstat;

oracle 性能調(diào)優(yōu)

TROUBLESHOOTING STEPS

For any ONE-TIME occurrence we can safely ignore the error. This error does not cause to any potential data corruption or any data loss. Check Note 35928.1 for known issues and two basic usages of OERI(12333):

Usage with 3 additional numeric values

Usage with 4 additional values ... new since 8.0.X

In case the errors are seen consistently and no known issues matches to your issue then do the following

Common Causes Solutions:

1. Check the client software version. An incompatible client software is a common cause of this error. Upgrade the client software to current version (i.e. match the server version).

The following note explains the supported combinations of clients for a particular RDBMS Server release.

Note 207303.1 Client / Server / Interoperability Support Between Different Oracle Versions

Similar issues are reported when the client installation is faulty.

2. An incompatible NLS Client setting can also trigger such internal errors. Check the NLS settings as explained in the following note.

Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained

3. There are a number of bugs associated with the use of bind peeking. Turn off bind peeking as follows:

alter system set "_optim_peek_user_binds"=false;

4. Please review your listener.log and SQLNET log for network errors at the time of the 1233 errors. Correct these errors.

Try to reproduce the issue from a different client machine, preferably from server. This will rule out the possibility of bad network. If the execution fails from a particular client then it could be due to the bad network from that client to server. The error ORA-600[12333] could occur when a request is being received from a network packet and the request code in the packet is not recognized.

5. Ask your sysadmin to correct any network delays. A slow network can lead to this error.

6. If the error still continues , then do the following to understand from where the error is generated.

We need to understand which session/program/application that triggers this issue. This shall be found from the trace files under the session state object '(session)'. Sometimes the ORA-00600 [12333] trace files will not capture the session information. In such cases we can check with the end users about any issues around the time-frame of this internal error.

There is a HIGH chance for application run-time exception to lead to this ORA-00600 [12333]. Due to the unhandled exceptions there is a chance for incomplete information passed to the server hence the internal errors are raised. In such cases, handling those exceptions (or) addressing those errors on the application code will resolve the issue.

In few cases, the problem application will work for a while before it terminates with the error. This could be due to the TIMEOUT settings at the Application Server (or) Forms Server.,etc. To resolve this either tune the failing application to complete within the TIMEOUT value (or) reset the TIMEOUT to an optimal value.

There could be more possibilities here; in simple words the approach should be from application side, which would help us to understand the other issues that triggered this network interruption (ORA-00600 [12333]).

Check the 'Current SQL Statement' section of the incident trace files. Does each trace file point to the same SQL statement. If so, there may be a problem with the code.

7. If you notice JDBC Client as the failing program under Session state object, please check the following

That you use latest compatible version of JDBC Drivers in your application side.

Note 430839.1 to verify the version of JDBC Drivers and for Installation steps.

Monitor and try to handle correctly the timeouts from the application

Check that idle or died programs/processes are not just killed at the client side but using a good cleaning procedure.

Increase queue sizes / buffers and similar from the jdbc side.

Further diagnostics regarding JDBC Application driver can be found:

Note 1361107.1 Suggestions For Troubleshooting ORA-3137 [12333] Errors Encountered When Using Oracle JDBC Driver

8. In-case if all the above suggestions does not help to identify the cause, and the ORA-00600 [12333] persists we may need to enable the networking tracings from the problem client and need to review the network traces to understand where the problem occurs. See Note 219968.1 Title: SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance. Check the network logs for errors and fix them with the network administrator.

E.g.:

12333錯(cuò)要 具體診斷的

如果自己搞不定可以找詩檀軟件專業(yè)ORACLE數(shù)據(jù)庫修復(fù)團(tuán)隊(duì)成員幫您恢復(fù)!

詩檀軟件專業(yè)數(shù)據(jù)庫修復(fù)團(tuán)隊(duì)

如何對Oracle sql 進(jìn)行性能優(yōu)化的調(diào)整

在SQL查詢中，為了提高查詢的效率，我們常常采取一些措施對查詢語句進(jìn)行SQL性能優(yōu)化。本文我們總結(jié)了一些優(yōu)化措施，接下來我們就一一介紹。

1.查詢的模糊匹配

盡量避免在一個(gè)復(fù)雜查詢里面使用 LIKE '%parm1%'—— 紅色標(biāo)識位置的百分號會導(dǎo)致相關(guān)列的索引無法使用，最好不要用。

解決辦法:

其實(shí)只需要對該腳本略做改進(jìn)，查詢速度便會提高近百倍。改進(jìn)方法如下：

a、修改前臺程序——把查詢條件的供應(yīng)商名稱一欄由原來的文本輸入改為下拉列表，用戶模糊輸入供應(yīng)商名稱時(shí)，直接在前臺就幫忙定位到具體的供應(yīng)商，這樣在調(diào)用后臺程序時(shí)，這列就可以直接用等于來關(guān)聯(lián)了。

b、直接修改后臺——根據(jù)輸入條件，先查出符合條件的供應(yīng)商，并把相關(guān)記錄保存在一個(gè)臨時(shí)表里頭，然后再用臨時(shí)表去做復(fù)雜關(guān)聯(lián)。

2.索引問題

在做性能跟蹤分析過程中，經(jīng)常發(fā)現(xiàn)有不少后臺程序的性能問題是因?yàn)槿鄙俸线m索引造成的，有些表甚至一個(gè)索引都沒有。這種情況往往都是因?yàn)樵谠O(shè)計(jì)表時(shí)，沒去定義索引，而開發(fā)初期，由于表記錄很少，索引創(chuàng)建與否，可能對性能沒啥影響，開發(fā)人員因此也未多加重視。然一旦程序發(fā)布到生產(chǎn)環(huán)境，隨著時(shí)間的推移，表記錄越來越多。這時(shí)缺少索引，對性能的影響便會越來越大了。

法則：不要在建立的索引的數(shù)據(jù)列上進(jìn)行下列操作:

避免對索引字段進(jìn)行計(jì)算操作

避免在索引字段上使用not，，!=

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

避免在索引列上出現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換

避免在索引字段上使用函數(shù)

避免建立索引的列中使用空值

3.復(fù)雜操作

部分UPDATE、SELECT 語句 寫得很復(fù)雜（經(jīng)常嵌套多級子查詢）——可以考慮適當(dāng)拆成幾步，先生成一些臨時(shí)數(shù)據(jù)表，再進(jìn)行關(guān)聯(lián)操作。

4.update

同一個(gè)表的修改在一個(gè)過程里出現(xiàn)好幾十次，如：

update table1 set col1=... where col2=...; update table1 set col1=... where col2=... ...

這類腳本其實(shí)可以很簡單就整合在一個(gè)UPDATE語句來完成（前些時(shí)候在協(xié)助xxx項(xiàng)目做性能問題分析時(shí)就發(fā)現(xiàn)存在這種情況）

5.在可以使用UNION ALL的語句里，使用了UNION

UNION 因?yàn)闀⒏鞑樵冏蛹挠涗涀霰容^，故比起UNION ALL ，通常速度都會慢上許多。一般來說，如果使用UNION ALL能滿足要求的話，務(wù)必使用UNION ALL。還有一種情況大家可能會忽略掉，就是雖然要求幾個(gè)子集的并集需要過濾掉重復(fù)記錄，但由于腳本的特殊性，不可能存在重復(fù)記錄，這時(shí)便應(yīng)該使用 UNION ALL，如xx模塊的某個(gè)查詢程序就曾經(jīng)存在這種情況，見，由于語句的特殊性，在這個(gè)腳本中幾個(gè)子集的記錄絕對不可能重復(fù)，故可以改用UNION ALL）。

6.在WHERE 語句中，盡量避免對索引字段進(jìn)行計(jì)算操作

這個(gè)常識相信絕大部分開發(fā)人員都應(yīng)該知道，但仍有不少人這么使用，我想其中一個(gè)最主要的原因可能是為了編寫寫簡單而損害了性能，那就不可取了。9月份在對XX系統(tǒng)做性能分析時(shí)發(fā)現(xiàn)，有大量的后臺程序存在類似用法，如：where trunc(create_date)=trunc(:date1)，雖然已對create_date 字段建了索引，但由于加了TRUNC，使得索引無法用上。此處正確的寫法應(yīng)該是where create_date=trunc(:date1) and create_date pre=""或者是where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)。

注意：因between 的范圍是個(gè)閉區(qū)間（greater than or equal to low value and less than or equal to high value.），故嚴(yán)格意義上應(yīng)該再減去一個(gè)趨于0的小數(shù)，這里暫且設(shè)置成減去1秒（1/(24*60*60)），如果不要求這么精確的話，可以略掉這步。

當(dāng)前名稱：oracle怎么設(shè)置性能,oracle運(yùn)行內(nèi)存設(shè)置多少合適
本文鏈接：http://chinadenli.net/article38/dsghosp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、網(wǎng)站營銷、微信公眾號、網(wǎng)頁設(shè)計(jì)公司、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站收錄

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)