Re: AW: AW: Index advisor and system cross-reference file

From my time working in a Japanese company...I have a habit of "asking why

Satya,

I help develop and support a software product. We often have support
requests to have our product take Action A. We then ask why Action A is
being requested. It then comes out that Action A is to ameliorate or fix
the results of Symptom B. Once we get to this point we can start thinking
about whether Action A is the best way to deal with Symptom B, or whether
another action, Action C may be better, which stops Symptom B from arising
entirely.

In your case:
Action A is to "stop the Index advisor";
Symptom B is "that the index advisor is using up resources (CPU and disk)
advising indexes over system tables";
Action C (from the rest of the list) is "to get your applications from to
stop querying the system tables so much", so that the index advisor will
stop performing Symptom B, and the questions hence are aimed at getting to
that.

In my opinion, Action C will have a double effect: less resources used by
the application; and less resources used by the Index advisor.

If Action C is off the table, I believe that CRPence has advised (!) how
to turn off the Index advisor. However this also means that genuine index
advice over application tables will not be generated.

Regards,

Kevin Wright
Developer, LPC

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
satya tiruveedhi
Sent: Friday, 4 September 2015 11:07 AM
To: midrange-l <midrange-l@xxxxxxxxxxxx>
Subject: Re: AW: AW: Index advisor and system cross-reference file

Although you say here that you are not coming to the list to get a

solution.

I did come to the list to get a solution. The solution I was looking for
is how to turn off Index Advisor. I did not come to the list for fixes to
the application.

just how much CPU time did it really take?

I do not remember exact time that got reported for update sysixadv SQL. It
is definitely more than 10 minutes for the day. The SQL with lowest amount
of CPU time for the day on Top 20 SQL daily report on our system on busy
days is around 10 minutes.


People on the thread are viewing the issue I raised as problem with
application and providing responses accordingly. From my point of view the
problem is with Index Advisor. Last week, I was booking hotel for my
upcoming vacation (starting tomorrow :) ) and while making the booking, I
wondered how iseries will work for hotel booking website.

On hotel booking site, customer can search based on location and dates
+ several other factors ( price, start rating, user rating, discount,
etc..) and then ask list to be sorted by another criteria. What will be
output of Index Advisor for this situation? It will be suggesting hundreds
of indexes, each index being advised thousands of times in a day. Looking
at above scenario, where do you guys think the problem is? Is it with hotel
booking application or with the way Index Advisor works?

Can someone run following SQL on their system and check whether it results
in index advise? It will answer the question I raised in the
first post of this thread. FYI, index advises for QSYS tables will
not show up in "Index Advisor" report in iseries navigator. These advises
can be seen only by querying sysixadv.


"SELECT * FROM QSYS2.SYSTABLES WHERE TABLE_NAME = 'SYSTABLES' AND
TABLE_SCHEMA = 'QSYS2'"

Thanks..

Satya..
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.