Can an increase in stmtheap db cfg value cause the package cache to overflow (adm4500w)? Both stmtheap and pckcachesz are not automatic.

If only indirectly. Larger statement heap will allow you to process longer queries, for example, which will take up more space in the statement cache when compiled.

This was my initial thought too. Larger statement heap will allow to process more complex statements, but I'm not sure why they would take up more space in the package cache. This is assuming the workload / statement text hasn't changed (and it hasn't according to the customer). Also, SQL is not complex.

n_i, You were right.

Response from the expert:
"It's possible that the more complex queries result in more complex access plans which take up more space in the package cache, but I wouldn't generally tie a larger stmtheap with a requirement to increase the package cache."


But if SQL is not complex, why would the access plan change and the statement text is exactly the same as per the customer.

I suspect the optimizer may have a different opinion than you (or the client) as to what constitutes a complex statement.

The SQL I saw (only several statements) looked pretty simple (to me). Also, the manual states:

"Statement Heap Size (stmtheap)
Although the size of the statement heap does not influence the optimizer in choosing different access paths, it can affect the amount of optimization performed for complex SQL or XQuery statements."

The way I read it, the value of STMTHEAP by itself does not influence the optimizer decision (unlike SORTHEAP, for example), however, additional memory will allow it to consider more optimization options, which indeed can result in a different plan.

It does make sense.

They have a prod mirror env where explains were performed on some SQL before/after the statement heap change and there was no difference in the plans.


After increasing the statement heap, they started getting package cache overflows as well as high CPU and lock-waits. No diagnostic info was collected prior to reverting the changes back.


My theory about lock-waits. Please let me know what you think:
increase in stmtheap -> increase in amount of optimization -> increase in compile time -> increase in V (variation) lock duration => lock-waits on V (exclusive) lock.

This variation lock is held in exclusive mode when the statement is compiled / loaded into the cache. So, other applications that need to execute exactly the same statement will have to wait until it's compiled / loaded.

The lock-waits could have been on user / catalog tables so V-lock waits is just my theory.

Somehow it does not add up.

Bella, if you still have those explains. check the stats before and after. I wonder if they were changed.

I got an update this morning that the problem (high cpu / lock-waits / package cache overflow / etc...) happened again with no change to the statement heap (change was undone last week). So, an increase in stmtheap doesn't seem to related to this perf / cache overflows issue.

I also checked with our optimizer expert (previously it was a package cache expert) and he doesn't think pckcachsz needs to be increased when increasing stmtheap. DB2 can generate a more optimal plan, but it should not be more complex (if previously db2 had to drop down to greedy join enumeration due to hitting statement heap limit).

Could be related to:
IBM IC68890: PERFORMANCE DEGRADATION AFTER THE UPGRADE TO DB2 V97 FROM EARLIER DB2 VERSIONS WHEN STMT_CONC IS ON. - United States

hi bella,

optimization of statement also depends on the level of optimization wht we set, or as u said increase of the stmtheap results in more optimization of the query ??
redgs
Paul