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!!!

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more