sql turning

最新推荐文章于 2024-11-04 16:18:41 发布

转载最新推荐文章于 2024-11-04 16:18:41 发布 · 244 阅读

一些收集SQL的语句：）

[@more@]

1．查找前十条性能差的sql

SELECT *
FROM (SELECT parsing_user_id executions,
sorts,
command_type,
disk_reads,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum < 10;

2．捕捉运行很久的SQL

SELECT username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || '%' AS progress,
time_remaining,
sql_text
FROM v$session_longops, v$sql
WHERE time_remaining <> 0 AND sql_address = address AND
sql_hash_value = hash_value;

3．耗资源的进程(top session)

SELECT s.schemaname schema_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' || to_char(command)) action,
status session_status,
s.osuser os_user_name,
s.sid,
p.spid,
s.serial# serial_num,
nvl(s.username, '[Oracle process]') user_name,
s.terminal terminal,
s.program program,
st.VALUE criteria_value
FROM v$sesstat st, v$session s, v$process p
WHERE st.sid = s.sid AND st.statistic# = to_number('38') AND
('ALL' = 'ALL' OR s.status = 'ALL') AND p.addr = s.paddr
ORDER BY st.VALUE DESC, p.spid ASC, s.username ASC, s.osuser ASC;