운영DBA가 보는 지연된 블록 클린아웃(Delayed Block Cleanout)

지난 6월 말에 네이버 카페(Debian)에 아래의 질문이 올라왔다.
https://cafe.naver.com/dbian/7976

대용량 데이타 Insert  개선 점

위의 게시물에 댓글을 달고 이 이슈내용이 계속 머리에 맴돌아 몇 가지 테스트를 해본 내용을 적어봅니다.

시나리오는 다음과 같습니다.

* 테스트 테이블 : 144Mb 사이즈 테이블(tb_batch) 준비
* 테스트 테이블 : tb_batch1 준비(tb_batch와 동일 구조. 0건)
* 현재 db_cache_size 사이즈 파악 : 약 1.5Gb(sga 2g)
* 각 테스트마다 db를 restart함.

1. sga사이즈 2Gb에서 테스트
  1.1 conventional path 로 tb_batch1에 insert : insert ... select from tb_batch;
      select count(*) from tb_batch1;을 2회 수행하여 각각 autotrace 확인
  1.2 direct path 로 tb_batch1에 insert : insert /*+ append */ ... select from tb_batch;
      select count(*) from tb_batch1;을 2회 수행하여 각각 autotrace 확인
2. sga사이즈 500Mb에서 테스트
  2.1 conventional path 로 tb_batch1에 insert : insert ... select from tb_batch;
      select count(*) from tb_batch1;을 2회 수행하여 각각 autotrace 확인
  2.2 direct path 로 tb_batch1에 insert : insert /*+ append */ ... select from tb_batch;
      select count(*) from tb_batch1;을 2회 수행하여 각각 autotrace 확인

테스트의 결론은
2.1에서만 Delayed Block Cleanout이 발생합니다. 그 이유는 한번의 트랜잭션 양이 db_cache_size의 10%를 넘기 때문입니다.
나머지 경우는 Fast Block Cleanout(또는 Fast Commit Cleanout)이 됩니다.

테스트를 진행한 이유는 오라클DB를 만진 지 상당히 오래되었는데 현실세계에서 Delayed Block Cleanout을 경험해보지 못한 것 같아서입니다.
경험해 보지 못한 것일수도 있고 발생했지만 몰랐을 수도 있을 것 같습니다.
역설적으로 그만큼 문제의 원인이 Delayed Block Cleanout인 경우는 그 원인을 파악하기 어려운 것 같습니다. Tom Kyte형님도 Expert Oracle Database(제이펍 번역책이 있습니다)에서 밝히신 것처럼 처음에는 왜 그런지 몰랐다고 얘기하실 정도니까요.(재현이 잘 안 되는 게 가장 큰 이유죠...)
테스트를 진행한 내용을 정리하면 다음과 같습니다.

1. 현실 세계에서 잘 발생하지 않는 이유는 db_cache_size가 보통 넉넉하기 때문이라고 생각합니다.
  테스트 시나리오를 보면 sga 사이즈를 확 줄여야(또는 변경 양을 많게 해야) 나타나기 때문입니다.
  조시형 선생님의 성능고도화 1권 77페이지 중간.
  (1) Delayed 블록 클린아웃
  '트랜잭션이 갱신한 블록 개수가 총 버퍼 캐시 블록 개수의 1/10을 초과 할때 사용하는 방식이다'라고 첫.번.째.줄.에 나옵니다.
  즉, 하나의 트랜잭션의 사이즈(블록 수)가 db_cache_size의 10% 이상일때 + Conventional Path로 변경했을 때 나타나는 현상입니다.
  
  그리고 또 하나 Tom Kyte형님이 언급하신 것 처럼 AMM기능으로 운영할 경우 db_cache_size가 자동으로 관리되기 때문일 수도 있을 것 같습니다.
  AMM을 사용할 경우 대량의 데이터 변경이 있을 때 db_cache_size가 늘어나 Delayed Block Cleanout이 더이상 발생하지 않는지?도 테스트가 필요할 것 같습니다.
  아마 최초 한번은 발생하겠지만 db_cache_size가 늘어나면 그다음에는 발생하지 않을 테니까요. 그렇게 되면 또 모른 채로 넘어가겠죠.
이 부분은 추후 정확한 테스트가 필요할 것 같습니다.

2. 그래서 발생한다면 어떻게 해야 하는가?
  제가 갖고 있는 책들 중 이 부분에 가장 많은 설명을 할애하고 있는 책은 Tom Kyte형님의 Expert Oracle Database였습니다.(그 다음이 성능고도화 ^^)
  Tom Kyte형님의 Expert Oracle Database(한글명 : 전문가를 위한 오라클 데이터베이스 아키텍처, 제이펍) 책에는 422페이지부터 Block Cleanout 내용입니다.
  핵심은 대량의 데이터를 변경한 그 세션에서 Block Cleanout을 해야 한다.
  Tom Kyte형님은 다른 세션에서 Delayed Block Cleanout을 경험하지 않도록 하기 위해 대량 데이터를 변경한 그 세션에서 트랜잭션이 발생한 블록들을 방문하도록 해야 한다고 나옵니다.
  그 방법으로 통계정보를 수집(dbms_stat.gather_table_stats)을 수행(serially)할 것을 얘기하고 있습니다.
  
3. 저의 생각
  이전에는 발생하지 않던 DB에서 이 상황이 발생한 것이라면 이 DB에서 발생하고 있는 데이터의 양이 점점 커지고 있으며 이에 대한 DB차원의 조치가 필요함을 나타내는 신호로 받아들여야 한다고 생각합니다.
  이에 따라 저는 sga사이즈를 늘려주는것이 가장 바람직하다고 봅니다.
  'Delayed Block Cleanout이 발생하는구나, 테이블 전체를 스캔하도록 해야겠다'로 끝날게 아니라 Delayed Block Cleanout이 발생하지 않도록 하는데 초점을 두어야 한다는 것입니다.
  Delayed Block Cleanout이 발생하지 않도록 목표를 잡는다면 튜닝 포인트는 선택지가 좀 더 늘어납니다.
  . sga 사이즈 늘리는 것을 고려
  . 수행하는 배치의 데이터 변경 양을 줄일수 있는지 고려(배치가 아닌 곳에서 이 상황이 생긴다면 문제가 심각하다고 봅니다)
  . Direct path로 수행가능한지 고려 : 테이블 락이 발생할 수 있으니 시간대등을 확인
  . 전체 DB의 사이즈가 늘어난 것이므로 테이블 리오그(Reorganization) 고려

추가.
이 내용으로 봤을때 위 게시물의 글쓴이 님의 경우 insert를 append 힌트로 direct path로 동작하도록 사용한 것이라 그 원인이 Delayed Block Cleanout 이 아닌 것으로 보입니다.
그렇다면 원인이 뭘까요?

시나리오의 테스트 결과(set autotrace on)
테스트 결과 전체 내용은 파일로 첨부합니다.

Summary.sql

0.02MB

================================================
=====>>> 1.1. conventional path insert test (2G SGA)
07:49:14 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
2 recursive calls
1 db block gets
17918 consistent gets
0 physical reads
184 redo size <<<<<<<<<<<<<<<----------------- No(Few...) redo generation on 1st Select : buffer cache size is big enough :-)
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

07:49:18 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17761 consistent gets
0 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 2nd Select : buffer cache size is big enough :-)
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

=====>>> 1.2. direct path insert test (2G SGA)
07:51:18 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17732 consistent gets
17728 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 1st Select : direct path operation
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

07:51:22 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17731 consistent gets
17728 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 2nd Select : direct path operation
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
=====>>> 2.1. conventional path insert test (500M SGA)
07:34:13 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
131 recursive calls
1 db block gets
32229 consistent gets
3909 physical reads
1252248 redo size <<<<<<<<<<<<<<<----------------- Occured Delayed Block CleanOut on 1st Select
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

07:34:17 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
165 recursive calls
0 db block gets
18055 consistent gets
0 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 2nd Select
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
54 sorts (memory)
0 sorts (disk)
1 rows processed

=====>>> 2.2. direct path insert test (500M SGA)
07:45:15 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
110 recursive calls
0 db block gets
17806 consistent gets
17728 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 1st Select(Direct path)
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

07:45:19 SYS@ora19c> select count(*) from han_tst.tb_batch1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17731 consistent gets
17728 physical reads
0 redo size <<<<<<<<<<<<<<<----------------- No redo generation on 2nd Select(Direct path)
553 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed