You Asked
Hi Sir,
I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity.
rename existing table to _old
create new partition blank table
insert into new blank partition table select * from _old table;
This preocess is running from last 12 hrs and only 20Gb of data is inserterd the table size is 90 Gb with 2 CLOB segments of 900 and 200Gb each.
Need your suggestion how to overcome this time consumption and make the process fast.
Below is the details of table and execution plan.
NOTE:
REFERENCE_INSTANCE -- ORIGINAL NON PARTITION TABLE.
In GB::
EAI_SOAINFRA REFERENCE_INSTANCE TABLE 103.708008
EAI_SOAINFRA SYS_LOB0000075987C00014$$ LOBSEGMENT 241.97522 REFERENCE_INSTANCE
EAI_SOAINFRA SYS_LOB0000075987C00015$$ LOBSEGMENT 924.013855 REFERENCE_INSTANCE
REFERENCE_INSTANCE_TP -- ENPTY PARTITION TABLE CREATED SAME AS REFERENCE_INSTANCE EXCEPT THE NAME DIFFERENCE AND PARTITION DETAILS.
CREATE TABLE "EAI_SOAINFRA"."REFERENCE_INSTANCE_TP"
( "ECID" VARCHAR2(100 BYTE),
"ID" NUMBER(*,0),
"COMPOSITE_INSTANCE_ID" NUMBER(*,0),
"PARENT_ID" VARCHAR2(100 BYTE),
"CONVERSATION_ID" VARCHAR2(100 BYTE),
"COMPOSITE_DN" VARCHAR2(500 BYTE),
"PROTOCOL_CORRELATION_ID" VARCHAR2(100 BYTE),
"REFERENCE_NAME" VARCHAR2(200 BYTE),
"BINDING_TYPE" VARCHAR2(200 BYTE),
"OPERATION_NAME" VARCHAR2(500 BYTE),
"STATE" NUMBER(*,0),
"ADDITIONAL_PROPERTIES" VARCHAR2(4000 BYTE),
"ERROR_CODE" VARCHAR2(100 BYTE),
"ERROR_MESSAGE" CLOB,
"STACK_TRACE" CLOB,
"CPST_PARTITION_DATE" TIMESTAMP (6),
"CREATED_BY" VARCHAR2(100 BYTE),
"CREATED_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(100 BYTE),
"UPDATED_TIME" TIMESTAMP (6)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 20 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EAI_SOAINFRA"
LOB ("ERROR_MESSAGE") STORE AS BASICFILE (
TABLESPACE "EAI_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("STACK_TRACE") STORE AS BASICFILE (
TABLESPACE "EAI_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
PARTITION BY RANGE ("CPST_PARTITION_DATE")
(
PARTITION "AIATBLPART_JANP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-31 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-28 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-28 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "EAI_SOAINFRA" )
ENABLE ROW MOVEMENT;
alter session enable parallel dml;
INSERT /*+ PARALLEL(b,22) */ INTO EAI_SOAINFRA.REFERENCE_INSTANCE_TP b SELECT /*+ PARALLEL(a,22) */ * from EAI_SOAINFRA.REFERENCE_INSTANCE a;
COMMIT;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4zwvtyf1bzdrt, child number 0
-------------------------------------
INSERT /*+ PARALLEL(b,22) */ INTO EAI_SOAINFRA.REFERENCE_INSTANCE_TP b SELECT /*+ PARALLEL(a,22) */ * from EAI_SOAINFRA.REFERENCE_INSTANCE a ;
Plan hash value: 795120472
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 186K(100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 340M| 84G| 186K (1)| 00:37:18 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 340M| 84G| 186K (1)| 00:37:18 | Q1,01 | PCWP | |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | P->P | PART (KEY) |
| 6 | PX BLOCK ITERATOR | | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | REFERENCE_INSTANCE | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
SID USERNAME PROGRAM OSUSER EVENT STATE SQL SQL_ID MINS_ACTIVE
---------- ------------ ------------ ------------ ------------------------- -------------- ------------------------------ ------------- -----------
6 SYS sqlplus@PRE oracle SQL*Net message to client WAITED SHORT select distinct w.sid,s.userna f1wvs9jwvcmv7 0
254 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864
258 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864
2756 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864
Request you to please suggest me what is wroing with this approach and what should i do for making it fast.
Regards
Wasim
and Chris said...
So you're transferring over a terabyte of data? That's going to take a while whatever you do...
And reading and writing clobs does a lot more work than "standard" data types. For example, a "insert ... select ..." of just 10,000 rows takes over half a minute on my system:
SQL> set timing off
SQL> create table t (
2 x int,
3 y clob
4 );
Table created.
SQL>
SQL> create table tpart (
2 x int,
3 y clob
4 );
Table created.
SQL>
SQL> declare
2 v varchar2(32000) := lpad('xxxxxxxxxxx', 32000, 'x');
3 ctemp clob;
4 begin
5
6 dbms_lob.createtemporary(ctemp,true);
7 ctemp := v;
8
9 for i in 1 .. 20 loop
10 dbms_lob.writeappend(ctemp,length(v),v);
11 end loop;
12
13 insert into T
14 select rownum, ctemp from dual connect by level <= 10000;
15 commit;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
10000
SQL>
SQL> set timing on
SQL> insert into tpart
2 select * from t;
10000 rows created.
Elapsed: 00:00:43.56
Compared to ~5s for similar varchar data:
SQL> create table tv (
2 x int,
3 y varchar2(4000)
4 );
Table created.
Elapsed: 00:00:00.32
SQL>
SQL> create table tpartv (
2 x int,
3 y varchar2(4000)
4 );
Table created.
Elapsed: 00:00:00.32
SQL>
SQL> insert into tv
2 select rownum, lpad('x', 4000, 'x')
3 from dual connect by level <= 10000;
10000 rows created.
Elapsed: 00:00:04.57
SQL> commit;
Commit complete.
Elapsed: 00:00:00.54
SQL>
SQL> insert into tpartv
2 select * from tv;
10000 rows created.
Elapsed: 00:00:05.20
Fortunately there's a way you can avoid actually copying the data.
Create your partitioned table with one partition. Then swap the tables over with an exchange partition! This is a metadata operation rather than a physical transfer, so will be much quicker:
SQL> drop table tpart purge;
Table dropped.
Elapsed: 00:00:00.47
SQL>
SQL> create table tpart (
2 x int,
3 y clob
4 ) partition by range (x) (
5 partition pmax values less than (maxvalue)
6 );
Table created.
Elapsed: 00:00:00.36
SQL>
SQL> alter table tpart exchange partition pmax with table t;
Table altered.
Elapsed: 00:00:00.39
SQL>
SQL> select count(*) from tpart;
COUNT(*)
----------
10000
Elapsed: 00:00:00.47
SQL> select count(*) from t;
COUNT(*)
----------
0
Elapsed: 00:00:00.80
Once this is done, split your partitions to get the arrangement you want.
Rating
(6 ratings)
Is this answer out of date? If it is, please let us know via a Comment