用示例演示一次硬分析(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次(存疑)
----------------------------------- ----------
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的次数
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;
----------------------------------- ----------
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的次数