优化10-解析-CSDN博客

本文链接：https://blog.csdn.net/qq_29431123/article/details/148470427

SQL执行过程

一个SQL执行过程可以分为三步解析（Parse）、执行（Execute）、抓取（Fetch）。下面是10046中某个SQL的执行信息

SQL ID: 7afpjznt597s1 Plan Hash: 2211052296

select * 
from
 scott.tab1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.00         78        152          4        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.00       0.00         78        152          4        1000

更详细一些，可分为下面的步骤：

打开游标
解析
绑定如果有绑定变量，执行此步骤，没有使用绑定变量将略过此步骤。
执行对于DML而言，执行阶段将完成实际的用户修改。而select 执行只是为抓取做些准备工作。
抓取逻辑读、物理读将在这一阶段产生。满足条件的行以逻辑读的形式从buffer cache读取到PGA的SDU中
设定列与变量的对应关系
将抓取的SDU中的结果传送给对应的客户端变量。
关闭游标

共享池和Latch

解析主要是在Oracle 的共享池内完成的，所以了解共享池的内存结构和管理其内存的锁的工作原理对深入理解解析是很有帮助的，后续会出专门的章节来介绍共享池和锁，这里只做简单介绍。

共享池就像厨房里的水池，什么东西都能往里面。相比较于buffer cache，共享池内容杂乱无章，共享池内的基本分配单元是chunk，大小极不统一，小的十来个字节，大的几十兆，上百兆。再加上频繁的分配释放，极易产生碎片，从而导致共享池效率下降，甚至内存不足4031错误就是共享池内存不足错误。

Oracle中有个视图 X$KSMSP专门记录共享池堆所有chunk情况。他有多少行，代表共享池有多少Chunk。ksmchsiz列是每个Chunk的大小。ksmchcls用于记录Chunk的类型。共享池中有8中类型的Chunk。free、freeabl、perm、recrR-free 、R-freeabl、R-perm、R-recr

free：空闲的Chunk，这种Chunk可以被直接覆盖，进程从共享池分配内存时，第一步找的就是free位置Chunk的直接覆盖。所有的free的Chunk都被放到Free list链表（注意和extent的free list区分）
perm：永久型Chunk，和free对应，这种Chunk不会被释放，所占内存在数据库运行期间一直持有，Oracle很多内部内存结构，很多X$视图都是占用的此内存。
recr ：recreatable，可重建的Chunk，从字面意思上理解它是可以被重建的。

freeabl：freeable，可以理解为可空闲的，可释放的。

SQL> select ksmchcls,count(1) from  X$KSMSP group by ksmchcls;

KSMCHCLS   COUNT(1)
-------- ----------
recr         107556
freeabl      114676
R-freea         102
perm             48
R-free           51
free           3325

和shared pool相关的锁：

Libaray cache lock/pin
Cursor ：Pin S相关
Shared pool latch
Mutex相关

硬解析/软解析/软软解析

下面我们从一个SQL语句，select * from t1 where id=1。来分析其解析类型，假设这个SQL语句是第一次执行。

第一步，Oracle会对SQL进行语法检查，主要是确定SQL书写没有错误

第二步，语义检查及权限检查，检查涉及对象是否存在和是否存在相应权限

第三步，根据SQL文本生成一个hash值，hash值在Oracle中用sql_id体现，会根据hash值寻找在shared SQL area中是否存在，因为是第一次执行肯定不存在。

第四步，优化器会根据SQL文本，生成一组所有可能得表访问方式和表连接的执行计划

第五步，优化器参考统计信息、和数据库参数配置生成每种执行计划的成本（cost）,选出成本最低的执行计划。至此解析完成。

上面五个步骤，就是硬解析。

当第二次执行同样SQL时，假设这个SQL的执行计划没有在shared SQL area被换出，那么就只需要执行上面的前三步就可，第三部根据SQL的HASH值，匹配到内存中有响应的执行计划了，就直接开始下一阶段执行了，这就是软解析。

当同样的SQL执行三次时，会将SQL缓存入PGA,当第四次执行时，直接从PGA中获取执行计划的父游标，根据父游标的信息从shared SQL area中获取完整的执行计划，这个就是软软解析

硬解析的过程会导致大量的shared pool latch和少量的Mutex相关争用
软解析的过程会有少量shared pool latch和Mutex相关争用
软软解析一般不会有争用

绑定变量和共享游标

影响硬解析的两个方面，一个是SQL书写是否使用绑定变量，第二个就是参数cursor_sharing。

绑定变量用例如下：

select * from t1 where id=:b

参数cursor_sharing用法如下：

SQL>  show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

参数的三个可可选值：EXACT、FORCE和SIMILAR；EXACT是默认值。

EXACT：仅当 SQL 语句的文本完全一致（包括字面量）时，才视为同一语句并共享游标。执行计划基于实际字面量生成，稳定性高（如利用直方图、数据分布）；但相同结构的 SQL 因字面量不同会多次硬解析，增加 CPU 和共享池开销。

SELECT * FROM employees WHERE id = 1;  -- 游标 A
SELECT * FROM employees WHERE id = 2;  -- 游标 B（独立解析）

FORCE:强制将所有字面量替换为绑定变量（Bind Variables），使结构相同的 SQL 共享游标。减少硬解析，提升性能（尤其对重复但字面量不同的 SQL）；优化器在首次解析时使用绑定变量的初始值（“绑定窥探”），若后续数据分布差异大（如数据倾斜），可能导致执行计划不是最优的。

SIMILAR：已弃用，12c+无效，尝试将部分 SQL 转换为绑定变量，但对某些情况（如函数、索引提示）保留字面量。

总结：

当应用已合理使用绑定变量，或业务逻辑依赖字面量优化时，优先使用EXACT；无法修改应用的遗留系统，可以使用FORCE，但需监控执行计划变化。

自适应游标——ACS

Oracle 的自适应游标共享（Adaptive Cursor Sharing, ACS）是一种动态优化技术，旨在解决使用绑定变量时因数据分布不均导致的执行计划不稳定问题。它允许数据库根据实际执行情况自动调整 SQL 游标，生成更适合当前绑定变量值的执行计划，从而平衡性能与稳定性。

当使用绑定变量时，SQL 语句会被统一解析并生成一个执行计划，若不同绑定变量值对应的数据分布差异大（如某些 id 值返回少量行，另一些返回大量行），单一执行计划可能导致性能问题（如全表扫描 vs 索引查找）。于是Oracle 11g引出了自适应游标共享，即动态检测绑定变量值的执行效果，按需生成多个子游标（Child Cursors），每个子游标对应特定绑定变量值的优化计划，在运行时自动选择最优计划，提升性能。

工作原理：

当 SQL 首次执行时，优化器基于绑定变量的统计信息（如直方图）生成一个通用计划。若无直方图，默认假设数据分布均匀，可能选择不理想的计划。
ACS 通过 V$SQL 和 AWR 报告收集执行统计信息（如逻辑读、执行时间）。若检测到性能问题（如缓冲区获取率高、逻辑读过多），触发重新解析。
重新解析时，优化器检查绑定变量的实际值，并生成针对该值的优化计划。生成新的子游标，与原有游标共存。后续相同 SQL 但不同绑定变量值时，优先使用匹配的子游标。
子游标数量受 CURSOR_SHARING 和 OPTIMIZER_DYNAMIC_SAMPLING 影响。过多的子游标可能导致共享池压力，需通过参数 open_cursors 控制。

使用条件：

在 Oracle 11g 及更高版本中默认启用。
SQL 使用绑定变量，优化器模式为 ALL_ROWS 或 FIRST_ROWS，统计信息收集充分（如直方图）
依赖参数cursor_sharing=FORCE/SIMILAR（推荐 FORCE），optimizer_dynamic_sampling ≥ 2（动态采样以生成统计信息）。

实验

--创建测试数据
 create table test (id int,name varchar2(10));
 insert into test select id,substr(name,1,5) from tab1;
 commit;
 
 --新开一个会话
  sqlplus scott/oracle
 --当前游标共享为exact
SQL>  show parameter cursor_sharing;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
--获取当前会话sid
select  SYS_CONTEXT('USERENV', 'SID') AS current_sid from dual;
CURRENT_SID
--------------------------------------------------------------------------------
140
  --查看当前会话解析次数（需要再另一个会话执行）
SQL> SELECT NAME,VALUE 
  2   FROM V$SESSTAT A,V$STATNAME B 
  3   WHERE A.STATISTIC#=B.STATISTIC#
  4   and sid=140
  5   AND NAME in('parse count (total)','parse count (hard)','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                                2
parse count (total)                                                      16
parse count (hard)                                                        1

--parse count (hard) ： 硬解析；
--parse count (total)：硬解析+软解析+软软解析
--session cursor cache count  ： 缓存到PGA中的执行计划，可以看做软软解析
--执行查询
SQL> select * from  test where id=66;

        ID NAME
---------- ----------
        66 PHS3M
 --查看当前会话解析次数（需要再另一个会话执行）
  SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                                2
parse count (total)                                                      18
parse count (hard)                                                        2

--硬解析次数+1，总解析+2，是因为还有一次软解析
--再次执行查询
SQL> select * from  test where id=88;

        ID NAME
---------- ----------
        88 JAB4F
 --查看当前会话解析次数（需要再另一个会话执行）
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                                2
parse count (total)                                                      20
parse count (hard)                                                        3

---硬解析次数+1，总解析+2，，

修改cursor_sharing为force,查看解析情况

alter system set cursor_sharing=force;
exit
sqlplus scott/oracle
--清空共享池，生产慎用
 alter system flush shared_pool;
--查看cursor_sharing配置和当前会话id
SQL> show parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE

SQL>  select  SYS_CONTEXT('USERENV', 'SID') AS current_sid from dual;

CURRENT_SID
--------------------------------------------------------------------------------
140
--查看当前解析次数

SQL> SELECT NAME,VALUE 
  2   FROM V$SESSTAT A,V$STATNAME B 
  3   WHERE A.STATISTIC#=B.STATISTIC#
  4   and sid=140
  5   AND NAME in('parse count (total)','parse count (hard)','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                               49
parse count (total)                                                     139
parse count (hard)                                                       66

--执行查询

SQL>  select * from  test where id=100;

        ID NAME
---------- ----------
       100 1HQ80
       
 --查看解析次数
 SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                               49
parse count (total)                                                     143
parse count (hard)                                                       71
 
--硬解析加了5次。再次执行查询
SQL>  select * from  test where id=101;

        ID NAME
---------- ----------
       101 6JNET
       
--查看解析次数
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                               49
parse count (total)                                                     144
parse count (hard)                                                       71

--硬解析没有增加，软解析一次
--再次执行查询

SQL>  select * from  test where id=102;

        ID NAME
---------- ----------
       102 KS8NJ
 --查看解析次数
 SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                               49
parse count (total)                                                     145
parse count (hard)                                                       71
--软解析一次
--再次执行查询，这是第4次了
SQL>  select * from  test where id=103;

        ID NAME
---------- ---------
       103 C0AFG
SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache count                                               50
parse count (total)                                                     146
parse count (hard)                                                       71

--pga缓存执行计划+1，此时是软软解析