5 Replies Latest reply on Jun 24, 2019 7:10 AM by Michal W

    PGA leak when repeatedly querying V$SQL

    Michal W

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