In this article, let’s look into the recent development by Oracle to Cancel the Oracle BI Reports which exceeds 5 hours or 4 billion logical I/O records.
You might have got notification from Oracle team regarding this information. This will be implemented for all production and non-production instances. Oracle has provided a 6 month window to optimize the reports which run for more than the specified time and also sent the list of reports which run for more than the 5 hrs limit set by Oracle.
We can also request for another 30 day extension for reports if the optimization has not happened within the 6 month window.
Post the 6 month period, Oracle will cancel all the long-running BI Reports after exactly the 5-hour mark.
More details in the below Oracle Note
Table of Contents
ANNOUNCEMENT: FA-SAAS SQL Guardrails for Long-Running BI Reports and Queries (Doc ID 2884584.1)
Now, lets look into how to optimize the BI Reports to make them run within lesser than 5 hours set by Oracle.
First Approach – Merge multiple data sets into single data set using CTE (Common Table Expression) WITH clause
If there are multiple data sets used by the Data Model, we can merge them into a single data set by using the WITH clause. In most of the cases we might need to call the second data set only for a specified criteria, however the data set gets executed for each and every employee increasing the time the report taken to run.
We can use the WITH clause in the main query and then use the filter criteria to fetch specific population and use the query from the second data set there. Finally, you can combine the results from both with queries to get the output.
WITH MAIN_LOGIC as (
select person, assignment, full_part_time
from <tables>
where <filter criteria>
)
, SECOND_QUERY as (
select
ml.person,
<fetch some balance value from second data set>
from MAIN_LOGIC ml
where full_part_time = 'FULL_TIME'
)
select
ml.person,
ml.assignment,
sq.balance
MAIN_LOGIC ML, SECOND_QUERY SQ
where ML.person = SQ.person(+)This way the second data set gets executed only for the right population and not for everyone. This will drastically reduce the performance of the report.
Second Approach – Use the appropriate hints while using payroll queries
We can us the /* Materialize */ hint while fetching the payroll balance or run result information. Which will also improve the performance of the report.
Third Approach – Remove unwanted joins and fields from the query
Many times, while developing the report, we reuse some other query which might contain many other tables which may or may not be required by the current report. We can remove the unnecessary tables and their respective joins. We can also remove the fields that are being fetched and are not included on the template and hence will not appear on the output file. It takes time to fetch these fields and once removed, will improve the performance a bit.
Last Approach – Using Parallel Instance concept if all above approaches doesn’t work
Even after implementing all the above approaches, if the report is still taking more than 5 hours and getting canceled, then it is mostly because of the number of records that the query is pulling. In most cases, it would be related to payroll run results, payroll costing, etc. In this case, we do not have much scope to optimize the query in the report. We can use the parallel processing technique discussed in the article, however, we will have to live with multiple output files and convince the client/users that at least with this approach we can view the output of the report which otherwise would have got canceled and doesn’t generate output.
We have to modify the report to process the employees in chunks instead of the complete report, so each individual chunk will take less time than the complete report. Post all the chunks run, we need to combine the output of all reports.
Hope this helps. If you have any questions, reach out to us using the contact us section.
