Oracle 变量绑定与变量窥视合集系列二

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

我们先看一个硬解析和软解析关系测试，什么时候硬解析，什么时候软解析

LEO1@LEO1> drop table leo6 purge;                          清理环境

Table dropped.

LEO1@LEO1> create table leo6 as select * from dba_objects;      创建leo6表

Table created.

LEO1@LEO1> select count(*) from leo6;                       第一次执行

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

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

select count(*) from leo6                 1           1        1

总解析次数：1

硬解析次数：1

执行次数：1

硬解析发生在SQL语句第一次执行时，后续在执行相同语句就是软解析了，看看下面

LEO1@LEO1> select count(*) from leo6;                       第二次执行

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

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

select count(*) from leo6                 2           1        2

总解析次数：2

硬解析次数：1

执行次数：2

当SQL硬解析之后，后续相同的SQL都被软解析，除非SQL被剔除shared_pool

LEO1@LEO1> alter system flush shared_pool;       清空shared_pool

System altered.

LEO1@LEO1> select count(*) from leo6;            重新执行一次

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

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

select count(*) from leo6                 1           1        1

当重新执行一次后，又重新有了一次硬解析，oracle认为这是一条新SQL，根据统计信息重新生成一次执行计划来执行。

Softer_soft_parse 会话对游标的缓存

什么是游标：游标可以理解为SQL语句的一个句柄，也叫SQL语句的指针，游标指向一条SQL语句，oracle会话要执行一条SQL时，首先要打开游标。

打开游标：新的SQL语句执行时候，在SGA中需要把这条SQL语句和shared_pool中SQL语句的哈希值建立一条通道（连接），即建立SQL语句句柄，这个建立通道的过程就叫打开游标。

softer_soft_parse超软解析：打开游标的过程是在软解析之后，它要在shared_pool中寻找哈希值（这个哈希值就是软解析之后得到的），如果没有找到就需要重新构造游标（这就是硬解析过程），如果游标是打开状态，那么会话可以直接使用打开的游标连接到shared_pool中SQL语句入口，执行SQL。如果游标是关闭状态，会话就需要重新建立到shared_pool连接（即打开游标操作），这样也会消耗一点点资源。而我们要做的就是尽量打开游标保持通道畅通，又由于这个操作是在软解析之后，又叫超软解析，比软解析还要软一点。

会话缓存游标实验

session_cached_cursor，这个参数可以控制，会话打开游标或关闭游标

如果值为0，说明不使用缓存游标功能，oracle每次都要重新打开游标

如果值为非0，说明使用缓存游标功能，会话一直保持打开状态，随时执行SQL语句

1.session_cached_cursors=0 测试

为了更好的对比效果，我们先要清空一下内存中的遗留数据，最简行以便的方法就是重启数据库了，当然我们这是测试环境，在生产环境中另当别论。

SYS@LEO1> shutdown immediate;               关闭数据库，清空SGA

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@LEO1> startup                           启动数据库

ORACLE instance started.

Total System Global Area  680607744 bytes

Fixed Size                  2216464 bytes

Variable Size             503320048 bytes

Database Buffers          167772160 bytes

Redo Buffers                7299072 bytes

Database mounted.

Database opened.

SYS@LEO1> conn leo1/leo1                    切换leo1用户

Connected.

LEO1@LEO1> set linesize 300                   设置版面

LEO1@LEO1> set pagesize 999

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

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

session_cached_cursors                   integer      50

检查参数默认为50

LEO1@LEO1> alter session set session_cached_cursors=0;

Session altered.

如果值为0，说明不使用缓存游标功能，oracle每次都要重新打开游标

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

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

session_cached_cursors                   integer      0

LEO1@LEO1> alter system flush shared_pool;       清空shared pool

System altered.

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';

'STAT-'||NAME                                VALUE

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

STAT-opened cursors cumulative                  51660

STAT-opened cursors current                     88          当前打开游标数

STAT-pinned cursors current                      5

STAT-session cursor cache hits                    37902       会话缓冲区游标命中率

STAT-session cursor cache count                  3153

STAT-cursor authentications                      668

STAT-parse time cpu                            845         CPU解析耗时

STAT-parse time elapsed                         1974        解析总耗时

STAT-parse count (total)                         30593       解析总次数

STAT-parse count (hard)                         2700        硬解析次数

STAT-parse count (failures)                       6

STAT-parse count (describe)                      0

LATCH-shared pool                             263201       共享池latch数

查询系统当前资源消耗情况

LEO1@LEO1> drop table leo7 purge;                           清空环境

Table dropped.

LEO1@LEO1> create table leo7 as select * from dba_objects;       创建leo7表

Table created.

LEO1@LEO1> select count(*) from leo7;                        表size71972条

  COUNT(*)

----------

     71972

循环执行1w次

LEO1@LEO1> begin

for leo in 1..10000 loop

execute immediate 'select count(*) from leo7';

end loop;

end;

/

PL/SQL procedure successfully completed.

再次查询一下当前系统资源消耗情况

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  2    3  

'STAT-'||NAME                                 VALUE

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

STAT-opened cursors cumulative                   63614

STAT-opened cursors current                      89      当前打开游标数

STAT-pinned cursors current                       6

STAT-session cursor cache hits                     39494   会话缓冲区游标命中率

STAT-session cursor cache count                   3513

STAT-cursor authentications                       713

STAT-parse time cpu                             851

STAT-parse time elapsed                          1992

STAT-parse count (total)                          41546   解析总次数

STAT-parse count (hard)                          2739    硬解析次数

STAT-parse count (failures)                        6

STAT-parse count (describe)                       0

LATCH-shared pool                              268172

这是session_cached_cursors=0的对比结果

当前打开游标数：88  ->  89   多了1个

会话缓冲区游标命中率：37902   ->   39494   多了1592次

硬解析次数：2700  ->  2739   多了39次

解析总次数：30593  ->  41546  多了10953次，这个和执行的次数很接近，差不多软解析了1w次

共享池latch数：263201  ->  268172   多了4971次

2.session_cached_cursors=100 测试

系统当前资源消耗

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  2    3  

'STAT-'||NAME                                 VALUE

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

STAT-opened cursors cumulative                   80112

STAT-opened cursors current                      88

STAT-pinned cursors current                      5

STAT-session cursor cache hits                    44463

STAT-session cursor cache count                  4334

STAT-cursor authentications                      791

STAT-parse time cpu                            872

STAT-parse time elapsed                         2016

STAT-parse count (total)                         55199

STAT-parse count (hard)                         2771

STAT-parse count (failures)                       10

STAT-parse count (describe)                      0

LATCH-shared pool                             278343

LEO1@LEO1> alter session set session_cached_cursors=100;      

如果值为非0，说明使用缓存游标功能，会话一直保持打开状态，随时执行SQL语句

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

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

session_cached_cursors               integer     100

再重新循环执行1w次

LEO1@LEO1> begin

for leo in 1..10000 loop

execute immediate 'select count(*) from leo7';

end loop;

end;

/

PL/SQL procedure successfully completed.

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  

'STAT-'||NAME                                 VALUE

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

STAT-opened cursors cumulative                   92040

STAT-opened cursors current                      89

STAT-pinned cursors current                       5

STAT-session cursor cache hits                     56058

STAT-session cursor cache count                   4690

STAT-cursor authentications                       803

STAT-parse time cpu                             872

STAT-parse time elapsed                          2017

STAT-parse count (total)                          56081

STAT-parse count (hard)                          2773

STAT-parse count (failures)                        10

STAT-parse count (describe)                       0

LATCH-shared pool                              280878

这是session_cached_cursors=100的对比结果

当前打开游标数：88  ->  89   多了1个

会话缓冲区游标命中率：44463   ->   56058   多了11595多

硬解析次数：2771  ->  2773   多了2次

解析总次数：55199  ->  56081  多了882次

共享池latch数：278343  ->  280878   多了2535次

小结：我们从会话缓冲区游标命中率指标的对比结果可知，设置session_cached_cursors参数为非0时，oracle使用了缓存游标功能，命中率从1592提升到11595，共享池latch数从4971减少到2535，我们可以很明显的看出使用打开游标技术可以大大提高数据库的性能，softer_soft_parse超软解析可以提供比soft_parse软解析更好的性能。

  本文转自 leonarding151CTO博客，原文链接：http://blog.51cto.com/leonarding/1131508，如需转载请自行联系原作者