Oracle 数据库排查SQL 耗时

最新推荐文章于 2025-01-17 02:52:11 发布

Aries-s

最新推荐文章于 2025-01-17 02:52:11 发布

阅读量876

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/theVirtual/article/details/115250066

SQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中通过SQL查询来排查和监控性能问题，包括查看正在执行的SQL、消耗时间最多的SQL、CPU消耗时间最多的SQL以及磁盘读取最多的SQL。此外，还提供了查询执行最慢和执行次数最多的SQL的方法，帮助进行数据库性能优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 数据库排查SQL

查看oracle数据库中正在执行的sql语句

select a.program,b.spid,c.sql_text from v $s e s s i o n a, v$ process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;

看总消耗时间最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v,
child_number,
v.sql_text,
last_load_time,
v.PARSING_USER_ID,
ROUND(
v.ELAPSED_TIME / 1000000 / ( CASE WHEN ( EXECUTIONS = 0 OR NVL ( EXECUTIONS, 1 ) = 1 ) THEN 1 ELSE EXECUTIONS END ),
2
) “执行时间’S’”,
v.SQL_FULLTEXT,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.elapsed_time DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 100
AND last_load_time > to_char( sysdate - 1 / 24, ‘YYYY-MM-DD/HH:MI:SS’ )
ORDER BY
“执行时间’S’” DESC

查看CPU消耗时间最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.cpu_time DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 10;

查看消耗磁盘读取最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.disk_reads DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 10;

查询执行最慢的sql

SELECT
*
FROM
(
SELECT
sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS “执行次数”,
round( sa.ELAPSED_TIME / 1000000, 2 ) “总执行时间”,
round( sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2 ) “平均执行时间”,
sa.COMMAND_TYPE,
sa.PARSING_USER_ID “用户ID”,
u.username “用户名”,
sa.HASH_VALUE
FROM
v$sqlarea sa leftjoinall_users u ON sa.PARSING_USER_ID = u.user_id
WHERE
sa.EXECUTIONS > 0
ORDER BY
( sa.ELAPSED_TIME / sa.EXECUTIONS ) DESC
)
WHERE
rownum <= 50

查询次数最多的 sql

SELECT
*
FROM
(
SELECT
s.SQL_TEXT,
s.EXECUTIONS “执行次数”,
s.PARSING_USER_ID “用户名”,
RANK ( ) OVER ( ORDER BY EXECUTIONS DESC ) EXEC_RANK
FROM
v$ SQL s
LEFT JOIN all_users u ON u.USER_ID = s.PARSING_USER_ID
) T
WHERE
exec_rank <= 100;