oracle中怎么确定性能差的SQL语句

oracle中怎么确定性能差的SQL语句,当涉及 SQL 时，性能不佳有两方面：CPU 密集型语句（CPU-intensive statements）和 I/O 密集型语句（I/O-intensive statements）。

oracle中怎么确定性能差的SQL语句,当涉及 SQL 时，性能不佳有两方面：CPU 密集型语句（CPU-intensive statements）和 I/O 密集型语句（I/O-intensive statements）。

前者很容易定位。所有的操作系统都可以让我们查看 CPU 密集型任务。这些任务可以追溯到一个特定用户，一个特定应用程序模块。 CPU 密集型模块一般都是由较差的代码和/或结构造成，而不是性能差的 SQL。一旦确定模块，你必须试图使之更有效率。一个可能的解决方案是将把某些处理移除程序，让数据库处理（高明点的 SQL，存储对象，内联函数，数组处理等）。

第二个是 I/O 密集型的 SQL 语句。这些语句会导致大量的数据库 I/O（全表扫描，排序，更新等），并以很高代价运行几个小时。从 Oracle 7 开始，解决了 SQL 识别问题。通过查询数据库共享池区域，我们可以很容易确定大多数 I/O 密集型 SQL 语句。

下面 SQL 语句演示了如何确定 I/O 命中率低于 80%的 SQL 语句。这个命中率是，自从 SQL 语句第一次被解析到共享池，通过所有执行的语句反应整体 I/O。下面可能是最近几分钟或几天的结果：

代码如下

复制代码

sql> SELECT executions,    2        disk_reads,    3        buffer_gets,    4        ROUND((buffer_gets - disk_reads) / buffer_gets, 2) hit_ratio,    5        sql_text    6     FROM   v$sqlarea    7    WHERE  executions  > 0    8     AND    buffer_gets > 0    9     AND    (buffer_gets - disk_reads) / buffer_gets    10   order by 4 desc ;   EXECUTIONS DISK_READS BUFFER_GETS  HIT_RATIO SQL_TEXT ---------- ---------- ----------- ---------- -----------------------------------------------------------------------         16        180         369        .51 SELECT SKU,PREPACK_IND,CASE_ID,TRANSFER_QTY,UNIT_COST,UNIT_RETAIL,ROWID                                              FROM TSF_DETAIL WHERE transfer = :1  order by sku         16        30          63         .52 SELECT TRANSFER,TO_STORE,TO_WH FROM TSFHEAD  WHERE TRANSFER = :b1  AND                                              TRANSFER_STATUS = 'A'         2         3           7          .57 SELECT SKU   FROM UPC_EAN  WHERE UPC = :b1         12        14          35         .60 SELECT SUBSTR(DESC_UP,1,30),DEPT,SYSTEM_IND   FROM DESC_LOOK  WHERE                                              SKU = :b1         14        13          35         .63 SELECT UNIT_COST,UNIT_RETAIL,SUBCLASS FROM WIN_SKUS WHERE SKU = :b1 事实上，我们发现对特定的 SQL，上面的数据有些误导，其实语句没有问题。考虑下面 v$sqlarea 输出： Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ---------- ----------- --------- --------------------     2          6          19         0.68   SELECT A.EMP_NO, ...

该语句的命中率很低，但事实上它很有效。因为，SQL 是通过 UNIQUE 索引操作的，物理磁盘读取的数量几乎与逻辑读取一样。UNIQUE 索引显著减少了整体的物理和逻辑磁盘 I/O 数量，导致了一个令人误解的低命中率。

下面例子，命中率很好。但是真的很好吗？

代码如下	复制代码
Executions Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ---------- ----------- --------- --------------------     2         3625       178777      0.98   SELECT A.EMP_NO, ...

这个 SQL 语句看上去很有效。但是， 当我们仔细看时，事情就不是那么回事了。命中率并没有透露出，该语句存在五个表连接，并且每次执行进行了超过 3600 个物理磁盘读取。这是否太多了？是否有效？若不进一步研究，无法回答这两个问题。事实上，这个实例中，五个表的中其一个错误地执行了全表扫描。通过重新构造 SQL，我们可以减少物理磁盘 I/O 到小于 50，同时，也显著减少逻辑磁盘 I/O。巧合的是，命中率也下降到不到 70％。

我们首选 V$SQLAREA 查询是每个语句执行的物理磁盘 I/O 的真实报告。命中率是信息性的，但有时会产生误导。逻辑 I/O 相关的很少。如果语句执行 1,000,000 个逻辑 I/O，但只用了不到十分之一秒，这就没人在乎了。这是总的物理 I/O，几乎消耗了所有的时间，和确定潜在不正确的 SQL。例如：

代码如下

复制代码

sql> SELECT sql_text, executions,              ROUND(disk_reads / executions, 2) reads_per_run,              disk_reads, buffer_gets,              ROUND((buffer_gets - disk_reads)                   / buffer_gets, 2) hit_ratio,              sql_text       FROM   v$sqlarea       WHERE  executions  > 0       AND    buffer_gets > 0       AND    (buffer_gets - disk_reads) / buffer_gets       ORDER by 3 desc ;

 

前两个语句会报告更具启发性的结果：

代码如下	复制代码
Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio Sql_Text ---------- ------------- ---------- ----------- --------- ------------     2           3            6          19        0.68    SELECT ...     2         1812.5       3625       178777      0.98    SELECT ...

从视图 V$SQLAREA 中，我们可以立即隔离所有具有高物理读取的语句。这些语句可能并不一定低效或写得不好，但恰恰是它们需要进一步调查或调整。