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: [EMAIL PROTECTED]
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: [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).
