No artigo "Por que o Otimizador criou um plano de execução ineficiente?" elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos abordar com mais detalhes um desses tópicos, como a repetição de dados não uniformes em uma coluna da tabela podem influenciar…
Datatype numérico e carácter, será que existem outros?
Algumas pessoas vão ler o título deste artigo e pensar que ele foi feito para leigos em tecnologia da informação, mas quem trabalha com banco de dados sabe que muitas aplicações que existem no mercado utilizam somente os Datatypes numérico e carácter, esta prática afeta negativamente o desempenho da aplicação pois o Otimizador não tem como deduzir, por exemplo, que a aplicação esta usando um campo numérico para armazenar datas.
Neste artigo vamos simular uma situação onde um campo numérico é carregado com datas e verificar porque o Otimizador fica vendido nesta situação e cria um plano de execução com baixo desempenho.
Para esta simulação prática vamos passar pelas seguintes etapas:
1) Criar uma tabela onde o campo DT_CAD é do tipo numérico
2) Executar uma consulta na tabela criada utilizando o campo DT_CAD na cláusula WHERE
3) Recriar a tabela da etapa 1 alterando o campo DT_CAD para o tipo DATA
4) Executar a mesma consulta novamente com campo DT_CAD na cláusula WHERE
1) Criar uma tabela onde o campo DT_CAD é numérico
Vamos criar os objetos necessários para realizar a nossa simulação.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SQL > SQL > CREATE TABLE dbtw061 (matr number, cod_cid number, dt_cad number, descr varchar(100)); Tabela criada. SQL > SQL > INSERT INTO dbtw061 2 SELECT rownum, 3 MOD(rownum,1000), 4 TO_NUMBER(TO_CHAR(sysdate-TRUNC(DBMS_RANDOM.VALUE(0,10000)), 'YYYYMMDD')), 5 DBMS_RANDOM.STRING('A', 100) 6 FROM dual 7 CONNECT BY level <=1000000; 1000000 linhas criadas. SQL > SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > CREATE INDEX dbtw061_matr_idx ON dbtw061(matr); Índice criado. SQL > CREATE INDEX dbtw061_dt_cad_idx on dbtw061(dt_cad); Índice criado. SQL > SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> USER, tabname=> 'DBTW061', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16); Procedimento PL/SQL concluído com sucesso. SQL > |
2) Executar uma consulta com o campo DT_CAD na cláusula WHERE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | SQL > SELECT /* dbtw-061.2 */ * 2 FROM dbtw061 3 WHERE dt_cad BETWEEN 19991231 and 20000101; MATR COD_CID DT_CAD DESCR ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 2239 239 19991231 GsraCoSnqAbFoxmqzKmWplZFWMcvSKqsGcRyuhVJIwFoZFXnQfduEiqdEUWVyJgxdsEifIAHHrKPqwtnsGqXthWLwnTsrCGmpJXF 8676 676 19991231 UwRSVzGqpeALcbxsvfEUQBeWyjBBQveZdNsghrtywdzvzlGxflymDdHSfTUlulPSssrJSqqrIEPQnkUdUAoTYmsJhMSbcJRoWrES 8127 127 20000101 kUPMpafQiRacIGnOdzcUGnMkwGnqdxQmrDPKDrDtcmsgbjSQHvMxIMVgbDDTYvDaVpnLyrIGmVAhXfAECAnmKlTSGSebmHTxcgrZ . . . 984189 189 20000101 CkRBQEPmoBhWKhbxAkKOBjTdQJiBgptyRRRDpQEDtpLyaaeIvZUeWyOTtzDwuJkFSyaAygBuaVMbJxmLiDgCoIRKRUAkapJMolyZ 206 linhas selecionadas. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-061.2%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 1ngp47mwuk766 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('1ngp47mwuk766', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 1ngp47mwuk766, child number 0 ------------------------------------- SELECT /* dbtw-061.2 */ * FROM dbtw061 WHERE dt_cad BETWEEN 19991231 and 20000101 Plan hash value: 2108755988 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 206 |00:00:00.06 | 16755 | |* 1 | TABLE ACCESS FULL| DBTW061 | 1 | 32997 | 206 |00:00:00.06 | 16755 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("DT_CAD"<=20000101 AND "DT_CAD">=19991231)) 19 linhas selecionadas. SQL > SQL > SQL > ------------------------------------------------------------------- SQL > -- SELETIVIDADE = (vval2-vval1)/((maxval-minval)+(2 * Density)) SQL > ------------------------------------------------------------------- SQL > SQL > SELECT MIN(dt_cad) min, 2 MAX(dt_cad) max 3 FROM dbtw061; MIN MAX ---------- ---------- 19920308 20190724 SQL > SQL > SELECT column_name, 2 density 3 FROM user_tab_cols 4 WHERE table_name='DBTW061' 5 AND column_name='DT_CAD'; COLUMN_NAME DENSITY --------------- ---------- DT_CAD ,000097694 SQL > SQL > SELECT (20000101-19991231)/(20190724-19920308)+(2*0.000097694) SELETIVIDADE 2 FROM dual; SELETIVIDADE ------------- ,032996702 SQL > SQL > ------------------------------------------------------------------- SQL > -- CARDINALIDADE = SELECTIVIDADE * NUM LINHAS DA TABELA SQL > ------------------------------------------------------------------- SQL > SQL > SELECT ROUND(0.032996702*1000000) CARDINALIDADE 2 FROM dual; CARDINALIDADE ------------- 32997 SQL > |
Ajudo DBAs e analistas de sistema a se destacarem em suas empresas
e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:
Observe no plano de execução que o Otimizador estimou que seriam selecionadas 32997 linhas na tabela e por isso escolheu fazer uma operação TABLE ACCESS FULL, mas na verdade foram selecionadas apenas 206 linhas, devido a esse erro de estimativa o Otimizador deixou de usar o índice que seria a melhor opção para selecionar 206 linhas na tabela.
Após o plano de execução podemos observar os cálculos realizados pelo Otimizador para estimar o numero de linhas que seriam lidos na tabela, o algoritmo do Otimizador acaba fornecendo um resultado errado pois faz uma subtração numérica dos valores máximo e minimo na tabela, bem como a subtração do intervalo fornecido na cláusula WHERE, porem o resultado da subtração de campos do tipo numérico são diferentes dos resultados de subtração de campos do tipo DATA, e no nosso caso apesar do tipo do campo ser numérico o conteúdo desse campo é data.
3) Recriar a tabela alterando o campo DT_CAD
Agora vamos recriar a tabela alterando o campo DT_CAD para o tipo DATA e verificar se o Otimizador é capaz de criar um plano de execução melhor.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | SQL > DROP TABLE dbtw061; Tabela eliminada. SQL > SQL > CREATE TABLE dbtw061 (matr number, cod_cid number, dt_cad date, descr varchar(100)); Tabela criada. SQL > SQL > INSERT INTO dbtw061 2 SELECT rownum, 3 MOD(rownum,1000), 4 TRUNC(sysdate-TRUNC(DBMS_RANDOM.VALUE(0,9999))), 5 DBMS_RANDOM.STRING('A', 100) 6 FROM dual 7 CONNECT BY level <=1000000; 1000000 linhas criadas. SQL > SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > CREATE INDEX dbtw061_matr_idx ON dbtw061(matr); Índice criado. SQL > CREATE INDEX dbtw061_dt_cad_idx on dbtw061(dt_cad); Índice criado. SQL > SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> USER, tabname=> 'DBTW061', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16); Procedimento PL/SQL concluído com sucesso. SQL > |
4) Executar a mesma consulta novamente
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | SQL > SELECT /* dbtw-061.3 */ * 2 FROM dbtw061 3 WHERE dt_cad BETWEEN to_date('19991231','yyyymmdd') and to_date('20000101','yyyymmdd'); MATR COD_CID DT_CAD DESCR ---------- ---------- -------- ---------------------------------------------------------------------------------------------------- 360801 801 31/12/99 ZkPmOyWkbcopuUumsNeUnzFnOgoIUxcBXNhkshyeQSLwAyrqbguIAQYnQDvjwylzqWanBsRIfqRoxwepNiQUOBpoRdUpgjdGolkC 368200 200 31/12/99 LouQSxQRimDBTBWeLVFyOqUuhXAryLDptcYkEYaFJJQwWRoejpToXxftLvHRJTHcOcmXZwjVfWvlHIYRMTgWJjjFXfCNrhhYlCUw 365402 402 31/12/99 uUDeJYLmLfPwsStjVsnfxHjHLasvPsSEfVTFCUBnwYXSRkaxuAqIRrsGuwZYQeqrAiBumuXSQLyOrKEGajDgjLKaRseTyrfxGYND . . . 52714 714 01/01/00 tSSQcxTRqeFriPpPcJehJpKMYiVwopfTUOrszyotZlCVRmCoFGDodKxRBnMQnykLswjiNdIBvgCacNBdpYlRzckVCDJnxYERilDe 179 linhas selecionadas. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-061.3%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ fbx73g4dgzv04 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('fbx73g4dgzv04', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID fbx73g4dgzv04, child number 0 ------------------------------------- SELECT /* dbtw-061.3 */ * FROM dbtw061 WHERE dt_cad BETWEEN to_date('19991231','yyyymmdd') and to_date('20000101','yyyymmdd') Plan hash value: 1720299405 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 179 |00:00:00.01 | 193 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW061 | 1 | 300 | 179 |00:00:00.01 | 193 | |* 2 | INDEX RANGE SCAN | DBTW061_DT_CAD_IDX | 1 | 300 | 179 |00:00:00.01 | 15 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DT_CAD">=TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT_CAD"<=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 21 linhas selecionadas. SQL > SQL > ------------------------------------------------------------------- SQL > -- SELETIVIDADE = (vval2-vval1)/((maxval-minval)+(2 * Density)) SQL > ------------------------------------------------------------------- SQL > SQL > SELECT MIN(dt_cad) min, 2 MAX(dt_cad) max 3 FROM dbtw061; MIN MAX -------- -------- 09/03/92 24/07/19 SQL > SQL > SELECT column_name, 2 density 3 FROM user_tab_cols 4 WHERE table_name='DBTW061' 5 AND column_name='DT_CAD'; COLUMN_NAME DENSITY -------------- ---------- DT_CAD ,00009984 SQL > SQL > SQL > SELECT (to_date('20000101','yyyymmdd')-to_date('19991231','yyyymmdd'))/(to_date('20190723','yyyymmdd')-to_date('19920308','yyyymmdd'))+(2*0.00010001) SELETIVIDADE 2 FROM dual; SELETIVIDADE ------------ ,00030004 SQL > SQL > ------------------------------------------------------------------- SQL > -- CARDINALIDADE = SELETIVIDADE * NUM LINHAS DA TABELA SQL > ------------------------------------------------------------------- SQL > SQL > SELECT ROUND(0.00030004*1000000) CARDINALIDADE 2 FROM dual; CARDINALIDADE ------------- 300 SQL > |
Observe no plano de execução que o Otimizador desta vez estimou que seriam lidas 300 linhas da tabela, valor bem próximo da quantidade de linhas selecionadas ao final da consulta (179 linhas), e como o Otimizador estimou uma pequeno número de linhas ele decidiu fazer a leitura da tabela utilizando índice, com isso o desempenho da consulta foi muito superior ao da primeira consulta executada na etapa 2.
Logo abaixo do plano de execução temos os cálculos realizados pelo Otimizador para estimar a leitura de 300 linhas da tabela.
CONCLUSÃO
Os tipos de campos que definimos para os dados que vão popular as tabelas, além de servir para validar as informações inseridas no banco de dados, servem para aumentar a precisão dos algoritmos utilizados pelo Otimizador que por sua vez é capaz de gerar planos de execução com ótimo desempenho. Quando decidimos ignorar a utilização dos tipos de campos adequados aos dados da tabela estamos sabotando o desempenho da aplicação.
Referências
https://docs.oracle.com/database/121/
