Table Exchange Partition Taking Longer After Upgrade To Database 19.4 (Doc ID 2763501.1)

Last updated on NOVEMBER 29, 2022

Applies to:

Symptoms

• After upgrade the database to 19.4, table exchange partition taking longer
• From 10046 tracing, following recursive sql taking most time:
  select /*+ first_rows(1) ordered */ 1 <<<<<<<<<<<<< Query like this Taking long time to execute
  from
  "XXXX"."T1" A

  call count cpu elapsed disk query current rows
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  Parse 1 0.08 0.08 0 0 0 0
  Execute 1 0.00 0.00 0 0 0 0
  Fetch 1 19381.94 19686.52 14076757 1701121479 0 0
  ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  total 3 19382.02 19686.60 14076757 1701121479 0 0

  Misses in library cache during parse: 1
  Optimizer mode: FIRST_ROWS
  Parsing user id: SYS (recursive depth: 1)
  Number of plan statistics captured: 1

  Rows (1st) Rows (avg) Rows (max) Row Source Operation
  ---------- ---------- ---------- ---------------------------------------------------
  0 0 0 COUNT STOPKEY (cr=1701121479 pr=14076757 pw=0 time=1228342642 us starts=1)
  0 0 0 NESTED LOOPS (cr=1701121479 pr=14076757 pw=0 time=1228342632 us starts=1 cost=8 size=240 card=4)
  2990984898 2990984898 2990984898 VIEW VW (cr=14118964 pr=14076757 pw=0 time=79224262 us starts=1 cost=4 size=120 card=4) 2990984898 2990984898 2990984898 UNION-ALL (cr=14118964 pr=14076757 pw=0 time=3082742720 us starts=1)
  0 0 0 PARTITION RANGE ITERATOR PARTITION: 1092 1102 (cr=0 pr=0 pw=0 time=1098 us starts=1 cost=2 size=84 card=2)
  0 0 0 TABLE ACCESS FULL FULL PARTITION: 1092 1102 (cr=0 pr=0 pw=0 time=1083 us starts=11 cost=2 size=84 card=2)
  2990984898 2990984898 2990984898 PARTITION RANGE ITERATOR PARTITION: 1 1090 (cr=14118964 pr=14076757 pw=0 time=900271461 us starts=1 cost=2 size=84 card=2) 2990984898 2990984898 2990984898 INDEX FAST FULL SCAN PK PARTITION: 1 1090 (cr=14118964 pr=14076757 pw=0 time=4289516252 us starts=1090 cost=2 size=84 card=2)(object id 481123)
  0 0 0 INDEX RANGE SCAN PK (cr=1687002515 pr=0 pw=0 time=894493682 us starts=1303982398 cost=1 size=30 card=1)(object id 4330599)

   

Changes

Database Upgraded to 19.4

Cause

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