My Oracle Support Banner

Partitioning activities are taking a long time to complete (Doc ID 2818146.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
Partitioning activities run long

We're noticing that partitioning activities such as splitting partitions and creation partitioned tables taking a long time.


Seeing row cache locks and reliable message waits in the traces.

ALTER TABLE SHANE_MONTH_DATA SPLIT PARTITION SHANE_MONTH_DATA_MAX AT ( TO_DATE(
'20220912', 'YYYYMMDD' ) ) INTO ( PARTITION SHANE_MONTH_DATA_P999, PARTITION
SHANE_MONTH_DATA_MAX ) PARALLEL 24

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.50          0          0        114           0
Execute      1      9.58      63.52       7709        514      37463           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.64      64.03       7709        514      37577           0

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=4036815 us starts=1)
        0          0          0   PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=10194912 card=8169)
        0          0          0    LOAD AS SELECT (HYBRID TSM/HWMB) SHANE_MONTH_DATA (cr=0 pr=0 pw=0 time=0 us starts=0)
        0          0          0     PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=10194912 card=8169)
        0          0          0      PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=10194912 card=8169)
        0          0          0       PX BLOCK ITERATOR PARTITION: 1 128 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=10194912 card=8169)
        0          0          0        TABLE ACCESS FULL SHANE_MONTH_DATA PARTITION: 3457 3584 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=10194912 card=8169)

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.