Hello, all
"Oracle Exadata Storage Server Software User's Guide", page
7-60, 7-61 say we can use CELL_FLASH_CACHE clause
for LOB segments:
CELL_FLASH_CACHE = NONE | DEFAULT | KEEP
NONE = not cache, obviously.
KEEP = cache, obviously.
What means DEFAULT ?
Will Oracle cache LOBs in DEFAULT mode or not ?
But Oracle say nothing about default policy.
So, i have the many questions to Oracle:
What is the Flash Cacle caching policy for LOB segments by default ?
Cache or not cache ?
Is there difference in policy for SecureFiles and for Basic (old LOBs) ?
I mean LOBs which have its separate segment, not inline LOBs.
And how the WriteBack mode influences to LOBs writes to FC ?
So
- one question is about Default Read LOB Caching policy for WriteThrough mode.
- and another question is for Default Read and Default Write caching policy in WriteBack mode.
I did the test:
created new user and created the tables with only 1 column type LOB.
Put one row in the table and queried the cell flash cache content.
db_securefile='PERMITTED'
Database version = 11.2.0.4.17 (latest 2015 July patch for Exadata )
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list cell detail|grep -i write
ed03celadm01: flashCacheMode: WriteBack
ed03celadm02: flashCacheMode: WriteBack
ed03celadm03: flashCacheMode: WriteBack
set timing on
drop table yu.BASICLOB purge ;
create table yu.BASICLOB (clob_col clob ) lob(clob_col) store as basicfile ;
declare
locator_clob clob;
begin
insert into yu.BASICLOB values (empty_clob()) returning clob_col into locator_clob;
for i in 1 .. 8192
loop
dbms_lob.writeAppend( locator_clob, 32767,rpad('*',32767,'*'));
end loop;
dbms_lob.writeAppend(locator_clob,8192,rpad('*',8192,'*'));
commit;
end;
/
drop table yu.securelob purge;
create table yu.SECURELOB (clob_col clob ) lob(clob_col) store as securefile ;
declare
locator_clob clob;
begin
insert into yu.SECURELOB values (empty_clob()) returning clob_col into locator_clob;
for i in 1 .. 8192
loop
dbms_lob.writeAppend( locator_clob, 32767,rpad('*',32767,'*'));
end loop;
dbms_lob.writeAppend(locator_clob,8192,rpad('*',8192,'*'));
commit;
end;
/
SQL> select rowid,length(clob_col)/1024/1024 from basiclob;
ROWID LENGTH(CLOB_COL)/1024/1024
------------------ --------------------------
AAAW77AAKAAAAEGAAA 256
SQL> select rowid,length(clob_col)/1024/1024 from yu.securelob;
ROWID LENGTH(CLOB_COL)/1024/1024
------------------ --------------------------
AAAW7+AAKAAAAEuAAA 256
SQL> select object_type,object_name,object_id from user_objects order by object_id;
SQL> select object_type,object_name,object_id from user_objects order by object_id
OBJECT_TYPE OBJECT_NAME OBJECT_ID
------------- ---------------------------- ----------
TABLE BASICLOB 93955
LOB SYS_LOB0000093955C00001$$ 93956
INDEX SYS_IL0000093955C00001$$ 93957
TABLE SECURELOB 93958
LOB SYS_LOB0000093958C00001$$ 93959
INDEX SYS_IL0000093958C00001$$ 93960
[root@ed03dbadm01 ~]# date
Tue Aug 11 17:06:41 MSK 2015
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93955
ed03celadm02: 1068513843 10 93955
ed03celadm03: 1068513843 10 93955
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93956
ed03celadm01: 1068513843 10 93956
ed03celadm02: 1068513843 10 93956
ed03celadm03: 1068513843 10 93956
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93957
ed03celadm01: 1068513843 10 93957
ed03celadm02: 1068513843 10 93957
ed03celadm03: 1068513843 10 93957
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93958
ed03celadm02: 1068513843 10 93958
ed03celadm03: 1068513843 10 93958
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93959
ed03celadm01: 1068513843 10 93959
ed03celadm02: 1068513843 10 93959
ed03celadm03: 1068513843 10 93959
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93960
ed03celadm02: 1068513843 10 93960
ed03celadm03: 1068513843 10 93960
[root@ed03dbadm01 ~]#
CellCLI> list flashcachecontent detail
ed03celadm01: cachedKeepSize: 0
ed03celadm01: cachedSize: 230006784
ed03celadm01: cachedWriteSize: 230006784
ed03celadm01: columnarCacheSize: 0
ed03celadm01: columnarKeepSize: 0
ed03celadm01: dbID: 1068513843
ed03celadm01: dbUniqueName: FNS
ed03celadm01: hitCount: 3031
ed03celadm01: missCount: 0
ed03celadm01: objectNumber: 93956
ed03celadm01: tableSpaceNumber: 10
ed03celadm02: cachedKeepSize: 0
ed03celadm02: cachedSize: 205504512
ed03celadm02: cachedWriteSize: 205504512
ed03celadm02: columnarCacheSize: 0
ed03celadm02: columnarKeepSize: 0
ed03celadm02: dbID: 1068513843
ed03celadm02: dbUniqueName: FNS
ed03celadm02: hitCount: 2480
ed03celadm02: missCount: 0
ed03celadm02: objectNumber: 93956
ed03celadm02: tableSpaceNumber: 10
ed03celadm03: cachedKeepSize: 0
ed03celadm03: cachedSize: 243924992
ed03celadm03: cachedWriteSize: 243908608
ed03celadm03: columnarCacheSize: 0
ed03celadm03: columnarKeepSize: 0
ed03celadm03: dbID: 1068513843
ed03celadm03: dbUniqueName: FNS
ed03celadm03: hitCount: 2681
ed03celadm03: missCount: 0
ed03celadm03: objectNumber: 93956
ed03celadm03: tableSpaceNumber: 10
ed03celadm01: cachedKeepSize: 0
ed03celadm01: cachedSize: 5906432
ed03celadm01: cachedWriteSize: 5906432
ed03celadm01: columnarCacheSize: 0
ed03celadm01: columnarKeepSize: 0
ed03celadm01: dbID: 1068513843
ed03celadm01: dbUniqueName: FNS
ed03celadm01: hitCount: 505
ed03celadm01: missCount: 0
ed03celadm01: objectNumber: 93959
ed03celadm01: tableSpaceNumber: 10
ed03celadm02: cachedKeepSize: 0
ed03celadm02: cachedSize: 5169152
ed03celadm02: cachedWriteSize: 5169152
ed03celadm02: columnarCacheSize: 0
ed03celadm02: columnarKeepSize: 0
ed03celadm02: dbID: 1068513843
ed03celadm02: dbUniqueName: FNS
ed03celadm02: hitCount: 336
ed03celadm02: missCount: 0
ed03celadm02: objectNumber: 93959
ed03celadm02: tableSpaceNumber: 10
ed03celadm03: cachedKeepSize: 0
ed03celadm03: cachedSize: 5390336
ed03celadm03: cachedWriteSize: 5390336
ed03celadm03: columnarCacheSize: 0
ed03celadm03: columnarKeepSize: 0
ed03celadm03: dbID: 1068513843
ed03celadm03: dbUniqueName: FNS
ed03celadm03: hitCount: 263
ed03celadm03: missCount: 0
ed03celadm03: objectNumber: 93959
ed03celadm03: tableSpaceNumber: 10
Summary:
CELL_FLASH_CACHE = NONE | DEFAULT | KEEP
NONE = not cache, obviously.
KEEP = cache, obviously.
What means DEFAULT ?
Will Oracle cache LOBs in DEFAULT mode or not ?
But Oracle say nothing about default policy.
What is the Flash Cacle caching policy for LOB segments by default ?
Cache or not cache ?
Is there difference in policy for SecureFiles and for Basic (old LOBs) ?
I mean LOBs which have its separate segment, not inline LOBs.
- one question is about Default Read LOB Caching policy for WriteThrough mode.
- and another question is for Default Read and Default Write caching policy in WriteBack mode.
---------------------------
created new user and created the tables with only 1 column type LOB.
Put one row in the table and queried the cell flash cache content.
db_securefile='PERMITTED'
Database version = 11.2.0.4.17 (latest 2015 July patch for Exadata )
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list cell detail|grep -i write
ed03celadm01: flashCacheMode: WriteBack
ed03celadm02: flashCacheMode: WriteBack
ed03celadm03: flashCacheMode: WriteBack
set timing on
drop table yu.BASICLOB purge ;
create table yu.BASICLOB (clob_col clob ) lob(clob_col) store as basicfile ;
locator_clob clob;
begin
insert into yu.BASICLOB values (empty_clob()) returning clob_col into locator_clob;
for i in 1 .. 8192
loop
dbms_lob.writeAppend( locator_clob, 32767,rpad('*',32767,'*'));
end loop;
dbms_lob.writeAppend(locator_clob,8192,rpad('*',8192,'*'));
commit;
end;
/
create table yu.SECURELOB (clob_col clob ) lob(clob_col) store as securefile ;
locator_clob clob;
begin
insert into yu.SECURELOB values (empty_clob()) returning clob_col into locator_clob;
for i in 1 .. 8192
loop
dbms_lob.writeAppend( locator_clob, 32767,rpad('*',32767,'*'));
end loop;
dbms_lob.writeAppend(locator_clob,8192,rpad('*',8192,'*'));
commit;
end;
/
ROWID LENGTH(CLOB_COL)/1024/1024
------------------ --------------------------
AAAW77AAKAAAAEGAAA 256
ROWID LENGTH(CLOB_COL)/1024/1024
------------------ --------------------------
AAAW7+AAKAAAAEuAAA 256
SQL> select object_type,object_name,object_id from user_objects order by object_id;
SQL> select object_type,object_name,object_id from user_objects order by object_id
------------- ---------------------------- ----------
TABLE BASICLOB 93955
LOB SYS_LOB0000093955C00001$$ 93956
INDEX SYS_IL0000093955C00001$$ 93957
TABLE SECURELOB 93958
LOB SYS_LOB0000093958C00001$$ 93959
INDEX SYS_IL0000093958C00001$$ 93960
Tue Aug 11 17:06:41 MSK 2015
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93955
ed03celadm02: 1068513843 10 93955
ed03celadm03: 1068513843 10 93955
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93956
ed03celadm01: 1068513843 10 93956
ed03celadm02: 1068513843 10 93956
ed03celadm03: 1068513843 10 93956
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93957
ed03celadm01: 1068513843 10 93957
ed03celadm02: 1068513843 10 93957
ed03celadm03: 1068513843 10 93957
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93958
ed03celadm02: 1068513843 10 93958
ed03celadm03: 1068513843 10 93958
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93959
ed03celadm01: 1068513843 10 93959
ed03celadm02: 1068513843 10 93959
ed03celadm03: 1068513843 10 93959
[root@ed03dbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcachecontent where objectnumber=93960
ed03celadm02: 1068513843 10 93960
ed03celadm03: 1068513843 10 93960
[root@ed03dbadm01 ~]#
ed03celadm01: cachedSize: 230006784
ed03celadm01: cachedWriteSize: 230006784
ed03celadm01: columnarCacheSize: 0
ed03celadm01: columnarKeepSize: 0
ed03celadm01: dbID: 1068513843
ed03celadm01: dbUniqueName: FNS
ed03celadm01: hitCount: 3031
ed03celadm01: missCount: 0
ed03celadm01: objectNumber: 93956
ed03celadm01: tableSpaceNumber: 10
ed03celadm02: cachedSize: 205504512
ed03celadm02: cachedWriteSize: 205504512
ed03celadm02: columnarCacheSize: 0
ed03celadm02: columnarKeepSize: 0
ed03celadm02: dbID: 1068513843
ed03celadm02: dbUniqueName: FNS
ed03celadm02: hitCount: 2480
ed03celadm02: missCount: 0
ed03celadm02: objectNumber: 93956
ed03celadm02: tableSpaceNumber: 10
ed03celadm03: cachedSize: 243924992
ed03celadm03: cachedWriteSize: 243908608
ed03celadm03: columnarCacheSize: 0
ed03celadm03: columnarKeepSize: 0
ed03celadm03: dbID: 1068513843
ed03celadm03: dbUniqueName: FNS
ed03celadm03: hitCount: 2681
ed03celadm03: missCount: 0
ed03celadm03: objectNumber: 93956
ed03celadm03: tableSpaceNumber: 10
ed03celadm01: cachedSize: 5906432
ed03celadm01: cachedWriteSize: 5906432
ed03celadm01: columnarCacheSize: 0
ed03celadm01: columnarKeepSize: 0
ed03celadm01: dbID: 1068513843
ed03celadm01: dbUniqueName: FNS
ed03celadm01: hitCount: 505
ed03celadm01: missCount: 0
ed03celadm01: objectNumber: 93959
ed03celadm01: tableSpaceNumber: 10
ed03celadm02: cachedSize: 5169152
ed03celadm02: cachedWriteSize: 5169152
ed03celadm02: columnarCacheSize: 0
ed03celadm02: columnarKeepSize: 0
ed03celadm02: dbID: 1068513843
ed03celadm02: dbUniqueName: FNS
ed03celadm02: hitCount: 336
ed03celadm02: missCount: 0
ed03celadm02: objectNumber: 93959
ed03celadm02: tableSpaceNumber: 10
ed03celadm03: cachedSize: 5390336
ed03celadm03: cachedWriteSize: 5390336
ed03celadm03: columnarCacheSize: 0
ed03celadm03: columnarKeepSize: 0
ed03celadm03: dbID: 1068513843
ed03celadm03: dbUniqueName: FNS
ed03celadm03: hitCount: 263
ed03celadm03: missCount: 0
ed03celadm03: objectNumber: 93959
ed03celadm03: tableSpaceNumber: 10
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.