Describe the bug
Not 100% sure this is a bug as such or whether this is 'just Oracle', but opening for discussion on ideas/possibilities/workarounds; somewhat discussed in https://utplsql.slack.com/archives/C0PK55Z08/p1663013558850999 previously.
When executing our test suite with coverage reporters enabled the executing session is killed due to ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT:
Errors in file /opt/oracle/diag/rdbms/xe/XE/trace/XE_ora_346.trc (incident=698) (PDBNAME=XEPDB1):
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
XEPDB1(3):Incident details in: /opt/oracle/diag/rdbms/xe/XE/incident/incdir_698/XE_ora_346_i698.trc
Observing memory usage of the session via a custom reporter dumping the following query result into a table via an after_calling_test hook we observe that usage is continuously growing without freeing until it hits the limits of the environment and the session is killed (~1.5GB memory used):
SELECT
ROUND(p.pga_used_mem/1024/1024,2) AS pga_used_mem_mb,
ROUND(p.pga_alloc_mem/1024/1024,2) AS pga_alloc_mem_mb,
ROUND(p.pga_freeable_mem/1024/1024,2) AS pga_freeable_mem_mb,
ROUND(p.pga_max_mem/1024/1024,2) AS pga_max_mem_mb
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.AUDSID = (select sys_context ('USERENV', 'SESSIONID') from dual);Executing the same suite without a coverage reporter the memory usage is steady at ~a few hundred MB of memory usage.
It feels like the PL/SQL Profiler is keeping all coverage data in memory throughout, leading us to the out of memory situation and session being killed.
Modifying https://github.com/utPLSQL/utPLSQL/blob/develop/source/core/coverage/ut_coverage_helper_profiler.pkb#L44 to call dbms_profiler.flush_data() seemed to produce a more saw-tooth shaped pattern of usage at the cost of now extremely slow test execution (from 10-15 minutes to hours).
We also attempted to add a 'profiler flusher' reporter which periodically invoked dbms_profiler.flush_data after each suite, but this does not seem to have achieved much:
overriding member procedure after_calling_suite(self in out nocopy ut_profiler_flusher, a_suite ut_logical_suite) as
l_return_code binary_integer;
begin
(self as ut_coverage_reporter_base).after_calling_suite(a_suite);
ut_coverage.coverage_resume();
l_return_code := dbms_profiler.flush_data();
ut_coverage.coverage_pause();
end;
Provide version info
Information about utPLSQL and Database version:
set serveroutput on
declare
l_version varchar2(255);
l_compatibility varchar2(255);
begin
dbms_utility.db_version( l_version, l_compatibility );
dbms_output.put_line( l_version );
dbms_output.put_line( l_compatibility );
end;
/
select substr(ut.version(),1,60) as ut_version from dual;
select * from v$version;
select * from nls_session_parameters;
select substr(dbms_utility.port_string,1,60) as port_string from dual;
21.0.0.0.0
21.0.0
PL/SQL procedure successfully completed.
UT_VERSION
------------------------------------------------------------
v3.1.12.3589
BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production 0
Version 21.3.0.0.0
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
PORT_STRING
------------------------------------------------------------
x86_64/Linux 2.4.xxInformation about client software
SQL Developer/utPLSQL Maven Plugin
To Reproduce
Steps to reproduce the behavior:
- Execute a somewhat sizeable test suite with a coverage reporter enabled (e.g. Sonar)
begin
ut.run(ut_coverage_sonar_reporter());
end;
/
- Monitor memory usage of test session as it executes
- Observe ever-growing memory usage by the session
Repeat above comparing to a test run without a coverage reporter enabled.
Expected behavior
Test suite completes successfully without ever growing PGA usage leading to out-of-memory and session killing.
Example code
N/A
Additional context
- ~100k LOC over ~500 objects in a single schema
- ~4.5k tests with ~50% coverage
Describe the bug
Not 100% sure this is a bug as such or whether this is 'just Oracle', but opening for discussion on ideas/possibilities/workarounds; somewhat discussed in https://utplsql.slack.com/archives/C0PK55Z08/p1663013558850999 previously.
When executing our test suite with coverage reporters enabled the executing session is killed due to
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT:Observing memory usage of the session via a custom reporter dumping the following query result into a table via an
after_calling_testhook we observe that usage is continuously growing without freeing until it hits the limits of the environment and the session is killed (~1.5GB memory used):Executing the same suite without a coverage reporter the memory usage is steady at ~a few hundred MB of memory usage.
It feels like the PL/SQL Profiler is keeping all coverage data in memory throughout, leading us to the out of memory situation and session being killed.
Modifying https://github.com/utPLSQL/utPLSQL/blob/develop/source/core/coverage/ut_coverage_helper_profiler.pkb#L44 to call
dbms_profiler.flush_data()seemed to produce a more saw-tooth shaped pattern of usage at the cost of now extremely slow test execution (from 10-15 minutes to hours).We also attempted to add a 'profiler flusher' reporter which periodically invoked
dbms_profiler.flush_dataafter each suite, but this does not seem to have achieved much:Provide version info
Information about utPLSQL and Database version:
Information about client software
SQL Developer/utPLSQL Maven Plugin
To Reproduce
Steps to reproduce the behavior:
Repeat above comparing to a test run without a coverage reporter enabled.
Expected behavior
Test suite completes successfully without ever growing PGA usage leading to out-of-memory and session killing.
Example code
N/A
Additional context