79 Shared Pool, Library Cache

# 요약
	■ Library Cache Lock ■ Library Cache Pin ■ Shared Pool Latch ■ Library Cache Latch ■ Version Count ■ Row Cache Lock

---

■ Library Cache Lock
- Library Cache Lock은 LCO(Library Cache Object)를 접근하거나 변경하는 경우 handle에 대해 획득하는 LOCK이다.
- 여러 세션에서 동시에 동일한 LCO를 변경하는 것을 방지하기 위해서 사용한다.
- Library Cache Lock을 획득하지 못해 대기하는 경우 Library Cache Lock wait event가 발생한다.

동일한 문장이 없는 경우
- SQL Hard Parsing : Library cache lock exclusive mode로 획득해야 한다.
                    LCO를 생성하면 Library cache lock NULL mode로 변환한다.
- SQL문 execute 단계 : Library cache lock shared mode로 획득해야 한다.
- SELECT문 fetch 단계 : Library cache lock NULL mode로 획득해야 한다.
- alter문(DDL, 구조변경 등) : Library cache lock exclusive mode로 획득해야 한다.
- create or replace procedure (function, package 등 생성) : Library cache lock exclusive mode로 획득해야 한다.

<< 예시 1 >>
	<< sess_1 : HR >> HR@ora11g> execute dbms_application_info.set_client_info('sess_1') << sess_2 : HR >> HR@ora11g> execute dbms_application_info.set_client_info('sess_2') << sess_3 : SYS >> SYS@ora11g> select client_info, sid from v$session where client_info in ('sess_1','sess_2'); CLIENT_INFO              SID -------------------- ------- sess_2                    27 sess_1                   180 SYS@ora11g> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (27, 180);        SID EVENT                          WAIT_CLASS  WAIT_TIME SECONDS_IN_WAIT STATE ---------- ------------------------------ ---------- ---------- --------------- --------------------         27 SQL*Net message from client    Idle                0             459 WAITING        180 SQL*Net message from client    Idle                0             466 WAITING SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid in (27, 180);        SID EVENT                                                            TOTAL_WAITS TIME_WAITED ---------- ---------------------------------------------------------------- ----------- -----------         27 log file sync                                                              1           2         27 SQL*Net message to client                                                 13           0         27 SQL*Net message from client                                               12        2402        180 SQL*Net message to client                                                 12           0        180 SQL*Net message from client                                               11        2327 - TOTAL_WAITS : 횟수 - TIME_WAITED : 1/100초
# 작업 수행
	<< sess_1 : HR >> + << sess_2 : HR >> : 동시 수행 HR@ora11g> begin     for i in 1..10000 loop         execute immediate 'create or replace procedure p1 is begin null; end;';     end loop; end; / - execute immediate 다이나믹 ddl 문 사용 가능 - begin null; end;  내용이 없으면 null이라도 넣어야 한다 << sess_3 : SYS >> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (27, 180);     SID EVENT                               WAIT_CLASS       WAIT_TIME SECONDS_IN_WAIT STATE ------- ----------------------------------- --------------- ---------- --------------- --------------------      27 library cache lock                  Concurrency             -1               0 WAITED SHORT TIME     180 library cache lock                  Concurrency             -1               0 WAITED SHORT TIME >> 실시간으로  상태 확인. 계속 실행하여 이벤트를 확인.
# 로그 확인
	SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid in (27, 180);        SID EVENT                                                            TOTAL_WAITS TIME_WAITED ---------- ---------------------------------------------------------------- ----------- -----------         27 Disk file operations I/O                                                   1           0         27 log file sync                                                              4          13         27 db file sequential read                                                  165           1         27 latch: row cache objects                                                   4           0         27 cursor: pin S                                                              2           1         27 latch: shared pool                                                         3           0         27 library cache lock                                                     10157         324         27 library cache: mutex X                                                    31          51         27 SQL*Net message to client                                                 21           0         27 SQL*Net message from client                                               20      127831         27 SQL*Net break/reset to client                                             10           0         27 events in waitclass Other                                                  2           0        180 Disk file operations I/O                                                   3           0        180 log file switch completion                                                 1          29        180 log file sync                                                              3         138        180 db file sequential read                                                  227          49        180 db file scattered read                                                     1           0        180 latch: row cache objects                                                   4           0        180 cursor: pin S                                                              1           8        180 latch: shared pool                                                         1           0        180 library cache lock                                                      9818         316        180 library cache: mutex X                                                    15          74        180 SQL*Net message to client                                                 17           0        180 SQL*Net message from client                                               16      128097        180 SQL*Net break/reset to client                                              4           0        180 events in waitclass Other                                                  2           0 >> 접속 ~ 로그아웃 전까지 누적 정보 - mutex : latch보다 짧은 용도의 lock 개념 - X : exclusive mode - library cache: mutex X : 동시에 잡을수 없기에 이벤트 발생 - row cache objects = data dictionary cache select sid, prev_sql_id from v$session where sid in (27, 180);     SID PREV_SQL_ID ------- -------------      27 39unwnghvw0vs     180 39unwnghvw0vs select sql_text from v$sql where sql_id = '39unwnghvw0vs'; SQL_TEXT -------------------------------------------------------------------------------------------------------------- begin     for i in 1..10000 loop         execute immediate 'create or replace procedure p1 is begin null; end; ';     end loop; end; > 이벤트가 발생한 SQL문 확인

<< 예시 2 >>
	<< sess_1 : HR >> HR@ora11g> create table hr.test( id number, name char(10)); Table created. HR@ora11g> insert into hr.test select rownum, lpad(rownum,10,'0') from dual connect by level <= 1000000; 1000000 rows created. HR@ora11g> commit; Commit complete. HR@ora11g> execute dbms_application_info.set_client_info('sess_1') PL/SQL procedure successfully completed. << sess_2 : HR >> HR@ora11g> execute dbms_application_info.set_client_info('sess_2') PL/SQL procedure successfully completed. << sess_3 : SYS >> SYS@ora11g> select client_info, sid from v$session where client_info in ('sess_1','sess_2'); CLIENT_INFO              SID -------------------- ------- sess_1                    22 sess_2                   180
# 작업 수행
	<< sess_1 : HR >> HR@ora11g> alter table hr.test modify name char(20); << sess_2 : HR >> HR@ora11g> select count(*) from hr.test; << sess_3 : SYS >> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (22, 180);     SID EVENT                               WAIT_CLASS       WAIT_TIME SECONDS_IN_WAIT STATE ------- ----------------------------------- --------------- ---------- --------------- --------------------      22 log buffer space                    Configuration            0               0 WAITING >> 실시간 계속 확인해보기 # library cache lock 유발하는 SQL 문장 확인 : 실시간 유발 시점에만 확인 가능 select  h.sid,         substr(s.sql_text, 1 , 40) as sql_text,         k.kglhdnsp,         K.kglnaobj,         decode(k.kgllkmod, 3 , '3(X)', 2, '2(S)' , 1, '1(N)', k.kgllkmod) as lkmode from sys.x$kgllk k,v$session_wait w, v$session h, v$sqlarea s where w.event = 'library cache lock' and h.saddr = k.kgllkuse and h.sql_id = s.sql_id(+) and k.kgllkhdl = ( case when w.p1raw like '00000000%' then substr(w.p1raw,9,8) else w.p1raw ||'' end) and k.kgllkmod > 0;     SID SQL_TEXT                                                       KGLHDNSP KGLNAOBJ   LKMODE ------- ------------------------------------------------------------ ---------- ---------- ----------      22 alter table hr.test                                                   1 TEST       3(X)
# 로그 확인
	<< sess_3 : SYS >> SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid in (22, 180);     SID EVENT                               TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- -----------      22 Disk file operations I/O                    419         135      22 Data file init write                        377          19      22 control file sequential read               3071           1      22 control file parallel write                 581         952            22 log buffer space                            186        2199    > 리두 발생      22 log file switch (checkpoint incomplete)      20        2581      22 log file switch completion                   14         176             22 db file sequential read                   37343         571    > 메모리에 없어 디스크를 읽는 io작업이 많았다      22 db file scattered read                        6           4      22 db file single write                         83          22            22 flashback log file sync                      85         237      22 SQL*Net message to client                    14           0      22 SQL*Net message from client                  13       49352      22 events in waitclass Other                  1134        1150     180 Disk file operations I/O                      1           0     180 log file sync                                 1           0     180 db file sequential read                      27           3     180 db file scattered read                       68           6     180 db file parallel read                         1           7     180 library cache lock                            1       10848 v     180 SQL*Net message to client                    15           0     180 SQL*Net message from client                  14       45650 - sequential  블럭 한개씩 읽겠다, 싱글 블럭 IO발생 - scattered   멀티블럭IO - parallel    병렬 select sid, prev_sql_id from v$session where sid in (22, 180);     SID PREV_SQL_ID ------- -------------      22 67qv1fwhkjmh6     180 1mat065c25crk SYS@ora11g> select sql_id,sql_text from v$sql where sql_id in ('67qv1fwhkjmh6','1mat065c25crk'); SQL_ID        SQL_TEXT ------------- ----------------------------------------------------------------------------------------------- 1mat065c25crk select count(*) from hr.test >> 22번 안나옴 : 놓치는 경우도 있다..

---

■ Library Cache Pin
SQL문 Hard Parsing : Library Cache Pin을 exclusive 획득한 후 실행계획 생성
SQL문 execute 단계 : Library Cache Pin을 shared 획득
SQL문 fetch 단계 : Library Cache Pin을 해제한다.
Procedure(function, package) 실행 단계 : Library Cache Pin을 shared 획득
alter Procedure(function, package) ... compile 단계 : Library Cache Pin을 exclusive 획득

<< 예시 3 >>
	HR@ora11g> create or replace procedure pin_proc (p_time in number) is begin     dbms_lock.sleep(p_time); end; / > 입력한 숫자만큼 대기 Warning: Procedure created with compilation errors. HR@ora11g> show err Errors for PROCEDURE PIN_PROC: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/5      PL/SQL: Statement ignored 4/5      PLS-00201: identifier 'DBMS_LOCK' must be declared SYS@ora11g> grant execute on dbms_lock to hr; >> SYS에서 권한 부여 후 실행 << sess_1 : HR >> execute dbms_application_info.set_client_info('sess_1') << sess_2 : HR >> execute dbms_application_info.set_client_info('sess_2')
# 작업 수행
	<< sess_1 : HR >> execute pin_proc(100) << sess_2 : HR >> alter procedure pin_proc compile; >> 동시 수행 << sess_3 : SYS >> select client_info,sid,blocking_session, event from v$session where client_info in ('sess_1','sess_2'); CLIENT_INFO              SID BLOCKING_SESSION EVENT -------------------- ------- ---------------- ----------------------------------- sess_2                    26              180 library cache pin sess_1                   180                  PL/SQL lock timer select  h.sid,         substr(s.sql_text, 1 , 40) as sql_text,         k.kglhdnsp,         K.kglnaobj,         decode(n.kglpnmod, 3 , '3(X)', 2, '2(S)' , 1, '1(N)', n.kglpnmod) as lkmode from sys.x$kgllk k,sys.x$kglpn n, v$session_wait w, v$session h, v$sqlarea s where h.saddr = n.kglpnuse and k.kglhdpar = n.kglpnhdl and h.sql_id = s.sql_id(+) and n.kglpnhdl = ( case when w.p1raw like '00000000%' then substr(w.p1raw,9,8) else w.p1raw ||'' end) and k.kgllkmod > 0;     SID SQL_TEXT                                             KGLHDNSP KGLNAOBJ        LKMODE ------- -------------------------------------------------- ---------- --------------- ---------------     180 BEGIN pin_proc(100); END;                                   1 PIN_PROC        2(S)      26 alter procedure pin_proc compile                            1 PIN_PROC        0     180 BEGIN pin_proc(100); END;                                   1 PIN_PROC        2(S)      26 alter procedure pin_proc compile                            1 PIN_PROC        0
# 로그 확인
	<< sess_4 : SYS >> select sid, event, total_waits, time_waited from v$session_event where sid in (26, 180);     SID EVENT                               TOTAL_WAITS TIME_WAITED ------- ----------------------------------- ----------- -----------      26 Disk file operations I/O                      2           0      26 log file sync                                 2           2      26 db file sequential read                       4           2      26 library cache pin                             1        9963      26 SQL*Net message to client                    14           0      26 SQL*Net message from client                  13       31449     180 Disk file operations I/O                      1           0     180 log file sync                                 1           0     180 db file sequential read                       1           2     180 SQL*Net message to client                    14           0     180 SQL*Net message from client                  13       32730     180 PL/SQL lock timer                             1       10004 << sess_3 : SYS >> select sid, prev_sql_id from v$session where sid in (26, 180);     SID PREV_SQL_ID ------- -------------      26 49xcprxxbqnta     180 1sjptmvq84ksx select sql_id,sql_text from v$sql where sql_id in ('49xcprxxbqnta','1sjptmvq84ksx'); SQL_ID        SQL_TEXT ------------- -------------------------------------------------- 1sjptmvq84ksx BEGIN pin_proc(100); END; 49xcprxxbqnta alter procedure pin_proc compile

- Library Cache Latch : library cache 영역에서 내가 수행한 SQL문 또는 PLSQL을 찾을 때 사용한다.
                        탐색을 동기화하는데 사용.

- Library Cache Lock, Library Cache Pin : handle, LCO에 대해 보호하는 역할에 사용.

---

■ Shared Pool Latch
- hard parsing이 발생하는 경우 shared pool의 library cache메모리를 할당 받기위해서 shared pool latch를 획득해야 한다.
- 9i버전부터는 shared pool을 여러개의 서브풀로 최대 7개까지 나눠서 관리하며 오라클은 CPU개수가 4개 이상이고 
    shared pool 크기가 250M 이상인 경우 _kghdsidx_count의 값만큼 서브풀을 생성해서 관리한다.
    shared pool latch의 경합을 줄이기 위해서다.

select name, gets from v$latch_children where name = 'shared pool'; NAME                                                GETS --------------------------------------------- ---------- shared pool                                           58 shared pool                                           58 shared pool                                           58 shared pool                                           58 shared pool                                           58 shared pool                                           58 shared pool                                      5122375 ㄴ 서브풀 7개 show parameter cpu_count NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ cpu_count                            integer     2 SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_kghdsidx_count'; PARAMETER                      SESSION_VALUE        INSTANCE_VALUE ------------------------------ -------------------- -------------------- _kghdsidx_count                1                    1

---

■ Library Cache Latch
- 동일한 SQL문, PLSQL문이 shared pool 안에 library cache에 있는지 탐색하는 latch
- CPU 수에 종속된다.
- latch : library cache 

---

■ Version Count

<< 예시 4 >>

<< SYS >> alter system flush shared_pool; alter system flush shared_pool; << HR >> HR@ora11g> var name varchar2(10) HR@ora11g> exec :name := 'King' PL/SQL procedure successfully completed. HR@ora11g> print name NAME -------------------------------- King select last_name, salary from hr.employees where last_name = :name; LAST_NAME                     SALARY ------------------------- ---------- King                           10000 King                           24000 HR@ora11g> var name varchar2(1000) > 동일한 바인드 변수에 사이즈만 다르게 다시 선언 > 개발자의 실수 HR@ora11g> exec :name := 'Grant' PL/SQL procedure successfully completed. HR@ora11g> print name NAME -------------------------------- Grant select last_name, salary from hr.employees where last_name = :name; LAST_NAME                     SALARY ------------------------- ---------- Grant                           2600 Grant                           7000 << SYS >> select sql_id, sql_text, version_count from v$sqlarea where sql_text like '%hr.employees%' and sql_text not like '%v$sqlarea%'; SQL_ID        SQL_TEXT                                           VERSION_COUNT ------------- -------------------------------------------------- ------------- gqgwd5rvu5ftu select last_name, salary from hr.employees where l             2                 ast_name = :name > SQL문은 1개 인데 version count 2 ? > child cursor가 만들어지는 57가지 이유 확인하기 select * from v$sql_shared_cursor; select address,child_address,child_number,bind_length_upgradeable from v$sql_shared_cursor where sql_id = 'gqgwd5rvu5ftu'; ADDRESS  CHILD_ADDRESS CHILD_NUMBER BIND_LENGTH_UPGRADEABLE -------- ------------- ------------ ----------------------- 31A8FA70 31AAB744                 0 N   --> 처음 만든 커서 31A8FA70 319488D0                 1 Y - BIND_LENGTH_UPGRADEABLE : 바인드 변수 length의 미스매치 select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu',0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID  gqgwd5rvu5ftu, child number 0 ------------------------------------- select last_name, salary from hr.employees where last_name = :name Plan hash value: 2077747057 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    12 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"=:NAME) select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu',1)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID  gqgwd5rvu5ftu, child number 1 ------------------------------------- select last_name, salary from hr.employees where last_name = :name Plan hash value: 2077747057 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    12 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"=:NAME) select address,child_address,datatype_string,max_length,value_string from v$sql_bind_capture where sql_id = 'gqgwd5rvu5ftu'; ADDRESS  CHILD_ADDRESS DATATYPE_STRING MAX_LENGTH VALUE_STRING -------- ------------- --------------- ---------- ---------------------------------------- 31A8FA70 319488D0      VARCHAR2(4000)        4000 Grant 31A8FA70 31AAB744      VARCHAR2(32)            32 King > version count가 높다 > 메모리 과사용 > ORA-04031 오류 발생할 수 있다. > version count가 높은데 내용이 안나온다? > 버그일 수도 있다 * 바인드변수 varchar2 size : 32, 128, 2000, 4000 4가지 크기로 결정된다.

<< 예시 5 >>

<< SYS >> alter system flush shared_pool; alter system flush shared_pool; << HR >> HR@ora11g> var name2 varchar2(4000)  > 사이즈를 크게 선언 HR@ora11g> exec :name2 := 'King' HR@ora11g> print name2 NAME2 -------------------------------- King select last_name, salary from hr.employees where last_name = :name2; LAST_NAME                     SALARY ------------------------- ---------- King                           10000 King                           24000 HR@ora11g> var name2 varchar2(32) > 사이즈 작게 선언 HR@ora11g> exec :name2 := 'Grant' PL/SQL procedure successfully completed. HR@ora11g> print name2 NAME -------------------------------- Grant select last_name, salary from hr.employees where last_name = :name2; LAST_NAME                     SALARY ------------------------- ---------- Grant                           2600 Grant                           7000 <<  SYS >> select sql_id, sql_text, version_count from v$sqlarea where sql_text like '%hr.employees%' and sql_text not like '%v$sqlarea%'; SQL_ID        SQL_TEXT                                           VERSION_COUNT ------------- -------------------------------------------------- ------------- aqdgv0cvz46v6 select last_name, salary from hr.employees where l             2               ast_name = :name2 select * from v$sql_shared_cursor where sql_id = 'aqdgv0cvz46v6'; select address,child_address,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id = 'aqdgv0cvz46v6'; ADDRESS  CHILD_ADDRESS CHILD_NUMBER BIND_MISMATCH -------- ------------- ------------ ------------- 31B36EF8 31B36CA0                 0 N 31B36EF8 31A85500                 1 Y >> 바인드 변수 선언의 순서를 바꿨을 뿐인데 사유가 다르다. select address,child_address,datatype_string,max_length,value_string from v$sql_bind_capture where sql_id = 'aqdgv0cvz46v6'; ADDRESS  CHILD_ADDRESS DATATYPE_STRING MAX_LENGTH VALUE_STRING -------- ------------- --------------- ---------- ---------------------------------------- 31B36EF8 31A85500      VARCHAR2(128)          128 Grant 31B36EF8 31B36CA0      VARCHAR2(4001)        4001 King select * from table(dbms_xplan.display_cursor('aqdgv0cvz46v6',0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID  aqdgv0cvz46v6, child number 0 ------------------------------------- select last_name, salary from hr.employees where last_name = :name2 Plan hash value: 2077747057 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    12 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"=:NAME2) select * from table(dbms_xplan.display_cursor('aqdgv0cvz46v6',1)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID  aqdgv0cvz46v6, child number 1 ------------------------------------- select last_name, salary from hr.employees where last_name = :name2 Plan hash value: 2077747057 ------------------------------------------------------------------------------------------- | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    12 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"=:NAME2)

---

■ Row Cache Lock
- dictionary cache(row cache)는 oracle dictionary 정보에 대한 cache 영역
- 유저, 테이블, 인덱스, 시퀀스, 컬럼, 함수, 프로시저, 패키지, 트리거, ...
- DDL 작업을 수행하면 딕셔너리 정보 입력, 수정, 삭제
- SQL문 수행시에 semantic, 권한 체크시에 딕셔너리 정보를 이용해서 체크한다.
- row cache lock은 dictionary object를 보호하는 시스템 lock 이다.

select pool,name,bytes from v$sgastat where name = 'row cache'; POOL         NAME                                               BYTES ------------ --------------------------------------------- ---------- shared pool  row cache                                        4323536 select cache#,type,parameter from v$rowcache;     CACHE# TYPE        PARAMETER ---------- ----------- ------------------------------          3 PARENT      dc_rollback_segments          1 PARENT      dc_free_extents          4 PARENT      dc_used_extents          2 PARENT      dc_segments          0 PARENT      dc_tablespaces          5 PARENT      dc_tablespace_quotas          6 PARENT      dc_files         10 PARENT      dc_users          8 PARENT      dc_objects         17 PARENT      dc_global_oids         12 PARENT      dc_constraints         13 PARENT      dc_sequences         16 PARENT      dc_histogram_defs         54 PARENT      dc_sql_prs_errors         32 PARENT      kqlsubheap_object         19 PARENT      dc_table_scns         18 PARENT      dc_outlines         14 PARENT      dc_profiles         47 PARENT      realm cache         48 PARENT      Command rule cache         49 PARENT      Realm Object cache         46 PARENT      Rule Set Cache         55 PARENT      DV Auth Cache         34 PARENT      extensible security user and rol         35 PARENT      extensible security principalpa         37 PARENT      extensible security UID to princ         36 PARENT      extensible security principalna         33 PARENT      extensible security principalne         38 PARENT      XS security class privilege         39 PARENT      extensible security midtier cach         43 PARENT      AV row cache 1         44 PARENT      AV row cache 2         45 PARENT      AV row cache 3         15 PARENT      global database name         20 PARENT      rule_info         21 PARENT      rule_or_piece         23 PARENT      dc_qmc_ldap_cache_entries         52 PARENT      qmc_app_cache_entries         53 PARENT      qmc_app_cache_entries         27 PARENT      qmtmrcin_cache_entries         28 PARENT      qmtmrctn_cache_entries         29 PARENT      qmtmrcip_cache_entries         30 PARENT      qmtmrctp_cache_entries         31 PARENT      qmtmrciq_cache_entries         26 PARENT      qmtmrctq_cache_entries          9 PARENT      qmrc_cache_entries         50 PARENT      qmemod_cache_entries         24 PARENT      outstanding_alerts         22 PARENT      dc_awr_control         25 PARENT      SMO rowcache         40 PARENT      sch_lj_objs         41 PARENT      sch_lj_oids          7 SUBORDINATE dc_users          8 SUBORDINATE dc_object_grants         16 SUBORDINATE dc_histogram_data         16 SUBORDINATE dc_histogram_data         19 SUBORDINATE dc_partition_scns          7 SUBORDINATE dc_users          7 SUBORDINATE dc_users         47 SUBORDINATE realm auth         49 SUBORDINATE Realm Subordinate Cache         21 SUBORDINATE rule_fast_operators

<< 예시 6 >>
	<< sess_HR 1 >> HR@ora11g> create sequence seq_1 nocache; HR@ora11g> exec dbms_application_info.set_client_info('sess_1') << sess_HR 2 >> HR@ora11g> exec dbms_application_info.set_client_info('sess_2') << SYS >> SYS@ora11g> select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'SEQ_1'; SEQUENCE_OWNER SEQUENCE_NAME  MIN_VALUE  MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER -------------- ------------- ---------- ---------- ------------ ---------- ---------- ---------- ----------- HR             SEQ_1                  1 1.0000E+28            1 N          N                   0           1
# 작업 수행
	<< sess_HR 1 >> + << sess_HR 2 >> 동시 수행 declare     v_value number; begin     for idx in 1..100000 loop         select seq_1.nextval into v_value from dual;     end loop; end; / << SYS >> 작업 수행 중에 상태 확인 가능 SYS@ora11g> select client_info,sid,blocking_session, event from v$session where client_info in ('sess_1','sess_2'); CLIENT_INFO              SID BLOCKING_SESSION EVENT -------------------- ------- ---------------- ----------------------------------- sess_2                    26                  row cache lock sess_1                   180               26 row cache lock select h.address, h.saddr, s.sid, h.lock_mode from v$rowcache_parent h, v$rowcache_parent w, v$session s where h.address = w.address and w.saddr = (select saddr from v$session where event = 'row cache lock' and rownum = 1) and h.saddr = s.saddr and h.lock_mode > 0; ADDRESS  SADDR        SID  LOCK_MODE -------- -------- ------- ---------- 34BE50BC 49924488     180          5
# 로그 확인
	select sql_text from v$sql where address = (select prev_sql_addr from v$session where sid = 26); SQL_TEXT -------------------------------------------------------------------------------------------------------------- declare     v_value number; begin     for idx in 1..100000 loop         select seq_1.nextval into v_value from  dual;     end loop; end; select sql_text from v$sql where address = (select prev_sql_addr from v$session where sid = 180); SQL_TEXT -------------------------------------------------------------------------------------------------------------- declare     v_value number; begin     for idx in 1..100000 loop         select seq_1.nextval into v_value from  dual;     end loop; end;

# Row Cache Lock 경합중에 Sequence Nocache 속성으로 인해 많이 발생한다.
- nextval 수행할 때 마다 Dictionary 정보를 변경하기 위해서 SSX(Shared Sub Exclusive) 모드를 획득해야 함으로 이때 경합이 발생한다.
    SSX(Shared Sub Exclusive) : LOCK_MODE : 5

- 해결방법 : cache 속성으로 수정해야 한다.
    create sequence seq_1 cache 100;
    alter sequence seq_1 cache 100;

- Cache 크기가 작을 경우 enq : SQ - contention wait event 발생

---

select * from v$session;
select * from v$session_wait;
select * from v$session_event;
select * from v$sql;
select * from v$sqlarea;
select * from sys.x$kgllk;
select * from sys.x$kglpn;
select * from v$latch_children;
select * from x$ksppi;
select * from x$ksppcv;
select * from x$ksppsv;
select * from v$sql_shared_cursor;
select * from v$sql_bind_capture
select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu',1));
select * from v$sgastat;
select * from v$rowcache;
select * from dba_sequences;
select * from v$rowcache_parent;
select * from v$rowcache_parent;



https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html#GUID-DE757E9C-3437-408A-8598-3EB4C8E2A3B0

Database Concepts

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-shared-pool-and-large-pool.html#GUID-7FD646B3-FCE3-4D3C-B988-C8D138FFD514%EF%BB%BF

Database Performance Tuning Guide

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/instance-tuning-using-performance-views.html

Database Performance Tuning Guide