Oracle维护常用SQL语句整理[记录]

FROM V$librarycache
-----------------------------------------------------------------------
14、查看某些用户的hash
SELECT ername, COUNT(b.Hash_Value) Total_Hash,
SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME,
V$rollstat.Extents Extents, V$rollstat.Rssize Size_In_Bytes,
V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits,
V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status Status
FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname
WHERE V$(+) = Sys.Dba_Rollback_Segs.Segment_Name
6、查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
-----------------------------------------------------------------------
7、回滚段查看
Decode(NAME, 'write requests', VALUE) "Write Request Length"
FROM V$sysstat
WHERE NAME IN ('summed dirty queue length', 'write requests')
AND VALUE > 0;
WHERE Time_Remaining <> 0
AND Sql_Address = Address
AND Sql_Hash_Value = Hash_Value;
-----------------------------------------------------------------------
-----------------------------------------------------------------------
13、查看库缓存命中率
SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins,
Pinhitratio * 100 Pinhitratio, Reloads, Invalidations
4、监控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;
AND b.Statistic# = 41 AND c.Statistic# Nhomakorabea= 42;
SELECT NAME,
(1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio
FROM V$buffer_Pool_Statistics;
-----------------------------------------------------------------------
9、计算data buffer 命中率
SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads",
d.Status
FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d
-----------------------------------------------------------------------
5、求数据文件的I/O分布
SELECT , Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,
-----------------------------------------------------------------------
3、求DISK SORT严重的SQL
SELECT ername, SQL.Sql_Text, Sort1.Blocks
FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1
12、查看对象的缓存情况
SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks,
Pins, Kept
FROM V$db_Object_Cache
WHERE TYPE NOT IN
('NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE')
-----------------------------------------------------------------------
10、查看内存使用情况
SELECT Least(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Used,
WHERE Sess.Serial# = Sort1.Session_Num
AND Sort1.Sqladdr = SQL.Address
AND Sort1.Sqlhash = SQL.Hash_Value
AND Sort1.Blocks > 200;
-----------------------------------------------------------------------
FROM V$rowcache;
-----------------------------------------------------------------------
16、查看undo段的使用情况
SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive,
AND Executions > 0
AND Loads > 1
AND Kept = 'NO'
ORDER BY Owner, Namespace, TYPE, Executions DESC;
SELECT TYPE, COUNT(*)
FROM V$db_Object_Cache
GROUP BY TYPE;
FROM Sys.v_$sqlarea a, Dba_Users b
WHERE a.Parsing_User_Id = er_Id
GROUP BY Username;
-----------------------------------------------------------------------
((SUM(a.Bytes) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct
FROM V$sgastat a, V$parameter b
WHERE (a.Pool = 'shared pool' AND NOT IN ('free memory'))
COUNT(b.Hash_Value) - COUNT(UNIQUE(b.Hash_Value)) Same_Hash,
(COUNT(UNIQUE(b.Hash_Value)) / COUNT(b.Hash_Value)) * 100 u_Hash_Ratio
FROM Dba_Users a, V$sqlarea b
column progress format a8
SELECT Username, Sid, Opname,
Round(Sofar * 100 / Totalwork, 0) || '%' AS Progress, Time_Remaining,
Sql_Text
FROM V$session_Longops, V$sql
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# = 40
2、求DISK READ较多的SQL
SELECT St.Sql_Text
FROM V$sql s, V$sqltext St