Buffer Pinning

One of my colleagues asked me about the meaning of buffer is pinned count and buffer pinning.

The baisc concept of buffer pinning.

In case of consecutive access on the same buffer within the same fetch call, Oracle pins the buffer so as to visit the buffer without acquiring the cache buffers chains latch and searching the cache buffers chains. The duration of pin is fetch-call scope, so we have a decreased overhead without the pin contention.

Basic but hard to understand. :(

So I made a very simple test case to demonstrate what is buffer pinning and how we interpret it. Here it goes.

UKJA@ukja102> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

UKJA@ukja102> create table t1(c1 int, c2 int, c3 char(100));

Table created.

UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, dbms_random.random, 'x'
  3  from dual connect by level <= 10000;

10000 rows created.

UKJA@ukja102>
UKJA@ukja102> create index t1_n1 on t1(c1);

Index created.

UKJA@ukja102> create index t1_n2 on t1(c2);

Index created.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

PL/SQL procedure successfully completed.

Note that index T1_N1 and T1_N2 have almost same data distributions, but clustering factors of big difference.

UKJA@ukja102> select index_name, clustering_factor
  2  from user_indexes where table_name = 'T1';

INDEX_NAME           CLUSTERING_FACTOR                                          
-------------------- -----------------                                          
T1_N1                              164                                          
T1_N2                             9933                                          

The comparison using SQL*Trace shows that the index T1_N1(good clustering factor of 164) has only 186 logical reads but the index T1_N2(bad clustering factor of 9933) has 9,960 logical reads for the same volume of data!

select /*+ index(t1 t1(c1)) */ count(c3) 
from
 t1 where c1 is not null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.11         69        186          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.11         69        186          0           1


select /*+ index(t1 t1(c2)) */ count(c3) 
from
 t1 where c2 is not null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.07       0.05          0      9960          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.05          0       9960          0           1

Big difference! Where does this number come from?

Session diff makes me realize that this number comes from the diffference of buffer is pinned count

-- index T1_N1(good clustering factor)
NAME                                             DIFF
---------------------------------------- ------------
buffer is pinned count                         19,835
table fetch by rowid                           10,000
session logical reads                             219
consistent gets from cache                        199
consistent gets                                   199
buffer is not pinned count                        165

-- index T1_N2(bad clustering factor)
NAME                                             DIFF
---------------------------------------- ------------
buffer is pinned count                         10,066
table fetch by rowid                           10,000
session logical reads                           9,998
consistent gets from cache                      9,975
consistent gets                                 9,975
no work - consistent read gets                  9,958

Good clustering factor enables us to have a consecutive visit on the same table block. The result is frequent buffer pinning and decreased logical reads.

Same effect with cache buffers chains latch.

-- index T1_N1(good clustering factor)
LATCH_NAME                         D_GETS
------------------------------ ----------
cache buffers chains                  492

-- index T1_N2(bad clustering factor)
LATCH_NAME                         D_GETS
------------------------------ ----------
cache buffers chains                20108

The main usage of buffer pinning is table access by rowid via index and nested loop join.

One thing to be cautious about. We do not have 100 times of performance gain with 1/100 of reduced logical reads. Buffer pinning enables us to read the buffer without the overhead of latch, but we still need to read the buffer!

As far as I know, we have no direct control on buffer pinning. It’s intrinsic mechanism of Oracle. But you can still make the use of it for index scan and nested loop join. It matters how the corresponing datas are sorted.

About these ads