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>