Informix technology: query plan

Showing posts with label query plan. Show all posts

Wednesday, November 27, 2024

V15: Obtain the query plan of a running query

New feature that allows retrieving a query plan of a running query (original version here)

English version
For anyone involved with RDBMS, the concept of a query plan and its importance is not new. Most performance issues are probably related to bad query plans. So, the ability to obtain the query plan of a query is a base stone of the DBA work. And informix allows it for as long as I can remember. The only "slight" issue is that it only works for a query that we run or simulate to run. Why is this an issue? For three main reasons:

It's an hassle to have to capture a query and it's parameters (if the query is prepared), run it in a tool and obtain the query plan. Specially for short lived queries
The fact that a prepared query may have a different query plan than the one we get in a tool with a query written with values (more on this later)
A long running query may be using a different query plan than the one we get, because some of the conditions may be different (update statistics may have been run, parameters or context may have changed)

So, we could overcome the first point with some work, but we cannot overcome the last points. And in order to understand what is happening with a running query we MUST be able to capture the existing plan of a query that is being run. This is why it was so difficult for me to understand why it took so long to fix this. The only explanation I have is an enormous "distance" between developers and people who use the products. This is not exclusive to Informix. I've personally felt this with other products.
This is just to explain that this was a "since ever" requested feature. I personally officially registered it at

https://ideas.ibm.com/ideas/INFX-I-249

on April 2013. But this was not the first time I pushed for it. I even made some attempts do dig into the memory structures that would show this, but the lack of internal documentation made it a nightmare and a dead end. Anyway, enough with the history. The feature was implemented in version 15, and for me personally this would be the top priority. Let's see how we can use it. There are two interfaces to access the query plan:

onstat -g qplan <0 | session_id>
query the sysmaster:syssqexplain

Let's start by opening a session an run a simple query:

SELECT * FROM customer WHERE customer_num > 110;

On another session let's identify the session and run the onstat command on that session:


asterix@myhost.onlinedomus.local:informix-> onstat -g qplan 91

IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 2 days 00:23:07 -- 2193704 Kbytes
2024-11-25 22:34:02 -- Infrastructure Version: 1

Session [91]
QUERY:
------
SELECT * FROM customer WHERE customer_num > 110

QUERY: (OPTIMIZATION TIMESTAMP: 11-25-2024 22:34:03)Estimated Cost: 3
Estimated # of Rows Returned: 18

  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 > 110



asterix@myhost.onlinedomus.local:informix->

Very simple. If a session has no active query (or if we issue the command too late) we'll get:


asterix@myhost.onlinedomus.local:informix-> onstat -g qplan 91

IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 2 days 00:25:14 -- 2193704 Kbytes
2024-11-25 22:36:10 -- Infrastructure Version: 1

Session [91]: No running query to generate the plan.

asterix@myhost.onlinedomus.local:informix->

Again very simple. Now let's check how this can be done through SQL The feature introduced a new column on the sysmaster:syssqexplain, called sqx_sqlstatementplan. So we simply need to query this with a filter on sqx_session_id. An example:


asterix@myhost.onlinedomus.local:informix-> dbaccess -e sysmaster qplan.sql

Database selected.

SELECT
        sqx_sqlstatementplan
FROM
        sysmaster:syssqexplain
WHERE
        sqx_sessionid = 91



sqx_sqlstatementp+
                    QUERY: (OPTIMIZATION TIMESTAMP: 11-25-2024 22:41:12)Estimat
                    ed Cost: 3
                    Estimated # of Rows Returned: 18

                      1) stores:informix.customer: INDEX PATH

                        (1) Index Name: informix. 100_1
                            Index Keys: customer_num   (Serial, fragments: ALL)

                            Lower Index Filter: stores:informix.customer.custom
                    er_num > 110


1 row(s) retrieved.


Database closed.

asterix@myhost.onlinedomus.local:informix->

Before I end this article, I'd like to get back to point 2) above. The fact that a prepared statement may have a different query plan from what we can reproduce by writing the same statement and using the same values as in it's parameters. This is usually hard to explain to customers who usually don't understand why I don't trust in the query plan obtained in dbaccess when we're trying to analyze a performance issue where prepared statements are involved. The reason why this happens is one of the main reasons why this feature is so important, so I think it's worth the effort to dive a bit into this subject. The first important thing we need to clarify is what is a prepared statement? A prepared statement is a statement sent to the engine with the conditions in the WHERE clause, but where the values used in those conditions CAN be replaced by questions marks. If we wanted to prepared the statement above used as an example, the query text would be: SELECT * FROM customer WHERE customer_num > ?
When the statement is prepared, it is sent to the engine, and the engine validates the syntax. The statement executions will pass the parameters. On the first execution, the engine will calculate the query plan. And this is the crucial point around this discussion. The plan will depend on the first execution values, and in normal circumstances will not change until the statement is re-prepared or re-optimized. I'll try to show this with an example, created in JAVA. Let's start with a snippet of the program code:


1   import java.sql.*;
2   import java.util.*;
3   import java.text.*;
4   import com.informix.jdbc.*;
5   import java.util.Scanner;
6
7
8   public class cursor_iter
9   {
10      public static void main( String [] args ) {
11
12              Connection conn = null;
13              int count=0;
14              ResultSet dbRes = null;
15              IfxStatement is = null;
16              Statement is1 = null;
17              String timeStamp;
18              SimpleDateFormat dateFormat;
19              com.informix.jdbc.IfmxPreparedStatement ps = null;
20              Statement st = null;
21              Scanner scan = new Scanner(System.in);
22
23              try {
24
25                      Class.forName("com.informix.jdbc.IfxDriver");
26                      conn = DriverManager.getConnection("jdbc:informix-sqli://myhost:10010/example_db:INFORMIXSERVER=goscinny;USER=informix;PASSWORD=MASKEDPWD;");
27
28              } catch (Exception sqle1) {
29                      System.out.println("Database connection has failed.");
30                      System.out.println("Reason: " + sqle1.getMessage());
31              }
32
33
34
35              try {
36                      st = conn.createStatement();
37                      st.executeUpdate("SET EXPLAIN ON");
38
39                      ps = (com.informix.jdbc.IfmxPreparedStatement) conn.prepareStatement("SELECT * FROM example_table WHERE id >= ?");
40                      System.out.println("STEP 1: Statement prepared. Press ENTER to set parameter\n");scan.nextLine();
41
42                      ps.setInt(1, 1);
43                      System.out.println("STEP 2: Parameter set to 1. Press ENTER to execute\n");scan.nextLine();
44                      ResultSet rs = ps.executeQuery();
45                      System.out.println("STEP 3: Query executed with 1. Press ENTER to position in result set\n");scan.nextLine();
46                      rs.next();
47                      System.out.println("STEP 4: Next() executed with 1. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
48                      rs.close();
49                      System.out.println("STEP 5: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
50
51                      ps.setInt(1, 2);
52                      System.out.println("STEP 6: Parameter set to 2. Press ENTER to execute query\n");scan.nextLine();
53                      rs = ps.executeQuery();
54                      System.out.println("STEP 7: Query executed with 2. Press ENTER to position in result set\n");scan.nextLine();
55                      rs.next();
56                      System.out.println("STEP 8: Next() executed with 2. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
57                      rs.close();
58                      System.out.println("STEP 9: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
59
60                      ps.setInt(1, 7);
61                      System.out.println("STEP 10: Parameter set to 7. Press ENTER to execute with reoptimization\n");scan.nextLine();
62                      rs = ps.executeQuery(false,true);
63                      System.out.println("STEP 11: Query executed with 7 with re-optimization. Press ENTER to position in result set\n");scan.nextLine();
64                      rs.next();
65                      System.out.println("STEP 12: Next() executed with 7. Value: " + rs.getString(1) + ". Press ENTER to close\n");scan.nextLine();
66                      rs.close();
67                      System.out.println("STEP 13: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
68
69                      ps.setInt(1, 1);
70                      System.out.println("STEP 14: Parameter set to 1. Press ENTER to execute query\n");scan.nextLine();
71                      rs = ps.executeQuery();
72                      System.out.println("STEP 15: Query executed with 1. Press ENTER to position in result set\n");scan.nextLine();
73                      rs.next();
74                      System.out.println("STEP 16: Next() executed with 1. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
75                      rs.close();
76                      System.out.println("STEP 17: ResultSet closed. Press ENTER to finish\n");scan.nextLine();
77              } catch (SQLException sqle) {

The code is fairly simple (disclaimer: it may contain some errors or bad practices). The list of actions is:

Lines 23-31: It connects to the DB
Lines 36,37: Activates the explain
Line 39: Prepares a statement with one host variable
Lines 42-49: Sets the host variable to "1" and executes the query, fetches a row and closes the result set
Lines 51-58: Repeats the execution for value "2"
Lines 60-67: Repeats the execution for value "7", but with re-optimization
Lines 69-76: Repeats the execution for value 1 again

Each step will wait for user input. The purpose of the code is to show the following:

The first query plan calculation happens on the first execution and the plan uses a sequencial scan (because the query has a filter >= 1 and "1" is the lowest table value, so statistically the query will return the whole table
The second execution does not recalculate the plan
The third execution uses a very selective value (7), and is executed with the option to re-optimize the plan. And because we have a different value (with much higher selectivity) it will choose an index
The last execution doesn't request re-optimization, and although it uses the first value (1) it will use the index

Hopefully when you analyze the behavior you'll understand:

Prepared statements calculate the query plan for the value(s) passed in the first execution, unless we request a re-optimization (which will recalculate the plan and from then on) this one will be used until a new re-optimization is requested
New simple execution will re-use the latest calculated plan
A plan calculated once can be good for certain values and bad for others. Prepared statements don't care about this
This "issue" is only meaningful if for a certain plan you have values that are good and others that would benefit from a different plan. In other words, if your table distribution is not relatively uniform across values
Most important conclusion: Given the previous points it should become clear why the need for this feature should be so obvious for anyone using Informix.

To make it a bit more clear why we get two different plans here is the column distributions:


goscinny@myhost.onlinedomus.local:informix-> dbschema -d example_db -hd example_table | head -33

DBSCHEMA Schema Utility       INFORMIX-SQL Version 12.10.FC15

{

Distribution for idt.example_table.id
Constructed on 2024-10-19 01:13:30.77780
High Mode, 0.500000 Resolution

--- DISTRIBUTION ---
    (                                   1)
 1: (        10,          1,            7)

--- OVERFLOW ---

 1: (  16670876,                        1)
 2: (  16664127,                        2)
 3: (  16659895,                        3)
 4: (  16664556,                        4)
 5: (  16670374,                        5)
 6: (  16670185,                        6)

goscinny@myhost.onlinedomus.local:informix->

So, the values 1,2,3,4,5 and 6 are considered overflows meaning they have many rows (~16.6M each). Value 7 has only 10 rows. So, if we start the executions with value 1, it will choose a sequential scan which is the best option because we're expecting to read the whole table. But once we have that query plan, an execution with "7" would take a long time (which is why I used "2" in the second execution).
On the other hand, if we use "7" on the first execution, the engine chooses the index path, which again is the best option for this value, but would not be a good option for "1" (and most probably the others).

This is the nature of prepared statements with tables with uneven distributions. The workarounds for these cases are not in the scope for this article (it could be a good idea to cover this in another article), but I hope this makes it very clear why we need to get the effective plan of a running query and not a plan we get on dbaccess by writing the query with values.

And here is video capture of the above code. On the left side you can see what is being written to the sqexplain.out file where it becomes evident when the query plans are calculated and re-used. On the right side there's the code running and pausing for user input.

One final note: When we re-optimize we seem to get a "phantom" execution of the query with the previous plan and the new value. If you check it, you'll see the statistics of the execution are equal to the last one used (for value 2). I believe this is a glitch or bug in the SET EXPLAIN code. A sequential scan for value 7 would take a long time to return (I tested it). And it would not return the same stats as for the value 2

Here's the video to make this more clear:

Versão Portuguesa

Para qualquer pessoa envolvida com RDBMS, o conceito de plano de execução e a sua importância não será novidade. A maioria dos problemas de performance estão provavelmente relacionados com maus planos de execução. Assim, a capacidade de obter um plano de execução de uma query, é como uma pedra de fundação do trabalho de DBA. E o Informix permite isso desde que me recordo. O único "ligeiro" problema é que isso só funciona para uma query que vamos executar ou simular a execução. Porque é que isto é um problema? Por três razões principais:

É trabalhoso ter de obter uma query e os seus parâmetros (se a query fôr "PREPAREd"), executá-la numa ferramenta e daí obter o plano se execução. Em especial se a query fôr de curta duração
O facto de que uma query "PREPAREd" pode ter um plano de execução diferente daquele que obtemos correndo a query directamente com valores numa ferramenta (mais sobre isto mais adiante)
Uma query com uma execução muito longa, pode estar a usar um plano de execução diferente daquele que obtemos, porque as condições podem ter-se alterado (as estatísticas podem ter sido refeitas, parâmetros ou contexto pode ser diferente...)

Portanto, podemos contornar ou ignorar o primeiro ponto, com mais ou menos trabalho, mas não conseguimos resolver os últimos pontos. E para entendermos o que se passa com uma query em execução TEMOS mesmo de ser capazes de capturar o seu efectivo plano de execução. É por isto que sempre me foi muito difícil entender porque demorou tanto tempo a corrigir isto. A única explicação que encontro é uma grande "distância" entre quem desenvolve e quem usa os produtos. Isto não se passa apenas no Informix. Tenho-o sentido com outros produtos também.
Serve tudo isto para explicar que esta funcionalidade era um pedido "desde sempre". Registei-o oficialmente aqui

https://ideas.ibm.com/ideas/INFX-I-249

em Abril de 2013. Mas este não foi o primeiro momento em que batalhei por isto. Cheguei mesmo a fazer algumas tentativas para vasculhar estruturas de memória que poderiam conter esta informação. Mas a falta de documentação sobre essas estruturas tornou a tarefa num pesadelo e beco sem saída.

Bom, mas chega de história. A funcionalidade foi implementada na versão 15, e para mim sempre seria uma prioridade absoluta. Vejamos como a podemos utilizar. Existem duas interfaces para aceder ao plano de execução:

onstat -g qplan <0 | id_sessao>
consultar a sysmaster:syssqexplain

Comecemos por abrir uma sessão e executar uma query simples::

SELECT * FROM customer WHERE customer_num > 110;

Noutra sessão vamos identificar a sessão de base de dados onde a query está a correr e executar o comando onstat contra essa sessão:


asterix@myhost.onlinedomus.local:informix-> onstat -g qplan 91

IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 2 days 00:23:07 -- 2193704 Kbytes
2024-11-25 22:34:02 -- Infrastructure Version: 1

Session [91]
QUERY:
------
SELECT * FROM customer WHERE customer_num > 110

QUERY: (OPTIMIZATION TIMESTAMP: 11-25-2024 22:34:03)Estimated Cost: 3
Estimated # of Rows Returned: 18

  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 > 110



asterix@myhost.onlinedomus.local:informix->

Muito simples. Se a sessão não tiver nenhuma query activa (ou se dermos o comando demasiado tarde) é isto que obtemos:


asterix@myhost.onlinedomus.local:informix-> onstat -g qplan 91

IBM Informix Dynamic Server Version 15.0.0.0AEE -- On-Line -- Up 2 days 00:25:14 -- 2193704 Kbytes
2024-11-25 22:36:10 -- Infrastructure Version: 1

Session [91]: No running query to generate the plan.

asterix@myhost.onlinedomus.local:informix->

Novamente muito simples. Agora vejamos como podemos aceder a esta informação via SQL. A funcionalidade introduziu uma nova coluna na sysmaster:syssqexplain, chamada sqx_sqlstatementplan. Portanto só temos de interrogar esta view seleccionando esta coluna e filtrando pela coluna sqx_session_id. Um exemplo:


asterix@myhost.onlinedomus.local:informix-> dbaccess -e sysmaster qplan.sql

Database selected.

SELECT
        sqx_sqlstatementplan
FROM
        sysmaster:syssqexplain
WHERE
        sqx_sessionid = 91



sqx_sqlstatementp+
                    QUERY: (OPTIMIZATION TIMESTAMP: 11-25-2024 22:41:12)Estimat
                    ed Cost: 3
                    Estimated # of Rows Returned: 18

                      1) stores:informix.customer: INDEX PATH

                        (1) Index Name: informix. 100_1
                            Index Keys: customer_num   (Serial, fragments: ALL)

                            Lower Index Filter: stores:informix.customer.custom
                    er_num > 110


1 row(s) retrieved.


Database closed.

asterix@myhost.onlinedomus.local:informix->

Antes de terminar este artigo, gostaria de voltar ao ponto 2) acima. É um facto que uma query PREPAREd pode usar um plano de execução diferente do que conseguimos reproduzir, usando o mesmo SQL e parâmetros. É habitual ter alguma dificuldade em explicar isto a clientes, que geralmente não entendem porque não confio nos planos de execução obtidos por exemplo no dbaccess, quando tentamos analisar problemas de performance que envolvam este tipo de instruções.

A razão porque isto acontece é uma das principais razões porque esta funcionalidade é tão importante, e assim penso que vale o esforço de aprofundar mais o tema. Temos de começar por clarificar o que é uma instrução "PREPAREd". É uma instrução enviada ao motor de base de dados, em que os valores das condições da cláusula WHERE podem ser substituídos por pontos de interrogação. Caso quiséssemos fazer o PREPARE do exemplo acima usaríamos: SELECT * FROM customer WHERE customer_num > ?

Quando a instrução é "PREPAREd", é enviada ao motor, e o motor irá validá-la sintacticamente. Depois as várias execuções da instrução irão passar parâmetros. Na primeira execução o motor irá calcular o plano de execução. E este é o ponto crucial para a discussão. O plano irá depender dos valores passados para a primeira execução, e em circunstâncias normais não irá mudar até que a instrução seja novamente PREPAREd ou re-optimizada. Vou tentar evidenciar isto com um exemplo, criado em JAVA. Comecemos por ver um excerto desse programa:


1   import java.sql.*;
2   import java.util.*;
3   import java.text.*;
4   import com.informix.jdbc.*;
5   import java.util.Scanner;
6
7
8   public class cursor_iter
9   {
10      public static void main( String [] args ) {
11
12              Connection conn = null;
13              int count=0;
14              ResultSet dbRes = null;
15              IfxStatement is = null;
16              Statement is1 = null;
17              String timeStamp;
18              SimpleDateFormat dateFormat;
19              com.informix.jdbc.IfmxPreparedStatement ps = null;
20              Statement st = null;
21              Scanner scan = new Scanner(System.in);
22
23              try {
24
25                      Class.forName("com.informix.jdbc.IfxDriver");
26                      conn = DriverManager.getConnection("jdbc:informix-sqli://myhost:10010/example_db:INFORMIXSERVER=goscinny;USER=informix;PASSWORD=MASKEDPWD;");
27
28              } catch (Exception sqle1) {
29                      System.out.println("Database connection has failed.");
30                      System.out.println("Reason: " + sqle1.getMessage());
31              }
32
33
34
35              try {
36                      st = conn.createStatement();
37                      st.executeUpdate("SET EXPLAIN ON");
38
39                      ps = (com.informix.jdbc.IfmxPreparedStatement) conn.prepareStatement("SELECT * FROM example_table WHERE id >= ?");
40                      System.out.println("STEP 1: Statement prepared. Press ENTER to set parameter\n");scan.nextLine();
41
42                      ps.setInt(1, 1);
43                      System.out.println("STEP 2: Parameter set to 1. Press ENTER to execute\n");scan.nextLine();
44                      ResultSet rs = ps.executeQuery();
45                      System.out.println("STEP 3: Query executed with 1. Press ENTER to position in result set\n");scan.nextLine();
46                      rs.next();
47                      System.out.println("STEP 4: Next() executed with 1. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
48                      rs.close();
49                      System.out.println("STEP 5: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
50
51                      ps.setInt(1, 2);
52                      System.out.println("STEP 6: Parameter set to 2. Press ENTER to execute query\n");scan.nextLine();
53                      rs = ps.executeQuery();
54                      System.out.println("STEP 7: Query executed with 2. Press ENTER to position in result set\n");scan.nextLine();
55                      rs.next();
56                      System.out.println("STEP 8: Next() executed with 2. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
57                      rs.close();
58                      System.out.println("STEP 9: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
59
60                      ps.setInt(1, 7);
61                      System.out.println("STEP 10: Parameter set to 7. Press ENTER to execute with reoptimization\n");scan.nextLine();
62                      rs = ps.executeQuery(false,true);
63                      System.out.println("STEP 11: Query executed with 7 with re-optimization. Press ENTER to position in result set\n");scan.nextLine();
64                      rs.next();
65                      System.out.println("STEP 12: Next() executed with 7. Value: " + rs.getString(1) + ". Press ENTER to close\n");scan.nextLine();
66                      rs.close();
67                      System.out.println("STEP 13: ResultSet closed. Press ENTER to set parameter\n");scan.nextLine();
68
69                      ps.setInt(1, 1);
70                      System.out.println("STEP 14: Parameter set to 1. Press ENTER to execute query\n");scan.nextLine();
71                      rs = ps.executeQuery();
72                      System.out.println("STEP 15: Query executed with 1. Press ENTER to position in result set\n");scan.nextLine();
73                      rs.next();
74                      System.out.println("STEP 16: Next() executed with 1. Value: " + rs.getString(1) + ". Press ENTER to close result set\n");scan.nextLine();
75                      rs.close();
76                      System.out.println("STEP 17: ResultSet closed. Press ENTER to finish\n");scan.nextLine();
77              } catch (SQLException sqle) {

O código é bastante simples (salvaguarda: o código pode conter erros ou más práticas). A lista de acções é:

Linhas 23-31: Abre a conexão à BD
Linhas 36,37: Activa a escrita do plano de execução para ficheiro
Linhas 39: Faz o PREPARE com uma variável "host"
Linhas 42-49: Define a variável "host" a "1", executa a query, posiciona-se numa linha e fecha o "result set"
Linhas 51-58: Repete a execução para o valor "2"
Linhas 60-67: Repete a execução para o valor "7", mas desta feita com re-optimização
Linhas 69-76: Repete a execução para o valor "1" novamente

Em cada passo espera pelo input do utilizador. Os objectivos deste código são mostrar o seguinte:

O cálculo do primeiro plano de execução acontece na primeira execução, e usa uma busca sequencial na tabela (pois tem um filtro ">= 1" e "1" é o menor valor da tabela, portanto estatisticamente deveremos ler a tabela toda)
A segunda execução não recalcula o plano
A terceira execução usa um valor muito selectivo (7), e é efectuada com a opção de re-optimização. Sendo um valor muito mais selectivo o plano vai escolher acesso por índice
A última execução não pede re-optimização, e embora use o primeiro valor (1) que despoletou um acesso sequencial vai usar o plano com acesso por índice calculado na interação anterior.

Espero que depois de analisado este comportamento possa entender o seguinte:

Instruções PREPAREd calculam o plano para o(s) valor(es) passado(s) na primeira execução, e a menos que seja pedida uma re-optimização (que irá recalcular o plano a usar daí em diante), irá usar sempre o mesmo plano calculado na primeira execução.
Novas execuções "simples" irão utilizar o último plano calculado
Um plano calculado uma vez pode ser bom para certos valores e mau para outros. Instruções PRERAREd trabalham mesmo assim
Este "problema" só tem impacto se para determinado plano há valores "bons" e outros "maus". Por outras palavras, só tem impacto quando a tabela tem distribuições não uniformes entre os diferentes valores possíveis.
A conclusão mais importante: Dados os pontos anteriores, deverá estar muito claro para quem usa Informix porque necessitamos tanto desta nova funcionalidade.

Para tornar o exemplo um pouco mais claro, e explicar porque obtemos diferentes planos, aqui fica a distribuição de valores na coluna usada na cndição:


goscinny@myhost.onlinedomus.local:informix-> dbschema -d example_db -hd example_table | head -33

DBSCHEMA Schema Utility       INFORMIX-SQL Version 12.10.FC15

{

Distribution for idt.example_table.id
Constructed on 2024-10-19 01:13:30.77780
High Mode, 0.500000 Resolution

--- DISTRIBUTION ---
    (                                   1)
 1: (        10,          1,            7)

--- OVERFLOW ---

 1: (  16670876,                        1)
 2: (  16664127,                        2)
 3: (  16659895,                        3)
 4: (  16664556,                        4)
 5: (  16670374,                        5)
 6: (  16670185,                        6)

goscinny@myhost.onlinedomus.local:informix->

Portanto, os valores 1,2,3,4,5 e 6 são considerados "overflows", o que significa que têm muitas linhas (~16.6M cada). O valor 7 só tem 10 linhas. Assim se iniciamos as execuções com o valor "1", irá escolher uma busca sequencial, dado que espera ler toda a tabela, e assim é o método mais eficiente. Mas uma vez que tenhamos esse plano, uma execução para o valor "7" demoraria muito tempo (daí ter usado "2" na segunda execução). Por outro lado, se usamos o "7" na primeira execução ficaremos com um plano de acesso por índice, pois é a melhor opção para este valor. Mas não será a melhor opção para o valor "1" (e provavelmente para os outros).

Esta é a natureza das instruções PREPAREd com tabelas com distribuições "irregulares". As formas de contornar estes casos não estão no âmbito deste artigo (poderia ser uma boa ideia fazer um artigo dedicado ao tema), mas espero que isto torne claro porque necessitamos mesmo de conseguir obter o plano efectivo de uma query em execução, e não o plano que é gerado pela query escrita com os valores.

E abaixo está uma captura de video da execução do código explicado acima. Do lado esquerdo pode seguir o que vai sendo escrito no ficheiro de saída do SET EXPLAIN. o que evidencia quando é que os planos são calculados e re-utilizados. Do lado direito tem a execução do código com as pausas para input do utilizador.

Uma nota final: Quando se efectua a re-optimização, aparentemente obtemos uma execução "fantasma" da query com o plano anterior e o valor novo. Se atentar nos dados, verá que as estatísticas e execução são iguais às mostradas no passo anterior (para o valor "2"). Suponho que isto seja um bug do SET EXPLAIN. Uma pesquisa sequencial para o valor "7" demoraria muito mais tempo a retornar (eu tentei). E naturalmente não retornaria as mesmas estatísticas que foram retornadas pelo valor "2".

Aqui fica o vídeo:

Friday, March 01, 2024

Rethinking AUTO_REPREPARE

Revisiting AUTO_REPREPARE parameter (original version here)

English version
A recent customer engagement made me analyze the use of this parameter. I have no absolute certain, but what it seems is that the system starts to use a non-optimal query plan (the reason for this is not yet clear, but seems related to the way statistics are updated) for a specific query. The query can be seen running very frequently and taking much longer than it should and the CPU consumption increases. Running statistics doesn't seem to help, but restarting the applications does solve the issue.

A possible explanation is that the query is PREPAREd, and the query plan doesn't change even when we run the statistics. Eventually new sessions will get the correct query plan, and the system returns to normal. This is (up to a point) a normal effect when using prepared statements. Obviously this is highly inconvenient.

That's where the AUTO_REPREPARE parameter can play a fundamental role. This parameter was introduced to avoid the error -710. This used to happen when a table used in a PREPARED statement was changed (new indexes, new columns, dropping columns etc.). The next occurrences of the execution would raise this error and would require an explicit re-preparation of the query, a re-opening of the associated cursor or another activity that would force the calculation of a new plan.

The parameter exists since version 11.10 but it only accepted two values: 0 to turn the feature off and 1 to turn it off. In version 12.10 new values were introduced but apparently they didn't get enough visibility:

0 = Disables the automatic repreparation of prepared objects after the schema of a directly or an indirectly referenced table is modified. Also disables the automatic reoptimization of SPL routines after the schema of an indirectly referenced table is modified.
1 = Enables automatic repreparation.
3 = Enables automatic repreparation in optimistic mode.
5 = Enables automatic repreparation on update statistics.
7 = Enables automatic repreparation in optimistic mode and on update statistics

As we can see, there are options that will trigger automatic statement re-preparation on UPDATE STATISTICS (I'll address the "optimistic mode" later). 5 should be exactly what we want. It will take care of schema changes and also when we UPDATE STATISTICS on the tables used. This way the existing (bad) plans will be updated automatically and we avoid the need to restart the applications. The process will be transparent to the applications.

I've created a simple test to show the effect of this setting. I picked up the "demo1.ec" sample from a Client SDK installation and made some changes to it:

Added a variable "customer_num" and a "count" to obtain the number of full scans executed on the table.
Changed the query WHERE condition to use "WHERE customer_num > ?". Also changed the query to use a table "customer_tst" which will be created for the test purpose.
Repeated the cursor block. I want to execute the query three times. First with value 1 (when plan is calculated it uses a sequential scan), then with value 115 (will choose an INDEX path if the plan is recalculated) and a last time with the original value of 1.
Added a SET EXPLAIN ON that will give us an overview of what's happening
Execute UPDATE STATISTICS LOW after first query execution to see if it triggers the re-optimization of the statement
Find out how many sequential scans were run on the test table and return it as return code of the program

I also created a test SHELL script that will do the following steps for different values of AUTO_REPREPARE (1 and 5):

Create a copy of the customer table in the stores demo database (includes data and the index on the customer_num column
Launch the demo_tst compiled program and obtain the return code (number of sequential scans executed on the table)
Show the explain output for the demo_tst program

The code is at the end of this article, if you want to run the test. To run it follow this steps:

Compile the demo_tst program with: esql -o demo_tst demo_tst.ec
Execute the shell script with: ./test.sh

Hopefully what you'll see is:

For execution with AUTO_REPREPARE set to 1:

You will get three sequential scans and the explain plan will show only one plan and three similar executions.

For execution with AUTO_REPREPARE set to 5:

You will get one sequential scan (triggered by the use of "1" in the first query) and two INDEX path (triggered by the use of "115" on the second query, re-optimized because between first and second execution we run UPDATE STATISTICS). Third execution, although it uses the same value as the first execution will follow the plan calculated on the second execution, because there is nothing that triggers the re-optimization between second and third executions.

Conclusion

The (not so) new value of 5 allows PREPARED queries to be re-optimized when statistics on the underlying table(s) are refreshed, allowing the queries to benefit from improved query plans without restarting. It will also avoid the -710 error as the usual value of "1" permits.

Note that sometimes customers don't have AUTO_REPREPARE explicitly set to one, but it assumes this value if AUTO_TUNE is set to 1. However to benefit from this extended improvement we need to explicitly set the value to 5.

The value 3 and 7 are similar respectively to values 1 and 5, but the system will not check for schema changes or UPDATE STATISTICS if a query run successfully in the last second. This will of course open the possibility of getting -710 errors if a query is constantly used.

One question pop out: Is there any reason to run Informix with a different value?

Versão Portuguesa
Uma actividade num cliente fez-me revisitar o uso deste parâmetro (AUTO_REPREPARE). De momento não tenho a certeza absoluta, mas parece que ocasionalmente o sistema começa a usar um plano de execução pior para uma query (a razão para isto ainda não é conhecida mas suspeita-se que está relacionado com a forma como se executam as estatísticas). Nestas alturas a query pode facilmente encontrar-se a correr e demora muito mais que o esperado, e o consumo de CPU aumenta. Recalcular as estatísticas não parece resolver, mas um re-inicio das aplicações faz desaparecer o problema.

Uma possível explicação é que a query está "preparada", e assim o plano não se altera quando executamos o UPDATE STATISTICS. Eventualmente novas sessões obtêm o novo e melhorado plano de execução, e depois o sistema volta ao normal. Isto é (até certo ponto) um efeito esperado da utilização de statements preparados. Obviamente é também bastante inconveniente.

É aqui que o parâmetro AUTO_REPREPARE pode ter um papel fundamental. Este parâmetro foi introduzido para evitar o erro -710. Isto acontecia quando uma tabela usada numa instrução preparada sofria alguma modificação (novos índices, novas colunas, remoção de colunas etc.). A execução seguinte da instrução geraria o erro e seria necessário uma nova abertura de cursor associado, a execução do PREPARE novamente ou outra acção que despoletasse a geração de um novo plano de execução.

Este parâmetro existe desde a versão 11.10, mas só aceitava dois valores: 0 para desligar a funcionalidade e 1 para a ligar. Na versão 12.10 foram introduzidos novos valores, mas aparentemente não tiveram visibilidade suficiente:

0 = Desactiva a "repreparação" automática depois de a estrutura de uma tabela directa ou indirectamente usada na query ser mudada. Desliga também a re-optimizção automática para procedimentos SPL.
1 = Activa a re-optimização automática.
3 = Activa a re-optimização automática em modo "optimista".
5 = Activa a re-optimização automática também para UPDATE STATISTICS.
7 = Activa a re-optimização automática também para UPDATE STATISTICS em modo "optimista".

Como se pode verificar há opções para despoletar a re-optimização das instruções quando ocorre um UPDATE STATISTICS (vermos o modo "optimista" depois). O valor 5 será exactamente o que se pretende. Endereça as alterações de estrutura bem como a execução de estatísticas nas tabelas envolvidas. Desta forma os planos existentes (não óptimos) serão actualizados automaticamente e evitamos o re-inicio das aplicações. Este processo é transparente para as aplicações.

Para demonstrar isto criei um teste simples que permite ver o efeito da funcionalidade. Parti do exemplo "demo1.ec" existente numa instalação de Client SDK e fiz-lhe algumas alterações:

Adicionei uma variável "customer_num" para a condição da query e uma "count" para obter o número de sequential scans efectuados.
Mofifiquei a condição WHERE da query para usar "WHERE customer_num > ?". Também alterei a query para usar uma tabela "customer_tst" que será criada para este propósito.
Repeti o bloco de código que abre o cursor. Quero executar a query três vezes. A primeira será com o valor 1 (o motor escolhe um sequential scan para este valor). Depois com o valor 115 (o plano terá um acesso por índice quando recalculado) e novamente com o valor inicial de 1.
Adicionei um SET EXPLAIN ON que nos dará visibilidade sobre o que se passou.
Execução de um UPDATE STATISTICS LOW depois da primeira execução para vermos de dispara a re-optimização na segunda execução
Obter o número de sequential scans executados na tabela de teste. Esse valor é usado como retorno do programa para que o script possa obter a contagem

Criei também um SHELL script que fará os seguintes passos para ambos os valores do AUTO_REPREPARE (1 e 5):

Cria uma cópia da tabela "customer" na base de dados de demonstração stores (incluí dados e o índice na coluna customer_num.
Lança o programa referido antes (demo_tst) e obtém o seu código de retorno (número de sequential scans executado na tabela).
Mostra o resultado do EXPLAIN contendo as queries feitas pelo programa demo_tst

O código está no fim do artigo, caso pretenda executar o teste. Os passos serão:

Compilar o demo_tst com: esql -o demo_tst demo_tst.ec
Executar o SHELL script com: ./demo.sh

Em princípio o resultado será:

Para a execução com AUTO_REPREPARE = 1:

Serão executados três sequential scans e o EXPLAIN terá apenas um plano e três execuções similares.

Para a execução com AUTO_REPREPARE = 5:

Será executado apenas um sequential scan (da primeira execução pela utilização do valor "1") e dois acessos por índice (este segundo plano obtido pelo uso do valor "115" na segunda execução, re-optimizado porque entre a primeira e segunda execução fizemos um UPDATE STATISTICS). A terceira execução terá sempre o mesmo plano da segunda, pois não há nada entre ambas que cause uma re-optimização.

Conclusão

O (relativamente) novo valor 5 permite que queries preparadas possam ser re-optimizadas logo que sejam refrescadas estatísticas nas tabelas envolvidas, permitindo que as queries beneficiem automaticamente de planos melhoradas, sem re-inicio das aplicações. Também evita o erro -710 como o mais habitual valor 1 permite.

Note-se que é frequente que os clientes não tenham o AUTO_REPREPARE explicitamente definido, mas em alguns casos acaba por assumir o valor 1, caso tenham o AUTO_TUNE a 1. Mas para beneficiarmos desta funcionalidade estendida é necessário defini-lo explicitamente a 5.

O valor 3 e 7 são semelhantes respectivamente aos valores 1 e 5, mas no modo "optimista" onde o sistema não vai verificar se é necessário fazer a re-optimização se a query correu com sucesso há menos de um segundo. Isto deixa naturalmente margem para ocorrência do erro -710 se uma query fôr executada constantemente.

Tendo isto em conta há uma questão que salta à vista: Haverá razão para ter outro valor que não o 5 neste parâmetro?

The code

demo_tst.ec:

/****************************************************************************

* Licensed Material - Property Of IBM * * IBM Informix Client-SDK * * (c) Copyright IBM Corporation 1997, 2013. All rights reserved. * (c) Copyright HCL Technologies Ltd. 2017. All Rights Reserved. * **************************************************************************** */ #include <stdio.h> #include <string.h> #include <unistd.h> EXEC SQL define FNAME_LEN 15; EXEC SQL define LNAME_LEN 15; int main() { EXEC SQL BEGIN DECLARE SECTION; char fname[ FNAME_LEN + 1 ]; char lname[ LNAME_LEN + 1 ]; integer customer_num = 1, seq_scans; EXEC SQL END DECLARE SECTION; int count; printf( "DEMO1 Sample ESQL Program running.\n\n"); EXEC SQL WHENEVER ERROR STOP; EXEC SQL connect to 'stores'; EXEC SQL SET EXPLAIN ON; EXEC SQL PREPARE p1 FROM "select fname, lname from customer_tst where customer_num > ?"; printf("======== Statment prepared ====================\n"); EXEC SQL declare democursor cursor for p1; printf("== Opening the cursor with value 1 ============\n"); EXEC SQL open democursor USING :customer_num; count=0; for (;;) { EXEC SQL fetch democursor into :fname, :lname; if (strncmp(SQLSTATE, "00", 2) != 0) break; count++; if ( count == 1 ) printf("Frst row: %s %s\n",fname, lname); } printf("%d rows were returned\n", count); if (strncmp(SQLSTATE, "02", 2) != 0) printf("SQLSTATE after fetch is %s\n", SQLSTATE); EXEC SQL close democursor; printf("== Updating statistics for table =====\n"); EXEC SQL UPDATE STATISTICS LOW FOR TABLE customer_tst; printf("======== Reopening the cursor again with 115 ==\n"); customer_num = 115; EXEC SQL open democursor USING :customer_num; count=0; for (;;) { EXEC SQL fetch democursor into :fname, :lname; if (strncmp(SQLSTATE, "00", 2) != 0) break; count++; if ( count == 1 ) printf("Frst row: %s %s\n",fname, lname); } printf("%d rows were returned\n", count); if (strncmp(SQLSTATE, "02", 2) != 0) printf("SQLSTATE after fetch is %s\n", SQLSTATE); EXEC SQL close democursor; printf("======== Reopening the cursor again with original 1 ==\n"); customer_num = 1; EXEC SQL open democursor USING :customer_num; count=0; for (;;) { EXEC SQL fetch democursor into :fname, :lname; if (strncmp(SQLSTATE, "00", 2) != 0) break; count++; if ( count == 1 ) printf("Frst row: %s %s\n",fname, lname); } printf("%d rows were returned\n", count); if (strncmp(SQLSTATE, "02", 2) != 0) printf("SQLSTATE after fetch is %s\n", SQLSTATE); EXEC SQL close democursor; EXEC SQL free democursor; EXEC SQL SET EXPLAIN OFF; EXEC SQL SELECT t2.pf_seqscans INTO :seq_scans FROM sysmaster:systabnames t1, sysmaster:sysptntab t2 WHERE t1.partnum = t2.partnum AND t1.dbsname = 'stores' and t1.tabname = 'customer_tst'; EXEC SQL disconnect current; printf("\nDEMO1 Sample Program over.\n\n"); printf("======== Sequential scans executed on customer_tst table: %d =======\n",seq_scans); return(seq_scans); }

test.sh:

#!/bin/bash run_actions() { AUTO_REPREPARE_MODE=$1 onmode -wm AUTO_REPREPARE=$AUTO_REPREPARE_MODE printf -- "------------------------------------------------------------------------------------------------\nPreparing the table (customer_tst) and launching the program demo1\n------------------------------------------------------------------------------------------------\n" dbaccess stores <<EOF DROP TABLE IF EXISTS customer_tst; CREATE TABLE customer_tst AS SELECT * FROM customer; CREATE INDEX cust_fis_pk ON customer_tst(customer_num); EOF ./demo_tst NUM_SEQSCANS=$? cat sqexplain.out case $NUM_SEQSCANS in 1) printf -- "------------------------------------------------------------------------------------------------\nOnly one sequential scan was done. That was the first. The plan changed between executions\n------------------------------------------------------------------------------------------------\n" if [ "X${AUTO_REPREPARE_MODE}" = "X5" ] then printf -- "------------------------------------------------------------------------------------------------\nThis is expected with AUTO_REPREPARE set to 5\n------------------------------------------------------------------------------------------------\n" else printf -- "------------------------------------------------------------------------------------------------\nThis is NOT expected with AUTO_REPREPARE set to 1\n------------------------------------------------------------------------------------------------\n" fi ;; 3) printf -- "------------------------------------------------------------------------------------------------\nThree sequential scans were done. The plan didn't change between executions\n------------------------------------------------------------------------------------------------\n" if [ "X${AUTO_REPREPARE_MODE}" = "X1" ] then printf -- "------------------------------------------------------------------------------------------------\nThis is expected with AUTO_REPREPARE set to 1 or unset if AUTO_TUNE is set to 1\n------------------------------------------------------------------------------------------------\n" else printf -- "------------------------------------------------------------------------------------------------\nThis is NOT expected with AUTO_REPREPARE set to 5\n------------------------------------------------------------------------------------------------\n" fi ;; *) printf -- "------------------------------------------------------------------------------------------------\nUnexpected value for number of sequential scans ($NUM_SEQSCANS) in customer_tst table. No external access to this table should be done during testing\n------------------------------------------------------------------------------------------------\n" exit 1 ;; esac printf -- "------------------------------------------------------------------------------------------------\nPlease check query plans. If three sequential scans were executed it should show only one query plan and three executions.\nIf only one sequential scan was executed it should show two plans and an execution for first and two for second\n------------------------------------------------------------------------------------------------\n" } printf -- "------------------------------------------------------------------------------------------------\nRunning for AUTO_REPREPARE = 1\n------------------------------------------------------------------------------------------------\n" rm -f sqexplain.out run_actions 1 printf "*************************************************************************\nPRESS ENTER TO CONTINUE WITH SECOND PART OF THE TEST\n*************************************************************************\n" read DUMMY printf "*************************************************************************\nSTARTING SECOND PART OF TEST\n*************************************************************************\n" printf -- "------------------------------------------------------------------------------------------------\nRunning for AUTO_REPREPARE = 5\n------------------------------------------------------------------------------------------------\n" rm -f sqexplain.out run_actions 5 dbaccess stores <<EOF DROP TABLE IF EXISTS customer_tst; EOF

Tuesday, March 18, 2014

Explain plans: for the last time / Planos de execução: pela última vez

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)

English version:

If there is a topic that have always caught my attention it's the ability (or lack of) to capture a query plan in a client tool. This has been one of the most dirty Informix "secrets". This should be an essential feature of any RDBMs, and Informix provided the query plan, but in a very akward manner. A file was written on the database server (or on a filesystem mounted on the database server). This served us well enough when programmers used to work on the same machine as the database server, but those times are gone (a long time ago). I dedicated some time to a rather complex way of solving this after IBM introduced a function called EXPLAIN_SQL() that would return an XML file containing the query plan. This was the topic for my presentation on IIUG conference in 2010, but it was no more than a proof of concept. In other words it was not useable.
More recently I implemented a much simpler way to have the plan in any tool and documented it here in the blog. This works, it's simple and has little to no drawbacks. But it's still an hack...

Now, with some 20 years of delay, an article tells us that IBM has finally implemented this. There is a new function called ifx_explain() that accepts the query for which we want the query plan and returns the plan in text format. There is also an equivalent function called bson_explain() that returns the query plan in BSON format which can be casted to JSON (::JSON)

Please note that this is not documented. This new website (meanwhile it was added to the link list) is very recent and seems to be a great source of information. Some tweets mentioned it as belonging to John Miller who is one of the historic guys behind Informix (he was involved in all things related to backups, he's the "father" of Open Admin Tool, has several articles which even today are the references about update statistics, is deeply involved in JSON functionality etc.).

As a final note, I'd like to speak about something that is known to happen and this is just a clear example of that: we have implemented features in the engine which are not documented. The reason for that should be that they haven't passed (does not mean they failed or would fail) through the QA tests considered necessary to allow their general use. Which is understandable. But my point is that some of these were implemented too long ago. Time and resources were employed in creating them, and after too much time customers still haven't seen the benefits of that work. From my humble and personal (I must underline the "personal" for obvious reasons) perspective, there are some major issues with these situations:

The cost (time and resources translate directly into money) it took to implement them are a waste until the day customers can use them and we can speak about them
Some features (specifically SQL compatibility functions) may be used by customers without them knowing they're not documented. As an hypothetical example consider the new QUARTER() function announced in 12.10.xC3. A customer that is used to use that function in other RDBMs may just write it in SQL queries. If the server accepts it, he won't noticed if by any chance it was not (yet) documented. And if it's buggy, nasty things could happen, because in theory the customer was using a "non-existent" function, that was accepted by the server.
Occasionally some of these functionalities are "leaked". I won't forget that I saw an ONCONFIG parameter in one of the sessions of IOD conference a few years ago, that was a very necessary feature for customers. I mentioned that to at least one customer and later I noticed it was not documented. After internal questioning the official position was "it didn't go through proper QA". Well... a bit late. Neither the slide had information that was supposed to be undocumented nor did I check that it was not documented. I simply tested and it worked!

So, my position about this is simple: If it's supposed to be used, it must be documented. If it's not supposed to be used, the engine MUST not accept it. And in some cases I feel we're needing just a little bit more work (for QA) so that we can document those features and take "profit" (meaning allowing customers to use them) from all the investment put into it's creation.
Maybe the users and my colleagues that will be joining the IIUG 2014 conference in Miami want to include this topic in their discussions?

Having said this, it's a great day, as we closed a sad story about Informix!
If you're using 12.10.xC2+ then use this new feature. If not, try my soluction referenced above.

Versão Portuguesa:

Se há um assunto que sempre me mereceu atenção é a capacidade (ou falta dela) de capturar um plano de execução de uma query e apresentá-lo numa ferramenta cliente. Este tem sido um dos "segredos sujos" do Informix. Isto será uma funcionalidade essencial a qualquer sistema de bases de dados, e de facto o Informix sempre disponibilizou o plano de execução, mas de uma maneira muito arcaica. O mesmo é escrito num ficheiro localizado (ou pelo menos acessível) no servidor de base de dados. Isto servia-nos razoalvelmente bem quando os programadores costumavam trabalhar na mesma máquina onde corria a base de dados. Mas esses tempos já lá vão (há muito tempo...).
Dediquei algum tempo a uma solução complexa para resolver isto, quando a IBM introduziu uma função chamada EXPLAIN_SQL() que devolvia a representação do query plan em XML. Este foi inclusive o tópico da minha apresentação na conferência de utilizadores do IIUG em 2010. Mas nunca passou de uma prova de conceito, ou por outras palavras nunca foi algo utilizável.
Mais recentemente implementei de forma muito mais simples a obtenção do plano de execução nas ferramentas cliente e documentei-o aqui no blog. Esta forma funciona e tem poucos ou nenhumas desvantagens. Mas ainda assim é um truque...

Porém agora, com uns vinte anos de atraso, apareceu um artigo que nos diz que a IBM finalmente implementou isto. Existe uma nova função chamada ifx_explain() que aceita a query para a qual queremos obter o plano de execução e retorna o mesmo sob a forma de texto simples. Existe ainda uma função semelhante, chamada bson_explain() que retorna o plano como um objecto BSON que pode ser transformando em JSON (::JSON)

Tenha em consideração que isto não está documentado. Este novo website (entretanto adicionado à lista de links) é muito recente e parece ser uma excelente fonte de informação. Alguns tweets mencionam que pertencerá ou que foi criado pelo John Miller, que é nem mais nem menos que um dos "históricos" por detrás do Informix (esteve envolvido com tudo o que se relaciona com backups, é o "pai" do Open Admin Tool, tem vários artigos que ainda hoje são as referências sobre o UPDATE STATISTICS, está profundamente envolvido com as funcionalidades JSON etc...)

Como nota final, gostaria de falar sobre algo que se sabe acontecer e este caso é um exemplo claro disso mesmo: temos implementado funcionalidades no motor que não se encontram documentadas. A razão para tal deverá ser que as mesmas não passaram (não necessariamente que falharam ou falhassem) pelos devidos testes de qualidade (QA), de forma a estarem prontas para uso genérico nos clientes. E isto parece-me razoável. Mas o meu "problema" é que algumas delas já foram implementadas há demasiado tempo. Tempo e recursos foram empregues para as criar, e mesmo depois de muito tempo os clientes ainda não podem tirar proveito desse esforço. Da minha humilde e pessoal (e é necessário reforçar o "pessoal" por motivos óbvios) perspectiva há vários potencias problemas que derivam destas situações:

Os custos (tempo e recursos traduzem-se directamente em dinheiro) que derivaram da implementação destas funcionalidades são um desperdício até ao dia em que os clientes as possam usar e que possamos falar delas
Algumas funcionalidades (especificamente funções de compatibilidade SQL) podem ser usadas pelos clientes, sei que eles saibam que não são documentadas. Como exemplo hipotético, consideremos a nova função QUARTER() introduzida na 12.10.xC3. Um cliente que esteja habituado a escrever SQL com essa função noutra base de dados, pode perfeitamente escrevê-la em Informix. Se o servidor a aceitar, o cliente não se irá aperceber se a função está ou não (ainda) documentada. E se a mesma estiver ainda imperfeita ou instável, coisas imprevisíveis podem acontecer, simplesmente porque um cliente usou algo "que não existe" mas que o servidor aceitou
Ocasionalmente, algumas destas funcionalidades "transparecem" para a comunidade. Não me vou esquecer de ter visto um parâmetro de $ONCONFIG numa sessão de uma conferência IOD há alguns anos atrás, que ativa uma funcionalidade bastante necessária aos clientes. Eu já a mencionei a pelo menos um cliente e só depois me apercebi que não estava documentada. Depois de indagar internamente entendi que a posição oficial era "não sofreu testes significativos de QA". Bom... um pouco tarde demais. Nem o diapositivo tinha informação de que não estava documentado, nem eu verifiquei isso. Apenas fiz alguns testes e funcionou!

Portanto, a minha posição sobre o tema é simples: Se é suposto ser usado tem de estar documentado. Se não é suposto ser usado o motor NÃO pode aceitar. E em alguns casos sinto que necessitamos um pedacinho mais de esforço (para QA), de forma a que possamos documentar estas funcionalidades e obter "lucro" (ou seja, permitir que os clientes as usem) de todo o investimento colocado na sua criação.
Talvez os utilizadores e colegas que vão estar presentes na conferência do IIUG 2014 em Miami queiram incluir este tópico nas suas discussões?

Posto isto, foi um grande dia, pois fechámos uma história triste do Informix.
Se usa uma versão 12.10.xC2+ use esta nova funcionalidade. Senão recorra à forma que documentei e que referi no início

Monday, December 24, 2012

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:

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.
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:

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.
Because the location has to be writable by all users, a user with resource privileges could create a procedure to remove all the files
A user can potentially obtain other user's query plan files.
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

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.
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:

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
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:

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
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
Um utilizador potencialmente pode obter ficheiros de outros utilizadores
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

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
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;

Informix technology

Wednesday, November 27, 2024

V15: Obtain the query plan of a running query

Friday, March 01, 2024

Rethinking AUTO_REPREPARE

Conclusion

Conclusão

The code

Tuesday, March 18, 2014

Explain plans: for the last time / Planos de execução: pela última vez

Monday, December 24, 2012

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

Introdution

Basics

New solution

Usage

Additional notes

Introdução

Principios báasicos

Nova solução

Utilização

Notas adicionais

The code/O código

About Me

My tweets

Informix IoT

Links

IDS documentation

IBM Informix Redbooks

Blogs

Blog Archive

Information Management - Informix Dynamic Server (IDS)

Informix Tools

Informix DataBlade Modules

Informix technology

Wednesday, November 27, 2024

V15: Obtain the query plan of a running query

Friday, March 01, 2024

Rethinking AUTO_REPREPARE

Conclusion

Conclusão

The code

Tuesday, March 18, 2014

Explain plans: for the last time / Planos de execução: pela última vez

Monday, December 24, 2012

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

Introdution

Basics

New solution

Usage

Additional notes

Introdução

Principios báasicos

Nova solução

Utilização

Notas adicionais

The code/O código

About Me

My tweets

Informix IoT

Subscribe To

Links

IDS documentation

IBM Informix Redbooks

Blogs

Blog Archive

Information Management - Informix Dynamic Server (IDS)

Informix Tools

Informix DataBlade Modules