oracle高资源消耗sql,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 语句的文本。