Execution plans on the client / Planos de execução nos clientes

This article is written in Englsih and Portuguese
Este artigo está escrito em Inglês e Português

English version

Introdution

Life is full of coincidences... Because IIUG announced the 2013 IIUG user's conference, to be held in Sand Diego on 21-25 April, I spent some time reviewing my 2010 presentation. The topic was "Use EXPLAIN_SQL with any Tool" and at the time I was proud of some of the achievements I reach. The basic idea was very simple: Use the function EXPLAIN_SQL() introduced in version 11.10 to obtain a query plan in XML format. Then use the engine XML capabilities to apply an XML transformation style sheet (XSLT) to finally get the plan in a manner that could be presented in any tool. Truth is, that although I think the idea is great, the fact that IBM never documented EXPLAIN_SQL properly, my lack of knowledge in XML technology (I know the basics, but I'm not efficient with it), and some technical issues, lead the idea to a stop... No progress in nearly 3 years. This triggered me to think about another approach... Because the basic problem still exists: It's not easy for a programmer with no access to the database server host machine to obtain the query plans.
But I was talking about coincidences... While I was thinking about this issue once again, I receive almost simultaneously two direct inquires about the use of EXPLAIN_SQL (from people who saw references to my presentation).
So, then what is this article about? Well... I think I finally managed to get a way to obtain the query plans on any client in an easy way, almost with no limitations or constraints... That's what I'm going to explain now

Basics

By default Informix writes the query plans to a file in the database server host's filesystem. The default file name is $HOME/sqexplain.out if the user asks for the plan. If it's the DBA that activates the plan generation then the default filename will be $HOME/sqexplain.out.SID where "SID" is the session ID.
The concept was created when most programmers and users were directly connected through a terminal window (Telnet or SSH) to the database server host. Currently this is not the case in many situations. And then you need some mechanism to move the file to the client machine, and open that file. That's technically a mess. And even if you set it up, it's awkward to work with it.
In version 11.10 IBM introduced a function called EXPLAIN_SQL() that allows a client tool to send the query and get back the query execution plan in XML. Then the tool should process this XML and present the query plan in some format (ideally in a graphic form). The problems are that the function inputs were never properly documented, the XML that it outputs is complex, and nearly no tools are able to do this (IBM Optim Data Studio can do it, but I'm not aware of any other tool which is able to implement this). What I explained in the IIUG presentation was a prototype on how to do that to get the query plan in the traditional text form result.
Another improvement introduced in version 10 was the ability to choose the filename. Besides the old SET EXPLAIN ON, version 10 also supported SET EXPLAIN FILE TO "path/name". This allowed for some custom tricks like having a shared filesystem between the server and the clients. But still this is far from a good solution.
Finally, version 10 also introduced the ability to SET EXPLAIN ON AVOID_EXECUTE which will generate the query plan but will not execute the SQL statement.

New solution

Given all the issues with EXPLAIN_SQL I tried to imagine a simple way to retrieve the file from the server and present it in the client. Version 11.70.FC6 introduced some new functions that allow us to read from a file, and this would allow a custom procedure to retrieve the file and return it as a LVARCHAR or CLOB. I implemented a prototype with this, but although it worked is was a bit complex and would require version 11.70.FC6. So I kept trying to reach a more generic solution that would work on most Informix versions. And I believe I got it.
It comes in the form of three or four procedures in SPL. The purpose of these procedures is to emulate the basic functionality of activating the explain, with or without executing, resetting the explain file, and obtain the file. The procedure are presented in the end of the article and I'm going to write a few comments about each of them:

• set_explain_on()
• Activates the explain output and resets the explain file. The explain filename is controlled by the DBA, not the user. So they can be put on a specific location
•  I declare two global variables (available through the user session). One for the explain file name and the other to remember is the user used AVOID_EXECUTE or not
• I create the explain file name (without PATH). Here I'm concatenating the username and it's session. This rule simulates the situation where a DBA runs onmode -Y. There are pros and cons about this. The good thing is that a user with different sessions can capture different explains. The bad thing is that the filename will be almost unique. So a method of cleaning them up should be implemented. It can be a scheduler task that calls a script or a crontab script... Just look for files where the SID does not exist on the instance
• Then I create the complete filename. Here I'm using /tmp as the file location but that's not a very good idea. It would be better to have a separate filesystem (or directory with quota). It needs to be written by all. Remember that you should be able to eliminate files on that directory to implement the cleaning script. Otherwise you could just use a single file name for each user. So the file would be re-used. Again, quotas are important to avoid that a user grabs all the space
• Then we clear the file using a SYSTEM command. This is a major difference from the SET EXPLAIN ON statement. This statement will append to the file. The reason why I clean it, is because when I retrieve the file, I send it to the client side. I don't process it to obtain just the last query.
• Finally I define the explain file and activate the explain (here without the AVOID_EXECUTE)
• This procedure would be the replacement for SET EXPLAIN ON
• set_explain_on_avoid_execute()
• Exactly the same thing but in this case the statement SET EXPLAIN ON AVOID EXECUTE is used. Same rules for file and file reset
• reset_explain()
• This is not really needed. It just resets the explain file (clears its content). In practice calling the set_explain_on() or set_explain_on_avoid_execute() again has the same effect
• Used just to clear the file. You need to previously have called set_explain_on() or set_explain_on_avoid_execute()
• This is not really need as calling one of the functions that starts explain has the same practical effect. But for clarity I decided to create it. It can be used between two query execution and explain file request to clear the explain file, so that on the second call the first query plan is not returned
• get_explain()
• This is the juicy part... It retrieves the file and returns it as a CLOB. In dbaccess it will not be very readable, but with GUI tools like Optim Data Studio, SQL Squirrel, AGS Server Studio etc. it works perfectly
• It uses the native Informix function FileToCLOB() to read the explain file and return a CLOB containing it
• Most tools I tested this on (Squirrel SQL, Server Studio...) will return this as a clickable result set. Once you click it, the explain will be showed in the tool in a separate window

Usage

As an example, we can try this on stores database with the following simple instructions (it assumes the functions were already created):

ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> dbaccess stores test_explain.sql

Database selected.


Routine executed.




customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2      
city          Sunnyvale
state         CA
zipcode       94086
phone         408-789-8075

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT * FROM customer WHERE customer_num = 101

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.customer: INDEX PATH

(1) Index Name: informix. 100_1
Index Keys: customer_num   (Serial, fragments: ALL)
Lower Index Filter: informix.customer.customer_num = 101 


Query statistics:
-----------------

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     1          1         1          00:00.00   1       



1 row(s) retrieved.


Routine executed.



(count(*)) 

15

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA'

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) informix.c: SEQUENTIAL SCAN

Filters: informix.c.state = 'CA' 

2) informix.o: INDEX PATH

(1) Index Name: informix. 102_4
Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
Lower Index Filter: informix.o.customer_num = informix.c.customer_num 
NESTED LOOP JOIN


Query statistics:
-----------------

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                c
t2                orders

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     18         3         28         00:00.00   4       

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t2     15         23        15         00:00.00   0       

type     rows_prod  est_rows  time       est_cost
-------------------------------------------------
nljoin   15         3         00:00.00   5       

type     rows_prod  est_rows  rows_cons  time
-------------------------------------------------
group    1          1         15         00:00.00



1 row(s) retrieved.


Database closed.

This was run in dbaccess. But in order to see the full potential you should try it on a GUI based tool like ServerStudio, Squirrel SQL, Aqua Data Studio, DBVisualizer etc.

Additional notes

My first feeling is that this article should not be necessary. It's about time that this problem had a "native" solution, although most people I found still like character based, local access using dbaccess. On the other hand, I always find it interesting to show how easy it is to solve some of the "traditional" issues.
This method raises some problems which are easy to solve, but if you think about using it, it's better to solve them in advance:

1. Where should we create these procedures and function? If you only have one database in your instance that's easy. But typically customers have several databases in a single instance. The answer is that you can create them in any database and call them referencing the external database (same logging assumed), or if you prefer you can create them in all the databases.
2. If the location of the explain files is to be a single location for all users, then it must be writable by all users. This may raise some issues, and you should avoid them. The issues that came to mind are:
1. A user with enough privileges could try to flood the explain file or create a new file in an attempt to fill the filesystem and cause some DoS attack.
2. Because the location has to be writable by all users, a user with resource privileges could create a procedure to remove all the files
3. A user can potentially obtain other user's query plan files.
4. You must be able to remove the files with an administrative user (like informix)
   
   The solution is to create an informix owned directory writable by anyone (this solves issue 4). You should configure quotas to avoid issue 1. If you're concerned about issue 2 and 3, then you can solve them using filesystem ACLs. And you can have some random factor for the filename... The user doesn't need to know the location of the files neither the file name... Of course, the user can get them from the global variables (assuming their names are "public") and it can retrieve the shared location from the procedure's code
   So if you want it bullet proof use quotas and ACLs. Another option would be to write the explain files in each user $HOME (like informix does by default), but that raises yet another potential issue. The argument for FileToCLOB must be a full pathname. So if all your user's $HOME is created in /home (or any other global location) this would not be a problem. The point is that you can't use the location "~user/" or "./
   Removing the files could be done in a script (as simple as removing all the files older than today), which could be called by the database scheduler, cron, or any other scheduler. You could also create a sysdbclose() procedure to clean up the explain file created
3. Because the explain file is cumulative, get_explain() will always retrieve the full explain history. reset_explain() can solve this. And we could of course replace FileToCLOB by a custom C code function that returns just the last query plan.
4. The FileToCLOB() function needs a smart BLOB space to be present and configured in SBSPACENAME $ONCONFIG parameter. If you miss this, an error 9810/12053 will be raised

Having all these in mind, I do think this is a very feasible solution for a very old Informix issue.
I would still like to see EXPLAIN_SQL being more documented and explored. The idea of transforming an XML containing the explain into whatever you prefer (explain using other databases format for example) really pleases me. But It's much more difficult to implement. This method is terribly simple and should work with any version equal or newer to 10 (so any currently fully supported version)

Versão Portuguesa

Introdução

A vida está cheia de coincidências... Devido ao anúncio que o IIUG fez da conferência de utilizadores de 2013 IIUG, que terá lugar em São Diego, de 21 a 25 de Abril, passei algum tempo a rever a minha apresentação de 2010. O tema era  "Use EXPLAIN_SQL with any Tool" e na altura estava bastante orgulhoso dos progressos que tinha alcançado. A ideia base era muito simples: Usar a função EXPLAIN_SQL introduzida na versão 11.10 para obter o plano de execução em formato XML. Depois usar as capacidades de XML do motor para aplicar uma transformação usando uma transformation style sheet (XSLT), para finalmente obter o plano numa forma que pudesse ser apresentado em qualquer ferramenta. A verdade é que, apesar de ainda achar que a ideia era boa, o facto de a IBM nunca ter documentado a função EXPLAIN_SQL com detalhe, a minha falta de conhecimento em tecnologia XML (sei o básico, mas não sou eficiente a lidar com a tecnologia), e mais alguns problemas técnicos levaram a ideia a um beco sem saída... Sem progresso em quase três anos.
Isto incentivou-me a pensar noutra abordagem... Porque o problema de fundo ainda existe: Não é fácil a um programador sem acesso à máquina da base de dados obter os planos de execução.
Mas estava a falar de coincidências... Enquanto pensava sobre este tema novamente, recebi quase em simultâneo duas questões diretas sobre o uso da função EXPLAIN_SQL (de pessoas que terão visto referências para a minha apresentação).
Portanto, sobre o que é este artigo? Bom... Julgo que finalmente criei uma maneira de obter o plano de execução no cliente, de uma forma fácil e praticamente sem limitações... É o que vou explicar de seguida.

Principios báasicos

Por omissão o Informix escreve os planos de execução em ficheiro no servidor de base de dados. O nome pré-definido do ficheiro é $HOME/sqexplain.out se o utilizador pedir para ser gerado o plano. Se for o DBA a ativar a geração dos planos de execução, usando o onmod -Y então o nome será $HOME/sqlexplain.out.SID onde "SID" será o session ID.
Esta forma de trabalhar foi pensada quando a maioria dos programadores e utilizadores estavam diretamente ligados através de uma consola (Telnet ou SSH) ao servidor de base de dados. Atualmente a situação já não é esta na maioria dos casos. E assim é necessário um mecanismo para mover o ficheiro para a máquina cliente, e depois o utilizador terá de o abrir. Tecnicamente é uma confusão e é um processo arcaico para se trabalhar.
 Na versão 11.10 a IBM introduziu uma função chamada EXPLAIN_SQL() que permite a uma ferramenta cliente enviar uma query e receber o plano de execução em formato XML. Depois a ferramenta tem de processar esse XML para apresentar o plano de execução numa forma legível (preferencialmente de forma gráfica). Os problemas são que os inputs da função nunca foram devidamente documentados,, o XML resultante é complexo, e praticamente nenhuma ferramenta é capaz de usar esta funcionalidade (o IBM Optim Data Studio implementa-a, mas não tenho conhecimento de mais nenhum caso). O que apresentei na sessão da conferência do IIUG foi um protótipo de como usar esta função para obter o plano de execução num formato de texto (que seria utilizável em qualquer ferramenta).
Outra melhoria, introduzida na versão 10 foi a possibilidade de escolher outro nome de ficheiro para escrever os planos de execução. Para além do tradicional SET EXPLAIN ON, a versão 10 introduziu o SET EXPLAIN FILE TO "caminho/ficheiro". Isto abriu a porta a alguns truques como ter um sistema de ficheiros partilhado entre o servidor de base de dados e os clientes. Mas mesmo assim ainda está longe de ser uma boa solução.
Por último a versão 10 também introduziu a possibilidade de executar SET EXPLAIN ON AVOID_EXECUTE, o que permite gerar o plano de execução sem efetivamente correr a query.

Nova solução

Dados todos os problemas com a função EXPLAIN_SQL(), eu tentei imaginar uma forma simples de obter o ficheiro do servidor para o cliente. E a versão 11.70.xC6 introduziu algumas novas funções que nos permitem ler de um ficheiro e apresentar o resultado no cliente. Isto permitiria criar uma função que lesse o ficheiro e retornasse um LVARCHAR ou CLOB. Cheguei a implementar um protótipo com estas funções, mas apesar de funcionar seria um pouco complexo e necessitava da versão 11.70.xC6. Por isso continuei a tentar obter uma solução mais genérica que funcionasse com a maioria das versões de Informix. E penso que consegui.
Aparece sob a forma de três ou quatro procedimentos em SPL. O objetivo destes procedimentos é emular a funcionalidade básica de ativar a escrita de planos de execução, com ou sem execução das queries, reinicialização do ficheiros de planos e obtenção desse mesmo ficheiro. O código destes procedimentos pode ser encontrado no final do artigo e vou comentar alguns pontos:

• set_explain_on()
• Ativa a escrita dos planos de execução e re-inicializa o ficheiro. O nome do ficheiro é controlado pelo DBA, não pelo utilizador. Podem assim ser colocados num local específico e controlado
• Declaro duas variáveis globais (disponíveis em toda a sessão do utilizador). Uma para o nome do ficheiro de planos e a outra para manter informação sobre o uso do AVOID_EXECUTE
• Defino o nome do ficheiro (sem caminho). Estou a concatenar o nome do utilizador com o número da sessão. Esta regra simula o comportamento obtido quando é o DBA a ativar a geração de planos. Há vantagens e desvantagens nesta abordagem. A vantagem é que um utilizador com mais que uma sessão pode obter informação independente de cada uma. A desvantagem é que o nome do ficheiro será praticamente único. Portanto haverá que criar um método de limpeza. Pode ser uma tarefa escalonada no próprio motor de base de dados (que chame um script) ou algo colocado no crontab... Bastará procurar por ficheiros cujo sufixo (SID) não tenha correspondência nas sessões de base de dados
• Depois defino o nome completo do ficheiro. Aqui estou a usar /tmp, mas isso não será boa ideia. Seria melhor ter um filesystem (ou pasta com quota). Necessita de estar aberto para escrita por todos os utilizadores. Lembre-se que deverá conseguir eliminar os ficheiros criados por qualquer utilizador para implementar o método de limpeza.
  Outra opção seria usar um único ficheiro para cada utilizador. Assim o ficheiro seria sempre re-utilizado. Mesmo assim o uso de quotas é importante para evitar que um utilizador esgote todo o espaço
• Depois o ficheiro é limpo recorrendo à instrução SYSTEM. Isto é uma grande diferença em relação à instrução SET EXPLAIN ON. Esta instrução adiciona ao conteúdo do ficheiro caso já exista. A razão porque o limpo é porque ao pedir o ficheiro, todo o seu conteúdo é enviado para o cliente. Não processo o conteúdo à procura apenas da última query
• Finalmente defino o nomes do ficheiro e ativo a escrita dos planos (aqui sem o AVOID_EXECUTE)
• Este procedimento seria o substituto da instrução SET EXPLAIN ON
• set_explain_on_avoid_execute()
• Exactamente igual ao de cima, mas aqui usando o SET EXPLAIN ON AVOID_EXECUTE. As mesmas regras para o ficheiro e a re-inicialização do mesmo
• reset_explain()
• Não será realmente necessário. Apenas re-inicializa o ficheiro onde são escritos os planos de execução. Na prática, chamar o set_explain_on() ou set_explain_on_avoide_execute() novamente tem o mesmo efeito. Mas decidi criá-lo por clareza. Pode ser usado entre execuções de queries diferentes para que na segunda chamada ao procedimneto que devolve o plan, vir apenas o da segunda query.
• Usado apenas para limpar o ficheiro. Terá de ter chamado antes o set_explain_on() ou o set_explain_on_avoid_execute()
• get_explain()
• É o ponto fulcral do mecanismo. Usa a função nativa do Informix FileToCLOB() para ler o ficheiro de planos e retornar um CLOB que o contém.
• Na maioria das ferramentas em que testei (Squirrel SQL, Server Studio....) o resultado é apresentado como uma célula onde se pode clicar. Depois de o fazer o plano de execução será mostrado numa janela à parte

Utilização

Como exemplo, podemos tentar usar isto na base de dados stores usando as seguintes instruções (pressupõe que as funções já tenham sido criadas):

 ids_117fc6@centaurus.onlinedomus.net:informix-> cat teste_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> cat test_explain.sql
EXECUTE PROCEDURE set_explain_on();
SELECT * FROM customer WHERE customer_num = 101;
EXECUTE FUNCTION get_explain();
EXECUTE PROCEDURE reset_explain();
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA';
EXECUTE FUNCTION get_explain();
ids_117fc6@centaurus.onlinedomus.net:informix-> dbaccess stores teste_explain.sql

Database selected.


Routine executed.




customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2      
city          Sunnyvale
state         CA
zipcode       94086
phone         408-789-8075

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT * FROM customer WHERE customer_num = 101

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.customer: INDEX PATH

(1) Index Name: informix. 100_1
Index Keys: customer_num   (Serial, fragments: ALL)
Lower Index Filter: informix.customer.customer_num = 101 


Query statistics:
-----------------

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     1          1         1          00:00.00   1       



1 row(s) retrieved.


Routine executed.



(count(*)) 

15

1 row(s) retrieved.




explain  

QUERY: (OPTIMIZATION TIMESTAMP: 12-19-2012 01:12:59)
------
SELECT COUNT(*) FROM customer c, orders o WHERE o.customer_num = c.customer_num AND c.state = 'CA'

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) informix.c: SEQUENTIAL SCAN

Filters: informix.c.state = 'CA' 

2) informix.o: INDEX PATH

(1) Index Name: informix. 102_4
Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
Lower Index Filter: informix.o.customer_num = informix.c.customer_num 
NESTED LOOP JOIN


Query statistics:
-----------------

Table map :
----------------------------
Internal name     Table name
----------------------------
t1                c
t2                orders

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t1     18         3         28         00:00.00   4       

type     table  rows_prod  est_rows  rows_scan  time       est_cost
-------------------------------------------------------------------
scan     t2     15         23        15         00:00.00   0       

type     rows_prod  est_rows  time       est_cost
-------------------------------------------------
nljoin   15         3         00:00.00   5       

type     rows_prod  est_rows  rows_cons  time
-------------------------------------------------
group    1          1         15         00:00.00



1 row(s) retrieved.


Database closed.

Isto foi executado no dbaccess. Mas para verificar todo o potencial, deve testar numa ferramenta com interface gráfica como ServerStudio, Squirrel SQL, Aqua Data Tudio, DBVisualizer etc.

Notas adicionais

Gostaria de afirmar que este artigo não deveria ser necessário. Já é tempo de que este problema tenha uma solução "nativa". Isto apesar de a maioria das pessoas que encontro ainda gostar do acesso local, baseado em caracter como o dbaccess. Por outro lado acho sempre interessante mostrar como é fácil resolver alguns destes "problemas tradicionais".
Este método levanta alguns problemas que são fáceis de resolver, mas se pensar usá-lo, é melhor resolvê-los antecipadamente:

1. Onde devemos criar os procedimentos e função? Se apenas tiver uma base de dados na sua instância será fácil. Mas tipicamente os clientes possuem mais que uma base de dados em cada instância. A resposta é que pode criá-los em qualquer base de dados e chamá-las referenciando a base de dados externa (assumindo que o modo de logging é o mesmo), ou se preferir pode criá-los em todas as bases de dados
2. Se a localização dos ficheiros for uma localização única para todos os utilizadores, então terá de ter permissões de escrita para todos eles. Isto pode leavantar alguns problemas, e deverá evitá-los. Os problemas que me ocorrem são:
1. Um utilizador com os privilégios adequados poderia tentar inundar o ficheiro de planos ou ir criando novos ficheiros numa tentativa de encher o filesystem
2. Porque a localização tem de permitir escrita por todos, um utilizador com privilégio resource poderá criar um procedimento para apagar os ficheiros de outros utilizadores
3. Um utilizador potencialmente pode obter ficheiros de outros utilizadores
   
4. Tem de poder remover os ficheiros com um utilizador de administração (como informix)
   
   A solução passa por criar uma pasta com dono informix, e permissões de escrita para todos os utilizadores. (resolve o problema 4). Deverá configurar quotas para evitar o problema 1. Se estiver preocupado com os assuntos 2 e 4, poderá resolvê-los usando ACLs de filesystem. E pode introduzir algum factor aleatório na construção do nome de ficheiro...  O utilizador não necessita de saber nem a localização dos ficheiros nem o nome dos ficheiros. Naturalmente o utilizador pode obter isto pelas variáveis globais (assumindo que os seus nomes são "publicos") e pode também saber a localização pela análise do código dos procedimentos.
   Portanto se quer algo à prova de bala tem de usar quostas e ACLs. Outra opção seria escrever os ficheiros em cada $HOME dos utilizadores (como o informix faz por omissão) mas isso levanta ainda outro problema: O argumento da função FileToCLOB tem de ser um caminho absoluto. Assim, se todos os utilizadores tiverem o $HOME definido para /home (ou qualquer outra localização) isto não seria um problema. O ponto é que não pode usar como localização não pode ser "~user" ou "./"
   Remover os ficheiros pode ser feito num script (tão simples como remover os ficheiros com mais que um dia), que poderá ser chamado pelo scheduler da base de dados, cron ou qualquer outro scheduler. Poderiam também criar um procedimento sysdbclose() para limpar o ficheiro de planos que tenha sido criado
   
   
3. Porque o ficheiro de planos é cumulativo, get_explain() irá sempre retornar toda a história. reset_explain() pode resolver isto. E pode claro substituir FileToCLOB() por uma função em C que retorne apenas o último plano contido no ficheiro
4. A função FileToCLOB requer que o sistema tenha um smart BLOB space, e que esteja configurado no parâmetro SBSPACENAMEno ficheiro $ONCONFIG . Caso tal não aconteça será retornado um erro 9810/12053

Tendo tudo isto em consideração acredito que esta seja uma solução fácil para um antigo problema com o Informix
Ainda gostaria de ver o EXPLAIN_SQL bem documentado e explorado. A ideia de transformar o XML contendo o plano de execução no que quer que preferiss (plano usando a nomenclatura ou aspecto de outras bases de dados por exemplo) agrada-me verdadeiramente. Mas é muito mais difícil de implementar. Ao contrário este método é muito simples e deverá funcionar com qualquer versão dos servidor a partir da 10 (portanto qualquer versão actualmente suportada)

The code/O código

1  CREATE PROCEDURE set_explain_on()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file, sys_cmd VARCHAR(255);
5       LET explain_file_name = USER||'.'||DBINFO('sessionid');
6       LET exp_file = '/tmp/'||explain_file_name;
7       LET sys_cmd='cat /dev/null > '||exp_file;
8       SYSTEM(sys_cmd);
9       SET EXPLAIN FILE TO exp_file;
10      SET EXPLAIN ON;
11      LET explain_execute = 't';
12  END PROCEDURE;

1  CREATE PROCEDURE set_explain_on_avoid_execute()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file, sys_cmd VARCHAR(255);
5    LET explain_file_name = USER||'.'||DBINFO('sessionid');
6    LET exp_file = '/tmp/'||explain_file_name;
7    LET sys_cmd='cat /dev/null > '||exp_file;
8    SYSTEM(sys_cmd);
9    SET EXPLAIN FILE TO exp_file;
10   SET EXPLAIN ON AVOID_EXECUTE;
11   LET explain_execute = 'f';
12 END PROCEDURE;

1  CREATE PROCEDURE reset_explain()
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE GLOBAL explain_execute BOOLEAN DEFAULT NULL;
4  DEFINE exp_file,sys_cmd VARCHAR(255);
5  IF explain_file_name IS NOT NULL
6  THEN
7    LET exp_file = '/tmp/'||explain_file_name;
8    SET EXPLAIN OFF;
9    LET sys_cmd='cat /dev/null > '||exp_file;
10   SYSTEM(sys_cmd);
11   SET EXPLAIN FILE TO exp_file;
12   IF explain_execute = 't'
13   THEN
14     SET EXPLAIN ON;
15   ELSE
16     IF explain_execute = 'f'
17     THEN
18       SET EXPLAIN ON AVOID_EXECUTE;
19     ELSE
20       RAISE EXCEPTION -746, "Execute option of set explain is not defined!";
21     END IF;
22   END IF;
23 ELSE
24   RAISE EXCEPTION -746, "Explain file is not set!";
25 END IF;
26 END PROCEDURE;

1  CREATE PROCEDURE get_explain() RETURNING CLOB AS explain;
2  DEFINE GLOBAL explain_file_name VARCHAR(255) DEFAULT NULL;
3  DEFINE exp_file VARCHAR(255);
4  DEFINE v_ret CLOB;
5
6  IF explain_file_name IS NOT NULL
7  THEN
8    LET exp_file = '/tmp/'||explain_file_name;
9    LET v_ret = FILETOCLOB(exp_file,'server');
10   RETURN v_ret;
11 END IF;
12 END PROCEDURE;