在Oracle数据库管理中，识别高消耗语句是性能优化的关键步骤。这些语句通常是导致系统资源瓶颈的主要原因，因此了解如何查找并分析它们至关重要。本文将详细介绍一种查找Oracle高消耗语句的方法。
我们需要生成AWR(Automatic Workload Repository)报告。AWR是Oracle数据库自带的一种性能监控工具，它会定期收集实例和数据库的性能数据，包括CPU使用率、I/O操作、缓冲区获取等。生成AWR报告的SQL脚本通常位于$ORACLE_HOME下的rdbms/admin目录中，名为awrrpt.sql。通过运行这个脚本，我们可以获取特定时间范围内的性能统计数据，从而确定开始和结束的snapshot编号：begin_snap和end_snap。
接下来，我们将使用以下SQL查询来找出高消耗语句：
```sql
set line 1000
set linesize 200
set pagesize 2000
set long 999999
set echo on
set markup html on
select res.*
from (
select to_char(d.end_interval_time,'yyyy-mm-dd'),
a.PARSING_SCHEMA_NAME,
c.MODULE,
a.sql_id,
a.execs as 执行次数,
ROUND(a.cpu_times / a.execs, 2) as 单次执行时间,
a.cpu_times as cpu消耗时间,
ROUND(a.cpu_times / b.sum_time * 100, 2) as 消耗cpu百分比,
a.buffer_gets as 逻辑读,
ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as 逻辑读百分比,
a.disk_read as 物理读,
ROUND(a.disk_read / b.sum_disk * 100, 2) as 物理读百分比,
c.sql_fulltext
from (
select PARSING_SCHEMA_NAME,
sql_id,
sum(EXECUTIONS_DELTA) AS execs,
round(sum(CPU_TIME_DELTA) / 1000000, 2) AS cpu_times,
round(sum(ELAPSED_TIME_DELTA) / 1000000, 2) AS elapsed_time,
sum(BUFFER_GETS_DELTA) AS buffer_gets,
sum(DISK_READS_DELTA) AS disk_read
from sys.WRH$_SQLSTAT wr, gv$instance i
where SNAP_ID <= &end_snap
and snap_id >= &begin_snap
and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER
and i.instance_number = &instance_number
group by PARSING_SCHEMA_NAME,
wr.INSTANCE_NUMBER,
sql_id
) a,
(SELECT round(SUM(CPU_TIME_DELTA) / 1000000, 2) sum_time,
SUM(BUFFER_GETS_DELTA) sum_buffer,
sum(DISK_READS_DELTA) sum_disk
FROM sys.WRH$_SQLSTAT wr, gv$instance i
where SNAP_ID <= &end_snap
and snap_id >= &begin_snap
and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER
and i.instance_number = &instance_number
) b,
v$sqlarea c,
dba_hist_snapshot d
where a.execs > 0
and a.sql_id = c.sql_id
and a.PARSING_SCHEMA_NAME <> 'SYS'
and d.snap_id = &end_snap
order by cpu消耗时间 desc
) res
where rownum < 41;
exit
```
此查询涵盖了以下几个关键指标：
1. `execs`：执行次数，表示语句在指定时间范围内被执行的频率。
2. `单次执行时间`：平均每次执行该语句消耗的CPU时间。
3. `cpu消耗时间`：总CPU时间，反映了语句对CPU资源的占用程度。
4. `消耗cpu百分比`：相对于所有SQL的总CPU时间，该语句所占的百分比。
5. `逻辑读`和`逻辑读百分比`：逻辑读取次数，表示从缓冲区缓存中获取数据块的次数及其占比。
6. `物理读`和`物理读百分比`：物理读取次数，即从磁盘读取数据块的次数及其占比。
查询结果将按照CPU消耗时间降序排列，显示前40个高消耗语句。通过分析这些信息，我们可以定位到最消耗资源的SQL语句，进一步进行性能调优。
为了便于查看和分享，可以将查询结果保存为HTML格式。将SQL查询的输出保存到文本文件中，然后将文件扩展名更改为.html，这将创建一个网页，其中包含格式化的性能数据。
通过这种方法，DBA（数据库管理员）能够快速识别出那些可能对系统性能造成负面影响的SQL语句，从而采取适当的措施进行优化，如重构查询、调整索引、优化表结构或配置数据库参数。这样，可以有效地提高Oracle数据库的整体性能和效率。
