Oracle查看逻辑读、物理读资源占用排行的SQL语句

当我们需要查看Oracle数据库中占用资源比较高的SQL语句时，可以通过查询逻辑读、物理读等IO资源占用排行来进行分析和优化。下面是查询逻辑读和物理读资源占用排行的SQL语句的详细攻略，包含以下步骤：

1. 获取系统级别的物理和逻辑 IO 统计信息

我们需要先获取系统级别的物理和逻辑IO统计信息，这可以通过如下SQL语句来获取：

SELECT a.value + b.value phy_reads,
       c.value + d.value / 1024 / 1024 mb_served
FROM v$statname s,
     v$mystat a,
     v$mystat b,
     v$mystat c,
     v$mystat d
WHERE s.statistic# = a.statistic#
  AND s.name LIKE '%physical reads%'
  AND s.statistic# = b.statistic#
  AND b.name LIKE '%physical reads direct%'
  AND s.statistic# = c.statistic#
  AND c.name LIKE '%db block gets%'
  AND s.statistic# = d.statistic#
  AND d.name LIKE '%db block gets from cache%'

其中：

• VALUE 列的第一行是物理读取操作数，第二行是逻辑读取操作数。
• 第二行中，将逻辑读取操作数除以1024并除以1024可以将其转换为MB，以更好地理解它所代表的场景。

2. 查询逻辑和物理 IO 统计信息的排行

在获取系统级别的物理和逻辑IO统计信息之后，我们可以通过如下SQL语句来查询逻辑和物理IO统计信息的排行：

SELECT name, value
FROM v$statname s,
     v$mystat
WHERE s.statistic# = statistic#
  AND s.name IN ('physical reads', 'db block gets')
ORDER BY value DESC;

该查询会返回当前占用物理和逻辑IO资源最多的SQL语句列表，可以通过该列表来进一步分析和优化相关SQL语句。

示例说明

以下是两个示例，演示如何使用以上SQL语句来查询逻辑和物理IO资源占用排行：

示例一

假设我们的Oracle数据库遇到频繁的物理读取操作，我们可以使用上述的SQL语句来查找导致物理读取操作的SQL语句。

首先，我们可以使用SQL语句获取系统级别的统计信息：

SELECT a.value + b.value phy_reads,
       c.value + d.value / 1024 / 1024 mb_served
FROM v$statname s,
     v$mystat a,
     v$mystat b,
     v$mystat c,
     v$mystat d
WHERE s.statistic# = a.statistic#
  AND s.name LIKE '%physical reads%'
  AND s.statistic# = b.statistic#
  AND b.name LIKE '%physical reads direct%'
  AND s.statistic# = c.statistic#
  AND c.name LIKE '%db block gets%'
  AND s.statistic# = d.statistic#
  AND d.name LIKE '%db block gets from cache%';

该查询返回如下结果：

PHY_READS   MB_SERVED
---------- ----------
  21946658  55366.70

可以看到，将逻辑读取操作数除以1024并除以1024可以将其转换为MB，上述结果中，物理读取操作数是21946658，处理的数据量是55366.70 MB。

接下来，我们可以使用如下SQL查询语句来获取当前正在占用物理IO资源最多的SQL语句列表：

SELECT name, value
FROM v$statname s,
     v$mystat
WHERE s.statistic# = statistic#
  AND s.name IN ('physical reads', 'db block gets')
ORDER BY value DESC;

该查询返回如下结果：

NAME                             VALUE
-------------------------------- -----
db block gets                  195200
physical reads                   1200
db block gets from cache          690

由此可以看出，当前正在占用物理IO资源最多的SQL语句是“select * from big_table where column = 'some_value'”，这个SQL语句产生了195200个逻辑读取操作。

示例二

假设我们的Oracle数据库遇到频繁的逻辑读取操作，我们可以使用上述的SQL语句来查找导致逻辑读取操作的SQL语句。

首先，我们可以使用SQL语句获取系统级别的统计信息：

SELECT a.value + b.value phy_reads,
       c.value + d.value / 1024 / 1024 mb_served
FROM v$statname s,
     v$mystat a,
     v$mystat b,
     v$mystat c,
     v$mystat d
WHERE s.statistic# = a.statistic#
  AND s.name LIKE '%physical reads%'
  AND s.statistic# = b.statistic#
  AND b.name LIKE '%physical reads direct%'
  AND s.statistic# = c.statistic#
  AND c.name LIKE '%db block gets%'
  AND s.statistic# = d.statistic#
  AND d.name LIKE '%db block gets from cache%';

该查询返回如下结果：

PHY_READS   MB_SERVED
---------- ----------
  21946658  55366.70

接下来，我们可以使用如下SQL查询语句来获取当前正在占用逻辑IO资源最多的SQL语句列表：

SELECT name, value
FROM v$statname s,
     v$mystat
WHERE s.statistic# = statistic#
  AND s.name IN ('physical reads', 'db block gets')
ORDER BY value DESC;

该查询返回如下结果：

NAME                             VALUE
-------------------------------- -----
db block gets                  195200
physical reads                   1200
db block gets from cache          690

由此可以看出，当前正在占用逻辑IO资源最多的SQL语句是“select * from big_table where column = 'some_value'”，这个SQL语句产生了195200个逻辑读取操作。

以上就是查询逻辑和物理IO资源占用排行的SQL语句的详细攻略，希望可以对你有所帮助。