Stored Proc Recompilation

[abhaysk]

I noticed one rare event while recompiling the SP with modified code....

Session X had called this SP before recompilation....and is running...
Now i change the program logic in that SP and recomplie it...Recompilation is sucessful....
But the session X errored.....which it shud not have errored....coz the change in SP is just change in Where cluase & morever...when SP is called , whole of the SP will be loaded into the memory & i guess it shud exexute from ther.........why the error then????

I tried to simulate the problem if it behaves same on my test server.....I didnt face any problem here.....SP got recompiled sucessfully....& session started before populated data in accordance with old SP.....

Appriciate ur help.

Abhay.

[slimdave]

what error did the session throw?

[guru_heaven]

I would liked to answer but I dont understand what you are talking about and as someone suggested you dont even know the error message?

[hrishy]

Hi

The proceedure/function/package maintains a state, user started using it. They have a state established.

some other user or you altered the code and practically destroyed or
wiped out their state. It is gone.

The sp would work erraneously at that point and hence they need to try the operation at the second time.You are not able to reproduce in the test environment because you didnt to the exact sequence of things that happened in production

http://technet.oracle.com/docs/produ...9depnd.htm#369

Session State and Referenced Packages
Each session that references a package construct has its own instance of that package, including a persistent state of any public and private variables, cursors, and constants. All of a session's package instantiations including state can be lost if any of the session's instantiated packages are subsequently invalidated and recompiled.



regards
Hrishy

[abhaysk]

Originally posted by hrishy
Hi

You are not able to reproduce in the test environment because you didnt to the exact sequence of things that happened in production

regards
Hrishy

I was able to able to reproduce the senario in test server....wat i noticed was, when the session referenced the SP & was runing, i issued the Create or replace SP command.....& i saw that this session infact was waiting for the other session which refereneced the SP....after it completed ..... this session sucessfully replaced the SP.....

1 thing is ther .... there is not as much as data in PRO as in my test server....so i copied the data just to prolong the execution and to see wat happens.......Now wat i noticed was....when i issued "Create or Replace " for SP.....it errored saying
"Timed Out due Waitng to lock the object SP_NAME"

Regards
Abhay.

[hrishy]

Hi

This is the right and expected behaviour..now you are on the right track as you sucessfully were able to reproduce the situation.

You can read somethin that would be of interest to you here

http://download-west.oracle.com/docs...cnsis.htm#3188

Your case is that of Breakable Parse Locks


Timed Out due Waitng to lock the object SP_NAME"


well this happened because the
A parse lock persists as long as the associated SQL statement remains in the shared pool.

You acn experiment in your test database by doing

alter system flush shared pool..

be careful dont do this in the prod environment..

regards
Hrishy

[hrishy]

Hi

This is the right and expected behaviour..now you are on the right track as you sucessfully were able to reproduce the situation.

You can read somethin that would be of interest to you here

http://download-west.oracle.com/docs...cnsis.htm#3188

Your case is that of Breakable Parse Locks

A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool


Timed Out due Waitng to lock the object SP_NAME"


well this happened because the
A parse lock persists as long as the associated SQL statement remains in the shared pool.

You acn experiment in your test database by doing

alter system flush shared pool..

be careful dont do this in the prod environment..

regards
Hrishy

[abhaysk]

Originally posted by hrishy
Hi

This is the right and expected behaviour..

Your case is that of Breakable Parse Locks

A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool


Timed Out due Waitng to lock the object SP_NAME"


well this happened because the
A parse lock persists as long as the associated SQL statement remains in the shared pool.

regards
Hrishy

Hrishy :

when altering SP....
Waiting for the other session, which has referenced this SP, to complete is normal behaviour.....

If wait is too long then u get Timout error for the session where we are altering SP.....that is farely good and that is wat happened in Test server.......


But in PRO server , it happened other way round, i.e Session that referenced the SP errored....( well i dont have logs as to wat error did it throw )?????
And Shared pool was not flushed by ne 1.......

Shud this be possible cause...
that when this session tried to Alter the SP...the other session tried to reference & momentorily the Object SP will be in invalid state during the alter period?????

Abhay.

[hrishy]

Hi Abhay

I suspect somethin else has happened in production..what has happened in test is logical..production somethin else has happened..I am not able to figure it out

can you post the source of the sp here


regards
Hrishy

[abhaysk]

Originally posted by hrishy
Hi Abhay

I suspect somethin else has happened in production..what has happened in test is logical..production somethin else has happened..I am not able to figure it out

can you post the source of the sp here


regards
Hrishy

Hrishy :

The SP code is abt 3000 lines....and i am afraid i cant post...

Thanks for ur reply...
Well i jus wanted to know if Object will in Invalid State even momentarily when we were to recompile the SP with modified code?

Abhay.