select * from V$process;
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by address, piece;
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
--检测数据库中的事件和等待
SELECT event, total_waits, total_timeouts,time_waited, average_wait FROM v$system_event;
--查询会话中的事件和等待时间
select sid, event, total_waits,average_wait from v$session_event;
--查询等待进程
SELECT sid, seq#, event, wait_time, state FROM v$session_wait;
--当前 sql 语句
select sql_text, users_executing, executions, loads from v$sqlarea;
--查看表锁
select * from sys.v_$sqlarea where disk_reads>100
--查看前台正在发出的SQL语句
select user_name, sql_text
from v$open_cursor
where sid in (select sid
from (select sid, serial#, username, program
from v$session
where status = 'ACTIVE'))
--数据表占用空间大小情况
select segment_name, tablespace_name, bytes, blocks
from user_segments
where segment_type = 'TABLE'
ORDER BY bytes DESC, blocks DESC
--查看表空间碎片大小
select tablespace_name,
round(sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))),
2) FSFI
from dba_free_space
group by tablespace_name
order by 1
--查看session使用回滚段
SELECT r.name 回滚段名,
s.sid,
s.serial#,
s.username 用户名,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程序
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io
--查看SGA区剩余可用内存
select name,
sgasize/1024/1024 "Allocated(M)",
bytes/1024 "**空间(K)",
round(bytes/sgasize*100, 2) "**空间百分比(%)"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory'
--性能最差的SQL
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM<100
--读磁盘数超100次的sql
select * from sys.v_$sqlarea where disk_reads>100
--最频繁执行的sql
select * from sys.v_$sqlarea where executions>100
--查询使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc
--当前每个会话使用的对象数
SELECT a.sid,s.terminal,s.program,count(a.sid)
FROM V$ACCESS a,V$SESSION s
WHERE a.owner <> 'SYS'AND s.sid = a.sid
GROUP BY a.sid,s.terminal,s.program
ORDER BY count(a.sid)
select sid,v$session.username 用户名,last_call_et 持续时间,status 状态,LOCKWAIT 等待锁,machine 用户电脑名,logon_time 开始登入时间,sql_text from v$session ,v$process ,v$sqlarea
where paddr=addr and sql_hash_value=hash_value
and status='ACTIVE' and v$session.username is not null
order by last_call_et desc;
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
SELECT LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
select name, waits, gets, waits / gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
--查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--查询日志状态
SELECT created, log_mode, log_mode FROM v$database;
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--查看表空间使用情况
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Tot"
from v$session_Wait
group by event
order by 4;
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from x$bh a, dba_objects b
where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = &2
union
select hladdr, file#, dbablk, tch, obj, null
from x$bh
where obj in
(select obj from x$bh
where hladdr = &2
minus
select object_id from dba_objects
minus
select data_object_id from dba_objects)
and hladdr = &2
order by 4;