一、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
客户端发出一个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