Dynamic Statistics

As Estatísticas Dinâmicas (Dynamic Statistics), controladas pelo parâmetro OPTIMIZER_DYNAMIC_SAMPLING, fazem com que o Oracle Database faça uma rápida coleta de estatísticas, imediatamente antes da execução de um comando SQL, durante a fase de otimização. A ideia desta funcionalidade é que mesmo uma coleta bem limitada de estatísticas é melhor do que nenhuma.
O parâmetro OPTIMIZER_DYNAMIC_SAMPLING possui diversos níveis, sendo que o nível 0 significa que a coleta dinâmica está desabilitada, e no nível 2, haverá coleta se pelo menos uma das tabelas envolvida no comando SQL não tiver estatística nenhuma.
Estas funcionalidade existe desde a versão 9i, e na versão 10g passou a ter como padrão o nível 2.
Até o Patchset 11.2.0.3, os níveis iam de 0 a 10, e no Patchset 11.2.0.4 foi criado o nível 11, que deixa a decisão do critério de coleta toda com o Oracle, ao invés de obedecer a regra dos níveis manuais. Esta alteração mostra a direção do fabricante em tornar o Oracle cada vez mais automatizado.

Vejamos então como o Dynamic Statistics funciona.

No usuário SCOTT, em tenho 5 tabelas (uma delas bem grande), e todas estão com estatísticas.

Microsoft Windows [versão 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. Todos os direitos reservados.

C:UsersRicardo>sqlplus SCOTT/TIGER

SQL*Plus: Release 11.2.0.3.0 Production on Seg Out 7 10:48:26 2013

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

Conectado a:
Personal Oracle Database 11g Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET PAGES 1000
SQL> SET LINES 210
SQL> SELECT TABLE_NAME, BLOCKS, EMPTY_BLOCKS, NUM_ROWS, SAMPLE_SIZE FROM USER_TABLES;

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- ------------ ---------- -----------
DEPT                                    5            0          4           4
EMP                                     5            0         14          14
BONUS                                   0            0          0           0
SALGRADE                                5            0          5           5
T                                   27459            0    1880544     1880544

SQL> SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_INDEXES;

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ----------- ------------- -----------------
T                              IDX_T                                   2        7201            24          71821
EMP                            PK_EMP                                  0           1            14         1
DEPT                           PK_DEPT                                 0           1             4         1

SQL>

Agora eu apago as estatísitcas de todo o SCHEMA.

SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Procedimento PL/SQL concluÝdo com sucesso.

SQL> SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_INDEXES;

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ----------- ------------- -----------------
T                              IDX_T
EMP                            PK_EMP
DEPT                           PK_DEPT

SQL> SELECT TABLE_NAME, BLOCKS, EMPTY_BLOCKS, NUM_ROWS, SAMPLE_SIZE FROM USER_TABLES;

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- ------------ ---------- -----------
DEPT
EMP
BONUS
SALGRADE
T

SQL>

Conferindo que o Dynamic Statistics está desabilitado, eu verifico o úmero total de linhas da tabela, e depois o plano de execução de dois SQLs.
Um dos SQLs retorna 136160 linhas, e o segundo 896864 (quase metade da tabela), de um total de 1880544.
Nos dois planos de execução, é escolhido o método de INDEX RANGE SCAN. Veja que a estimativa de Rows e Bytes para os dois SQLs é a mesma, o que obviamente está incorreto.

SQL> SHOW PARAMETER DYNAMIC

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     0
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   1880544

SQL> SET AUTOTRACE ON
SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE = 'VIEW';

COUNT(OBJECT_TYPE)
------------------
            136160

Plano de ExecuþÒo
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T | 22429 |   240K|     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='VIEW')

EstatÝsticas
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        463  consistent gets
        419  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE = 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
            896864

Plano de ExecuþÒo
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T | 22429 |   240K|     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='SYNONYM')

EstatÝsticas
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3223  consistent gets
       3222  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Agora eu altero o OPTIMIZER_DYNAMIC_SAMPLING para 2 (apenas na sessão, mas poderia ser para toda a instância), e como resultado o segundo SQL passa a utilizar um INDEX FAST FULL SCAN, e a estimativa de Rows nos dois casos ficou muito mais próxima da realidade, embora ainda imprecisas.

SQL> ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 2;

SessÒo alterada.

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE = 'VIEW';

COUNT(OBJECT_TYPE)
------------------
            136160

Plano de ExecuþÒo
----------------------------------------------------------
Plan hash value: 1500240790

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    11 |   514   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE   |       |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T |   115K|  1236K|   514   (1)| 00:00:07 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='VIEW')

Note
-----
   - dynamic sampling used for this statement (level=2)

EstatÝsticas
----------------------------------------------------------
         10  recursive calls
          1  db block gets
        685  consistent gets
        307  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE = 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
            896864

Plano de ExecuþÒo
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    11 |  1991   (1)| 00:00:24 |
|   1 |  SORT AGGREGATE       |       |     1 |    11 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T |   757K|  8142K|  1991   (1)| 00:00:24 |
-------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='SYNONYM')

Note
-----
   - dynamic sampling used for this statement (level=2)

EstatÝsticas
----------------------------------------------------------
          8  recursive calls
          3  db block gets
       7758  consistent gets
       5009  physical reads
          0  redo size
        442  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Sobre a nova opção 11 para o OPTIMIZER_DYNAMIC_SAMPLING, aqui eu tento altera-la no Patchset 11.2.0.3, e realmente ela ainda não era reconhecida.

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING = 11;
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING = 11
*
ERRO na linha 1:
ORA-00068: valor 11 invßlido para o parâmetro optimizer_dynamic_sampling; deve estar entre 0 e 10

SQL>

E no Patchset 11.2.0.4, a opção 11 funciona.

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER SYSTEM SET optimizer_dynamic_sampling = 11;

System altered.

SQL>

Documentação Dynamic Statistics

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.