PLSQL_低效SQL的识别和查询汇总（案例）

1. 查找排序最多的SQL

---

  SELECT   HASH_VALUE,
           SQL_TEXT,
           SORTS,
           EXECUTIONS
    FROM   V$SQLAREA
ORDER BY   SORTS DESC;

2.查找磁盘读写最多的SQL

---

SELECT   *
  FROM   (  SELECT   sql_text,
                     disk_reads "total disk",
                     executions "total exec",
                     disk_reads / executions "disk/exec"
              FROM   v$sql
             WHERE   executions > 0 AND is_obsolete = 'N'
          ORDER BY   4 DESC)
 WHERE   ROWNUM < 11;

 

3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的)

---

  SELECT   SUBSTR (TO_CHAR (s.pct, '99.00'), 2) || '%' load,
           s.executions executes,
           p.sql_text
    FROM   (SELECT   address,
                     disk_reads,
                     executions,
                     pct,
                     RANK () OVER (ORDER BY disk_reads DESC) ranking
              FROM   (SELECT   address,
                               disk_reads,
                               executions,
                               100 * ratio_to_report (disk_reads) OVER () pct
                        FROM   sys.v_$sql
                       WHERE   command_type != 47)
             WHERE   disk_reads > 50 * executions) s, sys.v_$sqltext p
   WHERE   s.ranking <= 5 AND p.address = s.address
ORDER BY   1, s.address, p.piece;

 

4. 用下列SQL工具找出低效SQL

---

  SELECT   executions,
           disk_reads,
           buffer_gets,
           ROUND ( (buffer_gets - disk_reads) / buffer_gets, 2) Hit_radio,
           ROUND (disk_reads / executions, 2) reads_per_run,
           sql_text
    FROM   v$sqlarea
   WHERE       executions > 0
           AND buffer_gets > 0
           AND (buffer_gets - disk_reads) / buffer_gets < 0.8
ORDER BY   4 DESC;