Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vishal.

Asked: July 06, 2017 - 9:09 am UTC

Last updated: July 07, 2017 - 1:54 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

We have an implementation where datasize has grown over 24TB's and most of it(13 TB's including the indexes) is from couple of BLOB tables which have partitions(around 40 for biggest table) and subpartitions(44 in each partition). Now the task is to move historical data over DBlink to archival database. We tried multiple things:

1. CTAS specifying the partition attribute. Copying was running for single partition(44,00,000) took 8 hrs and didn't completed, had to stop.
2. Copying and commit every 100,000 records. Total records in that partition were 44,00,000. each lot of 100,000 was taking around 10 minutes initially but this was running for 8 hrs, before i had to kill this as well.
3. We also tried disabling the indexes on archival DB, but not much benefit from that as well.
4. Export/import was done for 12 partitions and it took 3-4 Hrs for export, but import took 2 days. During this time archive DB was in no archive log mode when import was running.

Export/import could be a temporary solution to move bulk of data, as it is not impacting live database. We still have to find a solution to move 3 months of data every quarter i.e 3 partitions with 44 sub partitions. Latest partition has around 35,400,000 records and we are unable to find any solution to automate this.

Any help ?


and Connor said...

Well...we need to determine *why* its taking a long time. It could be:

- time to read the source data
- time to transfer it across dblink
- time to write it
- something else

Let's try work out that first.

So lets assume the CTAS is being done as user X, and is being done as:

create table T as select * from T@source_db

and the 'source_db' dblink connects as user Y.

So we want to capture details on both source and target.

On the source, we want to turn on a trace when the db link connects as Y from the target, so you can do:

create or replace
trigger TRACE_Y
after logon on Y.schema
declare
  l_details varchar2(200);
begin
  select ...
  into   l_details
  from   v$session
  where  sid = sys_context('USERENV','SID');
  
  if l_details like '...' then
    execute immediate 'alter session set tracefile_identifier = DBLINK_TRACE';
    dbms_monitor.session_trace_enable(waits=>true);
  end if;
exception
  when others then null;
end;
/



where you can do some experiment with the 'details' above to ensure you only activate the trace where apropriate. (Obviously if 'Y' is specific to the db link only and not other connections you wont this additional check).

Then on the target you do:

dbms_monitor.session_trace_enable(waits=>true);
create table T as select * from T@source_db where rownum <= 1000000


(Choose a value for rownum that will give you a reasonable run time, eg, 30mins, so we have a good idea of where the time is being spent)

And its a case of looking at the trace files and working from there.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here