LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ch0c0bana0

【Oracle Database】 Consistent Getsについて

Last updated at Posted at 2019-03-25

consistent getsについては下記に解説があります
しばちょう先生の試して納得！DBAへの道　
第13回 行移行、行連鎖を理解し性能トラブルを未然に防ぐ(2)

読み込み元（ディスク 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 が減るか？」

・セッション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

めっちゃ減った...

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?