Oracle 12.1.0.2 and SDE 10.2.2

2252
0
08-31-2015 07:10 AM
Highlighted
Occasional Contributor

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;

Reply
0 Kudos
0 Replies