Why keep a stored procedure forever, but the code in the procedure runs quickly on it?

Question

The first is Oracle. If I do this ...

execute my_package.sp_execute_my_procedure('...', '...');

It seems that he runs endlessly (I let him go for the night).

However, if I take the code from the stored procedure, delete it in the anonymous pl / sql block, put the declare keyword on my only cursor and run it, it ends in 10 minutes. How long does it take.

Without publishing all the code, at least right off the bat, did anyone see anything like this?

UPDATE: Okay, so I notice when I select from v $ session, when I start proc I get "UNKNOWN" blocking_session_status with the event "direct write path".

I can’t say, from the small amount of googling that I have done so far, what this means.

Answer 1

There may be some conflict for the package object that is blocking your session before it can even run the code.

While the command executeis hanging, request V$SESSIONto see what this session expects.

Answer 2

Does your PL / SQL code use a bind variable and does your DECLARE block use a literal in it? This may lead to a different plan and therefore to different performance.

Answer 3

, , pl/sql , declare , 10 .

...

, .

?