: We are experiencing a spike in the qsf pool that utilises all allocated space.
: I've been monitoring the qsf pool usage and the following has happened:
:
: 1) usage sitting around the 15% mark
: 2) one minute later, qsf pool is full.
: 3) qsf pool has purged objects and is back at around 2% utilization.
:
: Does anyone know of a method of tracking what is causing this spike??
: Does anyone have an idea what it maybe??
First of all, if your QSF pool is still at the default size, I wouldn't
spend a whole lot of time trying to figure out what's going on, I'd
just double it on spec.
If you have already increased its size, and you are still having
problems, then the first thing I would suspect is a proliferation of
terms when a WHERE clause is converted to disjunctive-normal (sum of
products) form for the convenience of the optimizer. An ugly
WHERE clause can explode into a real monster.
You can get around this by (1) increasing the size of the QSF pool
and just living with it (provided the resulting expression doesn't go
over the 128 term limit in 6.4 as well), or (2) break the statement up
into multiple statements. For instance, if you have something that
looks like:
SELECT ... WHERE (A and not(B or C)) or (((A and B) or not(C)) and A)
change it to
SELECT ... WHERE (A and not(B or C))
UNION
SELECT ... WHERE (((A and B) or not(C)) and A)
Of course if this query-from-hell is being generated by some
gorgeous-looking GUI ad hoc query tool, you are kind of stuck. Make
the pool bigger.
--Roy Hann
by