本文详细介绍了如何通过SQL查询来分析数据库性能，包括查询SQL_ID执行计划、过滤特定表的SQL、检查耗时超过1秒的查询以及提取绑定变量。内容涉及v$sql、v$sql_plan和v$sql_text视图的使用。
-查询最近执行的SQL
select sql_id,sql_text, cpu_time, elapsed_time, executions, first_load_time, last_load_time,last_active_time from v$sql where parsing_schema_name=‘数据库名称’ order by last_active_time desc
select sql_id,sql_text,SQL_FULLTEXT, cpu_time, elapsed_time, executions, first_load_time, last_load_time,
last_active_time from v$sql where parsing_schema_name=‘数据库名称’ —想查哪一个数据库就输入哪一个数据库
and instr(UPPER(sql_text),UPPER(‘表名’))>0 order by last_active_time desc
–查询SQL_ID执行计划
select lpad(mark,length(mark)+level2),id, position,nvl(parent_id,0), optimizer, to_char(cost), to_char(bytes),to_char(cardinality) from
(
select operation || ’ (’ || options || ') ’ || object_name as mark, a. from v
s
q
l
p
l
a
n
a
w
h
e
r
e
(
s
q
l
i
d
,
c
h
i
l
d
n
u
m
b
e
r
)
i
n
(
s
e
l
e
c
t
m
a
x
(
s
q
l
i
d
)
,
m
a
x
(
c
h
i
l
d
n
u
m
b
e
r
)
f
r
o
m
v
sql_plan a where (sql_id, child_number) in ( select max(sql_id), max(child_number) from v
sqlplanawhere(sqlid,childnumber)in(selectmax(sqlid),max(childnumber)fromvsql_plan
where sql_id=‘xxxxxxx’
)
) start with id=0 connect by prior id = parent_id order by id,position
–获取完整的SQL语句
select LISTAGG(sql_text,‘’) WITHIN GROUP(order by sql_id,piece ) from v
s
q
l
t
e
x
t
w
i
t
h
n
e
w
l
i
n
e
s
a
w
h
e
r
e
s
q
l
i
d
=
′
x
x
x
x
x
x
x
′
−
−
查询绑定变量
s
e
l
e
c
t
∗
f
r
o
m
v
sqltext_with_newlines a where sql_id='xxxxxxx' --查询绑定变量 select * from v
sqltextwithnewlinesawheresqlid=′xxxxxxx′−−查询绑定变量select∗fromvsql_bind_capture where sql_id=‘xxxxxxx’ order by position
–查耗时超过1s的sql
select sql_id,sql_text, cpu_time/executions/1000000, elapsed_time/executions/1000000, executions, first_load_time, last_load_time from v$sql
where parsing_schema_name=‘数据库名称’ and executions<>0 and cpu_time/executions > 1000000 order by last_active_time desc
扫一扫
279
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
