Stored Procedure Parsing

[zahmed]

OraDirect does not parse stored procedure for every execution.

But Microsoft & Oracle providers does.

Why is this? How does OraDirect work?


See this trace for detail:
For below SP, Microsoft & Oracle's provider do parsing n times while executing n times. OraDirect does parsing only once.

BEGIN
GETTAGSETUPFROMIDS(:I_TAG_SK, :O_TAG_SK, :O_VAL_CHECK_TAG_SK, :o_tag_comments, :o_data_sub_type, :o_primary_value_ind, :o_prod_date_lag);
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 1.10 0 0 0 0
Execute 10000 10312.50 11828.39 0 0 0 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 10312.50 11829.49 0 0 0 10000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 72
********************************************************************************

select user#
from
sys.user$ where name = 'OUTLN'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.39 0 0 0 0
Execute 1 0.00 0.40 0 0 0 0
Fetch 1 0.00 0.38 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 1.17 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)

********************************************************************************

SELECT
tag_sk,val_check_tag_sk,tag_comments,data_sub_type,primary_value_ind,prod_date_lag
FROM VC_SCADA_TAG_DEF
WHERE tag_sk = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 156.25 157.02 0 0 0 0
Execute 10000 1406.25 1571.89 0 0 0 0
Fetch 10000 1562.50 1502.98 0 30000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 3125.00 3231.89 0 30000 0 10000

[Oleg]

OraDirect .NET doesn't parse command for each execution.
If you need parse it before each execution you can create OracleCommand
and then release it using Dispose method.
This behaviour was corrected in OraDirect .NET 2.50.
You can download beta version now at http://www.crlab.com/oranet/oranet250b.msi