Home » RDBMS Server » Performance Tuning » does 11g not do a soft parse when we set cursor_cached_curosr? (11gr2)
| does 11g not do a soft parse when we set cursor_cached_curosr? [message #574695] |
Tue, 15 January 2013 02:02  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hi,
With Oracle 11gr2,I noticed when I set session_cached_cursors to a not zero value,Oracle will not make a soft parse to the subsequent SQL'S.
Is this correct?
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 14
parse count (hard) 0
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL> alter session set session_cached_cursors=500;
会话已更改。
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate 'select /*+ 1000 */ count(*) from emp' ;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 18
parse count (hard) 1
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL>
SQL> alter session set session_cached_cursors=100;
会话已更改。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 20
parse count (hard) 1
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL>
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate 'select /*+ 1000 */ count(*) from emp' ;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 23
parse count (hard) 1
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL>
SQL>
SQL> alter session set session_cached_cursors=10;
会话已更改。
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate 'select /*+ 1000 */ count(*) from emp' ;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 27
parse count (hard) 2
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL>
SQL> alter session set session_cached_cursors=0;
会话已更改。
SQL>
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate 'select /*+ 1000 */ count(*) from emp' ;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 1030
parse count (hard) 2
parse count (failures) 0
parse count (describe) 0
已选择6行。
SQL>
SQL>
SQL> alter session set session_cached_cursors=1;
会话已更改。
SQL> begin
2 for i in 1 .. 1000
3 loop
4 execute immediate 'select /*+ 1000 */ count(*) from emp' ;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic# and a.name like 'parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 9
parse count (total) 1034
parse count (hard) 3
parse count (failures) 0
parse count (describe) 0
已选择6行。
Regards
Alan
[Updated on: Tue, 15 January 2013 02:04] Report message to a moderator |
|
|
|
|
|
| Re: does 11g not do a soft parse when we set cursor_cached_curosr? [message #574729 is a reply to message #574698] |
Tue, 15 January 2013 08:53  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 15 January 2013 02:33Quote:Oracle will not make a soft parse to the subsequent SQL'S.
Is this correct?
This is correct for the already parsed statement, and this is correct for any version of Oracle.
Regards
Michel
Hi,Michel
Here is a same test from ASKTOM,the only different is on 8.1.7.
Please look at the "parse count (total)" ,it shows that Oracle did a soft parse when setting session_cached_cursor to 100.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80),
value int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> column name format a40
ops$tkyte@ORA817DEV.US.ORACLE.COM> 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.
so, session cached cursors RAN faster (i ran this a couple of times, there were no hard parses
going on. But the real good news is:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.checkpoint queue latch 3 4 1
LATCH.redo allocation 30 31 1
STAT...consistent gets 5088 5089 1
STAT...deferred (CURRENT) block cleanout 2 3 1
applications
STAT...calls to get snapshot scn: kcmgss 5019 5018 -1
STAT...enqueue releases 10 9 -1
STAT...execute count 1015 1014 -1
STAT...opened cursors cumulative 1015 1014 -1
STAT...parse count (total) 1015 1014 -1
STAT...session cursor cache count 0 1 1
STAT...redo entries 28 27 -1
STAT...recursive calls 1180 1179 -1
STAT...physical reads 1 0 -1
LATCH.direct msg latch 2 0 -2
LATCH.session queue latch 2 0 -2
LATCH.done queue latch 2 0 -2
STAT...free buffer requested 8 6 -2
STAT...enqueue requests 11 9 -2
LATCH.messages 3 0 -3
STAT...db block changes 47 44 -3
LATCH.redo writing 3 0 -3
LATCH.ksfv messages 4 0 -4
STAT...session logical reads 17128 17123 -5
LATCH.row cache objects 184 178 -6
STAT...db block gets 12040 12034 -6
STAT...parse time elapsed 9 3 -6
STAT...parse time cpu 13 4 -9
STAT...recursive cpu usage 51 38 -13
LATCH.cache buffers chains 34315 34335 20
STAT...redo size 23900 24000 100
STAT...session cursor cache hits 3 1002 999
LATCH.shared pool 2142 1097 -1045
LATCH.library cache 17361 2388 -14973
34 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Regards
Alan
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 29 15:29:16 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
