Open adrianhj opened 1 year ago
@adrianhj Thanks for documenting this issue here. I spent some time investigating problems with coverage and may also reach out to Oracle Development for help on that topic.
X
does the code from schema X
invokes code from schemas A,B,C,D...
etc?If the answer to both questions = yes
then it may be that the coverage is gathered on all the code from all the schemas that are used on the test run.
I will need to do some local experiments to understand this but it is clearly a problem with DBMS_PROFILER
and how much memory is needed to store the information.
If you see 1.5 GB mem used and your codebase is only 100k lines, it would indicate that DBMS_PROFILER
needs around 1.5kB of memory for each line of code.
So profiler must be gathering more information than you need.
I have never tried it, but maybe limiting the privileges of the user that is running the tests. Link
You would need to make sure that the user who is running the tests does not have the CREATE ANY PROCEDURE
privilege.
@adrianhj Are you able to provide the graph of memory usage with & without flush functionality that you added in slack? It would be very helpful to keep it here as slack has already hidden the conversation
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_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):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: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