On 04-Apr-2012 07:10 , rob@xxxxxxxxx wrote:
<<SNIP>> I see this sql statement:
UPDATE QTEMP / WHS SET ONHAND = : H WHERE WPROD = : H ;
and this kind of usage:
Times advised for Query Use: 106,195,819
estimated index creation time: 00:00:01
Rows in table when advised: 42,299
Since it's in QTEMP obviously building the index ahead of time is
out.
That really depends on how the file gets created into QTEMP.
Whatever created the file WHS in QTEMP could [also] create an INDEX, or
create the database physical file with a /physical/ access path using a
non-SQL interface such as CPYF CRTFILE(*YES) or CRTDUPOBJ to create the
file in QTEMP [non-SQL, because constraint keys that are associated with
the TABLE, unlike those created for an INDEX, are not allowed in QTEMP].
If the given UPDATE and similar statements are repeated in the job
effecting a temporary index being created by the DB, probably best to
create the index for the data rather than allowing the DB doing so;
possibly twice, where the first is discarded with a full close, the
second time maintained under a pseudo-close. Clearly the above quoted
text shows the index was being advised, but was that index ever created
as a temporary and then actually used to implement [that or later
repeated invocations]?
I forget where the information is [the statement viewed with visual
explain perhaps?], but if the DB created a temporary index then there
will be MTI [Maintained Temporary Index] tracked. Hmmm... a quick look
shows some MTI effect is available in the index advisor details.
However, thinking further, I am not sure what caveats or restrictions
might exist for QTEMP with regard to that; i.e. if the index is only
ever advised once within the job or even just because QTEMP is specific
to just the one job, the DB might never log such a temporary index as
"maintained" [since the term has most relevance for
perceived-as-permanent data; data for which access is possible across
many jobs].
Knowing what program to modify to do so would be beneficial. If
it's not vendor written I might even come up with better logic.
DB Monitor and SQL Plan Cache data as Matt suggested are probably
best. Though probably less efficient and less effective: querying
routines, PRTSQLINF, or some other source\object
cross-referencing\where-used feature, could be used to help track down
references to a file name WHS [in QTEMP].
Seems an odd DML request against a temporary copy of the data.?
Perhaps created both with a key\INDEX and as VOLATILE to further
encourage the use of that access path, that might eliminate the
advisement and effect the best possible performance. But again,
updating an On-Hand value for temporary data instead of the performing
an update of production\active data? For that reason\question alone, I
would want to investigate the source of that processing for the
concept\logic, irrespective of any index being advised.
Regards, Chuck
midrange.com
