诊断Oracle high version count(高版本游标)问题

什么是high version cursor(高版本游标)?对于一个特定的游标有多少个版本就属于高版本游标是没有明确定义的.对于不同的系统有不同

什么是high version cursor(高版本游标)?
对于一个特定的游标有多少个版本就属于高版本游标是没有明确定义的.对于不同的系统有不同的数量界定.然而在awr报告中对于一个父游标超过20个子游标个数时就会被报告出来

然而当一个游标的版本数据达到成百上千,那么这些绝对是高版本游标.所以要检查这些sql有高版本的原因要尽量使用这些sql能够被共享.

什么是共享sql?
首先要记住的是所有sql语句都是式共享的.当一个sql语句被输入时,Oracle将会对一个语句的文本创建一个hash value,oracle将使用这个hash value很容易地在共享池中查找是否已经存在有相同hash value的sql存在.

例如:select count*) from emp语句有一个hash value为 4085390015
那么oracle就会对这个sql语句创建一个父游标和一个子游标.如果一个sql语句永远也不会被共享也没关系-当它第一次被解析时会创建一个父游标和一个子游标.可以简单地认为这个父游标代表这个hash value,子游标代表sql的元数据.

--------------------------------------分割线 --------------------------------------

Oracle PL/SQL基础 游标

Oracle数据库中游标的使用

PL/SQL中三种游标循环效率对比

Oracle高级显式游标的使用

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

--------------------------------------分割线 --------------------------------------

什么是元数据?
元数据是能让sql语句运行的所有信息.例如,在上面的例子中给定的emp表属于scott用户,因此它有一个object_id来指示这个emp表.当scott用户登录时,对于运行这个语句的会话优化器参数会被初始化,所以优化器的使用也属于元数据.

当scott用户重新登录后运行相同的命令(相同的sql语句),这时在共享池中已经存在相同的sql,(但是我们是不知道的),将对这个sql生成hash value并且在共享池搜索这个hash value.如果找到这个hash value,就会通过子游标来进行搜索来判断是否存在子游标可以被重用(元数据相同).如果是那么就可以共享这个sql语句.

现在在共享池中这个sql语句只有一个子游标,因为元数据相同能让我们使用已经存在的子游标来共享sql语句.父游标不是判断能不能共享的基础子游标才能决定是否共享.

现在如果另一个用户test也有一个emp表.如果这个用户也运行上面的查询语句将会发生什么:
1. 会对这个语句创建一个hash value.它的hash value为4085390015
2. 这个sql在共享池中被找到
3. 搜索子游标(在这时已经有一个子游标了)
4. 因为test用户的emp表的object_id与scott用户的emp表的object_id是不同的所以会有一个’mismatch’
(本质上这里会依次搜索子游标链表,使用所有的子游标与当前sql的元数据进行比较.如果已经搜索了100个子游标直到找到一个可以共享的子游标为止.如果没有找到可以共享的子游标那么就会重新创建一个子游标

5. 因此创建一个新的子游标所以现在有1个父游标和2个子游标.

为什么要关注high version cursor(高版本游标)
可以共享但没有被共享的sql和合成版本的sql是造成library cache竞争的主要原因.竞争会降低数据库的性能.在极端情况下会使用数据库hang住.当一个游标有太多个不必要的版本时,每次游标被执行时,这个解析引擎为了找到你所想要的游标不得不搜索整个游标链表.这是非常消耗CPU资源的.

怎样查看high version cursor(高版本游标)以及为何不能被共享
一种最简单查看high version cursor(高版本游标)的方法是使用脚本High SQL Version Counts - Script to determine reason(s) (文档 ID 438755.1)
现在这个脚本的版本为 version_rpt3_23.sql
下载这个脚本后需要进行安装
SQ>conn / as sysdba
SQL>@F:\ version_rpt3_23.sql
使用方法如下:
对于10g及以后的版本来收集version超过100的所有游标
SQL> set pages 2000 lines 100
SQL>
SQL> SELECT b.*
2 FROM v$sqlarea a ,
3 TABLE(version_rpt(a.sql_id)) b
4 WHERE loaded_versions >=100;

COLUMN_VALUE
---------------------------------------------------------------------

根据hash value来收集version超过100的所有游标
SQL> set pages 2000 lines 100
SQL> SELECT b.*
2 FROM v$sqlarea a ,
3 TABLE(version_rpt(NULL,a.hash_value)) b
4 WHERE loaded_versions>=100;

COLUMN_VALUE
---------------------------------------------------------------------

使用sql_id来收集游标报告
SQL> set pages 2000 lines 100
SQL> SELECT * FROM TABLE(version_rpt('g7vpupcuqd9zz'));

COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 22-4月 -14 15:44
RDBMS Version :10.2.0.4.0 Host: IBMP740-1 Instance 1 : RLZY
==================================================================
Addr: 070000066F6659E8 Hash_Value: 895920127 SQL_ID g7vpupcuqd9zz
Sharable_Mem: 135775 bytes Parses: 110924 Execs:621954
Stmt:
0 insert into mt_fee( hospital_id,serial_no,serial_fee,stat_type,f
1 ee_batch,medi_item_type,item_code,item_name,his_item_code,his_it
2 em_name,serial_apply,fee_date,model,factory,standard,unit,price,
3 dosage,money,reduce_money,usage_flag,usage_days,opp_serial_fee,i
4 nput_staff,input_man,input_date,calc_flag,frozen_flag,frozen_ser
5 ial_fee,trans_date,recipe_no,hos_serial,doctor_no,doctor_name,au
6 dit_flag,trans_flag,defray_type ) values ( :1,:2,:3,:4,:5,:6,:7,
7 :8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:2
8 4,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37)
9
Versions Summary
----------------
AUTH_CHECK_MISMATCH :1
BIND_MISMATCH :13
TRANSLATION_MISMATCH :1
ROLL_INVALID_MISMATCH :3
Total Versions:12
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = exact
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
0 8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for AUTH_CHECK_MISMATCH :
# of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME
========== =============== ================= ===================
8 211 211 INSUR_CHANGDE
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PREC
======== ======== =============== =============== ======== =============== =====
13 1 32 32 1 No (,)
13 2 32 32 1 No (,)
13 3 32 32 1 No (,)
13 4 32 32 1 No (,)
13 5 32 32 1 No (,)
13 6 32 32 1 No (,)
13 7 32 32 1 No (,)
13 8 32 128 1 Yes (,)
13 9 32 128 1 Yes (,)
13 10 32 128 1 Yes (,)
13 11 32 32 1 No (,)
13 12 11 11 180 No (,)
13 13 32 32 1 No (,)
13 14 32 128 1 Yes (,)
13 15 32 128 1 Yes (,)
13 16 32 128 1 Yes (,)
9 17 32 128 1 Yes (,)
4 17 22 22 2 No (,)
13 18 32 128 1 Yes (,)
13 19 32 32 1 No (,)
13 20 32 32 1 No (,)
13 21 32 32 1 No (,)
13 22 32 32 1 No (,)
13 23 32 32 1 No (,)
13 24 32 32 1 No (,)
13 25 32 32 1 No (,)
13 26 11 11 180 No (,)
13 27 32 32 1 No (,)
13 28 32 32 1 No (,)
13 29 32 32 1 No (,)
13 30 7 7 12 No (,)
13 31 32 32 1 No (,)
13 32 32 128 1 Yes (,)
13 33 32 32 1 No (,)
13 34 32 32 1 No (,)
13 35 32 32 1 No (,)
13 36 32 32 1 No (,)
13 37 32 32 1 No (,)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for TRANSLATION_MISMATCH :
No objects in the plans with same name and different owner were found.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for ROLL_INVALID_MISMATCH :
No details available
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 895920127, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

如果不能使用这个脚本可以使用下面的方法从基本视图中来查询相同的信息
下面使用scott用户来运行select count(*) from emp 语句,并运行下面的查询来查看这个语句的父游标和它的hash value和address
SQL>select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';