Oracle 12.1.0.2 and SDE 10.2.2

SherrieKubis — Mon, 31 Aug 2015 14:10:11 GMT

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;

topic Oracle 12.1.0.2 and SDE 10.2.2 in Data Management Questions