This discussion is archived
3 Replies Latest reply: Oct 13, 2013 3:29 AM by Jonathan Lewis RSS

db block gets during execution of select

Alex.UA Newbie
Currently Being Moderated

Hi,

 

db block gets become to appear during execution of recursive subquery factoring clause if recursion level is big enough.

For example, if recursive member is being executed 1M times then there are more then 3M of db block gets.

SQL> with tmp(id) as (select 1 id from dual union all select id + 1 from tmp where tmp.id + 1 <= 1000000)

  2  select count(*) from tmp;

 

  COUNT(*)

----------

   1000000

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3653425330

 

-------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |      |     1 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE                            |      |     1 |            |          |

|   2 |   VIEW                                     |      |     2 |     4   (0)| 00:00:01 |

|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |            |          |

|   4 |     FAST DUAL                              |      |     1 |     2   (0)| 00:00:01 |

|*  5 |     RECURSIVE WITH PUMP                    |      |       |            |          |

-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   5 - filter("TMP"."ID"+1<=1000000)

 

 

Statistics

----------------------------------------------------------

         19  recursive calls

    3142654  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        346  bytes sent via SQL*Net to client

        359  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

    1000001  sorts (memory)

          0  sorts (disk)

          1  rows processed

Output of bufprof (1.02 by Tanel Poder) with 10000 samples during execution is below (you can find bufprof 1.02 here Re: Long running update):

       SID   KCBBFWHY KCBBFSO_FLG TO_CHAR(K    KCBBFCM KCBBFSO_ DEC KCBWHDES                       ADDR     TOTAL_SAMPLES ACTIVE_PCT

---------- ---------- ----------- --------- ---------- -------- --- ------------------------------ -------- ------------- ----------

       192          0           1    100001          0 BF7B25E8 CR  kdswh11: kdst_fetch            BD94F214         10000      24520

       192          0           1         0          0 BF7B1DF4 CUR kditwh00: kditpin              BD951944          6442  15795.784

       192          0           1         0          0 BF7B1DF4 CUR kditwh01: kditunp              BD951944          2406   5899.512

       192          0           1         0          0 BD950EF4 CUR kditwh00: kditpin              BD951944            13     31.876

       192          0           1         0          0 BF7B1DF4 CUR ktswh108: ktsbbsrch            BD951944             7     17.164

       192          0           1         0          0 BF7B1DF4 CUR ktswh133: ktsbvopn             BD951944             7     17.164

       192          0           1         0          0 BF7B1DF4 CUR ktswh102: ktsbvini             BD951944             7     17.164

       192          0           1         0          0 BF7B1DF4 CUR ktswh100: ktsbgsp              BD951944             4      9.808

       192          0           1         0          0 BD950EF4 CUR kditwh01: kditunp              BD951944             3      7.356

       192          0           1         0          0 BF7B1DF4 CUR ktswh123: ktsbbfill            BD951944             2      4.904

Could anyone advise why is db block gets count not zero for this select statement?

And the main question is how to link information from x$kcbbf (used for above report) to x$bh to find out what blocks are affected?

 

Thanks

  • 1. Re: db block gets during execution of select
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    HI,

    i think this link should help you

     

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:848805436235


    ----

    Ramin Hashimzade

  • 2. Re: db block gets during execution of select
    Alex.UA Newbie
    Currently Being Moderated

    Hi Ramin,

     

    How do you think Tom's answer "interesting, I do not know the root cause, if I stumble upon it - i'll try to update" can help me?

  • 3. Re: db block gets during execution of select
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    If you set statistics_level to all, or add the hint /*+ gather_plan_statistics */ to the query you'll find that the buffer gets occur on line 3 (the UNION ALL), and you should also see that you get a load of writes and possibly some reads. Notice, also, that the number of sorts matches the number of rows - every recursion triggers a sort, albeit a very small one.

     

    After a large enough number of recursions Oracle starts dumping the sort operations to the temp space, so it has to read temp blocks from the buffer cache to re-acquire the data - and these temp blocks are probably current because your session is the only thing that could have changed them.

     

    To cross check, run the query for a large level of recursion and look at v$sql_workarea_active while it's running; scan x$bh (or v$bh) aggregating tablespace number, and state to see the increasing appearance of XCUR buffers in the temp tablespace.

     

     

    Technically it is possible to link the buffer pins back to  buffers but they form a doubly linked list starting from us_nxt and us_prv (or wa_nxt and wa_prv for a waiter) on x$bh. basically this means it's not realistic to try to write some SQL that could connect all the columns together in real time.

     

    In this case, though, each pinned buffer is private to the session - so the chain would be a single link with forward and backward pointers pointing into x$bh. If there's nothing else running on the system you could then determine which buffers were pinned simply by looking for x$bh entries where us_nxt and us_prv weren't "empty" pointers, pointing back to themselves. This requires a little contorted thinking - here's a code sample:

     

     

    select *

    from    (

            select

            ts#, file#, dbarfil, dbablk, class, state, mode_held,

                    us_nxt, us_prv, to_number(us_prv,'xxxxxxxxxxxxxxxx') - to_number(us_nxt,'xxxxxxxxxxxxxxxx') user_diff,

                    wa_nxt, wa_prv, to_number(wa_prv,'xxxxxxxxxxxxxxxx') - to_number(wa_nxt,'xxxxxxxxxxxxxxxx') wait_diff,

                                    to_number(wa_nxt,'xxxxxxxxxxxxxxxx') - to_number(us_nxt,'xxxxxxxxxxxxxxxx') user_wait_diff

            from

                    x$bh

            )

    where

            user_wait_diff != 16

    ;

     

    If a buffer is not pinned, us_nxt =us_prv and points to the buffer header; however if it is pinned by just one session both pointers point to the same buffer pin, so we need a method of recognising the case where the address is not the address of the buffer.  Fortunately wa_nxt and wa_prv also point to a buffer when the buffer is not pinned. However, the pointers don't point to the buffer header address, they point to the address of us_nxt and wa_nxt respectively, and wa_nxt is 16 bytes offset from us_nxt (for 64-bit Oracle).

     

    So this code looks for cases where the gap between wa_nxt and us_nxt is NOT 16 bytes !

    I think I may have written something about this in Oracle Core.

     

     

    Regards

    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points