用SQL生成awr报表中的“SQL ordered by Elapsed Time” 部分

以下SQL用于生成awr的以下部分。




--提取&beg_snap 、&end_snap
select * from dba_hist_snapshot x ;
--提取&dbid 
select * from v$database;
--提取$inst_num
select * from v$instance;


select *
  from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",
              round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",
               sqt.exec,
               round(decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",
               round((100 *
               (sqt.elap / (select sum(e.value) - sum(b.value)
                               from dba_hist_sys_time_model b,
                                    dba_hist_sys_time_model e
                              where b.snap_id = &beg_snap and
                                    e.snap_id = &end_snap and
                                    b.dbid = &dbid and
                                    e.dbid = &dbid and
                                    b.instance_number = &inst_num and
                                    e.instance_number = &inst_num and
                                    e.stat_name = 'DB time' and
                                    b.stat_name = 'DB time'))) ,2)norm_val,
               sqt.sql_id,
               decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,
               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = &dbid and
                       instance_number = &inst_num and
                       &beg_snap < snap_id and
                       snap_id <= &end_snap
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id and
               st.dbid(+) = &dbid
         order by nvl(sqt.elap, -1) desc,
                  sqt.sql_id)
 where rownum < 65 and
       (rownum <= 10 or norm_val > 1);

从dba_hist_sqlstat提取更多的指标,调整加粗部分,照葫芦画瓢就可以写出
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory

<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'); > ``` ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值