Oracle Pending Statistics

Oracle Databases 8 minutos

Olá DBAs, a vida tá boa?

E ainda nestas sagas de consultor, pulando de cliente em cliente, esses dias encontrei uma informação muito legal da qual eu ainda não tinha ouvido falar, trocando uma ideia com um DBA amigo, Marcos Cavalcanti, ele me acenou uma possibilidade para uns testes de performance e eu fiquei realmente feliz em ter encontrado isso.

Não raramente, a coleta de estatísticas no Oracle pode se tornar um vilão. Sim, vilão. Existem ocasiões em que a coleta pode alterar os “desejos” do optimizer para pior, tornando uma execução que antes estava tranquila, em uma call com o cliente onde tudo tá pegando fogo, pois o que funcionava muito bem ontem, hoje não está funcionando mais. O Oracle pode sim se enganar, e ferrar seu sistema após uma coleta, por diversos motivos, seja remoção/adição de histogramas, cálculo errado das novas estatísticas com superestima ou subestima de cardinalidade, ignorar partition pruning e etc…

Quando isso acontece num ambiente produtivo, com certeza é o caos instalado, e aí começam as pesquisas tentando entender o que é, de onde veio e como surgiu o problema. Aí você vê que ontem as 20 horas, foram realizadas coletas de estatísticas nas tabelas que estão envolvidas no caos atual em que você se encontra, com 15 pessoas numa reunião querendo saber por que aquela query marota parou de funcionar a contento.

Minha primeira dica é: Se o bicho tá pegando muito, e você não tem tempo nem de respirar, rollback nas estatísticas. É possível fazer rollback das estatísticas utilizando a package DBMS_STATS.RESTORE_TABLE_STATS, e você pode verificar a lista com os timestamps das estatísticas para a tabela pela view DBA_TAB_STATS_HISTORY, assim como a data da última coleta na coluna LAST_ANALYZED na DBA_TABLES.

Em teoria, voltando as estatísticas anteriores, você resolve o problema de imediato. Mas…e a próxima coleta? Vai ferrar tudo de novo? E é aí que vem o pulo do gato.

Oracle Pending Statistics

Com o Oracle Pending Statistics você pode:

  • Simular o que vai acontecer com as suas queries após a próxima coleta;
  • Fazer testes e alterações no tipo de coleta
  • Testar suas queries antes de aceitar a coleta
  • Se livrar dessas estatísticas se elas não estiverem a contento

O que é o Pending Stats? Basicamente, as estatísticas são coletadas, mas não são gravadas no dicionário nas tabelas onde as estatísticas são consultadas pelo optimizer, mas sim em tabelas de dicionário auxiliares, que gravam essas novas estatísticas, sem que elas influenciem no que está acontecendo no seu ambiente no momento. É ideal para ambientes produtivos, onde você quer ver se aquele estimate_percent fica melhor em 30% ou em auto_sample.


É tipo um test-drive das estatísticas. Voce coleta, não paga nada por isso, verifica se te atende, e então você por liberá-las ao database. Quando digo liberá-las, é tirar do status de “pending” e aceitar a coleta. Internamente o que o Oracle vai fazer é basicamente pegar estes dados das tabelas auxiliares que ele utilizou no dicionário, e colocá-los nas tabelas reais que fornecem dados para o optimizer fazer suas escolhas.

E como eu uso isso? Vamos lá, hands-on pessoal.

Criação tabela de teste
CREATE TABLE tst_pending_stats (
    id        NUMBER,
    categoria VARCHAR2(10),
    payload   VARCHAR2(100)
);

Popular tabela de teste (A coluna categoria terá distribuição desigual, para demonstrar o plano mudando, com o campo CATEGORIA inserido com COMUM = 900 linhas, RARO = 100 linhas)
SQL> BEGIN
  2      FOR i IN 1..900 LOOP
  3          INSERT INTO tst_pending_stats VALUES (i, 'COMUM', 'linha comum ' || i);
  4      END LOOP;
  5
  6      FOR i IN 901..1000 LOOP
  7          INSERT INTO tst_pending_stats VALUES (i, 'RARO', 'linha rara ' || i);
  8      END LOOP;
  9
 10      COMMIT;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

Coleta de estatísticas para a tabela
SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TST_PENDING_STATS',method_opt => 'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.

Verificar estatísticas da tabela
SQL> select table_name, num_rows, last_analyzed from dba_tables where table_name = 'TST_PENDING_STATS';

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TST_PENDING_STATS                    1000 13-ABR-26

SQL> select owner, table_name, stats_update_time from DBA_TAB_STATS_HISTORY where table_name = 'TST_PENDING_STATS';

OWNER                TABLE_NAME                STATS_UPDATE_TIME
-------------------- ------------------------- -----------------------------------
SYS                  TST_PENDING_STATS         13-ABR-26 02.38.15.897000 PM -03:00

Checar plano escolhido para o otimizador
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM tst_pending_stats
  4  WHERE categoria = 'RARO';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------
Plan hash value: 2645543343

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   500 | 13000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TST_PENDING_STATS |   500 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORIA"='RARO')

13 rows selected.

Perfeito, ambiente de reprodução criado. Como podemos ver, uma tabela com 1.000 linhas, com uma cardinalidade de 9 pra 1, sem índice criado e estatística atualizada leva o Oracle a fazer um Full table scan na tabela, exatamente como o esperado.

Mas, e se criarmos um índice nesta tabela na coluna onde há filtro?

Vejamos:

SQL> CREATE INDEX idx_tst_pending_stats_cat
  2  ON tst_pending_stats(categoria);

Index created.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM tst_pending_stats
  4  WHERE categoria = 'RARO';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------
Plan hash value: 2645543343

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   500 | 13000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TST_PENDING_STATS |   500 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORIA"='RARO')

13 rows selected.

Bom, após criar o índice, podemos ver que NADA mudou. O Oracle não sabe ainda que este índice teria um custo mais baixo de acesso, pois o Oracle não tem estatísticas coletadas após isso, e não sabe que houve quaisquer alteração nos dados ou mesmo em melhorias para o acesso (como o índice nesse caso), então ele prefere manter o FTS, que é o plano/estado conhecido.

Mas, essa criação do índice foi feita no database, eu quero coletar as estatísticas com diferentes parametros para testar, mas não quero que sejam visíveis a outros usuários para não comprometer qualquer operação, somente ao meu usuário, para que eu possa testar se o índice é realmente mais rápido nesse caso (obviamente aqui o caso é muito simples, mas em determinadas situações o índice pode sim ser mais custoso e não ser vantajoso). Como eu posso fazer esse teste sem atrapalhar os outros usuários?

Bom pessoal, é aí que entra o majestoso Oracle Pending Stats, mas antes, informações importantes que precisam ser entendidas:

  • PUBLISH não aparece explicitamente como parâmetro da DBMS_STATS.GATHER_TABLE_STATS porque ele NÃO é um argumento direto da procedure;
  • O controle de publish vs pending é feito via PREFERÊNCIAS de estatísticas, não como parâmetro formal da chamada.
SQL> SELECT dbms_stats.get_prefs('PUBLISH','SYS','TST_PENDING_STATS') AS publish FROM dual;
PUBLISH
-------------
TRUE

TRUE → estatísticas são publicadas automaticamente
FALSE → estatísticas ficam PENDING

Alterando a preferencia de publicação de estatísticas para a tabela:

SQL> EXEC dbms_stats.set_table_prefs(ownname => 'SYS',tabname => 'TST_PENDING_STATS',pname   => 'PUBLISH',pvalue  => 'FALSE');

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_prefs('PUBLISH','SYS','TST_PENDING_STATS') AS publish FROM dual;

PUBLISH
---------------------------------
FALSE

Agora coletamos as estatísticas novamente e verificamos o plano que seria utilizado com a query:

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TST_PENDING_STATS',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM tst_pending_stats
  4  WHERE categoria = 'RARO';

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------
Plan hash value: 2645543343

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   500 | 13000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TST_PENDING_STATS |   500 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORIA"='RARO')

13 rows selected.

Desde que as estatísticas estão em pending, nada muda, nem plano, nem hash value, nem nada. O Oracle coletou mas ignora completamente estas estatísticas. Então, se estivéssemos querendo saber como seria o comportamento em um ambiente produtivo, este seria o caminho para ter novas estatísticas sem afetar o ambiente e nem o comportamento de qualquer aplicação que esteja acessando, mas a título de teste para saber se as novas estatísticas serão benéficas ou não, queremos utiliza-las, então, simplesmente ativamos ela para a sessão específica:

SQL> ALTER SESSION SET optimizer_use_pending_statistics = TRUE;

Session altered.

E então, iremos ver como o plano se comporta após isso:

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM tst_pending_stats
  4  WHERE categoria = 'RARO';

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------
Plan hash value: 2206549089

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |   100 |  2600 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TST_PENDING_STATS         |   100 |  2600 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_TST_PENDING_STATS_CAT |   100 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CATEGORIA"='RARO')

14 rows selected.

Excelente! Neste ponto, a título de teste, minha sessão está enxergando essas estatísticas e mostra que ela seria benéfica à query, pois passaria a utilizar o índice que foi criado, qualquer outra sessão aberta, ainda utilizaria o plano anterior com FTS, pois qualquer outra sessão não sabe da existência dessas estatísticas, garantindo o funcionamento corrente e atual da base.

Vamos abrir outra sessão e verificar por nós mesmos:

PS C:\Users\jose.valdezio.junior> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 13 16:41:27 2026
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM tst_pending_stats
  4  WHERE categoria = 'RARO';

Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------
Plan hash value: 2645543343

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |   500 | 13000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TST_PENDING_STATS |   500 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORIA"='RARO')

13 rows selected.

Pronto! Provado por A mais B que

  • As outras sessões não são afetadas
  • Posso ativa-las na minha sessão para testes
  • Posso verificar a performance e decidir por publica-las ou deleta-las.

Caso queira publicá-la, fazemos o seguinte:

SQL> EXEC dbms_stats.publish_pending_stats('SYS','TST_PENDING_STATS');
PL/SQL procedure successfully completed.

E tão logo eu publiquei as estatísticas, me conectei em outra sessão para fazer o teste, e “voilá”!!! Sessões novas utilizando o plano novo, devido as novas estatísticas coletadas que dizem para o Oracle que nesse caso é melhor utilizar o índice do que fazer um FTS.

Fiquem a vontade para fazer quaisquer pontuações caso já usado e tenham outros casos de uso para ele.

Essa é a dica 0800 pra voces desta vez.

“Saber das coisas vale ouro. Compartilhar esse conhecimento não tem preço.”

Publicado por jvaldezio

Published

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *