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:
Postar um comentário