Re: exchange partition in 9.2

Are you using UPDATE GLOBAL INDEXES clause in the ALTER TABLE EXCHANGE PARTITION? This causes significant IO.

Try tracing the session using 10046 trace and see exactly what statements are being issued.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; ALTER TABLE EXCHANGE ....
ALTER SESSION SET EVENTS '10046 trace name context off';

See the trace file generated in udump; this should tell you the p1 and p2 values of the "db file ... reads", which you use to get the segment information from dba_extents.

Another option, and much better one, is using Oracle Trace, which lets you see these p1 and p2 values in the database.

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, June 17, 2003 10:59 AM

> Arup and Jacques:
>
> thanks for your help.
>
> The command we are executing is alter table exchange
> partition ... with table .. include indexes. We have
> also a PK on each of the tables (local index on a
> partitioned table). Go global indices. We have tried
> the same command with novalidate and the results
> were the same.
>
> Any thougths?
> --- Jacques Kilchoer <[EMAIL PROTECTED]>
> wrote:
> > Did you say alter table exchange partition ...
> > validate or alter table exchange partition ...
> > novalidate? If you say novalidate then Oracle will
> > sort the exchange table to make sure that you are
> > not creating duplicate rows for the PK constraint.
> > There is a Metalink note on that.
> > Does the table have a PK enforced by a locally
> > partitioned unique index? Any global indexes?
> > What options did you have on the exchange partition
> > statement?
> >
> > > -----Original Message-----
> > > From: Gurelei [mailto:[EMAIL PROTECTED]
> > >
> > > We are having an interesting issue with the
> > exchange
> > > partition command in our shop. We are running
> > oracle
> > > 9202 on AIX 5l. The exchange partition command
> > used
> > > to take about 1 sec (or less) in our old
> > environmen
> > > (oracle 81 Dynix 4.3.3). Now the same operation
> > takes
> > > about a minute and creates a ton of IO. an Oracle
> > rep
> > > said that this has to do something with the way
> > > exchange partition now treats primary and unique
> > keys.
> > > Recently however a DBA on my team did 4 exchanges
> > in a
> > > row between the same two tables. First one took
> > about
> > > 45s and did a lot of IO. next one took almost no
> > time
> > > and no IO (and it did move the data), the next one
> > -
> > > again 45s and IOs, the fourth one - no time again.
> > > Does any one has any experience with this issue
> > and
> > > may be can shed some light on it.
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Jacques Kilchoer
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gurelei
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).