Re: Index advisor and system cross-reference files

My concern is about CPU usage by Index advisor. I am not concerned about
disk space used by Index advisor. The update statement on sysixadv (by
Index advisor) shows up once in a while in top 20 SQLs by total CPU usage.
Index advisor might be spending lot more CPU on analysis compared to update
statement on sysixadv. It might save significant amount of CPU by turning
off Index advisor.

I sympathize, in that I like to run as optimized a system as possible as well. I think you will find that it is very difficult to do away with the Index Advisor because it is the least cumbersome part of the system's attempt to optimize SQL statements. When you run either a SELECT or an UPDATE, the database is going to attempt to optimize the execution of the statement. Writing a note that an index would be handy is far less overhead than the determination of how the SQL statement will run underneath the covers. If you can turn off that index advisor creation (something I doubt, but it is available on other platforms, so it may be possible), the underlying engine is still going to look for indexes and evaluate execution options before running the statement. This would be analogous to turning off job log creation on a job: it will save time, but probably not be much help in getting the job to run faster.

Now the more knowledgeable members of the list, who actually understand the workings of the query engines, can take me to task for either a bad analogy, bad understanding, or both. :-)





_____________________________________________________________________
Spirax-Sarco Engineering Plc. This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk