Re: Explain Plan for queries

Actually in 9.30 there's an optimizer directive AVOID_EXECUTE which will provide the explain plan without executing the query. You can also set it using SET EXPLAIN ON AVOID_EXECUTE (pls check the syntax though). In 9.40 in addition to the above, you can dynamically turn on explain on for a session using an onmode switch (I think its onmode -Y 1). This helps you to see the explain plan for queries which are coming from the client directly instead of asking the application group to provide you with the query and then trying to get the plan seperately. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi Obnoxio The Clown <obnoxio@hotmail. To: informix-list@iiug.org com> cc: Sent by: Subject: Re: Explain Plan for queries owner-informix-li st@iiug.org 10/16/2003 11:07 AM Please respond to Obnoxio The Clown ovkrishna wrote: > I have a bunch of issues, though mostly related to obtaining the query > plan in informix. I am working with version 9.30. > > I am a newbie to Informix online dynamic server. So my questions might > be too basic. Please be patient with me. > > I need to obtain the query plan for a set of queries and tune them. I am > using DBACCESS to run the queries. Going through the documents I came to > know that I can get the query plan by executing SET EXPLAIN ON and then > executing my queries. Now I have the following questions > > 1. Is there a way in INFORMIX as in ORACLE to obtain the query plan, > without actually executing the queries? If yes how? Not in 9.30, I think there is an EXPLAIN optimiser directive in 9.40. > 2. As I mentioned I need to obtain query plans for multiple > queries. Is there a way to run a whole file having all these > queries using DBACCESS? Yes. Edit a file to contain: SET EXPLAIN ON;SELECT ...; SELECT ...; SELECT ...; Then execute "dbaccess yourdatabase yourfile" -- this should leave all the explain plans in sqexplain.out. > 3. All my queries are huge in size. So is there a mechanism to set the > SQL Edit buffer size in dbaccess, as I am getting a error "801: SQL > Edit buffer is full." Run them from the command line. > 4. As in Oracle SQLPLUS (set spool on), is there a way to redirect the > output to a file in Informix. OUTPUT TO "filename" SELECT ...; -- Ciao, The Obnoxious One "Ogni uomo mi guarda come se fossi una testa di cazzo" sending to informix-list