buffer_gets

Anyone know the units of buffer_gets in v$sqlarea? Is it in blocks or bytes?

it's in blocks

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 [email protected]


Regards,
Rohit Nirkhe,Oracle DBA,OCp 8i

howly blocks?

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

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 ...

well what I mean is that the SQL is not very effcient ;D

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

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 :o

Anyway I am pasting it :o



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
/