I encountered a similar issue recently on a custom module of Oracle CC&B.
The process errors with ORA-04036
If you dump the dba_hist_active_sess_history and graph it in time series
you'll see PGA_ALLOCATED increases overtime, and you can color that by
SQL_ID and you'll be able to track the PL/SQL entry object id that invoked
those SQLs.
The problem was the package contained logic that would loop based on the
number of rows of the driving cursor and push it to a PL/SQL collection in
memory which overloads the PGA (reaching up to 30GB).The culprit SQL_ID was
executed 283 million times with 282 million rows processed all that pushed
to PGA. (Increasing the Size of a Collection (EXTEND Method)
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CJAIJHEI
)
The recommendation to the developer was to rewrite the package to a
set-based approach rather than row by row.
Putting 282 million rows on a PL/SQL collection is not scalable (due to
server physical memory limitations) and will run longer as more rows are
processed vs one parallelized high IO bandwidth operation.
-Karl
On Fri, Dec 4, 2020 at 11:03 AM Purav Chovatia <puravc@xxxxxxxxx> wrote:
Hello Experts,
Need some help desperately.
We have an application that is doing a StoredProc call and a SQL call from
a Standalone Java App which uses the Thin driver.
The StoredProc does an UPDATE with a WHERE clause on the single column
Primary Key.
If the SQL%ROWCOUNT=0 then it will do an INSERT.
The SQL does a select on the table with a WHERE clause on the Primary Key.
No joins. No CLOB/BLOB/LOB. No XML. Prepared Statements used. Only thing
that stands out is that the table has 100+ columns.
PGA goes on increasing and hence free memory on DB server goes on
decreasing.
Problem originally started with ojdbc7.jar from 12.1.0.1.
We tried the ojdbc7.jar from 12.1.0.2 and the problem worsens.
Problem persists with ojdbc6.jar from 11.2.
With ojdbc6.jar problem does not reproduce.
Problem persists with DB 11.2.0.4, 12.1.0.2. Problem persists on DB server
on Linux as well on Solaris.
On DB server, we dont see any open cursor problem. No error on App side or
on DB side except ORA-04036 in case of 12c because it will cap PGA with
pga_aggregate_limit.
We dug on PGA front and identified that subheap kxs-heap-p is where the
memory is used. But cannot understand what does that mean?
Would appreciate any ideas.
It was a shock to realize that there is no way beyond identifying the
subheap! Developers are ready to fix the issue but they have been
requesting to tell what is to be fixed. Is there no way to identify what is
eating up the PGA?
Thank you.
