Rob,
1)
Isn't "Estimated Index Creation Time" what you are looking for? F12 to
add more columns to report view.
"Average of Query Estimates" : Average execution time in seconds of all
queries that generated this index advice. As defined by the help.
Press F11 to filter results by a very small number reported columns.
Then press refresh.
Also, don't forget that you can drill down to highlight a table's advice
indexes by right clicking -> table -> Index Advisor.
2)
I have only been able to capture the program running the SQL using a DB
monitor.
Or if the advised index was very recent you might be able to capture the
information using the SQL Plan Cache Snapshots.
HTH,
Thanks, Matt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, April 04, 2012 8:11 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Index Advisor: What job/program?
I was reading Paul Tuohy's article at
http://www.itjungle.com/fhg/fhg040412-story01.html and I had a couple
of
questions:
1) Isn't there some way to also sort it by how expensive each index
request was? For example, sure it may need the index several hundred
times but if it only takes .00000001 nanoseconds to build the index is
that any more expensive than 75 requests that take 5 minutes to build
the
temporary index (or whatever it does)? Would that be "Average of Query
Estimates"?
2) Is there some way to see what job/program is running the sql
statement? 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.
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
Rob Berendt
midrange.com
