ORACLE系统查询语句(二)
t_id
AND s.sid = l.session_id
AND s.sid = k.sid
ORDER BY s.sid
/* 查询事务中的锁和相关信息 */
SELECT /*+ ordered */
o.object_name, o.object_type,k.type, l.locked_mode, s.machine, l.os_user_name,
s.terminal, l.oracle_username, o.subobject_name, s.program, s.module,
k.id1, k.id2, k.request, k.ctime, k.block,
t.status, t.start_time, t.xidusn seg_num, r.name seg_name
FROM v$rollname r, v$lock k, v$session s, v$locked_object l, dba_objects o,
v$transaction t
WHERE l.object_id = o.object_id
AND s.sid = l.session_id
AND s.sid = k.sid
AND t.xidusn = r.usn
AND l.xidusn = t.xidusn
/* 缓冲区命中率 */
/* The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools
within one set of statistics */
/* 50%以下(危险,立即增加缓冲区缓存) 95%以上分配过度 90%-95%为调整的目标 */
SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100,
3) || '%' "Buffer Cache Hit Ratio"
FROM v$sysstat phy, v$sysstat cur, v$sysstat con
WHERE phy.name = 'physical reads'
AND cur.name = 'db block gets'
AND con.name = 'consistent gets'
/* 每个Buffer Pool的命中率 */
select name "Buffer Pool",1 - (physical_reads / (db_block_gets + consistent_gets)) "Buffer Pool Hit Ratio"
from v$buffer_pool_statistics order by name;
/* 查询某个会话的缓冲区命中率(查询的响应时间比较长) */
SELECT s.sid, s.username,
ROUND((1 - phy_read.value / (cons_get.value + block_get.value)) * 100, 3) || '%'
"BUFFER_HIT_RATIO"
FROM v$session s,
v$sesstat cons_get, v$statname cons,
v$sesstat block_get, v$statname block,
v$sesstat phy_read, v$statname phy
WHERE s.sid = cons_get.sid
AND cons_get.statisti
c# = cons.statistic#
AND cons.name = 'consistent gets'
AND s.sid = block_get.sid
AND block_get.statistic# = block.statistic#
AND block.name = 'db block gets'
AND s.sid = phy_read.sid
AND phy_read.statistic# = phy.statistic#
AND phy.name = 'physical reads'
AND cons_get.value + block_get.value > 0
ORDER BY 1
/* 查询确定Shared Pool Memory的利用率 */
/* 60%-80%利用是最佳的 */
SELECT (used / value) * 100 SHARED_POOL_USAGE_RATIO
FROM v$parameter p,
(SELECT SUM(bytes) used
FROM v$sgastat
WHERE pool = 'shared pool'
AND name <> 'free memory')
WHERE p.name = 'shared_pool_size'
/* 共享池空闲率 */
/* 40%-100% 分配过度, 10%-20% 分配最佳*/
SELECT (s.bytes / p.value)* 100 shared_pool_free_ratio
FROM v$parameter p, v$sgastat s
WHERE s.pool = 'shared pool'
AND s.name = 'free memory'
AND p.name = 'shared_pool_size'
/* Library Cache Reloads*/
/* 如果reload的值大于1%就得增加共享池的大小 */
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses",
SUM(reloads) / SUM(pins)
FROM v$librarycache
/* 查询排序的情况 */
SELECT *
FROM v$sysstat
WHERE name LIKE '%sorts%'
/* 查询磁盘排序和内存排序的比率(应该低于5%)*/
SELECT disk.value "Disk", mem.value "Mem",
(disk.value / mem.value) * 100 "Ratio"
FROM v$sysstat mem, v$sysstat disk
WHERE mem.name = 'sorts (memory)'
AND disk.name = 'sorts (disk)'
/* 查询正在做的事务 */
SELECT xid, status, start_time, xidusn seg_num, r.name seg_name
FROM v$transaction t, v$rollname r
WHERE t.xidusn = r.usn
/* 查询父表外键引用的子表 */
SELECT a.table_name 外键表, a.constraint_name 外键名, b.column_name 外键字段,
c.table_name 主键表, a