db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Have Derby Network Server having an out of memory (PermGen)
Date Thu, 22 Nov 2012 09:57:55 GMT
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

Knut Anders

View raw message