Gather table statistics for SILOS_SL_GLBALANCEFACT running long

Question

Hi.

We have observed that the gather table statistics for SILOS_SL_GLBALANCEFACT frequently runs very slow. Although this is not always the case.

Here are some execution times (in seconds) for the last 19 loads - these are all incremental loads.

This is the command that is being run:

DBMS_STATS.GATHER_TABLE_STATS

(ownname => 'XXXXX',

tabname => 'W_GL_OTHER_F',

cascade => FALSE,

estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt =>  'FOR ALL COLUMNS
SIZE AUTO',

degree => DBMS_STATS.DEFAULT_DEGREE);

We are trying to determine if there is anything we can do to increase the performance of this task. Also, wondering why sometimes it finishes very quickly.

Thanks to all who participate here!

ODI version 11.1.1.9

Source: EBS 12.2 / Target: OBIA 11.1.1.10.1 [DB 11.2.0.4]

Regards,

Charles

Charles M · Answer

Thanks Thomas. I agree. This may be better served in the database forum for a deeper dive. I created it here in case any similar situations have occurred with other OBIA implementations. I did find some information suggesting that the above parameters for GATHER_TABLE_STATS are the "recommended" ones.

I will redirect this to our database friends now. I'll keep it open here as well, so that if/when there is a resolution I can add it back to this discussion. Thanks for the optimizer document ... I will review it.

Regards,

Charles

Thomas Dodds · Answer

1 - Serious:  high functioning OBI teams have their own DBA -- you've found the reason why

2 - Partly facetious:  if you don't have one then you've got to do two (or more) serious jobs for one paycheck ... so ... here's the manual  Managing Optimizer Statistics - 11g Release 2 (11.2)

3 - Serious: without knowing what you've done to the table (partitioned, indexes, etc) AND/OR load on this instance when you are running ELT, it's really difficult to point you in any sort of direction.

4 - Serious: you might want to ask in the DB forum - you'll get a DBA answer and not just a OBIA/ODI implementer answer.  It's not so much the issue with OBIA/ODI but with the database.  Those guys can give you a much more rich answer.

Charles M · Answer

There are 175,226,008 rows in this table (W_GL_OTHER_F).