Oracle 数据库排查SQL

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

select a.program,b.spid,c.sql_text from v$sessiona,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;