Oralce Find Not Binded Sql

分析oracle硬解析(Hard Parse)&软解析(Soft Parse)&块软解析(fast soft parse) (转载)
http://junfengwang060905.blog.163.com/blog/static/94223330201022564111107/

Hard parse 解析步骤:

1.检查语法

2.检查sql语句涉及object是否存在,不存在则推出,检查需要数据字典 加载row cache

3.转换对象名,(例如翻译synonym 或实际名 test=xh.test),有 问题退出parse

4.检查发出sql的 user是否有对应权限,权限不足退出parse

5.根据object的 统计信息optimizer会建立一个 执行计划

6.将产生的执行计划装入shared pool 中library cache 中的heap

这六步完全执行就是hard parse

  • 用户发一条语句 (例如select * from test)。
  • oracle将这条语句转为acsII数值,并进行利用 hash 函数进行 hash运算。
  • 需要传入hash函数 2个参数(name,namespace)name 就是sql语句,namespace就是 'sqlarea' 对于SQL语句,计算出hash value(表示该语句被分派到library cache中 此hash bucket中)。
  • 然后到library cache 中 对应的hash bucket中比较下 该bucket里是否存在该语句(是否是第一次运行)
  • 这个计算hash value及其搜索library cache找是否以前执行过
  • 存在该语句(即可共享用)需要持有library cache latch(闩latch是轻量级的串行化设备,用于协调对共享数据结构、对象和文件的多用户访问),
  • 找到了为soft parse,执行SQL语句的serverprocess利用找到以前执行过的sql语句跳过解析步骤中的几步并获取该SQL的执行计划
  • 释放library cache latch 然后开始执行sql,
  • 没找到则需要hard parse 释放library cache latch 获得shared pool latch(需要将SQL语句,执行计划写入 shared pool中library cache)
  • 查找锁定 shared pool 中free space ,释放shared pool latch(主要控制shard pool中空间分配和回收)获得library cache latch(保护cache在内存中的sql及其执行计划,要向library cache插入新sql及其执行计划时 需要library cache latch)开始解析 
  • 然后将sql及执行计划插入library cache,释放library cache latch 执行sql 
  • 整个硬解析过程oracle会持有library cache(TOM:硬解析一个查询时,数据库会更长时间地占用一种低级串行化设备 latch) 
  • 所以hard parse非常影响latch 会造成长时间持有造成latch 争用

soft parse:跳过解析步骤中的几步

但Soft parse最后必须要使用共享的sql执行计划,如果产生新执行计划那么 就是hard parse

10g session_cached_cursors

SQL> show parameter session_cache
 
NAME                                 TYPE        VALUE
 
------------------------------------ ----------- ------------------------------
 
session_cached_cursors               integer     30

10G 这个参数default 为20 ,9I 为0

多了这个后oracle 执行不步骤就变了

在算出hash value后 oracle 会查找 该session的PGA有的话直接执行 避开所有parse

SQL语句的处理过程修正(参考 http://www.itpub.net/thread-877110-1-5.html 讨论 帖)

对照metalink给出的这个示意图,我们可以对SQL的处理过程作如下的描述:

1、检查是否有打开的游标,如果有,则直接通过游标link到位于PGA的private SQL AREA( private SQL area),转步骤11。否则,执行步骤2。

2、检查初始化参数SESSION_CACHED_CURSORS是否被设置,如果被设置,则同样可以通过游标指向到位于PGA的私有SQL AREA,转步骤11。否则执行步骤3。

3、检查HOLD_CURSOR以及RELEASE_CURSOR的设置。如果RELEASE_CURSOR=no(默认no),HOLD_CURSOR=yes(默认为no),当ORACLE执行完SQL语句,为private SQL AREA分配的内存

空间被保留,cursor和private SQL AREA之间的link也被保留,预编译程序不再使用它,同样可以通过这个指针直接在private SQL AREA获得语句,转步骤11。

这上面的三种情况,实际上都没有作任何parse,都是直接从位于PGA中的private SQL AREA获得语句并直接执行。此为fast parse。

这三种情况都不存在的情况下,oracle转到步骤4执行。

4、创建一个游标。

5、语法检查Syntax Check:检查语法书写是否正确,是否符合SQL Reference Manual中给出的SQL语法。

6、语义分析Semantic Analysis:查找数据字典,检查表、列是否正确,在所要求的对象上获取语法分析锁,使得在语句的语法分析过程中不改变这些对象的定义, 验证为存取所涉及的模式对象所需的权限是否满足。

7、将语句转化成ASCII等效数字码,再通过散列算法得到散列值。

8、检查库缓存中是否存在同样hash值的语句。如果存在,转步骤11。否则,执行步骤9。 这就是soft parse。

9、选择执行计划。从可用的执行计划中选择一个最优的执行计划,其中包括存储大纲(srored outline)或物化视图(materialized view)相关的决定。

10、生成该语句的一个编译代码(p-code)。

11、执行语句。

find not binded sql

WITH t AS
(
SELECT 
 regexp_replace(
  regexp_replace(
   regexp_replace(
    sql_text, 
    ' {1,}', ' '               -- replace multiple space with single
   ),
   '''[^'']{1,}''', '''@'''    -- replace string with '@'
  ),
  '[0-9]{0,}[.]{0,}[0-9]{1,}', '@'             -- replace digit with @
 ) AS bind_sql_text
FROM   v$sql s
WHERE  s.executions = 1
)
-- split
SELECT SYSDATE AS analyze_date, bind_sql_text, COUNT(*) executions
FROM   t
GROUP  BY bind_sql_text
HAVING COUNT(*) > 300;