DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: buffer_gets

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Anyone know the units of buffer_gets in v$sqlarea? Is it in blocks or bytes?

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    it's in blocks

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    The value of buffer_gets in v$sqlarea is in blocks.It shows the number of database buffers already found in the database buffer cache for the cursor.

    In case of any help please be free to write to me at rohitsn@altavista.com


    Regards,
    Rohit Nirkhe,Oracle DBA,OCp 8i

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    howly blocks?

    I have a SQL with buffer_gets of 448 blocks! Block size 8K! And just returns 10 rows

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    buffer_gets gives the number of buffers that Oracle had to scan to bring you the result, if you do a full scan on a big table, you can have your SQL using 20000 blocks and just giving one line as a result ...

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well what I mean is that the SQL is not very effcient ;D

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Buffer_gets is more commonly known as logical reads. Logical reads represent how many physical reads would have to be done if there were *absolutely* no caching.

    For example, A simple indexed lookup of 2 rows in a single table would be, not counting any parsing:

    - Get root index node
    - Follow to next level node (assume leaf node)
    - Follow to actual row
    - Get root index node
    - Follow to next level node (assume leaf node)
    - Follow to actual row

    Resulting in 6 logical reads to return 2 rows in an incredibly simple example.

    If you are dealing with 5 tables and restrictions on each, you can easily run into the thousands of logical reads to return 2 rows *and still be optimal*. This is because a poorly-optimized query might run into the hundreds of thousands of Logical Reads. In the database I'm currently working on, some 'bad' statements run into the millions of logical reads. A 'good' version of the statement may still require 100,000 logical reads.

    The point is that it is all relative. If the statement is complex, or uses several tables or the tables are large, then 448 may be *very good* for returning 10 rows.

    Just my .02,

    - Chris

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well I would say it´s fairly complex, joining 9 tables, 6 of them are lookup tables, 3 are child tables

    lookup table 1 has 9382 records
    lookup table 2 has 53 records
    lookup table 3 has 8417 records
    lookup table 4 has 40 records
    lookup table 5 has 639 records
    lookup table 6 has 4 records

    child table 1 has 6752 records
    child table 2 has 56426 records
    child table 3 has 55485 records


    I would like to paste the SQL but it´s pretty big

    Anyway I am pasting it



    SELECT IC_INMOFERTA.PRE_INI, IC_INMIMG.CHK_IMG_DEF, IC_INMIMG.FIC_IMG_3,
    IC_SBTINMUEB.DES_SBT_INM, IC_ZONA.DES_ZON, IC_INMUEBLE.COD_INM,
    IC_INMUEBLE.COD_AGE, IC_INMUEBLE.NUM_HAB, IC_INMUEBLE.NUM_BANO, IC_INMUEBLE.NUM_ASEO,
    IC_INMUEBLE.CHK_TRA, IC_INMUEBLE.CHK_AMU, IC_INMUEBLE.CHK_EXT, IC_INMUEBLE.SUP_UTIL,
    IC_INMUEBLE.DESCRIPC, IC_INMUEBLE.CHK_NUE, IC_EMPRESA.RZN_SCL, IC_EMPRESA.CALLE,
    IC_EMPRESA.NUM_CALL, IC_EMPRESA.COD_POS, IC_EMPRESA.COD_COM, IC_EMPRESA.COD_POB,
    IC_EMPRESA.COD_PRV, IC_EMPRESA.DES_COR_AGE, IC_EMPRESA.TEL_1, IC_EMPRESA.TEL_2,
    IC_EMPRESA.FAX, IC_EMPRESA.EMAIL, IC_EMPRESA.DES_URL, IC_EMPRESA.COD_TIP_EMP,
    IC_POBLACION.DES_POB, IC_PROVINCIA.DES_PRV, IC_TIPEMPRESA.DES_TIP_EMP


    FROM IC_INMIMG, IC_INMOFERTA, IC_INMUEBLE,
    IC_POBLACION, IC_PROVINCIA, IC_ZONA, IC_SBTINMUEB, IC_EMPRESA, IC_TIPEMPRESA


    WHERE (IC_INMOFERTA.COD_TIP_OFE = 1)
    AND (IC_INMUEBLE.COD_TIP_INM = 8)
    AND (IC_INMUEBLE.COD_COM = 10)
    AND (IC_INMUEBLE.COD_PRV = 8)
    AND (IC_INMUEBLE.COD_POB = 4128)
    AND (IC_INMOFERTA.CHK_EST_ON = 1)
    AND (IC_INMUEBLE.COD_EST_INM > 2)
    AND (IC_INMUEBLE.COD_AGE = IC_EMPRESA.COD_AGE)
    AND (IC_INMUEBLE.COD_INM = IC_INMOFERTA.COD_INM)
    AND (IC_SBTINMUEB.COD_SBT_INM = IC_INMUEBLE.COD_SBT_INM)
    AND (IC_EMPRESA.COD_POB = IC_POBLACION.COD_POB)
    AND (IC_EMPRESA.COD_PRV = IC_PROVINCIA.COD_PRV)
    AND (IC_INMUEBLE.COD_ZON = IC_ZONA.COD_ZON)
    AND ((IC_INMIMG.CHK_IMG_DEF = 1) OR (IC_INMIMG.CHK_IMG_DEF IS NULL))
    AND (IC_INMIMG.COD_INM (+)= IC_INMUEBLE.COD_INM)
    AND (IC_EMPRESA.COD_TIP_EMP = IC_TIPEMPRESA.COD_TIP_EMP)
    AND (IC_INMUEBLE.COD_INM IN (114830, 116174, 115982, 109187, 114784, 116652, 115992,
    116648, 114766, 114950))
    ORDER BY IC_INMIMG.CHK_IMG_DEF, IC_INMOFERTA.PRE_INI
    /



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width