父子游标与version count

本文探讨了Oracle中SQL调优的重要性,特别是针对version count数目过多及子游标过多的问题。介绍了父子游标的概念,并通过示例展示了如何利用v$sql_shared_cursor视图诊断子游标不共享的原因。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

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 fromv$sqlareawhere sql_text like 'select /*+version_count */count(*)%';

 

SQL_ID       VERSION_COUNT

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

54fuganxkyky6            1

 

SQL> select sql_id, child_number fromv$sqlwhere 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子游标的原因。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值