前段时间参加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中解析往往是执行的先提工作，但是通过游标共享 可以解析一次 执行多次， 执行解析可能分成多种场景：
解析与执行的场景
-
硬解析 SQL（Hard Coding）：
- 特点：硬解析一次，执行一次。
- 表现：
- 执行解析比接近 1:1。
Execute to Parse%接近 0（极差）。- 软解析率（Soft Parse%） 也接近 0%。
- 问题：高频硬解析会显著增加系统资源消耗。
-
绑定变量但仍需软解析：
- 特点：每次执行前仍需软解析。
- 表现：
- 执行解析比接近 1:1。
Execute to Parse%仍接近 0（差）。- 软解析率（Soft Parse%） 可能很高。
- 问题：虽然比硬解析略好，但解析开销仍然较高。
-
高效的解析与执行（理想场景）：
- 特点：通过静态SQL、绑定变量、
session_cached_cursor、open_cursors等技术，实现“解析一次，执行多次”。 - 表现：
- 执行解析比为 N:1（N 越大越好）。
Execute to Parse%趋近于 100%。- 软解析比例降低，解析开销显著减少。
- 优化效果：非常适合 OLTP 环境。
- 特点：通过静态SQL、绑定变量、
通俗地说 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
SELECTsql_id,executions,parse_calls,sql_text,module,parsing_schema_name,ROUND(executions / (parse_calls + 1), 2) AS execution_to_parse_ratioFROMv$sqlWHEREexecutions > 1000 -- 执行次数大于 1000（可调整）AND parse_calls > 0 -- 存在解析调用AND executions / (parse_calls + 1) < 10 -- 执行与解析比值较低（表示未使用绑定变量）ORDER BYexecutions 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
SELECTSQL_ID,ELAPSED_TIME / 1000000 AS ELAPSED_SECONDS,EXECUTIONS,SQL_TEXTFROMV$SQLWHEREEXECUTIONS > 0 -- 排除未执行的SQLAND (ELAPSED_TIME / EXECUTIONS) / 1000000 > 10 -- 平均执行时间超过10秒ORDER BYELAPSED_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;