db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Have Derby Network Server having an out of memory (PermGen)
Date Fri, 23 Nov 2012 12:39:44 GMT
Here is the table function that is being used in case it might be obvious that I am doing something
wrong.   Basically there are 53 tables named NPA_RESULTS_WEEK_1, NPA_RESULTS_WEEK_2, ...,
NPA_RESULTS_WEEK_53.   There is a customer defined setting of the number of weeks of data
to retain (disk space is an issue as 2 weeks of data at one customer is about 28G).   As data
is to be inserted, the week ordinal number is computed and the correct week table is inserted
into.   At a scheduled time (usually Sunday at 2AM), a purge process is performed by truncating
the proper week table which quickly removes all of the data and reclaims the disk space.

As data is extracted for analysis, a view is used which is based on this table function. 
 The table function dynamically creates a query of the correct week tables by determining
the current week ordinal and using the customer setting of the number of weeks and a UNION
is created of just these tables and a result set is returned.

I know I have not take into consideration the table functions column name restriction in the
initScan which I will eventually get to.   What would be useful I think would be to also possibly
pass in the ORDER BY restriction and have the table function to be able to signal that it
can return an ordered result set.   This might make it possible to optimize any sorting that
might be required if the returned result set were known to be ordered.

Using the View based on the table function also affords me the ability to place a shared lock
on a table to act as a semaphore.   The purge process waits to exclusively lock this same
table before performing it TRUNCATE TABLE.   So the truncate will not happen while there is
an open result set looking at the unioned data and the table function will wait while the
truncate is being performed.   Using just a straight View does not allow me to such.

I do have a question, however,  and it is "is it more performant to have a View that is a
union of 53 tables or have a View based on a table function that dynamically creates a query?"
  I was wondering if there is some internal knowledge that might sway one way or other?

Aslo it is interesting that modifying the query to not use the View but rather just use the
table function directly, the class loaded count does not increased.   So one might ask, "why
not use the table function in the query instead of the View".    Well this is part of a larger
Java EE application that is designed in a component fashion and built and released and installed
on the customers system.   The part that has the query was built and released years ago, so
by using the View, I was able to maintain the data structure appearance as it was at that
time which was a single NPA_RESULTS table.   So the code generating the query did not have
to be rev'ed, Q/A'ed, scheduled for an install into a system that is up 24/7, etc.   On the
next release, it will be changed to used the table function directly.

Anyways, here is the table function details.

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.canoga.derby.fcn;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Map;
import org.apache.derby.vti.Restriction;
import org.apache.derby.vti.RestrictedVTI;
import org.apache.derby.vti.VTIEnvironment;

/**
 *
 * @author root
 */
public class NpaResultsTableFunction implements ResultSet, /*VTICosting,*/ RestrictedVTI {

    /**
     * The internal connection that we will use
     */
    private Connection conn;
    /**
     * The template of the statement that will be created
     */
    private static String template = "SELECT " +
            "ID, " +
            "REPORTKEY, " +
            "MASTERIP, " +
            "BOOTCOUNT, " +
            "TESTRESULTID, " +
            "PROFILEREFID, " +
            "ADDRESSREFID, " +
            "STARTDATETIME, " +
            "ACCURACYLEVEL, " +
            "RESULTFLAG, " +
            "PACKETSSENT, " +
            "ROUNDTRIPPACKETS, " +
            "DROPPEDPACKETS, " +
            "OUTOFORDERPACKETS, " +
            "MINROUNDTRIPLATENCY, " +
            "MAXROUNDTRIPLATENCY, " +
            "TOTALROUNDTRIPLATENCY, " +
            "AVGROUNDTRIPLATENCY, " +
            "LATENCYBUCKETVALUE1, " +
            "LATENCYBUCKETVALUE2, " +
            "LATENCYBUCKETVALUE3, " +
            "LATENCYBUCKETVALUE4, " +
            "LATENCYBUCKETVALUE5, " +
            "LATENCYBUCKETVALUE6, " +
            "LATENCYBUCKETVALUE7, " +
            "LATENCYBUCKETVALUE8, " +
            "LATENCYBUCKETVALUE9, " +
            "LATENCYBUCKETVALUE10, " +
            "JITTERMEASUREMENT, " +
            "MINLOCALREMOTEJITTER, " +
            "MAXLOCALREMOTEJITTER, " +
            "TOTALLOCALREMOTEJITTER, " +
            "AVGLOCALREMOTEJITTER, " +
            "LOCALREMOTEJITTERBUCKETVALUE1, " +
            "LOCALREMOTEJITTERBUCKETVALUE2, " +
            "LOCALREMOTEJITTERBUCKETVALUE3, " +
            "LOCALREMOTEJITTERBUCKETVALUE4, " +
            "LOCALREMOTEJITTERBUCKETVALUE5, " +
            "LOCALREMOTEJITTERBUCKETVALUE6, " +
            "LOCALREMOTEJITTERBUCKETVALUE7, " +
            "LOCALREMOTEJITTERBUCKETVALUE8, " +
            "LOCALREMOTEJITTERBUCKETVALUE9, " +
            "MINREMOTELOCALJITTER, " +
            "MAXREMOTELOCALJITTER, " +
            "TOTALREMOTELOCALJITTER, " +
            "AVGREMOTELOCALJITTER, " +
            "REMOTELOCALJITTERBUCKETVALUE1, " +
            "REMOTELOCALJITTERBUCKETVALUE2, " +
            "REMOTELOCALJITTERBUCKETVALUE3, " +
            "REMOTELOCALJITTERBUCKETVALUE4, " +
            "REMOTELOCALJITTERBUCKETVALUE5, " +
            "REMOTELOCALJITTERBUCKETVALUE6, " +
            "REMOTELOCALJITTERBUCKETVALUE7, " +
            "REMOTELOCALJITTERBUCKETVALUE8, " +
            "REMOTELOCALJITTERBUCKETVALUE9, " +
            "CIRCUIT1REFID, " +
            "CIRCUIT2REFID, " +
            "UNAVAILABLEEXCLUDED " +
            "FROM PCS_V1.NPARESULTS_WEEK_";
    /**
     * The statement that will be executed
     */
    private Statement stmt = null;
    /**
     * The underlying result set
     */
    private ResultSet resultSet;
    /**
     * The constraint clause
     */
    private String whereConstraint;

    /**
     * Invoked by the databae engine to read the table
     * @return An instance of this class used to read the table
     * @throws java.sql.SQLException
     */
    public static NpaResultsTableFunction instance() throws SQLException {
        return new NpaResultsTableFunction();
    }

    /**
     * Creates a new instance of this class.
     * @throws java.sql.SQLException
     */
    public NpaResultsTableFunction() throws SQLException {
        conn = DriverManager.getConnection("jdbc:default:connection");
        stmt = conn.createStatement();
    }

    private ResultSet getResultSet() throws SQLException {

        if (!isClosed()) {
            if (null == this.resultSet) {
                pcsPmLock();
                this.resultSet = stmt.executeQuery(prepareStatement());
            }

            return this.resultSet;
        } else {
            throw new SQLException("Already closed");
        }
    }

    private void pcsPmLock() throws SQLException {
        stmt.execute("LOCK TABLE PCS_V1.PCS_PM_LOCK IN SHARE MODE");
    }

    private String prepareStatement() throws SQLException {

        int keepWeeks = getKeepWeeks();
        int weekOfYear = getThisWeek();

        StringBuilder sb = new StringBuilder();

        String unionAll = "";
        while (keepWeeks > 0) {
            sb.append(unionAll);
            sb.append(template);
            sb.append(weekOfYear);
            if (null != whereConstraint) {
                sb.append(" WHERE ");
                sb.append(whereConstraint);
            }
            keepWeeks -= 1;
            if (--weekOfYear == 0) {
                weekOfYear = 53;
            }
            unionAll = " UNION ALL ";
        }

        String s = sb.toString();

        return s;
    }

    private int getKeepWeeks() throws SQLException {
        int keepWeeks = 1;
        ResultSet weeks = stmt.executeQuery("SELECT PM_PURGE_KEEP_WEEKS FROM PCS_V1.PCS_PROPERTIES");
        if (weeks.next()) {
            keepWeeks = weeks.getInt(1);
        }
        // See if we need to keep one more week. This will be the case for example
        //  where we are keeping 4 weeks: the current week and the previous 4 weeks
        //  and if the current week is the first week of the year and the previous
        //  year had 52 weeks, then we need to keep weeks 1, 53, 52, 51, and 50
        //  since week 53 of the previous year never had any data
        if (52 == getLastWeekOfYear()) {
            keepWeeks += 1;
        }
        return keepWeeks;
    }

    private int getThisWeek() {
        java.util.Date date = new java.util.Date();
        Calendar cal = GregorianCalendar.getInstance();
        cal.setMinimalDaysInFirstWeek(7);
        cal.setTime(date);
        int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);

        return weekOfYear;
    }

    private int getLastWeekOfYear() {
        java.util.Date date = new java.util.Date();
        Calendar cal = GregorianCalendar.getInstance();
        cal.setMinimalDaysInFirstWeek(7);

        cal.setTime(date);
        cal.roll(Calendar.YEAR, -1);
        cal.set(Calendar.MONTH, Calendar.DECEMBER);
        cal.set(Calendar.DAY_OF_MONTH, 31);

        int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);

        return weekOfYear;
    }

    public void initScan(String[] columNames, Restriction restriction) throws SQLException
{
        if (null != restriction) {
            String s = restriction.toSQL();
            if (null != s && 0 != s.length()) {
                whereConstraint = s;
            }
        }
    }

    public double getEstimatedRowCount(VTIEnvironment arg0) throws SQLException {
        return 1000.0;
    }

    public double getEstimatedCostPerInstantiation(VTIEnvironment arg0) throws SQLException
{
        return 1.0;
    }

    public boolean supportsMultipleInstantiations(VTIEnvironment arg0) throws SQLException
{
        return false;
    }

    @Override
    public void close() throws SQLException {
        if (!isClosed()) {
            if (null != this.resultSet) {
                this.resultSet.close();
                this.resultSet = null;
            }
            if (null != this.stmt) {
                this.stmt.close();
                this.stmt = null;
            }
                this.conn = null;
       }
    }

    @Override
    public ResultSetMetaData getMetaData() throws SQLException {
        return getResultSet().getMetaData();
    }

    @Override
    public boolean next() throws SQLException {
        return getResultSet().next();
    }

    public RowId getRowId(int columnIndex) throws SQLException {
        return getResultSet().getRowId(columnIndex);
    }

    public RowId getRowId(String columnLabel) throws SQLException {
        return getResultSet().getRowId(columnLabel);
    }

    public void updateRowId(int columnIndex, RowId x) throws SQLException {
        getResultSet().updateRowId(columnIndex, x);
    }

    public void updateRowId(String columnLabel, RowId x) throws SQLException {
        getResultSet().updateRowId(columnLabel, x);
    }

    public int getHoldability() throws SQLException {
        return getResultSet().getHoldability();
    }

    public boolean isClosed() throws SQLException {
        return null == this.conn ? true : false;
    }

    public void updateNString(int columnIndex, String nString) throws SQLException {
        getResultSet().updateNString(columnIndex, nString);
    }

    public void updateNString(String columnLabel, String nString) throws SQLException {
        getResultSet().updateNString(columnLabel, nString);
    }

    public void updateNClob(int columnIndex, NClob nClob) throws SQLException {
        getResultSet().updateNClob(columnIndex, nClob);
    }

    public void updateNClob(String columnLabel, NClob nClob) throws SQLException {
        getResultSet().updateNClob(columnLabel, nClob);
    }

    public NClob getNClob(int columnIndex) throws SQLException {
        return getResultSet().getNClob(columnIndex);
    }

    public NClob getNClob(String columnLabel) throws SQLException {
        return getResultSet().getNClob(columnLabel);
    }

    public SQLXML getSQLXML(int columnIndex) throws SQLException {
        return getResultSet().getSQLXML(columnIndex);
    }

    public SQLXML getSQLXML(String columnLabel) throws SQLException {
        return getResultSet().getSQLXML(columnLabel);
    }

    public void updateSQLXML(int columnIndex, SQLXML xmlObject) throws SQLException {
        getResultSet().updateSQLXML(columnIndex, xmlObject);
    }

    public void updateSQLXML(String columnLabel, SQLXML xmlObject) throws SQLException {
        getResultSet().updateSQLXML(columnLabel, xmlObject);
    }

    public String getNString(int columnIndex) throws SQLException {
        return getResultSet().getNString(columnIndex);
    }

    public String getNString(String columnLabel) throws SQLException {
        return getResultSet().getNString(columnLabel);
    }

    public Reader getNCharacterStream(int columnIndex) throws SQLException {
        return getResultSet().getNCharacterStream(columnIndex);
    }

    public Reader getNCharacterStream(String columnLabel) throws SQLException {
        return getResultSet().getNCharacterStream(columnLabel);
    }

    public void updateNCharacterStream(int columnIndex, Reader x, long length) throws SQLException
{
        getResultSet().updateNCharacterStream(template, x, length);
    }

    public void updateNCharacterStream(String columnLabel, Reader reader, long length) throws
SQLException {
        getResultSet().updateNCharacterStream(columnLabel, reader, length);
    }

    public void updateAsciiStream(int columnIndex, InputStream x, long length) throws SQLException
{
        getResultSet().updateAsciiStream(columnIndex, x, length);
    }

    public void updateBinaryStream(int columnIndex, InputStream x, long length) throws SQLException
{
        getResultSet().updateBinaryStream(columnIndex, x, length);
    }

    public void updateCharacterStream(int columnIndex, Reader x, long length) throws SQLException
{
        getResultSet().updateCharacterStream(columnIndex, x, length);
    }

    public void updateAsciiStream(String columnLabel, InputStream x, long length) throws SQLException
{
        getResultSet().updateAsciiStream(columnLabel, x, length);
    }

    public void updateBinaryStream(String columnLabel, InputStream x, long length) throws
SQLException {
        getResultSet().updateBinaryStream(columnLabel, x, length);
    }

    public void updateCharacterStream(String columnLabel, Reader reader, long length) throws
SQLException {
        getResultSet().updateCharacterStream(columnLabel, reader, length);
    }

    public void updateBlob(int columnIndex, InputStream inputStream, long length) throws SQLException
{
        getResultSet().updateBlob(columnIndex, inputStream, length);
    }

    public void updateBlob(String columnLabel, InputStream inputStream, long length) throws
SQLException {
        getResultSet().updateBlob(columnLabel, inputStream, length);
    }

    public void updateClob(int columnIndex, Reader reader, long length) throws SQLException
{
        getResultSet().updateClob(columnIndex, reader, length);
    }

    public void updateClob(String columnLabel, Reader reader, long length) throws SQLException
{
        getResultSet().updateClob(columnLabel, reader, length);
    }

    public void updateNClob(int columnIndex, Reader reader, long length) throws SQLException
{
        getResultSet().updateNClob(columnIndex, reader, length);
    }

    public void updateNClob(String columnLabel, Reader reader, long length) throws SQLException
{
        getResultSet().updateNClob(columnLabel, reader, length);
    }

    public void updateNCharacterStream(int columnIndex, Reader x) throws SQLException {
        getResultSet().updateNCharacterStream(columnIndex, x, columnIndex);
    }

    public void updateNCharacterStream(String columnLabel, Reader reader) throws SQLException
{
        getResultSet().updateNCharacterStream(columnLabel, reader, FETCH_FORWARD);
    }

    public void updateAsciiStream(int columnIndex, InputStream x) throws SQLException {
        getResultSet().updateAsciiStream(columnIndex, x);
    }

    public void updateBinaryStream(int columnIndex, InputStream x) throws SQLException {
        getResultSet().updateBinaryStream(columnIndex, x);
    }

    public void updateCharacterStream(int columnIndex, Reader x) throws SQLException {
        getResultSet().updateCharacterStream(columnIndex, x);
    }

    public void updateAsciiStream(String columnLabel, InputStream x) throws SQLException {
        getResultSet().updateAsciiStream(columnLabel, x);
    }

    public void updateBinaryStream(String columnLabel, InputStream x) throws SQLException
{
        getResultSet().updateBinaryStream(columnLabel, x);
    }

    public void updateCharacterStream(String columnLabel, Reader reader) throws SQLException
{
        getResultSet().updateCharacterStream(columnLabel, reader);
    }

    public void updateBlob(int columnIndex, InputStream inputStream) throws SQLException {
        getResultSet().updateBlob(columnIndex, inputStream);
    }

    public void updateBlob(String columnLabel, InputStream inputStream) throws SQLException
{
        getResultSet().updateBlob(columnLabel, inputStream);
    }

    public void updateClob(int columnIndex, Reader reader) throws SQLException {
        getResultSet().updateClob(columnIndex, reader);
    }

    public void updateClob(String columnLabel, Reader reader) throws SQLException {
        getResultSet().updateClob(columnLabel, reader);
    }

    public void updateNClob(int columnIndex, Reader reader) throws SQLException {
        getResultSet().updateNClob(columnIndex, reader);
    }

    public void updateNClob(String columnLabel, Reader reader) throws SQLException {
        getResultSet().updateNClob(columnLabel, reader);
    }

    public <T> T unwrap(Class<T> iface) throws SQLException {
        return getResultSet().unwrap(iface);
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return getResultSet().isWrapperFor(iface);
    }

    public boolean wasNull() throws SQLException {
        return getResultSet().wasNull();
    }

    public String getString(int columnIndex) throws SQLException {
        return getResultSet().getString(columnIndex);
    }

    public boolean getBoolean(int columnIndex) throws SQLException {
        return getResultSet().getBoolean(columnIndex);
    }

    public byte getByte(int columnIndex) throws SQLException {
        return getResultSet().getByte(columnIndex);
    }

    public short getShort(int columnIndex) throws SQLException {
        return getResultSet().getShort(columnIndex);
    }

    public int getInt(int columnIndex) throws SQLException {
        return getResultSet().getInt(columnIndex);
    }

    public long getLong(int columnIndex) throws SQLException {
        return getResultSet().getLong(columnIndex);
    }

    public float getFloat(int columnIndex) throws SQLException {
        return getResultSet().getFloat(columnIndex);
    }

    public double getDouble(int columnIndex) throws SQLException {
        return getResultSet().getDouble(columnIndex);
    }

    public BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException {
        return getResultSet().getBigDecimal(columnIndex);
    }

    public byte[] getBytes(int columnIndex) throws SQLException {
        return getResultSet().getBytes(columnIndex);
    }

    public Date getDate(int columnIndex) throws SQLException {
        return getResultSet().getDate(columnIndex);
    }

    public Time getTime(int columnIndex) throws SQLException {
        return getResultSet().getTime(columnIndex);
    }

    public Timestamp getTimestamp(int columnIndex) throws SQLException {
        return getResultSet().getTimestamp(columnIndex);
    }

    public InputStream getAsciiStream(int columnIndex) throws SQLException {
        return getResultSet().getAsciiStream(columnIndex);
    }

    public InputStream getUnicodeStream(int columnIndex) throws SQLException {
        return getResultSet().getUnicodeStream(columnIndex);
    }

    public InputStream getBinaryStream(int columnIndex) throws SQLException {
        return getResultSet().getBinaryStream(columnIndex);
    }

    public String getString(String columnLabel) throws SQLException {
        return getResultSet().getString(columnLabel);
    }

    public boolean getBoolean(String columnLabel) throws SQLException {
        return getResultSet().getBoolean(columnLabel);
    }

    public byte getByte(String columnLabel) throws SQLException {
        return getResultSet().getByte(columnLabel);
    }

    public short getShort(String columnLabel) throws SQLException {
        return getResultSet().getShort(columnLabel);
    }

    public int getInt(String columnLabel) throws SQLException {
        return getResultSet().getInt(columnLabel);
    }

    public long getLong(String columnLabel) throws SQLException {
        return getResultSet().getLong(columnLabel);
    }

    public float getFloat(String columnLabel) throws SQLException {
        return getResultSet().getFloat(columnLabel);
    }

    public double getDouble(String columnLabel) throws SQLException {
        return getResultSet().getDouble(columnLabel);
    }

    public BigDecimal getBigDecimal(String columnLabel, int scale) throws SQLException {
        return getResultSet().getBigDecimal(columnLabel);
    }

    public byte[] getBytes(String columnLabel) throws SQLException {
        return getResultSet().getBytes(columnLabel);
    }

    public Date getDate(String columnLabel) throws SQLException {
        return getResultSet().getDate(columnLabel);
    }

    public Time getTime(String columnLabel) throws SQLException {
        return getResultSet().getTime(columnLabel);
    }

    public Timestamp getTimestamp(String columnLabel) throws SQLException {
        return getResultSet().getTimestamp(columnLabel);
    }

    public InputStream getAsciiStream(String columnLabel) throws SQLException {
        return getResultSet().getAsciiStream(columnLabel);
    }

    public InputStream getUnicodeStream(String columnLabel) throws SQLException {
        return getResultSet().getUnicodeStream(columnLabel);
    }

    public InputStream getBinaryStream(String columnLabel) throws SQLException {
        return getResultSet().getBinaryStream(columnLabel);
    }

    public SQLWarning getWarnings() throws SQLException {
        return getResultSet().getWarnings();
    }

    public void clearWarnings() throws SQLException {
        getResultSet().clearWarnings();
    }

    public String getCursorName() throws SQLException {
        return getResultSet().getCursorName();
    }

    public Object getObject(int columnIndex) throws SQLException {
        return getResultSet().getObject(columnIndex);
    }

    public Object getObject(String columnLabel) throws SQLException {
        return getResultSet().getObject(columnLabel);
    }

    public int findColumn(String columnLabel) throws SQLException {
        return getResultSet().findColumn(columnLabel);
    }

    public Reader getCharacterStream(int columnIndex) throws SQLException {
        ;
        return getResultSet().getCharacterStream(columnIndex);
    }

    public Reader getCharacterStream(String columnLabel) throws SQLException {
        return getResultSet().getCharacterStream(columnLabel);
    }

    public BigDecimal getBigDecimal(int columnIndex) throws SQLException {
        return getResultSet().getBigDecimal(columnIndex);
    }

    public BigDecimal getBigDecimal(String columnLabel) throws SQLException {
        return getResultSet().getBigDecimal(columnLabel);
    }

    public boolean isBeforeFirst() throws SQLException {
        return getResultSet().isBeforeFirst();
    }

    public boolean isAfterLast() throws SQLException {
        return getResultSet().isAfterLast();
    }

    public boolean isFirst() throws SQLException {
        return getResultSet().isFirst();
    }

    public boolean isLast() throws SQLException {
        return getResultSet().isLast();
    }

    public void beforeFirst() throws SQLException {
        getResultSet().beforeFirst();
    }

    public void afterLast() throws SQLException {
        getResultSet().afterLast();
    }

    public boolean first() throws SQLException {
        return getResultSet().first();
    }

    public boolean last() throws SQLException {
        return getResultSet().last();
    }

    public int getRow() throws SQLException {
        return getResultSet().getRow();
    }

    public boolean absolute(int row) throws SQLException {
        return getResultSet().absolute(row);
    }

    public boolean relative(int rows) throws SQLException {
        return getResultSet().relative(rows);
    }

    public boolean previous() throws SQLException {
        return getResultSet().previous();
    }

    public void setFetchDirection(int direction) throws SQLException {
        getResultSet().setFetchDirection(direction);
    }

    public int getFetchDirection() throws SQLException {
        return getResultSet().getFetchDirection();
    }

    public void setFetchSize(int rows) throws SQLException {
        getResultSet().setFetchSize(rows);
    }

    public int getFetchSize() throws SQLException {
        return getResultSet().getFetchSize();
    }

    public int getType() throws SQLException {
        return getResultSet().getType();
    }

    public int getConcurrency() throws SQLException {
        return getResultSet().getConcurrency();
    }

    public boolean rowUpdated() throws SQLException {
        return getResultSet().rowUpdated();
    }

    public boolean rowInserted() throws SQLException {
        return getResultSet().rowInserted();
    }

    public boolean rowDeleted() throws SQLException {
        return getResultSet().rowDeleted();
    }

    public void updateNull(int columnIndex) throws SQLException {
        getResultSet().updateNull(columnIndex);
    }

    public void updateBoolean(int columnIndex, boolean x) throws SQLException {
        getResultSet().updateBoolean(columnIndex, x);
    }

    public void updateByte(int columnIndex, byte x) throws SQLException {
        getResultSet().updateByte(columnIndex, x);
    }

    public void updateShort(int columnIndex, short x) throws SQLException {
        getResultSet().updateShort(columnIndex, x);
    }

    public void updateInt(int columnIndex, int x) throws SQLException {
        getResultSet().updateInt(columnIndex, x);
    }

    public void updateLong(int columnIndex, long x) throws SQLException {
        getResultSet().updateLong(columnIndex, x);
    }

    public void updateFloat(int columnIndex, float x) throws SQLException {
        getResultSet().updateFloat(columnIndex, x);
    }

    public void updateDouble(int columnIndex, double x) throws SQLException {
        getResultSet().updateDouble(columnIndex, x);
    }

    public void updateBigDecimal(int columnIndex, BigDecimal x) throws SQLException {
        getResultSet().updateBigDecimal(columnIndex, x);
    }

    public void updateString(int columnIndex, String x) throws SQLException {
        getResultSet().updateString(columnIndex, x);
    }

    public void updateBytes(int columnIndex, byte[] x) throws SQLException {
        getResultSet().updateBytes(columnIndex, x);
    }

    public void updateDate(int columnIndex, Date x) throws SQLException {
        getResultSet().updateDate(columnIndex, x);
    }

    public void updateTime(int columnIndex, Time x) throws SQLException {
        getResultSet().updateTime(columnIndex, x);
    }

    public void updateTimestamp(int columnIndex, Timestamp x) throws SQLException {
        getResultSet().updateTimestamp(columnIndex, x);
    }

    public void updateAsciiStream(int columnIndex, InputStream x, int length) throws SQLException
{
        getResultSet().updateAsciiStream(columnIndex, x);
    }

    public void updateBinaryStream(int columnIndex, InputStream x, int length) throws SQLException
{
        getResultSet().updateBinaryStream(columnIndex, x);
    }

    public void updateCharacterStream(int columnIndex, Reader x, int length) throws SQLException
{
        getResultSet().updateCharacterStream(columnIndex, x);
    }

    public void updateObject(int columnIndex, Object x, int scaleOrLength) throws SQLException
{
        getResultSet().updateObject(columnIndex, x);
    }

    public void updateObject(int columnIndex, Object x) throws SQLException {
        getResultSet().updateObject(columnIndex, x);
    }

    public void updateNull(String columnLabel) throws SQLException {
        getResultSet().updateNull(columnLabel);
    }

    public void updateBoolean(String columnLabel, boolean x) throws SQLException {
        getResultSet().updateBoolean(columnLabel, x);
    }

    public void updateByte(String columnLabel, byte x) throws SQLException {
        getResultSet().updateByte(columnLabel, x);
    }

    public void updateShort(String columnLabel, short x) throws SQLException {
        getResultSet().updateShort(columnLabel, x);
    }

    public void updateInt(String columnLabel, int x) throws SQLException {
        getResultSet().updateInt(columnLabel, x);
    }

    public void updateLong(String columnLabel, long x) throws SQLException {
        getResultSet().updateLong(columnLabel, x);
    }

    public void updateFloat(String columnLabel, float x) throws SQLException {
        getResultSet().updateFloat(columnLabel, x);
    }

    public void updateDouble(String columnLabel, double x) throws SQLException {
        getResultSet().updateDouble(columnLabel, x);
    }

    public void updateBigDecimal(String columnLabel, BigDecimal x) throws SQLException {
        getResultSet().updateBigDecimal(columnLabel, x);
    }

    public void updateString(String columnLabel, String x) throws SQLException {
        getResultSet().updateString(columnLabel, x);
    }

    public void updateBytes(String columnLabel, byte[] x) throws SQLException {
        getResultSet().updateBytes(columnLabel, x);
    }

    public void updateDate(String columnLabel, Date x) throws SQLException {
        getResultSet().updateDate(columnLabel, x);
    }

    public void updateTime(String columnLabel, Time x) throws SQLException {
        getResultSet().updateTime(columnLabel, x);
    }

    public void updateTimestamp(String columnLabel, Timestamp x) throws SQLException {
        getResultSet().updateTimestamp(columnLabel, x);
    }

    public void updateAsciiStream(String columnLabel, InputStream x, int length) throws SQLException
{
        getResultSet().updateAsciiStream(columnLabel, x);
    }

    public void updateBinaryStream(String columnLabel, InputStream x, int length) throws SQLException
{
        getResultSet().updateBinaryStream(columnLabel, x);
    }

    public void updateCharacterStream(String columnLabel, Reader reader, int length) throws
SQLException {
        getResultSet().updateCharacterStream(columnLabel, reader, length);
    }

    public void updateObject(String columnLabel, Object x, int scaleOrLength) throws SQLException
{
        getResultSet().updateObject(columnLabel, x, scaleOrLength);
    }

    public void updateObject(String columnLabel, Object x) throws SQLException {
        getResultSet().updateObject(columnLabel, x);
    }

    public void insertRow() throws SQLException {
        getResultSet().insertRow();
    }

    public void updateRow() throws SQLException {
        getResultSet().updateRow();
    }

    public void deleteRow() throws SQLException {
        getResultSet().deleteRow();
    }

    public void refreshRow() throws SQLException {
        getResultSet().refreshRow();
    }

    public void cancelRowUpdates() throws SQLException {
        getResultSet().cancelRowUpdates();
    }

    public void moveToInsertRow() throws SQLException {
        getResultSet().moveToInsertRow();
    }

    public void moveToCurrentRow() throws SQLException {
        getResultSet().moveToCurrentRow();
    }

    public Statement getStatement() throws SQLException {
        return getResultSet().getStatement();
    }

    public Object getObject(int columnIndex, Map<String, Class<?>> map) throws
SQLException {
        return getResultSet().getObject(columnIndex, map);
    }

    public Ref getRef(int columnIndex) throws SQLException {
        return getResultSet().getRef(columnIndex);
    }

    public Blob getBlob(int columnIndex) throws SQLException {
        return getResultSet().getBlob(columnIndex);
    }

    public Clob getClob(int columnIndex) throws SQLException {
        return getResultSet().getClob(columnIndex);
    }

    public Array getArray(int columnIndex) throws SQLException {
        return getResultSet().getArray(columnIndex);
    }

    public Object getObject(String columnLabel, Map<String, Class<?>> map) throws
SQLException {
        return getResultSet().getObject(columnLabel, map);
    }

    public Ref getRef(String columnLabel) throws SQLException {
        return getResultSet().getRef(columnLabel);
    }

    public Blob getBlob(String columnLabel) throws SQLException {
        return getResultSet().getBlob(columnLabel);
    }

    public Clob getClob(String columnLabel) throws SQLException {
        return getResultSet().getClob(columnLabel);
    }

    public Array getArray(String columnLabel) throws SQLException {
        return getResultSet().getArray(columnLabel);
    }

    public Date getDate(int columnIndex, Calendar cal) throws SQLException {
        return getResultSet().getDate(columnIndex, cal);
    }

    public Date getDate(String columnLabel, Calendar cal) throws SQLException {
        return getResultSet().getDate(columnLabel, cal);
    }

    public Time getTime(int columnIndex, Calendar cal) throws SQLException {
        return getResultSet().getTime(columnIndex, cal);
    }

    public Time getTime(String columnLabel, Calendar cal) throws SQLException {
        return getResultSet().getTime(columnLabel, cal);
    }

    public Timestamp getTimestamp(int columnIndex, Calendar cal) throws SQLException {
        return getResultSet().getTimestamp(columnIndex, cal);
    }

    public Timestamp getTimestamp(String columnLabel, Calendar cal) throws SQLException {
        return getResultSet().getTimestamp(columnLabel, cal);
    }

    public URL getURL(int columnIndex) throws SQLException {
        return getResultSet().getURL(columnIndex);
    }

    public URL getURL(String columnLabel) throws SQLException {
        return getResultSet().getURL(columnLabel);
    }

    public void updateRef(int columnIndex, Ref x) throws SQLException {
        getResultSet().updateRef(columnIndex, x);
    }

    public void updateRef(String columnLabel, Ref x) throws SQLException {
        getResultSet().updateRef(columnLabel, x);
    }

    public void updateBlob(int columnIndex, Blob x) throws SQLException {
        getResultSet().updateBlob(columnIndex, x);
    }

    public void updateBlob(String columnLabel, Blob x) throws SQLException {
        getResultSet().updateBlob(columnLabel, x);
    }

    public void updateClob(int columnIndex, Clob x) throws SQLException {
        getResultSet().updateClob(columnIndex, x);
    }

    public void updateClob(String columnLabel, Clob x) throws SQLException {
        getResultSet().updateClob(columnLabel, x);
    }

    public void updateArray(int columnIndex, Array x) throws SQLException {
        getResultSet().updateArray(columnIndex, x);
    }

    public void updateArray(String columnLabel, Array x) throws SQLException {
        getResultSet().updateArray(columnLabel, x);
    }
}

________________________________________
From: Knut Anders Hatlen [knut.hatlen@oracle.com]
Sent: Thursday, November 22, 2012 4:57 AM
To: derby-dev@db.apache.org
Subject: Re: Have Derby Network Server having an out of memory (PermGen)

Mike Matrigali <mikem_app@sbcglobal.net> writes:

> On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote:
>> "Bergquist, Brett" <BBergquist@canoga.com> writes:
>>
>>> Yes, the statement cache size has been increased to 50K statements so
>>> that might be an issue. Maybe the PermGen space will need to be
>>> increased because of that. The documentation is not clear which type
> I am not an expert in this area, is there any case where we expect the
> re-execution of the same query to need to generate a different entry
> in the statement cache?

I think what's flooding the statement cache here is whatever gets
executed by the table function, which I understand is some dynamically
generated SQL statements.

This is also why I don't understand how changing from a view to a direct
table function call should change anything, as the top-level statement
should only have one entry in the cache, and the statements executed
inside the table function should be the same.

Two possible explanations:

1) Changing between view and direct call changes the plan picked by the
optimizer, so that the table function call one time ends up as the inner
table in a join, and another time as the outer table. This could change
the number of times the table function is called per query. If each call
to the table function generates truly unique SQL statements, calling it
more often will fill the cache quicker.

2) If it is a restricted table function, the actual
restriction/projection pushed down to the table function may vary
depending on which plan the optimizer picks. And this could affect what
kind of SQL is generated by the table function. Perhaps sometimes it
generates statements that are likely to be identical across invocations,
needing fewer entries in the cache, and other times it generates
statements that are less likely to be identical.

Following up on that last thought, if the queries generated by the table
function would be something like

  select * from t where x < N

where N varies between invocations, it's better for the statement cache
if a parameter marker is used, like

  select * from t where x < ?

rather than inlining the actual constant

  select * from t where x < 5
  select * from t where x < 42
  ...

Even though the table function itself doesn't execute the query more
than once, using parameter markers increases the likelihood of finding a
match in the statement cache.

Not sure if this affects Brett's table function. Just throwing out
ideas...

--
Knut Anders


Mime
View raw message