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

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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：主機(jī)域名、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、長島網(wǎng)站維護(hù)、網(wǎng)站推廣。

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

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

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

執(zhí)行SQL后，可以看到每個(gè)服務(wù)器連接oracle數(shù)據(jù)庫的連接數(shù)，若某個(gè)服務(wù)器的連接數(shù)非常大，或者已經(jīng)達(dá)到其最大連接數(shù)，那么這臺服務(wù)器上的應(yīng)用可能有問題導(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查詢出來的結(jié)果不能看到具體的SQL語句，需要看具體SQL語句的執(zhí)行下面的方法。

3、查詢每個(gè)活動的連接執(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ù)庫用戶名，一個(gè)sql_hash_value下的SQL_TEXT組合成一個(gè)完整的SQL語句。這樣就可以看到一個(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列來替換disk_reads列可以得到占用最多內(nèi)存的sql語句的相關(guān)信息。

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

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

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語句。對于這種實(shí)用非常頻繁的SQL語句，我們需要對其進(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ù)雜的查詢中會遇到，對于這樣的大數(shù)據(jù)量查詢SQL語句更加需要持續(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è)語句在SQL性能查看中用的比較多，可以明顯的看出哪些SQL會影響到數(shù)據(jù)庫性能。

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

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

如何去評估oracle系統(tǒng)存在io性能問題

簡單點(diǎn)判斷的話，你top或topas觀察下，cpu和磁盤讀的負(fù)載情況。

然后生成一份業(yè)務(wù)高峰時(shí)段的AWR報(bào)告，看看top 5等待事件主要是哪些，是不是跟磁盤讀相關(guān)的等待事件(比如全表掃描)

降低IO最有效的方法就是優(yōu)化sql語句，避免大表全表掃描，根據(jù)awr報(bào)告中sga各個(gè)內(nèi)存組件的使用情況，適當(dāng)調(diào)整buffer cache的值，來減少磁盤IO

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

這種問題要回答好要求知識比較全面。

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

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

2 從磁盤上看

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

3 從數(shù)據(jù)庫本身來看。

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

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

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

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

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

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

如何進(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ū)的活動情況，庫緩沖區(qū)的活動統(tǒng)計(jì)信息保留在動態(tài)性能表v$librarycache數(shù)據(jù)字典中，可通過查詢該表來了解其活動情況，以決定如何調(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ū)的使用情況記錄在動態(tài)性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調(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ū)高速緩存的使用情況記錄在動態(tài)性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調(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ù)庫，在重新啟動數(shù)據(jù)庫后才能使新的設(shè)置起作用。