Oracle 性能优化之高消耗的SQL

高资源消耗的 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 语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 0
d0knq1hqbsunv 0
5khbzgpncr2an 0
0v9u9pcwk93xq 0
65p9vk5a6khar 0
a1vukjd565f1p 0
9ur5wdahzm0r8 0
7ht68a765a5wk 0
g1ppkt6aam9q8 0
5zruc4v6y32f9 0
10 rows selected

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 503659
6mcpb06rctk0x 0 357586
9t76mbbnfd6qx 0 313762
2zsg19udkb16g 0 275046
gbmz0s1k7c0hd 1 221525
5zruc4v6y32f9 0 167840
3mh20bujr6cd0 0 147844
b1wtt4gzadtqs 0 98749
0f0unwp9ypug8 0 98747
05s9358mm6vrr 0 87929
10 rows selected

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

本文地址:http://blog.sunansheng.com/2016/04/22/oracle-vsql/