Coleta de Estatísticas: Quanto (%) Coletar?

As opções padrão da coleta de estatísticas (a partir da versão 11.2.0.4) atendem muito bem a vasta maioria dos casos.

A chamada “Coleta FULL”, que é utilizar ESTIMATE_PERCENT em 100%, geralmente é desnecessária, e até prejudicial a partir do 12c.

Eu trabalho com o padrão, e só altero este comportamento na ocorrência de um problema. Alguém me reclamou que o banco está lento, eu chego em um SQL, e vejo que o Oracle está tomando uma decisão errada.

Temos aqui duas tabelas relacionadas, a T1 e T2, com uma boa quantidade de linhas.

SQL> SELECT COUNT(*) FROM T1;

  COUNT(*
----------
  10787584

SQL> SELECT COUNT(*) FROM T2;

  COUNT(*)
----------
  10787584

SQL>

Na tabela T2, temos uma coluna com apenas dois valores distintos, e um destes valores possui uma quantidade de linhas muito pequena em relação à quantidade total.

SQL> SELECT DISTINCT STATUS FROM T2;

STATUS
-------
INVALID
VALID

SQL> SELECT COUNT(*) FROM T2 WHERE STATUS = 'VALID';

  COUNT(*)
----------
  10780416

SQL> SELECT COUNT(*) FROM T2 WHERE STATUS = 'INVALID';

  COUNT(*)
----------
      7168

SQL>

As estatísticas são então coletadas com as opções padrão.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');

PL/SQL procedure successfully completed.

SQL>

E um SELECT com um JOIN entre as duas tabelas utiliza o método Nested Loops e Index Range Scan  (que é mais adequado para uma pequenos Result Sets), pois o Oracle “acha” que só existem 1944 linhas com o valor solicitado na T2.

SQL> SELECT COUNT(T1.OBJECT_ID) FROM T1, T2 WHERE T2.STATUS = 'INVALID' AND T1.OBJECT_ID = T2.OBJECT_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 395103383

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    16 |  3906   (1)| 00:00:47 |
|   1 |  SORT AGGREGATE               |                  |     1 |    16 |            |          |
|   2 |   NESTED LOOPS                |                  |   245K|  3839K|  3906   (1)| 00:00:47 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2               |  1944 | 21384 |    16   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX_STATUS    |  1963 |       |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T1_IDX_OBJECT_ID |   126 |   630 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL>

E porque isto acontece? Quanto mais raro é o valor, mais fácil é obter uma amostragem incorreta.

Mas veja que apenas 10% de coleta já suficiente para ter uma amostragem quase perfeita, mesmo que sejam 7.168 linhas dentro de um universo de 10.787.584. E então, com a informação correta, o Oracle decide que é melhor um Hash Join e um Index Fast Full Scan.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', ESTIMATE_PERCENT=>10);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', ESTIMATE_PERCENT=>10);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(T1.OBJECT_ID) FROM T1, T2 WHERE T2.STATUS = 'INVALID' AND T1.OBJECT_ID = T2.OBJECT_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 640780353

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    16 |  6489   (2)| 00:01:18 |
|   1 |  SORT AGGREGATE               |                  |     1 |    16 |            |          |
|*  2 |   HASH JOIN                   |                  |   948K|    14M|  6489   (2)| 00:01:18 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2               |  7420 | 81620 |    47   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX_STATUS    |  7420 |       |    20   (0)| 00:00:01 |
|   5 |    INDEX FAST FULL SCAN       | T1_IDX_OBJECT_ID |    10M|    51M|  6411   (1)| 00:01:17 |
--------------------------------------------------------------------------------------------------

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

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   4 - access("T2"."STATUS"='INVALID')

SQL>

O que eu faria neste caso seria manter uma coleta com opções padrão para todo o banco (mas em GATHER STALE, ou seja, só do que razoavelmente alterado), mas em seguida uma coleta de exceção (coletando 10%) apenas deste objeto. Para que a coleta principal não afete a especial, as estatísticas do objeto precisam ser bloqueadas.

SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS('RICARDO', 'T2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>'GATHER STALE');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS('RICARDO', 'T2');

PL/SQL procedure successfully completed.

SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS('RICARDO', 'T2', ESTIMATE_PERCENT=>10);

PL/SQL procedure successfully completed.

SQL>

6 comments

  1. Excelente explicação e demonstração. Parabéns, top demais!
    Uma dúvida, após a coleta de 10% percebe-se que o plano foi “ajustado”, reconhecendo as 7168 linhas, e fazendo o “melhor caminho”, porém o tempo estimado de execução aumentou. O Oracle poderia executar a query mais rápida com um “plano errado” (como estava anteriormente)? Caso a coleta de 10%, mesmo estando “correta”, deixasse com um tempo inferior a anterior, seria indicado restaurar a estatística da tabela, mantendo o plano “errado”?

    Muito obrigado!

    1. Muito obrigado pelo comentário e pela excelente pergunta Guilherme.

      O tempo ESTIMADO de execução aumentou sim.
      É como você me perguntar quanto tempo leva para ir até a padaria. Eu digo que é melhor usaro o método ANDANDO (Index Range Scan + Nested Loops), e leva 47 segundos, pois são apenas 1.944 metros até lá. Achei melhor utilizar o método ANDANDO pois é perto.
      Depois pense melhor (ESTIMATE_PERCENT=>10), e agora acho que são 7.420 metros, portanto é melhor usar o método UBER (Index Fast Full Scan + Hash Join), e vai levar 1 minuto e 18 segundos (tem que pedir o Uber, o motorista tem que aceitar, etc.).

      E o tempo real? Aí é outra coisa. Habilitei o SET TIMING ON e refiz o teste. Veja o “Elapsed”:

      SQL> SET TIMING ON
      SQL> SELECT COUNT(T1.OBJECT_ID) FROM T1, T2 WHERE T2.STATUS = 'INVALID' AND T1.OBJECT_ID = T2.OBJECT_ID;
      
      COUNT(T1.OBJECT_ID)
      -------------------
                   917504
      
      Elapsed: 00:00:01.24
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 395103383
      
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                  |     1 |    31 |  2542   (1)| 00:00:31 |
      |   1 |  SORT AGGREGATE               |                  |     1 |    31 |            |          |
      |   2 |   NESTED LOOPS                |                  |   378K|    11M|  2542   (1)| 00:00:31 |
      |   3 |    TABLE ACCESS BY INDEX ROWID| T2               |  2500 | 45000 |    41   (0)| 00:00:01 |
      |*  4 |     INDEX RANGE SCAN          | T2_IDX_STATUS    |  2500 |       |     5   (0)| 00:00:01 |
      |*  5 |    INDEX RANGE SCAN           | T1_IDX_OBJECT_ID |   151 |  1963 |     1   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - access("T2"."STATUS"='INVALID')
         5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
      
      Note
      -----
         - dynamic sampling used for this statement (level=2)
      
      SQL>
      
      
      
      
      
      SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', ESTIMATE_PERCENT=>10);
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:41.75
      SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', ESTIMATE_PERCENT=>10);
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:27.61
      SQL> SELECT COUNT(T1.OBJECT_ID) FROM T1, T2 WHERE T2.STATUS = 'INVALID' AND T1.OBJECT_ID = T2.OBJECT_ID;
      
      COUNT(T1.OBJECT_ID)
      -------------------
                   917504
      
      Elapsed: 00:00:01.15
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 640780353
      
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                  |     1 |    16 |  6268   (2)| 00:01:16 |
      |   1 |  SORT AGGREGATE               |                  |     1 |    16 |            |          |
      |*  2 |   HASH JOIN                   |                  |   890K|    13M|  6268   (2)| 00:01:16 |
      |   3 |    TABLE ACCESS BY INDEX ROWID| T2               |  6970 | 76670 |    45   (0)| 00:00:01 |
      |*  4 |     INDEX RANGE SCAN          | T2_IDX_STATUS    |  7206 |       |    20   (0)| 00:00:01 |
      |   5 |    INDEX FAST FULL SCAN       | T1_IDX_OBJECT_ID |    10M|    51M|  6192   (1)| 00:01:15 |
      --------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
         4 - access("T2"."STATUS"='INVALID')
      
      SQL>
      

Leave a Reply

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.