Question on Package Cache Overflows

[datapro01]

Running DB2 8.1.6A on AIX 5.1

We are experience package cache overflows.

The high water mark for package cache is showing as 16,108,513 bytes,
or
approximately 3933 4K pages.

The package cache size is set at Maxapples * 8
Maxapples is at 500.

Makes the package cache size 4000 (4K pages).

We are not clear on how we can be experiencing overflows when the high
water mark is showing as less than the cfg parameter.

Is it that we are not accounting for overhead on the page?

Thanks in advance.
Gerry

[Pierre Saint-Jacques]

Would it not be because the last insert into package cache requested more
than 67 (4000-3933)pages of cache that it would cause it to signal an
overflow.
It would have to "push" something large enough to make room for the request.
I believe that would cause an overflow to be signalled.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<da*******@yahoo.com> a écrit dans le message de news:
11**********************@f14g2000cwb.googlegroups. com...

Running DB2 8.1.6A on AIX 5.1

We are experience package cache overflows.

The high water mark for package cache is showing as 16,108,513 bytes,
or
approximately 3933 4K pages.

The package cache size is set at Maxapples * 8
Maxapples is at 500.

Makes the package cache size 4000 (4K pages).

We are not clear on how we can be experiencing overflows when the high
water mark is showing as less than the cfg parameter.

Is it that we are not accounting for overhead on the page?

Thanks in advance.
Gerry

[Serge Rielau]

Pierre Saint-Jacques wrote:

Would it not be because the last insert into package cache requested
more than 67 (4000-3933)pages of cache that it would cause it to signal
an overflow.
It would have to "push" something large enough to make room for the
request.
I believe that would cause an overflow to be signalled.
HTH, Pierre.

I'm not privy to the details of the implementation of the cache, but it
may also be that there isn't sufficient contigeous space available
(fragmentation).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Pierre Saint-Jacques wrote:

Would it not be because the last insert into package cache requested
more than 67 (4000-3933)pages of cache that it would cause it to
signal an overflow.
It would have to "push" something large enough to make room for the
request.
I believe that would cause an overflow to be signalled.
HTH, Pierre.

I'm not privy to the details of the implementation of the cache, but it
may also be that there isn't sufficient contigeous space available
(fragmentation).

Aside.. Do you use SQL Procedures? If so I recommend being generous with
the cache.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3j************@individual.net...

I'm not privy to the details of the implementation of the cache, but it
may also be that there isn't sufficient contigeous space available
(fragmentation).

Aside.. Do you use SQL Procedures? If so I recommend being generous with
the cache.

Cheers
Serge

--
Serge Rielau

Is it better to define a fixed cache, or to use the default formula based on
connections?

[Serge Rielau]

Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3j************@individual.net...

I'm not privy to the details of the implementation of the cache, but it
may also be that there isn't sufficient contigeous space available
(fragmentation).

Aside.. Do you use SQL Procedures? If so I recommend being generous with
the cache.

Cheers
Serge

--
Serge Rielau

Is it better to define a fixed cache, or to use the default formula based on
connections?

Starting with the default is a good start for a generic use.
But especially in those "no SQL in the App other than CALL" type of apps
where the you have thousands of procs, many of which only containing
only an open cursor or a single UPDATE/DELETE/INSERT statement you
essentially have doubled the number of packages (1 dynamic CALL + 1
procedure) and thrashing packages can become the dominant factor.
Just yesterday I learned from an ISV porting to DB2 where performance
improved 10 fold(!) just by fixing the package cache.
Needless to say they are a lot happier with DB2 now ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[datapro01]

I certainly appreciate all the responses. However I am still wondering
why the 'high water mark' (less than 4K) did not reflect a value that
was more than the set parameter (4K) if there were overflows.

[Matt Emmerton]

<da*******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I certainly appreciate all the responses. However I am still wondering
why the 'high water mark' (less than 4K) did not reflect a value that
was more than the set parameter (4K) if there were overflows.

The high water mark will ALWAYS be less than the size of the cache. This is
because the package cache cannot grow to be larger than it's configured
size -- it's a hard limit.

An overflow occurs when a new package needs to be entered in the package
cache and there is not enough contingous free space for it. hence the cache
is deemed to have "overflowed" and something is removed to make room for the
new package.

In your case, seeing a large number of package cache overflows and a
high-water-mark that is very close to the configured size of the package
cache simply means that you need to make your cache larger -- if you make it
large enough, overflows will cease to happen.

If you increase your package cache too large (say you increase it to 16K
pages) and the high-water mark after running for a while is only around 5K
pages, then you can easily see that you have too much package cache and can
reduce it to a "safe" value such as 6K or 8K pages.

The optimal configuration for the package cache is one where the
high-water-mark is very close to the configured size, but with NO overflows.

--
Matt Emmerton

[Pierre Saint-Jacques]

Matt, I thought that since V8 if the package cache is full or close to it,
DB2 would "steal" the required pages to insert the package (still generating
an overflow signal) but also pushing the cache HWM to greater than defined
in the cfg.
In V8.1, the stealing was allowed if db cfg parm. database_memory was not
reached, then stealing happened.
I haven't read all of the intrcacies of V8.2, but I seem to remeber that
V8.2 now "allocates" some memoey buffer from which it can dynamically expand
caches of shared memory, on demand.
Imay have misread stuff but I do clearly rember seeing a HWM higher than
max. defined for both catalog and package cache.
Regards, Pierre.
PS: Overflows will still get signaled anyway.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Matt Emmerton" <me******@nospam.yahoo.com> a écrit dans le message de news:
TM********************@rogers.com...

<da*******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I certainly appreciate all the responses. However I am still wondering
why the 'high water mark' (less than 4K) did not reflect a value that
was more than the set parameter (4K) if there were overflows.

The high water mark will ALWAYS be less than the size of the cache. This
is
because the package cache cannot grow to be larger than it's configured
size -- it's a hard limit.

An overflow occurs when a new package needs to be entered in the package
cache and there is not enough contingous free space for it. hence the
cache
is deemed to have "overflowed" and something is removed to make room for
the
new package.

In your case, seeing a large number of package cache overflows and a
high-water-mark that is very close to the configured size of the package
cache simply means that you need to make your cache larger -- if you make
it
large enough, overflows will cease to happen.

If you increase your package cache too large (say you increase it to 16K
pages) and the high-water mark after running for a while is only around 5K
pages, then you can easily see that you have too much package cache and
can
reduce it to a "safe" value such as 6K or 8K pages.

The optimal configuration for the package cache is one where the
high-water-mark is very close to the configured size, but with NO
overflows.

--
Matt Emmerton

[Matt Emmerton]

Pierre,

What you say is possible -- but it is obviously not happening since the HWM
in this case is always less than the configured size.

Matt

"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:GL********************@wagner.videotron.net.. .

Matt, I thought that since V8 if the package cache is full or close to it,
DB2 would "steal" the required pages to insert the package (still generating an overflow signal) but also pushing the cache HWM to greater than defined
in the cfg.
In V8.1, the stealing was allowed if db cfg parm. database_memory was not
reached, then stealing happened.
I haven't read all of the intrcacies of V8.2, but I seem to remeber that
V8.2 now "allocates" some memoey buffer from which it can dynamically expand caches of shared memory, on demand.
Imay have misread stuff but I do clearly rember seeing a HWM higher than
max. defined for both catalog and package cache.
Regards, Pierre.
PS: Overflows will still get signaled anyway.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Matt Emmerton" <me******@nospam.yahoo.com> a écrit dans le message de news: TM********************@rogers.com...

<da*******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I certainly appreciate all the responses. However I am still wondering
why the 'high water mark' (less than 4K) did not reflect a value that
was more than the set parameter (4K) if there were overflows.

The high water mark will ALWAYS be less than the size of the cache. This is
because the package cache cannot grow to be larger than it's configured
size -- it's a hard limit.

An overflow occurs when a new package needs to be entered in the package
cache and there is not enough contingous free space for it. hence the
cache
is deemed to have "overflowed" and something is removed to make room for
the
new package.

In your case, seeing a large number of package cache overflows and a
high-water-mark that is very close to the configured size of the package
cache simply means that you need to make your cache larger -- if you make it
large enough, overflows will cease to happen.

If you increase your package cache too large (say you increase it to 16K
pages) and the high-water mark after running for a while is only around 5K pages, then you can easily see that you have too much package cache and
can
reduce it to a "safe" value such as 6K or 8K pages.

The optimal configuration for the package cache is one where the
high-water-mark is very close to the configured size, but with NO
overflows.

--
Matt Emmerton

[datapro01]

Again, many thanks.