unable to split a partition in parallel

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Wed, 05 Jun 2002 06:18:27 -0800
Message-ID: <F001.00474EEA.20020605061827@fatcity.com>

Listers,

We have a table range partitioned on a date column. Last night I tried to split the earliest partition into itself and an earlier (empty) partition in parallel. The partition is ~ 25 gigs. This ran overnight and did not finish.

Here is the statement:

alter table dwcorp.t_claim_alv
split partition p_200107
at (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
into (partition p_200106, partition p_200107) parallel(Degree 12)
/

A quick check of waits showed that the processes were active and not waiting. A check of the tablespace showed no temp segments being written there by the parallel processes.

I removed the parallelism clause and the statement immediately started writing temp segs that were growing. This ran for 5 hours and was less than 1/2 done, so we need the PQ to work since the split will break indexes and remove stats on the split partitions and we can't have THAT in the middle of the day.

Tried these things to rectify:

1. created empty table with same structure and indexes in a temp schema, parallel partition split worked.
2. altered the partition to nologging
3. altered the pq of the partition to match that of the statement
4. ran as both DBA and schema owner
5. removed the leading space from the partition clause
   (this is a historical design flaw, might be time to
   rectify)

Since the empty table worked, this is probably either a problem unique to this table or related to the data. My first thought is that the pq process co-ordinator is unable to resolve the partition key adequately and so is unable to properly handoff the required information to the child pq procs, so they are active but cannot proceed. This would explain why they write no temp segs but are active. I would expect to see pq enqueue waits of some sort, however.

Otherwise, perhaps the pq procs cannot write, but have never had problems with our PQ procs writing before and have done many parallel CTAS to this tablespace.

thanks,

jack silvey

---

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Wed Jun 05 2002 - 09:18:27 CDT