Oracle Transactional Business Intelligence

Products Banner

OTBI Report Too Slow / Not Generating Results

Received Response


Have created a report to pull all roles for all users (Audit purposes), and admittedly there is an excessive amount of data to pull. That being said, when running the report, i leave it running for 2-3 hours and nothing generates, the loading icon just runs. Even when scheduling the report, nothing generates.

Is there anything that can be done to fix this?


Content (required):

Roles Report created for Audit purposes. (Can attach catalog if necessary).

Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):


  • Nathan CCC
    Nathan CCC ✭✭✭✭✭


    One solution is to use instead the pre-defined report provided out of the box to give you a list of users and roles for audit purposes. Or at least compare your query to theirs to debug what joins you are doing differently to make it soooooo slow. Maybe start again with copy of out of the box extend that to plug any gaps if you need more stuff.

    For more information see

    Oracle Human Capital Management Cloud Securing HCM F46062-01 21D

    chapter 16 "Reporting on Application Users and Roles"

    Regards, Nathan

  • Try running it for a single user and see if you are getting the results.

    I do have the same issue. We created a user role report using security subject area and Workforce real time subject area. They were working fine until 21D and since then the report is spinning and doesn't return any data.

    I have an SR with oracle and they are investigating. Not sure if others are facing the same issue.


  • Hi @Nathan CCC, this is really helpful, thank you. This report pulls back some important info I can use, so I might try and work with this one.



  • @Vijay Ramani VJ This is also really helpful, thank you! Do you know of a porblem record at all? Or is the SR still ongoing? Checking today I can see this is still an issue.



  • Hi Katie,

    Per Oracle, this subject area (security real time) can't be used to create a cross-subject area analysis. Don't mix data from different folders within this subject area either.

    However, this was working for me or the last one year and Oracle admitted it as a bug and gave me a fix to apply on my report. Since it is working. Please let me know if you need the fix.


  • Hi Vijay,

    If possible I would really appreciate it if you could share the fix.



  • No worries...Here you go!

    Please use below hint in the report and let confirm :

    SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency1', OBIS_ORACLEDB_HINTS_FOR_TOP_SELECT='OPT_PARAM(''_optimizer_mjc_enabled'',''false'') OPT_PARAM(''_optimizer_cartesian_enabled'',''false'') OPT_PARAM(''_optimizer_skip_scan_enabled'',''false'') OPT_PARAM(''_complex_view_merging'',''false'') OPT_PARAM(''_push_join_predicate'',''false'') OPT_PARAM(''_optimizer_push_pred_cost_based'',''false'')',PREFERRED_CURRENCY='User Preferred Currency 1',QUERY_SRC_CD='Report';

    Steps :

    1. Edit the analysis

    2. Goto Advanced tab

    3. Provide above hint in prefix text box.

    4. Click on Apply SQL

    However, the internal development is still working for a permanent fix. Please let me know if the above hint worked on your OTBI report


  • It works!!!

    Thank you!!!


  • Thanks Vijay! Interesting as that's different from the hints they gave us. Below are the ones the gave us.

    1) SET VARIABLE OBIS_ORACLEDB_HINTS_FOR_TOP_SELECT= 'OPT_PARAM(''optimizer_adaptive_plans'',''false'') OPT_PARAM(''_optimizer_use_feedback'',''false'') OPT_PARAM(''optimizer_features_enable'','''')';

    1) SET VARIABLE OBIS_ORACLEDB_HINTS_FOR_TOP_SELECT = 'OPT_PARAM(''_push_join_predicate'',''false'') OPT_PARAM(''_complex_view_merging'',''false'') GATHER_PLAN_STATISTICS MONITOR';