【转】Useful SQL reference 一些有用的SQL
一些有用的SQL，都是oracle manage常用的。
列在这里做参考，因为太难记了。
时时更新。
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。表空间使用状态
select 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 的命中率
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;
6。监控 SGA 中字典缓冲区的命中率
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;
7。监控 SGA 中共享缓存区的命中率，应该小于1%
select 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。哪筆數據正在被人update，而且是被誰正在update
select 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
列在这里做参考，因为太难记了。
时时更新。
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。表空间使用状态
select 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 的命中率
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;
6。监控 SGA 中字典缓冲区的命中率
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;
7。监控 SGA 中共享缓存区的命中率，应该小于1%
select 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。哪筆數據正在被人update，而且是被誰正在update
select 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
