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

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

类型	硬解析（Hard Parsing）	软解析（Soft Parsing）	软软解析（Fast  Parsing）
定义	SQL 无匹配计划，需重新生成执行计划。	SQL 匹配到已有计划，但需部分验证。	SQL 完全命中缓存，直接使用执行计划。
触发条件	不使用绑定变量或缓存中无匹配。	使用绑定变量，计划部分验证通过。	使用绑定变量，计划完全匹配，无需验证。
资源消耗	最高（CPU、内存开销大）。	较低（部分解析验证）。	最低（几乎无消耗）。
性能	最差（增加解析时间、锁竞争）。	中等（解析步骤减少，性能提升）。	最优（完全缓存命中，解析效率最高）。
优化方法	统一 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;