ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Version Count数目过多诊断的方法

Version Count数目过多诊断的方法

原创 Linux操作系统 作者:realkid4 时间:2012-05-15 23:08:01 0 删除 编辑

 

Oracle SQL调优是我们进行应用系统调优的一个重要方面。一个SQL的书写方式、执行计划和执行时间会显著的影响到系统的投产性能。本篇我们主要聊一下SQL version count数目过多,子游标过多的问题诊断。

 

1、关于version count

 

首先我们一起来回顾一下Oracle的“父子游标”(child cursor and parent cursor)概念。在Oracle中,任何一个执行的SQL语句,都会以游标cursor的形式进行处理。具体来说,一个SQL要涉及到两个游标对象,共享游标shared cursor和私有游标private cursor

 

Private cursor是驻留在Oracle Server Process PGA内部,只能被一个Server Process对应的会话使用。而shared cursor驻留在SGAshared pool中,具体位于shared pool中的library cache

 

shared cursor而言,主要缓存的目的在于执行计划的共享。一个SQL经过validateparse过程,会形成父游标和子游标的配对组合。SQL文本完全相同的语句,会共享父游标。而环境信息、对象信息相同的SQL语句,才可能共享子游标。当找不到对应的子游标或者父游标时,也就意味着找不到可共享的执行计划。这样对应的SQL就需要发生hard parse,重新生成执行计划。

 

一个父游标下对应的子游标个数,我们成为version count。每一个子游标对应一个执行计划对象。下面通过示例来演示,依然选择Oracle 11g环境进行试验。

 

 

SQL> select * from v$version;

BANNER

------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

 

 

构建数据表T

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

我们使用实验SQL,通过视图v$sqlareav$sql可以观察到library cache中的父子游标情况。v$sqlarea中保存父游标信息,而v$sql保存子游标信息。

 

 

SQL> select /*+version_count */count(*) from t where wner='SCOTT';

 

  COUNT(*)

----------

        14

 

SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';

 

SQL_ID        VERSION_COUNT

------------- -------------

54fuganxkyky6             1

 

SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';

 

SQL_ID        CHILD_NUMBER

------------- ------------

54fuganxkyky6            0

 

 

SQL语句(sql_id=54fuganxkyky6)对应一个父游标和一个子游标。

 

version count就表示当前父游标下对应子游标的个数。如果一个父游标对应的子游标version count过多,也就是对应了很多的子游标对象。这样,当server process检查可共享的游标时,就需要长时间的检索子游标列表。

 

最有名的version count过多问题是由于设置cursor_sharing参数为similar后,引发的version count错误。

 

此外,version count过多也是我们需要诊断SQL为什么不会共享的一个出发点。比较常用的有两种,本篇中进行详细介绍:

 

2v$sql_shared_cursor视图

 

SQL执行计划的生成,是受到很多因素影响的。相同父游标只是表示输入SQL的字面值相同。子游标对应的因素,如优化器类型、optimizer_mode、对应对象权限等的差异,都会影响到子游标的共享。

 

Oracle 11g中,一个新的视图被提供出来,用于帮助诊断子游标不共享问题的原因,就是v$sql_shared_cursor

 

 

 

SQL> desc v$sql_shared_cursor;

Name                          Type         Nullable Default Comments

----------------------------- ------------ -------- ------- --------

SQL_ID                        VARCHAR2(13) Y                        

ADDRESS                       RAW(4)       Y                        

CHILD_ADDRESS                 RAW(4)       Y                        

CHILD_NUMBER                  NUMBER       Y                         

UNBOUND_CURSOR                VARCHAR2(1)  Y                        

SQL_TYPE_MISMATCH             VARCHAR2(1)  Y                        

OPTIMIZER_MISMATCH            VARCHAR2(1)  Y                        

OUTLINE_MISMATCH              VARCHAR2(1)  Y                        

(篇幅原因,省略……)

PDML_ENV_MISMATCH             VARCHAR2(1)  Y                        

INST_DRTLD_MISMATCH           VARCHAR2(1)  Y                         

BIND_LENGTH_UPGRADEABLE       VARCHAR2(1)  Y                        

 

 

该视图是一个宽列视图,通过sql_idchild_number就可以定义某个特定子游标的信息。其他大部分列都是以varchar2(1)Y/N取值,每列的含义都是一个不能共享的理由。注意:这个理由N表示的是不能共享第一个子游标child_number=0的理由。下面通过简单的示例演示,继续上面的实验。

 

 

SQL> select sql_id, child_number from v$sql where sql_id='54fuganxkyky6';

 

SQL_ID        CHILD_NUMBER

------------- ------------

54fuganxkyky6            0

 

 

通过变换环境信息来生成新的子游标。

 

 

SQL> show parameter optimizer_mode

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode                       string      ALL_ROWS

 

--会话层面取值变化;

SQL> alter session set optimizer_mode=first_rows;

Session altered

 

SQL> select value from v$parameter where name='optimizer_mode';

 

VALUE

-----------------------------------------------------------------

FIRST_ROWS

 

 

重新执行SQL,检查执行计划。

 

 

SQL> select /*+version_count */count(*) from t where wner='SCOTT';

 

  COUNT(*)

----------

        14

 

SQL> select sql_id, version_count from v$sqlarea where sql_text like 'select /*+version_count */count(*)%';

 

SQL_ID        VERSION_COUNT

------------- -------------

54fuganxkyky6             2

 

SQL> select sql_id, child_number, OPTIMIZER_MODE from v$sql where sql_id='54fuganxkyky6';

 

SQL_ID        CHILD_NUMBER OPTIMIZER_MODE

------------- ------------ --------------

54fuganxkyky6            0 ALL_ROWS

54fuganxkyky6            1 FIRST_ROWS

 

 

生成了两个执行计划。此时,如果我们检查v$sql_shared_cursor,就可以发现非共享的原因。

 

 

SQL> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='54fuganxkyky6';

 

SQL_ID        CHILD_NUMBER OPTIMIZER_MODE_MISMATCH

------------- ------------ -----------------------

54fuganxkyky6            0 N

54fuganxkyky6            1 Y

 

 

在实际中,使用v$sql_shared_cursor就可以确定非共享SQL子游标的原因。

 

3version-rpt脚本

 

MOS438755.1中,Oracle提供了一个专门的脚本程序,用于协助诊断high SQL version count问题。

 

首先,我们需要从MOS上下载到脚本version_rpt.sql,目前笔者的版本为3.1.2。使用sys用户登录之后,调用该脚本创建相应的数据库对象。

 

 

SQL> @version_rpt3_12.sql

 

视图已创建。

函数已创建。

 

 

注意,在笔者的实验中,直接从MOS上下载的文件是不能正确编译通过的。要将下面片段进行修改:

 

 

--原有结构

SELECT COLUMN_NAME,0

            from cols

           where table_name='SQL_SHARED_CURSOR'

             and CHAR_LENGTH=1

          order by column_id;

 

--修改之后

           SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt

            from dba_tab_cols

           where table_name='SQL_SHARED_CURSOR'

             and CHAR_LENGTH=1

          order by column_id;

 

 

借助创建的函数version_rpt,可以详细分析version count问题。具体使用的方法有三个场景:

 

ü  列出version count大于某个阈值的报告,以sql_id方式显示

 

 

SQL> select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=4;

 

COLUMN_VALUE

----------------------------------------------------------------------------

Version Count Report Version 3.1.2 -- Today's Date 12-5 -12 15:20

RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson

==================================================================

Addr: 385D72E0  Hash_Value: 3393782897  SQL_ID 9p6bq1v54k13j

Sharable_Mem: 51266 bytes   Parses: 5

Stmt:

0 select value$ from sys.props$ where name = :1

Versions Summary

----------------

SQL_TYPE_MISMATCH :3

BIND_MISMATCH :1

Total Versions:3

~

Plan Hash Value Summary

-----------------------

Plan Hash Value Count

=============== =====

      415205717     4

 

COLUMN_VALUE

-----------------------------------------------------------------------

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for SQL_TYPE_MISMATCH :

No details available

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for BIND_MISMATCH :

Consolidated details for :

BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and

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 (PRECISION,SCALE)

======== ======== =============== =============== ======== ================

       4        1              32              32        1 (,)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL

IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE

=========== ================= ============= ============

          0                 0             0            4

####

 

COLUMN_VALUE

--------------------------------------------------------------------------------

To further debug Ask Oracle Support for the appropiate level LLL.

alter session set events

 'immediate trace name cursortrace address 3393782897, level LLL';

To turn it off do use address 1, level 2147483648

================================================================

 

46 rows selected

 

 

ü  列出version count大于某个阈值的报告,以sql_hash方式显示

 

 

SQL> select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=4;

 

COLUMN_VALUE

--------------------------------------------------------------------------------

Version Count Report Version 3.1.2 -- Today's Date 12-5 -12 15:22

RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson

==================================================================

Addr: 385D72E0  Hash_Value: 3393782897  SQL_ID 9p6bq1v54k13j

Sharable_Mem: 51266 bytes   Parses: 5

Stmt:

0 select value$ from sys.props$ where name = :1

Versions Summary

----------------

SQL_TYPE_MISMATCH :3

BIND_MISMATCH :1

Total Versions:3

~

Plan Hash Value Summary

-----------------------

Plan Hash Value Count

=============== =====

      415205717     4

 

(篇幅原因,省略……)

 

 

ü  列出某个特定sql_idversion count

 

 

SQL> select * from table(version_rpt('9p6bq1v54k13j'));

 

COLUMN_VALUE

--------------------------------------------------------------------------------

Version Count Report Version 3.1.2 -- Today's Date 12-5 -12 15:23

RDBMS Version :11.2.0.1.0 Host: oracle11g Instance 1 : wilson

==================================================================

Addr: 385D72E0  Hash_Value: 3393782897  SQL_ID 9p6bq1v54k13j

Sharable_Mem: 51266 bytes   Parses: 5

Stmt:

0 select value$ from sys.props$ where name = :1

Versions Summary

----------------

SQL_TYPE_MISMATCH :3

BIND_MISMATCH :1

Total Versions:3

 

 

三种方法,都可以很直观的帮助我们定位问题和错误。

 

4、结论

 

version count问题是Oracle SQL共享的一个方面。借助适当的工具手段,我们可以准确的定位问题,解决问题。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-729872/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7647142