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.
midrange.com
