Home > Oracle > Buffer is pinned count

Buffer is pinned count


Introduction

There are many cases where Oracle revisit some buffer in the buffer cache many times inside one database call. It such cases it can pin the buffer and hold the buffer pinned and just read pinned buffer in consequences visits. It allows to avoid redundant logical reads.

There are statistics “buffer is pinned count” and “buffer is not pinned count”.
The concept is simple: there is the function kcbispnd (“Kernel Cache Buffer Is Pinned” as I suggest) where Oracle checks if a buffer is pinned or not. If a buffer is pinned then statistic “buffer is pinned count” is incremented, otherwise Oracle increments “buffer is not pinned count” and usually initiates logical read after that.
There is analog of kcbispnd, the function kcbipnns. It does the same checks but does not change any statistics. The function kcbispnd is usually called before logical read, the function kcbispnd can be called in another places, for example, before to release a buffer.

Inside a procedure performing logical read Oracle pins the buffer (except examination), attaches buffer handle to x$kcbbf by the function kcbzgs. When it exits from the function performing logical read it holds the buffer pinned and continues to work visiting the buffer or doing another work until it will be released. The function kcbrls (Kernel Cache Buffer Release) is used to release a buffer, also it is possible from another places, for example from kcb_post_apply can call kcbzfs (detach handle from x$kcbbf) directly.

Very simplified it looks like something like this

if kcbispnd(buffer) != 1 then
    <logical read>
end if;
<works with buffer>
<release the buffer> -- usually by kcbrls;

In such cases Oracle:
– acquires latch “cache buffer chains” during logical read, inside kcbgtcr
acquires can acquire latch “cache buffer handles” during attaching of buffer handle, inside kcbzgs
– acquires latch “cache buffer chains” during releasing of a buffer
acquires can acquire latch “cache buffer handles” during detaching of buffer handle, inside kcbzfs

  • Upd: In order to do not grab latch “cache buffer handles” during every buffer pin, a session has _db_handles_cached (5 by default) buffer handles. The latch is captured if a session needs more than this number.

There are cases when Oracle just read and does not pin a buffer inside logical read. In such cases it increments statistic “consistent gets – examination”. In such cases only one latch “cache buffer chains” is acquired.

  • Note: read more about latches acquiring inside Oracle functions

It is necessary to note, that Oracle does not execute the function kcbispnd before every logical read, but only in some cases, when in some point of source code it does not know if a buffer will be pinned or not, when if buffer is pinned or not depends on some conditions, on some data. In some cases Oracle uses kcbipnns to check if a buffer is pinned. And when it is known that a buffer will be pinned in some point of time Oracle just re-visit it without any check.


Example
Note: Oracle 11.2.0.2 on Solaris 10 is used

Let’s try to look at a simple example: reading table via an index.
The query:

select --+ index(tbl)
       count(pad) 
  from tbl;

The execution plan:

--------------------------------------
| Id  | Operation                    |
--------------------------------------
|   0 | SELECT STATEMENT             |
|   1 |  SORT AGGREGATE              |
|   2 |   TABLE ACCESS BY INDEX ROWID|
|   3 |    INDEX FULL SCAN           |
--------------------------------------

In this example we create simple table with index

SQL> create table tbl(id not null, pad) as
  2      select rownum id,
  3             rpad('*', 100, '*') pad
  4        from all_objects
  5       where rownum <= 100;
 
Table created

SQL> create index idx on tbl(id) pctfree 95;
 
Index created

SQL> exec dbms_stats.gather_table_stats(user, 'TBL', estimate_percent => 100, cascade => true);
 
PL/SQL procedure successfully completed

SQL> select blevel,
  2         leaf_blocks,
  3         clustering_factor
  4    from dba_ind_statistics
  5   where index_name = 'IDX';
 
    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1           7                 2

Notice that I use pctfree on the index to force Oracle create more than one index block for our small table. It is because mechanics of pinning of root index block slightly differs from pinning of leaf index block.

I execute the following query to get rowsource statistics

select --+ index(tbl) gather_plan_statistics
       count(pad) 
  from tbl;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |      1 |00:00:00.01 |      10 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TBL  |      1 |    100 |00:00:00.01 |      10 |
|   3 |    INDEX FULL SCAN           | IDX  |      1 |    100 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------

8 buffer gets (1 root index block + 7 leaf blocks) were done during INDEX FULL SCAN and two buffer gets (clustering factor) were done during TABLE ACCESS BY INDEX ROWID.

Below is an excerpt from DTraceLIO aggregation output (full DTraceLIO output: dtracelio_opifch.trc):

================================= Logical I/O Summary (grouped by object) ================================
 object_id  data_object_id       lio        cr    cr (d)        cu    cu (d) ispnd (Y) ispnd (N)   pin rls
---------- --------------- --------- --------- --------- --------- --------- --------- --------- ---------
         0               0         0         0         0         0         0         0         1         0
     79083           79083         2         2         0         0         0       197         2         2
     79084           79084         8         8         0         0         0         0         1         7
---------- --------------- --------- --------- --------- --------- --------- --------- --------- ---------
     total                        10        10         0         0         0       197         4         9
==========================================================================================================

Here we can see again that this query requires 8 logical reads of the object 79084 (index IDX) and two logical reads of object 79083 (the table TBL).
Pay attention on
isnpd (Y) = 197 and ispnd (N) = 2 for the table TBL,
isnpd (N) = 1 for the index IDX,
isnpd (N) = 1 for object with object_id = 0.

pin rls (pin release, kcbrls function) has been called twice for the table and 7 times for the index.

So, now the time to use the Digger!

Before I execute our query I execute the Digger and wait for prompt in the file.

digger.sh -p 16829 -Fgdeoz -f opifch* > opifch.trc

the output: opifch.trc (200Kb)

these keys means:
-p PID – to trace process ID 16829
-F – to use flow indents
-gdeo – print columns: timestamp, relative timestamp (cpu time of the process minus DTrace overhead), elapsed time of a call (ela), cpu time of a call (cpu)
-z – to print call stack before enter to traced function
-f – to trace calls inside function opifch*
A filter (option t) is not used, it means that all application calls will be traced inside traced function function opifch.

Note: Pay attention, that we trace the function calls only from the application and do not trace the calls from libraries and syscalls. Don’t worry about it, we will see syscalls in another Digger’s examples.

Why opifch? It is possible to trace a function qergsFetch (qer Group by Sort Fetch) the step 2 in the execution plan, it is where our execution plan starts to execute. Or even qertbFetchByRowID, step 3, where buffer pinning will be caused. But there is some important piece of code related with buffer pinning outside of qergsFetch. It is why I trace opifch.

Why I use the symbol star (opifch*) instead of opifch?
There are functions opifch and opifch2. Different clients uses different API. As a result with different clients you can get different entry points of a fetch call. An exmple, an output from PL/SQL Developer.
Notice that in the output SQL contains two fetch calls. It is because SQL+ calls the second fetch to get “no data found”.
And output from PL/SQL Developer also contains two fetch calls, it is because PL/SQL Developer run “select x from dual” before each query.

After that I execute the query and look at the output

select --+ index(tbl)
       count(pad) 
  from tbl;


So, lets take look at the output. I will start from the function qergsFetch, this is the step 2 in the execution plan, it is where our execution plan starts to execute. We will investigate calls before qergsFetch in following examples.

   -> qergsFetch(0x89FAF9B0, 0xFFFFFD7FFD99EB60, 0x4D98000)
     -> qeaeCnt(0xFFFFFD7FFD99FCA8, 0xFFFFFD7FFD99EB60, 0x1)
     <- qeaeCnt = 0x8
     -> qertbFetchByRowID(0x89FAFB80, 0xFFFFFD7FFD99E858, 0x3ABDE00)
       -> qerixtFetch(0x89FAFE08, 0xFFFFFD7FFD99E170, 0x0)

Here we see that Oracle calls qergsFetch in order to perform SORT AGGREGATE, step 2.
SORT AGGREGATE needs to get data from the step 3, so it calls qertbFetchByRowID in order to perform TABLE ACCESS BY INDEX ROWID.
TABLE ACCESS BY INDEX ROWID needs to get rowids from the index, so it calls qerixtFetch in order to perform INDEX FULL SCAN.

         -> qeilsr(0xFFFFFD7FFD99E3A8, 0xFFFFFD7FFD99E568, 0x0)
           -> qeilbk1(0xFFFFFD7FFD99E568, 0x0, 0xFFFFFD7FFD99E3A8)
             -> kcbispnd(0xFFFFFD7FFD99E610, 0x0, 0x0)
             <- kcbispnd = 0x0
             -> ktrgtc2(0xFFFFFD7FFD99E600, 0xFFFFFD7FFD99E1A0, 0x46491D0)
               -> ktsmg_max_query(0x0, 0x0, 0xFFFFFD7FFDC12038)
               <- ktsmg_max_query = 0x3F
               -> kcbgtcr(0xFFFFFD7FFD99E610, 0x1, 0x47C)
                 -> ktrexc(0xFFFFFD7FFFDFA960, 0xE771F80, 0x0)
                   -> ktrEvalBlockForCR(0xFFFFFD7FFD99DE9C, 0xE771F80, 0xFFFFFD7FFD99DEC0)
                   <- ktrEvalBlockForCR = 0x1
                   -> ktcckv(0xFFFFFD7FFFDFA980, 0xFFFFFD7FFD99DE9C, 0x129FC2)
                   <- ktcckv = 0x0
                   -> kdifkc(0x74ADA014, 0xFFFFFD7FFD99E568, 0x129FC2)
                     -> kdxbrs1(0x74ADA04C, 0xFFFFFD7FFD9A2CD0, 0xFFFFFD7FFFDF9F5E)
                       -> lmebucp(0xFFFFFD7FFD9A2D11, 0x0, 0x74ADBFB1)
                       <- lmebucp = 0xFFFFFFFF
                       -> lmebucp(0xFFFFFD7FFD9A2D11, 0x0, 0x74ADBFC1)
                       <- lmebucp = 0xFFFFFFFF
                     <- kdxbrs1 = 0x1000BCC
                   <- kdifkc = 0x1
                 <- ktrexc = 0x2
               <- kcbgtcr = 0x0
             <- ktrgtc2 = 0x0

Here Oracle gets index root block. It executes function kcbispnd and check if the buffer is pinned yet. Buffer is not pinned, so function kcbispnd increments statistic “buffer is not pinned count” and returns 0. After that Oracle initiates logical read (ktrgtc2->kcbgtcr).
Notice that function ktrexc is called in kcbgtcr. It is examination – the case when Oracle just read the buffer and does not pin it. The statistic “consistent gets – examination” is incremented inside this function.

             -> kcbipnns(0xFFFFFD7FFD99E610, 0x1, 0xFFFFFD7FFD99E600)
             <- kcbipnns = 0x0
             -> ktrget2(0xFFFFFD7FFD99E600, 0xFFFFFD7FFD99E1A0, 0x47D)
               -> ktsmg_max_query(0x0, 0x0, 0xFFFFFD7FFDC12038)
               <- ktsmg_max_query = 0x3F
               -> kcbgtcr(0xFFFFFD7FFD99E610, 0x0, 0x47D)
                 -> ktrexf(0xFFFFFD7FFFDFA350, 0xE771F80, 0x0)
                 <- ktrexf = 0x9
                 -> kcbzgs(0x1, 0xE771F80, 0x1)
                   -> kssadf_numa_intl(0x26, 0x91F2BB00, 0x924905D8)
                   <- kssadf_numa_intl = 0x91A74018
                 <- kcbzgs = 0x91A74018
                 -> kcbz_fp_buf(0x74BDF348, 0x91A74098, 0x1)
                 <- kcbz_fp_buf = 0x1
               <- kcbgtcr = 0x748C4014
               -> kcbcge(0xFFFFFD7FFD99E610, 0xFFFF8000, 0x0)
               <- kcbcge = 0x129D0C
               -> ktcckv(0xFFFFFD7FFD99E610, 0xFFFFFD7FFD99DE9C, 0x0)
               <- ktcckv = 0x129D0C
             <- ktrget2 = 0x748C4064

Here Oracle reads index leaf block.
Pay attention that it uses function kcbipnns instead of kcbispnd to determine if buffer pinned or not, found that buffer is not pinned and initiates logical read of leaf block. Inside kcbgtcr Oracle pins the buffer, attaches the buffer handle to x$kcbbf (kcbzgs).
After Oracle exits from kcbgtcr it holds a buffer pinned.

           <- qeilbk1 = 0x0
         <- qeilsr = 0x0
         -> kdifxs(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
           -> kdifxs1(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
           <- kdifxs1 = 0x748C5FB8
         <- kdifxs = 0x748C5FB8
         -> kafgex1(0x0, 0x748C5FB8, 0x0)
         <- kafgex1 = 0x0
       <- qerixtFetch = 0x0

After that Oracle fetches row from index block (kdifxs) and exits from qerixtFetch.
Now Oracle has the first rowid and needs to read table block to get value of the column PAD from the table.

       -> qetlbr(0xFFFFFD7FFD99E970, 0xFFFFFD7FFD9A2C14, 0x0)
         -> kcbispnd(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD9A2C14, 0x0)
         <- kcbispnd = 0x0
         -> kdsgrp(0xFFFFFD7FFD99E970, 0x0, 0xFFFFFD7FFD99E970)
           -> kcbispnd(0xFFFFFD7FFD99E988, 0x0, 0xFFFFFD7FFD99E970)
           <- kcbispnd = 0x0
           -> ktrget2(0xFFFFFD7FFD99E978, 0xFFFFFD7FFD99E8A0, 0x360)
             -> ktsmg_max_query(0x0, 0x0, 0xFFFFFD7FFDC12038)
             <- ktsmg_max_query = 0x3F
             -> kcbgtcr(0xFFFFFD7FFD99E988, 0x0, 0x360)
               -> ktrexf(0xFFFFFD7FFFDFA720, 0xE771F80, 0x0)
               <- ktrexf = 0x9
               -> kcbzgs(0x1, 0xE771F80, 0x1)
                 -> kssadf_numa_intl(0x26, 0x91F2BB00, 0x924905D8)
                 <- kssadf_numa_intl = 0x91A74398
               <- kcbzgs = 0x91A74398
               -> kcbz_fp_buf(0x74BF2CC8, 0x91A74418, 0x1)
               <- kcbz_fp_buf = 0x1
             <- kcbgtcr = 0x74AD4014
             -> kcbcge(0xFFFFFD7FFD99E988, 0x0, 0x0)
             <- kcbcge = 0x0
             -> ktcckv(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD99DE9C, 0x0)
             <- ktcckv = 0x0
           <- ktrget2 = 0x74AD407C
         <- kdsgrp = 0x74AD5F91
       <- qetlbr = 0x74AD5F91

Here we can see similar strategy – Oracle executes kcbispnd and checks if a buffer containing required table block is pinned yet or not. The function kcbispnd increments statistic “buffer is not pinned count”, returns 0, and Oracle initiates logical read of a buffer containing table block.
Pay attention that it executes function kcbispnd twice: the first time before function kdsgrp (Kernel Data Scan Get Row Piece) and the second time inside this procedure.
Be aware that the first call of kcbispnd here is the call for object_id=0 which we saw in DTraceLIO above.

Why kcbispnd is executed twice here?
As we will see below, for each rowid from the index oracle performs two checks:
1) the first call of kcbispnd is checking if buffer from previous rowid still be pinned. If yes and if it differs then block from latest read rowid from the index it releases pinned buffer.
In our case at this point of time there was no previous rowid and there was no pinned buffer containing table block. It is why object_id=0.
2) the second call of kcbispnd is checking if buffer from current(latest) rowid is pinned. If not then Oracle initiates logical read.

Now we have two buffers are pinned, these buffers contain index block and table block.

After that Oracle repeats following pattern (except two cases when Oracle either needs to read next leaf block or rowid from the index points to another table block):

       -> qerixtFetch(0x89FAFE08, 0xFFFFFD7FFD99E170, 0x0)
         -> kdifxs(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
           -> kdifxs1(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
           <- kdifxs1 = 0x748C5FAC
         <- kdifxs = 0x748C5FAC
         -> kafgex1(0x0, 0x748C5FAC, 0x0)
         <- kafgex1 = 0x0
       <- qerixtFetch = 0x0

Here Oracle again calls fetch from the index (qerixtFetch) and gets row from the index without execution of kcbispnd and logical I/O. It just reads a pinned buffer.

       -> qetlbr(0xFFFFFD7FFD99E970, 0xFFFFFD7FFD9A2C14, 0x0)
         -> kcbispnd(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD9A2C14, 0x0)
         <- kcbispnd = 0x1
         -> kdsgrp(0xFFFFFD7FFD99E970, 0x0, 0xFFFFFD7FFD99E970)
           -> kcbispnd(0xFFFFFD7FFD99E988, 0x0, 0xFFFFFD7FFD99E970)
           <- kcbispnd = 0x1
         <- kdsgrp = 0x74AD5F26
       <- qetlbr = 0x74AD5F26

After that Oracle reads row from the table buffer with twice execution of kcbispnd: before kdsgrp and inside kdsgrp.

Pay attention that Oracle executes kcbispnd before each logical read (or visit to pinned) of a buffer containing table block, but does not execute it before each read of buffer containing index block. It is what I said that Oracle uses this concept of using kcbispnd not in each case when it is going to perform logical read, but in cases when in some point of source code it does not know if a buffer will be pinned or not, when it depends on some conditions, on data in our case. In this case in some point of source code it is known that index buffer will be pinned, it does not depend on anything, in contrast the table buffer which depends of previous rowid from the index.

In our case with the current execution plan Oracle would gets index blocks one-by-one, gets each next index buffer once, reads rows from the buffer and releases it. In contrast a table buffer can be got many times. It depends on clustering factor. It is exactly where clustering factor is important.

Two exception from previous pattern:

       -> qerixtFetch(0x89FAFE08, 0xFFFFFD7FFD99E170, 0x0)
         -> kdifxs(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
           -> kdifxs1(0xFFFFFD7FFD99E3A8, 0x1, 0x0)
             -> kcbipnns(0xFFFFFD7FFD99E3C0, 0x1, 0x0)
             <- kcbipnns = 0x1
             -> kcbrls(0xFFFFFD7FFD99E3C0, 0x1, 0x0)
               -> kcbzar(0x91A74958, 0x91F5D738, 0x100000)
               <- kcbzar = 0x8
               -> kcbzfs(0x91A748D8, 0x2000000000000019, 0xFFFFFFFD)
                 -> kjbilms(0x91A748D8, 0x2000000000000019, 0xFFFFFFFD)
                 <- kjbilms = 0x0
                 -> kssrmf_numa_intl(0x91A748D8, 0x91F2BB00, 0x0)
                 <- kssrmf_numa_intl = 0x0
               <- kcbzfs = 0x0
             <- kcbrls = 0x0
             -> ksuttctest(0xE768C18, 0x1, 0x0)
               -> nioqts(0xE768D28, 0x0, 0x0)
               <- nioqts = 0x0
             <- ksuttctest = 0x0
             -> ktrget2(0xFFFFFD7FFD99E3B0, 0xFFFFFD7FFD99E1A0, 0x391)
               -> ktsmg_max_query(0x0, 0x0, 0xFFFFFD7FFDC12038)
               <- ktsmg_max_query = 0x3F
               -> kcbgtcr(0xFFFFFD7FFD99E3C0, 0x0, 0x391)
                 -> ktrexf(0xFFFFFD7FFFDF94D0, 0xE771F80, 0x0)
                 <- ktrexf = 0x9
                 -> kcbzgs(0x1, 0xE771F80, 0x1)
                   -> kssadf_numa_intl(0x26, 0x91F2BB00, 0x924905D8)
                   <- kssadf_numa_intl = 0x91A748D8
                 <- kcbzgs = 0x91A748D8
                 -> kcbz_fp_buf(0x74BDF218, 0x91A74958, 0x1)
                 <- kcbz_fp_buf = 0x1
               <- kcbgtcr = 0x748C2014
               -> kcbcge(0xFFFFFD7FFD99E3C0, 0xFFFF8000, 0x0)
               <- kcbcge = 0x129D0C
               -> ktcckv(0xFFFFFD7FFD99E3C0, 0xFFFFFD7FFD99DE9C, 0x0)
               <- ktcckv = 0x129D0C
             <- ktrget2 = 0x748C2064
           <- kdifxs1 = 0x748C3FB8
         <- kdifxs = 0x748C3FB8
         -> kafgex1(0x0, 0x748C3FB8, 0x0)
         <- kafgex1 = 0x0
       <- qerixtFetch = 0x0

Here Oracle found that it needs to read the next leaf block. It releases the pinned buffer and initiates logical read in order to get the next leaf block.

       -> qetlbr(0xFFFFFD7FFD99E970, 0xFFFFFD7FFD9A2C14, 0x0)
         -> kcbispnd(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD9A2C14, 0x0)
         <- kcbispnd = 0x1
         -> kcbipnns(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD9A2C14, 0x0)
         <- kcbipnns = 0x1
         -> kcbrls(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD9A2C14, 0x0)
           -> kcbzar(0x91A74418, 0x919D7E10, 0x100000)
           <- kcbzar = 0x8
           -> kcbzfs(0x91A74398, 0x2000000000000019, 0xFFFFFFFD)
             -> kjbilms(0x91A74398, 0x2000000000000019, 0xFFFFFFFD)
             <- kjbilms = 0x0
             -> kssrmf_numa_intl(0x91A74398, 0x91F2BB00, 0x0)
             <- kssrmf_numa_intl = 0x0
           <- kcbzfs = 0x0
         <- kcbrls = 0x0
         -> kdsgrp(0xFFFFFD7FFD99E970, 0x0, 0xFFFFFD7FFD99E970)
           -> kcbispnd(0xFFFFFD7FFD99E988, 0x0, 0xFFFFFD7FFD99E970)
           <- kcbispnd = 0x0
           -> ktrget2(0xFFFFFD7FFD99E978, 0xFFFFFD7FFD99E8A0, 0x360)
             -> ktsmg_max_query(0x0, 0x0, 0xFFFFFD7FFDC12038)
             <- ktsmg_max_query = 0x3F
             -> kcbgtcr(0xFFFFFD7FFD99E988, 0x0, 0x360)
               -> ktrexf(0xFFFFFD7FFFDFA720, 0xE771F80, 0x0)
               <- ktrexf = 0x9
               -> kcbzgs(0x1, 0xE771F80, 0x1)
                 -> kssadf_numa_intl(0x26, 0x91F2BB00, 0x924905D8)
                 <- kssadf_numa_intl = 0x91A74398
               <- kcbzgs = 0x91A74398
               -> kcbz_fp_buf(0x74BDF6D8, 0x91A74418, 0x1)
               <- kcbz_fp_buf = 0x1
             <- kcbgtcr = 0x748CA014
             -> kcbcge(0xFFFFFD7FFD99E988, 0x0, 0x0)
             <- kcbcge = 0x0
             -> ktcckv(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD99DE9C, 0x0)
             <- ktcckv = 0x0
           <- ktrget2 = 0x748CA07C
         <- kdsgrp = 0x748CBF91
       <- qetlbr = 0x748CBF91

Here Oracle got rowid pointing to another table block. It checks if previous buffer is pinned, kcbispnd returns 1, this means that buffer is pinned, Oracle finds pinned buffer contains another table block then block from current rowid and releases pinned buffer. It is why Oracle executes kcbispnd before kdsgrp. It checks if rowid from the index contains the same table buffer as it keeps pinned or not. Inside kdsgrp it executes kcbispnd again to check that block from current rowid is pinned, finds that this buffer is not pinned yet, increments “buffer is not pinned count”, returns 0, and initiates logical read of the table block.

It is why clustering factor is important.
After Oracle reads next index entry from an index it checks if this index entry points to the same table buffer as previous index entry or not. If yes then Oracle just revisit a pinned buffer. Otherwise Oracle releases previous pinned table buffer and get and pin the buffer from the current index entry.

Have you noticed that a few buffers are still pinned in this point? These buffers are released at the end of the fetch call.

   -> qecrlssub(0x89FB0D58, 0xFFFFFD7FFDC12038, 0xA)
     -> qergsRelease(0x89FAF9B0, 0xFFFFFD7FFD99EB60, 0xA)
       -> qertbRelease(0x89FAFB80, 0xFFFFFD7FFD99E858, 0xA)
         -> kcbipnns(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD99E858, 0xA)
         <- kcbipnns = 0x1
         -> kcbrls(0xFFFFFD7FFD99E988, 0xFFFFFD7FFD99E858, 0xA)
           -> kcbzar(0x91A74418, 0x918C9830, 0x100000)
           <- kcbzar = 0x8
           -> kcbzfs(0x91A74398, 0x2000000000000019, 0xFFFFFFFD)
             -> kjbilms(0x91A74398, 0x2000000000000019, 0xFFFFFFFD)
             <- kjbilms = 0x0
             -> kssrmf_numa_intl(0x91A74398, 0x91F2BB00, 0x0)
             <- kssrmf_numa_intl = 0x0
           <- kcbzfs = 0x0
         <- kcbrls = 0x0
         -> qerixRelease(0x89FAFE08, 0xFFFFFD7FFD99E170, 0x0)
           -> qerixReleaseSelf(0x89FAFE08, 0x1, 0x0)
             -> kcbipnns(0xFFFFFD7FFD99E610, 0x1, 0x0)
             <- kcbipnns = 0x1
             -> kcbipnns(0xFFFFFD7FFD99E610, 0x1, 0x0)
             <- kcbipnns = 0x1
             -> kcbrls(0xFFFFFD7FFD99E610, 0x1, 0x0)
               -> kcbzar(0x91A74098, 0x918A0548, 0x100000)
               <- kcbzar = 0x8
               -> kcbzfs(0x91A74018, 0x2000000000000019, 0xFFFFFFFD)
                 -> kjbilms(0x91A74018, 0x2000000000000019, 0xFFFFFFFD)
                 <- kjbilms = 0x0
                 -> kssrmf_numa_intl(0x91A74018, 0x91F2BB00, 0x0)
                 <- kssrmf_numa_intl = 0x0
               <- kcbzfs = 0x0
             <- kcbrls = 0x0
             -> kcbipnns(0xFFFFFD7FFD99E3C0, 0x91F2BB00, 0x0)
             <- kcbipnns = 0x1
             -> kcbrls(0xFFFFFD7FFD99E3C0, 0x91F2BB00, 0x0)
               -> kcbzar(0x91A74958, 0x91F9D780, 0x100000)
               <- kcbzar = 0x8
               -> kcbzfs(0x91A748D8, 0x2000000000000019, 0xFFFFFFFD)
                 -> kjbilms(0x91A748D8, 0x2000000000000019, 0xFFFFFFFD)
                 <- kjbilms = 0x0
                 -> kssrmf_numa_intl(0x91A748D8, 0x91F2BB00, 0x0)
                 <- kssrmf_numa_intl = 0x0
               <- kcbzfs = 0x0
             <- kcbrls = 0x0
           <- qerixReleaseSelf = 0x1
         <- qerixRelease = 0xE6
       <- qertbRelease = 0xE6
     <- qergsRelease = 0x26
   <- qecrlssub = 0x26


Conclusion
We can conclude that “buffer is pinned count”
– is not a number of times when Oracle re-visit pinned buffer. It can read from pinned buffer without any checks, it is just reading from memory, as fetched rows from the index in our example.
– is not a check before every logical read.
– is not a number of times when a buffer has been pinned. Number times when a buffer has been pinned is number of logical reads, except examinations and direct-path reads.
– is called when Oracle in some point of source code does not know if a buffer is pinned or not. When it depends on some conditions and on data.

Notice that there are no calls related exactly with latches such as kslget or ksl_get_shared_latch. It is what I was talking about scripts KSLBEGIN/KSLEND inside procedures kcbgtcr and kcbrls (look at Important note #1).


Appendix
There is an option to trace buffer pinning by set the parameter _trace_pin_time:

alter system set "_trace_pin_time"=1 scope=spfile;

The database should be restarted.

In this case buffer pins (where, data block address, time) in all sessions will be traced. Example:

pin kdiwh15: kdifxs dba 0x100071d:1 time 3630971669
pin kdswh05: kdsgrp dba 0x1000714:1 time 3630973016
pin kdswh05: kdsgrp dba 0x1000713:1 time 3630973465
pin kdswh05: kdsgrp dba 0x1000714:1 time 3630974088
pin kdswh05: kdsgrp dba 0x1000713:1 time 3630974532

Every row contains

pin <where> dba <DBA> time <timestamp>

“where” means function name inside which buffer has been pinned.

Following query can be used in order to see used (active) buffer handles

select bf.*, 
       w.kcbwhdes
  from (select b.*, 
               decode(kcbbfcr, 1, 'CR', 'CUR') cr
          from x$kcbbf b
         where bitand(b.kcbbfso_flg, 1) = 1) bf,
       x$kcbwh w
 where bf.kcbbfwhr = w.indx

Join with x$kcbwh here is helpful just to see kcbwhdes which means “where” that is a function name where buffer has been pinned.


Post Scriptum
I hope it helped you to understand better what is “buffer is pinned count” and how to use the Digger.

By the way look at the annoucements at the right side of the page. It is what I am going to publish next time. Next blog entries will be about timing, rowsource statistics and wait events.
Coming soon.

About these ads
  1. February 8, 2013 at 11:37 am

    The basic argument here is twofold. First, for index scans, the argument is that the larger the block size, the shorter the index (its height, BLEVEL) hence the faster the index. Second, for full table scans, the larger the block the faster since there are more rows per block and hence fewer blocks to read.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: