Re: PGA increasing continuously

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_at_gmail.com> 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.
>

-- 
Karl Arao
Wiki: karlarao.wiki
Twitter: _at_karlarao <http://twitter.com/karlarao>

--
http://www.freelists.org/webpage/oracle-l