jump to navigation

Quick Tips

I like blogs, books and articles that give evidence for their advice and assertions. Some people don’t. So I’ll try putting here the points I make, without the supporting evidence.

 

COUNT(*), COUNT(1), COUNT(PK)
They are as fast as each other, supposed differences are urban myths. See here.

What are Consistent Gets and DB Block Gets
NB this is a simplified version of the full truth

Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a view consistent at a point in time {so it takes into account your changes and other people’s changes, commited or not) but most of the time a reconstruction is not needed, The block is just got from the cache.
DB Block Gets – When the very latest version of the block is needed. ie it is being changed (you don’t want to change a reconstructed version!) or internal processing, getting segment headers for scans and the like.
Physical Reads – Where Oracle has to get a block from the IO subsystem

 

Select Min and Max

Selecting just MIN(x) or MAX(x) from a table (where the column X has a supporting index) are incredibly fast. Doing them together in one statement as select min(x),max(x) is not. See here.

MBRC and segment scans

if you have no system stats, when the CBO calculates the cost of scanning a segment, the cost is the number of blocks divided by a modified value of the db_file_multiblock_read_count. This modified value only increases slightly as the value of db_file_multiblock_read count raises above 16

Leave a comment