高资源消耗的 SQL
与用户执行 SQL 有关的动态视图有 v$sql、v$sqlarea、v$sqltext、v$sql_plan、 v$sqlstats 等。v$sql 中包含了所有用户执行的所有 SQL 信息，不同用户、不同会 话执行相同 SQL 的语义、执行计划可能会不同，这些 SQL 字面值相同（具有相同 的 sql_id），通过不同的 child_number 来区分。v$sqlarea 中仅包含 SQL 语句的字面 信息，忽略了相同 SQL 语句在执行会话、语义、执行计划上的不同，相同的 SQL 语句在 v$sqlara 中仅以一行显示。v$sqltext 以多个 piece 的形式给出了 SQL 语句 的完整文本，它通过字段 address 和 hash_value 的联合可以唯一地标识一条 SQL 语句。v$sql_plan 包含了 SQL 语句的执行计划信息，它通过 address、hash_value、 child_number 三个字段（可以和 v$sql 关联）唯一地标识 SQL 语句的执行计划。 v$sqlstats 包含 SQL 语句的性能统计信息，通过 sql_id 和 plan_hash_value 可以唯一 地标识一条 SQL 语句的性能统计数据。v$sqlstats 视图的字段是 v$sql 和 v$sqlarea 字段的子集，即视图中的内容来自于它们，但 v$sqlstats 视图中的数据比 v$sql 和 v$sqlarea 保存更持久。
当我们需要根据各种资源消耗查找 top SQL 时，可以根据不同的性能统计字 段查询 v$sql，该视图不仅包含用户提交给数据库的 SQL 语句及其文本，而且还包含这些语句的性能统计数据。表 13.1 给出了资源消耗与 v$sql 视图相关字段的 说明。
| 资 源 消 耗 | 字段及其说明 |
|---|---|
| 执行代价 cost | optimizer_cost 优化器给出的执行代价 |
| CPU 消耗 | cpu_time、elapsed_time 每次执行的 CPU 耗时= cpu_time/executions 每次执行消耗的时间= elapsed_time/executions |
| I/O 消耗 | disk_reads 每次执行的磁盘读次数= disk_reads/executions |
| 内存消耗 | buffer_gets、sharable_mem（消耗 shared pool） 每次执行的缓存消耗= buffer_gets/executions |
| 语句解析消耗 | parse_calls 该语句（硬）解析的次数 |
| 排序消耗 | sorts |
| 检索记录数 | rows_processed、fetches |
top SQL 查询示例
（1）查询最消耗 CPU 时间的前 10 个 SQL 语句。
12 |
SQL> SELECT * 2 FROM (SELECT sql_id, 3 child_number 4 FROM v$sql 5 ORDER BY cpu_time DESC) 6 WHERE rownum <= 10;SQL_ID CHILD_NUMBER------------- ------------f5mkwycvxw09f 0d0knq1hqbsunv 05khbzgpncr2an 00v9u9pcwk93xq 065p9vk5a6khar 0a1vukjd565f1p 09ur5wdahzm0r8 07ht68a765a5wk 0g1ppkt6aam9q8 05zruc4v6y32f9 010 rows selected |
（2）查询消耗磁盘读次数最多的 10 个 SQL 语句。
123456789101112131415161718192021 |
SQL> SELECT * 2 FROM (SELECT sql_id, 3 child_number, 4 round(disk_reads / executions) dsk_rds 5 FROM v$sql 6 WHERE executions > 0 7 ORDER BY dsk_rds DESC) 8 WHERE rownum <= 10;SQL_ID CHILD_NUMBER DSK_RDS------------- ------------ ----------3trrg5vyyp34v 0 5036596mcpb06rctk0x 0 3575869t76mbbnfd6qx 0 3137622zsg19udkb16g 0 275046gbmz0s1k7c0hd 1 2215255zruc4v6y32f9 0 1678403mh20bujr6cd0 0 147844b1wtt4gzadtqs 0 987490f0unwp9ypug8 0 9874705s9358mm6vrr 0 8792910 rows selected |
另外，ORACLE 提供了一个视图 v$session_longops，记录了所有超过 6 秒的 会话操作，如执行备份与恢复操作、统计信息的收集等，包括执行超过 6 秒的 SQL 语句（需要设置 timed_statistics 为 true 并需要统计信息的支持）。可以通过该视图 获得响应时间较长的 SQL 语句信息，通过字段 sql_address 和 sql_hash_value 与视 图 v$sql（或 v$sqltxt）联接可以获得完整的 SQL 语句的文本。