Re: exchange partition in 9.2

Gene,

I doubt the procedures followed for 9.2 are the same as 8.1; Partition exchange does not move any data, merely resets the pointers inside the data dictionary; so it should not take more than 1 second as you saw earlier.

Check if you were doing an ALTER TABLE TAB1 EXCHANGE PARTITION P1 WITH TABLE ST1 WITHOUT VALIDATION earlier and are doing WITH VALIDATION (or nothing at all, in which case this is the default). The with validation will have to check the non-partitioned table will generate a lot of read (and writes too, if doing a delayed block cleanout).

Are your 9.2 tablespaces locally managed with Automatic Segment Space Managed? I have seen a slight performance degradation with the setup; but can't prove conclusively. Un any case the performance impact on exchange partition is slight.

HTH. Arup Nanda

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, June 16, 2003 6:04 PM

> Hi all:
>
> 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.
>
> thanks
>
> gene
>
> __________________________________
> 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).