外观
Oracle SQL级别指标
执行时间指标
响应时间
- 总执行时间：
SELECT elapsed_time/1000000 "Seconds" FROM v$sql WHERE sql_id = 'sql_id'; - CPU 时间：
SELECT cpu_time/1000000 "Seconds" FROM v$sql WHERE sql_id = 'sql_id'; - 等待时间：
SELECT (elapsed_time - cpu_time)/1000000 "Seconds" FROM v$sql WHERE sql_id = 'sql_id';
执行频率
- 执行次数：
SELECT executions FROM v$sql WHERE sql_id = 'sql_id'; - 平均执行时间：
SELECT (elapsed_time/executions)/1000000 "Avg Seconds" FROM v$sql WHERE sql_id = 'sql_id' AND executions > 0; - 每分钟执行次数：
SELECT executions/(elapsed_time/1000000/60) "Executions Per Minute" FROM v$sql WHERE sql_id = 'sql_id' AND elapsed_time > 0;
时间分布
- 解析时间：
SELECT parse_time/1000000 "Seconds" FROM v$sql WHERE sql_id = 'sql_id'; - 优化时间：
SELECT optimizer_cost FROM v$sql WHERE sql_id = 'sql_id'; - 执行时间：
SELECT (elapsed_time - parse_time)/1000000 "Seconds" FROM v$sql WHERE sql_id = 'sql_id';
资源消耗指标
I/O 消耗
- 逻辑读：
SELECT buffer_gets FROM v$sql WHERE sql_id = 'sql_id'; - 物理读：
SELECT disk_reads FROM v$sql WHERE sql_id = 'sql_id'; - 直接读：
SELECT direct_reads FROM v$sql WHERE sql_id = 'sql_id'; - 直接写：
SELECT direct_writes FROM v$sql WHERE sql_id = 'sql_id'; - 平均逻辑读：
SELECT buffer_gets/executions "Avg Buffer Gets" FROM v$sql WHERE sql_id = 'sql_id' AND executions > 0;
内存消耗
- 共享池使用：
SELECT sharable_mem FROM v$sql WHERE sql_id = 'sql_id'; - 持久内存使用：
SELECT persistent_mem FROM v$sql WHERE sql_id = 'sql_id'; - 运行时内存使用：
SELECT runtime_mem FROM v$sql WHERE sql_id = 'sql_id';
网络消耗
- 行处理数：
SELECT rows_processed FROM v$sql WHERE sql_id = 'sql_id'; - 平均每行处理时间：
SELECT (elapsed_time/rows_processed)/1000000 "Avg Seconds Per Row" FROM v$sql WHERE sql_id = 'sql_id' AND rows_processed > 0; - 平均每行逻辑读：
SELECT buffer_gets/rows_processed "Avg Buffer Gets Per Row" FROM v$sql WHERE sql_id = 'sql_id' AND rows_processed > 0;
执行计划指标
执行计划成本
- 优化器成本：
SELECT optimizer_cost FROM v$sql WHERE sql_id = 'sql_id'; - 实际行数：
SELECT rows_processed FROM v$sql WHERE sql_id = 'sql_id'; - 估计行数：通过执行计划查看
- 行数差异：实际行数与估计行数的差异
执行计划操作
- 全表扫描：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' AND operation = 'TABLE ACCESS' AND options = 'FULL'; - 索引扫描：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' AND operation = 'INDEX'; - 嵌套循环：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' AND operation = 'NESTED LOOPS'; - 哈希连接：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' AND operation = 'HASH JOIN'; - 排序操作：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' AND operation = 'SORT';
执行计划稳定性
- 执行计划变更：监控 SQL 语句的执行计划是否发生变化
- 绑定变量窥视：
SELECT * FROM v$sql WHERE sql_id = 'sql_id' AND bind_data IS NOT NULL; - 统计信息影响：检查统计信息变更对执行计划的影响
等待事件指标
主要等待事件
- 查看等待事件：
SELECT event, total_waits, time_waited FROM v$sql_metrics WHERE sql_id = 'sql_id'; - 等待事件占比：分析各种等待事件的占比
- 主要等待原因：识别 SQL 语句的主要等待原因
常见等待事件
- I/O 等待：
db file sequential read,db file scattered read - 锁等待：
enqueue,row lock contention - 缓冲区等待：
buffer busy waits,free buffer waits - 并发等待：
latch free,mutex contention - 网络等待：
SQL*Net message from client,SQL*Net message to client
等待事件分析
- 等待时间分析：
SELECT event, time_waited FROM v$sql_metrics WHERE sql_id = 'sql_id' ORDER BY time_waited DESC; - 等待次数分析：
SELECT event, total_waits FROM v$sql_metrics WHERE sql_id = 'sql_id' ORDER BY total_waits DESC; - 平均等待时间：
SELECT event, time_waited/total_waits "Avg Wait Time" FROM v$sql_metrics WHERE sql_id = 'sql_id' AND total_waits > 0 ORDER BY "Avg Wait Time" DESC;
统计信息指标
表统计信息
- 表行数：
SELECT num_rows FROM dba_tables WHERE table_name = 'TABLE_NAME'; - 表块数：
SELECT blocks FROM dba_tables WHERE table_name = 'TABLE_NAME'; - 平均行长度：
SELECT avg_row_len FROM dba_tables WHERE table_name = 'TABLE_NAME';
索引统计信息
- 索引高度：
SELECT blevel FROM dba_indexes WHERE index_name = 'INDEX_NAME'; - 索引叶子块：
SELECT leaf_blocks FROM dba_indexes WHERE index_name = 'INDEX_NAME'; - 索引区分度：
SELECT distinct_keys FROM dba_indexes WHERE index_name = 'INDEX_NAME';
统计信息新鲜度
- 统计信息收集时间：
SELECT last_analyzed FROM dba_tables WHERE table_name = 'TABLE_NAME'; - 数据变更率：分析表数据的变更情况
- 统计信息是否过时：根据数据变更率判断
SQL 性能监控
实时监控
- 活动 SQL 监控：
EXECUTE DBMS_SQLTUNE.CREATE_SQL_SET('sqlset_name'); - 查看活动 SQL：
SELECT * FROM v$active_session_history WHERE sql_id = 'sql_id'; - 实时执行计划：
SELECT * FROM v$sql_plan_monitor WHERE sql_id = 'sql_id';
历史监控
- AWR 报告：
@$ORACLE_HOME/rdbms/admin/awrrpt.sql - SQL 性能分析器：
EXECUTE DBMS_SQLPA.ANALYZE_SQL_SET; - 历史执行计划：
SELECT * FROM dba_hist_sql_plan WHERE sql_id = 'sql_id';
自动性能诊断
- ADDM 报告：
@$ORACLE_HOME/rdbms/admin/addmrpt.sql - SQL 调优顾问：
EXECUTE DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'sql_id'); - 自动 SQL 优化：
EXECUTE DBMS_AUTO_SQLTUNE.EXECUTE_TUNING_TASK;
SQL 级别指标的使用
性能问题识别
- 识别高负载 SQL：
SELECT sql_id, elapsed_time, buffer_gets FROM v$sql ORDER BY elapsed_time DESC; - 识别 I/O 密集型 SQL：
SELECT sql_id, disk_reads FROM v$sql ORDER BY disk_reads DESC; - 识别 CPU 密集型 SQL：
SELECT sql_id, cpu_time FROM v$sql ORDER BY cpu_time DESC; - 识别执行频率高的 SQL：
SELECT sql_id, executions FROM v$sql ORDER BY executions DESC;
性能瓶颈分析
- 分析执行计划：
EXPLAIN PLAN FOR SELECT * FROM table_name; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); - 分析等待事件：
SELECT event, time_waited FROM v$sql_metrics WHERE sql_id = 'sql_id' ORDER BY time_waited DESC; - 分析资源消耗：
SELECT buffer_gets, disk_reads, cpu_time FROM v$sql WHERE sql_id = 'sql_id';
性能优化建议
- 索引优化：添加或修改索引
- SQL 重写：优化 SQL 语句结构
- 执行计划调整：使用提示或修改统计信息
- 分区优化：使用分区表减少数据扫描范围
- 并行执行：对于大型查询使用并行执行
常见问题（FAQ）
Q1: 如何识别性能最差的 SQL 语句？
A1: 可以通过以下方法识别性能最差的 SQL 语句：
- 按执行时间排序：
SELECT sql_id, sql_text, elapsed_time/1000000 "Seconds", executions FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY; - 按逻辑读排序：
SELECT sql_id, sql_text, buffer_gets, executions FROM v$sql ORDER BY buffer_gets DESC FETCH FIRST 10 ROWS ONLY; - 按物理读排序：
SELECT sql_id, sql_text, disk_reads, executions FROM v$sql ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY; - 按平均执行时间排序：
SELECT sql_id, sql_text, (elapsed_time/executions)/1000000 "Avg Seconds", executions FROM v$sql WHERE executions > 0 ORDER BY "Avg Seconds" DESC FETCH FIRST 10 ROWS ONLY;
Q2: 如何分析 SQL 语句的执行计划？
A2: 分析 SQL 语句的执行计划可以通过以下方法：
- 使用 EXPLAIN PLAN：
EXPLAIN PLAN FOR SELECT * FROM table_name WHERE condition; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); - 使用 V$SQL_PLAN：
SELECT * FROM v$sql_plan WHERE sql_id = 'sql_id' ORDER BY id; - 使用 DBMS_XPLAN.DISPLAY_CURSOR：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id')); - 使用 Oracle Enterprise Manager：通过 OEM 界面查看执行计划
- 分析执行计划操作：识别全表扫描、索引扫描、连接操作等
- 分析执行计划成本：评估优化器成本是否合理
Q3: 如何优化高 I/O 消耗的 SQL 语句？
A3: 优化高 I/O 消耗的 SQL 语句可以通过以下方法：
- 添加或修改索引：减少物理读和逻辑读
- 使用分区表：只扫描相关分区的数据
- 优化 SQL 语句：减少不必要的列和行
- 使用物化视图：对于复杂查询使用物化视图
- 调整执行计划：使用提示或修改统计信息
- 增加缓冲区缓存：提高数据缓存命中率
- 优化存储系统：提高 I/O 子系统性能
Q4: 如何监控 SQL 语句的执行频率和响应时间？
A4: 监控 SQL 语句的执行频率和响应时间可以通过以下方法：
- 使用 V$SQL 视图：
SELECT sql_id, sql_text, executions, (elapsed_time/executions)/1000000 "Avg Response Time" FROM v$sql WHERE executions > 0 ORDER BY executions DESC; - 使用 AWR 报告：查看 Top SQL 部分
- 使用 SQL 监控：
EXECUTE DBMS_SQLTUNE.CREATE_SQL_SET('frequent_sql'); - 使用自定义监控脚本：定期收集 SQL 执行统计信息
- 设置阈值告警：当 SQL 执行时间超过阈值时告警
Q5: 如何判断 SQL 语句的性能是否正常？
A5: 判断 SQL 语句的性能是否正常可以通过以下方法：
- 与历史性能比较：分析 SQL 语句的历史执行时间
- 与预期性能比较：根据业务需求评估性能是否满足
- 与同类 SQL 比较：与相似功能的 SQL 语句性能比较
- 分析资源消耗：评估 CPU、I/O 等资源消耗是否合理
- 分析执行计划：检查执行计划是否最优
- 分析等待事件：识别是否存在异常等待事件
- 使用基准测试：建立性能基准，定期比较