UsefulSQLreference

文章作者 100test 发表时间 2007:09:25 12:53:44
来源 100Test.Com百考试题网

1。监控当前数据库谁在运行什么SQL 语句

SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece.

2。查看碎片程度高的表

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).

3。表空间使用状态

0select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id( )
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name

4。查看USER

SELECT OSUSER,SERIAL#
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS AND
V$SESSION.STATUS = ACTIVE.

5。监控 SGA 的命中率
0select 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.

6。监控 SGA 中字典缓冲区的命中率
0select 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.

7。监控 SGA 中共享缓存区的命中率，应该小于1%
0select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache.

8。监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk)).

9。哪筆數據正在被人0update，而且是被誰正在0update
0select a.os_user_name, a.oracle_username,a.object_id,c.object_name,c.object_type
from v$locked_object a, dba_objects c
where a.object_id=c.object_id