Consistent gets 와 physical reads

db block gets와 consistent gets 오라클자바커뮤니티(ojc.asia) 2015. 1. 23. 16:32 이미 아시겠지만 db block gets와 consistent gets는 Logical Read를 나타냅니다. 두개를 더한 값 과 Pysical Reads를 비교해서 Hit Ratio를 구하죠. (The sum of db block gets and consistent gets equals the total read from the RAM data buffer cache)

우선 consistent gets는 consistent mode에서 db block read를 수행한 숫자입니다. TKPROF 레포트에서 query에 해당하는 값입니다. consistent라는 말은 read consistency와 관련이 있는데 즉 읽기 일관성이 보장되는 상황에서 읽는 숫자라는 것입니다. 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이 발생하지 않습니다.

db block gets는 CURRENT mode에 있는 block의 데이터를 읽은 숫자입니다. TKPROF 레포트에서 current에 해당하는 값이구요. current mode에서는 곧 수정될 Segment header나 block을 얻을 때 일어납니다. INSERT, UPDATE, DELETE에서 데이터 쿼리부분이 아니라 수정될 값을 읽을 때 나타나는 숫자입니다. select문일 경우에는 Full table scan일 경우 Segment header를 읽을 때 나타납니다.

위의 내용은 Performance Tuning 문서에 있는 내용을 대충 정리한 것입니다. 그래서 아마도 위의 내용은 대부분 보셧을 내용이겠지만, 정확한 차이점을 이해하려면 더 많은 것들을 알고 이해해야합니다.

여기서 Consistent mode와 current mode가 무엇인지를 아는 것이 중요합니다.

우선 Consistency를 알기 위해서 오라클이 제공하는 Mulitversion concurrency control에 대해 알아야 합니다. 사용자가 쿼리를 날렸을때 그 순간의 이미지를 가지고 데이터를 가져오게 됩니다. 쿼리를 날린 후에 다른 세션에서(자기 자신의 세션에서도 마찬기지입니다.) 원하는 데이터가 수정이 되어도 처음 쿼리의 처리를 시작한 순간의 내용을 오라클은 제공합니다. 이런 처리를 위해 쿼리가 실행되는 순간 SCN(System change number)가 결정의 되고 각 DB Block의 scn과 비교하여 더 큰 scn을 가지고 있는 경우 즉 변경이 일어난 경우에는 rollback segment에서 과거의(자신의 scn보다 낮은 scn을 가지고 있는) 이미지의 block을 가지고 오게 됩니다.

consistent gets가 consistent mode에서 block의 데이터를 읽은 숫자라는 것은 이와 같이 읽기 일관성이 보장되는 상황에서 읽은 block의 숫자라는 것입니다.

DML문장이 수행되는 경우에 처리되는 부분을 두 부분으로 나눌 수 있는데, 하나는 수정할 데이터를 찾기 위해 읽는 부분이고, 다른 하나는 실제 데이터를 수정하기 위해 데이터를 읽는 부분입니다. 데이터를 찾기위해 읽는 부분은 INSERT문의 sub query부분과 delete문과 update문의 where 조건절에 해당하는 부분이나 sub query에 해당하는 부분입니다.

DML문에서는 데이터를 찾기 위한 부분이 consistent gets에 나타나고 수정하기 위한 부분이 db block gets에 나타납니다.

update t set value = value + 5 where value > 10; 예를 들어 위와 같은 문장을 보면 처음 위의 문장이 실행 되는 순간의 이미지를 이용해서 조건에 맞는 즉 value가 10보다 큰 row들을 찾습니다. 그리고 각각의 row를 실제로 update하는 value = value + 5를 실행할때는 current mode에서 수행이 됩니다. 조건에 해당하는 row를 이미 읽었지만 수정하기 위해서 다시 또 읽게 됩니다. 이 때는 실제 데이터를 수정해야 하기 때문에 지금 바로 현재의 데이터여야 합니다. consistent mode에서 읽은 데이터는 과거의 이미지의 데이터일 수 있지만 수정시에는 가장 최근의 버젼을 수정해야 하기 때문입니다. 즉 current mode라는 것은 과거의 시점이 아닌 바로 지금의 data를 읽는 것을 말합니다.

update문을 수행시에 consistent gets와 db block gets의 숫자를 비교해 보면 db block gets의 숫자가 더 큰 경우를 보게 되는데 이것은 consistent gets는 즉 수정될 로우를 찾을 경우는 block단위로 io가 일어나서 읽은 block의 수를 나타내게 되지만, db block gets는 즉 수정될 로우를 찾을 때는 각 로우마다 current mode에서 데이터를 읽기 때문에 같은 block도 row의 수만큼 읽게 됩니다. trace를 보시면 db block gets의 수는 실제 수정될 row의 수와 거의 같은 것을 확인하실 수 있습니다.

consistent gets는 physical reads를 포함하고 있기 때문에 실제로 쿼리를 튜닝할 때 중요한 것은 Logical IO를 줄이는 것입니다. 이것에 관한 내용도 설명하자면 매우 길어지므로 여기서는 logical reads의 한 경우인 db block gets의 관한 예를 하나만 들겠습니다.

다음의 두 문장은 똑 같은 읽을 수행합니다. 읽은 범위는 똑 같습니다. 즉 consistent reads의 수는 동일합니다. 그러나 db block gets의 숫자는 두배의 차이가 있기때문에 속도의 차이가 있습니다. 직접 샘플을 만들어 확인해 보시기 바랍니다. UPDATE ta A SET A.QTY = ( SELECT SUM(B.QTY) + A.QTY FROM tb B WHERE B.ID = A.ID );

UPDATE ta A SET A.QTY = A.QTY + ( SELECT SUM(B.QTY) FROM tb B WHERE B.ID = A.ID );

질문의 내용에는 벗어나지만 성능을 향상시키기 위해서는 LIO를 줄여야합니다. 모든 LIO는 latch를 발생시키기 때문에 가능한 적은 LIO를 수행하도록 하여야 좋은 성능과 확장성을 보장할 수 있습니다.

[출처: dbguide]

Consistent gets 와 physical reads

DB CAFE