Hello! I have a friend that told me that compiling a new piece of Pl/SQL code will put library cache locks on the dependent objects, and that there's a risk for database contention when new code is deployed.
In other words, if I go out to a database and compile
for the very first time when nobody else is trying to call or use the get_record_count procedure yet:
create procedure get_record_count
is
v_record_count number;
begin
select count(*) into v_record_count
from my_dependent_table;
log_record_count(v_record_count);
dbms_output.put_line(v_record_count || ' records');
end get_record_count;
...my friend says that I risk putting library cache locks on MY_DEPENDENT_TABLE and/or the LOG_RECORD_COUNT procedure during the compilation, and as a result there could be database contention with the other sessions.
I've seen library cache locks firsthand when re-compiling existing code that someone else is trying to use, or when people are trying to use code that calls the code I'm re-compiling (for example, if I tried to re-compile LOG_RECORD_COUNT while someone was calling GET_RECORD_COUNT I can see where this would likely cause library cache lock contention). But I've never once seen contention as a result of
new code being deployed that no one else is trying to run.
I suspect my friend is incorrect, but I'm not sure how to even write a test to verify whether or not this is true though.
First, is my friend correct or not?
Second, is possible to write a test that will prove this one way or another?
Thanks so much!
OK, so we can test this. In one session, create the table, a procedure which queries it in an infinite loop, and run the procedure:
create table my_dependent_table (
c1 int
);
create procedure infinite_loop
is
v_record_count number;
begin
loop
select count(*) into v_record_count
from my_dependent_table;
end loop;
end infinite_loop;
/
exec infinite_loop;If you try and compile infinite_loop in another session, it'll hang, stuck on "library cache pin":
alter procedure infinite_loop compile;
If, in yet another session, you try and create a modified version of get_record_count, you'll see it hangs on "library cache lock":
create procedure get_record_count
is
v_record_count number;
begin
select count(*) into v_record_count
from my_dependent_table;
dbms_output.put_line(v_record_count || ' records');
infinite_loop; -- calling running procedure
end get_record_count;
/
So clearly it's possible you could run into issues when creating a procedure.
But how often to you create a procedure that accesses another procedure that someone else is waiting to compile? Almost never. I hope.
So let's start again. Kill the second and third sessions and reconnect. So you only have one session executing infinite_loop.
This time create another procedure that creates/drops get_record_count in an infinite loop. Then execute the procedure:
create procedure infinite_loop_create
is
v_record_count number;
begin
loop
execute immediate q'!create procedure get_record_count
is
v_record_count number;
begin
select count(*) into v_record_count
from my_dependent_table;
dbms_output.put_line(v_record_count || ' records');
infinite_loop;
end get_record_count;!';
execute immediate 'drop procedure get_record_count';
end loop;
end infinite_loop_create;
/
exec infinite_loop_create;At this point you have two sessions. One querying my_dependent_table in an infinite loop. Another creating/dropping get_record_count forever. These should be merrily chugging along.
You can verify that infinite_loop_create is creating/dropping get_record_count by querying user_objects in another session. You should see this procedure disappear/reappear with an increasing date in created.
Now in a third sesison run the following:
SQL> create or replace function wait_fn ( p int )
2 return int as
3 begin
4 dbms_lock.sleep ( 1 ) ;
5 return p;
6 end wait_fn;
7 /
Function created.
SQL> set timing on
SQL> insert into my_dependent_table
2 select wait_fn ( level ) from dual
3 connect by level <= 5;
5 rows created.
Elapsed: 00:00:05.43
SQL> update my_dependent_table set c1 = c1 + 1
2 where c1 = wait_fn ( c1 ) ;
5 rows updated.
Elapsed: 00:00:05.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.38
SQL>
SQL> select * from my_dependent_table;
C1
----------
2
3
4
5
6
Elapsed: 00:00:00.47
SQL>
SQL> delete my_dependent_table
2 where c1 = wait_fn ( c1 ) ;
5 rows deleted.
Elapsed: 00:00:05.50
SQL>
SQL> select * from my_dependent_table;
no rows selected
Elapsed: 00:00:00.32
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.31
SQL>
SQL> alter table my_dependent_table add c2 int;
Table altered.
Elapsed: 00:00:00.37
SQL>
SQL> desc my_dependent_table
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
C1 NUMBER(38)
C2 NUMBER(38)
SQL>
SQL> lock table my_dependent_table in exclusive mode;
Table(s) Locked.
Elapsed: 00:00:00.31Wait_fn is to ensure that the DML statements take a few seconds to execute. So if creating/droppping get_record_count lead to library cache issues we should see them.
But I didn't. And all the statements took the expected amount of time (just over 5s or "instant"). And there were no library cache waits recorded in ASH or elsewhere.
So, we can:
- Run all DML against my_dependent_table
- Alter it
- Lock it
And in the third session, you can even run:
SQL> drop table my_dependent_table cascade constraints purge;
Table dropped.
Elapsed: 00:00:00.46
At which point the other two sessions will fail:
SQL> exec infinite_loop_create;
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "CHRIS.INFINITE_LOOP_CREATE", line 7
ORA-06512: at line 1
SQL> exec infinite_loop;
BEGIN infinite_loop; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "CHRIS.INFINITE_LOOP", line 7
ORA-06512: at line 1
So yes, it's possible you could run into library cache locking issues when creating new objects. There may be some other cases I haven't though of. And maybe things were different in older verions (I tested on 11.2.0.4 & 12.2.0.1). But in practice it's unlikely!