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

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

我們提供的服務(wù)有：成都網(wǎng)站制作、網(wǎng)站建設(shè)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、船山ssl等。為1000+企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的船山網(wǎng)站制作公司

如何檢查oracle數(shù)據(jù)庫(kù)性能

這種問(wèn)題要回答好要求知識(shí)比較全面。

1 從操作系統(tǒng)層次上看

看CPU 內(nèi)存 swqp（交換分區(qū)）等使用率

2 從磁盤(pán)上看

主要看磁盤(pán)讀寫(xiě)。可以用dd測(cè)磁盤(pán)讀寫(xiě)的速度 也可以在業(yè)務(wù)高峰期檢測(cè)磁盤(pán)的速率。

3 從數(shù)據(jù)庫(kù)本身來(lái)看。

先要看數(shù)據(jù)庫(kù)各個(gè)參數(shù)的值 。 如sga的大小，process的大小，redo日志的個(gè)數(shù)與大小等這些關(guān)系到性能的參數(shù)是否設(shè)置合理。

長(zhǎng)期觀察的方式就是看各個(gè)時(shí)期的AWR報(bào)告。里面有各種性能指標(biāo)，以及按執(zhí)行時(shí)間或資源排列的sql ，以及各種等待時(shí)間的排名。從這里面可以掌握數(shù)據(jù)庫(kù)的長(zhǎng)期的性能變化。

即時(shí)觀察的方式就是利用各種sql 查詢 數(shù)據(jù)庫(kù)在當(dāng)前時(shí)間的各個(gè)性能指標(biāo)(AWR報(bào)告里面的各種指標(biāo)也都是通過(guò)sql查詢出來(lái)的)

還有對(duì)數(shù)據(jù)庫(kù)整體的一個(gè)檢查：

如 表的大小，表是否需要分區(qū)而沒(méi)有分區(qū)，索引是否創(chuàng)建，索引是否失效，開(kāi)發(fā)人員寫(xiě)的sql是否正確使用到了索引，頻繁使用的sql是否有綁定變量，有頻繁大批量增刪改的表是否存在高水位。。。

額 總之，這個(gè)話題涉及的知識(shí)非常多，盡可能多的學(xué)習(xí)一些東西，祝你好運(yùn)。

如何查詢Oracle性能監(jiān)控

可以通過(guò)生成數(shù)據(jù)庫(kù)性能報(bào)告查看數(shù)據(jù)庫(kù)性能

數(shù)據(jù)庫(kù)版本：oracle 9i

statspack

SQL conn perfstat/perfstat

SQL execute statspack.snap

SQL @$ORACLE_HOME/rdbms/admin/spreport.sql

數(shù)據(jù)庫(kù)版本：oracle 10g,11g

awr

SQL execute dbms_workload_repository.create_snapshot;

SQL @$ORACLE_HOME/rdbms/admin/awrrpt.sql

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

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

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

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

3、有人說(shuō)使用如下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ù)做了測(cè)試，效果一般，依然很慢，原因：

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

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

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

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

對(duì)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功能來(lái)完成

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

job_queue_processes=5

重新啟動(dòng)oracle數(shù)據(jù)庫(kù)服務(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，索引重建

重建索引會(huì)刪除原來(lái)的索引，重新生成索引，需要較長(zhǎng)的時(shí)間。

重建索引語(yǔ)法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

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

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

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

如何查詢oracle 數(shù)據(jù)庫(kù)性能，sql資源占用

作為一個(gè)開(kāi)發(fā)/測(cè)試人員，或多或少都得和數(shù)據(jù)庫(kù)打交道，而對(duì)數(shù)據(jù)庫(kù)的操作歸根到底都是SQL語(yǔ)句，所有操作到最后都是操作數(shù)據(jù)，那么對(duì)sql性能的掌控又成了我們工作中一件非常重要的工作。下面簡(jiǎn)單介紹下一些查看oracle性能的一些實(shí)用方法：

1、查詢每臺(tái)機(jī)器的連接數(shù)

select?t.MACHINE,count(*)?from?v$session?t?group?by?t.MACHINE

這里所說(shuō)的每臺(tái)機(jī)器是指每個(gè)連接oracle數(shù)據(jù)庫(kù)的服務(wù)器，每個(gè)服務(wù)器都有配置連接數(shù)據(jù)庫(kù)的連接數(shù)，以websphere為例，在數(shù)據(jù)源中，每個(gè)數(shù)據(jù)源都有配置其最大/最小連接數(shù)。

執(zhí)行SQL后，可以看到每個(gè)服務(wù)器連接oracle數(shù)據(jù)庫(kù)的連接數(shù)，若某個(gè)服務(wù)器的連接數(shù)非常大，或者已經(jīng)達(dá)到其最大連接數(shù)，那么這臺(tái)服務(wù)器上的應(yīng)用可能有問(wèn)題導(dǎo)致其連接不能正常釋放。

2、查詢每個(gè)連接數(shù)的sql_text

v$session表里存在的連接不是一直都在執(zhí)行操作，如果sql_hash_value為空或者0，則該連接是空閑的，可以查詢哪些連接非空閑，?web3?是機(jī)器名，就是WebSphere?Application?Server?的主機(jī)名。

select?t.sql_hash_value,t.*??from?v$session?t?where?t.MACHINE='web3'?and?t.sql_hash_value!=0

這個(gè)SQL查詢出來(lái)的結(jié)果不能看到具體的SQL語(yǔ)句，需要看具體SQL語(yǔ)句的執(zhí)行下面的方法。

3、查詢每個(gè)活動(dòng)的連接執(zhí)行什么sql

select?sid,username,sql_hash_value,b.sql_text

from?v$session?a,v$sqltext?b

where?a.sql_hash_value?=?b.HASH_VALUE?and?a.MACHINE='web3'

order?by?sid,username,sql_hash_value,b.piece

order?by這句話的作用在于，sql_text每條記錄不是保存一個(gè)完整的sql,需要以sql_hash_value為關(guān)鍵id,以piece排序，如圖

Username是執(zhí)行SQL的數(shù)據(jù)庫(kù)用戶名，一個(gè)sql_hash_value下的SQL_TEXT組合成一個(gè)完整的SQL語(yǔ)句。這樣就可以看到一個(gè)連接執(zhí)行了哪些SQL。

4、.從V$SQLAREA中查詢最占用資源的查詢

select?b.username?username,a.disk_reads?reads,?a.executions?exec,

a.disk_reads/decode(a.executions,0,1,a.executions)?rds_exec_ratio,

a.sql_text?Statement

from??v$sqlarea?a,dba_users?b

where?a.parsing_user_id=b.user_id

and?a.disk_reads??100000

order?by?a.disk_reads?desc;

用buffer_gets列來(lái)替換disk_reads列可以得到占用最多內(nèi)存的sql語(yǔ)句的相關(guān)信息。

V$SQL是內(nèi)存共享SQL區(qū)域中已經(jīng)解析的SQL語(yǔ)句。

該表在SQL性能查看操作中用的比較頻繁的一張表，關(guān)于這個(gè)表的詳細(xì)信息大家可以去?上學(xué)習(xí)，介紹得比較詳細(xì)。我這里主要就將該表的常用幾個(gè)操作簡(jiǎn)單介紹一下：

1、列出使用頻率最高的5個(gè)查詢：

select?sql_text,executions

from?(select?sql_text,executions,

rank()?over

(order?by?executions?desc)?exec_rank

from?v$sql)

where?exec_rank?=5;

該查詢結(jié)果列出的是執(zhí)行最頻繁的5個(gè)SQL語(yǔ)句。對(duì)于這種實(shí)用非常頻繁的SQL語(yǔ)句，我們需要對(duì)其進(jìn)行持續(xù)的優(yōu)化以達(dá)到最佳執(zhí)行性能。

2、找出需要大量緩沖讀?。ㄟ壿嬜x）操作的查詢：

select?buffer_gets,sql_text

from?(select?sql_text,buffer_gets,

dense_rank()?over

(order?by?buffer_gets?desc)?buffer_gets_rank

from?v$sql)

where?buffer_gets_rank=5;

這種需要大量緩沖讀?。ㄟ壿嬜x）操作的SQL基本是大數(shù)據(jù)量且邏輯復(fù)雜的查詢中會(huì)遇到，對(duì)于這樣的大數(shù)據(jù)量查詢SQL語(yǔ)句更加需要持續(xù)的關(guān)注，并進(jìn)行優(yōu)化。

3、持續(xù)跟蹤有性能影響的SQL。

SELECT?*?FROM?(

SELECT?PARSING_USER_ID,EXECUTIONS,SORTS,

COMMAND_TYPE,DISK_READS,sql_text?FROM?v$sqlarea

ORDER?BY?disk_reads?DESC

)

WHERE?ROWNUM10

這個(gè)語(yǔ)句在SQL性能查看中用的比較多，可以明顯的看出哪些SQL會(huì)影響到數(shù)據(jù)庫(kù)性能。

本文主要介紹了使用SQL查詢方式查看oracle數(shù)據(jù)庫(kù)SQL性能的部分常用方法。此外還有許多工具也能實(shí)現(xiàn)SQL性能監(jiān)控，大家可以在網(wǎng)上搜索相關(guān)知識(shí)進(jìn)行學(xué)習(xí)。

轉(zhuǎn)載僅供參考，版權(quán)屬于原作者