Hard Parse For Large Partitioned Table Is Very Slow (Doc ID 2486764.1)

SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
NVL(scanhint,0),NVL(bitmapranges,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 103636 10.56 13.18 0 0 0 0
Fetch 103636 2.22 3.84 3667 414544 0 103636
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 207272 12.79 17.03 3667 414544 0 103636

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS CLUSTER SEG$ (cr=4 pr=0 pw=0 time=30 us cost=2 size=65 card=1)
1 1 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=3 pr=0 pw=0 time=17 us cost=1 size=0 card=1)(object id 9)

SQL ID: c3zymn7x3k6wy Plan Hash: 3446064519

select obj#, dataobj#, part#, hiboundlen, hiboundval, flags, ts#, file#,
block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt,
blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, pctthres$,
length(bhiboundval), bhiboundval
from
indpart$ where bo# = :1 order by part#

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 112 0.00 0.00 0 0 0 0
Execute 112 0.02 0.05 0 0 0 0
Fetch 224 1.45 22.77 2854 4196 0 3584
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 1.48 22.83 2854 4196 0 3584

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
32 32 32 TABLE ACCESS BY INDEX ROWID INDPART$ (cr=4 pr=0 pw=0 time=1298 us cost=3 size=4929 card=31)
32 32 32 INDEX RANGE SCAN I_INDPART_BOPART$ (cr=3 pr=0 pw=0 time=661 us cost=2 size=0 card=31)(object id 274)

SQL ID: 9b4m3fr3vf9kn Plan Hash: 2436900644

select obj#, dataobj#, subpart#, hiboundlen, hiboundval, flags, ts#, file#,
block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt,
blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare2,
length(bhiboundval), bhiboundval
from
indsubpart$ where pobj# = :1 order by subpart#

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 2400 0.15 0.18 0 0 0 0
Fetch 4800 6.81 8.59 1410 99773 0 88800
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7206 6.96 8.77 1410 99773 0 88800

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
37 37 37 TABLE ACCESS BY INDEX ROWID INDSUBPART$ (cr=4 pr=3 pw=0 time=1411 us cost=0 size=2287 card=1)
37 37 37 INDEX RANGE SCAN I_INDSUBPART_POBJSUBPART$ (cr=3 pr=1 pw=0 time=829 us cost=0 size=0 card=1)(object id 720)