General OBIEE

Get Involved. Join the Conversation.

Topic

    Gaurav Kasliwal
    Error while running BIP report (0ORA-10260: limit size...Answered
    Topic posted December 18, 2015 by Gaurav KasliwalGold Medal: 3,500+ Points 
    63 Views, 6 Comments
    Title:
    Error while running BIP report (0ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded)
    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.

     

     

    Best Comment

    Ling Xiang

    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'

     

    Comment

     

    • Wade Wilson

      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.

    • Ling Xiang

      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

      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.

    • Gaurav Kasliwal

      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

        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

    • Gaurav Kasliwal

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