Oracle 第四天 查询TOP100

熟悉TOP100收集的基本步骤

--查前100个cpu占用率高的sql语句
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
disk_reads,
s.BUFFER_GETS,
s.CPU_TIME,
s.ELAPSED_TIME,
sql_text,
command_type,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY CPU_TIME DESC)
WHERE rownum < 100 ;


--查连接数
select count(*) from v$session;
select * from v$session;
#查oracle配置
select * from v$resource_limit;
#获取执行次数前100位的SQL
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
command_type,
disk_reads,
sql_text,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY executions DESC)
WHERE rownum < 100 ;


--获取sort前100位的SQL
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
command_type,
disk_reads,
sql_text,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY sorts DESC)
WHERE rownum < 100;


--获取磁盘I/O前100位的SQL
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
command_type,
disk_reads,
sql_text,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY disk_reads DESC)
WHERE rownum < 100;


--获取逻辑读前100位的SQL
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
command_type,
s.BUFFER_GETS,
sql_text,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY BUFFER_GETS DESC)
WHERE rownum < 100;


--获取CPU消耗前100位的SQL
SELECT *
FROM (SELECT parsing_user_id,
executions,
sorts,
disk_reads,
s.BUFFER_GETS,
s.CPU_TIME,
s.ELAPSED_TIME,
sql_text,
command_type,
S.HASH_VALUE
FROM v$sqlarea s
WHERE S.PARSING_USER_ID=uid
ORDER BY CPU_TIME DESC)
WHERE rownum < 100 ;