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

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

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

中圖分類號：TP393 文獻標志碼：A 文章編號：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ù)庫技術的不斷發(fā)展，數(shù)據(jù)庫在各行各業(yè)中的應用越來越廣泛[1-4]。目前，氣象系統(tǒng)中開發(fā)、使用了大量業(yè)務信息系統(tǒng)，數(shù)據(jù)庫是這些系統(tǒng)的基礎。數(shù)據(jù)庫系統(tǒng)如果運行性能過低，甚至無法運行，會對相應業(yè)務造成不可估量的損失，因而確保系統(tǒng)中各個數(shù)據(jù)庫的持續(xù)可靠和高性能運行是相當重要的。為了確保系統(tǒng)中各個數(shù)據(jù)庫持續(xù)可靠、高效地運行，需要對數(shù)據(jù)庫性能進行監(jiān)控，本文根據(jù)實際需求摸索了一種利用SQL語句實現(xiàn)數(shù)據(jù)庫性能監(jiān)控的方法，該方法對于數(shù)據(jù)庫管理和開發(fā)人員具有一定的應用指導意義。

1 SQL介紹

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

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

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

⑴ 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；

⑵ 內存占用前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；

⑶ 會話的內存占用

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是用來存放所有數(shù)據(jù)庫進程共享的數(shù)據(jù)和控制信息的存儲區(qū)域，當數(shù)據(jù)庫一啟動SGA就立即占有服務器的內存空間，SGA中的庫高速緩存、字典高速緩存、數(shù)據(jù)高速緩存以及日志緩沖區(qū)的大小對系統(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ū)的命中率，應該小于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ū)的命中率，應該小于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)控當前數(shù)據(jù)庫誰在運行什么SQL語句

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；

⑾ 會話的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 %”；

⑿ 表、索引的存儲情況檢查

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í)行時間很長的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較大的正在運行的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 結束語

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

參考文獻：

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

工程，2007.33（20）：64-69

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

實現(xiàn)[J].計算機工程與應用，2003.1：198-201

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

程，2005.31（14）：91-93

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

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

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

2003.