OTBI Report Too Slow / Not Generating Results

User_DKPSX

Summary:

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?

Thanks!

Content (required):

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

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

21D

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

Nathan CCC

Hi,

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"

https://docs.oracle.com/en/cloud/saas/human-resources/21d/ochus/reporting-on-application-users-and-roles.html#OCHUS1250121

Regards, Nathan

Vijay Ramani VJ

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.

Vj

User_DKPSX

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.

Thanks!

Katie

User_DKPSX

@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.

Thanks!

Katie

Vijay Ramani VJ

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.

Vj

User_DKPSX

Hi Vijay,

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

Thanks!

Katie

Vijay Ramani VJ

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

Vj

User_DKPSX

It works!!!

Thank you!!!

Katie

Vijay Ramani VJ

Perfect!

Matthew Voss

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'',''12.1.0.2'')';

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';