在資料庫設計的相關技巧中，正規化是DBA耳熟能詳的基本技巧，而基本的正規化至少要達到第三正規化，才有正規化的效果。正規化可以帶來以下的一些好處：1.刪除重複資料。2.節省儲存空間。當然某些情況下，正規化可能導致效能上的問題。這時可以適時地使用反正規化來增加整體的效能，不過在進行反正規化之前，當然還是要先正規化，因為不是每個表格都需要反正規化。以下對正規化的規則做一些簡單的說明：
第一正規化：每個表格中都要有主鍵(Primary Key)用以辨識資料列，而且表格中不能有重複的欄位，同時每個欄位中只能有一筆值。
學號為主鍵，沒有重複欄位以及每個欄位只有一個值。因此範例表格滿足第一正規化。
第二正規化：當滿足第一正規化的要求後，表格中非主鍵的欄位，還必須跟主鍵有完全相依性(即可以由主鍵決定其他非主鍵的欄位)。
國文、數學、英文都與學號有著完全的相依性。所以此表格也滿足第二正規化的要求。
第三正規化：當滿足第二正規化後，非主鍵欄位之間不能存在相依性。
國文、數學、英文這些非主鍵欄位之間並沒有相依性。所以這個範例表格可以說符合第三正規化的表格。
也許有人會建議再加上一個欄位：總分，用來存放三科的總成績。可是總分除與學號(主鍵)相依外，還與國文、數學、英文的成績相依，為達到第三正規化的要求，所以不能加上總分的欄位。
因此當需要計算學生的總成績時，可以由下列方式得到總成績。
1.由AP人員必須將計算公式(國文+數學+英文)寫到程式中，這種方法的好處是，表格依然維持正規化的設計，但是若計算公式一但有所變動，則需要修改程式內容，以及必須將新的程式部署到各個客戶端。
2.然而DBA也可以透過建立一個視圖(VIEW)，將計算公式產生總分的虛擬欄位(create view student_view as select 國文,數學,英文,(國文+數學+英文) as 總分 from student)，這樣便可以透過視圖存取總分的資料。這種方式的缺點是必須多花一些維護成本在視圖的管理上。（
3.DBA直接在Student表格上，多加一個總分的欄位，其內容值為國文、數學、英文的總和。並利用觸發器(Trigger)在相關分數(國文、數學、英文)變動時，自動維護總分的內容，以保持資料的一致性。此方式即所謂的反正規化，可以增加查詢的效能，但也造成儲存的浪費，也增加了資料不一致的風險。所以反正規化在使用前必須仔細設計，避免相關的問題發生。
虛擬欄位(Virtual Column)介紹
由Oracle Database 11g開始，DBA可以宣告一種欄位形態：虛擬欄位，這種欄位並沒有真正的消耗儲存空間，只有在表格定義中存在。當此欄位被查詢時，才會使用欄位定義的公式計算欄位值。欄位定義的公式可以包括任何合法的運算以及函數。
SQL> create table t1
2 (a number,b number,c number generated always as (a+b) virtual);
/*generated always是為語法清晰而存在，virtual也是相同用途。所以可以直接使用c number as (a+b)即可*/
SQL> desc t1 /*這裡看不出來，是否有使用虛擬欄位*/
Name Null? Type
--------------------------------------------------------- ------ ---------
A NUMBER
B NUMBER
C NUMBER
SQL> select column_name,data_type,data_default from user_tab_columns
2> where table_name='T1';
COLUMN_NAM DATA_TYPE DATA_DEFAULT
---------- ---------- --------------------
A NUMBER
B NUMBER
C NUMBER "A"+"B" /*欄位公式*/
SQL> insert into t1 values(1,2,3); /*不能直接Insert/Update虛擬欄位的內容*/
insert into t1 values(1,2,3)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL> insert into t1 values(1,2);
insert into t1 values(1,2)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into t1(a,b) values(1,2); /*必須如此才能新增成功*/
或 insert into t1 values(1,2,default);
1 row created.
SQL> select * from t1;
A B C
---------- ---------- ----------
1 2 3
/*找出row真正存放的位置*/
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block#
3 from t1 where a=1 and b=2 and c=3;
FILE# BLOCK#
---------- ----------
1 40378
/*將Data Block內容dump到user tracefile*/
SQL> alter system dump datafile 1 block 40378;
SQL> show parameter user_dump_dest /*確認User tracefile的位置*/
NAME TYPE VALUE
----------------------------- --------- ----------------------------
user_dump_dest string /u03/diag/diag/rdbms/ora11g
/ora11g/trace
/*找到Server Process的OS Process ID*/
SQL> select spid from v$process
2 where addr=(select paddr from v$session where
3 sid=(select distinct sid from v$mystat));
SPID
------------------------
5000
SQL> !cat /u03/diag/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5000.trc
從Data Block的內容來看，可以證明虛擬欄位-C，並未佔有任何空間。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 /*A欄位*/
col 1: [ 2] c1 03 /*B欄位*/
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 40378 maxblk 40378
使用User Defined Function的注意事項
當虛擬欄位用到User Defined Function時，必須確定函數是Deterministic，即只要輸入相同的參數值，函數就一定回傳相同的結果。
SQL> create or replace function minus_10(x number) return number
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b)) virtual)
*
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> create or replace function minus_10(x number) return number deterministic
2 is
3 begin
4 return x-10;
5 end;
6 /
Function created.
SQL> create table t2 (a number,b number,c number as (minus_10(a+b) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b) virtual)
*
ERROR at line 1:
ORA-02000: missing ) keyword
SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
Table created.
虛擬欄位的操作
虛擬欄位在Oracle Database 11g中，被當作一般的欄位，所以除了不能直接新增、異動其內容外，其餘的操作如：當做主鍵、建立索引等都可以正常運行。
SQL> create index t1_c_idx on t1(c);
Index created.
SQL> select index_name,index_type from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE
------------------------------- ----------------------
T1_C_IDX FUNCTION-BASED NORMAL
/*從INDEX_TYPE可以得知，當建立索引在虛擬欄位上時，所建立的索引為Function-Based形態*/
SQL> alter table t1 add constraints t1_c_pk primary key(c);
Table altered.
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='T1';
CONSTRAINT_NAME C
------------------------ --
T1_C_PK P /*P表示為Primary Key*/
SQL> execute dbms_stats.gather_table_stats('SYS','T1'); /*收集統計值*/
PL/SQL procedure successfully completed.
/*虛擬欄位也可以當作分區欄位，這種分區方式稱為Virtual Column-Based Partitioning*/
SQL> create table t3
2 (a number,b number,c number as (a+b+100) virtual)
3 partition by range (c)
4 (partition p1 values less than(150),
5 partition p2 values less than(250),
6 partition p3 values less than(350));
虛擬欄位的使用限制
目前虛擬欄位在使用上，還存在著一些限制：
1.僅適用在Heap Table上，還不能用在Index-Organized、Cluster、External、Temporary以及Object等表格上。
2.虛擬欄位不能參考其他的虛擬欄位，只能參考同一個表格中的真實欄位。
3.當使用函數時，此函數必須宣告為Deterministic以及回傳的資料型態必須為Scalar。
4.虛擬欄位的資料型態不能為PL/SQL的資料形態、User Defined Type、LOB以及Long Raw等型態。
了解以上這些限制，可以幫助DBA在使用虛擬欄位時，更加的得心應手。
結論
資料庫表格設計的正規化是DBA的基本功，也可以減少資料庫的資料量，避免資料不一致的情況，同時更可以增加資料庫整體的效能。然而某一情況下，DBA可能採用反正規化的技巧，來解決某些效能上的問題，不過重複資料與內容不一致的風險也同時增加。如果DBA能夠正確地使用虛擬欄位的功能，便可以一方面擁有正規化的好處，同時也可以達到原本需要反正規化才能得到的效能提昇。
2010年11月1日 星期一
Oracle 11g New Features - Result Cache
當一支SQL指令由客戶端發送到伺服器端時，Server Process首先必須先將其解析為執行計畫，將執行計畫存放在Shared Pool後，才使用該執行計畫進行SQL操作，這樣當下次使用者執行相同的SQL指令時，便可以直接由Library Cache中取得執行計畫，而不需要再次解析一次。這樣在重複地執行相同的SQL指令的環境下，可以減少硬解析(Hard Parse)的次數，這樣對整體Oracle Server效能有很大的提昇。
同時當Server Process執行該執行計畫時，利用Buffer Cache將由資料檔讀入的資料區塊(data block)快取起來，往後如果有需要相同的資料區塊，便可以直接由Buffer Cache中取得，利用記憶體的存取速度遠快過於磁碟機的優勢，將原本的Disk I/O轉換成Memory I/O。因此即便是I/O的總數量不變，但是整體I/O的時間卻可以大幅下降，如此便可以減少SQL指令的反應時間。
Oracle11g更進一步將SQL指令的結果，直接快取起來，存放在Result Cache。因此當使用者執行相同的SQL指令時，不需再次執行該SQL指令，直接將存放在Result Cache中的結果集，直接回傳給使用者即可，如此可以得到更短的反應時間。同時Oracle11g會自動偵測結果集的正確性，如果相關資料表的內容有變動，將會將該結果集設定為失效，下次執行SQL指令時，會重新執行SQL指令產生最新的結果集並將其快取在Result Cache中，而Result Cache存放在Shared Pool中。
Result Cache的相關參數
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
result_cache_max_size big integer 1536K --Result Cache所佔的最大值
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
其中result_cache_mode決定是否自動使用RESULT CACHE功能。
如果result_cache_mode設定為MANUAL，查詢指令必須加上RESULT_CACHE的HINT才會使用RESULT CACHE。
反之result_cache_mode設定為FORCE，則會自動使用RESULT CACHE，除非加上NO_RESULT_CACHE才會不使用RESULT CACHE。
NOTE︰有些人發現可以將result_cache_mode設定為auto，並顧名思義地認為可以讓Oracle Server自動判斷是否要使用RESULT CACHE，但是根據Oracle內部的研發人員說明，AUTO現在不會有任何效果。
建立相關測試環境
建立一個測試表格
Connected.
SQL> create user frank
2 identified by oracle
3 default tablespace users
4 quota unlimited on users;
User created.
SQL> grant connect,resource to frank;
Grant succeeded.
SQL> create table frank.big1 as select * from dba_objects;
Table created.
SQL> insert into frank.big1 select * from frank.big1;
11654 rows created.
SQL> /
23308 rows created.
SQL> /
46616 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from frank.big1;
COUNT(*)
----------
93232
SQL> execute dbms_stats.gather_table_stats('FRANK','BIG1');
PL/SQL procedure successfully completed.
Result Cache的功能示範
SQL> show parameter result_cache_mode
NAME TYPE VALUE
----------------------------- --------- ------------------------------
result_cache_mode string MANUAL
SQL> set autotrace traceonly
/*底下的SQL指令將不會產生Result Cache，因為result_cache_mode為MANUAL*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 2 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets /*需要讀取1241個data blocks，才能完成*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*使用result_cahce的HINT，要求將此SQL的結果快取起來*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*以下的SQL使用result_cache的HINT，可以由Result Cache取得結果*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)
"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets /*沒有任何data block被讀取*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
/*查詢目前已被快取的結果集*/
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
1 Published
select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*)
2 Published
SELECT DECODE('A','A','1','2') FROM DUAL
0 Published
/*將Result Cache的內容請空*/
SQL> execute dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> alter session set result_cache_mode=FORCE;
Session altered.
/*自動將執行結果快取到Result Cache中*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
0 Published
select owner,count(*) from frank.big1 group by owner order by count(*)
1 Published
SQL> set autotrace on statistics
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets /*完全沒有任何I/O*/
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以使用V$RESULT_CACHE_DEPENDENCY的內容，查知與哪些表格有被快取。
SQL> select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
3 0 12082
SQL> col object_name format a30
SQL> select owner,object_name,object_type from dba_objects where object_id=12082;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
FRANK BIG1 TABLE
如果需要察看目前的Result Cache的狀態，可以透過V$RESULT_CACHE_STATISTICS或DBMS_RESULT_CACHE.MEMORY_REPORT得知。
SQL> col name format a30
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1536
3 Block Count Current 32
4 Result Size Maximum (Blocks) 76
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
SQL> set serveroutput on
SQL> execute dbms_result_cache.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 = 1536K bytes (1536 blocks)
Maximum Result Size = 76K bytes (76 blocks)
[Memory]
Total Memory = 116008 bytes [0.132% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 110876 bytes [0.126% of the Shared Pool]
....... Overhead = 78108 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Result Cache的內容會自動由Oracle Server維護，例如當Result Set中的相關表格內容被異動後，已快取的Result Set將會被設定為Invalid，同時相關的相依關係也會被消除。
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
2 select owner,count(*) from fra Published
nk.big1 group by owner order b
y count(*)
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
SQL> update frank.big1 set object_id=object_id where object_id=10000;
8 rows updated.
SQL> commit;
Commit complete.
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
2 select owner,count(*) from fra Invalid
nk.big1 group by owner order b
y count(*)
SQL> select * from v$result_cache_dependency;
no rows selected
結論
Result Set Cache的功能可以大幅降低SQL查詢指令的反應時間，尤其當記憶體的成本越來越低的現在，未來有更多的功能，會利用記憶體的空間，來節省整體的反應時間。不過這項功能不適合用在OLTP系統，因為即便已經將結果集快取後，但是表格內容一但被異動後，便失去其意義。因此這項功能應該用在DW/BATCH的資料庫才對。
同時當Server Process執行該執行計畫時，利用Buffer Cache將由資料檔讀入的資料區塊(data block)快取起來，往後如果有需要相同的資料區塊，便可以直接由Buffer Cache中取得，利用記憶體的存取速度遠快過於磁碟機的優勢，將原本的Disk I/O轉換成Memory I/O。因此即便是I/O的總數量不變，但是整體I/O的時間卻可以大幅下降，如此便可以減少SQL指令的反應時間。
Oracle11g更進一步將SQL指令的結果，直接快取起來，存放在Result Cache。因此當使用者執行相同的SQL指令時，不需再次執行該SQL指令，直接將存放在Result Cache中的結果集，直接回傳給使用者即可，如此可以得到更短的反應時間。同時Oracle11g會自動偵測結果集的正確性，如果相關資料表的內容有變動，將會將該結果集設定為失效，下次執行SQL指令時，會重新執行SQL指令產生最新的結果集並將其快取在Result Cache中，而Result Cache存放在Shared Pool中。
Result Cache的相關參數
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
result_cache_max_size big integer 1536K --Result Cache所佔的最大值
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
其中result_cache_mode決定是否自動使用RESULT CACHE功能。
如果result_cache_mode設定為MANUAL，查詢指令必須加上RESULT_CACHE的HINT才會使用RESULT CACHE。
反之result_cache_mode設定為FORCE，則會自動使用RESULT CACHE，除非加上NO_RESULT_CACHE才會不使用RESULT CACHE。
NOTE︰有些人發現可以將result_cache_mode設定為auto，並顧名思義地認為可以讓Oracle Server自動判斷是否要使用RESULT CACHE，但是根據Oracle內部的研發人員說明，AUTO現在不會有任何效果。
建立相關測試環境
建立一個測試表格
Connected.
SQL> create user frank
2 identified by oracle
3 default tablespace users
4 quota unlimited on users;
User created.
SQL> grant connect,resource to frank;
Grant succeeded.
SQL> create table frank.big1 as select * from dba_objects;
Table created.
SQL> insert into frank.big1 select * from frank.big1;
11654 rows created.
SQL> /
23308 rows created.
SQL> /
46616 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from frank.big1;
COUNT(*)
----------
93232
SQL> execute dbms_stats.gather_table_stats('FRANK','BIG1');
PL/SQL procedure successfully completed.
Result Cache的功能示範
SQL> show parameter result_cache_mode
NAME TYPE VALUE
----------------------------- --------- ------------------------------
result_cache_mode string MANUAL
SQL> set autotrace traceonly
/*底下的SQL指令將不會產生Result Cache，因為result_cache_mode為MANUAL*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 2 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets /*需要讀取1241個data blocks，才能完成*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*使用result_cahce的HINT，要求將此SQL的結果快取起來*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1241 consistent gets
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
/*以下的SQL使用result_cache的HINT，可以由Result Cache取得結果*/
SQL> select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*);
Execution Plan
----------------------------------------------------------
Plan hash value: 2655342821
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 45 | 346 (3)| 00:00:05 |
| 1 | RESULT CACHE | 86cxjfsndphbsd664abz33z4bt | | | | |
| 2 | SORT ORDER BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 3 | HASH GROUP BY | | 9 | 45 | 346 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| BIG1 | 93232 | 455K| 338 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(FRANK.BIG1); parameters=(nls); name="select /*+ result_cache */ owner,cou
nt(*) from frank.big1 group by owner order by count(*)
"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets /*沒有任何data block被讀取*/
0 physical reads
0 redo size
638 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
/*查詢目前已被快取的結果集*/
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
1 Published
select /*+ result_cache */ owner,count(*) from frank.big1 group by owner order by count(*)
2 Published
SELECT DECODE('A','A','1','2') FROM DUAL
0 Published
/*將Result Cache的內容請空*/
SQL> execute dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
no rows selected
SQL> alter session set result_cache_mode=FORCE;
Session altered.
/*自動將執行結果快取到Result Cache中*/
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
SQL> select name,id,status from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------
ID STATUS
---------- ---------
FRANK.BIG1
0 Published
select owner,count(*) from frank.big1 group by owner order by count(*)
1 Published
SQL> set autotrace on statistics
SQL> select owner,count(*) from frank.big1 group by owner order by count(*);
OWNER COUNT(*)
------------------------------ ----------
FRANK 8
TESTUSER 16
TSMSYS 24
ORACLE_OCM 64
OUTLN 72
DBSNMP 440
SYSTEM 4064
PUBLIC 23376
SYS 65168
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets /*完全沒有任何I/O*/
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以使用V$RESULT_CACHE_DEPENDENCY的內容，查知與哪些表格有被快取。
SQL> select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
3 0 12082
SQL> col object_name format a30
SQL> select owner,object_name,object_type from dba_objects where object_id=12082;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
FRANK BIG1 TABLE
如果需要察看目前的Result Cache的狀態，可以透過V$RESULT_CACHE_STATISTICS或DBMS_RESULT_CACHE.MEMORY_REPORT得知。
SQL> col name format a30
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1536
3 Block Count Current 32
4 Result Size Maximum (Blocks) 76
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
10 rows selected.
SQL> set serveroutput on
SQL> execute dbms_result_cache.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 = 1536K bytes (1536 blocks)
Maximum Result Size = 76K bytes (76 blocks)
[Memory]
Total Memory = 116008 bytes [0.132% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 110876 bytes [0.126% of the Shared Pool]
....... Overhead = 78108 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Result Cache的內容會自動由Oracle Server維護，例如當Result Set中的相關表格內容被異動後，已快取的Result Set將會被設定為Invalid，同時相關的相依關係也會被消除。
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
2 select owner,count(*) from fra Published
nk.big1 group by owner order b
y count(*)
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
SQL> update frank.big1 set object_id=object_id where object_id=10000;
8 rows updated.
SQL> commit;
Commit complete.
SQL> select id,name,status from v$result_cache_objects;
ID NAME STATUS
------- ------------------------------ ---------
0 FRANK.BIG1 Published
1 SELECT DECODE('A','A','1','2') Published
FROM DUAL
2 select owner,count(*) from fra Invalid
nk.big1 group by owner order b
y count(*)
SQL> select * from v$result_cache_dependency;
no rows selected
結論
Result Set Cache的功能可以大幅降低SQL查詢指令的反應時間，尤其當記憶體的成本越來越低的現在，未來有更多的功能，會利用記憶體的空間，來節省整體的反應時間。不過這項功能不適合用在OLTP系統，因為即便已經將結果集快取後，但是表格內容一但被異動後，便失去其意義。因此這項功能應該用在DW/BATCH的資料庫才對。
訂閱：
文章 (Atom)
