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:

1. Added a variable "customer_num" and a "count" to obtain the number of full scans executed on the table.
   
2. 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.
   
3. 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.
4. Added a SET EXPLAIN ON that will give us an overview of what's happening
5. Execute UPDATE STATISTICS LOW after first query execution to see if it triggers the re-optimization of the statement
6. 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):

1. Create a copy of the customer table in the stores demo database (includes data and the index on the customer_num column
2. Launch the demo_tst compiled program and obtain the return code (number of sequential scans executed on the table)
   
3. 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:

1. Compile the demo_tst program with: esql -o demo_tst demo_tst.ec
2. 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:

1. Adicionei uma variável "customer_num" para a condição da query e uma "count" para obter o número de sequential scans efectuados.
   
2. 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.
   
3. 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.
4. Adicionei um SET EXPLAIN ON que nos dará visibilidade sobre o que se passou.
5. Execução de um UPDATE STATISTICS LOW depois da primeira execução para vermos de dispara a re-optimização na segunda execução
6. 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):

1. Cria uma cópia da tabela "customer" na base de dados de demonstração stores (incluí dados e o índice na coluna customer_num.
2. 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).
   
3. 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:

1. Compilar o demo_tst com: esql -o demo_tst demo_tst.ec
2. 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