Oracle中的解析

每当一个语句被执行的时候，Oracle要遵循一套方法来检查语句的语法、其中引用对象的有效性以及用户的权限。除此之外，Oracle还会检查是否已经执行了相同的语句，目的是减少处理负担。所有这些都在几分之一秒甚至更短的时间内完成，发出语句的用户毫不知晓。这个过程被称为解析。

 

解析的类型

所有的语句，DDL或者DML在执行的时候都要被解析。关键是这个解析是软解析（语句已经被解析并且在内存中可用）还是硬解析（所有的解析步骤都要被执行）。软解析将会显著的提升系统性能，而硬解析则会严重影响系统性能。减少硬解析会提升资源的利用和优化SQL代码。

 

解析过程

Oracle在内部会遵循下面的步骤从而产生SQL语句的输出。

语法检查：检查语句的语法。
语义检查：检查语句中引用的对象的有效性以及发出语句的用户的权限。这是一个数据字典检查。
为语句在内存中分配私有的SQL区域。
产生语句的已解析形式并且分配共享SQL区域，这涉及到为语句找到最优的执行路径。
在第四点中，Oracle会首先检查内存中是否存在已解析的相同的语句，一旦发现，已解析形式将会被选择并立即执行（软解析）。如果没有发现，产生语句的解析形式并且存放到共享SQL区域（SGA中共享池的一部分），然后执行该语句（硬解析）。这一步设计到语句的优化，它将决定性能。

 

相同的语句

Oracle会执行下面的步骤来发现相同的语句，从而决定是执行软解析还是硬解析。

1.当一个新语句发出时，会为它的文本字符串产生一个hash值，Oracle检查这个新的hash值是否和共享池中已有的hash值匹配。
2.然后，新语句的文本会和hash值匹配的语句做比较，这包括大小写，空格和注释的比较。
3.如果发现了匹配的语句，新语句中的引用对象会和匹配语句中的对象做比较。属于不同模式的相同表将被认为是不匹配的。
4.新语句中的绑定变量和被比较的语句中的绑定变量应该具有相同的类型。

5.如果满足上面的所有条件，Oracle会重用已存在的解析。如果没有发现匹配的语句，Oracle将完成解析过程并把它放到共享池中。

减少硬解析

当发生争用的时候，共享池内存会增加，我们应该在代码级别解决这个问题。下面是减少硬解析的一些建议。

1.

在语句中使用绑定变量而不是硬编码值。
2.

编写可以在不同地方使用的通用程序，这同样会减少代码重复。
3.

尽管你进行了严格的检查，也会偶然出现相同的语句以不同的格式书写的情况。定期的查询SQL区域来检查相似但独立解析的查询。把这些语句改成相同的格式或者放到通用程序中，以使该语句的多次调用只进行一次解析。 在系统级识别不必要的解析

select parse_calls, executions,   
    substr(sql_text, 1, 300)   
from v$sqlarea   
where command_type in (2, 3, 6, 7);  

检查被多次执行的语句，如果发现上面语句中的PARSE_CALLS的值和EXECUTIONS的值很接近就说明情况很糟糕。上面的查询只针对DML语句（检查其他类型的语句可以使用适当的command type number），也会忽略递归调用（数据字典访问），因为它是Oracle的内部调用。 在会话级识别不必要的解析调用

select b.sid, a.name, b.value   
from v$sesstat b, v$statname a   
where a.name in ('parse count (hard)', 'execute count')   
and b.statistic# = a.statistic#   
order by sid;  

识别出包含大量重解析的会话（VALUE列）。从V$SESSION中查询这些会话，然后定位出造成这么多次解析的正在执行的程序。

select a.parse_calls, a.executions, substr(a.sql_text, 1, 300)  
from   v$sqlarea a, v$session b  
where  b.schema# = a.parsing_schema_id  
and    b.sid = <:sid>  
order  by 1 desc;  

上面的查询也会显示Oracle内部发出的递归SQL调用。

4.

为一个会话分配足够容纳所有SQL语句的私有SQL区域。根据需要，参数OPEN_CURSORS可能要被重设为一个更高的值。把SESSION_CACHED_CURSORS设置成更高的值以使在会话级缓存更多的cursor并且避免重新解析。  识别一个会话打开了多少cursor

select a.username, a.sid, b.value  
from   v$session a, v$sesstat b, v$statname c  
where  b.sid = a.sid  
and    c.statistic# = b.statistic#  
and    c.name = 'opened cursors current' 
order  by 3 desc;  

VALUE列表明一个会话中打开了多少cursor。比较它和OPEN_CURSORS参数值之间的差距，如果差距太小，考虑增加OPEN_CURSORS参数。  评估缓存的cursor和解析次数之间的比较

select a.sid, a.value parse_cnt,   
       (select x.value  
        from   v$sesstat x, v$statname y  
        where  x.sid = a.sid  
        and    y.statistic# = x.statistic#  
        and    y.name = 'session cursor cache hits') cache_cnt  
from   v$sesstat a, v$statname b  
where  b.statistic# = a.statistic#  
and    b.name = 'parse count (total)' 
and    value > 0;  

应该把一个会话的CACHE_CNT('session cursor cache hits')和PARSE_CNT('parse count(total)')做比较，如果差别很大，考虑增加SESSION_CACHED_CURSORS参数的值。 下面和解析相关的信息可以通过v$SYSSTAT和V$SESSTAT视图查询

SQL> select * from v$statname where name like '%parse%';  
 
STATISTIC# NAME                           CLASS  
---------- ------------------------- ----------  
       217 parse time cpu                    64  
       218 parse time elapsed                64  
       219 parse count (total)               64  
       220 parse count (hard)                64  
       221 parse count (failures)            64  

通过设置参数CURSOR_SHARING为FORCE可以进一步利用共享SQL区域，不止包含相同的SQL，还包含相似的查询。
6.

要防止很大的SQL或者PL/SQL在共享池中过期，过期基于最近最少使用机制而发生。把参数SHARED_POOL_RESERVED_SIZE设置为一个较大的值以防止大package过期，因为重载大的package会产生很大的负担。
7.

通过使用DBMS_SHARED_POOL包在内存中保持被频繁使用的对象，这个包默认情况下会被创建，也可以通过执行DBMSPOOL.SQL 脚本显式的创建它，该脚本在内部会调用PRVTPOOL.PLB 脚本。使用它在实例启动后保持最频繁使用的对象，包括过程、函数、包和触发器。实例启动后保持对象将避免内存碎片。  查看被频繁使用和重载的对象列表：

select loads, executions, substr(owner, 1, 15) "Owner",   
       substr(namespace, 1, 20) "Type", substr(name, 1, 100) "Text"   
from v$db_object_cache   
order by executions desc;  

在内存中保持一个包
SQL>exec dbms_shared_pool.keep('standard', 'p'); 

 

查看被保持的对象的列表select substr(owner, 1, 15) "Owner",   
       substr(namespace, 1, 20) "Type",   
       substr(name, 1, 100) "Text"   
from v$db_object_cache   
where kept = 'YES';  

8.

增加共享池的大小是一个即时的解决方案，但是应该先执行上述步骤从而使数据库的长期运行得到优化。可以通过在初始化文件中设置SHARED_POOL_SIZE参数增加共享池的大小。

总结

 
5.

尽量减少硬解析的次数！可以通过编写可被多个地方调用的通用程序实现。

 

=================================================

下面简化的说一下一下SQL的执行过程，以说明SHARED POOL LATCHES和LIBRARY CACHE LATCH在SQL解析过程中所起的作用。

 

1. 首先需要获得LIBRARY CACHE LATCH，根据SQL的HASH_VALUE值在LIBRARY CACHE中寻找是否存在可共享的代码。如果找到则为软解析，SERVER进程获得该SQL执行计划，转向第四步；如果找不到共享代码则执行硬解析。
2. 释放LIBRARY CACHE LATCH，获取SHARED POOL LATCH，查找并锁定自由空间。
3. 释放SHARED POOL LATCH，重新获得LIBRARY CACHE LATCH，将SQL及执行计划插入到LIBRARY CACHE中。
4. 释放LIBRARY CACHE LATCH，保持NULL模式的LIBRARY CACHE PIN/LOCK。
5. 开始执行。