Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hard parse every time even though bind variables are used

482976Aug 13 2008 — edited Aug 14 2008
A colleague runs C++ with Roguewave Oracle libraries.
He is using bind variables as below.
I cannot think why he is getting a hard parse every time, when I would have expected parse once; execute/fetch many in this example.
Could someone explain please?
Many thanks.


SELECT t1.STR, t1.DAT
FROM
TestTable t1 WHERE t1.ID = :ph10 AND t1.NUM = :ph11


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 100 0.03 0.03 0 0 0 0
Fetch 100 0.00 0.00 0 200 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 0.03 0.04 0 200 0 100

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 153

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TESTTABLE (cr=2 pr=0 pw=0 time=32 us)
1 INDEX UNIQUE SCAN TESTTABLE_PK (cr=1 pr=0 pw=0 time=16 us)(object id 88549)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 11 2008
Added on Aug 13 2008
4 comments
1,048 views