SQL 已经使用了绑定变量，为啥parse call还这么高

kewin

写了while 循环来测试：

declare
i int;
begin
i := 1;
while i < 100 loop
execute immediate 'select count(*) from test';
i := i + 1;
end loop;
end ;



SQL> select executions, parse_calls, sql_text from  v$sql where sql_text like 'select count(*) from test';
;
EXECUTIONS PARSE_CALLS
---------- -----------
SQL_TEXT
--------------------------------------------------------------------------------
       203           7
select count(*) from test


SQL> declare
  2  i int;
begin
i := 1;
while i < 100 loop
execute immediate 'select count(*) from test';
i := i + 1;
end loop;
end ;
  3    4    5    6    7    8    9   10  
11  /

PL/SQL procedure successfully completed.

SQL> select executions, parse_calls, sql_text from  v$sql where sql_text like 'select count(*) from test';

EXECUTIONS PARSE_CALLS
---------- -----------
SQL_TEXT
--------------------------------------------------------------------------------
       302           8
select count(*) from test

在while循环中，parse 增加一次，execute 为99次。
no parse 发生在同一个 transaction 中； 在sqlplus 中，单独的去执行语句，oracle认为是不同的transaction ，所以每次都会增加soft parse .

kewin

而no parse受到 一个参数的影响 session_cached_cursors   
系统当前的 session_cached_cursors    为100.
SQL> show parameter cursor

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
cursor_sharing                       string
EXACT
cursor_space_for_time                boolean
FALSE
open_cursors                         integer
1024
session_cached_cursors               integer
100      

在session 级别关闭  no parse 的选项：
SQL> alter session set session_cached_cursors= 0;

Session altered.

SQL> select executions, parse_calls, sql_text from  v$sql where sql_text like 'select count(*) from test';

EXECUTIONS PARSE_CALLS
---------- -----------
SQL_TEXT
--------------------------------------------------------------------------------
       302           8
select count(*) from test

再次执行一个匿名package：
SQL> declare
  2  i int;
begin
i := 1;
while i < 100 loop
execute immediate 'select count(*) from test';
i := i + 1;
end loop;
end ;
  3    4    5    6    7    8    9   10  
11  /

PL/SQL procedure successfully completed.

SQL> select executions, parse_calls, sql_text from  v$sql where sql_text like 'select count(*) from test';

EXECUTIONS PARSE_CALLS
---------- -----------
SQL_TEXT
--------------------------------------------------------------------------------
       302           8
select count(*) from test

        99          99
select count(*) from test  

看到oracle因为编译环境的变化，认为是个新的sql， 重新分配sql area 。 看到由于session_cache_cursor 的关系，导致产生了98 次soft  parse + 1次 hard parse。

Yong Huang

Thanks for the test. Some of your wording may need to be changed.

> 在while循环中，parse 增加一次，execute 为99次。
> no parse 发生在同一个 transaction 中； 在sqlplus 中，单独的去执行语句，oracle认为是不同的transaction ，所以每次都会增加soft parse .

It's not transaction. It's the execution context, SQL vs. PL/SQL. Inside a PL/SQL block, the cursor is internally kept open in the session's private memory (UGA), as long as you didn't disable session cursor cache.

> 看到oracle因为编译环境的变化，认为是个新的sql， 重新分配sql area 。 看到由于session_cache_cursor 的关系，
> 导致产生了98 次soft  parse + 1次 hard parse。

I tested your code. A new cursor is created and v$sql_shared_cursor indicates the reason is TOP_LEVEL_RPI_CURSOR, which I don't fully understand. RPI is short for "Recursive Program Interface", basically a software layer for recursive calls. Disabling session cursor cache causes a new type of recursive call? Maybe. But I don't know the details.

Yong Huang

kewin

原帖由 Yong Huang 于 2011-8-19 01:14 发表
Thanks for the test. Some of your wording may need to be changed.

> 在while循环中，parse 增加一次，execute 为99次。
> no parse 发生在同一个 transaction 中； 在sqlplus 中，单独的去执行语句，oracle认为是不同的transaction ，所以每次都会增加soft parse .

It's not transaction. It's the execution context, SQL vs. PL/SQL. Inside a PL/SQL block, the cursor is internally kept open in the session's private memory (UGA), as long as you didn't disable session cursor cache.

> 看到oracle因为编译环境的变化，认为是个新的sql， 重新分配sql area 。 看到由于session_cache_cursor 的关系，
> 导致产生了98 次soft  parse + 1次 hard parse。

I tested your code. A new cursor is created and v$sql_shared_cursor indicates the reason is TOP_LEVEL_RPI_CURSOR, which I don't fully understand. RPI is short for "Recursive Program Interface", basically a software layer for recursive calls. Disabling session cursor cache causes a new type of recursive call? Maybe. But I don't know the details.

Yong Huang

Thanks your comment. I thought about sql area but didn't  look at related view.
I will do advanced test if I am avaiable today.
Thanks again.