( we are using stock SH.CUSTOMERS table )
( Well actually I've found out there's a bug in DBMS_XPLAN.DISPLAY_CURSORs, it doesn't display result set caching ! back to Autotrace, hopefully that'll be OK !
Annnyway
set long 32000
set linesize 32000
set pagesize 0
set autotrace on
alter system set result_cache_mode=manual;
select cust_first_name,cust_last_name from customers where cust_id=103379;
set autotrace off
-- select cust_first_name,cust_last_name from customers where cust_id=103379;
-- select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));
quit;
System altered.
Alana Yoon
Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID"=103379)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
[oracle@oel6u3m1 xplain]$
So our nice little plan costs us 3 gets. if we were doing this a bazillion times we would have to probe the BC for the blocks every time. Why not just cache the results of the query ?
et long 32000
set linesize 32000
set pagesize 0
set autotrace on
alter system set result_cache_mode=force;
select cust_first_name,cust_last_name from customers where cust_id=103379;
set autotrace off
quit;
System altered.
Alana Yoon
Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 3d41f60nry3b51vp54prrk2m38 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID"=103379)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(SH.CUSTOMERS); attributes=(single-row); name="select cust_first_name,cust_last_name from customers where cust_id=103379"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
... If we look we can see that as soon as we turn caching on for Results the consistent gets goes to Zero !
so instead of a bazillion hits we have zero.
Now again this is still only server side caching, not front-end... let's look at that in a few blog posts.
Relevant Parameters are
result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0
Check into these variables; each should be considered carefully
No comments:
Post a Comment