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';