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;
}
}
}