Oracle · PL/SQL - SQL

Stored Procedure Dependency May Crash Your Database

SP Dependency

DDL operations on your production may cripple your system. It is an all known fact. Especially when a Stored Procedure (SP), that has a dependency on that object, is heavily used. Most probably you can not compile it and you will have a lot of library cache pin, library cache lock … events.

This post is not about that but a more sophisticated issue.

First of all Let me briefly explain the scenario.

  •  You have a very very important transaction SP. Assume that If it stops, your system can not make orders, make financial transactions .
  • There is job or batch. When it start it runs for a long time. Assume that an hour something.
  • There is a dependency between the job SP and transaction SP. Job SP calls the transaction SP directly or indirectly (through calls of other SP s )
  • This job executes the transaction SP at the beginning and does other things but do not execute that SP ever.
  • Run the job and the job keeps running.
  • Execute a DDL on an object that is used in the transaction SP.
  • voila !!!!  after that all transactions halts and the transaction SP stays in invalid as long as the job keeps running

Lets see the steps in detail :
SETUP

--create a transaction table
create table eser.transaction_table ( id number, store varchar2(10), tran_date date ) ;

--create a sequence for that table
create sequence eser.transaction_sqn minvalue 1 maxvalue 10000 start with 1 increment by 1 nocache;

--create the SP that is responsible for the transaction
create or replace procedure eser.create_transaction_sp (v_store varchar2) as
begin
 insert into eser.transaction_table ( id , store , tran_date ) values (transaction_sqn.nextval, v_store , sysdate ) ;
 commit;
end;

--job SP that uses the transaction SP
create or replace procedure eser.job_normal_sp as
begin
 eser.create_transaction_sp('MAIN_STORE');
 loop
  dbms_lock.sleep(1);
 end loop;
end;

STEP 0 – System is running just fine

 --at the beginning all objects are valid.
 select owner, object_name, status, last_ddl_time from dba_objects 
    where object_name in ('CREATE_TRANSACTION_SP','JOB_NORMAL_SP','TRANSACTION_TABLE') ;
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
ESER TRANSACTION_TABLE VALID 30.01.2017 10:44:02
ESER JOB_NORMAL_SP VALID 30.01.2017 10:53:09
ESER CREATE_TRANSACTION_SP VALID 30.01.2017 10:52:49

STEP 1 – Run The job

--get my SID of the job session 
select distinct SID from v$mystat;
2196
--run the job in a session and let the job run
begin eser.job_normal_sp; end;

STEP 2 – Make a DDL that affects the transaction SP

--alter the table that is used in the transaction sp
--alter runs without any problem. It finishes successfully. 
alter table eser.transaction_table modify store varchar2(15) ;
 --Now the transaction SP is invalid. Lat DDL time still the same we understand that it is not implicitly compiled. 
 select owner, object_name, status, last_ddl_time from dba_objects 
    where object_name in ('CREATE_TRANSACTION_SP','JOB_NORMAL_SP','TRANSACTION_TABLE') ;
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
ESER TRANSACTION_TABLE VALID 30.01.2017 10:57:26
ESER JOB_NORMAL_SP INVALID 30.01.2017 10:53:09
ESER CREATE_TRANSACTION_SP INVALID 30.01.2017 10:52:49

STEP 3 – New coming transactions

--What happens if users make some transaction
--run the transaction SP on other sessions
--SESSION 1 SID with 2762
select distinct SID from v$mystat;
2762
--make a transaction 
begin eser.create_transaction_sp('LOCAL'); end ;

--SESSION 2 SID with 2953
select distinct SID from v$mystat;
2953
--make another transaction 
begin eser.create_transaction_sp('LOCAL'); end ;

STEP4 – Now the transactions halt

select a.SID,a.SERIAL#,a.BLOCKING_SESSION , a.EVENT, a.sql_id
 from v$session a
 where   a.status='ACTIVE' 
        and type <>'BACKGROUND' and sid in (2762, 2953, 2196 )
 order by last_call_et desc, a.module, a.event, a.sql_id;
SID SERIAL# BLOCKING_SESSION EVENT SQL_ID
2196 2205 _ PL/SQL lock timer 43wxrcznxz4qs
2762 615 2196 library cache pin 9211sy9zzq5xk
2953 631 2762 cursor: pin S wait on X 9211sy9zzq5xk
--transaction SP is invalid. Sessions wait on library cache events and can not continue.
--The system (transaction SP) will not work at this point. 
 select owner, object_name, status, last_ddl_time from dba_objects 
    where object_name in ('CREATE_TRANSACTION_SP','JOB_NORMAL_SP','TRANSACTION_TABLE') ;
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
ESER TRANSACTION_TABLE VALID 30.01.2017 10:57:26
ESER JOB_NORMAL_SP INVALID 30.01.2017 10:53:09
ESER CREATE_TRANSACTION_SP INVALID 30.01.2017 10:52:49

STEP5 – Kill the job
–Kill the running job OR cancel the session
–This will enable the transactional SP to be compiled ant let the users make some real transaction.

--kill or stop the job. 
--after the job session is killed the transaction SP finally can be compiled by the calling sessions in the background
 select owner, object_name, status, last_ddl_time from dba_objects 
    where object_name in ('CREATE_TRANSACTION_SP','JOB_NORMAL_SP','TRANSACTION_TABLE') ;
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
ESER TRANSACTION_TABLE VALID 30.01.2017 10:57:26
ESER JOB_NORMAL_SP INVALID 30.01.2017 10:53:09
ESER CREATE_TRANSACTION_SP VALID 30.01.2017 10:59:07

STEP6 – Now the system is running

--now the customers can make transactions
begin eser.create_transaction_sp('LOCAL'); end ;

CONCLUSION
Stored Procedure dependency should be considered deeply on high transaction systems. If you have a lot of jobs that uses the important stored procedures at sometime during their run time, It is a definite risk for your system. Most important one is, DDL on critic objects are risky business and should be done with extra care.

You may not prevent SP dependency but there are other ways to prevent from this scenario.
I will explain on another post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s