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' -- 查看某主机名，或用户名

转载于:https://www.cnblogs.com/hopedba/p/6076328.html