Archive for June, 2013

Oracle Database 12.1c released. Licensing gotchas

Wednesday, June 26th, 2013

Accroding to Oracle® Database Licensing Information 12c Release 1 (12.1) there are some gotchas but also features which seemed pretty reasonable to be included in “basic” Enterprise Edition. The most interesting list for me is as follows:

  • Multitenant (CDB/PDB stuff) is additional paid option.
  • ILM/Heat Maps/Automatic Data Optimization belong to Advanced Compression Option
  • DataGuard Far Sync (semi bunker-site like SRDF/A* in EMC Symmetrix/VMAX solution) is licensed as follows: Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license. (..) A far sync instance consumes very little disk and processing resources, yet provides the ability to failover to a terminal destination with zero data loss, as well as offload the primary database of other types of overhead (for example, redo transport). You would guess they charge additionally for the number of cores on the lightweight (in terms of storage capacity and CPU) proxy instance, WRONG! Here is is stated that The far sync instance can be installed and used on a server different from the server where the Oracle Database is installed and used. It is not necessary to obtain a separate license for the server running the far sync instance.
  • Oracle Flex ASM and Oracle Flex Cluster – this is a mystery to me
  • Oracle Clusterware may be used to protect any application (restarting or failing over the application in the event of a failure) on any server, free of charge. Oracle will provide support for Clusterware only if the server is running an Oracle product, which may include Oracle Linux or Oracle Solaris, that is also under Oracle support.
  • There is also interesting statement: As of the release of Oracle Database 12c, network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of the Oracle database.- Finally! No excuses of not encrypting your sensitive connections! Wait… yeah, it means more CPU usage especially on the (Oracle) CPU-licensed side ;)

Part 2: Oracle SecureFile LOB is better than BasicFile, isn’t it?

Tuesday, June 18th, 2013

So we start an example from real life of using SecureFiles (without deduplication and without encryption) on large scale system (say no-no to a “laptop database” tests) version Oracle non-RAC 11.2.0.2.3 (yup, a pretty old release but the DB has been already migrated since gathering this info):

primary:sys@XXXX> desc YYYYYYYY.magic_table;
 Name                                                  Null?    Type
[..]
 HEADERS                                                        BLOB
 PAYLOAD                                                        BLOB

primary:sys@XXXX>
primary:sys@XXXX> select num_rows, blocks, AVG_ROW_LEN from dba_tables  where table_name='MAGIC_TABLE';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
   3791737     891649         204

The table is on 8kB tablespace, so our MAGIC_TABLE with 2 LOB columns is taking ~ 891649*8192/1024/1024/1024 =~ 6.8GB. We verify our math to real usage as shown by dba_segments:

primary:sys@XXXX> select bytes/1024/1024/1024 mb from dba_segments where segment_name='MAGIC_TABLE';

        MB
----------
    6,9375

The value is pretty close to the standard Oracle theory, so we are ok so far. We proceed with analysis what uses space in that table (we are going to concentrate on single LOB for PAYLOAD column for simplicity):

primary:sys@XXXX> SELECT CASE WHEN DBMS_LOB.GETLENGTH(PAYLOAD) > 3964 THEN 'Out-of-line' ELSE 'Inline' END x, COUNT(*) FROM YYYYYYYY.magic_table GROUP BY  CASE WHEN BMS_LOB.GETLENGTH(PAYLOAD) > 3964 THEN 'Out-of-line' ELSE 'Inline' END; 

X             COUNT(*)
----------- ----------
Inline         3100800
Out-of-line     732826

primary:sys@XXXX>

FACT1: so 24% of rows have PAYLOAD LOB stored out-of-line, out-of-line probably means different place, let’s check it.

primary:sys@XXXX> SELECT table_name, column_name, segment_name, round(a.bytes/1024/1024,1) mb FROM dba_segments a
JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name LIKE 'MAGIC_TABLE%';

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                           MB
------------------------------ -------------------- ------------------------------ ----------
MAGIC_TABLE                    HEADERS              SYS_LOB0000099004C00007$$              ,1
MAGIC_TABLE                    PAYLOAD              SYS_LOB0000099004C00008$$         1769095

primary:sys@XXXX>

FACT2: this 24% of rows takes 1769095 MB (sic!) which means 1.72TB consumed for just 1/4 of rows.

primary:sys@XXXX> select table_name, tablespace_name, chunk, pctversion, retention, in_row, securefile, retention from dba_lobs where segment_name = 'SYS_LOB0000099004C00008$$';

TABLE_NAME                     TABLESPACE_NAME                     CHUNK PCTVERSION  RETENTION IN_ SEC  RETENTION
------------------------------ ------------------------------ ---------- ---------- ---------- --- --- ----------
MAGIC_TABLE                    LOB4K                                4096                       YES YES

primary:sys@XXXX>
primary:sys@XXXX>

FACT3: 24% of rows have data stored in separate tablespace dedicated for LOBs with 4kB blocksize.

primary:sys@XXXX> SELECT CASE
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 3964  THEN '< 4kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 6*1024 THEN '<6kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 7*1024 THEN '<7kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 8*1024 THEN '<8kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) <16*1024 THEN '<16kB'
  ELSE 'even bigger'
  END x,
  COUNT(*) cnt
FROM YYYYYYYY.magic_table GROUP BY CASE
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 3964  THEN '<4kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 6*1024 THEN '<6kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 7*1024 THEN '<7kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) < 8*1024 THEN '<8kB'
  WHEN DBMS_LOB.GETLENGTH(PAYLOAD) <16*1024 THEN '<16kB'
  ELSE 'even bigger'
  END;   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  

X                  CNT
----------- ----------
<7kB                76
<4kB           3215841
<6kB            701916

primary:sys@XXXX>

We can double-check Oracle on it’s math here. There is not a single row in that table that has LOB entry bigger than 7kB, still everything is stored out-of-line (technically bigger than something like 3600-3900 bytes which is max size for inline LOBs) in LOB segment named SYS_LOB0000099004C00008$$ is taking 1769095 MB (sick!).
So actually 701916+76 rows potentially consume 1769095 MB in datafiles, which gives us 2.5MB of waste per row. This table is really heavy duty OLTP table which means really lots of INSERT/UPDATE/DELETE concurrent stuff, I’m not even sure if the application responsible, is doing updates against they PAYLOAD column, from my analysis it is just inserts row once (with LOB via JDBC) and sometime later deletes it. All I can say about the application in scope is the fact that it is “database interdependent”.

Let’s now calculate theory behind space calculation (A.K.A what should be really used if there would be no overhead). Space that should be used for PAYLOAD column stored in LOBSEGMENT should be:

  • 3215841 rows stored inside tablespace with the rest of data; table segment for MAGIC_TABLE already stores 6.7GB which contains data for those 3215841 rows, so we can skip it
  • (701916+76) rows stored in separated tablespace LOB4K, which means that for rows bigger than 3600..3900 this translates to at least 1 CHUNK (4kB) and maximum up to 2 CHUNKs (8kB) because we do not have anything bigger than 7kB; we can assume even worse scenario that single CHUNK(4kB) would require up to 2 blocks in 4kB tablespace due to some overheads. So even in the worst case we have (701916+76)*4096*2/1024/1024/1024 =~ 5.36GB

So what we really have is 1727 GB used to store 5.36GB. Closing this part i would like to give you an idea how hard SecureFile LOB processing is for Oracle, they seem to be struggling with it for couple years. The list of bugs for 11.x series include:

  • Bug 14849553 : MOVE LOB SLOWER FOR SECURE FILES THAN BASIC FILES
  • Bug 13593640 : DATABASE IS SLOW DUE TO BUFFER BUSY WAITS ON SECUREFILE FOR INSERTS
  • Bug 10197852 : SLOW PERFORMANCE ACCESSING SECUREFILE BLOBS WITH XA CONNECTIONS
  • Bug 9792464 : DEGRADED PERFORMANCE INSERTING LARGE BLOB WITHIN AN XA TRANSACTION
  • Bug 13005760 : BUG:EXCESSIVE ENQ TX-CONTENTION WAIT ON SECUREFILE
  • Bug 8620054 : LOBS WASTE STORAGE USING LOB TYPE DISABLE STORAGE IN ROW
  • Bug 14723894 : SECUREFILE BLOB NOT REUSING SPACE
  • Bug 8487668: RETENTION NONE NOT HONORED CORRECTLY IN SECUREFILES
  • Bug 10097920: SPACE NOT RELEASED USING SECUREFILES
  • Bug 8603465: AGGRESSIVE ALLOCATION OF SPACE IN SECUREFILES
  • Bug 12582664: DEDUPLICATION WITH SECUREFILES NOT WORKING AS EXPECTED
  • Bug 12588744: SECUREFILE WASTE STORAGE WHEN SPECIFY INITIAL EXTENT
  • Bug 12656535: ORA-1555 DURING DIRECT PATH READ OF SECUREFILE LOB
  • Bug 12662040: SECUREFILE LOB SEGMENT KEEPS GROWING IN CASE OF PLENTY OF FREE SPACE

I’m certain you could find even more. Even with the most recent 11.2.0.3.x SecureFilles are still being patched, as they fixed bug 14176879 in 11.2.0.3.6 in Critical Patch Updates April 2013. Bug 14176879 stands for “Extensive growth of Securefiles segments undergoing updates”.

To make it even more appealing to use SecureFiles (irony intended), here’s the next real the BIG THING: in section “Using ALTER TABLE with SecureFiles LOBs” in official documentation, it is written: “Note that the SHRINK option is not supported for SecureFiles LOBs”. Yeah, now you can scream ;)

Part 1: Oracle SecureFile LOB is better than BasicFile, isn’t it?

Thursday, June 6th, 2013

According to Oracle Technical Marketing SecureFiles are superior in every way to the older BasicFile LOB implementation. Part1 of this articles is going to try to prove it for strictly OLTP like workloads (having LOB column as requirement for application that is doing a lot of heavy/duty short & quick transaction requiring to store data that VARCHAR2 type is not capable of). This series is not going to concentrate on the LOBs used for anything bigger than 5300 bytes. We also want to take profit from the LOB inline storage option that stores LOB data together with the table data. The benchmarks were performed on 2 sockets 8 cores Intel Xeon box running Oracle 11.2.0.3.5 with some local RAID1 storage. Before each iteration shared pool and buffer cache were flushed, log switch forced (BTW: DB running without archive redo logs), dedicated tablespaces recreated, workload has been parallel (like in OLTP) using 8 jobs hitting single table creating (intended) contention, each run has been run at least 3 times to guarantee at least some kind of “repeatability”. The table has not been partitioned and it looked like this:

create table t_blob ( id number primary key, l1 clob ) tablespace blobs
lob ( l1 ) store as &blob_type (
tablespace blobs &inrow storage in row chunk &chunk &cache &logging
)
;

there are several variables set like this:

  • &inrow – always “enable” for purposes of this test
  • &chunk – always set for 4k for purposes of this test
  • &cache – always set to CACHE
  • &logging – always set
  • &blob_type – securefile, basicfile
  • tablespace – the tested values were 4k and 8k, each time setting db_buffer_cache for the appriopiate buffer to 1.5GB
  • payload for LOB data – always generated randomly using dbms_random package, with with length of minimum from 300 bytes (incremented with step of 2000 bytes) up to 5300 bytes (incremented also by 2000)

The results are below, first space efficiency BasicFile vs SecureFile:

Second, elapsed time of execution comparing BasicFile VS SecureFile:

But that’s not the end, stay tuned for part 2 where I’ll show that the world is not so beautiful as it looks like :)

-J.