RE: Any way to forace parallel query for another session?

  • From: <krish.hariharan@xxxxxxxxxxxx>
  • To: <Kurt-Franke@xxxxxx>, <anysql@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Nov 2007 09:05:32 -0700

Since you are going to be constrained on the write with exp as John Hallas
said, it would likely end up in a "hurry up and wait" scenario. 

If your environment is like ours, then the constraint is usually not export,
but
1. transporting the data to the target environment 
2. importing the data

I would think you would be better off, parallelizing the process as follows
(or equivalent), instead of just the export component of it, which is not
likely to be the long pole.

Assuming datapump is not applicable to your situation (and even if it were
there are parts here that may still be relevant)

1. Export the structures
2. Export the data (small group of tables for small tables, and partitioning
with predicates for larger tables, without losing data of course, and using
direct path where applicable).
3. Gzip/compress the files after the fact or using named pipes depending on
your environment
4. Transport the files efficiently scp -C or robocopy (hearsay for me) or
play with the mtu/sdu on a remote import session (by habit and not reason, I
use file transfer methods)
5. Import in multiple sessions and close out by rebuilding the indexes and
the constraints with no validate 

Depending on your version of Oracle and the volume of data being moved, the
frequency of such moves, and your SLAs, you may also want to look at other
options such as direct path loads, RMAN, TTS, for data movement for
environment replication.

-Krish

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kurt Franke
Sent: Thursday, November 29, 2007 6:47 AM
To: anysql@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Any way to forace parallel query for another session?


Hi,

you may build a logon trigger on database, check there if program in
v$session
is exp or imp and if found then call the alter session with native dynamic
sql:
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY PARALLEL n';


regards

kf


> -----Ursprüngliche Nachricht-----
> Von: anysql@xxxxxxxxx
> Gesendet: 28.11.07 13:04:37
> An: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> Betreff: Any way to forace parallel query for another session?


> Hi:
> 
>  
> 
> I want to speed up the exp or imp process, one way is to force parallel of
the exp/imp session. We can use "ALTER SESSION FORCE PARALLEL QUERY PARALLEL
n" for current session.
> 
>  
> 
> Is their any interface to change another session of the force parallel
query setting?
> 
>  
> 
> 
> 
> 
> 
> Thanks.
> 
>  
> 
> -- Fangxin Lou, Oracle DBA, from China
> 
>  
> 
> Good tools make work easy and improve life quality
> 
> -- http://www.dbatools.net
> 
>  
> 
> Personal software, life, research and professional Oracle recovery service
> 
> -- http://www.anysql.net/en
> 
> -- http://www.anysql.net   (Chinese)


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



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


Other related posts: