Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PGA leak when repeatedly querying V$SQL

Michal WJun 3 2019 — edited Jun 24 2019

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;

}

}

}

This post has been answered by Zlatko Sirotic on Jun 3 2019
Jump to Answer

Comments

Processing

Post Details

Added on Jun 3 2019
5 comments
2,456 views