尧图网站建设 
前言做 DBA 应该经常碰到这种事情业务突然发过来一条消息这条 SQL 最近很慢帮忙看一下。这种问题处理多了以后我现在拿到 SQL 已经很少第一时间去想“是不是缺索引”了。一条 SQL 慢可能是 Physical Reads 突然上来了也可能是 Buffer Gets 本身就很高还有些 SQL 读的数据并不多时间其实耗在锁等待、RAC 或其他 Wait Event 上。所以我现在拿到 SQL_ID我一般先看几个最直接的指标Physical Reads ↓ Buffer Gets逻辑读 ↓ Rows Processed ↓ Elapsed Time先把 SQL 慢在哪里定下来再去翻 AWR、看执行计划、查 Stats、索引、选择性和 Bind。下面这套查询基本就是我现在常用的完整路径拿到 SQL_ID 后可以一路往下查。只有 SQL Text先找到 SQL_ID如果业务已经给了 SQL_ID这一步直接跳过。如果只有 SQL Text或者只知道涉及某张表可以先从 Shared Pool 找。RAC 环境建议直接查GV$SQLSET LINES 300 SET PAGES 200 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SELECT inst_id, sql_id, child_number, plan_hash_value, executions, buffer_gets, disk_reads, rows_processed, ROUND(elapsed_time/1e6,2) elapsed_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE UPPER(sql_fulltext) LIKE %PE_BATCH_PROCESS% ORDER BY last_active_time DESC;找到 SQL_ID 后把完整 SQL 拿出来SELECT sql_fulltext FROM gv$sql WHERE sql_idsql_id AND ROWNUM1;如果 SQL 已经不在 Shared Pool可以继续从 AWR 找SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE UPPER(sql_text) LIKE %PE_BATCH_PROCESS%;先把 SQL_ID 确定下来后面的当前执行情况、历史表现、执行计划和 Bind 才能串到一起。拿到 SQL_ID先看当前资源画像我一般先把单次执行的几个核心指标一次查出来SET LINES 300 SET PAGES 200 SELECT inst_id, sql_id, child_number, plan_hash_value, executions, buffer_gets, disk_reads, rows_processed, ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec, ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec, ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec, ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s, ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE sql_idsql_id ORDER BY inst_id,child_number;第一眼先看Reads/Exec。Physical Reads 明显升高SQL 很容易从几秒变成几十秒如果 Physical Reads 不高再看Gets/Exec判断 SQL 本身是不是做了大量逻辑访问。Buffer Gets 很高时还要结合Rows/Exec因为“为了返回几十行访问几百万个块”和“本身就要处理几百万行”不是一回事。大致可以先按下面的方式定方向这里先做初步定性不急着改 SQL。Reads 和 Gets 都解释不了再看 Wait如果 Physical Reads 和 Buffer Gets 都不高但Elapsed/Exec仍然很高就应该先确认时间到底花在哪里。SQL 正在执行时SET LINES 300 SET PAGES 200 SELECT inst_id, sid, serial#, username, sql_id, status, event, wait_class, seconds_in_wait, blocking_instance, blocking_session, module, program FROM gv$session WHERE sql_idsql_id ORDER BY inst_id,sid;如果 SQL 已经执行结束可以从 ASH 看历史状态SELECT NVL(event,ON CPU) event, NVL(wait_class,CPU) wait_class, COUNT(*) samples, ROUND(COUNT(*) * 10 / 60,2) active_minutes FROM dba_hist_active_sess_history WHERE sql_idsql_id GROUP BY NVL(event,ON CPU), NVL(wait_class,CPU) ORDER BY samples DESC;这一步主要解决一个问题Elapsed 很高到底是 SQL 自己在做事还是大部分时间其实在等。再通过 AWR 看历史变化当前数据只能说明这一刻业务反馈“以前几秒现在几十秒”可以作为参考但是建议最好从 AWR 看真实历史SET LINES 300 SET PAGES 300 SELECT s.instance_number, s.snap_id, TO_CHAR(s.begin_interval_time,YYYY-MM-DD HH24:MI) begin_time, st.plan_hash_value, st.executions_delta executions, st.buffer_gets_delta buffer_gets, st.disk_reads_delta disk_reads, st.rows_processed_delta rows_processed, ROUND(st.disk_reads_delta/ NULLIF(st.executions_delta,0)) reads_per_exec, ROUND(st.buffer_gets_delta/ NULLIF(st.executions_delta,0)) gets_per_exec, ROUND(st.rows_processed_delta/ NULLIF(st.executions_delta,0)) rows_per_exec, ROUND(st.elapsed_time_delta/1e6/ NULLIF(st.executions_delta,0),2) elapsed_per_exec_s, ROUND(st.cpu_time_delta/1e6/ NULLIF(st.executions_delta,0),2) cpu_per_exec_s FROM dba_hist_sqlstat st JOIN dba_hist_snapshot s ON s.dbidst.dbid AND s.instance_numberst.instance_number AND s.snap_idst.snap_id WHERE st.sql_idsql_id ORDER BY s.begin_interval_time DESC, s.instance_number;这里主要比较Plan Hash、Reads/Exec、Gets/Exec、Rows/Exec、Elapsed/Exec和Executions。AWR 这一层最重要的是找到到底哪个指标从什么时候开始发生变化。确认是 SQL 本身的问题再看执行计划如果前面的证据已经指向 SQL 自身访问量或访问路径再看执行计划SET LINES 300 SET PAGES 500 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id, NULL, ALLSTATS LAST PEEKED_BINDS PREDICATE ALIAS ) );我主要看Access Predicate Filter Predicate E-Rows / A-Rows Starts Buffers / Reads Peeked Binds表面上出现INDEX RANGE SCAN不代表访问路径一定合理。真正要看的是 Oracle 用什么条件进入索引又有哪些条件被留到回表以后才过滤。E-Rows / A-Rows也很重要。如果 CBO 估算几十行实际却返回几十万行说明基数估算已经明显失真这时候下一步应该先查 Stats、Histogram、Bind 或列相关性而不是直接开始建索引。ALLSTATS LAST只有在实际执行统计被采集时才能看到完整的 A-Rows、Buffers 等信息。如果没有采集到不要把缺失值当成 0先利用现有的 E-Rows、Predicate、Peeked Binds 和其他运行数据继续分析。根据 Plan再查 Stats、索引、选择性和 Bind如果执行计划估算明显不对先查表统计信息SET LINES 300 SET PAGES 200 SELECT owner, table_name, num_rows, blocks, avg_row_len, stale_stats, stattype_locked, TO_CHAR(last_analyzed,YYYY-MM-DD HH24:MI:SS) last_analyzed FROM dba_tab_statistics WHERE ownerowner AND table_nametable_name AND object_typeTABLE;重点看NUM_ROWS、BLOCKS、LAST_ANALYZED、STALE_STATS和STATTYPE_LOCKED。如果 CBO 仍然拿着明显过期的数据规模和分布做成本估算计划出现偏差并不奇怪。Stats 没明显问题再把现有索引摊开SET LINES 300 SET PAGES 300 SELECT i.index_name, i.uniqueness, i.status, i.degree, ic.column_position, ic.column_name FROM dba_indexes i JOIN dba_ind_columns ic ON ic.index_owneri.owner AND ic.index_namei.index_name WHERE i.table_ownerowner AND i.table_nametable_name ORDER BY i.index_name, ic.column_position;再看真正参与过滤的字段SELECT column_name, num_distinct, num_nulls, density, histogram, num_buckets, sample_size, TO_CHAR(last_analyzed,YYYY-MM-DD HH24:MI) last_analyzed FROM dba_tab_col_statistics WHERE ownerowner AND table_nametable_name ORDER BY column_name;这里真正要回答的不是“有没有索引”而是索引列顺序是否匹配 SQL 的过滤方式当前 Access Predicate 的选择性到底怎么样。如果 SQL 使用 Bind Variable还要把实际捕获到的 Bind 一起看SET LINES 300 SET PAGES 300 SELECT inst_id, sql_id, child_number, name, position, datatype_string, value_string, TO_CHAR(last_captured,YYYY-MM-DD HH24:MI:SS) last_captured FROM gv$sql_bind_capture WHERE sql_idsql_id ORDER BY inst_id, child_number, position;同一条时间范围 SQL查一天和查三个月SQL_ID 可以完全一样实际扫描量却不是一回事。自己构造测试 SQL 时Bind 尽量还原业务现场。到这里优化方向通常已经比较清楚Stats 失真就处理统计信息访问路径不合理再考虑索引扫描范围本身太大就考虑 SQL 改写、分区裁剪或者业务侧缩小范围如果 SQL 本身读得不多真正耗时来自 Blocking、RAC 或 I/O 等待就应该处理对应等待而不是继续加索引。优化后再跑一次核心指标验收真正实施变更以后不需要重新发明一套验收方法直接回到文章最开始那几个指标SELECT inst_id, sql_id, child_number, plan_hash_value, executions, ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec, ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec, ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec, ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s, ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE sql_idsql_id ORDER BY inst_id,child_number;前后至少对比Plan Hash Reads/Exec Gets/Exec Rows/Exec Elapsed/Exec例如指标优化前优化后Plan HashABReads/Exec65,0000Gets/Exec800,000198Elapsed/Exec32.5s0.08sRows/Exec5050Explain Plan 变好了不算结束手工测试变快也不能完全代表业务现场。条件允许的话等真实业务再次执行以后再从GV$SQL或 AWR 验证一次结果才更有说服力。最后如果把整套过程压成一条线就是SQL 优化真正重要的不是最后用了哪一种手段而是前面的证据链能不能说明它为什么慢、为什么这样改以及改完以后到底改善了多少。 Oracle 慢 SQL 怎么查？这一套组合拳够用了
前言做 DBA 应该经常碰到这种事情业务突然发过来一条消息这条 SQL 最近很慢帮忙看一下。这种问题处理多了以后我现在拿到 SQL 已经很少第一时间去想“是不是缺索引”了。一条 SQL 慢可能是 Physical Reads 突然上来了也可能是 Buffer Gets 本身就很高还有些 SQL 读的数据并不多时间其实耗在锁等待、RAC 或其他 Wait Event 上。所以我现在拿到 SQL_ID我一般先看几个最直接的指标Physical Reads ↓ Buffer Gets逻辑读 ↓ Rows Processed ↓ Elapsed Time先把 SQL 慢在哪里定下来再去翻 AWR、看执行计划、查 Stats、索引、选择性和 Bind。下面这套查询基本就是我现在常用的完整路径拿到 SQL_ID 后可以一路往下查。只有 SQL Text先找到 SQL_ID如果业务已经给了 SQL_ID这一步直接跳过。如果只有 SQL Text或者只知道涉及某张表可以先从 Shared Pool 找。RAC 环境建议直接查GV$SQLSET LINES 300 SET PAGES 200 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SELECT inst_id, sql_id, child_number, plan_hash_value, executions, buffer_gets, disk_reads, rows_processed, ROUND(elapsed_time/1e6,2) elapsed_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE UPPER(sql_fulltext) LIKE %PE_BATCH_PROCESS% ORDER BY last_active_time DESC;找到 SQL_ID 后把完整 SQL 拿出来SELECT sql_fulltext FROM gv$sql WHERE sql_idsql_id AND ROWNUM1;如果 SQL 已经不在 Shared Pool可以继续从 AWR 找SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE UPPER(sql_text) LIKE %PE_BATCH_PROCESS%;先把 SQL_ID 确定下来后面的当前执行情况、历史表现、执行计划和 Bind 才能串到一起。拿到 SQL_ID先看当前资源画像我一般先把单次执行的几个核心指标一次查出来SET LINES 300 SET PAGES 200 SELECT inst_id, sql_id, child_number, plan_hash_value, executions, buffer_gets, disk_reads, rows_processed, ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec, ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec, ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec, ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s, ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE sql_idsql_id ORDER BY inst_id,child_number;第一眼先看Reads/Exec。Physical Reads 明显升高SQL 很容易从几秒变成几十秒如果 Physical Reads 不高再看Gets/Exec判断 SQL 本身是不是做了大量逻辑访问。Buffer Gets 很高时还要结合Rows/Exec因为“为了返回几十行访问几百万个块”和“本身就要处理几百万行”不是一回事。大致可以先按下面的方式定方向这里先做初步定性不急着改 SQL。Reads 和 Gets 都解释不了再看 Wait如果 Physical Reads 和 Buffer Gets 都不高但Elapsed/Exec仍然很高就应该先确认时间到底花在哪里。SQL 正在执行时SET LINES 300 SET PAGES 200 SELECT inst_id, sid, serial#, username, sql_id, status, event, wait_class, seconds_in_wait, blocking_instance, blocking_session, module, program FROM gv$session WHERE sql_idsql_id ORDER BY inst_id,sid;如果 SQL 已经执行结束可以从 ASH 看历史状态SELECT NVL(event,ON CPU) event, NVL(wait_class,CPU) wait_class, COUNT(*) samples, ROUND(COUNT(*) * 10 / 60,2) active_minutes FROM dba_hist_active_sess_history WHERE sql_idsql_id GROUP BY NVL(event,ON CPU), NVL(wait_class,CPU) ORDER BY samples DESC;这一步主要解决一个问题Elapsed 很高到底是 SQL 自己在做事还是大部分时间其实在等。再通过 AWR 看历史变化当前数据只能说明这一刻业务反馈“以前几秒现在几十秒”可以作为参考但是建议最好从 AWR 看真实历史SET LINES 300 SET PAGES 300 SELECT s.instance_number, s.snap_id, TO_CHAR(s.begin_interval_time,YYYY-MM-DD HH24:MI) begin_time, st.plan_hash_value, st.executions_delta executions, st.buffer_gets_delta buffer_gets, st.disk_reads_delta disk_reads, st.rows_processed_delta rows_processed, ROUND(st.disk_reads_delta/ NULLIF(st.executions_delta,0)) reads_per_exec, ROUND(st.buffer_gets_delta/ NULLIF(st.executions_delta,0)) gets_per_exec, ROUND(st.rows_processed_delta/ NULLIF(st.executions_delta,0)) rows_per_exec, ROUND(st.elapsed_time_delta/1e6/ NULLIF(st.executions_delta,0),2) elapsed_per_exec_s, ROUND(st.cpu_time_delta/1e6/ NULLIF(st.executions_delta,0),2) cpu_per_exec_s FROM dba_hist_sqlstat st JOIN dba_hist_snapshot s ON s.dbidst.dbid AND s.instance_numberst.instance_number AND s.snap_idst.snap_id WHERE st.sql_idsql_id ORDER BY s.begin_interval_time DESC, s.instance_number;这里主要比较Plan Hash、Reads/Exec、Gets/Exec、Rows/Exec、Elapsed/Exec和Executions。AWR 这一层最重要的是找到到底哪个指标从什么时候开始发生变化。确认是 SQL 本身的问题再看执行计划如果前面的证据已经指向 SQL 自身访问量或访问路径再看执行计划SET LINES 300 SET PAGES 500 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id, NULL, ALLSTATS LAST PEEKED_BINDS PREDICATE ALIAS ) );我主要看Access Predicate Filter Predicate E-Rows / A-Rows Starts Buffers / Reads Peeked Binds表面上出现INDEX RANGE SCAN不代表访问路径一定合理。真正要看的是 Oracle 用什么条件进入索引又有哪些条件被留到回表以后才过滤。E-Rows / A-Rows也很重要。如果 CBO 估算几十行实际却返回几十万行说明基数估算已经明显失真这时候下一步应该先查 Stats、Histogram、Bind 或列相关性而不是直接开始建索引。ALLSTATS LAST只有在实际执行统计被采集时才能看到完整的 A-Rows、Buffers 等信息。如果没有采集到不要把缺失值当成 0先利用现有的 E-Rows、Predicate、Peeked Binds 和其他运行数据继续分析。根据 Plan再查 Stats、索引、选择性和 Bind如果执行计划估算明显不对先查表统计信息SET LINES 300 SET PAGES 200 SELECT owner, table_name, num_rows, blocks, avg_row_len, stale_stats, stattype_locked, TO_CHAR(last_analyzed,YYYY-MM-DD HH24:MI:SS) last_analyzed FROM dba_tab_statistics WHERE ownerowner AND table_nametable_name AND object_typeTABLE;重点看NUM_ROWS、BLOCKS、LAST_ANALYZED、STALE_STATS和STATTYPE_LOCKED。如果 CBO 仍然拿着明显过期的数据规模和分布做成本估算计划出现偏差并不奇怪。Stats 没明显问题再把现有索引摊开SET LINES 300 SET PAGES 300 SELECT i.index_name, i.uniqueness, i.status, i.degree, ic.column_position, ic.column_name FROM dba_indexes i JOIN dba_ind_columns ic ON ic.index_owneri.owner AND ic.index_namei.index_name WHERE i.table_ownerowner AND i.table_nametable_name ORDER BY i.index_name, ic.column_position;再看真正参与过滤的字段SELECT column_name, num_distinct, num_nulls, density, histogram, num_buckets, sample_size, TO_CHAR(last_analyzed,YYYY-MM-DD HH24:MI) last_analyzed FROM dba_tab_col_statistics WHERE ownerowner AND table_nametable_name ORDER BY column_name;这里真正要回答的不是“有没有索引”而是索引列顺序是否匹配 SQL 的过滤方式当前 Access Predicate 的选择性到底怎么样。如果 SQL 使用 Bind Variable还要把实际捕获到的 Bind 一起看SET LINES 300 SET PAGES 300 SELECT inst_id, sql_id, child_number, name, position, datatype_string, value_string, TO_CHAR(last_captured,YYYY-MM-DD HH24:MI:SS) last_captured FROM gv$sql_bind_capture WHERE sql_idsql_id ORDER BY inst_id, child_number, position;同一条时间范围 SQL查一天和查三个月SQL_ID 可以完全一样实际扫描量却不是一回事。自己构造测试 SQL 时Bind 尽量还原业务现场。到这里优化方向通常已经比较清楚Stats 失真就处理统计信息访问路径不合理再考虑索引扫描范围本身太大就考虑 SQL 改写、分区裁剪或者业务侧缩小范围如果 SQL 本身读得不多真正耗时来自 Blocking、RAC 或 I/O 等待就应该处理对应等待而不是继续加索引。优化后再跑一次核心指标验收真正实施变更以后不需要重新发明一套验收方法直接回到文章最开始那几个指标SELECT inst_id, sql_id, child_number, plan_hash_value, executions, ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec, ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec, ROUND(rows_processed/NULLIF(executions,0)) rows_per_exec, ROUND(elapsed_time/1e6/NULLIF(executions,0),2) elapsed_per_exec_s, ROUND(cpu_time/1e6/NULLIF(executions,0),2) cpu_per_exec_s, TO_CHAR(last_active_time,YYYY-MM-DD HH24:MI:SS) last_active FROM gv$sql WHERE sql_idsql_id ORDER BY inst_id,child_number;前后至少对比Plan Hash Reads/Exec Gets/Exec Rows/Exec Elapsed/Exec例如指标优化前优化后Plan HashABReads/Exec65,0000Gets/Exec800,000198Elapsed/Exec32.5s0.08sRows/Exec5050Explain Plan 变好了不算结束手工测试变快也不能完全代表业务现场。条件允许的话等真实业务再次执行以后再从GV$SQL或 AWR 验证一次结果才更有说服力。最后如果把整套过程压成一条线就是SQL 优化真正重要的不是最后用了哪一种手段而是前面的证据链能不能说明它为什么慢、为什么这样改以及改完以后到底改善了多少。