【Oracle Database】 Consistent Getsについて

consistent getsについては下記に解説があります
[しばちょう先生の試して納得！DBAへの道　
第13回 行移行、行連鎖を理解し性能トラブルを未然に防ぐ(2)](https://www.oracle.com/technetwork/jp/database/articles/shibacho/index-1889009-ja.html)
>読み込み元（ディスク or バッファ・キャッシュ）を問わず読み込んだ全ブロック数（論理読み込みブロック数 = consistent gets）

最近こいつについてSQL実行タイミングによって増減するという問い合わせがあったので、別セッションで更新があった場合にどういう風に変化するかメモ程度に記載します

##下準備
データ作成

```
begin
for i in 1..100000 loop
  insert into test1 values(i);
end loop;
commit;
end;
/

SQL> select count(*) from test1;

  COUNT(*)
----------
    100000
```

バッファをフラッシュ

```
SQL>  alter system flush buffer_cache;

System altered.
```

トレースON
```
SQL> set autot trace
```

##1.通常時のconsistent gets確認
1回目なのでPhysical Readが発生するはず

・セッションA
```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
        154  physical reads
          0  redo size
    2282679　bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

ですよね

では2回目

・セッションA
```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
          0  physical reads
          0  redo size
    2282679　bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

ついでにこのあと使うのでセッションBも

・セッションB
```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
          0  physical reads
          0  redo size
    2282679  bytes sent via SQL*Net to client
      73878  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

consistent getsの量は変わっていません
メモリにブロックが載ったのでPhysical Readはなくなりました

##2.読み取り一貫性発生時のconsistent gets確認
値を一部マイナスにしてみましょう

・セッションA：UPDATE

```
begin
for i in 1..10000 loop
  update test1 set c01= -1 * i where c01=i;
end loop;
end;
/
```

各セッションでSELECT

・セッションA：SELECT

```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
          0  physical reads
          0  redo size
    2292679  bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

・セッションB：SELECT

```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16840  consistent gets
          0  physical reads
       1240  redo size
    2282679  bytes sent via SQL*Net to client
      73878  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

セッションBは読み取り一貫性を保つためUNDOブロックにアクセスしたため、consistent getsが増えたものと想定されます

コミットしてみるとどうでしょう

・セッションA：commit

```
SQL> commit;

Commit complete.
```

・セッションA

```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
          0  physical reads
          0  redo size
    2292679  bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

・セッションB

```
SQL> select * from test1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6824  consistent gets
          0  physical reads
          0  redo size
    2292679  bytes sent via SQL*Net to client
      73878  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```


コミットされてUNDOブロックにアクセスする必要がなくなりましたので、もとに戻りました

##3.SORTしてSELECTした場合
最後にAZEさんが記事にしていたSORTした場合について、確認だけしてみましょう

詳細は下記記事を参照されたし
[ablog「sort するとなぜ consistent gets が減るか？」](https://yohei-a.hatenablog.jp/entry/20140322/1395437598)

・セッションA

```
SQL> select * from test1 order by 1;

100000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1692556001

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     2 |     8 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |       |     2 |     8 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST1 |     2 |     8 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
    1892735  bytes sent via SQL*Net to client
      73877  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed
```

めっちゃ減った...