6.5.1 NVA Generation Slow in a 12.2.5 Oracle Applications Planning Instance (ASCP), Needing Fixed Object and Data Dictionary Stats Gathered

We have our 12.2.5 Oracle Applications ERP instances and then we have our 12.2.5 Oracle Applications ASCP, the planning module, instances. 

With the 6.4.1 NVA regeneration process associated with our planning module, my NVA regeneration process (stage 2 through 4) would usually take about 15 to 20 minutes.

With 6.5.1 NVA, my regenerations were taking about 1 hour.

While watching my output from SQL*Plus (used for my NVA regeneration process), I see this text, "Default role prefixes to Oracle user names", which is from xgenall.sql script. It stays at this output line for about 1/2 hour or so.

When I look at the most recent records in v$sql, I see that w_process_user_flag.sql is being ran.


Thinking that there might be a locking issue (seen this before), I queried for the existing locked objects:

SELECT
    a.os_user_name,
    a.oracle_username,
    a.object_id,
    c.object_name,
    c.object_type
FROM
    v$locked_object a
    JOIN
        dba_objects c
    ON
        a.object_id = c.object_id;

After reviewing the lack of locks associated with the NVA as a possible source of impediments, I raised a ticket with Magnitude Software.


Their proposal was to execute running statistics for a special group of objects. Specifically, they suggested to have statistics gathered for fixed objects and data dictionary objects:



exec dbms_stats.gather_fixed_objects_stats();
exec dbms_stats.gather_dictionary_stats;



After this was executed by our dba, the performance reverted back to 15 to 20 minutes to refresh.


From a dba perspective, these objects just are not gathered very often (comes down to need). With us migrating to 12.2.5 Oracle Applications in July of 2016, many things change in the database so you would anticipate that these object could benefit from a gathering of statistics.