Re: Identify Optimizer issues
mohitanchlia@gmail.com wrote: > Version: IDS 10 > > Recently I saw some inconsistencies in optimizer taking incorrect > index path. This Bug has been reported. Now I am attempting to do the > following: > > 1. Write perl script to get all the "Select" from the code. This seems > to be easy. > 2. Execute the select statement with explain on. > 3. Parse the results in sqexplain file to determine if it took correct > index path. Basically, for large tables it should take index path if > head of the index is part of the where clause. Is there a good way of > doing this ? Has anybody done this before ? I am trying to proactively > identify those queries that has problems, so that we can use Index > directives, if required, while bug is being fixed. I've seen Art's august discussions, but you cycled around to reiterating your quesstion, so I'll start answering the original question again... Step 1 is easy until and unless you start to deal with dynamically constructed queries - for example, where the user specifies search criteria to you, as in the CONSTRUCT statement in I4GL. Then you cannot predict what the SQL will look like. (In the days of yore, I wrote code that analyzed the output from CONSTRUCT and included more or fewer tables in the FROM clause and join conditions in the WHERE clause depending on the search criteria specified by the user.) Also, depending on the language you are looking at, it may be more or less easy to detect the end of the SQL statement. Finally, if your applications generate temp tables for intermediate results, then reproducing the contents of those accurately can be hard. (Note that you can run UPDATE STATISTICS on a temp table, and it can be a good idea to do so - though the latest versions of IDS do so automatically, speaking a little loosely.) Given the validity of Step 1 and the generated list of SQL, Step 2 is doable. Step 3 is the hard part - how do you determine whether it took the correct index path? Presumably, you'll need to analyze the tables in each query (which itself may not be trivial), then determine the available indexes, and decide which of those indexes gives the biggest bang for the buck. If you're not careful, you end up with a non-negligible portion of an SQL optimizer! So, it 'can' be done: IDS does it all the time. It is non-trivial. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-2182 haval 2008-01-03 06:00:04 C70CF9C9C126042A89BA3C218E19F609
Jonathan Leffler wrote: > mohitanchlia@gmail.com wrote: >> Version: IDS 10 >> >> Recently I saw some inconsistencies in optimizer taking incorrect >> index path. This Bug has been reported. Now I am attempting to do the >> following: >> >> 1. Write perl script to get all the "Select" from the code. This seems >> to be easy. >> 2. Execute the select statement with explain on. >> 3. Parse the results in sqexplain file to determine if it took correct >> index path. Basically, for large tables it should take index path if >> head of the index is part of the where clause. Is there a good way of >> doing this ? Has anybody done this before ? I am trying to proactively >> identify those queries that has problems, so that we can use Index >> directives, if required, while bug is being fixed. > > I've seen Art's august discussions, but you cycled around to reiterating > your quesstion, so I'll start answering the original question again... > <snip> > > So, it 'can' be done: IDS does it all the time. It is non-trivial. > I have pondered about this, and ... What are actually trying to achieve? To write an "optimiser verifier" is quite a big task. And is that really what you want to do? What percentage of your queries are giving you problems? If you are trying to find queries which "match" the defect / bug you have found and reported, then that may be achievable in a straight forward logic way; but give us a clue - what was the defect, and what specific version of 10? How does it manifest itself, what are its characteristics?
On Jan 4, 2:11 am, TBP <TheBigPot...@NotHere.Co.Uk> wrote: > Jonathan Leffler wrote: > > mohitanch...@gmail.com wrote: > >> Version: IDS 10 > > >> Recently I saw some inconsistencies in optimizer taking incorrect > >> index path. This Bug has been reported. Now I am attempting to do the > >> following: > > >> 1. Write perl script to get all the "Select" from the code. This seems > >> to be easy. > >> 2. Execute the select statement with explain on. > >> 3. Parse the results in sqexplain file to determine if it took correct > >> index path. Basically, for large tables it should take index path if > >> head of the index is part of the where clause. Is there a good way of > >> doing this ? Has anybody done this before ? I am trying to proactively > >> identify those queries that has problems, so that we can use Index > >> directives, if required, while bug is being fixed. > > > I've seen Art's august discussions, but you cycled around to reiterating > > your quesstion, so I'll start answering the original question again... > > <snip> > > > So, it 'can' be done: IDS does it all the time. It is non-trivial. > > I have pondered about this, and ... > > What are actually trying to achieve? > > To write an "optimiser verifier" is quite a big task. And is that really > what you want to do? What percentage of your queries are giving you > problems? If you are trying to find queries which "match" the defect / > bug you have found and reported, then that may be achievable in a > straight forward logic way; but give us a clue - what was the defect, > and what specific version of 10? How does it manifest itself, what are > its characteristics?- Hide quoted text - > > - Show quoted text - All I want to know is if a column is a head of the index "IX_A" and is used in where clause then optimizer is choosing index "IX_A" as the INDEX_PATH and not some other index. For eg: if table A has column a,b,c and column a and column b has an index IX_A and column b and c has an index IX_B then select * from A where a =1 and b =2 is choosing IX_A.