Oracle 11g has now extended its cache option to queries result set.
I know the result, the query to retrieve is also very the same and no data has been changed, in this case if i re-execute the query should Oracle SQL engine proceed with the entire SQL life cycle ? can't it just retrieve the result for me ?
And that's what the optimizer does with Query Result Cache, Lets see Query Result Cache in detail, SQL> REM--JUST A NORMAL QUERY ;
SQL> select count(id_vh) from tbl_rc where id > 0;
COUNT(ID_VH)
------------
100001
Elapsed: 00:00:00.21
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 73 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TBL_RC | 99999 | 1269K| 73 (2)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">0)
Statistics
----------------------------------------------------------
254 recursive calls
0 db block gets
314 consistent gets
--289 physical reads
0 redo size
428 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
Now i know the result and the data is not gonna change. Under these circumstances one can leverage the result cache to minimize the load on SGA. Prior to usage of result cache, validate if it is enabled like below,
SQL> select dbms_result_cache.status() from dual; DBMS_RESULT_CACHE.STATUS() ------------------------------------------------- ENABLED
As part of Query Result Cache, the optimizer executes the query and looks in the cache memory to determine whether the result exists in the cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.
This is primarily based on the parameter RESULT_CACHE_MODE which instructs oracle to perform the same. They operate under two modes - MANUAL and FORCE.
In MANUAL mode the result set are cached through a hint /*+RESULT_CACHE*/
Result Cache in MANUAL Mode -
Manual Mode - First Run
Result Cache in MANUAL Mode -
SQL> SQL> alter session set result_cache_mode=MANUAL; Session altered. Elapsed: 00:00:00.00
Manual Mode - First Run
SQL> select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0;
COUNT(ID_VH)
------------
100001
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 73 (2)| 00:00:01 |
| 1 | RESULT CACHE | b7f8nxtz1mvuj8q1ph0jj6quxx | | | | |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 99999 | 1269K| 73 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">0)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
284 consistent gets
--278 physical reads
0 redo size
428 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Above is the first run with result cache hint, oracle does an hard parse and generates the cache id "b7f8nxtz1mvuj8q1ph0jj6quxx" for the given SQL. This would in-turn point to the result set stored in the result cache. On re-executing the query optimizer would pick the data from the cache without actually executing the query
Manual Mode - Second Run
SQL> select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0;
COUNT(ID_VH)
------------
100001
"Elapsed: 00:00:00.00"
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 73 (2)| 00:00:01 |
| 1 | RESULT CACHE | b7f8nxtz1mvuj8q1ph0jj6quxx | | | | |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 99999 | 1269K| 73 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">0)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select /*+ RESULT_CACHE */ count(ID_VH) from tbl_rc where id > 0"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
"0 physical reads"
0 redo size
428 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Nill Physical reads, Elapsed time as zero and the usage of Result Cache Id(b7f8nxtz1mvuj8q1ph0jj6quxx) indicates that the optimizer has taken a new route without going through the traditional SQL lifecycle.
Result Cache in FORCE Mode -
In Force mode by default oracle would perform
caching the result, even when RESULT_CACHE
hint is not specified in the query.
SQL> SQL> alter session set result_cache_mode=FORCE; Session altered. Elapsed: 00:00:00.00
Force Mode - First Run
SQL> REM /* FIRST RUN - Query WITHOUT Result Cache Hint */
SQL> select count(ID) from tbl_rc where id > 3;
COUNT(ID)
----------
99996
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 73 (2)| 00:00:01 |
| 1 | RESULT CACHE | 3cc3zwwpyb7nhbkbt78kc9vpu9 | | | | |
| 2 | SORT AGGREGATE | | 1 | 5 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 99997 | 488K| 73 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">3)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select count(ID) from tbl_rc where id > 3"
Statistics
----------------------------------------------------------
1540 recursive calls
0 db block gets
509 consistent gets
288 physical reads
0 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
Force Mode - Second Run
SQL> REM /* SECOND RUN - Re-Execution - Now Optimizer would pick result from result cache */
SQL>
SQL> select count(ID) from tbl_rc where id > 3;
COUNT(ID)
----------
99996
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2864772016
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 73 (2)| 00:00:01 |
| 1 | RESULT CACHE | 3cc3zwwpyb7nhbkbt78kc9vpu9 | | | | |
| 2 | SORT AGGREGATE | | 1 | 5 | | |
|* 3 | TABLE ACCESS FULL| TBL_RC | 99997 | 488K| 73 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID">3)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HARI.TBL_RC); attributes=(single-row);
name="select count(ID) from tbl_rc where id > 3"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
From the time consumed and statistics it is clear that the cache is used to retrieve the data. The physical reads has completely vanished from 288 (During First Run) to zero.
Thus the performance is same as that of MANUAL mode.
No comments:
Post a Comment