与用户执行 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

查询最消耗 CPU 时间的前 10 个 SQL 语句

SQL> SELECT * FROM (SELECT sql_id,child_number FROM v$sql ORDER BY cpu_time DESC) WHERE rownum <= 10;

SQL_ID        CHILD_NUMBER
------------- ------------
6gvch1xu9ca3g            0
aykvshm7zsabd            0
3am9cfkvx7gq1            0
bunssq950snhf            0
f6cz4n8y72xdc            0
66gs90fyynks7            0
cm5vu20fhtnq1            0
4dy1xm4nxc0gf            0
84k66tf2s7y1c            0
7xa8wfych4mad            0

10 rows selected.

查询消耗磁盘读次数最多的 10 个 SQL 语句

SQL> SELECT * FROM (SELECT sql_id,child_number,round(disk_reads / executions) dsk_rds FROM v$sql WHERE executions > 0 ORDER BY dsk_rds DESC) WHERE rownum <= 10;

SQL_ID        CHILD_NUMBER    DSK_RDS
------------- ------------ ----------
ajymgnp1qnruw            0       2932
f6cz4n8y72xdc            0       2653
12a2xbmwn5v6z            0       1766
2crngsbggkkzv            0        866
9pvbjqqt222b4            0        757
c2p32r5mzv8hb            0        735
g0jvz8csyrtcf            0        669
02577v815yp77            0        188
8mz0wa11tacp0            0        155
6v0cyda83r2qf            0        132

10 rows selected.

ORACLE 提供了一个视图 v$session_longops,记录了所有超过 6 秒的 会话操作,如执行备份与恢复操作、统计信息的收集等,包括执行超过 6 秒的 SQL 语句(需要设置 timed_statistics 为 true 并需要统计信息的支持)。
通过该视图 获得响应时间较长的 SQL 语句信息,通过字段 sql_address 和 sql_hash_value 与视 图 v$sql(或 v$sqltxt)联接可以获得完整的 SQL 语句的文本。

带符号 * 的表示必填项