In Oracle 12c we allow the nightly system stats gathering to collect statistics for us.
We have one feature class that causes a timeout. In reproducing this manually, I find that it's the domain index, with this SQL running.
This FC is dropped and recreated nightly, so the possibility of gathering and then lock stats isn't there. Also, I have yet to get it to finish.
I tried to put a degree on the index, but running the stats doesn't pick that up.
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'SDECREATOR'
,TabName => 'SITES_EDIT_WCPFLG1'
,Estimate_Percent => dbms_stats.auto_sample_size
,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO '
,Degree => dbms_stats.default_degree
,Cascade => FALSE
,No_Invalidate => FALSE);
END;
/
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (
OwnName => 'SDECREATOR'
,IndName => 'A1245_IX1'
,Estimate_Percent => NULL
,Degree => dbms_stats.default_degree
,No_Invalidate => FALSE);
END;
/
SELECT /*+ no_parallel(b) no_parallel_index(b) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
DBMS_ROWID.rowid_block_number (b.ROWID)
FROM (SELECT s.sp_id,
s.gx,
s.gy,
ROW_NUMBER () OVER (PARTITION BY s.sp_id ORDER BY s.gx, s.gy) rncol
FROM SDECREATOR.S1375_IDX$ s) sp,
SDECREATOR.SITES_EDIT_WCPFLG1 b
WHERE rncol = 1 AND b.ROWID = sp.sp_id
ORDER BY sp.gx, sp.gy, sp.sp_id;
