Oracle 12C: Lob Enhancement & Parallelism Supported.
With Oracle 11g, when creating a table containing LOB data, LOB are stored as a part of BASIC FILE feature and the default value for the init parameter DB_SECUREFILE is set to PERMITTED.
But in 12C; by default storage is changed to Secure file when compatible parameter is set 12.0 or high and it does support Parallelism <Non partitioned table Insert add on feature>
SQL> sho parameter comp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0
SQL> show parameter db_secure
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PREFERRED
SQL> create table CLOB_TEST ( x clob) tablespace POOL_DATA;
SQL> select table_name, securefile from user_lobs where table_name='CLOB_TEST';
TABLE_NAME SEC
--------------------------------
CLOB_TEST YES
If you change compatible parameter to 11G or <12C then parameter db_securefile default value gets transitioned back to PERMITTED mode; where in LOB will get store in Secure file if and only if it is explicitly mentioned while creating of the segment.
There is one more condition that we must not forget i.e. the tablespace where you are creating the secure file needs to be be Automatic Segment Space Management (ASSM). In Oracle Database 11g, the default mode of tablespace creation is ASSM so it may already be so for the tablespace. If it's not, then you have to create the Secure File on a new ASSM tablespace.
Same applies to 12C as well i.e. even though db_securefile value is PREFERRED; if your tablespace is not ASSM then LOB will not be store as a part of Secure file.
SQL> select SEGMENT_SPACE_MANAGEMENT,TABLESPACE_NAME from dba_tablespaces;
SEGMEN TABLESPACE_NAME
------ ------------------------------
MANUAL SYSTEM
AUTO SYSAUX
MANUAL UNDOTBS
MANUAL TEMP
AUTO POOL_DATA
AUTO POOL_IX
MANUAL TOOLS
SQL> create table CLOB_TEST ( x clob) tablespace TOOLS;
Table created.
SQL> select table_name, securefile from user_lobs where table_name='CLOB_TEST';
TABLE_NAME SEC
--------------------------------
CLOB_TEST NO
In Oracle 11g, Parallel Insert was not supported both with Basic and Secure file.
insert /*+ parallel(testclob,4) enable_parallel_dml */ into testclob select /*+ parallel(t,4) full(t) */ * from testclob_pump t;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 82 | 321K| 2 (0)| 00:00:01 | | | |
| 1 | LOAD TABLE CONVENTIONAL | TESTCLOB | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | TESTCLOB_PUMP | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------
But starting with 12c, Parallel INSERTs<parallel dml> is allowed into non-partitioned tables with LOB columns provided that those columns are declared as SecureFiles LOBs.
Let’s start with 12C test case : Created two tables and then inserted the data equivalent a big lob. Note below table is created with Secure file feature<db_securefile=PERMITTED>
CREATE TABLE testclob
(
x NUMBER,
y CLOB,
z VARCHAR2(4000)
) tablespace POOL_DATA;
CREATE TABLE testclob_bk
(
x NUMBER,
y CLOB,
z VARCHAR2(4000)
) tablespace POOL_DATA;
DECLARE
textstring CLOB := '123';
i INT;
BEGIN
WHILE Length(textstring) <= 60000 LOOP
textstring := textstring
|| '000000000000000000000000000000000';
END LOOP;
begin
FOR I IN 1..100000 LOOP
INSERT INTO testclob
(x,
y,
z)
VALUES (0,
textstring,
'done');
END LOOP;
commit;
end;
END;
/
Here is the insert and you can clearly see Parallel DML is supported as compared to 11g.
insert /*+ parallel(testclob_bk,4) enable_parallel_dml */ into testclob_bk select /*+ parallel(t,4) full(t) */ * from testclob t;
SQL> @plan
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |-----PARALLEL DML supported
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | TESTCLOB | 82 | 321K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Even from below we can see in famous Db file sequential read is replaced with direct path read which says it all.
EVENT Reported on SQL
----------------------------------------------------------------
direct path read
direct path write
SQL_PLAN_LINE_ID BLOCKING_SE SQL_ID SAMPLE_TIME EVT CURRENT_OBJ#
---------------- ----------- --------------- ------------------------------ ------------------------- ------------
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.05.195 PM direct path read 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.06.195 PM ON CPU 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.07.195 PM log file switch (checkpoi 367192
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.08.195 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.09.205 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.10.215 PM log file switch (checkpoi 367192
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.11.215 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.12.245 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.13.245 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.14.435 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.15.435 PM log file switch completio 367192
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.16.445 PM direct path read 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.17.445 PM ON CPU 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.18.455 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.19.465 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.20.465 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.21.465 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.22.465 PM log file switch (checkpoi 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.23.465 PM log file switch (checkpoi 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.24.475 PM log file switch (checkpoi 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.25.475 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.26.475 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.27.485 PM direct path read 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.28.485 PM ON CPU 367192
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.29.495 PM ON CPU 367192
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.30.505 PM direct path read 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.31.505 PM ON CPU 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.32.535 PM direct path read 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.33.535 PM ON CPU 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.34.545 PM log file switch (checkpoi 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.35.555 PM ON CPU 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.36.555 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.37.555 PM log file switch (checkpoi 367192
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.38.565 PM ON CPU 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.39.575 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.40.575 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.41.575 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.42.575 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.43.585 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.44.595 PM log file switch completio 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.45.595 PM log file switch (checkpoi 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.46.650 PM ON CPU 367345
3 NOT IN WAIT 2kzx34bhh7t17 18-NOV-16 10.49.47.660 PM ON CPU 367192
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.48.660 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.49.660 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.50.680 PM direct path read 367345
3 VALID 2kzx34bhh7t17 18-NOV-16 10.49.51.680 PM log file switch (checkpoi 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.52.680 PM direct path read 367345
3 UNKNOWN 2kzx34bhh7t17 18-NOV-16 10.49.53.690 PM direct path read 367345
SQL> @objnm
Enter value for enter_obj_id: 367345
OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------------------------------------------------------
AIM_DBA 367345 SYS_LOB0000367344C00002$$ LOB
SQL> select TABLE_NAME,SEGMENT_NAME from user_lobs where SEGMENT_NAME='SYS_LOB0000367344C00002$$';
TABLE_NAME SEGMENT_NAME
------------------------------------------------
TESTCLOB SYS_LOB0000367344C00002$$
I tried to do export as well via Datapump to see if secure feature is able to export the data with parallelism or not; but seems a worthless effort; I see two workers started and only one worked; the other process is just waited. It might get work with partition table but that I leave up to you.
expdp username/password dumpfile=clobdump%u.dmp logfile=dumpfile.log tables=testclob exclude=statistics directory=DUMP parallel=4
Job: SYS_EXPORT_TABLE_01
Operation: EXPORT
Mode: TABLE
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /DUMP/clobdump01.dmp
bytes written: 4,096
Dump File: /DUMP/clobdump%u.dmp
Worker 1 Status:
Process Name: DW00
State: WORK WAITING
Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: AIM_DBA
Object Name: TESTCLOB
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 13,102
Worker Parallelism: 1
In my view secure file enhancement is very helpful!!
Hope you enjoyed!!!
