SQL> select id,type,status,namespace,name from v$result_cache_objects;
3 Dependency Published SH.CUSTOMERS
110 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489
109 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=103379
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers
OK all good so what happens if we update the customers table ?
SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;
Branden Kennedy
SQL> update customers set cust_first_name='Brandon' where cust_id=104489;
1 row updated.
OK so now what has happened to the cache ?
SQL> select id,type,status,namespace,name from v$result_cache_objects;
3 Dependency Published SH.CUSTOMERS
110 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489
109 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=103379
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers
STILL SHOWING AS CACHE !
Why ?
Let's COMMIT !
SQL> commit;
Commit complete.
SQL> select id,type,status,namespace,name from v$result_cache_objects;
3 Dependency Published SH.CUSTOMERS
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers
109 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=103379
110 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=104489
8 rows selected.
So now committing has caused the dependency on CUSTOMERS to force a flush on those cached items.
Now if we reissue the SQL we can see
SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;
Brandon Kennedy
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 | 39tgvk9kh2k8y33jug4hnv7mr5 | | | | |
| 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"=104489)
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=104489"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select cust_first_name,cust_last_name from customers where cust_id=104489;
Brandon Kennedy
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 | 39tgvk9kh2k8y33jug4hnv7mr5 | | | | |
| 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"=104489)
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=104489"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We have as expected the 3 consistent gets, so it is bypassing the cache.
If we reissue
SQL> /
Brandon Kennedy
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 | 39tgvk9kh2k8y33jug4hnv7mr5 | | | | |
| 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"=104489)
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=104489"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So now it's caching again.
Let's look at the result cache info again
SQL> select id,type,status,namespace,name from v$result_cache_objects
2 ;
3 Dependency Published SH.CUSTOMERS
112 Result Published SQL select cust_first_name,cust_last_name from customers where cust_id=104489
111 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
108 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
2 Result Published SQL SELECT USERENV('SESSIONID') FROM DUAL
1 Result Published SQL SELECT DECODE('A','A','1','2') FROM DUAL
0 Result Published SQL SELECT USER FROM DUAL
4 Result Invalid SQL select cust_id from customers
109 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=103379
110 Result Invalid SQL select cust_first_name,cust_last_name from customers where cust_id=104489
10 rows selected.
A new Cache entry ! Note there are now TWO entries for the 104489 lookup, One is invalid and the other is Published ( available for use ! )
So be very careful about selecting from this table and using the information.
This brings me to the last item in this post... dependency checks
V$RESULT_CACHE_DEPENDENCY will tell us what object dependencies are in the cache.
SQL> select * from V$RESULT_CACHE_DEPENDENCY;
112 3 76289
That's not very useful
let's enhance a little
select d.result_id,o.owner,o.object_name,o.object_type from V$RESULT_CACHE_DEPENDENCY D, dba_objects o where d.object_no=o.object_id
/
SQL> /
RESULT_ID OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------ -------------------
112 SH CUSTOMERS TABLE
That's a little better... so we can see that our 112 cache from above, depends on the customers table. Which explains why if we commit a change to the customers table the cached results are invalid.
That's enough for now.... More soon
No comments:
Post a Comment