oracle 11g提供了dbms_result_cache包來查詢sql結果緩存内容的狀态和适當地控制sql結果緩存内容。
dbms_result_cache功能和存儲過程
功能/存儲過程 描述
status： 傳回結果緩存的目前狀态。值包括：
enabled： 結果緩存是激活的。
disabled：結果緩存是不可用的。
bypassed：結果緩存暫時不可用。
sync： 結果緩存是可用的，但是目前正與其他rac節點重新同步。
memory_report： 列出結果緩存記憶體利用的一個概要（預設）或詳細的報表。
flush： 推出整個結果緩存的内容。
invalidate： 使結果緩存中一個特定對象的緩存結果無效。
invalidate_object： 根據緩存id使一特定結果緩存無效。
下面通過具體的實驗給予介紹：
一 status： 傳回結果緩存的目前狀态。
dbms_result_cache.status()
--------------------------------------------------------
enabled
二 memory_report： 列出結果緩存記憶體利用的一個概要（預設）或詳細的報表。視圖v$result_cache_statistics 是memory_report相同的描述
r e s u l t c a c h e m e m o r y r e p o r t
[parameters]
block size = 1k bytes
maximum cache size = 15744k bytes (15744 blocks)
maximum result size = 787k bytes (787 blocks)
[memory]
total memory = 12704 bytes [0.001% of the shared pool]
... fixed memory = 12704 bytes [0.001% of the shared pool]
... dynamic memory = 0 bytes [0.000% of the shared pool]
pl/sql procedure successfully completed.
id name value
---------- ------------------------------ ---------------------------------------------------------------------------------
1 block size (bytes) 1024
2 block count maximum 15744
3 block count current 0
4 result size maximum (blocks) 787
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 global hit count 0
13 global miss count 0
13 rows selected.
count(*)
----------
74484
execution plan
----------------------------------------------------------
plan hash value: 362321706
------------------------------------------------------------------------------------------
| id | operation | name | rows | cost (%cpu)| time |
| 0 | select statement | | 1 | 297 (1)| 00:00:04 |
| 1 | result cache | 7uz1ww4x7gs2a6ba4qjauzt4bq | | | |
| 2 | sort aggregate | | 1 | | |
| 3 | table access full| yangobj | 61204 | 297 (1)| 00:00:04 |
result cache information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(yang.yangobj); attributes=(single-row); name="select /*+ result_cache */ count(*) from yangobj"
note
-----
- dynamic sampling used for this statement (level=2)
statistics
9 recursive calls
4 db block gets
1126 consistent gets
0 physical reads
548 redo size
528 bytes sent via sql*net to client
520 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
<a href="mailto:yang@rac1%3e/">yang@rac1>/</a>
0 recursive calls
0 db block gets
0 consistent gets
0 redo size
從視圖v$result_cache_objects中擷取cache_id。
name status cache_id
------------------------------ --------- -----------------------------------
yang.yangobj published yang.yangobj
select /*+ result_cache */ cou published 7uz1ww4x7gs2a6ba4qjauzt4bq
nt(*) from yangobj
三 invalidate_object： 根據緩存id--cache_id使一特定結果緩存無效。
select /*+ result_cache */ cou invalid 7uz1ww4x7gs2a6ba4qjauzt4bq
四 flush： 清理整個結果緩存的内容。
maximum cache size = 2080k bytes (2080 blocks)
maximum result size = 104k bytes (104 blocks)
total memory = 107812 bytes [0.049% of the shared pool]
... fixed memory = 9460 bytes [0.004% of the shared pool]
... dynamic memory = 98352 bytes [0.045% of the shared pool]
....... verhead = 65584 bytes
....... cache memory = 32k bytes (32 blocks)
........... unused memory = 26 blocks
........... used memory = 6 blocks
............... dependencies = 3 blocks (3 count)
............... results = 3 blocks
................... sql = 2 blocks (2 count)
................... invalid = 1 blocks (1 count)
pl/sql 過程已成功完成。
已用時間: 00: 00: 00.35
已用時間: 00: 00: 00.02
total memory = 9460 bytes [0.004% of the shared pool]
結果緩存已被清除。。
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)