Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Gather table statistics for SILOS_SL_GLBALANCEFACT running long

Received Response
1
Views
3
Comments
Charles M
Charles M Rank 6 - Analytics Lead
in Oracle Business Intelligence Applications

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.

pastedImage_1.png

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

Answers

  • Charles M
    Charles M Rank 6 - Analytics Lead

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

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Charles M Rank 6 - Analytics Lead

    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