The relationship between oracle comments and query hints (result_cache_mode)
1. Result_cache_mode takes precedence over table annotations.
Create table test_Result_cache (id number) result_cache (mode default)
The value mode default removes only any table comments that have been set, and does not allow query results that contain this table to be cached.
SQL > select t.tableroomname from user_Tables .resultroomcache t where t.tableroomnamewriting TESTSTABSTRESULTRESULTCACHE'
TABLE_NAME RESULT_CACHE
-
TEST_RESULT_CACHE DEFAULT
The above statement to create a table has the same effect as the following statement to create a table.
Create table test_Result_cache (id number)
Let's take a look at the settings of the relevant result set cache parameters
SQL > show parameter result_cache
NAME TYPE VALUE
-
Client_result_cache_lag big integer 3000
Client_result_cache_size big integer 0
Result_cache_max_result integer 5
Result_cache_max_size big integer 4608K
Result_cache_mode string MANUAL
Result_cache_remote_expiration integer 0
When you need to cache the result set, you can use query hints, as follows
Select / * + result_cache*/* from test_Result_cache
You can check whether the result set is cached successfully in the following ways
SQL > select id,name,value from v$result_cache_statistics
ID NAME VALUE
-1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 32
4 Result Size Maximum (Blocks) 230
5 Create Count Success 5
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 0
13 Latch (Share) 0
Create Count Success: represents the number of result sets cached successfully.
2. Case II in which result_cache_mode takes precedence over table annotations.
Alter table test_result_cache result_cache (mode force)
Then make sure that the value of result_cache_mode is MANUAL
SQL > show parameter result_cache_mode
NAME TYPE VALUE
-
Result_cache_mode string MANUAL
Clear the data in the result set cache.
SQL > exec dbms_result_cache.Flush
PL/SQL procedure successfully completed
SQL > select id,name,value from v$result_cache_statistics
ID NAME VALUE
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 0
4 Result Size Maximum (Blocks) 230
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
12 Find Copy Count 0
13 Latch (Share) 0
Test the situation with the following statement
SQL > select / * + no_result_cache*/* from test_Result_cache
Carry out the plan
Plan hash value: 5006760
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 2 | 26 | 3 (0) | 00:00:01 |
| | 1 | TABLE ACCESS FULL | TEST_RESULT_CACHE | 2 | 26 | 3 (0) | 00:00:01 |
-
Note
-
-dynamic statistics used: dynamic sampling (level=2)
As you can see from the above view, the query does not use the contents of the result set cache. You can also query related views directly.
SQL > select id,name,value from v$result_cache_statistics
ID NAME VALUE
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 0
4 Result Size Maximum (Blocks) 230
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
12 Find Copy Count 0
13 Latch (Share) 0
The result is the same.
3. The situation in which the annotation of the table takes precedence over result_cache_mode.
Alter table test_result_cache result_cache (mode force)
You can check the value of result_cache_mode at this time.
SQL > show parameter result_cache_mode
NAME TYPE VALUE
-
Result_cache_mode string MANUAL
At this point, the data in the result set cache will be read directly through the following query
SQL > select * from test_Result_cache
Carry out the plan
Plan hash value: 5006760
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 2 | 26 | 3 (0) | 00:00:01 |
| | 1 | RESULT CACHE | 5z4pvwymt41zz4hjnb3pwvcfuy |
| | 2 | TABLE ACCESS FULL | TEST_RESULT_CACHE | 2 | 26 | 3 (0) | 00:00:01 |
-
Result Cache Information (identified by operation id):
1-column-count=1; dependencies= (DESIGNER.TEST_RESULT_CACHE); name= "select * from test_Result_cache"
Note
-
-dynamic statistics used: dynamic sampling (level=2)
You can also view the number of cached result sets directly
SQL > select id,name,value from v$result_cache_statistics
ID NAME VALUE
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 32
4 Result Size Maximum (Blocks) 230
5 Create Count Success 6
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 1
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 0
13 Latch (Share) 0
