Re: By default, LOB store in line or out of line?

  • From: fairlie rego <fairlie_r@xxxxxxxxx>
  • To: "tanel@xxxxxxxxxxxxxx" <tanel@xxxxxxxxxxxxxx>, Leyi Kamus Zhang <kamusis@xxxxxxxxx>
  • Date: Mon, 23 Apr 2012 04:10:33 -0700 (PDT)

I am doing similar migrations to exadata where I had to move 13 TB of data from 
a 10.2.0.4 databases to an X2-2 on 11.2.0.2
90% of data was LOBS but thankfully most of it was is read only and partitioned 
by month
Approach 1
----------
Datapump export of LOBS was too slow and took more than 40 hours to export a 
500GB partition

Approach 2
---------
Insert over a dblink by having 12 concurrent scheduler jobs each working on 
discrete data (as outlined by Tanel below) took less than 9 hours.

We used approach 2 and could not use more concurrent sessions because the 
source is a production system and hence we couldn't go crazy...


To reduce downtime for copy of the active readwrite portion we used GoldenGate 
and the downtime was less than 1 hour

Thanks


 
Fairlie Rego
Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405
 


________________________________
 From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
To: Leyi Kamus Zhang <kamusis@xxxxxxxxx> 
Cc: louis <ylouis83@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx 
Sent: Monday, 23 April 2012 2:17 AM
Subject: Re: By default, LOB store in line or out of line?
 
(reposting due to overquoting)
Yeah, LOBs over DBlinks are a problem. As every LOB item will require a
SQL*Net roundtrip ... thus there's a lot of idle time on both ends of the
dblink due to network latency. I don't think that even doing datapump *over
dblinks* changes this as it's how LOB access over OPI works (their data has
to be retrieved with a separate OPI call - it isn't bundled in the regular
row stream).

So unless you want to go with other approaches (like datapump'ing to a NFS
share and mounting it on the target server too) then you'll just need to
start tens or hundreds of connections, each fetching their own subset of
data.

You might end up with various contention issues (on LOB segments example),
especially if you don't have partitioned tables in the target system, but
it's matter of testing & benchmarking as usual.

Tanel.

On Sun, Apr 22, 2012 at 7:05 PM, Leyi Kamus Zhang <kamusis@xxxxxxxxx> wrote:

> Hi Tanel
>
> Thanks for your reply, I'm reading your ppt on slideshare, perfect one.
>
> One more question for BLOB performance, I knew you and Randy write the
> "Expert Oracle Exadata" Chaper 13, it's for migration to Exadata. Now I'm
> doing a huge database migration, not to Exadata, just from an Oracle
> database to another, in my test, we can only achieve 10MB/s when CTAS a
> table with BLOB, and only 25MB/s when CTAS a normal table without LOB
> columns(database link in 1000Mbps NIC), I'm wonder how fast the speed you
> can achieve?
>
> --
> Kamus <kamusis@xxxxxxxxx>
>
> Visit my blog for more : http://www.dbform.com
> Join ACOUG: http://www.acoug.org
>
>
>


--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l


Other related posts: