OracleSQL语句高版本分析-创新互联

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$sqlgroupby sql_idorderby2desc;

version_count对应子游标数：

select sql_id,sql_text,executions,version_countfromgv$sqlareawhere sql_id='6hk042t0trr1t';

selectsql_id,child_number,sql_text,optimizer_mode,plan_hash_valuefrom gv$sqlwhere sql_id='6hk042t0trr1t';

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

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

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

selectposition,LAST_CAPTURED,datatype_string,value_stringfromgv$sql_bind_capturewhere sql_id='6hk042t0trr1t';

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

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

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。