Error while running BIP report (0ORA-10260: limit size (1048576) of the PGA heap set by event 10261 — Oracle Analytics

Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Error while running BIP report (0ORA-10260: limit size (1048576) of the PGA heap set by event 10261

Received Response
105
Views
6
Comments

Content

Hi,

 We are getting an below error while working on BIP report to fetch Person data in HCM module. Any idea what could be the cause..?  Thanks!

ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded

Any help is highly appreciated.

 

 

Answers

  • Wade Wilson
    Wade Wilson Rank 4 - Community Specialist

    Hi Gaurav. This seems like a bug. You should probably create an SR or check the versions listed in this document to see if any of these apply to you - https://support.oracle.com/epmos/faces/DocumentDisplay?id=403584.1

    Thanks.

  • User720640-Oracle
    User720640-Oracle Rank 5 - Community Champion

    When you get database error 'ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded', it means the custom SQL report is too complex and has run out of the allocated memory size.  The recommendation is to simplify the SQL.  Please check out the blog posting on "ORA-10260: Limit size (1048576) of PGA heap set by event 10261 exceeded'

     

  • Colin Foyle-19923
    Colin Foyle-19923 Rank 1 - Community Starter

    Hi Gaurav,

    I had a similar error message when using a very complex OTBI report. Oracle couldn't help as I was using Cloud, and I was hitting limits on the memory being used. I worked out that my SQL was bringing back more data than was really needed. So edited the SQL to use less data, and was able to get passed the limits in Cloud. You might want to see if you restrict the data by bringing in just a few employee records whether it works ok. If like me you are on the Cloud, then you will be limited by the amount of memory Oracle assign to their environments, and isn't willing to change. Best to check that the SQL used doesn't have too many Left Outer Joins (which was my case), or something else. Assuming you are using SQL. You can also try the SR route, just in case it is a bug, and not the same thing I experienced. We raised an SR too, but in the end I had to create a workaround.

    Good luck.

  • ~ GK ~
    ~ GK ~ Rank 2 - Community Beginner

    Thanks all. Yes, you all are right, it has to do with the complex SQL. Our SQL is 2000+ lines of code with outer joins and Unions. Surprisingly when same SQL is execute on the other customer POD it is executing like charm. We are on Saas when raised SR we got the same standard answer , it is custom SQL and we need to tune the query. We are tuning the query now.

    Thanks again for your response.

     

    Thanks

    ~ GK ~

  • Aravind Ragul-4349
    Aravind Ragul-4349 Rank 2 - Community Beginner

    Hi Gaurav,

    Even we faced the same issue where the code worked in one instance and ran into error in another instance where I wanted it to run :)

    As a work around we ran the code with multiple datasets(we split the unions to different datasets) and designed the template accordingly so we have the output in a single file.

    Let me know if this helps

    Thanks,

    Arav

  • ~ GK ~
    ~ GK ~ Rank 2 - Community Beginner

    Thanks Aravind. We are following this approach and simplifying the complex unions.