Library Cache Lock _ 같은 session에서 procedure 실행 후 wait event 확인

■ Library Cache Lock
1. Library Cache Lock은 Oracle 데이터베이스에서 사용되는 Lock의 일종으로, Library Cache Object(LCO)를 접근하거나 변경하는 경우 해당 LCO 에 대한 특정한 handle에 대해 획득하는 lock을 획득하는 것이다. 

2. 여러 세션에서 동시에 동일한 LCO를 변경하는 것을 방지하기 위해서 사용한다.
3. Library cache lock 을 획득하지 못해 대기하는 경우, "library cache lock wait" event 가 발생한다.

참고) SELECT문 , DML, DDL 실행 : 해당 LCO를 변경하는 경우 handle-LCO-lock 이 획득된다.

(예시) 
1. SQL문장 hard parsing라면?

▶ library cache lock → exclusive mode 획득된다. 
- LCO를 생성하는 과정 → exclusive mode → Lock 획득 → 다른 session + 동시에 SQL문장 변경 불가. →  Hard Parsing 이후 LCO 생성 → library cache lock  생성 → null mode 변환

 

2. SQL문 execute 단계

▶ library cache lock → shared mode 획득된다.

- 다수의 session, 동시에 동일한 SQL문장 실행 가능

3. SQL문(SELECT문) fetch 단계 

▶ library cache lock → null mode 획득된다.

- LCO의 구조를 변경하지 않기 때문에 null mode로의 Lock 획득 가능.

4. ALTER문 추가 작업 이라면?

▶ library cache lock → exclusive mode 획득된다.

- 동시에 여러 session에서 변경되지 않도록 한다.

5. CREATE or REPLACE PROCEDURE(또는 FUNCTION, PACKAGE) 이라면?  

▶ library cache lock → exclusive mode 획득된다.

- 객체의 구조 변경 작업으로, 다른 session이 동시에 변경 불가.

 

▷ Library Cache Lock의 획득 모드 : SQL문장이나 객체의 변경 유형에 따라 달라지며, 적절한 Lock 획득은 데이터베이스의 일관성과 무결성을 유지하는데 중요하다.

[PL/SQL문]
<<sess_1>>

execute dbms_application_info.set_client_info('sess_1')

<<sess_2>>

execute dbms_application_info.set_client_info('sess_2')

 

<<sys_1>>

select client_info, sid from v$session where client_info in ('sess_1', 'sess_2');

 

#) session 대기 이벤트 확인

sid(session 식별자) , event(대기이벤트 이름), wait_time(세션이 대기한 시간/마이크로초), seconds_in_wait(세션이 대기한 시간/초단위), state(상태)

select sid, event, wait_class, wait_time, seconds_in_wait, state 
from v$session_wait 
where sid in (25, 148);

<<sys_2>> 
#) 통계 정보 확인

total_waits(이벤트가 발생한 횟수), time_waited(세션이 해단 이벤트에 대한 대기한 총 시간,100분의 1초)

select sid, event, total_waits, time_waited from v$session_event where sid in (25, 148);

#) 동시에 쿼리문 실행해야 한다!

<<sess_1>> : HR 계정

BEGIN
    FOR i IN 1..10000 LOOP
        EXECUTE IMMEDIATE 'create or replace procedure p1 is begin null; end;';
    END LOOP;
END;
/

<<sess_2>> : HR 계정

BEGIN
    FOR i IN 1..10000 LOOP
        EXECUTE IMMEDIATE 'create or replace procedure p1 is begin null; end;';
    END LOOP;
END;
/

<<sys_1>>

select sid, event, wait_class, wait_time, seconds_in_wait, state from 
v$session_wait 
where sid in (25, 148);

<<sys_2>>

=> library cache: mutex X(배타적모드)

select sid, event, total_waits, time_waited from v$session_event where sid in (25, 148);

 

<<sys_1>> 

#) sid 에 해당하는 SQL_ID 확인 

select prev_sql_id from v$session where sid in (25, 148);

 

#) SQL TEXT 확인

select sql_text from v$sql where sql_id = '1bvbq65q6yhc9';