Tuesday 26 March 2013

SecureFiles Oracle 11g

Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.
This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.
If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.
By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.

The default behavior for securefile usage can be changed via the db_securefile initialization parameter.
db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}

ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs


Can be set dynamically via an ALTER SYSTEM:
SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;
Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.
Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );


Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.

CREATE TABLE table1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );


DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.

CREATE TABLE table1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;


Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.

SQL> select name, bytes from v$sgainfo ;
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0


SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304


When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.

SQL> select * from v$securefile_timer where name like '%PGA%';
NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache

No comments:

Post a Comment