软分析--快速软分析

一、SQL执行过程
客户端发出一个SQL，会经历如下步骤后才会返回执行结果：

首先从当前会话的PGA中查找是否存在同样的SQL。如果存在，则SGA中肯定存在该SQL的执行计划，直接根据PGA中提供的hash值从SGA中查找对

应的执行计划并执行。

若当前会话的PGA中不存在，则从其他会话的PGA中查找是否存在同样的SQL。如果存在，则同样根据hash值在SGA中查找对应的执行计划并执行

。同时伴随的一个动作是将该SQL及hash值在本会话的PGA中保存一份，这样下次执行这个SQL的时候，就直接从本地PGA中找到该SQL，从而跳过

了从其他PGA中查找的步骤。

以上称为快速软分析。

若其他会话PGA中也没有，则将该SQL作为一个字符串计算hash值，然后在library cache中查找该hash值（library cache是shared_pool的一部

分，而shared_pool是SGA的一部分）。若存在，则直接根据该hash值对应的执行计划进行执行。这称为软分析。

若library cache中未找到该hash值。则说明该SQL是第一次执行，或者以前的执行计划已经被替换出了内存（LRU算法）。则需要进行硬分析：

语法分析

语义分析（权限分析）

视图的融合

SQL改写（oracle自动对SQL进行重新组合，无法人为干预）

优选最佳执行计划（根据优化器以及统计数据的不同，而选择oracle认为最好的计划）

执行SQL

由此可知，快速软分析是执行效率最高的。那么，快速软分析的命中率和什么相关呢？

二、v$open_cursor与session_cached_cursors
（本段内容摘自wanghai的文章）

1. v$open_cursor

v$open_cursor是oracle提供的一张查看每个session打开过的cursor的视图,它的表结构如下：

SQL> desc v$open_cursor

Name Null? Type

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

SADDR RAW(4)

SID NUMBER

USER_NAME VARCHAR2(30)

ADDRESS RAW(4)

HASH_VALUE NUMBER

SQL_TEXT VARCHAR2(60)

当我们执行一条SQL语句的时候,我们将会在shared pool产生一个library cache object，cursor就是其中针对于SQL语句的一种library cache

object。另外我们会在PGA有一个cursor的拷贝，同时在客户端会有一个statement handle，这些都被称为cursor，在v$open_cursor里面我们

可以看到当前打开的cursor和PGA内cached cursor。

2. session_cached_cursor

这个参数限制了在PGA内session cursor cache list的长度，session cursor cache list是一条双向的lru链表，当一个session打算关闭一个

cursor时，如果这个cursor的parse count超过3次，那么这个cursor将会被加到session cursor cache list的MRU端。当一个session打算

parse一个SQL时，它会先去PGA内搜索session cursor cache list，如果找到那么会把这个cursor脱离list，然后当关闭的时候再把这个

cursor加到MRU端.session_cached_cursor提供了快速软分析的功能，提供了比soft parse更高的性能。

三、相关测试
1.快速软分析的初步了解
--0.环境

SQL> select *From v$version;



BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 – Production



SQL> show parameter session_cache



NAME TYPE VALUE

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

session_cached_cursors integer 0



--1.session1：查看session1的sid，然后断开

SQL> conn / as sysdba

已连接。

SQL> select distinct sid from v$mystat;



SID

----------

9



SQL> disconn

从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中断开



--session2：PGA中目前无session1的信息。

SQL> select * from v$open_cursor where sid=9;



未选定行



--session1连接数据库，执行一个SQL，则在PGA中缓存该SQL。下次session1再执行这个SQL，就直接从PGA中取。

SQL> conn / as sysdba

已连接。

SQL> select distinct sid from v$mystat;



SID

----------

9



--session2



SQL> select * from v$open_cursor where sid=9;



SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_TEXT

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

682102D8 9 SYS 66D0E548 695466438 select distinct sid from v$mystat



SQL>



--session3：如果session3也要执行这个SQL，则它首先从自己的PGA中查找：没找到；然后从session1的PGA中找到该SQL，直接用hash_value

在SGA中找到对应的执行计划。同时从session1的PGA中复制一份到session3的PGA中。

SQL> select sid from v$mystat where rownum=1;



SID

----------

12



SQL> select distinct sid from v$mystat;



SID

----------

12

--session2：各个PGA以及SGA中该SQL的哈希值是一样的。

SQL> select * from v$open_cursor where sid=12;



SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_TEXT

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

68211F28 12 SYS 66D0E548 695466438 select distinct sid from v$mystat



SQL> select hash_value, SQL_text from v$SQLarea where SQL_text like 'select distinct sid from %';



HASH_VALUE SQL_TEXT

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



695466438 select distinct sid from v$mystatx

总结：以上就是快速软分析的过程。那么，如何在PGA中保存更多的SQL，以实现快速软分析呢？

2.快速软分析的性能比较
由wanghai的文章可知，参数session_cached_cursors控制session cursor cache list的长度。详细的测试可以参考他的文档。不过我在9201

版上的测试结果与他的不同，而且每次测试结果都不太相同，缓存算法似乎不只与执行次数相关。不过这个就暂时不关心了，有兴趣的可以自

己测试，我们还是来看一下它是如何改善系统性能的吧：

在tom的小测试中，可以很清晰地看出增大session_cached_cursors前后系统性能的变化：

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table emp as select * from scott.emp;
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table run_stats ( runid varchar2(15),
name varchar2(80), value int );
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;

View created.


ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> column name format a40
ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> declare
2 l_start number;
3 l_cnt number;
4 begin
5 execute immediate 'alter session set session_cached_cursors=0';
6 insert into run_stats select 'before', stats.* from stats;
7
8 l_start := dbms_utility.get_time;
9 for i in 1 .. 1000
10 loop
11 execute immediate 'select count(*) from emp' into l_cnt;
12 end loop;
13 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
14
15 execute immediate 'alter session set session_cached_cursors=100';
16 insert into run_stats select 'after 1', stats.* from stats;
17
18 l_start := dbms_utility.get_time;
19 for i in 1 .. 1000
20 loop
21 execute immediate 'select count(*) from emp' into l_cnt;
22 end loop;
23 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
24
25 insert into run_stats select 'after 2', stats.* from stats;
26 end;
27 /
45 hsecs
35 hsecs

PL/SQL procedure successfully completed.

增加session_cached_cursors后，不但程序执行速度大大提高，而且大大减少了库缓存和共享池中latch的数目（详细测试参考tom的文章），

这真是一个令DBA高兴的消息。

而在10g中，已经默认将session_cached_cursors参数设置为100了：

SQL> select * from v$version;



BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production



SQL> show parameter session_cach



NAME TYPE VALUE

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

session_cached_cursors integer 100