As estatísticas sobre os dados de uma tabela são fundamentais para o Otimizador escolher qual a maneira mais eficiente de acessar esses dados, mas existem informações sobre os dados que não são coletadas de forma automática e portanto não estão disponíveis para o Otimizador sem que haja uma intervenção manual,…
Anabolizante para o Otimizador
O Otimizador do Oracle cria planos de execução com alta performance baseado nas estatísticas dos objetos no banco de dados, com estas informações ele consegue fazer estimativas de acesso para cada uma das operações do plano de execução e a qualidade destas estimativas é que determina o desempenho das instruções SQL. O padrão de coleta de estatísticas é baseado em cada coluna de uma tabela, este padrão atende bem a necessidade do Otimizador quando ele cria um plano de execução de uma instrução SQL cujo filtro na cláusula WHERE é composto de apenas uma coluna da tabela, porem quando a consulta possui duas ou mais colunas da tabela no filtro da cláusula WHERE as estatísticas individuais de colunas da tabela não garantem uma precisão adequada no cálculo da CARDINALIDADE da operação e pode levar o Otimizador a montar uma plano de execução cujo desempenho fique abaixo do esperado. O banco de dados Oracle permite a utilização de um processo anabolizante que vai melhorar a qualidade das estatísticas e ampliar a precisão do Otimizador na estimativa da CARDINALIDADE das operações no plano de execução.
Neste artigo vamos mostrar um exemplo prático de um processo que permite a criação de ESTATÍSTICAS ESTENDIDAS no atacado, ou seja, o Oracle vai monitorar as instruções SQL executadas num determinado período e baseado nos filtros utilizados na cláusula WHERE destas instruções ele vai criar todas as ESTATÍSTICAS ESTENDIDAS necessárias para melhorar a estimativa de CARDINALIDADE da operações realizadas pelo Otimizador.
Para realizar a simulação do processo de coleta de estatísticas para grupos de colunas vamos executar as seguintes etapas:
1) Criar as tabelas e índices para a simulação
2) Executar a consulta sem a estatística de grupo de colunas
3) Ativar monitoração de grupo de colunas utilizadas como filtro
4) Executar consulta que utiliza grupo de colunas como filtro
5) Gerar relatório para verificar os grupos de colunas identificados
6) Criar as estatísticas dos grupos de colunas identificados
7) Executar a consulta novamente para verificar a melhoria no plano de execução
1) Criar as tabelas e índices para a simulação
Vamos criar os objetos necessários para realizar a nossa simulação e coletar as estatísticas destes objetos.
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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 1 linha selecionada. SQL > SQL > DROP TABLE tab01 purge; Tabela eliminada. SQL > DROP TABLE tab02 purge; Tabela eliminada. SQL > DROP TABLE tab03 purge; Tabela eliminada. SQL > SQL > CREATE TABLE tab01 2 AS 3 SELECT rownum col01, 4 MOD(level, 20) col02, 5 MOD(level, 200) col03, 6 MOD(level,1000) col04, 7 lpad('x',40,'x') col05 8 FROM dual 9 CONNECT BY level <= 10000 10 / Tabela criada. SQL > SQL > CREATE TABLE tab02 2 AS 3 SELECT rownum col01, 4 MOD(level, 20) col02, 5 MOD(level, 200) col03, 6 MOD(level,1000) col04, 7 lpad('y',40,'y') col05 8 FROM dual 9 CONNECT BY level <= 10 10 / Tabela criada. SQL > SQL > CREATE TABLE tab03 2 AS 3 SELECT rownum col01, lpad('z',40,'z') col02 4 FROM dual 5 CONNECT BY level <= 10000 6 / Tabela criada. SQL > SQL > EXEC dbms_stats.gather_table_stats(USER,'tab01',method_opt=>'for all columns size auto'); Procedimento PL/SQL concluído com sucesso. SQL > EXEC dbms_stats.gather_table_stats(USER,'tab02',method_opt=>'for all columns size auto'); Procedimento PL/SQL concluído com sucesso. SQL > EXEC dbms_stats.gather_table_stats(USER,'tab03',method_opt=>'for all columns size auto'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > ALTER TABLE tab01 ADD CONSTRAINT tab01col01 PRIMARY KEY(col01); Tabela alterada. SQL > ALTER TABLE tab02 ADD CONSTRAINT tab02col01 PRIMARY KEY(col01); Tabela alterada. SQL > ALTER TABLE tab03 ADD CONSTRAINT tab03col01 PRIMARY KEY(col01); Tabela alterada. SQL > |
2) Executar a consulta sem a estatística de grupo de colunas
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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > WITH visao AS ( 2 SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3 3 FROM tab01 t1, 4 tab02 t2, 5 tab03 t3 6 WHERE t1.col02 = t2.col02 7 AND t1.col03 = t2.col03 8 AND t1.col01 = t3.col01) 9 select /* DBTW059.1 */ * from visao where rownum < 51; V1 V2 V3 ---------------------------------------- ---------------------------------------- ---------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > SQL > SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada 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 '%DBTW059.1%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2kvfq9y3nw9hf 0 1 linha selecionada. SQL > SQL > -- Gera o relatório do plano de execução da consulta SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2kvfq9y3nw9hf, child number 0 ------------------------------------- WITH visao AS ( SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3 FROM tab01 t1, tab02 t2, tab03 t3 WHERE t1.col02 = t2.col02 AND t1.col03 = t2.col03 AND t1.col01 = t3.col01) select /* DBTW059.1 */ * from visao where rownum < :"SYS_B_0" Plan hash value: 328153508 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 50 |00:00:00.01 | 101 | |* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 101 | |* 2 | HASH JOIN | | 1 | 25 | 3600 | 11 (10)| 00:00:01 | 50 |00:00:00.01 | 101 | |* 3 | HASH JOIN | | 1 | 25 | 2475 | 6 (0)| 00:00:01 | 500 |00:00:00.01 | 89 | | 4 | TABLE ACCESS FULL| TAB02 | 1 | 10 | 470 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | 5 | TABLE ACCESS FULL| TAB01 | 1 | 10000 | 507K| 4 (0)| 00:00:01 | 10000 |00:00:00.01 | 87 | | 6 | TABLE ACCESS FULL | TAB03 | 1 | 10000 | 439K| 4 (0)| 00:00:01 | 810 |00:00:00.01 | 12 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<:SYS_B_0) 2 - access("T1"."COL01"="T3"."COL01") 3 - access("T1"."COL02"="T2"."COL02" AND "T1"."COL03"="T2"."COL03") 28 linhas selecionadas. 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 acima que na operação de ID=3 (HASH JOIN) o Otimizador estimou que seriam selecionadas 25 linhas na tabela quando na verdade foram lidas 500 linhas, uma diferença muito significativa que pode levar o Otimizador a escolher uma plano de execução inadequado.
3) Ativar monitoração de grupo de colunas utilizadas como filtro
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL > conn sys/password@lab01 as sysdba; Connected. SQL > grant ANALYZE ANY to curso03; Concessão bem-sucedida. SQL > grant ANALYZE ANY DICTIONARY to curso03; Concessão bem-sucedida. SQL > conn curso03/curso03@lab01; SQL > EXEC DBMS_STATS.seed_col_usage(NULL, NULL, 180); Procedimento PL/SQL concluído com sucesso. SQL > |
Para executar a procedure SEED_COL_USAGE o usuário precisa ter os privilégios ANALYZE ANY e ANALYZE ANY DICTIONARY, os dois primeiros parâmetros passados para a procedure são utilizados quando criamos um SQLSET, na nossa simulação vamos deixar estes parâmetros como NULL e informar somente o terceiro parâmetro que representa o numero de segundos durante os quais o banco vai monitorar a utilização de grupos de colunas das instruções SQL.
4) Executar consulta que utiliza grupo de colunas como filtro
Ativada a monitoração, nos próximos 180 segundos todas as instruções que forem executadas no banco serão examinadas quanto a utilização de grupos de colunas de uma mesma tabela sendo utilizadas no filtro da cláusula WHERE. Na nossa simulação vamos executar a mesma consulta da etapa 2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL > WITH visao AS ( 2 SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3 3 FROM tab01 t1, 4 tab02 t2, 5 tab03 t3 6 WHERE t1.col02 = t2.col02 7 AND t1.col03 = t2.col03 8 AND t1.col01 = t3.col01) 9 select /* DBTW059.2 */ * from visao where rownum < 51; V1 V2 V3 ---------------------------------------- ---------------------------------------- ---------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > |
5) Gerar relatório para verificar os grupos de colunas identificados
A procedure REPORT_COL_USAGE do pacote DBMS_STATS gera um relatório contendo todos os grupos de colunas utilizados no filtro da cláusula WHERE das instruções SQL executadas durante o período de monitoração que iniciamos na etapa anterior, esta procedure possui dois parâmetros, o primeiro é o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo parâmetro como NULL será gerado um relatório para todas as tabelas do SCHEMA.
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 | SQL > SET LONG 100000 SQL > SET LINES 120 SQL > SET PAGES 50 SQL > SQL > SELECT DBMS_STATS.report_col_usage(USER, NULL) FROM dual; DBMS_STATS.REPORT_COL_USAGE(USER,NULL) -------------------------------------------------------------------------------- LEGEND: ....... EQ : Used in single table EQuality predicate RANGE : Used in single table RANGE predicate LIKE : Used in single table LIKE predicate NULL : Used in single table is (not) NULL predicate EQ_JOIN : Used in EQuality JOIN predicate NONEQ_JOIN : Used in NON EQuality JOIN predicate FILTER : Used in single table FILTER predicate JOIN : Used in JOIN predicate GROUP_BY : Used in GROUP BY expression ............................................................................... ############################################################################### COLUMN USAGE REPORT FOR CURSO03.TAB01 ..................................... 1. COL01 : EQ_JOIN 2. COL02 : EQ_JOIN 3. COL03 : EQ_JOIN 4. (COL02, COL03) : JOIN ############################################################################### ############################################################################### COLUMN USAGE REPORT FOR CURSO03.TAB02 ..................................... 1. COL02 : EQ_JOIN 2. COL03 : EQ_JOIN 3. (COL02, COL03) : JOIN ############################################################################### ############################################################################### COLUMN USAGE REPORT FOR CURSO03.TAB03 ..................................... 1. COL01 : EQ_JOIN ############################################################################### SQL > |
6) Criar as estatísticas dos grupos de colunas identificados
A procedure CREATE_EXTENDED_STATS do pacote DBMS_STATS cria as estatísticas estendidas para os grupos de colunas identificados na etapa 4, esta procedure possui dois parâmetros, o primeiro é o SCHEMA da tabela e o segundo o nome da tabela, deixando o segundo parâmetro como NULL serão criadas todas as estatísticas estendidas para todas as tabelas do SCHEMA. Alem da criação das estatísticas estendidas é necessário coleta-las manualmente pois o processo de coleta de estatísticas não é dinâmico, ele ocorre normalmente de forma programada no período da noite.
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 | SQL > SELECT DBMS_STATS.create_extended_stats(USER, NULL) FROM dual; DBMS_STATS.CREATE_EXTENDED_STATS(USER,NULL) -------------------------------------------------------------------------------- ############################################################################### EXTENSIONS FOR CURSO03.TAB01 ............................ 1. (COL02, COL03) : SYS_STU#0G3WGNECOTHXW8K0TKS02G created ############################################################################### ############################################################################### EXTENSIONS FOR CURSO03.TAB02 ............................ 1. (COL02, COL03) : SYS_STU#0G3WGNECOTHXW8K0TKS02G created ############################################################################### SQL > EXEC DBMS_STATS.gather_schema_stats(USER, method_opt => 'for all columns size auto'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT table_name, column_name, num_distinct, density, num_nulls, histogram, num_buckets 2 FROM user_tab_col_statistics 3 WHERE table_name in ('TAB01','TAB02'); TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS HISTOGRAM NUM_BUCKETS ------------------------------ ------------------------------ ------------ ---------- ---------- --------------- ----------- TAB01 COL01 10000 ,0001 0 NONE 1 TAB01 COL02 20 ,00005 0 FREQUENCY 20 TAB01 COL03 200 ,00005 0 FREQUENCY 200 TAB01 COL04 1000 ,001 0 NONE 1 TAB01 COL05 1 1 0 NONE 1 TAB01 SYS_STU#0G3WGNECOTHXW8K0TKS02G 200 ,005 0 NONE 1 TAB02 COL01 10 ,1 0 NONE 1 TAB02 COL02 10 ,1 0 NONE 1 TAB02 COL03 10 ,1 0 NONE 1 TAB02 COL04 10 ,1 0 NONE 1 TAB02 COL05 1 1 0 NONE 1 TAB02 SYS_STU#0G3WGNECOTHXW8K0TKS02G 10 ,1 0 NONE 1 12 linhas selecionadas. SQL > |
7) Executar a consulta novamente para verificar a melhoria no plano de execuçã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 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 | SQL > ALTER SESSION SET statistics_level = ALL; Sessão alterada. SQL > SQL > WITH visao AS ( 2 SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3 3 FROM tab01 t1, 4 tab02 t2, 5 tab03 t3 6 WHERE t1.col02 = t2.col02 7 AND t1.col03 = t2.col03 8 AND t1.col01 = t3.col01) 9 select /* DBTW059.3 */ * from visao where rownum < 51; V1 V2 V3 ---------------------------------------- ---------------------------------------- ---------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz . . . xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 50 linhas selecionadas. SQL > SQL > SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada 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 '%DBTW059.3%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 0d2k76dcn6sf8 0 1 linha selecionada. SQL > SQL > -- Gera o relatório do plano de execução da consulta SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 0d2k76dcn6sf8, child number 0 ------------------------------------- WITH visao AS ( SELECT t1.col05 v1,t2.col05 v2,t3.col02 v3 FROM tab01 t1, tab02 t2, tab03 t3 WHERE t1.col02 = t2.col02 AND t1.col03 = t2.col03 AND t1.col01 = t3.col01) select /* DBTW059.3 */ * from visao where rownum < :"SYS_B_0" Plan hash value: 732749324 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 50 |00:00:00.01 | 87 | |* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:00.01 | 87 | |* 2 | HASH JOIN | | 1 | 50 | 14650 | 8 (0)| 00:00:01 | 50 |00:00:00.01 | 87 | | 3 | TABLE ACCESS FULL | TAB02 | 1 | 10 | 470 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | |* 4 | HASH JOIN | | 1 | 1000 | 145K| 6 (0)| 00:00:01 | 810 |00:00:00.01 | 85 | | 5 | TABLE ACCESS FULL| TAB03 | 1 | 10000 | 439K| 4 (0)| 00:00:01 | 10000 |00:00:00.01 | 72 | | 6 | TABLE ACCESS FULL| TAB01 | 1 | 10000 | 507K| 2 (0)| 00:00:01 | 810 |00:00:00.01 | 13 | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<:SYS_B_0) 2 - access("T1"."COL02"="T2"."COL02" AND "T1"."COL03"="T2"."COL03") 4 - access("T1"."COL01"="T3"."COL01") 28 linhas selecionadas. SQL > |
Observe no plano de execução que a estimativa de cardinalidade das operações estão muito próximas do numero real de linhas selecionadas, como a estimativa de cardinalidade melhorou o Otimizador gerou um plano de execução mais eficiente, neste plano de execução foram lidos 87 Buffers enquanto no plano de execução inicial foram 101 Buffers. Esta diferença de desempenho é pequena mas em consultas mais complexas estes erros de estimativas de cardinalidade podem gerar planos de execução com desempenho insatisfatório para os usuários.
Referências:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/
