SQL解析过程及执行计划失效的原因

[原文]
Parsing is the process of:

* Translating a SQL statement, verifying it to be a valid statement
* Performing data dictionary lookups to check table and column definitions
* Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
* Checking privileges to access referenced schema objects
* Determining the optimal execution plan for the statement
* Loading it into a shared SQL area
* Routing all or part of distributed statements to remote nodes that contain referenced data
[译文]
SQL的解析过程:

1. 翻译SQL语句, 检查语法是否合法.
2. 查找数据字典以确认表和字段的定义是否正确.
3. 得到所需对象的解析锁, 目的是在解析的过程中对象的定义不会发生变化.
4. 检查所引用的数据库对象的访问权限.
5. 决定语句的最佳执行计划.
6. 将它载入共享SQL区中.
7. 对分布式语句中包含的引用数据, 定位它们所在的远程节点.
[理解]
根据以上的解析过程, 推理以下操作会导致 cursor 失效, sql重新解析:

1. SQL所依赖的数据库对象的结构发生变动(ALTER TABLE/VIEW/FUNCTION),
 这个过程是传递的, 比如ALTER TABLE, 会导致依赖这个TABLE的VIEW失效,
 那么这时依赖这个VIEW的 cursor 也会相应失效.
2. SQL所依赖的数据库对象的权限发生变动(GRANT/REVOKE).
3. SQL所依赖的数据库对象的统计信息发生变动(ANALYZE/DBMS_STATS).
4. 刷新share pool(ALTER SYSTEM FLUSH SHARED_POOL).
5. SQL所依赖的DBLINK被删除.
[重新解析带来的问题]
当开启 bind value peeking 时, 解析的时候会重新 peeking.这个时候生成的执行计划不可控.

当即收集表的统计信息，让优化器从新对SQL作硬解析，期待可以恢复原来的执行计划。数据库若是是大表，收集统计信息的时间会比较长，并且执行计划变差通常伴随着CPU利用率高和IO繁忙，这个时间会更长；

有些DBA在收集统计信息时，没有使用no_invalidate=>false选项，即便收集了统计信息，执行计划却没有当即改变。由于该参数的默认值是AUTO_INVALIDATE，优化器会选择5个小时内的某个时间点来对SQL从新作硬解析。由于不了解这个参数，有人还会在收集完统计信息后flush shared_pool来强制对全部SQL作硬解析。性能

三、优化

有些SQL执行计划改变是跟统计信息没有关系的，即便从新收集了统计信息，执行计划仍是没法恢复正常。不少DBA习惯于使用coe_xfr_sql_profile.sql脚原本固定sql 执行计划，可是这个脚本操做起来比较麻烦，并且容易出错。这个脚本的正确用途是用来作不一样数据库之间sql执行计划的固定。

最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只须要输入几个参数，就能完成快速恢复执行计划的任务。

具体步骤以下：

一、用DBA权限的用户登陆sqlplus （不能是sys用户，能够是system用户）

二、执行脚本 SQL>coe_load_sql_profile.sql

三、输入第一个参数：须要恢复执行计划的sql_id

四、输入第二个参数：再输入一次相同的sql_id

五、此时会显示该sql_id对应的几个执行计划的plan_hash_value，第三个参数须要你选择最优执行计划对应的那个plan_hash_value

六、最后一步，输入链接sqlplus用户的密码，导出sql profile信息到一个表。若是不须要导出sql profile信息，最后一步exp操做能够从原脚本中屏蔽（注释掉以HOS exp开头那一行）。

下面是一个具体的实例截图（没有最后作exp导出输入密码的步骤）：

注：

 coe_load_sql_profile.sql 脚本能够从MOS网站下载的sqlt工具包里面获取，

默认隐含参数取值为18000s，也就是5小时。 5小时内失效这个时间可以改。

Oracle支持true、false和dbms_stats.auto_invalidate取值。如果取值为true，表示不进行游标失效动作，原有的shared cursor保持原有状态。如果取值为false，表示将统计量对象相关的所有cursor全部失效。如果设置为auto_invalidate，根据官方文档，Oracle自己决定shared cursor失效动作。

从10G开始，Oracle就将auto_invalidate作为默认的统计量收集行为。

SQL> select dbms_stats.get_param(pname => 'no_invalidate') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'N

--------------------------------------------------------------------------------

DBMS_STATS.AUTO_INVALIDATE

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVAL

-------------------------------------------------

DBMS_STATS.AUTO_INVALIDATE

默认隐含参数取值为18000s，也就是5小时。