PGA leak when repeatedly querying V$SQL

Hello.

Is the following a known problem?

I get a PGA memory leak on a connection where I repeatedly query the V$SQL view via a JDBC connection. The PGA growth rate is around 0.33 MB / invocation on a tiny developer database. And much higher on a small production database. I am able to generate a 2GB PGA and an "ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT" in under a minute by running this query in a loop.

I want to use a similar query to poll the V$SQL view every 5 minutes in production to get a high-level overview of the Oracle SQL cache over time.

See the https://gist.github.com/micmich/ef8597914743ecef3402269e82ded830 github gist for a class generating the leak. I am reusing the JDBC connection, but closing the Satements and ResultSets.

Query generating leak:

select a1.sql_id,
      a1.elapsed_time,
      a1.cpu_time,
      a1.sql_fulltext sql_fulltext,
      a1.disk_reads,
      a1.direct_writes,
      a1.executions,
      to_timestamp(a1.first_load_time, 'yyyy-mm-dd/hh24:mi:ss') first_load_time,
      a1.last_load_time,
      a1.parse_calls,
      a1.buffer_gets,
      a1.rows_processed,
      a1.user_io_wait_time,
      a1.cluster_wait_time,
      a1.concurrency_wait_time,
      a1.application_wait_time,
      a1.parsing_schema_name
from v$sql a1

PGA size check, which increases until ORA-04036 is thrown:

select machine, round(pga_used_mem / (1024 * 1024),2) pga_used_mb
from v$session
     join v$process on v$session.paddr = v$process.addr
where sid = (select sid from v$mystat where rownum=1)
order by machine desc

I am able to recreate the issue on a developer database as below, but got similar issues from a customer presumably running a different configuration:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE12.1.0.2.0Production

TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

To recreate the issue I used the ojdbc7_g.jar retrieved from https://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html

Java sample code (see gist above)

package pgamemoryleak;


import java.io.BufferedReader;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


import oracle.jdbc.pool.OracleDataSource;


public class Main {


public static void main(String[] args) throws IOException, SQLException, InterruptedException {


final OracleDataSource oracleDataSource = readConfig();
try (Connection connection = oracleDataSource.getConnection()) {


while (true) {
final ResultSet resultSetMyPGA;
try (Statement statement = connection.createStatement();
ResultSet resultSetTopSQL = statement.executeQuery(QUERY_TOP_SQL)) {


// ignore the result - calling the query is enough to cause memory leak
}
try (Statement statement = connection.createStatement()) {
resultSetMyPGA = statement.executeQuery(QUERY_MY_PGA);
resultSetMyPGA.next();
final int pgaUsage = resultSetMyPGA.getInt(2);
System.out.println("PGA usage: " + pgaUsage);
}


Thread.sleep(1000);
}
}
}


private static final String QUERY_TOP_SQL =
"select a1.sql_id,"
+ "       a1.elapsed_time,"
+ "       a1.cpu_time,"
+ "       a1.sql_fulltext sql_fulltext,"
+ "       a1.disk_reads,"
+ "       a1.direct_writes,"
+ "       a1.executions,"
+ "       to_timestamp(a1.first_load_time, 'yyyy-mm-dd/hh24:mi:ss') first_load_time,"
+ "       a1.last_load_time,"
+ "       a1.parse_calls,"
+ "       a1.buffer_gets,"
+ "       a1.rows_processed,"
+ "       a1.user_io_wait_time,"
+ "       a1.cluster_wait_time,"
+ "       a1.concurrency_wait_time,"
+ "       a1.application_wait_time,"
+ "       a1.parsing_schema_name"
+ " from v$sql a1";


private static final String QUERY_MY_PGA = "select machine, round(pga_used_mem / (1024 * 1024),2) pga_used_mb"
+ " from v$session"
+ "         join v$process on v$session.paddr = v$process.addr"
+ " where sid = ("
+ "    select sid from v$mystat where rownum=1"
+ "    )"
+ " order by machine desc";




private static OracleDataSource readConfig() throws IOException, SQLException {
final Path configFile = Paths.get("config", "config.properties");
try (BufferedReader reader = Files.newBufferedReader(configFile)) {
Properties config = new Properties();
config.load(reader);


String host = config.getProperty("host");
String port = config.getProperty("port");
String service = config.getProperty("service");
String login = config.getProperty("login");
String password = config.getProperty("password");


System.out.println("Config read:"
+ " host:" + host
+ " port:" + port
+ " service:" + service
+ " login:" + login
+ " password:" + password);


final OracleDataSource oracleDataSource = new OracleDataSource();
oracleDataSource.setDriverType("thin");
oracleDataSource.setServerName(config.getProperty("host"));
oracleDataSource.setPortNumber(Integer.parseInt(config.getProperty("port")));
oracleDataSource.setServiceName(config.getProperty("service"));
oracleDataSource.setUser(config.getProperty("login"));
oracleDataSource.setPassword(config.getProperty("password"));


return oracleDataSource;
}
}
}