一些常用查詢指令碼

• 查詢Oracle的版本資訊： select * from v$version
• 查詢資料庫的基本資訊：select * from v$database
• 查詢license資訊：select * from v$license
• 查詢初始化引數：v$parameter
• 查詢分配記憶體的詳細資訊：v$sgastat
• 查詢資料在記憶體的命中率：

select 1-(sum(decode(name,'physical reads',value, 0))/(sum(decode(name, 'db block gets', value, 0))+(sum(decode(name, 'consistent gets', value, 0))))) from v$sysstat

• 查詢資料字典的在記憶體的命中率：

select sum(gets), sum(getmisses),(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 hitrate from v$rowcache

• 查詢SQL及PL/SQL的在記憶體的命中率：

select sum(pins) "executions", sum(pinhits) "hits", ((sum(pinhits)/sum(pins))*100) "PinHitRatio", sum(reloads) "misses", ((sum(pins)/(sum(pins)+sum(reloads)))*100) "RelHitRatio"

from v$librarycache

• 查詢有問題的查詢：

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_usrs b where a.parsing_user_id=b.user_id and a.disk_reads>10000 order by a.disk_reads desc

• 查詢使用者及其操作：

select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address=s.address and a.sql_hash_value=s.hash_value order by a.username,a.sid, s.piece

• 查詢使用者正在訪問的物件：

select a.sid, a.username, b.owner, b.object, b.type from v$session a, v$access b where a.sid=b.sid

• 查詢存在多個會話的使用者：

select username, count(*) from v$session group by username

• 查詢磁碟IO問題：

select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file$=b.file#