Re: Statements parsing from stored procedures

<<Anyone has tried using Ref cursor or PL/SQL tables in .NET application
for reducing parsing.

Please let me know.

Thanks & Regards,

Shailesh>>

I'm not sure it matters. Every test case I could put together had a soft parse of any recursive SQL, which SQL inside of PL/SQL is considered to be.

See the following from Tom Kyte's site...

"recursive SQL is (has to be) parsed during each execution. so yes, in this case, this is normal."

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2588723819082

So if you are using REF CURSORS, it will at least be soft parsed.

As shown below, the REF CURSOR, even though in v$open_cursor for my SID, is still soft parsed for the next execution...

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct sid from v$mystat;

       SID

       159

SQL> select user_name,sid from v$open_cursor where sql_id='48tu1msqmbb65';

no rows selected

SQL> exec p0411.get_cursor(:p);

PL/SQL procedure successfully completed.

SQL> select parse_calls from v$sql where sql_id='48tu1msqmbb65';

PARSE_CALLS

          1

SQL> exec p0411.get_cursor(:p);

PL/SQL procedure successfully completed.

SQL> select user_name,sid from v$open_cursor where sql_id='48tu1msqmbb65';

USER_NAME                             SID


------------------------------ ----------

REP                                   159

SQL> select parse_calls from v$sql where sql_id='48tu1msqmbb65';

PARSE_CALLS

          2

SQL> exec p0411.get_cursor(:p);

PL/SQL procedure successfully completed.

SQL> select parse_calls from v$sql where sql_id='48tu1msqmbb65';

PARSE_CALLS

          3

SQL> select user_name,sid from v$open_cursor where sql_id='48tu1msqmbb65';

USER_NAME                             SID


------------------------------ ----------

REP                                   159

SQL> Is this an academic question, or are you actually having performance problems due to soft parsing?

Regards,

Steve Received on Tue Apr 18 2006 - 12:32:40 CDT