Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


terça-feira, 21 de novembro de 2017

Otimização: Cursor Sharing, Histogramas, Cursores no Oracle

Por Eduardo Legatti

Olá,

O objetivo deste artigo é demonstrar através de um exemplo prático como um histograma pode ajudar o otimizador do Oracle a encontrar um plano de execução melhor para instruções SQL que acessam tabelas que possuem registros distribuídos de forma não uniforme (skewed distribution) em uma ou mais colunas. Algumas variáveis podem influenciar como o Oracle enxerga uma instrução SQL, tais como o valor atual do parâmetro cursor_sharing, Configurações de NLS da sessão, se a instrução SQL utiliza literal ou bind variables, além de o otimizador fazer uso do Adaptive Cursosr Sharing de forma a avaliar se existe um plano de execução melhor de acordo com os valores das bind variables utilizadas. Essas variações serão tratadas em artigos futuros. Abaixo foi criada uma tabela T1 com cerca de 17 milhões de registros. É possível notar que a coluna ID possui 3 valores distintos (1, 2 e 3) e que os mesmos estão distribuídos de forma não uniforme. Vale a pena salientar que a coluna ID está indexada. Irei coletar estatísticas da tabela T1 sem coletar histogramas para as colunas conforme abaixo.

SQL> select id,count(*) from t1 group by id order by 1;

        ID   COUNT(*)
---------- ----------
         1   16777216
         2     262144
         3         64

SQL> exec dbms_stats.gather_table_stats(
 2   ownname=>'SCOTT',
 3   tabname=>'T1',
 4   cascade => true,
 5   METHOD_OPT => 'for all columns size 1');

Procedimento PL/SQL concluído com sucesso.

Abaixo, podemos ver que o parâmetro cursor_sharing está configurado para EXACT (default) o que significa que somente instruções SQL com textos idênticos serão compartilhadas para reutilização.

SQL> show parameter cursor_sharing;

NAME                         TYPE          VALUE
--------------------------- ------------- ---------------------
cursor_sharing              string        EXACT

Após coletar estatísticas da tabela (sem histogramas), irei executar três consultas abaixo. Vale a pena salientar que as instruções se diferenciam textualmente pelo valor da coluna ID que é passada na cláusula WHERE, ou seja, elas não são idênticas.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Consultando a view dinâmica de desempenho V$SQLAREA que mostra os PARENT CURSORS de todas as instruções SQL executadas, podemos ver que cada instrução SQL possui um SQL_ID/HASH_VALUE diferentes, exatamente porque os literais passados na cláusula WHERE na coluna ID são diferentes. Podemos ver também que a coluna VERSION_COUNT mostra o valor 1  o que significa que cada PARENT CURSOR criou apenas um CHILD CURSOR para cada consulta. É importante lembrar que cada PARENT CURSOR (V$SQLAREA) deverá sempre ter no mínimo 1 CHILD CURSOR (V$SQL).

SQL> SELECT sql_id,
  2         hash_value,
  3         version_count,
  4         executions,
  5         parsing_schema_name,
  6         module,
  7         last_active_time,
  8         is_bind_sensitive,
  9         is_bind_aware,
 10         sql_profile,
 11         sql_text
 12    FROM V$SQLAREA
 13   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 14         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE VERSION_COUNT EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025             1          1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993             1          1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Consultando a view dinâmica de desempenho V$SQL que mostra os CHILD CURSORS de todas as instruções SQL executadas, podemos ver que o Oracle gerou o mesmo plano de execução (PLAN_HASH_VALUE) para as 3 consultas.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 0000000092188610      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:56:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 000000009F85B2D8      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:18 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 00000000944BC930      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 10:55:15 N N            select count(object_name) from t1 where id=1

Irei gerar um explain plan da consulta abaixo de forma que verificar qual plano de execução foi gerado para as consultas SQL.

SQL> explain plan for select count(object_name) from t1 where id=1;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 | 55703   (1)| 00:11:09 |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5679K|    54M| 55703   (1)| 00:11:09 |
---------------------------------------------------------------------------

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

   2 - filter("ID"=1)

14 linhas selecionadas.

Pelo plano de execução mostrado acima, podemos ver que o otimizador resolveu fazer um Full Table Scan na tabela T1. Para o valor de ID=1 está ótimo, porque este valor está em 98% dos registros da tabela. No entanto, esse plano de execução é horrível para os valores 2 e 3 que teria melhor performance se fizesse uso do índice criado na coluna ID. A questão é saber porque o Oracle não utilizou o índice? A resposta é porque o Oracle não sabe a distribuição dos valores na coluna ID. Por exemplo, ele não sabe que 98% dos valores são 1 e que os outros 2% são 1 e 2. Pelo resultado da consulta abaixo é possível perceber que o valor da coluna DENSITY é 33%, ou seja, o Oracle sabe que existem 3 valores distintos na coluna ID (NUM_DISTINCT=3), no entanto, ele enxerga de forma uniforme a distribuição dos valores na coluna, ou seja, cerca de 5,6 milhões de linhas para cada ID.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               1              3
SCOTT        T1              ID               NUMBER                3 ,333333333 NONE                      1 17/11/2017 10:41:12               0              1

Agora irei criar um histograma para a coluna ID de forma que o otimizador possa enxergar a não uniformidade dos valores armazenadas na coluna.

SQL> exec dbms_stats.gather_table_stats(
  2  ownname=>'SCOTT',
  3  tabname=>'T1',
  4  cascade => true,
  5  METHOD_OPT => 'for columns ID');

Procedimento PL/SQL concluído com sucesso.

SQL> SELECT a.owner,
  2         a.table_name,
  3         a.column_name,
  4         a.data_type,
  5         a.num_distinct,
  6         a.density,
  7         a.histogram,
  8         a.num_buckets,
  9         a.last_analyzed,
 10         b.endpoint_number,
 11         b.endpoint_value
 12  FROM dba_tab_columns a, dba_tab_histograms b, dba_tab_col_statistics c
 13  WHERE a.owner = b.owner(+)
 14  AND a.table_name = b.table_name(+)
 15  AND a.column_name = b.column_name(+)
 16  AND b.owner = c.owner(+)
 17  AND b.table_name = c.table_name(+)
 18  AND b.column_name = c.column_name(+)
 19  AND a.owner='SCOTT'
 20  AND a.table_name='T1'
 21  AND a.column_name='ID';

OWNER        TABLE_NAME      COLUMN_NAME      DATA_TYPE  NUM_DISTINCT    DENSITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- ---------------- ---------- ------------ ---------- --------------- ----------- ------------------- --------------- --------------
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5262              1
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5343              3
SCOTT        T1              ID               NUMBER                3 3,0206E-08 FREQUENCY                 3 17/11/2017 11:28:47            5342              2

Após executada a coleta de estatísticas incluindo a criação de histograma (FREQUENCY), irei executar novamente as 3 consultas SQL.

SQL> select count(object_name) from t1 where id=1;

COUNT(OBJECT_NAME)
------------------
          16777216

SQL> select count(object_name) from t1 where id=2;

COUNT(OBJECT_NAME)
------------------
            262144

SQL> select count(object_name) from t1 where id=3;

COUNT(OBJECT_NAME)
------------------
                64

Fazendo novamente a consulta na view V$SQL, é possível observar que o plano de execução (PLAN_HASH_VALUE) das consultas que utilizam os IDs 2 e 3 mudaram.

SQL> SELECT sql_id,
  2         hash_value,
  3         child_number,
  4         child_address,
  5         plan_hash_value,
  6         optimizer_mode,
  7         executions,
  8         parsing_schema_name,
  9         module,
 10         last_active_time,
 11         is_bind_sensitive,
 12         is_bind_aware,
 13         sql_profile,
 14         sql_text
 15    FROM V$SQL
 16   WHERE     LOWER (SQL_TEXT) LIKE 'select count(object_name) from t1%'
 17         AND LOWER (SQL_TEXT) NOT LIKE '%HASH%';

SQL_ID           HASH_VALUE CHILD_NUMBER CHILD_ADDRESS    PLAN_HASH_VALUE OPTIMIZER_ EXECUTIONS PARSING_SC MODULE     LAST_ACTIVE_TIME    I I SQL_PROFIL SQL_TEXT
---------------- ---------- ------------ ---------------- --------------- ---------- ---------- ---------- ---------- ------------------- - - ---------- --------------------------------------------
d6jg3h82uc7t1      94773025            0 000000009F8DED10      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:25 N N            select count(object_name) from t1 where id=3
84nk5292j0umb    1158703723            0 0000000098978A30      1284813898 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:22 N N            select count(object_name) from t1 where id=2
1wgjtthkcftxt     617047993            0 000000009F8CA890      3724264953 ALL_ROWS            1 SCOTT      SQL*Plus   17/11/2017 11:40:14 N N            select count(object_name) from t1 where id=1

Para finalizar, irei gerar um novo explain plan da consulta que utiliza o ID=3 de forma a verificar qual plano de execução foi gerado pelo otimizador do Oracle.

SQL> explain plan for select count(object_name) from t1 where id=3;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1284813898

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    10 |    52   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |  3189 | 31890 |    52   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_ID |  3189 |       |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("ID"=3)

15 linhas selecionadas.

Pronto. Pelo resultado acima podemos ver que o otimizador escolheu o acessar os dados através do índice IDX_T1_ID, ou seja, o histograma foi responsável por ajudar o otimizador a verificar que os valores da coluna ID não estavam com a distribuição uniforme e encontrou um plano de acesso mais performático para a instrução SQL com ID=3.




Nenhum comentário:

Postagens populares