Re: Index Advisor: What job/program?

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