首页 » ORACLE » SQL ordered by Version Count and Troubleshooting

SQL ordered by Version Count and Troubleshooting

在AWR 报告中有一个指标
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed

列出的是大于20个版本child cursor的sql,也可以使用下面的sql去v$sqlarea查询
SELECT sql_text,version_count,loaded_versions,executions,PARSE_CALLS,address,hash_value
FROM V$SQLAREA
where version_count>20
order by version_count desc;

version_count 表示这个父游标下子游标的数量,通常执行一条sql,加载到shared pool中,v$sqlarea是父游标,而v$sql反应的也就是子游标
loaded_versions表示这个父游标在cache中的已加载context heap的子游标数量对应的是v$sql中loaded_versions=1的数量。对应V$SQL_SHARED_CURSOR

v$sql
users_openng表示当前child cursor被任何用户打开的总数,对应的是v$open_cursor中的数量
loads 表示对象被加载或挤出shared pool中重新加载回来的次数
还有last_load_time,last_active_time,first_load_time都有可以参考

如果一个相同的sql字有太多的子游标,就会增加library cache latch竞争。

下面用一个例子来说明问题db_version 10204

SQL> SELECT version_count,loaded_versions,executions,PARSE_CALLS,address,hash_value,sql_id
  2  FROM V$SQLAREA 
  3  where version_count>100
  4  order by version_count desc;

VERSION_COUNT LOADED_VERSIONS EXECUTIONS PARSE_CALLS ADDRESS          HASH_VALUE SQL_ID
------------- --------------- ---------- ----------- ---------------- ---------- -------------
          147              19      13780       13782 0000000249DC8A60  835964168 fhnpmnnsx7m88
          104               2        193         192 0000000248FE13A8 1769035544 0d917t1nr2pss


SQL> select sql_text from v$sqlarea where sql_id='fhnpmnnsx7m88'
----------------------------------------------------------------------------------------
insert into ICME_NOPROJECT_SCORE (ACTIVITY_TYPE_ID, FROM_ORG_ID, SCORE_TYPE_ID, GIVE_SCORE_ORG_ID, IC_CODE, SPECIALTY_ID, TO_ORG_ID, CLASS_HOUR, CLASS_SCORE, 

SCORE_ACTIVITY_DATE, SCORE_ACTIVITY_CONTENT, TRAIN_PLACE, VERIFY_CODE, SCORE_REMARK, SCORE_CHECK_FLAG, ADMIN_ID, ORG_ID, IS_VALID, SOURCE_FLAG, CREATE_TIME, UPDATE_TIME, 

CERTIFICATE_CODE, score_holder, SCORE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24)

可以看到sql使用了绑定变量,而且是一条简单的insert sql
cause:
bug,related to name resolution issues, fine grained access control, cursor invalidation, bind buffer length or any of the other common structural or coding reasons for multiple child cursors

Technical background:
The database does adjust the length of CHAR bind buffers to preset sizes. There are four steps: 32, 128, 2000 and 4000 bytes. So if we are execute a statement with a bind value

of 10 bytes,the buffer will be 32 bytes. Do we re-execute it with 52 bytes in the bind variable, the previously created child cursor cannot be reused, and will be recreated with

a bind buffer of 128 bytes. The system view v$sql_shared_cursor indicates this invalidated child cursor as BIND_LENGTH_UPGRADEABLE.

SQL> desc icme.icme_noproject_score;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SCORE_ID                                              NOT NULL NUMBER(38)
 IC_CODE                                               NOT NULL VARCHAR2(9)
 CLASS_HOUR                                            NOT NULL NUMBER(8,2)
 CLASS_SCORE                                           NOT NULL NUMBER(8,2)
 SCORE_ACTIVITY_DATE                                   NOT NULL DATE
 SCORE_ACTIVITY_CONTENT                                         VARCHAR2(500)
 SCORE_REMARK                                                   VARCHAR2(500)
 GIVE_SCORE_ORG_ID                                     NOT NULL NUMBER(38)
 FROM_ORG_ID                                           NOT NULL NUMBER(38)
 SCORE_CHECK_FLAG                                      NOT NULL NUMBER(38)
 SCORE_TYPE_ID                                         NOT NULL NUMBER(38)
 ACTIVITY_TYPE_ID                                      NOT NULL NUMBER(38)
 SPECIALTY_ID                                          NOT NULL NUMBER(38)
 ADMIN_ID                                              NOT NULL VARCHAR2(50)
 ORG_ID                                                NOT NULL NUMBER(38)
 IS_VALID                                              NOT NULL NUMBER(38)
 CREATE_TIME                                           NOT NULL DATE
 UPDATE_TIME                                           NOT NULL DATE
 TO_ORG_ID                                             NOT NULL NUMBER(38)
 TRAIN_PLACE                                                    VARCHAR2(100)
 VERIFY_CODE                                                    VARCHAR2(100)
 CERTIFICATE_CODE                                               VARCHAR2(100)
 SOURCE_FLAG                                           NOT NULL NUMBER(1)
 SCORE_HOLDER                                                   VARCHAR2(100)
 COURSE_ID                                                      VARCHAR2(20)

每个字符类型的绑定变量有4种类型,表上有9个varchar2类型,如果开发的在声明绑定变量时长度不一,理论上就有可能产生power(4,9),262144个child cursor,幸运的是我们当最大时也就不到150,平

时loaded_count也小于20,这个虽然对于一个executions 1.3W 次并不多,但也应该重视。一个游标有多个版本确实不是什么好事,因为所有的version都会转换为libary cache latch,在执行你的sql查

找正确version,你就会holding latch并阻止其它用户,随着version的增长等待的时间就会越来越长,带来的性能问题就是latch contention for the library cache latches ,这也是10gr2推出mutex 的原因。

通过v$sql_bind_metadata可以查看每个绑定变量的长度范围,比如绑定变更:11就在两种长度区间中。

SQL> select  m.position,m.bind_name , m.max_length,count(*) child_cursor_count
  2  from v$sql s, v$sql_bind_metadata m
  3  where s.sql_id =  'fhnpmnnsx7m88'
  4  and s.child_address = m.address group by m.position,m.bind_name , m.max_length
  5  order by 1, 2;

  POSITION BIND_NAME                      MAX_LENGTH CHILD_CURSOR_COUNT
---------- ------------------------------ ---------- ------------------
         1 1                                      22                  6
         2 2                                      22                  6
         3 3                                      22                  6
         4 4                                      22                  6
         5 5                                      32                  6
         6 6                                      22                  6
         7 7                                      22                  6
         8 8                                      22                  6
         9 9                                      22                  6
        10 10                                     11                  6
        11 11                                    128                  3
        11 11                                   2000                  3
        12 12                                     32                  6
        13 13                                     32                  1
        13 13                                    128                  5
        14 14                                     32                  1
        14 14                                    128                  1
        14 14                                   2000                  4
        15 15                                     22                  6
        16 16                                     32                  6
        17 17                                     22                  6
        18 18                                     22                  6
        19 19                                     22                  6
        20 20                                     11                  6
        21 21                                     11                  6
        22 22                                     32                  5
        22 22                                    128                  1
        23 23                                     32                  4
        23 23                                    128                  2
        24 24                                     22                  6

30 rows selected.

可以去V$SQL_SHARED_CURSOR查询什么没有共享子游标的原因
SQL> select * from V$SQL_SHARED_CURSOR where sql_id=’fhnpmnnsx7m88′;
发现全是BIND_MISMATCH 值为Y

Metalink’s node ID 10157392.8 Bug 10157392 12.1 fixed

* High version counts are seen on cursors with binds
* The reason given in v$sql_shared_cursor is a bind mismatch
* The problem happens when many sessions are trying to execute the same cursor and there’s evidence that the child cursors are being reloaded back into the shared pool.
(See v$librarycache).

临时的解决办法是加大shared_pool size,减少cursor 被flush出去的机率,或使用dbms_shared_pool包keep 方法,减少cursor老化被替换出去。

exec dbms_shared_pool.keep(‘&address,&hash_value’,’C’)

keep以后的对象去v$db_object_cache查询,条件是kept=’YES’,相反用unkeep

SELECT * FROM v$db_object_cache where name like ‘insert into ICME_NOPROJECT_SCORE%’

dbms_shared_pool.purge是指定单条sql的cursor从library cache 中清除参考官方文档

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#sthref7227

DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT ‘P’,
heaps NUMBER DEFAULT 1)

name
Name of the object to purge.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.
Currently, TABLE and VIEW objects may not be purged.
flag
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to ‘P’ or ‘p’ to fully specify that the input is the name of a package/procedure/function.
Set to ‘T’ or ‘t’ to specify that the input is the name of a type.
Set to ‘R’ or ‘r’ to specify that the input is the name of a trigger.
Set to ‘Q’ or ‘q’ to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except ‘P’ or ‘p’ or ‘Q’ or ‘q’ or ‘R’ or ‘r’ or ‘T’ or ‘t’.
heaps
Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged

purge sql curosr使用如下
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’,65);
or
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’,64); –only executions plan
or
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’,1);
or
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’);
or
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168’,’z’);

note: 10r2中使用dbms_shared_pool.purge中存在bug,要使用5314566 enven 或者应用5614566补丁,如还还是清理不掉,就要自己排查当前cursor是否被其它session cache or open from v$open_cursor.

SQL> alter session set events ‘5614566 trace name context forever’;
SQL> exec dbms_shared_pool.purge(‘0000000249DC8A60,835964168′,’c’);

打赏

对不起,这篇文章暂时关闭评论。