Tuesday, August 11, 2015

Is Flash Cacle caching policy will cache LOB segments by default ?

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:

WriteBack Flash Cache will cache the Basic and SecureFile LOBs !

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

How to disable/setup autostart parameters for specified instance ?

Q: We have a 4-node RAC. I need to disable autostart of the DB on one node only.    How to do it and how to see autostart parameters, confir...