Performance loss for a query after 11.50 >12.10 up

Hi all, we just completed an offplace migration from 11.50 to 12.10 FC8W1. This instance is mainly intended for DWH, size reaching 0.7 Tb Migration ran well, but we now have a severe performance loss on one very complex query, which ran formerly in about 120 seconds on 11.50, and that I have to abort after 2 hours although not completed. - Update statistics are done accurately for each table involved in the query - the tables involved are not very big ( all less than 100,000 records) - I won't list the query here because it is 7 'A4' format pages long(no I didn't write this query...) the Buffer POOL is 25Gb+, SHMVITSIZE is comfortable, tempdbs is comfortable too, discs are not bad. User is alone in instance when running the query. I am aware that the optimizer has important changes starting 11.70, and that setting SET OPTIMIZATION OPT_SEEK_FACTOR '0' may be a solution for queries showing those symptoms after that kind of migration. (It actually solved a problem at another customer's, it did magic) I won't show the query here because, yes, it is totally ununderstandable and unmaintainable, first reaction after finishing to read it "OUCH! that hurts!" but it worked perfect in 11.50. The query plan involves a lot of temp tables dynamic creation, LEFT join on sub SELECT statements and more funny things. tables size are not enormous, but there are 16 tables or dynamically created tables. Kind of difficult to optimize. Considering that what worked well on 11.50 is supposed to work as well or even better in 12.10, is there any other 'magic' (i.e magic setting or parameter) that could fix the issue ? I am also waiting for the original query plan in 11.50, which is in another company, thus not easy and fast to obtain. This last call is before creating a PMR ... Thanks for any magic light :-) Eric

Hi Eric I get into similar situation here at the last week. Probably your situation isn't the same we had here.... Since as you wrote : "...and more funny things..." , I will share... Here we migrate our production from 11.50.FC9XW to 12.10.FC8W1 . Then a program which run over 1h30min , goes to +24h and not finish... The issue was functional index + remote access ... The SQL involves tables from different databases and at the WHERE clause use a function which is a functional index at the remote table (other database at same instance). At version 11.50 the query run in 0.00sec (yes is practically instantaneous) , at version 12.10 run +8 seconds... (the program run this query +100 times per second) This function is some "generic" at our system and exists at both databases (local and remote). At where clause doesn't specify the database at the call of function , something like : --- from local_table a , dbB:remote_table b where a.id = myfunc(b.field2) -- at version 11.50 , they recognize the functional index for dbB:remote_table and use it. at version 12.10 , they use the local index , because it have the same name ... so, the engine not use the functional index. the solution, change our application and include the database at function call : --- from local_table a , dbB:remote_table b where a.id = db2:myfunc(b.field2) -- then the query plan at 12.10 backs equals of 11.50 Bests regards Cesar 2017-05-24 12:35 GMT-03:00 ERIC VERCELLETTO < eric.vercelletto@begooden-it.com>: > Hi all, > > we just completed an offplace migration from 11.50 to 12.10 FC8W1. This > instance is mainly intended for DWH, size reaching 0.7 Tb > > Migration ran well, but we now have a severe performance loss on one very > complex query, which ran formerly in about 120 seconds on 11.50, and that I > have to abort after 2 hours although not completed. > > - Update statistics are done accurately for each table involved in the > query > - the tables involved are not very big ( all less than 100,000 records) > - I won't list the query here because it is 7 'A4' format pages long(no I > didn't write this query...) > > the Buffer POOL is 25Gb+, SHMVITSIZE is comfortable, tempdbs is comfortable > too, discs are not bad. User is alone in instance when running the query. > > I am aware that the optimizer has important changes starting 11.70, and > that > setting SET OPTIMIZATION OPT_SEEK_FACTOR '0' may be a solution for queries > showing those symptoms after that kind of migration. (It actually solved a > problem at another customer's, it did magic) > > I won't show the query here because, yes, it is totally ununderstandable > and > unmaintainable, first reaction after finishing to read it "OUCH! that > hurts!" > but it worked perfect in 11.50. > > The query plan involves a lot of temp tables dynamic creation, LEFT join on > sub SELECT statements and more funny things. tables size are not enormous, > but > there are 16 tables or dynamically created tables. Kind of difficult to > optimize. > > Considering that what worked well on 11.50 is supposed to work as well or > even > better in 12.10, is there any other 'magic' (i.e magic setting or > parameter) > that could fix the issue ? > > I am also waiting for the original query plan in 11.50, which is in another > company, thus not easy and fast to obtain. > > This last call is before creating a PMR ... > > Thanks for any magic light :-) > Eric > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Forgot mention... We open a PMR and the IBM support identify why this happen and suggest this solution . 2017-05-24 13:03 GMT-03:00 Cesar Martins <cesar.inacio.martins@gmail.com>: > Hi Eric > > I get into similar situation here at the last week. > Probably your situation isn't the same we had here.... > > Since as you wrote : "...and more funny things..." , I will share... > > Here we migrate our production from 11.50.FC9XW to 12.10.FC8W1 . > Then a program which run over 1h30min , goes to +24h and not finish... > > The issue was functional index + remote access ... > The SQL involves tables from different databases and at the WHERE clause > use a function which is a functional index at the remote table (other > database at same instance). > > At version 11.50 the query run in 0.00sec (yes is > practically instantaneous) , at version 12.10 run +8 seconds... > (the program run this query +100 times per second) > > This function is some "generic" at our system and exists at both databases > (local and remote). > At where clause doesn't specify the database at the call of function , > something like : > --- > from local_table a , dbB:remote_table b > where a.id = myfunc(b.field2) > -- > at version 11.50 , they recognize the functional index for > dbB:remote_table and use it. > at version 12.10 , they use the local index , because it have the same > name ... so, the engine not use the functional index. > the solution, change our application and include the database at function > call : > --- > from local_table a , dbB:remote_table b > where a.id = db2:myfunc(b.field2) > -- > > then the query plan at 12.10 backs equals of 11.50 > > Bests regards > Cesar > > > 2017-05-24 12:35 GMT-03:00 ERIC VERCELLETTO <eric.vercelletto@begooden-it. > com>: > >> Hi all, >> >> we just completed an offplace migration from 11.50 to 12.10 FC8W1. This >> instance is mainly intended for DWH, size reaching 0.7 Tb >> >> Migration ran well, but we now have a severe performance loss on one very >> complex query, which ran formerly in about 120 seconds on 11.50, and that >> I >> have to abort after 2 hours although not completed. >> >> - Update statistics are done accurately for each table involved in the >> query >> - the tables involved are not very big ( all less than 100,000 records) >> - I won't list the query here because it is 7 'A4' format pages long(no I >> didn't write this query...) >> >> the Buffer POOL is 25Gb+, SHMVITSIZE is comfortable, tempdbs is >> comfortable >> too, discs are not bad. User is alone in instance when running the query. >> >> I am aware that the optimizer has important changes starting 11.70, and >> that >> setting SET OPTIMIZATION OPT_SEEK_FACTOR '0' may be a solution for queries >> showing those symptoms after that kind of migration. (It actually solved a >> problem at another customer's, it did magic) >> >> I won't show the query here because, yes, it is totally ununderstandable >> and >> unmaintainable, first reaction after finishing to read it "OUCH! that >> hurts!" >> but it worked perfect in 11.50. >> >> The query plan involves a lot of temp tables dynamic creation, LEFT join >> on >> sub SELECT statements and more funny things. tables size are not >> enormous, but >> there are 16 tables or dynamically created tables. Kind of difficult to >> optimize. >> >> Considering that what worked well on 11.50 is supposed to work as well or >> even >> better in 12.10, is there any other 'magic' (i.e magic setting or >> parameter) >> that could fix the issue ? >> >> I am also waiting for the original query plan in 11.50, which is in >> another >> company, thus not easy and fast to obtain. >> >> This last call is before creating a PMR ... >> >> Thanks for any magic light :-) >> Eric >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >

Thanks Cesar! unfortunately no functional index to remote queries involved here only 15 LEFT joins written the 'Oracle way' (just to help...) If I were the Informix optimizer, I would be lost in such a query *lol* Trying to find out where the engine is spending time...

I can only suggest general query tuning best practice, apologies if this is stuff you already know. If the query ran to completion a "set explain statistics" would show what is taking the time. A "set explain on avoid_execute" may also give some hints. If you have sole use of the system it ought to be fairly obvious at least which table or index is being heavily read and you don't have to wait for the query to finish e.g. onstat -z onstat -g ppf | sort -nk 10 | tail You'll have to then find what the partnum value relates to. Or if you have partn from the IIUG software repository (recommended) you can identify the object more easily with: onstat -g ppf | sort -nk 10 | tail | partn -k 1 -x HTH Ben.