Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.