Version Count数目过多诊断的方法

 

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驻留在SGA的shared pool中，具体位于shared pool中的library cache。

 

对shared cursor而言，主要缓存的目的在于执行计划的共享。一个SQL经过validate和parse过程，会形成父游标和子游标的配对组合。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$sqlarea和v$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';