利用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)性能監(jiān)控

摘 要： 數(shù)據(jù)庫(kù)是各個(gè)氣象信息系統(tǒng)的基礎(chǔ)，為了保證氣象信息系統(tǒng)中數(shù)據(jù)庫(kù)的持續(xù)可靠和高性能運(yùn)行，需要對(duì)數(shù)據(jù)庫(kù)性能進(jìn)行監(jiān)控，依據(jù)數(shù)據(jù)庫(kù)性能監(jiān)控結(jié)果及時(shí)地做出適當(dāng)調(diào)整。根據(jù)實(shí)際需求摸索了一種利用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)性能監(jiān)控的方法，具體探討了如何利用SQL語(yǔ)句實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)性能監(jiān)控。該方法的應(yīng)用對(duì)于數(shù)據(jù)庫(kù)性能監(jiān)控有指導(dǎo)意義。

關(guān)鍵詞： SQL語(yǔ)句； 數(shù)據(jù)庫(kù)； 性能監(jiān)控； Oracle

中圖分類(lèi)號(hào)：TP393 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：1006-8228（2013）12-50-02

Using SQL statements to realize database performance monitoring

Ji Gang

（Anhui Meteorological Information Center， Hefei， Anhui 230031， China）

Abstract： Database is the foundations of various meteorological information systems. In order to ensure the reliable and efficient running of information system， the database performance monitoring is required. Based on the monitoring results of database performance the appropriate adjustments can be done in time. A database performance monitoring method is proposed using SQL statements according to the actual needs. Firstly， SQL statements are introduced. An example is presented for demonstrating how to use SQL statements to realize the database performance monitoring. For some particular system， it has guiding significance.

Key words： SQL statements； database； performance monitoring； Oracle

0 引言

隨著數(shù)據(jù)庫(kù)技術(shù)的不斷發(fā)展，數(shù)據(jù)庫(kù)在各行各業(yè)中的應(yīng)用越來(lái)越廣泛[1-4]。目前，氣象系統(tǒng)中開(kāi)發(fā)、使用了大量業(yè)務(wù)信息系統(tǒng)，數(shù)據(jù)庫(kù)是這些系統(tǒng)的基礎(chǔ)。數(shù)據(jù)庫(kù)系統(tǒng)如果運(yùn)行性能過(guò)低，甚至無(wú)法運(yùn)行，會(huì)對(duì)相應(yīng)業(yè)務(wù)造成不可估量的損失，因而確保系統(tǒng)中各個(gè)數(shù)據(jù)庫(kù)的持續(xù)可靠和高性能運(yùn)行是相當(dāng)重要的。為了確保系統(tǒng)中各個(gè)數(shù)據(jù)庫(kù)持續(xù)可靠、高效地運(yùn)行，需要對(duì)數(shù)據(jù)庫(kù)性能進(jìn)行監(jiān)控，本文根據(jù)實(shí)際需求摸索了一種利用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)性能監(jiān)控的方法，該方法對(duì)于數(shù)據(jù)庫(kù)管理和開(kāi)發(fā)人員具有一定的應(yīng)用指導(dǎo)意義。

1 SQL介紹

SQL（Structured Query Language）結(jié)構(gòu)化查詢語(yǔ)言[5]，是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語(yǔ)言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)，同時(shí)也是數(shù)據(jù)庫(kù)腳本文件的擴(kuò)展名。SQL是高級(jí)的非過(guò)程化編程語(yǔ)言，是溝通數(shù)據(jù)庫(kù)服務(wù)器和客戶端的重要工具，允許用戶在高層數(shù)據(jù)結(jié)構(gòu)上工作。它不要求用戶指定對(duì)數(shù)據(jù)的存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式，所以，具有完全不同底層結(jié)構(gòu)的不同數(shù)據(jù)庫(kù)系統(tǒng)，可以使用相同的SQL語(yǔ)言作為數(shù)據(jù)輸入與管理的接口。SQL語(yǔ)言包括三種主要程序設(shè)計(jì)語(yǔ)言類(lèi)別的語(yǔ)句：數(shù)據(jù)定義語(yǔ)言（DDL），數(shù)據(jù)操作語(yǔ)言（DML）及數(shù)據(jù)控制語(yǔ)言（DCL）。

2 數(shù)據(jù)庫(kù)性能監(jiān)控的實(shí)現(xiàn)

影響數(shù)據(jù)庫(kù)性能的因素主要包括兩個(gè)方面：數(shù)據(jù)庫(kù)自身參數(shù)設(shè)置和應(yīng)用是否適合，以及數(shù)據(jù)庫(kù)所在服務(wù)器的資源使用情況。利用SQL語(yǔ)句可以很方便地對(duì)數(shù)據(jù)庫(kù)性能進(jìn)行監(jiān)控，如監(jiān)控表空間的I/O比例、SGA情況、Session情況和碎片程度等。而對(duì)服務(wù)器資源的監(jiān)控可以使用系統(tǒng)自帶工具來(lái)完成，下面是利用SQL語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)庫(kù)性能監(jiān)控的詳細(xì)描述。

⑴ CPU占用前20

select ROWNUM top， x.* from （select/*cpu*/u.SID， u.serial#，

s.VALUE cpu， u.username， u.machine，u.program u.osuser

from v$session u ，v$sesstat s where s.SID = u.SID and

statistic# IN（12） order by cpu DESC） x where ROWNUM

<21；

⑵ 內(nèi)存占用前20

select ROWNUM top x.* from （select u.SID， u.serial#，

s.VALUE mem， u.username， u.machine， u.program u.osuser

from v$session u ，（select /*mem* SID， SUM（VALUE） value

from v$sesstat where statistic# IN （20，15） GROUP BY SID）

s where s.SID=u.SID order by mem DESC） x where

ROWNUM <21；

⑶ 會(huì)話的內(nèi)存占用

select se.sid， n.name， max（se.value） maxmem from v

$sesstat se， v$statname n where n.statistic#=se.statistic#

and n.name in （‘session pga memory’， ’session pga

memory max’， ‘session uga memory’， ‘session uga

memory max’） group by n.name， se.sid order by 1，3；

⑷ IO占用前20

select ROWNUM top x.* from （select u.SID， u.serial#，

s.VALUE io， u.username， u.machine，u.program u.osuser

from v$session u ，（select /*io* SID， SUM（VALUE） value

from v$sesstat where statistic# IN （40，44，9） GROUP

BY SID） s where s.SID=u.SID order by io DESC）

x where ROWNUM <21；

⑸ 監(jiān)控表空間的I/O比例

select df.tablespace_name name，df.file_name \"file\"，f.phyrds

pyr，f.phyblkrd pbr，f.phywrts pyw， f.phyblkwrt pbw from

v$filestat f， dba_data_files df where f.file#=df.file_id

order by df.tablespace_name；

⑹ 監(jiān)控文件系統(tǒng)的I/O比例

select substr（a.file#，1，2） \"#\"， substr（a.name，1，30） \"Name\"，

a.status， a.bytes， b.phyrds， b.phywrts from v$datafile a，

v$filestat b where a.file#=b.file#；

⑺ 監(jiān)控SGA情況

SGA是用來(lái)存放所有數(shù)據(jù)庫(kù)進(jìn)程共享的數(shù)據(jù)和控制信息的存儲(chǔ)區(qū)域，當(dāng)數(shù)據(jù)庫(kù)一啟動(dòng)SGA就立即占有服務(wù)器的內(nèi)存空間，SGA中的庫(kù)高速緩存、字典高速緩存、數(shù)據(jù)高速緩存以及日志緩沖區(qū)的大小對(duì)系統(tǒng)性能有極大的影響。

監(jiān)控SGA的命中率：

select a.value + b.value \"logical_reads\"，c.value \"phys_reads\"，

round（100 * （（a.value+b.value）-c.value）/（a.value +b.value））

\"BUFFER HIT RATIO\" from v$sysstat a， v$sysstat b，

v$sysstat c where a.statistic#=38 and b.statistic#=39

and c.statistic#=40；

監(jiān)控SGA中字典緩沖區(qū)的命中率：

select parameter，gets，Getmisses ，getmisses/（gets+getmisses）

*100 \"miss ratio\"，（1-（sum（getmisses）/ （sum（gets）+ sum

（getmisses））））*100 \"Hit ratio\" from v$rowcache where gets

+getmisses <>0 group by parameter， gets， getmisses；

監(jiān)控SGA中共享緩存區(qū)的命中率，應(yīng)該小于1%：

select sum（pins） \"Total Pins\"， sum（reloads） \"Total Reloads”，

sum（reloads）/sum（pins） *100 libcache from v$librarycache；

select sum（pinhits-reloads）/sum（pins） \"hit radio\"， sum（reloads）/

sum（pins） \"reload percent\" from v$librarycache；

監(jiān)控SGA中重做日志緩存區(qū)的命中率，應(yīng)該小于1%：

select name， gets， misses， immediate_gets，

immediate_misses， Decode （gets，0，0， misses/gets*100）

ratio1， Decode （immediate_gets+immediate_misses，0，0，

immediate_misses/（immediate_gets+immediate_misses）*100）

ratio2 from v$latch where name IN （'redo allocation'，

'redo copy'）；

⑻ 監(jiān)控當(dāng)前數(shù)據(jù)庫(kù)誰(shuí)在運(yùn)行什么SQL語(yǔ)句

select osuser， username， sql_text from v$session a，

v$sqltext b where a.sql_address=b.address order by

address， piece；

⑼ 監(jiān)控碎片程度

select tablespace_name， count （tablespace_name） from

dba_free_space group by tablespace_namen having count

（tablespace_name）>10；

alter tablespace name coalesce；

alter table name deallocate unused；

create or replace view ts_blocks_v as select

tablespace_name，block_id，bytes，blocks， gment_name from

dba_free_space union all select tablespace_name， block_id，

bytes， blocks， segment_name from dba_extents；

select * from ts_blocks_v；

select tablespace_name，sum（bytes），max（bytes），count（block_id）

from dba_free_space group by tablespace_name；

查看碎片程度高的表：

select segment_name table_name， COUNT （*） extents from

dba_segments where owner NOT IN （'SYS'， 'SYSTEM'）

GROUP BY segment_name HAVING COUNT（*）=

（SELECT MAX（COUNT（*）） from dba_segments GROUP

BY segment_name）；

⑽ 找使用CPU多的用戶session

select a.sid，spid，status，substr（a.program，1，40） prog， a.terminal，

osuser，value/60/100 value from v$session a，v$process b，

v$sesstat c where c.statistic#=12 and c.sid=a.sid and

a.paddr=b.addr order by value desc；

⑾ 會(huì)話的IO命中率

select username， osuser， ROUND（100 * （consistent_gets +

block_gets-physical_reads）/（consistent_gets + block_gets））

“Hit_Ratio %”， consistent_gets， block_gets，physical_reads

from v$session， v$sess_io where v$session.SID=

v$sess_io.SID and consistent_gets + block_gets >0 and

username IS NOT NULL order by username， “Hit_Ratio %”；

⑿ 表、索引的存儲(chǔ)情況檢查

select segment_name， sum （bytes）， count （*） ext_quan from

dba_extents where tablespace_name='tablespace_name'

and segment_type='TABLE' group by tablespace_name，

segment_name；

select segment_name，count（*） from dba_extents where

segment_type='INDEX' and owner='owner' group by

segment_name；

⒀ 等待前20的事件

select * from （select event， total_waits， total_timeouts，

ROUND （total_timeouts，*100/ total_waits） “Waits%” from v

$system_event t where total_waits >5 order by 4 DESC）

where ROWNUM <21；

⒁ 查找執(zhí)行時(shí)間很長(zhǎng)的SQL

Select v.OPNAME， nvl（v.TARGET， v.TARGET_DESC），

v.TOTAL_WORK，v.SOFAR， round（v.SOFAR*100.00/

v.TOTAL_WORK， 2） finished， v.TIME_REMAINING，

v.ELAPSED_SECONDS， v.UNITS， v.START_TIME，

v.LAST_UPDATE_TIME， v.MESSAGE， s.USERNAME，

s.SCHEMANAME， s.MACHINE， s.PROGRAM， s.MODULE，

s.EVENT， s.SERVICE_NAME from v$session_longops v，

v$session s where v.SID=s.SID and

v.ELAPSED_SECONDS >=5 order by v.START_TIME；

⒂ 查看占IO較大的正在運(yùn)行的session

select se.sid， se.serial#， pr.SPID， se.username， se.statusse

.terminal， se.program， se.MODULE， se.sql_address， st.event，

st.p1text， si.physical_reads， si.block_changes from v$session

se， v$session_wait st， v$sess_io si， v$process pr， where st.

sid=se.sid and st.sid=si.sid and se.PADDR=pr.ADDR AND

se.sid > 6 and st.wait_time =0 and st.event NOT LIKE

‘%SQL%’ order by physical_reads DESC；

3 結(jié)束語(yǔ)

本文描述了利用SQL語(yǔ)句實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)性能的監(jiān)控，數(shù)據(jù)庫(kù)管理員可以隨時(shí)監(jiān)控?cái)?shù)據(jù)庫(kù)服務(wù)器運(yùn)行狀態(tài)，及早發(fā)現(xiàn)服務(wù)器性能下降的問(wèn)題，依據(jù)數(shù)據(jù)庫(kù)性能監(jiān)控結(jié)果及時(shí)地做出適當(dāng)調(diào)整，抑制數(shù)據(jù)庫(kù)性能下滑的趨勢(shì)，使數(shù)據(jù)庫(kù)服務(wù)器始終工作在性能較優(yōu)的狀態(tài)下。該方法在氣象信息系統(tǒng)中的應(yīng)用實(shí)踐表明，利用SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)性能監(jiān)控，可以有效地保證數(shù)據(jù)庫(kù)的持續(xù)可靠和高性能運(yùn)行。

參考文獻(xiàn)：

[1] 翟巖龍，宿紅毅，戰(zhàn)守義.網(wǎng)格數(shù)據(jù)庫(kù)性能監(jiān)控研究與設(shè)計(jì)[J].計(jì)算機(jī)

工程，2007.33（20）：64-69

[2] 許建中，戚飛虎.基于分布式數(shù)據(jù)庫(kù)系統(tǒng)的監(jiān)控系統(tǒng)的開(kāi)發(fā)模型及其

實(shí)現(xiàn)[J].計(jì)算機(jī)工程與應(yīng)用，2003.1：198-201

[3] 杜慶峰，張衛(wèi)山.Oracle的中大型應(yīng)用系統(tǒng)性能優(yōu)化分析[J] 計(jì)算機(jī)工

程，2005.31（14）：91-93

[4] 田李，李?lèi)?ài)平，賈焰等.一種大規(guī)模分布式監(jiān)控系統(tǒng)中預(yù)測(cè)模型的研

究[J].計(jì)算機(jī)研究與發(fā)展，2006.43（增）：565-570

[5] Ingram G著，張建明，英宇譯.Oracle性能優(yōu)化[M].清華大學(xué)出版社，

2003.