sqexplain.out
A user running a query with SET EXPLAIN ON AVOID_EXECUTE found sqexplain.out containing the same SELECT three times, each with a different plan and estimated cost, and asked whether the engine splits the query up. Respondents noted the file is appended to across runs, and that subqueries, views or UNIONs can produce multiple plan blocks, but Informix normally writes only the chosen plan. After deleting the file and re-running, all three entries had the same timestamp; the poster reported it was a defect in 11.50FC6. No APAR number or fix details were given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
When I run a query with set explain on avoid_execute, I get three different selects statements back that are each followed by what looks like is an execution plan in the sqexplain.out file Are these three select statements how the database engine is going to break the query down to execute it?
It's hard to tell... the database will execute a single query, but it may contain sub-queries...Only by looking at the queries we could tell... On Wed, Apr 4, 2018 at 6:42 PM, BENJI LONG <ruggedmouse@hotmail.com> wrote: > When I run a query with set explain on avoid_execute, > > I get three different selects statements back that are each followed by > what > looks like is an execution plan in the sqexplain.out file > > Are these three select statements how the database engine is going to break > the query down to execute it? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Possibly...could be a number of things... - You ran 3 selects after setting explain on - The sqexplain file will be appended to each time, so may have explain plans from previous executions - Could be subqueries in the SQL - these are broken out from the main query - Could be the SQL used a view - if a view is expanded so that the results are written to a file, you may see what looks like multiple statements captured -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Wednesday, April 04, 2018 11:43 AM To: ids@iiug.org Subject: sqexplain.out [40960] When I run a query with set explain on avoid_execute, I get three different selects statements back that are each followed by what looks like is an execution plan in the sqexplain.out file Are these three select statements how the database engine is going to break the query down to execute it? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
It looks to me like it is the exact same query printed 3 times, with 3 different execution plans for each one. I'm now thinking it is three different plans that will all take the same time to run and the engine just chooses which one of those three it will run, but will all give the same times to run it.
No... Check the optimization timestamp... The file is not reset... But it's weird it shows different query plans... On Wed, Apr 4, 2018 at 6:57 PM, BENJI LONG <ruggedmouse@hotmail.com> wrote: > It looks to me like it is the exact same query printed 3 times, with 3 > different execution plans for each one. > > I'm now thinking it is three different plans that will all take the same > time > to run and the engine just chooses which one of those three it will run, > but > will all give the same times to run it. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
I saw that it was appending so I deleted it. All three of these selects have the same timestamp. They each have different Estimated Costs and different plans. The query I got from the developer only has one big select with a lot of joins
No, Informix will evaluate different plans but will only write out the plan that it is chosen. If they are identical, then my guess is that you ran the query 3 times with "set explain on", either in one session or over several sessions (the file is appended to each time). -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Wednesday, April 04, 2018 11:57 AM To: ids@iiug.org Subject: Re: sqexplain.out [40963] It looks to me like it is the exact same query printed 3 times, with 3 different execution plans for each one. I'm now thinking it is three different plans that will all take the same time to run and the engine just chooses which one of those three it will run, but will all give the same times to run it. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
That is what I was thinking so I deleted out the sqexplain.out and ran it again today and the timestamp for all three is: 04-04-2018 11:58:34
Does it access views? Or does it contains UNIONS? On Wed, Apr 4, 2018 at 7:06 PM, BENJI LONG <ruggedmouse@hotmail.com> wrote: > I saw that it was appending so I deleted it. All three of these selects > have > the same timestamp. They each have different Estimated Costs and different > plans. > > The query I got from the developer only has one big select with a lot of > joins > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
It has a bunch of inner joins and then at the end it groups by 7 different things. I'm going to send it to IBM, and I will post back here what they say incase it can help anyone else that is trying to decipher these things.
Does the query have UNIONs in it? Those would likely appear as separate plans. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJI LONG Sent: Wednesday, April 04, 2018 12:14 PM To: ids@iiug.org Subject: Re: RE: sqexplain.out [40967] That is what I was thinking so I deleted out the sqexplain.out and ran it again today and the timestamp for all three is: 04-04-2018 11:58:34 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
It is a defect in 11.50FC6
Hi, Do you have the APAR number? Regards, David. > On 04 April 2018 at 19:34 BENJI LONG <ruggedmouse@hotmail.com> wrote: > > > It is a defect in 11.50FC6 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >