From 11g, Oracle has introduced a new component in SGA i.,e RESULT CACHE(RC). RC would store the result set of the queries and not the data blocks. Lets take a look at the oracle architecture,
This new component Result Cache is used to store the end result of a query or function. By storing the end result of a query whose data is static, I/O(Physical Reads) are reduced to an large extent.
Result Cache - Components
There are two components within Result Cache, they cache the output of query and PLSQL Function calls. Does this new component has an impact over buffer cache or is this impacted when ever shared pool is flushed. Lets discuss this in detail with a small example,
Step1 - Query using Result Cache
Step2 - Flushing Shared Pool
Lets re-execute the same SQL,
Step3 - SQL Re-Execution
Thus even after flushing the shared pool, the physical reads is still ZERO, indicating the retrieval of result from the result cache and not through the physical reads
Step4 - Flushing Buffer Cache
Step5 - SQL Re-Execution
Thus even after flushing the shared pool/buffer cache, the query has used the same CACHE ID and has managed to execute in just one millisecond. Unlike the buffer cache, the server result cache holds result sets and not data blocks.
Step4 - Restarting DB
The result set are stored in the cache, So a database restart should flush the results, lets check that too
Step5 - SQL Re-Execution
Yes the elapsed time difference and the physical reads indicate that the optimizer has gone for full SQL life cycle. The cache id paired with the SQL Text remain the same. Nether shared_pool flush nor buffer cache flush, flushes my result cache. To flush the result cache DBMS_RESULT_CACHE.FLUSH has to be used.
Result Cache - Components
There are two components within Result Cache, they cache the output of query and PLSQL Function calls. Does this new component has an impact over buffer cache or is this impacted when ever shared pool is flushed. Lets discuss this in detail with a small example,
Step1 - Query using Result Cache
SQL> REM /* Query Using Result Cache */;
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;
COUNT(ID)
----------
6930
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 11 (0)| 00:00:01 |
| 1 | RESULT CACHE | 04f8pmukbsk8tfstmbxb5pu9dx | | | | |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 6432 | 19296 | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">2.75)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step2 - Flushing Shared Pool
alter system flush shared_pool; System altered. Elapsed: 00:00:00.28
Lets re-execute the same SQL,
Step3 - SQL Re-Execution
REM --/* Re-Executing the same SQL */
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;
COUNT(ID)
----------
6930
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 11 (0)| 00:00:01 |
| 1 | RESULT CACHE | 04f8pmukbsk8tfstmbxb5pu9dx | | | | |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 6432 | 19296 | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">2.75)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"
Statistics
----------------------------------------------------------
235 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
Thus even after flushing the shared pool, the physical reads is still ZERO, indicating the retrieval of result from the result cache and not through the physical reads
Step4 - Flushing Buffer Cache
alter system flush buffer_cache; System altered. Elapsed: 00:00:00.13
Step5 - SQL Re-Execution
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;
COUNT(ID)
----------
6930
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 11 (0)| 00:00:01 |
| 1 | RESULT CACHE | 04f8pmukbsk8tfstmbxb5pu9dx | | | | |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 6432 | 19296 | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">2.75)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Thus even after flushing the shared pool/buffer cache, the query has used the same CACHE ID and has managed to execute in just one millisecond. Unlike the buffer cache, the server result cache holds result sets and not data blocks.
Step4 - Restarting DB
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 778387456 bytes Fixed Size 1374808 bytes Variable Size 268436904 bytes Database Buffers 503316480 bytes Redo Buffers 5259264 bytes Database mounted. Database opened.
The result set are stored in the cache, So a database restart should flush the results, lets check that too
Step5 - SQL Re-Execution
SQL> select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75;
COUNT(ID)
----------
6930
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 11 (0)| 00:00:01 |
| 1 | RESULT CACHE | 04f8pmukbsk8tfstmbxb5pu9dx | | | | |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 6432 | 19296 | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">2.75)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row); name
="select /*+ RESULT_CACHE */ count(id) from tbl_rc where id >2.75"
Statistics
----------------------------------------------------------
235 recursive calls
0 db block gets
66 consistent gets
39 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
Yes the elapsed time difference and the physical reads indicate that the optimizer has gone for full SQL life cycle. The cache id paired with the SQL Text remain the same. Nether shared_pool flush nor buffer cache flush, flushes my result cache. To flush the result cache DBMS_RESULT_CACHE.FLUSH has to be used.
No comments:
Post a Comment