Oracle maintains a "buffer header" data structure in the SGA for every buffer in its buffer cache. The buffer header contains the tablespace, file and block numbers for the corresponding cached block. Incidentally, the same information is also present in the block header itself. The array of buffer headers resides in the permanent memory part of the variable area of the SGA. The buffer headers also contain a number of in-memory pointers to other buffer headers. This is how the LRU lists and similar data structures are implemented.

Access to buffers in the cache is hash based. For this Oracle maintains an array of hash chain headers that point to linked lists of buffer headers for the buffers containing blocks that hash to the same hash chain. The hash value for each block is computed from its tablespace, file and block numbers. To search for a block in cache, Oracle just has to compute the hash value, choose the correct hash chain header, and search the hash chain. The hash chains are normally very short and so this is a very efficient process.

To determine which blocks need to be accessed to scan a particular table, Oracle first looks in the data dictionary to locate the segment header block. The segment header block contains an extent table that is used to find the other blocks that need to be accessed. Oracle first looks for each block in its buffer cache and if it is not found reads it from disk.

This is all greatly simplified, but I hope that gives you the general idea.

Whenever a SQL statement is executed, the Server process has to look into the SGA if any blocks corresponding to that SQL statements table exist or not. If they are not there, it has to load them. But to look for what all the blocks are existing in SGA, how does it do? Is there any list of blocks that is maintained which maps the tables to blocks in SGA?

Also, while the database writer (DBW) process has to write the blocks from SGA to files, where is the relationship between the buffer blocks and file blocks is maintained.