Como travar seu banco com ANALYZE

O uso do comando ANALYZE TABLE … COMPUTE STATISTICS é depreciado. Para a coleta de estatisticas, deve ser utilizado o DBMS_STATS.

Entretanto, este comando ainda tem seu uso, e um deles é saber se há espaço livre na tabela causado por DELETEs (o que pode ser verificado de outra forma). Entretanto, seu uso causa um efeito colateral desastroso em um ambiente sério.

Nesta tabela eu tenho muito mais linhas com o valor TABLE do que JAVA CLASS em determinada coluna.

10:04:20 RICARDO@CATALOGO> SET AUTOTRACE OFF
10:04:32 RICARDO@CATALOGO> SELECT COUNT(OBJECT_ID) FROM T1 WHERE OBJECT_TYPE = 'TABLE';

COUNT(OBJECT_ID)
----------------
           50688

Elapsed: 00:00:00.02
10:05:11 RICARDO@CATALOGO> SELECT COUNT(OBJECT_ID) FROM T1 WHERE OBJECT_TYPE = 'JAVA CLASS';

COUNT(OBJECT_ID)
----------------
         1202016

Elapsed: 00:00:01.54
10:05:22 RICARDO@CATALOGO>

Então eu faço uma coleta de estatisticas com o comando DBMS_STATS, e confiro que os planos de execução são diferentes para consultas em cada um dos valores, o que é bom – seria mais custoso utilizar o índice no segundo caso, que lê quase metade da tabela.

10:05:22 RICARDO@CATALOGO> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.53
10:06:26 RICARDO@CATALOGO> SET AUTOTRACE TRACEONLY EXPLAIN
10:06:43 RICARDO@CATALOGO> SELECT OBJECT_ID FROM T1 WHERE OBJECT_TYPE = 'TABLE';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 614934925

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          | 50688 |   742K|  2371   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       | 50688 |   742K|  2371   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX01 | 50688 |       |   180   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

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

10:06:54 RICARDO@CATALOGO> SELECT OBJECT_ID FROM T1 WHERE OBJECT_TYPE = 'JAVA CLASS';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1202K|    17M| 11058   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1202K|    17M| 11058   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_TYPE"='JAVA CLASS')

10:07:01 RICARDO@CATALOGO>

Agora eu coleto as estatísticas com ANALYZE, e verifico os planos novamente. Veja que o otimizador passa a considerar que há a mesma quantidade de linhas tanto para TABLE quanto JAVA CLASS, e utiliza o mesmo plano de execução para os dois SELECTs.

10:07:01 RICARDO@CATALOGO> ANALYZE TABLE T1 COMPUTE STATISTICS;

Table analyzed.

Elapsed: 00:01:51.80
10:09:03 RICARDO@CATALOGO> SELECT OBJECT_ID FROM T1 WHERE OBJECT_TYPE = 'TABLE';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 614934925

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          | 76151 |   966K|  3331   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       | 76151 |   966K|  3331   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX01 | 76151 |       |   270   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

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

10:10:51 RICARDO@CATALOGO> SELECT OBJECT_ID FROM T1 WHERE OBJECT_TYPE = 'JAVA CLASS';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 614934925

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          | 76151 |   966K|  3331   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       | 76151 |   966K|  3331   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX01 | 76151 |       |   270   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='JAVA CLASS')

10:10:57 RICARDO@CATALOGO>

O motivo para isto é que o ANALYZE não coleta histogramas, que é exatamente a funcionalidade que permite ao otimizador saber que há mais linhas com um valor do que outro.

Abaixo eu verifico que nenhuma das colunas da tabela tem histograma, após o ANALYZE. E em seguida, executo a coleta com DBMS_STATS, e passa a ser considerado um histograma na coluna em questão.

10:10:57 RICARDO@CATALOGO> SET AUTOTRACE OFF
10:12:08 RICARDO@CATALOGO> SELECT COLUMN_NAME, HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T1';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OWNER                          NONE
OBJECT_NAME                    NONE
SUBOBJECT_NAME                 NONE
OBJECT_ID                      NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                    NONE
CREATED                        NONE
LAST_DDL_TIME                  NONE
TIMESTAMP                      NONE
STATUS                         NONE
TEMPORARY                      NONE
GENERATED                      NONE
SECONDARY                      NONE
NAMESPACE                      NONE
EDITION_NAME                   NONE
SHARING                        NONE
EDITIONABLE                    NONE
ORACLE_MAINTAINED              NONE
APPLICATION                    NONE
DEFAULT_COLLATION              NONE
DUPLICATED                     NONE
SHARDED                        NONE
CREATED_APPID                  NONE
CREATED_VSNID                  NONE
MODIFIED_APPID                 NONE
MODIFIED_VSNID                 NONE

26 rows selected.

Elapsed: 00:00:01.07
10:13:00 RICARDO@CATALOGO> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.88
10:14:36 RICARDO@CATALOGO> SELECT COLUMN_NAME, HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'T1';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OWNER                          NONE
OBJECT_NAME                    NONE
SUBOBJECT_NAME                 NONE
OBJECT_ID                      NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                    FREQUENCY
CREATED                        NONE
LAST_DDL_TIME                  NONE
TIMESTAMP                      NONE
STATUS                         NONE
TEMPORARY                      NONE
GENERATED                      NONE
SECONDARY                      NONE
NAMESPACE                      NONE
EDITION_NAME                   NONE
SHARING                        NONE
EDITIONABLE                    NONE
ORACLE_MAINTAINED              NONE
APPLICATION                    NONE
DEFAULT_COLLATION              NONE
DUPLICATED                     NONE
SHARDED                        NONE
CREATED_APPID                  NONE
CREATED_VSNID                  NONE
MODIFIED_APPID                 NONE
MODIFIED_VSNID                 NONE

26 rows selected.

Elapsed: 00:00:00.01
10:14:43 RICARDO@CATALOGO>

Em que tipo de tabelas o DBA mal informado irá querer verificar a respeito de fragmentação? Provavelmente nas maiores. E é justamente nas maiores tabelas onde este impacto será potencialmente maior.

10 comments

  1. Recentemente em uma visita a uma grande empresa do ramo financeiro, solicitei que efetuasse a coleta de estatísticas em Oracle 11g. Posteriormente ao conferir as informações, percebi que tinha sido feito pelo Analyze. Questionei o responsável e o mesmo informou que já era prática de anos a realização pelo Analyze e estava no dicionário de procedimentos da empresa e que o dbms_stats não havia sido homologado pela instituição. Doeu o coração!

    1. 😀
      Se estiver no dicionário de procedimentos que deve ser feito backup com exp, certamente ainda estarão fazendo também.

  2. Eu utilizo o DBMS_STATS, mais o detalhe da não coleta de histogramas pelo ANALYZE eu não havia percebido. Se tivesse verificado a USER_TAB_COLUMNS, detectaria o problema.

    Valeu !!!

    1. Realmente só encontrei esta situação na vida real, não encontrei isto explícito na documentação, veja como ela é vaga neste ponto:
      “For the collection of most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways.”

  3. Bom dia,
    Excelente post. Só uma dúvida, quando eu executo o exec dbms_sqltune.execute_tuning_task, para dignositcar algum sql_id que por alguma maneira estava lento ou identifiquei entre meus eventos primordiais que acho que vale apena analisar, vejo que é utilizado um Analyse embutido. Seria o mesmo não?

    Luiz Marinho

    1. Oi Luiz.
      Poderia ser mais específico com “eventos primordiais”? Você executou o SQLTUNE em Trace, ou viu o ANALYZE ocorrer pela V$SESSION / V$SESSION_WAIT / V$SQL ?

      1. Bom dia Ricardo,
        Então. Todavia que eu vejo algum problema de performance em determinado sql_id eu examino

        DECLARE
        l_sql_tune_task_id VARCHAR2(100);
        BEGIN
        l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
        begin_snap => 50468,
        end_snap => 52671,
        sql_id => ‘8f3kw92tpzr2b’,
        scope => DBMS_SQLTUNE.scope_comprehensive,
        time_limit => 7200,
        task_name => ”,
        description => ‘Diagnosticando_Tuning Performance’);
        DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
        END;
        Quando executo o meu task abaixo ai que posso ver no gv$session_longops que é utilizado um ANALYZE.
        exec dbms_sqltune.execute_tuning_task(task_name => ”);

        Obrigado pela atenção
        Luiz Marinho

        Obs: Quando refirome aos eventos são estes: db file sequential read
        db file scattered read
        direct path read
        Mas sei que estarei ai no próximo curso para apreender mais a diagnosticar problemas de performance com esta empresa especializada.

        1. Ah, vou testar e ver se este ANALYZE é executado na tabela utilizada pelo SQL analisado pelo SQL_TUNE, ou em uma tabela auxiliar.

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.