How Sql statement is executed in Oracle

1. Shared pool contains 2 important things ( Locks and Result cache) . So when a user request for data first the result cache is checked to see if the data already exist. A simple case is previous user also requested for same data
2. If its not found then oracle tried to construct the resultset ( answer of your sql) . First it checks if the required table( data blocks) for creating the output exist in the buffer cache. Also it wont be able to find all the blocks in database buffer cache it has to read some  tables (data blocks) from the database
3. If  they exist it check the redo log to see if they are the current data blocks and no changes have been made. ( this what makes database  in sync with ACID properties)

4. If no changes have been made recently then it reads those blocks form Database buffer cache

Point to be noted -- When we are inserting some data and we say commit the data is inserted from the Redo log buffer

so you run some sql, this sql is going to do some operation like

o hashing

o sorting (order by, analytics, sort distinct, group by sort, etc...)

o etc - anything that needs "memory"

Oracle will allocate a workarea (a sort area, a hash area, whatever). The size of this workarea will be determined by Oracle based on the current workload on the system.

If this workarea (in your pga) fills up - and we are not done with the operation (the sort, the hash, whatever) we will swap the contents of that out to disk - a write to temp. Later, we'll read it back in. Temp works like "virtual memory" in a sense - we page your pga out to it and page it back in (it is not true virtual memory, that is just an analogy)

All the operations you mentioned above - group by, hash joins, analytic functions occur in the pga - but could definitely be swapped out to temp.

As for the large pool, we use that in parallel query to send messages back and forth, the parallel execute servers are in different processes - they cannot talk directly to eachother since they each have their own address space. Hence, one process will put some data into the large pool (a piece of SHARED memory that everyone can access) and another will read it from there. It is just a shared memory structure that everyone - every oracle process - can read/write.