AnsweredAssumed Answered

Oracle 12.1.0.2 and SDE 10.2.2

Question asked by sherriekubis on Aug 31, 2015

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;

Outcomes