Oracle 数据库排查SQL 耗时

本文介绍了如何在Oracle数据库中通过SQL查询来排查和监控性能问题,包括查看正在执行的SQL、消耗时间最多的SQL、CPU消耗时间最多的SQL以及磁盘读取最多的SQL。此外,还提供了查询执行最慢和执行次数最多的SQL的方法,帮助进行数据库性能优化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Oracle 数据库排查SQL

查看oracle数据库中正在执行的sql语句

select a.program,b.spid,c.sql_text from vsessiona,vsession a,vsessiona,vprocess b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;

看总消耗时间最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v,
child_number,
v.sql_text,
last_load_time,
v.PARSING_USER_ID,
ROUND(
v.ELAPSED_TIME / 1000000 / ( CASE WHEN ( EXECUTIONS = 0 OR NVL ( EXECUTIONS, 1 ) = 1 ) THEN 1 ELSE EXECUTIONS END ),
2
) “执行时间’S’”,
v.SQL_FULLTEXT,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.elapsed_time DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 100
AND last_load_time > to_char( sysdate - 1 / 24, ‘YYYY-MM-DD/HH:MI:SS’ )
ORDER BY
“执行时间’S’” DESC

查看CPU消耗时间最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.cpu_time DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 10;

查看消耗磁盘读取最多的前10条SQL语句

SELECT
*
FROM
(
SELECT
v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
RANK ( ) OVER ( ORDER BY v.disk_reads DESC ) elapsed_rank
FROM
v$ SQL v
) A
WHERE
elapsed_rank <= 10;

查询执行最慢的sql

SELECT
*
FROM
(
SELECT
sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS “执行次数”,
round( sa.ELAPSED_TIME / 1000000, 2 ) “总执行时间”,
round( sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2 ) “平均执行时间”,
sa.COMMAND_TYPE,
sa.PARSING_USER_ID “用户ID”,
u.username “用户名”,
sa.HASH_VALUE
FROM
v$sqlarea sa leftjoinall_users u ON sa.PARSING_USER_ID = u.user_id
WHERE
sa.EXECUTIONS > 0
ORDER BY
( sa.ELAPSED_TIME / sa.EXECUTIONS ) DESC
)
WHERE
rownum <= 50

查询次数最多的 sql

SELECT
*
FROM
(
SELECT
s.SQL_TEXT,
s.EXECUTIONS “执行次数”,
s.PARSING_USER_ID “用户名”,
RANK ( ) OVER ( ORDER BY EXECUTIONS DESC ) EXEC_RANK
FROM
v$ SQL s
LEFT JOIN all_users u ON u.USER_ID = s.PARSING_USER_ID
) T
WHERE
exec_rank <= 100;

<think>好的,用户想知道如何在Oracle数据库排查和优化执行缓慢的SQL语句。首先,我需要回忆一下Oracle常见的性能调优方法。根据用户提到的工具,比如执行计划、v$session、v$sqlarea、AWR报告,这些都是关键点。 首先,应该从识别慢SQL开始。用户可能不知道具体哪些SQL慢,这时候需要用到AWR报告,因为它能提供一段时间内的性能数据,包括高负载的SQL。另外,实时监控的话,v$session和v$sqlarea视图也很重要。v$session可以查看当前正在执行的会话,找出阻塞或长时间运行的SQL。v$sqlarea则能列出所有SQL的执行统计信息,比如执行次数、耗时等。 接下来是分析执行计划。执行计划能显示SQL如何访问数据,全表扫描还是索引问题,连接方式是否高效。用户可能需要知道如何使用EXPLAIN PLAN或者DBMS_XPLAN来获取和分析执行计划。 然后,优化策略。比如检查是否有全表扫描,统计信息是否过期,索引是否合理。还有绑定变量、SQL重写、分区表这些方法。用户提到的引用中,引用3提到了TABLE ACCESS FULL,这可能是全表扫描导致性能问题,所以需要检查索引情况。 还要考虑系统层面的因素,比如引用1提到的REDO LOG大小可能影响性能,引用3中的CPU使用率高可能与低效SQL有关。需要综合数据库整体状态,比如等待事件、锁竞争等。 生成相关问题的时候,需要覆盖用户可能进一步想知道的内容,比如具体工具的使用步骤,如何生成AWR报告,如何解读执行计划中的问题,或者索引优化的具体方法。 另外,要确保回答结构清晰,分步骤说明排查和优化的流程,引用相关参考文献中的例子来支持建议,比如引用3中的全表扫描案例,引用1的日志问题,这样用户能更直观理解。 最后,检查是否所有数学公式和独立公式都用了正确的LaTeX格式,但看起来用户的问题不涉及数学表达式,可能不需要。不过,如果有性能分析中的统计公式,可能需要用$...$,但这里可能用不上。确保回答符合用户的要求,使用中文,步骤明确,引用适当。</think>### Oracle数据库SQL排查与优化方法 --- #### **一、定位慢SQL** 1. **实时监控活跃会话** 使用动态性能视图 `v$session` 查看当前执行时间长的SQL: ```sql SELECT sid, serial#, username, sql_id, status, seconds_in_wait, event FROM v$session WHERE status = 'ACTIVE' AND sql_id IS NOT NULL; ``` 通过 `sql_id` 可关联 `v$sqlarea` 获取完整SQL文本[^3]。 2. **分析历史SQL性能** - **AWR报告**:生成指定时间段的AWR报告,查看 `SQL ordered by Elapsed Time` 和 `SQL ordered by CPU Time` 部分,识别高消耗SQL[^2]。 ```sql -- 生成AWR报告 @?/rdbms/admin/awrrpt.sql ``` - **v$sqlarea视图**:查询执行次数多、逻辑读高、耗时长的SQL: ```sql SELECT sql_id, sql_text, executions, elapsed_time/1e6 "Elapsed(s)", cpu_time/1e6 "CPU(s)", disk_reads, buffer_gets FROM v$sqlarea ORDER BY elapsed_time DESC; ``` --- #### **二、分析执行计划** 1. **获取执行计划** - 使用 `EXPLAIN PLAN`: ```sql EXPLAIN PLAN FOR SELECT * FROM your_table WHERE condition; SELECT * FROM TABLE(dbms_xplan.display); ``` - 直接查询内存中的执行计划: ```sql SELECT * FROM TABLE(dbms_xplan.display_cursor('sql_id')); ``` 2. **关键执行计划问题** - **全表扫描(TABLE ACCESS FULL)**:检查是否缺少索引或索引失效。 - **高成本操作(如HASH JOIN)**:评估表统计信息是否准确,连接顺序是否合理。 - **索引异常(INDEX SKIP SCAN/FULL SCAN)**:确认索引选择性是否足够。 --- #### **三、优化策略** 1. **索引优化** - 为高频过滤条件字段创建索引: ```sql CREATE INDEX idx_name ON table(column); ``` - 避免索引失效场景(如对索引列进行函数操作)。 2. **SQL重写** - 减少子查询嵌套,改用 `JOIN` 或 `WITH` 子句。 - 避免 `SELECT *`,仅选择必要字段。 - 使用绑定变量减少硬解析: ```sql -- 绑定变量示例 SELECT * FROM table WHERE id = :id; ``` 3. **统计信息更新** ```sql EXEC dbms_stats.gather_table_stats(ownname=>'SCHEMA', tabname=>'TABLE', cascade=>TRUE); ``` 4. **分区表与并行查询** - 对大表按时间或范围分区,减少扫描数据量。 - 使用并行执行(需评估系统资源): ```sql SELECT /*+ PARALLEL(table, 4) */ * FROM table; ``` --- #### **四、系统级检查** 1. **等待事件分析** 查看 `v$session_wait` 或 AWR 报告中的 `Top 10 Foreground Events`,确认是否由I/O、锁竞争(如 `enq: TX - row lock contention`)导致延迟[^1]。 2. **资源瓶颈** - **CPU使用率**:高CPU可能由低效SQL引起(如全表扫描)。 - **REDO LOG配置**:过小的日志文件会导致频繁切换,增加等待事件 `log file sync`。 --- #### **五、工具辅助** - **SQL Tuning Advisor**:自动生成优化建议: ```sql DECLARE task_name VARCHAR2(30); BEGIN task_name := dbms_sqltune.create_tuning_task(sql_id => 'your_sql_id'); dbms_sqltune.execute_tuning_task(task_name); END; / SELECT dbms_sqltune.report_tuning_task('task_name') FROM dual; ``` ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值