EBS_DBA_技能:常用SQL
4.1 检查数据库的等待事件
--检查数据库的等待事件 SELECT sid, event, p1, p2, p3, wait_time, seconds_in_wait FROM v$session_wait WHERE event NOT LIKE 'SQL%' AND event NOT LIKE 'rdbms%'; |
如果数据库长时间持续出现大量像latchfree,enqueue,bufferbusywaits,dbfilesequential read,db file scattered read等等待事件时,需要对其进行分析,可能存在问题的语句。
4.2 查找前十条性能差的sql
--查找前十条性能差的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; |
4.3 运行很久的SQL
--查找前十条性能差的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; |
4.4 监控数据库某用户在运行什么SQL
--监控数据库某用户在运行什么SQL SELECT sql_text FROM v$sqltext t, v$session s WHERE t.address = s.sql_address AND t.hash_value = s.sql_hash_value AND s.machine = 'XXXXX' OR username = 'XXXXX' -- 查看某主机名,或用户名 |