Re: COPY COMMAND in SQLPLUS
- From: "Toon Koppelaars" <toon@xxxxxxxxxxx>
- To: anuragdba@xxxxxxxxx
- Date: Mon, 16 Jun 2008 22:05:35 +0200
Anurag,
Yes you can. Copy command just issues the query you supply it with and you
can force it to do parallel query with hints (or otherwise). The question
however is: will it speed up your copy process? What is the bottleneck?
Reading the data from the source-table, or something else? What would you
ideally want to be the bottleneck of a large copy-process?
A couple of weeks ago I was challenged to copy a 270 Gbyte table from one
database to another. Given the 100 Mbit/second ethernet in between the two
databases, this could not be done any faster than a little less than 6
hours. I did some testing and eventually ended up with a copy-process that
would have the network as its bottleneck. It turned out that just having the
process perform a (non-parallel) full table scan would generate rows at a
rate that the network could not keep up with. Point being: forcing it
to perform a parallel scan would not speed up any further.
Here's what I did (with thanks going to Alex Gorbachev):
In the database session that performs the 'from/using' query of the
copy-command:
alter session set "_serial_direct_read"=true;
alter session set db_file_multiblock_read_count=128;
-- Increased from 64
The _serial_direct_read prevented me from hitting a 'snapshot too old'
error. Apparently (though still not 100% sure) it prevents 'block cleanouts'
that cause block writes, which in turn cause rollback segment allocation.
I used a database login-trigger to perform these two alter session
statements in the session that performs the 'from/using' query.
Then (of course) in SQLPlus I set the arraysize to its maximum possible
value:
set arraysize = 5000
The query on the source table was performing a simple full table scan.
Copy commit was set to zero: i.e. commit once at the end of the copy
process. And finally: no indexes or constraints were present on the
destination-table (which was an empty table upon start of the copy-process).
The copy process took a little under 6 hours. It was copying from a 9i
database to an 11G database. After the copy-process it took another 6-7
hours to build indexes and enable constraints.
Toon
On 6/16/08, Anurag Verma <anuragdba@xxxxxxxxx> wrote:
>
> Hi,
>
>
> Can we use parallel degree option to copy a table from one database to
> another?
>
> I have 2 Oracle 9i databases and have to copy some huge amount of data.
>
> Thinking of various options and want to know whether I can implement
> parallel option (in the same way what we do with Parallel DMLs).
>
> This is what the COPY syntax I am seeing from sqlplus utility.
>
> usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
> <db> : database string, e.g., hr/your_password@d:chicago-mktg
> <opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
> <table>: name of the destination table
> <cols> : a comma-separated list of destination column aliases
> <sel> : any valid SQL SELECT statement
>
>
> --
> Thanks,
>
> Anurag Verma,
> Database Administrator
> ERCOT(Electric Reliability Council of Texas),
> Texas 76574
>
--
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
RuleGen BV
+31-615907269
toon_at_rulegen_dot_com
www_dot_rulegen_dot_com
Author: "Applied Mathematics for Database Professionals"
Other related posts:
