Stored Procedure Parsing

Stored Procedure Parsing

Postby zahmed » Fri 19 Nov 2004 17:20

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
zahmed
 

Re: Stored Procedure Parsing

Postby Oleg » Mon 22 Nov 2004 11:17

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
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56


Return to dotConnect for Oracle