Oracle最佳实践-优化硬解析

前段时间参加oracle CAB，oracle高级服务部门做了一个数据库最佳实践的报告，其中就有一项就是解决未使用绑定变量但执行次数很多的SQL； 对于一个数据库来说如果不知道该如何优化，那么最简单最有效的优化就是减少硬解析，当然这部分优化对于DBA来说推动起来会有些难度，毕竟修改代码需要开发和业务部分配合，但是相对来说优化的难度低（绑定变量），带来的收益高，这里来介绍一下如何优化硬解析。

1. sql的执行路径

在优化之前首先要了解sql的执行路径，只有了解了sql的执行路径才能更好的了解硬解析和软解析。

1.1 SQL 解析（Parsing）

语法检查：验证 SQL 语句的语法是否正确。

语义检查：检查语义的合法性（如表、列是否存在，权限是否满足）。

共享池检查：在共享池中查找相同的 SQL 语句，避免重复解析（软解析）。

生成执行计划：如果是首次执行（硬解析），优化器会生成最佳的执行计划。

1.2 SQL 绑定（Binding）

如果 SQL 包含绑定变量（如 :1 或 :2），将实际的变量值替换到绑定变量的位置。

确保 SQL 在执行时能够正确应用输入参数。

1.3 SQL 优化（Optimization）

使用优化器（基于成本CBO或规则）评估多种执行计划。

选择具有最低成本的计划，用于后续执行。

1.4 执行计划生成（Execution Plan Generation）

确定具体的访问路径（如全表扫描、索引扫描）。

确定连接方法（如嵌套循环、哈希连接）。

1.5 SQL 执行（Execution）

根据执行计划，读取所需的数据块。

完成逻辑操作（如过滤、排序、连接）。

1.6 数据返回（Fetching）

如果是查询操作，按需从缓冲区或磁盘中提取数据。

数据按行或批量形式返回给客户端。

oracle sql执行流程图

2.硬解析VS软解析VS软软解析

通用整体性sql优化，我认为最容易最有效的方式就是减少系统的硬解析比例，使用绑定变量减少硬解析的比例可以有效提升系统的整体性能。

硬解析，软解析和软软解析对比

3.了解系统整体的解析情况

最常用的办法是通过AWR报表来了解系统的整体的解析情况，其中关键的指标如下几个指标

Parses（SQL）：每秒or事务解析次数，反应了系统的繁忙层度，数据来源v$sysstat statistics parse count (total)

Hard parses（SQL）：每秒or事务硬解析次数，反应了系统硬解析的整体状况，数据来源v$sysstat statistics parse count (hard)

Soft Parse%：最重要的一个指标， 软解析比例，无需多说的经典指标，数据来源v$sysstat statistics的parse count(total)和parse count(hard)。 合理值>95% Soft Parse %是AWR中另一个重要的解析指标，该指标反应了快照时间内 软解析次数 和 总解析次数 (soft+hard 软解析次数+硬解析次数)的比值，若该指标很低，那么说明了可能 存在剧烈的hard parse硬解析，大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharing=FORCE)； 理论上我们总是希望 Soft Parse % 接近于 100%， 但并不是说100%的软解析就是最理想的解析状态，通过设置 session_cached_cursors参数和反复重用游标我们可以让解析来的更轻量级，即通俗所说的利用会话缓存游 标实现的软软解析(fast parse)

Execute to Parse%:Execute to Parse% 指标反映了执行解析比 计算方法 1-(parse/execute) , 目标为100% 即接近于只执行而不解析。 数据来源v$sysstat statistics parse count (total) 和execute count 

在oracle中解析往往是执行的先提工作，但是通过游标共享 可以解析一次 执行多次， 执行解析可能分成多种场景：

解析与执行的场景

1. 硬解析 SQL（Hard Coding）：

   • 特点：硬解析一次，执行一次。
   • 表现：
     • 执行解析比接近 1:1。
     • Execute to Parse% 接近 0（极差）。
     • 软解析率（Soft Parse%） 也接近 0%。
   • 问题：高频硬解析会显著增加系统资源消耗。

2. 绑定变量但仍需软解析：

   • 特点：每次执行前仍需软解析。
   • 表现：
     • 执行解析比接近 1:1。
     • Execute to Parse% 仍接近 0（差）。
     • 软解析率（Soft Parse%） 可能很高。
   • 问题：虽然比硬解析略好，但解析开销仍然较高。

3. 高效的解析与执行（理想场景）：

   • 特点：通过静态SQL、绑定变量、session_cached_cursor、open_cursors等技术，实现“解析一次，执行多次”。
   • 表现：
     • 执行解析比为 N:1（N 越大越好）。
     • Execute to Parse% 趋近于 100%。
     • 软解析比例降低，解析开销显著减少。
   • 优化效果：非常适合 OLTP 环境。

 通俗地说 soft parse% 反映了软解析率， 而软解析在oracle中仍是较昂贵的操作， 我们希望的是解析1次执行N次，如果每次执行均需要软解析，那么虽然soft parse%=100% 但是parse time仍可能是消耗DB TIME的大头。 Execute to Parse反映了 执行解析比，Execute to Parse和soft parse% 都很低 那么说明确实没有绑定变量 ， 而如果 soft parse% 接近99% 而Execute to Parse 不足90% 则说明执行解析比低， 可以通过静态SQL、动态绑定、session_cached_cursor、open cursors等技术减少软解析。

Parse CPU To Parse Elapsd:该指标反映了 快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time)； 若该指标水平很低，那么说明在整个解析过程中 实际在CPU上运算的时间是很短的，而主要的解析时间都耗费在各种其他非空闲的等待事件上了(如latch:shared pool,row cache lock之类等) 数据来源 V$sysstat 的 parse time cpu和parse time elapsed

4.查询未绑定变量的高频 SQL

V$SQL 视图包含每个 SQL 语句的执行统计信息，可以用以下 SQL 查询未绑定变量的高频 SQL

SELECT     sql_id,    executions,    parse_calls,    sql_text,    module,    parsing_schema_name,    ROUND(executions / (parse_calls + 1), 2) AS execution_to_parse_ratioFROM     v$sqlWHERE     executions > 1000   -- 执行次数大于 1000（可调整）    AND parse_calls > 0 -- 存在解析调用    AND executions / (parse_calls + 1) < 10 -- 执行与解析比值较低（表示未使用绑定变量）ORDER BY     executions DESC;

-- 说明

-- executions：SQL 语句的执行次数。

-- parse_calls：SQL 被解析的次数（高解析调用可能是未绑定变量的症状）。

-- execution_to_parse_ratio：执行次数与解析次数的比值，越低越可能是未绑定变量。

-- sql_text：SQL 文本，可以查看具体内容。

重点关注用户schema下的高频sql

检查具体 SQL 的绑定变量使用情况

SELECT *   FROM    v$sql_bind_captureWHERE sql_id = '&SQL_ID';

 VALUE_STRING 最近一次捕获的绑定变量值（以字符串形式存储）。

5.检查高负载 SQL

使用 Oracle 提供的 Active Session History（ASH）或 AWR 报告分析高负载 SQL：

• ASH：分析活跃会话中的高频 SQL
• AWR 报告：查看执行次数最多的 SQL 列表

• 根据查出的结果在针对sql做具体的分析

在AWR中可以按各种维度来定位top sql 然后再做针对性优化

查询 AWR 中执行次数最多的 SQL

-- 查询 AWR 中执行次数最多的 SQLSELECT * FROM dba_hist_sqlstatWHERE executions_delta > 1000 -- 根据执行次数筛选ORDER BY executions_delta DESC;

查询平均执行时间超过10秒的sql

 SELECT     SQL_ID,    ELAPSED_TIME / 1000000 AS ELAPSED_SECONDS,    EXECUTIONS,    SQL_TEXTFROM     V$SQLWHERE     EXECUTIONS > 0 -- 排除未执行的SQL    AND (ELAPSED_TIME / EXECUTIONS) / 1000000 > 10 -- 平均执行时间超过10秒ORDER BY     ELAPSED_SECONDS DESC;

利用sql_monitor优化

----sql monitor---------SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sql_id', type => 'TEXT') AS report FROM dual;

被sql monitor监控的触发条件

执行时间>5秒

SQL 的累计执行时间（CPU 时间 + I/O 时间）超过 5秒。

这个时间阈值可以通过参数 SQLMON_THRESHOLD 调整：

ALTER SESSION SET "_sqlmon_threshold" = 2; -- session级别设置阈值为 2 秒ALTER SESSION SET "_sqlmon_threshold" = 2; --系统级别设置阈值为2秒

并行执行（Parallel Execution）：

SQL 使用了并行执行计划（Parallel Execution）。

即使执行时间较短，但因为使用了并行，SQL 会自动被监控。

被强制要求监控：

通过 SQL Hints 强制启用 SQL Monitor

SELECT /*+ MONITOR */ ... FROM table_name;