Server process will receive the statement sent by user process on server side and will handover that to library cache of shared pool
The 1st phase of sql execution i.e Parsing will be done in library cache.
Then, OPTIMIZER (brain of oracle sql engine) will generate many execution plans, but chooses the best one based on time & cost .(time – response time, cost – cpu resource utilization)
Server process will send the parsed statement with its execution plan to PGA and 2nd phase i.e EXECUTION will be done there.
After execution, server process will start searching for the data from LRU end of LRU list and this search will continue till it founds data or reaches MRU end. If it found data, it will be given to the user. If it didn’t found any data, it means data is not there in database buffer cache.
In such cases, server process will copy data from datafiles to MRU end of LRU list of database buffer cache.
From MRU end the rows pertaining to requested table will be filtered and placed in SERVER RESULT CACHE along with execution plan id and then it will be given to user (displayed on user’s console)
Note : for statements issued for the second time, server process will get parsed tree and plan id from library cache and it will straightly goes to server result cache and compares the plan id. If the plan id matches, corresponding rows will be given to user. So, in this case, it is skipping all 3 phases of SQL execution by which response time is much faster than 10g database.
SERVER RESULT CACHE
It is new component introduced in 11g.
Usage of result cache Is dependent on parameters RESULT_CACHE_MODE and RESULT_CACHE_MAX_SIZE
The possible values for RESULT_CACHE_MODE is MANUAL or FORCE. When set to MANUAL, sql query should have hint /* result cache */. When using FORCE all queries will use result cache.
Even though after setting to FORCE, we can still avoid any query to use result cache using hint /* no result cache */
Oracle recommends to enable result cache only if database is hitting with lot of statements which are frequently repeated. So it must be enabled in OLTP environment.
If we specify MEMORY_TARGET parameter, oracle will allocate 0.25% of shared pool size as result cache. If we specify SGA_TARGET (which is of 10g), result cache will be 0.5% of shared pool. If we use individual parameters (like in 9i), result cache will be of 1% size of shared pool.
When any DML/DDL statements modify table data or structure, data in result cache will become invalid and need to be processed again.