1、查看目前是否有锁：
select sn.username,
m.sid,
sn.serial#,
m.type,
decode (m.lmode,
0,
‘none’,
1,
‘null’,
2,
‘rowshare’,
3,
‘rowexcl.’,
4,
‘share’,
5,
‘srowexcl.’,
6,
‘exclusive’,
lmode,
ltrim (to_char (lmode, ‘990’)))
lmode,
decode (m.request,
0,
‘none’,
1,
‘null’,
2,
‘rowshare’,
3,
‘rowexcl.’,
4,
‘share’,
5,
‘srowexcl.’,
6,
‘exclusive’,
request,
ltrim (to_char (m.request, ‘990’)))
request,
m.id1,
m.id2
from v
s
e
s
s
i
o
n
s
n
,
v
session sn, v
sessionsn,vlock m
where (sn.sid = m.sid and m.request != 0) --存在锁请求，即被阻塞
or (sn.sid = m.sid --不存在锁请求，但是锁定的对象被其他会话请求锁定
and m.request = 0 and lmode != 4
and (id1, id2) in
(select s.id1, s.id2
from v$lock s
where request != 0
and s.id1 = m.id1
and s.id2 = m.id2))
order by id1, id2, m.request;
2、查看各用户的各种资源占用，可以运行下面的SQL
select se.SID, ses.username, ses.osuser, n.NAME, se.VALUE
from v
s
t
a
t
n
a
m
e
n
,
v
statname n, v
statnamen,vsesstat se, v$session ses
where n.statistic# = se.statistic# and
se.sid = ses.sid and
ses.username is not null and
n.name in (‘CPU used by this session’,
‘db block gets’,
‘consistent gets’,
‘physical reads’,
‘free buffer requested’,
‘table scans (long tables)’,
‘table scan rows gotten’,
‘sorts (memory)’,
‘sorts (disk)’,
‘sorts (rows)’,
‘session uga memory max’ ,
‘session pga memory max’)
order by sid, n.statistic#;
3，要想看占用资源的SQL top10之类的数据
从V
S
Q
L
A
R
E
A
中
查
询
最
占
用
资
源
的
查
询
s
e
l
e
c
t
b
.
u
s
e
r
n
a
m
e
u
s
e
r
n
a
m
e
,
a
.
d
i
s
k
r
e
a
d
s
r
e
a
d
s
,
a
.
e
x
e
c
u
t
i
o
n
s
e
x
e
c
,
a
.
d
i
s
k
r
e
a
d
s
/
d
e
c
o
d
e
(
a
.
e
x
e
c
u
t
i
o
n
s
,
0
,
1
,
a
.
e
x
e
c
u
t
i
o
n
s
)
r
d
s
e
x
e
c
r
a
t
i
o
,
a
.
s
q
l
t
e
x
t
S
t
a
t
e
m
e
n
t
f
r
o
m
v
SQLAREA中查询最占用资源的查询 select b.username username,a.disk_reads reads, a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio, a.sql_text Statement from v
SQLAREA中查询最占用资源的查询selectb.usernameusername,a.diskreadsreads,a.executionsexec,a.diskreads/decode(a.executions,0,1,a.executions)rdsexecratio,a.sqltextStatementfromvsqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。
4、V$SQL是内存共享SQL区域中已经解析的SQL语句。
列出使用频率最高的5个查询：
select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v
s
q
l
)
w
h
e
r
e
e
x
e
c
r
a
n
k
<
=
5
;
消
耗
磁
盘
读
取
最
多
的
s
q
l
t
o
p
5
：
s
e
l
e
c
t
d
i
s
k
r
e
a
d
s
,
s
q
l
t
e
x
t
f
r
o
m
(
s
e
l
e
c
t
s
q
l
t
e
x
t
,
d
i
s
k
r
e
a
d
s
,
d
e
n
s
e
r
a
n
k
(
)
o
v
e
r
(
o
r
d
e
r
b
y
d
i
s
k
r
e
a
d
s
d
e
s
c
)
d
i
s
k
r
e
a
d
s
r
a
n
k
f
r
o
m
v
sql) where exec_rank <=5; 消耗磁盘读取最多的sql top5： select disk_reads,sql_text from (select sql_text,disk_reads, dense_rank() over (order by disk_reads desc) disk_reads_rank from v
sql)whereexecrank<=5;消耗磁盘读取最多的sqltop5：selectdiskreads,sqltextfrom(selectsqltext,diskreads,denserank()over(orderbydiskreadsdesc)diskreadsrankfromvsql)
where disk_reads_rank <=5;
5、找出需要大量缓冲读取（逻辑读）操作的查询：
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
dense_rank() over
(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank<=5;
1890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
