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 Go to next message
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 #574698 is a reply to message #574695] Tue, 15 January 2013 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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

[Updated on: Tue, 15 January 2013 02:33]

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 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member
Michel Cadot wrote on Tue, 15 January 2013 02:33
Quote:
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
Re: does 11g not do a soft parse when we set cursor_cached_curosr? [message #574733 is a reply to message #574729] Tue, 15 January 2013 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change my answer which was about your question: session_cached_cursors.

In 8i, dynamic queries were ALWAYS parsed even if they are the same ones, cursor cache is not used.
This enhancement was introduced in 10g, iirc.

Regards
Michel

[Updated on: Tue, 15 January 2013 09:17]

Report message to a moderator

Re: does 11g not do a soft parse when we set cursor_cached_curosr? [message #574736 is a reply to message #574733] Tue, 15 January 2013 09:29 Go to previous message
alantany
Messages: 115
Registered: July 2007
Senior Member
Thanks ,Michel

You are always knowledgeable!


Regards
Alan
Previous Topic: Tune a cursor
Next Topic: Physical Reads & Logical Reads
Goto Forum:
  


Current Time: Thu Mar 28 11:22:57 CDT 2024