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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
