Re: Re: more consistent gets, but more quickly?

When Oracle 'expects' to visit a buffer more than once in a single call, it will hold the cache buffers chains latch long enough to create a pin (in this case a memory structure that associates the session with the buffer) and link it into the linked list of current users (x$bh.usprev, usnxt) of the buffer. On subsequent accesses to the block, Oracle need not grab the latch and search the bucket, instead it can jump to the block by way of the pin which will definitely be there as a pinned block may not be flushed from the buffer.

Visits which take this short-cut are recorded under the 'buffer is pinned count' statistic. So it is a logical I/O, but using a shorter, often cheaper, access path.

Typically it will be index leaf blocks that show most pinning, as they tend to be
revisited during range scans.

In your case, I assumed that the rebuilt table would result in there being more usable row entries per leaf block than there had been, so more jumps back and forth from index to table each time a leaf was pinned - hence more pins, fewer gets.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 29 January 2003 06:42

Jonathan Lewis,
Can you interpret more about this statistics?How does this affect the sql running time? And how did you think about this statistics that is seldom used?
The following is the test result:

------sql1:

   00:00:01.58 00:00:01.59

NAME                                     VALUE               VALUE
----------------------------------- ----------           ---------
CPU used by this session                   160                 161
CPU used when call started                 160                 161
buffer is not pinned count               41612               41604
buffer is pinned count                 1685183             1685183
consistent gets                          43911               43907
no work - consistent read gets           43893               43889
session logical reads                    43914               43910


Elapsed:
------sql2: 00:00:01.69     00:00:01.71
NAME                                      VALUE               VALUE
------------------------------------ ----------          ----------
CPU used by this session                    170                 171
CPU used when call started                  170                 171
buffer is not pinned count                19889               19889
buffer is pinned count                  1706898             1706898
consistent gets                           22192               22192
no work - consistent read gets            22174               22174
session logical reads                     22195               22195

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).