• Post category:BI Publisher
  • Post comments:5 Comments
  • Post last modified:June 12, 2020
  • Reading time:2 mins read
You are currently viewing Fix for SQL query time out error in BI Reports
Fix for SQL query time out error in BI Reports

In this article we will look into resolving the time out error for BI Publisher reports.

The default limit set at BI server level for time out is 500 sec.

However we can override this time out limit setting for individual reports at the data model level.

Open the Data Model from BI Catalog.
Click on Properties tab:

Set the Query Time out to 1000000 – It will increase the time out for this specific report only.

set Enable SQL Pruning to “On” – SQL pruning enhances performance by fetching only the columns that are used in the report layout/template. Columns that are defined in the query but are not used in the report are not fetched

timeout error 1024x550 - Fix for SQL query time out error in BI Reports
Data Model Properties

Once these changes are done, save the data model and run it again.

Now the issue should be resolved.

Tip: The settings at the Data Model will not have any impact on the other reports. For other reports the server level setting of 500 sec will work. Override will apply only to the specific Data Model.

  • Hi Sricharan,

    I have made changes Query Time out to 1000000 and Enabling SQL Pruning to “On” – SQL pruning enhances in the Data Model Properties.
    But we are getting below timeout error. We are requesting you to help me on this issue.

    Error Message:

    Detail Message:
    ::JOB_WAIT_TIME_SECONDS{2}::JOB_FETCH_TIME_HOURS{14.2}::JOB_EXECUTION_TIME_HOURS{14.2}::JOB_PRIORITY_COLON_NORMAL::::JOB_PROCESSOR_EXCEPTION::[INSTANCE_ID=bip.bi_server1] [INSTANCE_JOB_ID=1640034]::::ERROR_GETTING_REPORT_DATA::[INSTANCE_ID=bip.bi_server1] DataException:[INSTANCE_JOB_ID=1640034] oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 246 with name “_SYSSMU246_3157079749$” too small

    oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLException: ORA-01555: snapshot too old: ro::[INSTANCE_ID=bip.bi_server1]

    Regards,
    Bala

  • Hi Bala,

    Even after doing these changes there is still a chance you get the error if you are using payroll balances in the report as they use packages to get data. Are you using payroll balances? You can try scheduling the report and see if it runs instead of running manually?

    Thanks,
    Sricharan

  • Hi Sricharan,

    Thanks for your quick response. We are using payroll tables. Please help me on this issue.
    SQL Query:

    SELECT DISTINCT PAPF.PERSON_NUMBER ID
    ,PN.DISPLAY_NAME NAME
    , XEP.LEGAL_ENTITY_IDENTIFIER COMPANY
    ,HR_DEPT.ATTRIBUTE1 DEPARTMENT_NAME
    ,PAF.ASSIGNMENT_TYPE EMPLOYEE_TYPE
    ,JOBS.JOB_CODE JOB_CODE
    ,JOBS.NAME JOBCODE_DESCRIPTION
    ,TO_CHAR (MAX(PEE.EFFECTIVE_START_DATE), ‘MM/DD/YYYY’) LAST_DAY_OF_REPORTED_TIME
    FROM PER_ALL_PEOPLE_F PAPF
    ,PER_PERSON_NAMES_F PN
    ,PER_ALL_ASSIGNMENTS_M PAF
    –,PER_ASSIGNMENT_STATUS_TYPES_TL PASTT
    ,PER_PERIODS_OF_SERVICE PPOS
    ,PER_JOBS_F_VL JOBS
    –,HR_LOOKUPS HL
    ,hr_locations_all_f_vl LOC
    ,hr_all_organization_units_f_vl HR_DEPT
    ,PAY_ELEMENT_ENTRIES_F PEE
    ,PAY_ELEMENT_TYPES_VL PET
    ,PAY_ELE_CLASSIFICATIONS_VL PEC
    ,PER_PHONES PPH
    ,HR_ORGANIZATION_V HROV
    –,HR_ORGANIZATION_V HROV1
    ,HR_ALL_POSITIONS_F_VL POSI
    , XLE_ENTITY_PROFILES XEP
    WHERE PAPF.PERSON_ID = PAF.PERSON_ID
    AND PAPF.PERSON_ID = PN.PERSON_ID
    AND PAPF.PERSON_ID = PPH.PERSON_ID(+)
    AND PAF.POSITION_ID = POSI.POSITION_ID
    AND POSI.attribute1 IS NOT NULL
    AND PN.NAME_TYPE = ‘GLOBAL’
    AND PAF.ASSIGNMENT_TYPE=’E’
    AND PAF.EFFECTIVE_LATEST_CHANGE = ‘Y’
    AND PAF.HOURLY_SALARIED_CODE = ‘H’
    –AND PAF.ASSIGNMENT_STATUS_TYPE =’ACTIVE’
    –AND PAF.ASSIGNMENT_STATUS_TYPE_ID = PASTT.ASSIGNMENT_STATUS_TYPE_ID
    –AND PASTT.SOURCE_LANG=’US’
    AND JOBS.JOB_ID = PAF.JOB_ID
    –AND JOBS.JOB_FUNCTION_CODE = HL.LOOKUP_CODE(+)
    — AND HL.LOOKUP_TYPE(+) = ‘JOB_FUNCTION_CODE’
    AND papf.person_id = ppos.person_id
    AND PAF.PERIOD_OF_SERVICE_ID=PPOS.PERIOD_OF_SERVICE_ID
    AND ppos.date_start = (SELECT MAX(ppos1.date_start)
    FROM per_periods_of_service ppos1
    WHERE ppos1.person_id = ppos.person_id
    AND ppos1.date_start <= TRUNC(SYSDATE)
    AND ppos1.primary_flag = 'Y'
    GROUP BY ppos1.person_id)
    AND PAF.LOCATION_ID = LOC.LOCATION_ID (+)
    AND HR_DEPT.ORGANIZATION_ID(+) = PAF.ORGANIZATION_ID
    AND PEE.PERSON_ID = PAF.PERSON_ID
    AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
    AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
    AND HROV.ORGANIZATION_ID = PAF.LEGAL_ENTITY_ID
    AND HROV.CLASSIFICATION_CODE ='HCM_LEMP'
    AND HROV.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
    –AND HROV1.ORGANIZATION_ID = PAF.BUSINESS_UNIT_ID
    –AND HROV1.CLASSIFICATION_CODE ='FUN_BUSINESS_UNIT'
    AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PN.EFFECTIVE_START_DATE AND PN.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
    AND :EFFECTIVE_START_DATE BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN HROV.EFFECTIVE_START_DATE AND HROV.EFFECTIVE_END_DATE
    –AND TRUNC(SYSDATE) BETWEEN HROV1.EFFECTIVE_START_DATE AND HROV1.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN JOBS.EFFECTIVE_START_DATE AND JOBS.EFFECTIVE_END_DATE
    AND PEC.classification_name IN ('Standard Earnings','Nonpayroll Payments')
    AND LOC.COUNTRY IN ('US','CA')
    –AND PASTT.USER_STATUS ='Active – Payroll Eligible'
    AND PET.BASE_ELEMENT_NAME NOT LIKE ('%Retro%')
    –AND PAPF.PERSON_NUMBER ='00000767332'
    –AND PAPF.PERSON_NUMBER IN NVL(:PERSON_NUMBER, PAPF.PERSON_NUMBER)
    AND (PAPF.PERSON_NUMBER IN (:PERSON_NUMBER) OR 'All' IN (:PERSON_NUMBER || 'All'))
    –AND HR_DEPT.NAME IN NVL(:DEPT_NAME, HR_DEPT.NAME)
    AND (HR_DEPT.NAME IN (:DEPT_NAME) OR 'All' IN (:DEPT_NAME || 'All'))
    –AND PEE.EFFECTIVE_START_DATE = :EFFECTIVE_START_DATE
    GROUP BY PAPF.PERSON_NUMBER, PN.DISPLAY_NAME, XEP.LEGAL_ENTITY_IDENTIFIER, HR_DEPT.ATTRIBUTE1, PAF.ASSIGNMENT_TYPE, JOBS.JOB_CODE, JOBS.NAME
    ORDER BY PAPF.PERSON_NUMBER

    Regards,
    Bala

  • Hi Bala,
    I think you are missing joins some where due to which duplicate rows are coming up i guess and hence resulting in time out issue.Can you hardcode one employee and check if the result is coming up correctly and then run for all employees. In this way you can diagnose issues.
    Thanks,
    Sricharan

  • Hi Sri,

    I have changed time limit to 1000000 but still I am getting timeout (500) error. Is there something changed in recent release?

    Thanks,
    Gopi

  • In visible box by plugintheme