테이블 조회 시 다른 세션에서도 uncommit block 을 읽을까?

DB 버전 : Oracle Database 11.2.0.1

요약 :

테이블에 commit 되지 않은 데이터가 있고 다른 세션에서 해당 테이블 조회 시 commit 되지 않은 데이터를 읽어 올까?

• INSERT 만 발생시키고 uncommit 상태에서 다른 세션을 통한 데이터 조회 시 uncommit block 조회 하는 것 확인 됨

RAC 환경에서 commit 되지 않은 데이터를 가진 테이블을 다른 노드에서 조회 시 어떻게 동작 할까?

• RAC 환경에서 LMS 프로세스가 uncommit block 다른 노드로 전달 해줌
• 다른 노드에서 두번째 조회 시 buffers 가 올라 가는 것으로 봐서 최초 조회 시 local undo 에 저장 후 재 조회 시 사용 하는 것으로 보임

LMS(Global cache service background process) LMS 는 RAC 노드들 사이에서 데이터 블럭들을 전송해 주는 역할을 한다. 이 백그라운드 프로세스의 일반적인 logical read 부하는 초당 100 블럭을 넘지 않는다. 초당 수십만 블럭을 액세스 하면 무언가 잘못된 것이고 시스템은 버티기 어려워 진다.

 

insert 수행 uncommit 상태에서 데이터 조회 시 uncommit block 조회 테스트

## session 1
SQL> create table t1 (c1 varchar(10), c2 varchar(10)) ;
SQL> insert into t1 values(1,'a');
SQL> insert into t1 values(2,'a');
SQL> commit ;

SQL> select * from t1 ;
C1        C2
------------------------------ ------------------------------
1        a
2        a

SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL ;
Session altered.

SQL> select * from t1 ;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST'));
C1        C2
------------------------------ ------------------------------
1        a
2        a

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows | A-Time  | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |    |  2 (100)|    |   2 |00:00:00.01 |  4 |
|   1 |  TABLE ACCESS FULL| T1  | 1 |  2 | 28 |  2   (0)| 00:00:01 |   2 |00:00:00.01 |  4 |
--------------------------------------------------------------------------------------------------------------------

## session 2
SQL> insert into t1 select level, 'a' from dual connect by level <= 2000000 ;
2000000 rows created.
-- commit 안함

## session 1
SQL> select * from t1 ;
C1        C2
------------------------------ ------------------------------
1        a
2        a

-- uncommit 블록까지 조회 함
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows | A-Time  | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |    |  2 (100)|    |   2 |00:00:00.26 |   19780 |
|   1 |  TABLE ACCESS FULL| T1  | 1 |  2 | 28 |  2   (0)| 00:00:01 |   2 |00:00:00.26 |   19780 |
--------------------------------------------------------------------------------------------------------------------

반응형

RAC 환경 다른 노드에서 데이터 조회시 LMS 프로세스 stat 변화

-- rac1 lms 프로세스 stat
select *
from v$sesstat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and sid = 131
and b.name like '%read%'
and b.name in (
'session logical reads'
,'data blocks consistent reads - undo records applied'
,'no work - consistent read gets'
,'cleanouts and rollbacks - consistent read gets'
)
;
       SID STATISTIC#    VALUE STATISTIC# NAME CLASS  STAT_ID
---------- ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ----------
       131    11    59283  11 session logical reads     1 3143187968
       131   264    26776 264 data blocks consistent reads - undo records applied    128 2915445793
       131   265    13596 265 no work - consistent read gets   128 2814375799
       131   268     8965 268 cleanouts and rollbacks - consistent read gets   128 1083903169


## rac2 session 에서 select 
SQL> select * from t1 ;
C1        C2
------------------------------ ------------------------------
1        a
2        a

-- 1번 에서 조회보다 적은 buffer read, lms 프로세스로 부터 undo 데이터를 받아 그런 것
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows | A-Time  | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |    |  1081 (100)|    |   2 |00:00:00.80 |    4895 |
|   1 |  TABLE ACCESS FULL| T1  | 1 |  1 | 14 |  1081   (1)| 00:00:13 |   2 |00:00:00.80 |    4895 |
--------------------------------------------------------------------------------------------------------------------

-- rac1 lms 프로세스 stat 변화량, 1번 노드의 언두 데이터를 조회해서 2번 노드로 보내 줌
SQL> /
       SID STATISTIC#    VALUE STATISTIC# NAME CLASS  STAT_ID
---------- ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ----------
       131    11    79194  11 session logical reads     1 3143187968
       131   264    38588 264 data blocks consistent reads - undo records applied    128 2915445793
       131   265    13656 265 no work - consistent read gets   128 2814375799
       131   268    12943 268 cleanouts and rollbacks - consistent read gets   128 1083903169

## rac2 session 에서 두번째 select
-- buffer read 가 1번 노드와 같아지고 disk read 가 있음, local undo 에 저장해서 읽어 오는 듯 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows | A-Time  | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |    |  1081 (100)|    |   2 |00:00:01.64 |   19780 |   3185 |
|   1 |  TABLE ACCESS FULL| T1  | 1 |  1 | 14 |  1081   (1)| 00:00:13 |   2 |00:00:01.64 |   19780 |   3185 |
-----------------------------------------------------------------------------------------------------------------------------

 

참고

https://engineering-skcc.github.io/oracle%20tuning/LMS%EA%B0%80-%ED%8A%84%EB%8B%A4/