Oracle遊標共享，父遊標和子游標的概念

Oracle設計shared pool記憶體的一個主要目的就是語句共享，透過語句共享，節省語句的解析時間，從而得到效能的提升。如果解釋shared pool記憶體，工作量太大，這裡只是做一個非常簡單的演示。

檢視語句共享可以藉助兩個資料字典：V$SQLAREA和V$SQL
V$SQLAREA：保留SQL語句的父遊標資訊，可以透過SQL_ID標識，其中的VERSION_COUNT列表示子游標的數量
V$SQL    ：保留SQL語句的子游標資訊，可以透過SQL_ID和CHILD_NUMBER標識

V$SQL_SHARED_CURSOR:語句產生子游標的原因

首先確認引數cursor_sharing,預設值是EXACT，也就是說只有在不使用繫結變數的情況下，語句要完全一樣才可以共享，包括大小寫、空格回車等所有都要一樣。
SQL> conn / as sysdba
Connected.
SQL> show parameter cursor_sharing
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

清空shared_pool記憶體，這個命令可以在實驗的時候使用，在生產系統庫要謹慎

SQL> alter system flush shared_pool;
System altered.

在SCOTT使用者下和TJ使用者下有一模一樣的表叫做demo，這是我準備的實驗場景，以下操作，淺色表示第一個視窗或者session，深色表示在另外一個視窗或session查詢動態效能檢視資訊驗證

在第一個視窗：
SQL> conn scott/tiger
Connected.
SQL> select * from demo;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH            1200
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975

SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

由於上邊這條語句是清空share pool後第一次執行，所以Oracle要做硬解析，生成遊標，確切來講是一個父遊標和一個子遊標，分別可以透過V$SQLAREA和V$SQL查到相關資訊

 

在第二個視窗：
[oracle@asm11g workshop]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 16 21:20:24 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> get qs.sql
  1  col sql_text for a50
  2  set linesize 120
  3  col exec for 999
  4  col invalid for 99
  5  col loads for 999
  6  select sql_text,
  7         sql_id,
  8         hash_value,
  9         executions exec,
 10         loads,
 11         invalidations invalid
 12  from v$sqlarea
 13* where sql_text like '&text%'
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    1     1       0

 

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          1     1

透過查詢我們可以看到，V$SQLAREA資料字典中的是父遊標的資訊，語句解析（LOADS）了一次，執行（EXEC）了一次，當然在V$SQL中也可以看到類似的資訊。

 

到第一個視窗：
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

再一次執行上一條語句，由於語句在share pool記憶體中已經有遊標存在，所以語句會共享。

 

到第二個視窗：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    2     1       0

 

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1

透過上面的查詢我們可以看到，語句解析（LOADS）了一次，執行（EXEC）了兩次，在子游標也可以看到相同的資訊。

 

到第一個視窗：
SQL> select empno,ename from demo where empno=7499;

     EMPNO ENAME
---------- ----------
      7499 ALLEN

這個語句和剛剛的語句的區別在於我把條件改成了7499，這樣這就是一個全新的語句，Oracle要做硬解析，並在記憶體中申請新的父子游標。

 

第二個視窗：

SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    2     1       0

 

到第一個視窗：
切換使用者到tj，tj使用者下也有相同的表demo，執行select empno,ename from demo where empno=7369;
SQL> conn tj/tj
Connected.
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

 

到第二個視窗：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    3     2       0

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1
dhdkpzyv9b1w7            1          1     1

 

雖然tj使用者下的語句和scott使用者下寫的語句一模一樣，但是語意顯然不一樣，兩個demo表屬於不同的使用者，所以我們看到，Oracle的父遊標解析(LOADS)加1，執行次數也加1，從父遊標的角度來講，語句是共享的，而在子游標中，有了區別，新生成了一個子遊標CHILD_NUMBER 為1，解析(LOADS)了一次，執行了一次。

 

到第一個視窗：

再次執行語句
SQL> select empno,ename from demo where empno=7369;

     EMPNO ENAME
---------- ----------
      7369 SMITH

 

到第二個視窗：
SQL> @qs
Enter value for text: select empno
old   9: where sql_text like '&text%'
new   9: where sql_text like 'select empno%'

SQL_TEXT                                           SQL_ID         HASH_VALUE EXEC LOADS INVALID
-------------------------------------------------- -------------  ---------- ---- ----- -------
select empno,ename from demo where empno=7499      0m3wzw5mrdg8z  1735834911    1     1       0
select empno,ename from demo where empno=7369      dhdkpzyv9b1w7  3063252871    4     2       0

SQL> select sql_id,child_number,executions,loads from v$sql where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER EXECUTIONS LOADS
------------- ------------ ---------- -----
dhdkpzyv9b1w7            0          2     1
dhdkpzyv9b1w7            1          2     1

我們看到語句的父遊標解析次數沒有增加，執行次數加1，而對於子游標來說，CHILD_NUMBER 為1的子游標執行次數加1。

 

產生子游標的原因很多，比如我上邊的使用者方案（SCHEMA）改變的例子，當然還有很多其他原因也可以導致子游標的產生，比如最佳化器模式的改變，或者繫結變數的窺視等，如果你想確定是由那種原因造成的，需要檢視v$sql_shared_cursor
對於剛才的例子，屬於驗證/事物檢查不匹配

SQL> select sql_id,CHILD_NUMBER,AUTH_CHECK_MISMATCH from v$sql_shared_cursor where sql_id='dhdkpzyv9b1w7';

SQL_ID        CHILD_NUMBER A
------------- ------------ -
dhdkpzyv9b1w7            0 N
dhdkpzyv9b1w7            1 Y