O Otimizador baseado em custo (CBO) utiliza estatísticas para determinar o custo dos diversos planos de execução que ele produz para uma determinada instrução SQL e escolhe o plano com o menor custo para utilizar, portanto as informações estatísticas são cruciais na escolha do plano de execução e devem fornecer…
Qual a importância das estatísticas de tabelas para o Otimizador?
O Otimizador baseado em custo (CBO) utiliza estatísticas para determinar o custo dos diversos planos de execução que ele produz para uma determinada instrução SQL e escolhe o plano com o menor custo para utilizar, portanto as informações estatísticas são cruciais na escolha do plano de execução e devem fornecer informações precisas e atualizadas para que o Otimizador consiga realizar um bom trabalho.
Existem três tipos de estatísticas de objetos: TABLE STATISTICS, COLUMN STATISTICS e INDEX STATISTICS. Nesse artigo vamos explorar um pouco as estatísticas de tabelas (TABLE STATISTICS), vamos fazer uma simulação e ver como o Otimizador utiliza essas informações.
TABLE STATISTICS
As informações estatísticas de tabela podem ser consultadas na visão USER_TAB_STATISTICS, existem muitas informações disponíveis nessa visão mas os campos mais importantes para o Otimizador são:
NUM_ROWS – Número de linhas na tabela
AVG_ROW_LEN – Tamanho médio de uma linha na tabela
Para ilustrar na prática a utilização das estatísticas de tabelas, vamos fazer uma simulação simples com uma consulta utilizando o “SAMPLE SCHEMA HR” que faz parte da instalação do banco de dados Oracle, nessa simulação vamos executar a mesma consulta duas vezes, uma sem estatísticas e outra com estatísticas, o parâmetro OPTIMIZER_DYNAMIC_SAMPLING será desativado a nível de sessão pois o objetivo é verificar como o Otimizador utiliza as estatísticas de tabela na montagem do plano de execução.
Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:
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:
1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução
Consulta sem as estatísticas de tabelas
Para execução da consulta sem as estatísticas de tabelas vamos criar uma nova tabela copia da tabela EMPLOYEES e não vamos coletar as estatísticas, em seguida vamos verificar como o Otimizador estimou o numero de linhas dessa tabela.
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 | HR@LAB11 > SELECT * FROM V$VERSION where rownum < 2; BANNER ------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production 1 linha selecionada. Decorrido: 00:00:00.07 HR@LAB11 > HR@LAB11 > CREATE TABLE EMP 2 AS 3 SELECT * FROM EMPLOYEES; Tabela criada. HR@LAB11 > HR@LAB11 > HR@LAB11 > SELECT num_rows, avg_row_len 2 FROM user_tab_statistics 3 WHERE table_name = 'EMP'; NUM_ROWS AVG_ROW_LEN ---------- ----------- HR@LAB11 > ALTER SESSION SET statistics_level=ALL; Sessão alterada. HR@LAB11 > Alter session set optimizer_dynamic_sampling=0; Sessão alterada. HR@LAB11 > HR@LAB11 > HR@LAB11 > select /* tst102 */ 2 * 3 from EMP; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- ------------- 198 Donald OConnell DOCONNEL 650.507.9833 21/06/07 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 13/01/08 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 17/09/03 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 17/02/04 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 17/08/05 MK_REP 6000 201 20 .. .. .. 197 Kevin Feeney KFEENEY 650.507.9822 23/05/06 SH_CLERK 3000 124 50 107 linhas selecionadas. HR@LAB11 > HR@LAB11 > HR@LAB11 > column sql_id new_value m_sql_id HR@LAB11 > column child_number new_value m_child_no HR@LAB11 > HR@LAB11 > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%tst102%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 923fg367zbm89 0 HR@LAB11 > HR@LAB11 > HR@LAB11 > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('923fg367zbm89', 0,'typical iostats last')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 923fg367zbm89, child number 0 ------------------------------------- select /* tst102 */ * from EMP Plan hash value: 3956160932 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 107 |00:00:00.01 | 12 | 2 | | 1 | TABLE ACCESS FULL| EMP | 1 | 409 | 54397 | 3 (0)| 00:00:01 | 107 |00:00:00.01 | 12 | 2 | ----------------------------------------------------------------------------------------------------------------------------- 13 linhas selecionadas. HR@LAB11 > |
No plano de execução acima observamos que a consulta retornou 107 linhas (A-Rows) porem o otimizador estimou 409 (E-Rows), esta estimativa é baseada em valores padrões que o Otimizador assume quando não existem estatísticas para a tabela.
Para chegar ao valor 409 (E-Rows) o Otimizador utilizou a formula {num_of_blocks * (block_size – cache_layer) / avg_row_len}, o valor “block_size” é 8.192 no banco que estamos realizando essa simulação, o valor “cache_layer” = 24 e o valor “avg_row_len” quando não existem estatísticas para a tabela é 100 bytes conforme tabela acima. Para calcular o resultado da formula falta descobrir o valor de “num_of_blocks” que pode ser 100 ou valor real baseado no “extent map”, a seguir vamos utilizar o pacote “DBMS_SPACE.UNUSED_SPACE” para descobrir o numero de blocos utilizados pela tabela.
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 | dbauser@LAB11 > show parameters db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 dbauser@LAB11 > dbauser@LAB11 > dbauser@LAB11 > create or replace 2 procedure show_space 3 (p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'TABLE', 6 p_partition in varchar2 default NULL ) 7 authid current_user 8 as 9 l_free_blks number; 10 l_total_blocks number; 11 l_total_bytes number; 12 l_unused_blocks number; 13 l_unused_bytes number; 14 l_LastUsedExtFileId number; 15 l_LastUsedExtBlockId number; 16 l_LAST_USED_BLOCK number; 17 18 procedure p( p_label in varchar2, p_num in number ) 19 is 20 begin 21 dbms_output.put_line( rpad(p_label,40,'.') || 22 p_num ); 23 end; 24 25 begin 26 dbms_space.unused_space 27 ( segment_owner => p_owner, 28 segment_name => p_segname, 29 segment_type => p_type, 30 partition_name => p_partition, 31 total_blocks => l_total_blocks, 32 total_bytes => l_total_bytes, 33 unused_blocks => l_unused_blocks, 34 unused_bytes => l_unused_bytes, 35 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 36 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 37 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 38 39 p( 'Total Blocks', l_total_blocks ); 40 p( 'Unused Blocks', l_unused_blocks ); 41 p( 'Blocks Used', l_total_blocks-l_unused_blocks ); 42 43 end; 44 / Procedimento criado. dbauser@LAB11 > set serveroutput on dbauser@LAB11 > exec show_space('EMP','HR'); Total Blocks............................8 Unused Blocks...........................3 Blocks Used.............................5 Procedimento PL/SQL concluído com sucesso. dbauser@LAB11 > dbauser@LAB11 > select 5*(8192-24)/100 from dual; 5*(8192-24)/100 --------------- 408,4 dbauser@LAB11 > |
Conforme resultado da “procedure” acima o número de blocos usados pela tabela são 5 e aplicando esse valor na formula informada na documentação da Oracle temos um resultado de 408,4 que é o mesmo valor arrendondado que aparece no plano de execução no campo E-Rows = 409.
Consulta com as estatísticas de tabelas
Agora vamos coletar as estatísticas da tabela utilizada na nossa simulação, executar a consulta novamente e observar quantas linha o Otimizador vai estimar para o 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 82 83 84 85 86 87 88 89 90 91 92 | HR@LAB11 > BEGIN 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 'EMP', 5 estimate_percent => 100, 6 method_opt => 'for all columns size auto', cascade => TRUE); 7 END; 8 / Procedimento PL/SQL concluído com sucesso. HR@LAB11 > HR@LAB11 > SELECT num_rows, avg_row_len 2 FROM user_tab_statistics 3 WHERE table_name = 'EMP'; NUM_ROWS AVG_ROW_LEN ---------- ----------- 107 69 HR@LAB11 > HR@LAB11 > HR@LAB11 > HR@LAB11 > ALTER SESSION SET statistics_level=ALL; Sessão alterada. HR@LAB11 > Alter session set optimizer_dynamic_sampling=0; Sessão alterada. HR@LAB11 > HR@LAB11 > HR@LAB11 > select /* tst104 */ 2 * 3 from EMP; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DAT JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- ------------- 198 Donald OConnell DOCONNEL 650.507.9833 21/06/07 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 13/01/08 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 17/09/03 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 17/02/04 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 17/08/05 MK_REP 6000 201 20 ... ... ... 197 Kevin Feeney KFEENEY 650.507.9822 23/05/06 SH_CLERK 3000 124 50 107 linhas selecionadas. HR@LAB11 > HR@LAB11 > HR@LAB11 > column sql_id new_value m_sql_id HR@LAB11 > column child_number new_value m_child_no HR@LAB11 > HR@LAB11 > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%tst104%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 85hb1va9m5s58 0 HR@LAB11 > HR@LAB11 > HR@LAB11 > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('85hb1va9m5s58', 0,'typical iostats last')) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 85hb1va9m5s58, child number 0 ------------------------------------- select /* tst104 */ * from EMP Plan hash value: 3956160932 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 107 |00:00:00.01 | 11 | | 1 | TABLE ACCESS FULL| EMP | 1 | 107 | 7383 | 3 (0)| 00:00:01 | 107 |00:00:00.01 | 11 | -------------------------------------------------------------------------------------------------------------------- 13 linhas selecionadas. HR@LAB11 > |
Após a coleta de estatísticas podemos observar que estimativa de linhas selecionadas é igual ao número de linhas que a consulta retorna (107 linhas), ou seja, quando as estatísticas existem o Otimizador as utiliza.
Conclusão
Nesta simulação utilizamos uma consulta simples, a existência ou não de estatística não fez diferença em termos de plano de execução pois neste caso a consulta não tem filtro na clausula WHERE e a execução da operação “TABLE ACCESS FULL” na tabela “EMP” é inevitável. Entretanto se essa consulta fosse uma junção de duas tabelas, a existência das estatísticas de tabela seria crucial na decisão de qual tipo de JOIN seria utilizado nessa operação.
Referências
http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm
