Big Table and Small SGA [ Direct Path Read !? ]

 Given the below scenario, which document I should follow the underlying architecture, in more details?

1) Table TAB1 has a size of 40 GB

2) The SGA size is 2GB

3) User is firing ' select * from TAB1 '

My instincts are saying this query will not fail!

But not able to figure out how it will be successful?
Is this something like the below in a cyclic manner

==>bring block to memory==>from memory to client==>no more place in memory then overwrite db buffer cache

You're right, the query will succeed!

For "small" table, Oracle Database will load the table into the buffer cache. 

This is part of the SGA. 

If the cache is full, it overwrites what is there. 

Oracle uses a least recently used algorithm to decide what to replace.

To prevent overwriting all of this cache for "large" tables, it'll load the data into the PGA instead. 

This is a direct path read.

If the database needs to process data from the big table before returning to the client (e.g. for a join), it'll write the information to temp (disk).

You can find more details about the buffer cache at:

http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT1222

Look for "Buffers and Full Table Scans" to find the section that discusses this issue specifically.

=====================================================================

If we have a table which is of more size than than the SGA, then we will have to define the size of SGA again to the required size, and then the problem is solved.

we can define the size of SGA and PGA while creating the database.

Buffered Cache Working:

When we request data from disk, at minimum oracle reads one block. Even if we request only one row, many rows in the same table are likely to be retrieved, which lie in the same block. The same goes for column.

A block in the buffered cache can be in one of the three states:

• Free: Currently not used.
• Pinned: Currently being accessed.
• Dirty: Block has been modified but currently not been written to disk.

A block write up is triggered when one of the following happens:

1. The database is issued a shutdown command.
2. A full or partial checkpoint occurs.
3. A recovery time threshold occurs, which is again set by us.
4. A free block is needed and none are found after a given amount of time( we use LRU algorithm here).
5. Certain Data Definition Language commands(DDL).
6. Every three seconds. There are many other reasons, the algorithm is complex and can change with each release of oracle.