Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

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

ORA-29400: data cartridge error ORA-10260: PGA limit (20000 MB) exceeded - process terminated

Received Response
79
Views
5
Comments
Darwin HP
Darwin HP Rank 3 - Community Apprentice
in Oracle Analytics Publisher

Summary:

We are running a custom report that have recover more than 1.2 million of records and cheking the XML Data this have over the 1GB, when we executed this report we are getting the follow error message:

Error getting report data
[INSTANCE_ID=bip.bi_server1] DataException:[INSTANCE_JOB_ID=2302655] oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLException: ORA-29400: data cartridge error
ORA-10260: PGA limit (20000 MB) exceeded - process terminated


However, when we execute this same report with other parameters to recovery less records we are getting the output in a good time. In addition we optimized the query cost and reduce the tag of the XML but still continue getting the same error message.

¿Someone could help with this issue? ¿We need to setup something in the BIP in Fusion ERP?

Thank for your help!

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi,

    You initially posted this on the OTBI forum so assume this is a report in Oracle Transactional Business Intelligence OTBI in Oracle Fusion Cloud Applications not in Oracle Analytics Cloud OAC. So in either case this issue looks to be in the data source database server, not in the application server, caused by the SQL query sent from the data model in your publisher report to the data source database.

    PGA is the Program Global Area which is the not shared private random access memory area reserved exclusive for the operating process or thread. PGA is used for various purpose including sort area, cursor cache etc. All databases are configured with limits to restrict how much work they can do. You hit a limit.

    Do you get any insights when you debug your data model using

    View Engine Log and Generate SQL Monitor Report ?

    Does your query include an order by? Maybe consider this performance recommendation

    Oracle Business Intelligence Publisher Performance Recommendations for Fusion Cloud Applications (Doc ID 2800118.1)
    Consider removing ORDER BY from very large Financial extracts to eliminate expensive SORT operation.

  • Gianni Ceresa
    Gianni Ceresa Mod

    As Nathan said, ORA-xxxxx errors are Oracle Database errors. On your side you should adapt your query if you want to avoid the error.

    Or take those ORA-xxxxx errors, your query and go speak with your DBA to see what can be done on the database side. The PGA limit can be defined at the database level (assuming you are querying your own database and you have a DBA in charge of it, if not, then follow the links posted by Nathan above and change your query.

  • Darwin HP
    Darwin HP Rank 3 - Community Apprentice

    Thank you, for your responses.

    Dear Nathan, in fact the report is running over OTBI. We work tunning the query and supress a order by sentence because there are a lot of data and other fixes to reduce the costs of the query statement, we could reduce . Even we reduce the size o xml data (Although according you said this not impact over the PGA)

    So, for all the thing we did, maybe do you know if it is possible to increase the PGA size for OTBI?. Consider that we are running this report accessing by Fusion Application.

    Best Regards

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi Darwin,

    Your query is not failing in the OTBI database (RPD) on the application server in OTBI. The database (RPD) in OTBI does not have a PGA. This is a different type of oracle database with different architecture from standard.

    Your physical SQL query(s), either 1) generated by your analysis in OTBI or in data model in report in OTBI with data sets i) of type sql with data source oracle bi ee or ii) data set type analysis or 2) in your data model in a report in OTBI with data sets of type sql with data source FSCM/CRM/HCM - are failing on the database server in the oracle fusion cloud applications database. The oracle fusion cloud applications database has a PGA.

    So you can perhaps ask the database administrator of the hosted oracle fusion cloud applications database. But this request is not a request to change the configuration of OTBI. Your request is to change the application database used by everybody - not just consumers in OTBI but all the actors in all your business process who do transactions.

    You can prove this theory by cut pasting the physical sql(s) from your analysis or report in OTBI. Then issue that SQL directly (as direct as you can) to the oracle database with the same parameters. You should get the same error. But you have proven it has nothing much to do with OTBI. Unfortunately this is difficult to do since the only easy way to issue physical sql to application database is build an ad-hoc data model in OTBI to issue physical SQL - but at least you rule out any formatting etc in report laouts in OTBI / anything going on in the RPD database if you are using subject areas etc.in OTBI

  • RichardChan
    RichardChan Rank 6 - Analytics Lead

    Have you thought about configuring to burst to file subsets filtering on some field such as Country, this will break down the work the database has to do