<think>我们正在讨论如何查找Oracle数据库中最近最耗时的SQL语句。根据用户提供的引用内容，我们知道优化器在处理SQL语句时的重要性，以及编写高效SQL语句的注意事项。现在，用户希望了解如何检查最近最耗时的SQL语句。
在Oracle数据库中，我们可以通过以下几种方式来查找最近执行时间长、资源消耗大的SQL语句：
### 方法1：使用AWR报告（需要AWR许可）
AWR（Automatic Workload Repository）报告是Oracle提供的一个强大的性能诊断工具。它定期收集性能数据并生成快照。我们可以通过AWR报告来获取指定时间段内最耗时的SQL语句。
#### 步骤：
1. **确定要分析的快照时间范围**：
```sql
SELECT * FROM dba_hist_snapshot ORDER BY end_interval_time DESC;
```
2. **生成AWR报告**：
```sql
-- 使用脚本awrrpt.sql（位于$ORACLE_HOME/rdbms/admin目录）
@?/rdbms/admin/awrrpt.sql
```
在生成的报告中，有一个“SQL ordered by Elapsed Time”部分，列出了在选定快照期间内按消耗时间排序的SQL语句。
### 方法2：使用ASH报告（需要Oracle诊断包许可）
ASH（Active Session History）报告提供了最近活动的会话信息，包括当前正在执行的SQL语句。
#### 步骤：
1. **生成ASH报告**：
```sql
-- 使用脚本ashrpt.sql（位于$ORACLE_HOME/rdbms/admin目录）
@?/rdbms/admin/ashrpt.sql
```
在ASH报告中，可以查看按DB Time排序的SQL语句。
### 方法3：查询动态性能视图（不需要额外许可）
如果没有购买AWR或ASH许可，我们可以直接查询动态性能视图来获取最近执行的SQL语句信息。
#### 查询按执行时间排序的SQL（最近24小时）：
```sql
SELECT
sql_id,
sql_text,
executions,
elapsed_time / 1000000 AS elapsed_sec,
elapsed_time / 1000000 / NULLIF(executions, 0) AS avg_elapsed_sec,
disk_reads,
buffer_gets,
last_active_time
FROM
v$sqlstats
WHERE
last_active_time >= SYSDATE - 1 -- 最近24小时
AND elapsed_time / 1000000 > 5 -- 总执行时间超过5秒
ORDER BY
elapsed_time DESC;
```
#### 查询按CPU时间排序的SQL：
```sql
SELECT
sql_id,
sql_text,
executions,
cpu_time / 1000000 AS cpu_sec,
elapsed_time / 1000000 AS elapsed_sec,
disk_reads,
buffer_gets
FROM
v$sqlstats
WHERE
last_active_time >= SYSDATE - 1
ORDER BY
cpu_time DESC;
```
### 方法4：使用Oracle Enterprise Manager (OEM)
如果使用了Oracle Enterprise Manager，可以通过图形界面查看：
1. 进入“Performance”页面
2. 选择“SQL”选项卡
3. 使用“Top Activity”或“SQL Monitoring”功能查看实时和历史的高负载SQL
### 注意事项：
1. **权限要求**：查询动态性能视图需要授予`SELECT_CATALOG_ROLE`或`SELECT ANY DICTIONARY`权限。
2. **SQL完整文本**：对于长SQL，`v$sqlstats`中的`sql_text`可能被截断，可以结合`v$sqlarea`或`v$sql`的`sql_fulltext`列（CLOB类型）获取完整SQL。
3. **历史数据**：动态性能视图只保留有限时间的数据（取决于共享池大小），而AWR保留更长时间（默认7天）。
### 示例：获取完整SQL文本
```sql
SELECT
s.sql_id,
t.sql_text AS full_sql_text, -- 完整SQL文本
s.elapsed_time / 1000000 AS elapsed_sec,
s.executions
FROM
v$sqlstats s
JOIN
v$sqltext t ON s.sql_id = t.sql_id
WHERE
s.last_active_time >= SYSDATE - 1
AND s.elapsed_time / 1000000 > 10
ORDER BY
s.elapsed_time DESC;
```
### 性能优化建议
根据引用[2]和[3]，在找到耗时SQL后，应检查：
- WHERE子句是否使用了索引（避免全表扫描）
- 避免在索引列上使用函数或表达式（会导致索引失效）
- 避免隐式类型转换（如字符串与数字比较）
- 避免使用`||`连接条件（应拆分为多个条件）
例如，引用[3]中提到：条件`qc_bh || kh_bh='5400250000'`应优化为`qc_bh='5400' and kh_bh='250000'`，以利用索引[^3]。
### 自动化监控
可以创建一个定期运行的作业，将高负载SQL记录到表中：
```sql
CREATE TABLE long_running_sql AS
SELECT
sql_id,
sql_text,
elapsed_time,
cpu_time,
disk_reads,
buffer_gets,
executions,
last_active_time,
SYSDATE AS capture_time
FROM
v$sqlstats
WHERE
1=0; -- 只创建表结构
-- 定期插入（每小时）
INSERT INTO long_running_sql
SELECT
sql_id,
(SELECT sql_text FROM v$sqlarea a WHERE a.sql_id = s.sql_id) sql_text,
elapsed_time,
cpu_time,
disk_reads,
buffer_gets,
executions,
last_active_time,
SYSDATE
FROM
v$sqlstats s
WHERE
last_active_time >= SYSDATE - 1/24 -- 最近1小时
AND elapsed_time / 1000000 > 10; -- 总执行时间超过10秒
```
### §§相关问题§§
1. 如何分析SQL执行计划？
2. Oracle中如何创建索引以提高查询性能？
3. 如何解读AWR报告中的关键指标？
4. Oracle中SQL优化器的工作原理是什么？
5. 如何监控Oracle数据库的实时性能？</think>### Oracle 检查最近最耗时的 SQL 语句方法
#### 方法 1：使用 `v$sqlstats` 视图（实时监控）
```sql
SELECT
sql_id,
sql_text,
executions AS "执行次数",
elapsed_time / 1000000 AS "总耗时(秒)",
ROUND((elapsed_time / 1000000) / NULLIF(executions, 0), 4) AS "单次耗时(秒)",
disk_reads AS "磁盘读",
buffer_gets AS "缓存读",
last_active_time AS "最后执行时间"
FROM v$sqlstats
WHERE last_active_time > SYSDATE - 1/24 -- 最近1小时
AND elapsed_time / 1000000 > 5 -- 总耗时超过5秒
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
```
#### 方法 2：AWR 报告分析（历史分析）
```sql
-- 步骤1：查找AWR快照ID
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
-- 步骤2：生成指定时间段内最耗时的SQL
SELECT
sql_id,
executions_delta AS "执行次数",
elapsed_time_delta / 1000000 AS "总耗时(秒)",
ROUND((elapsed_time_delta / 1000000) / NULLIF(executions_delta, 0), 4) AS "单次耗时(秒)",
disk_reads_delta AS "磁盘读",
buffer_gets_delta AS "缓存读"
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN &begin_snap AND &end_snap -- 输入开始和结束快照ID
AND elapsed_time_delta / 1000000 > 10 -- 总耗时超过10秒
ORDER BY elapsed_time_delta DESC;
```
#### 方法 3：ASH 实时分析（活动会话历史）
```sql
SELECT
sql_id,
COUNT(*) * 10 AS "消耗时间(秒)", -- 每10秒采样一次
session_id,
module
FROM v$active_session_history
WHERE sample_time > SYSDATE - 15/1440 -- 最近15分钟
AND sql_id IS NOT NULL
GROUP BY sql_id, session_id, module
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;
```
#### 方法 4：自动生成TOP SQL报告
```sql
-- 创建监控表
CREATE TABLE top_sql_monitor AS
SELECT
sql_id,
sql_text,
executions,
elapsed_time,
disk_reads,
buffer_gets,
SYSDATE AS capture_time
FROM v$sqlstats
WHERE 1=0;
-- 定时任务（每小时运行）
BEGIN
INSERT INTO top_sql_monitor
SELECT sql_id, sql_text, executions, elapsed_time, disk_reads, buffer_gets, SYSDATE
FROM v$sqlstats
WHERE elapsed_time / 1000000 > 5;
COMMIT;
END;
/
```
#### 关键性能指标解读：
1. **执行计划分析**：
```sql
EXPLAIN PLAN FOR SELECT * FROM your_table WHERE ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```
查看执行计划中的`FULL TABLE SCAN`（全表扫描）和`COST`（成本）值[^2]
2. **索引使用检查**：
- 避免在WHERE子句中对索引列使用函数：`WHERE UPPER(name) = 'JOHN'` ❌
- 避免隐式类型转换：`WHERE char_column = 123` ❌（应改为 `WHERE char_column = '123'`）[^3]
- 避免连接条件：`qc_bh || kh_bh = '5400250000'` ❌
优化为：`qc_bh='5400' AND kh_bh='250000'` ✅[^3]
#### 优化建议：
1. **索引优化**：
```sql
-- 创建函数索引解决UPPER()问题
CREATE INDEX idx_name_upper ON employees(UPPER(last_name));
```
2. **SQL重写**：
```sql
-- 优化前（低效）
SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM') = '2023-01';
-- 优化后（高效）
SELECT * FROM orders
WHERE order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND TO_DATE('2023-01-31', 'YYYY-MM-DD');
```
3. **绑定变量使用**：
```sql
-- 避免硬解析
VARIABLE dept_id NUMBER;
EXEC :dept_id := 60;
SELECT * FROM employees WHERE department_id = :dept_id;
```
> **重要提示**：优化器可能因统计信息过期而选择低效执行计划[^1]，定期收集统计信息：
> ```sql
> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME','TABLE_NAME');
> ```
###
本文提供了一种查询Oracle数据库中耗时最长SQL的方法，并介绍了如何进一步分析这些SQL的执行计划来定位性能瓶颈。
扫一扫
4374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
