Procedure hangs

[balajiyes]

Hi All,

My procedure hangs when I use a CONSTANT variable in my WHERE clause.

When I hard code, it come out in a second.

Please revert.

[WilliamR]

Perhaps the optimizer is choosing a different execution plan.

[balajiyes]

Thanks williams. The same query plan are same. FOr time being I have hard coded. The same code is working in our production and not in our development (ver 9.2.6).

[WilliamR]

What is it waiting on? Query v$session_wait for the session SID to find the event.

Is the actual execution plan the same as the one you see when you copy & paste the SQL to SQL*Plus, PL/SQL Developer etc? You can check this with tkprof, or something along the lines of Tom Kyte's DYNAMIC_PLAN_TABLE (http://asktom.oracle.com/pls/ask/f?p=4950:8:9546226128166358179::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:39947833352874).