Customer is using BI Custom Report, which use to work fine before, but with time due to on-going requirements, Customer added more functionality and the code becomes more complex SQL. The report in question is complex because the user requirements are complex and there is no obvious way of reducing complexity. And not only that, but these BI Custom Report is now throwing "PGA Heap Size Error".
We have to be able restrict/tune "bad" SQLs. Not using optimized SQL statements by custom report is not something new. Making such changes in database can cause instability to production environments. Open this gate is not a solution.
The configuration of PGA (is 1GB) in the database for Cloud Customers and making PGA Size change in Database could impact all Fusion Application sessions as well. Which shouldn’t be good idea? For BI custom reports making changes in code is better way rather than making changes in Database (PGA Size) to avoid the error “ORA-10260: Limit size (1048576) of PGA heap set by event 10261 exceeded”.
If the report is running through RPD (like OTBI Queries), PGA increase via RPD Connection “later session” statement can help to BI sessions with required 2 GB PGA Size, as it will affect only BI sessions.
But if these are Custom BIP Reports, then it will not use RPD connections, instead creates JNDI connections (JDBC/ApplicationDB) to execute the queries. So we can try overriding the session values by adding:
‘ALTER SESSION SET … ‘commands into the query for Custom Reports.
Setting the session variable during initialization of the BI connections in WLS limits the change to only the BI connections. Changing it in the init.ora will affect all sessions to the database, and could result in a database outage.
Setting the alter session statement at init SQL and restarted WLS:
WLS console -> Datasources -> ApplicationDB -> Configuration -> Advanced -> init SQL
SQL alter session set events '10261 trace name context forever,level 2097152'
Whenever BIP creates new connection this SQL gets executed.