『ORACLE』session_cached_cursor了解硬解析、软解析（11g）

用示例演示一次硬分析（hard parse)和一次软分析（soft  parse），以及一次更软的分析（softer soft parse)，并对给出演示结果。

SYS@ORA11GR2>show parameter session_cached

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
SYS@ORA11GR2>
SYS@ORA11GR2>alter system set session_cached_cursors=0 scope=spfile;

System altered.

SYS@ORA11GR2>startup force;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             339741056 bytes
Database Buffers           75497472 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0
SYS@ORA11GR2>

--执行一条SQL
SYS@ORA11GR2>select count(*) from scott.emp;

  COUNT(*)
----------
        14

SYS@ORA11GR2>
--我们可以看到，此时由于这个SQL是第一次执行，所以，我们可以看到这个SQL解析了一次，并且是硬解析
SYS@ORA11GR2>select sql_text,parse_calls,loads from v$sql where sql_text ='select count(*) from scott.emp';

SQL_TEXT                       PARSE_CALLS      LOADS
------------------------------ ----------- ----------
select count(*) from scott.emp             3          1

SYS@ORA11GR2>
--再次执行相同的SQL
SYS@ORA11GR2>select count(*) from scott.t;

  COUNT(*)
----------
        14

SYS@ORA11GR2>
--我们可以看到，LOADS没变，也就是没有发生硬解析，PARSE_CALLS为2次，也就是解析了2次，其中第二次就为软解析
SYS@ORA11GR2>select sql_text,parse_calls,loads from v$sql where sql_text ='select count(*) from scott.t';

SQL_TEXT                       PARSE_CALLS      LOADS
------------------------------ ----------- ----------
select count(*) from scott.emp;             2          1

SYS@ORA11GR2>


SYS@ORA11GR2>select count(*) from scott.emp;

  COUNT(*)
----------
        14

SYS@ORA11GR2>select sql_text,parse_calls,loads from v$sql where sql_text ='select count(*) from scott.emp';

SQL_TEXT                       PARSE_CALLS      LOADS
------------------------------ ----------- ----------
select count(*) from scott.t             3          1

SYS@ORA11GR2>

--softer soft parse
--Oracle11g，参数session_cached_cursor的默认值为50
SYS@ORA11GR2>show parameter session_cached_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
SYS@ORA11GR2>
--session 1:
SCOTT@ORA11GR2>select userenv('sid') from dual;

USERENV('SID')
--------------
           191

SCOTT@ORA11GR2>
--session 2:
SYS@ORA11GR2>select a.name,b.value from v$sesstat b, v$statname a where a.statistic#=b.statistic# and a.name in ('parse count (total)','parse count (hard)','session cursor cache count','session cursor cache hits','opened cursors current') and b.sid=191;

NAME                                     VALUE
----------------------------------- ----------
opened cursors current                      10
session cursor cache hits                 3552
session cursor cache count                  38
parse count (total)                       1130
parse count (hard)                         587

SYS@ORA11GR2>
--session 1:第一次执行SQL
SCOTT@ORA11GR2>select count(*) from scott.dept;

  COUNT(*)
----------
         4

SCOTT@ORA11GR2>
--session 2:此时session cursor cache count已由38变为40，说明，已将SQL对应的session cursor已缓存，并且硬解析了2次（存疑）

SYS@ORA11GR2>select a.name,b.value from v$sesstat b, v$statname a where a.statistic#=b.statistic# and a.name in ('parse count (total)','parse count (hard)','session cursor cache count','session cursor cache hits','opened cursors current') and b.sid=191;

NAME                                     VALUE
----------------------------------- ----------
opened cursors current                      10
session cursor cache hits                 3564
session cursor cache count                  40
parse count (total)                       1136
parse count (hard)                         589

SYS@ORA11GR2>
--session 1:第二次执行SQL
SCOTT@ORA11GR2>select count(*) from scott.dept;

  COUNT(*)
----------
         4

SCOTT@ORA11GR2>
--session 2:此时session cursor cache hits多了3次，解析次数也增加了4次
SYS@ORA11GR2>select a.name,b.value from v$sesstat b, v$statname a where a.statistic#=b.statistic# and a.name in ('parse count (total)','parse count (hard)','session cursor cache count','session cursor cache hits','opened cursors current') and b.sid=191;

NAME                                     VALUE
----------------------------------- ----------
opened cursors current                      10
session cursor cache hits                 3567
session cursor cache count                  40
parse count (total)                       1140
parse count (hard)                         589

SYS@ORA11GR2>
--session 1:第三次执行SQL
SCOTT@ORA11GR2>select count(*) from scott.dept;

  COUNT(*)
----------
         4

SCOTT@ORA11GR2>
--session 2:与第二次执行的结果相同
SYS@ORA11GR2>select a.name,b.value from v$sesstat b, v$statname a where a.statistic#=b.statistic# and a.name in ('parse count (total)','parse count (hard)','session cursor cache count','session cursor cache hits','opened cursors current') and b.sid=191;

NAME                                     VALUE
----------------------------------- ----------
opened cursors current                      10
session cursor cache hits                 3591
session cursor cache count                  36
parse count (total)                       1155
parse count (hard)                         594

SYS@ORA11GR2>
--小结
session_cached_cursor，这个参数控制会话打开游标或关闭游标
如果为0，说明不使用缓存游标功能，oracle每次都要重新打开游标
如果非0，说明使用缓存游标功能，会话一直保持打开状态
这个软软解析在11g上（起码在我的环境上）测试多次，结果都不统一，而且与理解的有偏差，本次作业就先做到这里，日后如有这方面的需求，再仔细的研究下。

session cursor cache hits:在高速缓存区中找到cursor的次数