Oracle SQL 语句高版本分析

OracleSQL 语句高版本分析

1.     何为高版本

每次执行一条SQL语句时，如果其对应的当前已经存在于library cache里的一个父游标下的各个子游标都不能被该SQL语句重新使用（即共享），则会产生一个新的子游标，此时就会在V$SQL_SHARED_CURSOR里新增一行，分别描述该SQL语句不能使用当前已经存在的各个子游标的原因。当然一个父游标下的第一个子游标产生时也会在该视图上新增一行，只是各个该视图上的描述原因的列的值都为N，在V$SQL_SHARED_CURSOR行数就是此SQL语句执行的版本数，也就种类数。

2.     怎样才会产生高版本

    A.       ORACLE使用子游标去区分一个不能被共享的SQL，因为虽然SQL相同，但是SQL所指向的对象使不同的。也就是说，这些SQL的父游标都是一样的，HASH_VALUE值都相同。例如，数据库有三个表T，有这样一个语句，select *from T,由于每个T都被不同的对象使用或是用户使用，而在数据库级别，这些语句都是一样的，HASH_VALUE都相同，但是他们的子游标就不同了，这就会产生High Version Counts，由于HASH_VALUE相同，持有LATCH会不放，所以当PARSE的时候就会产生LATCH FREE。这是产生High Version的一个方面；

  B.       对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是BIND_MISMATCH，此时会产生一个子CURSOR，同时分配大于32字节的BIND BUFFER；

  C.       对于NULL值，因为oracle对NULL 这个值表示什么都不是，如果变量为NULL则也会新一个子游标。

3.     有何后果

发现Oracle因为某些原因不可重用这些SQL。当这类SQL执行次数很多,就会占用大量的shared pool,引起library cache pin和library cache 的等待事件，严重引起主机挂起。

4.     解决方法

A.  数据库升级

l  版本升级( 11.2.0.4)升级包，但分险较大，周期长，需要测试验证，搭建测试环境，需要更新文件有以下文件

p13390677_112040_Linux-x86-64_1of7.zip

p13390677_112040_Linux-x86-64_2of7.zip

p13390677_112040_Linux-x86-64_3of7.zip

• 定时清 ALTERSYSTEM FLUSH SHARED_POOL;

B.  程序修改方法

l 程序对字符变量设定固定大小

l 对NULL值特别处理，使用不同的InSert 语句

l Insert 操作时防止同一主键同时插入。

1. 5.     实例分析

select sql_id,count(0) from gv$sql groupby sql_id orderby2desc;

version_count对应子游标数：

select sql_id,sql_text,executions,version_count fromgv$sqlarea where sql_id='6hk042t0trr1t';

selectsql_id,child_number,sql_text,optimizer_mode,plan_hash_value from gv$sql where sql_id='6hk042t0trr1t';

查子游标不能共享（失效的原因，如optimizer_mode_mismatch,BIND_MISMATCH等原因）的原因：

selectchild_number,optimizer_mode_mismatch,BIND_MISMATCH  from gv$sql_shared_cursor where sql_id='6hk042t0trr1t'orderby child_number;

绑定失效的时候，查看一下每次绑定变量的值：

selectposition,LAST_CAPTURED,datatype_string,value_string fromgv$sql_bind_capture where sql_id='6hk042t0trr1t';

绑定变量字段长度变化的情况：

select * from dba_hist_sqlbind where sql_id='6hk042t0trr1t';