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