A "Feature Dynamic Sampling" foi disponibilizada a partir da versão Oracle 9iR2. O equívoco mais comum é que ela pode ser utilizada para substituir as estatísticas coletadas pelo pacote DBMS_STATS. O objetivo dela é dar mais opções estatísticas ao otimizador, ela é usada quando as estatísticas regulares não são suficientes…
Plano de execução com custo baixo tem melhor desempenho, Mito ou Fato?
Alguns profissionais acreditam que se um plano de execução tem um custo baixo ele será melhor que um plano de execução cujo custo seja maior, essa afirmação é um mito ou fato?
Nesse artigo vamos fazer uma simulação de uma instrução SQL que vai ser executada duas vezes com planos de execução diferentes e custos diferentes e vamos comparar os tempos de execução para responder esta questão.
Plano de execução “A”
Para essa simulação vamos utilizar o “SAMPLE SCHEMA SH” que faz parte da instalação do banco de dados Oracle, vamos executar a mesma consulta duas vezes e na segunda execução vamos observar que o otimizador vai utilizar um novo plano devido a “feature Cardinality feedback”.
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 | appuser@LAB11 > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Elapsed: 00:00:00.19 appuser@LAB11 > appuser@LAB11 > set echo off; appuser@LAB11 > set tab off; appuser@LAB11 > set lines 300 pages 100; appuser@LAB11 > appuser@LAB11 > ALTER SESSION SET statistics_level=ALL; Session altered. Elapsed: 00:00:00.00 appuser@LAB11 > ALTER SESSION SET current_schema=SH; Session altered. Elapsed: 00:00:00.00 appuser@LAB11 > appuser@LAB11 > select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD) QUANTITY_SOLD 2 from CUSTOMERS c, SALES s 3 where c.CUST_ID = s.CUST_ID 4 and c.CUST_GENDER = 'F' 5 and c.CUST_LAST_NAME like '%Bishop%' 6 group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME; CUST_ID CUST_FIRST_NAME CUST_LAST_NAME QUANTITY_SOLD ---------- -------------------- ---------------------------------------- ------------- 100376 Dylan Bishop 4 100842 Hankil Bishop 4 100232 Chloe Bishop 5 100025 Brayden Bishop 2 100188 Hankil Bishop 3 100915 Andrew Bishop 3 100896 Noah Bishop 4 100218 Andrew Bishop 3 100642 Aidan Bishop 4 100854 Joshua Bishop 3 100813 Andrew Bishop 5 100927 Dakota Bishop 1 100383 Dakota Bishop 3 13 rows selected. Elapsed: 00:00:00.07 appuser@LAB11 > appuser@LAB11 > column sql_id new_value m_sql_id appuser@LAB11 > column child_number new_value m_child_no appuser@LAB11 > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%QRY-TST%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 801jbjyrx5dzz 0 Elapsed: 00:00:00.03 appuser@LAB11 > appuser@LAB11 > appuser@LAB11 > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); old 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) new 2: FROM TABLE (dbms_xplan.display_cursor ('801jbjyrx5dzz', 0,'typical iostats last')) PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 801jbjyrx5dzz, child number 0 ------------------------------------- select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD) QUANTITY_SOLD from CUSTOMERS c, SALES s where c.CUST_ID = s.CUST_ID and c.CUST_GENDER = :"SYS_B_0" and c.CUST_LAST_NAME like :"SYS_B_1" group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME Plan hash value: 2222044125 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 954 (100)| | | | 13 |00:00:00.06 | 5928 | | 1 | HASH GROUP BY | | 1 | 916 | 36640 | 954 (4)| 00:00:12 | | | 13 |00:00:00.06 | 5928 | |* 2 | HASH JOIN | | 1 | 916 | 36640 | 953 (4)| 00:00:12 | | | 13 |00:00:00.06 | 5928 | | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 916 | 20152 | 405 (1)| 00:00:05 | | | 20 |00:00:00.01 | 1457 | |* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 916 | 20152 | 405 (1)| 00:00:05 | | | 20 |00:00:00.01 | 1457 | | 5 | VIEW | VW_GBC_5 | 1 | 7059 | 124K| 548 (6)| 00:00:07 | | | 14 |00:00:00.05 | 4471 | | 6 | HASH GROUP BY | | 1 | 7059 | 56472 | 548 (6)| 00:00:07 | | | 14 |00:00:00.05 | 4471 | | 7 | JOIN FILTER USE | :BF0000 | 1 | 918K| 7178K| 525 (2)| 00:00:07 | | | 111 |00:00:00.05 | 4471 | | 8 | PARTITION RANGE ALL| | 1 | 918K| 7178K| 525 (2)| 00:00:07 | 1 | 28 | 111 |00:00:00.05 | 4471 | |* 9 | TABLE ACCESS FULL | SALES | 28 | 918K| 7178K| 525 (2)| 00:00:07 | 1 | 28 | 111 |00:00:00.05 | 4471 | ------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."CUST_ID"="ITEM_1") 4 - filter(("C"."CUST_LAST_NAME" LIKE :SYS_B_1 AND "C"."CUST_GENDER"=:SYS_B_0)) 9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")) 32 rows selected. Elapsed: 00:00:00.03 appuser@LAB11 > |
Analisando o plano de execução acima podemos verificar que a consulta selecionou 13 registros (A-Rows) e o tempo de execução foi de 6 centésimos de segundo (A-Time) com um custo de 954 (Cost), também podemos observar que para esse SQL_ID “801jbjyrx5dzz” tem somente um plano de execução cujo HASH é “2222044125” e CHILD NUMBER 0. Outro ponto importante a se observar é o numero de “Buffers” de memoria visitados que nesse caso foi 5.928.
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:
- Como verificar a ordem que as operações são realizadas
- Como interpretar os valores estatísticos estimados
- Como interpretar os valores estatísticos coletados durante a execução
- Como interpretar as seções não estatísticas do plano de execução
Plano de execução “B”
Vamos executar novamente a mesma consulta e observar o que acontece:
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 | appuser@LAB11 > select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD) QUANTITY_SOLD 2 from CUSTOMERS c, SALES s 3 where c.CUST_ID = s.CUST_ID 4 and c.CUST_GENDER = 'F' 5 and c.CUST_LAST_NAME like '%Bishop%' 6 group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME; CUST_ID CUST_FIRST_NAME CUST_LAST_NAME QUANTITY_SOLD ---------- -------------------- ---------------------------------------- ------------- 100376 Dylan Bishop 4 100842 Hankil Bishop 4 100232 Chloe Bishop 5 100025 Brayden Bishop 2 100188 Hankil Bishop 3 100915 Andrew Bishop 3 100896 Noah Bishop 4 100218 Andrew Bishop 3 100642 Aidan Bishop 4 100854 Joshua Bishop 3 100813 Andrew Bishop 5 100927 Dakota Bishop 1 100383 Dakota Bishop 3 13 rows selected. Elapsed: 00:00:00.40 appuser@LAB11 > appuser@LAB11 > column sql_id new_value m_sql_id appuser@LAB11 > column child_number new_value m_child_no appuser@LAB11 > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%QRY-TST%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 801jbjyrx5dzz 0 801jbjyrx5dzz 1 Elapsed: 00:00:00.03 appuser@LAB11 > appuser@LAB11 > appuser@LAB11 > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); old 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')) new 2: FROM TABLE (dbms_xplan.display_cursor ('801jbjyrx5dzz', 1,'typical iostats last')) PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 801jbjyrx5dzz, child number 1 ------------------------------------- select /* QRY-TST */ c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME, sum(QUANTITY_SOLD) QUANTITY_SOLD from CUSTOMERS c, SALES s where c.CUST_ID = s.CUST_ID and c.CUST_GENDER = :"SYS_B_0" and c.CUST_LAST_NAME like :"SYS_B_1" group by c.CUST_ID, c.CUST_FIRST_NAME, CUST_LAST_NAME Plan hash value: 3219243479 -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 563 (100)| | | | 13 |00:00:00.39 | 18088 | | 1 | HASH GROUP BY | | 1 | 14 | 560 | 563 (6)| 00:00:07 | | | 13 |00:00:00.39 | 18088 | | 2 | NESTED LOOPS | | 1 | 14 | 560 | 562 (6)| 00:00:07 | | | 13 |00:00:00.39 | 18088 | | 3 | NESTED LOOPS | | 1 | 14 | 560 | 562 (6)| 00:00:07 | | | 7059 |00:00:00.38 | 11029 | | 4 | VIEW | VW_GBC_5 | 1 | 14 | 252 | 548 (6)| 00:00:07 | | | 7059 |00:00:00.37 | 4471 | | 5 | HASH GROUP BY | | 1 | 14 | 112 | 548 (6)| 00:00:07 | | | 7059 |00:00:00.37 | 4471 | | 6 | PARTITION RANGE ALL | | 1 | 918K| 7178K| 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.18 | 4471 | | 7 | TABLE ACCESS FULL | SALES | 28 | 918K| 7178K| 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.08 | 4471 | |* 8 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 7059 | 1 | | 0 (0)| | | | 7059 |00:00:00.01 | 6558 | |* 9 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 7059 | 1 | 22 | 1 (0)| 00:00:01 | | | 13 |00:00:00.01 | 7059 | -------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("C"."CUST_ID"="ITEM_1") 9 - filter(("C"."CUST_LAST_NAME" LIKE :SYS_B_1 AND "C"."CUST_GENDER"=:SYS_B_0)) Note ----- - cardinality feedback used for this statement 35 rows selected. Elapsed: 00:00:00.02 appuser@LAB11 > |
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:
A primeira observação sobre a execução da consulta acima é que o plano de execução foi alterado, agora o HASH é “3219243479” e CHILD NUMBER 1, se observarmos na seção “Note” linha 83 vamos encontrar o motivo pelo qual o plano de execução foi alterado: “cardinality feedback used for this statement“, isto significa que o Otimizador percebeu que as estimativas de linhas por operação na primeira execução não estavam corretas e utilizou as informações da primeira execução para melhorar as estimativas na segunda execução. Alem disso podemos observar que a consulta selecionou os mesmos 13 registros (A-Rows) da primeira execução, porem o tempo de execução aumentou para 39 centésimos de segundo (A-Time) e o custo diminuiu para 563 (Cost). O aumento do tempo de execução se deve ao fato de que houve um aumento no número de “Buffers” em memoria visitados para execução da consulta.
Conclusão
Observando o quadro acima podemos concluir que o plano com menor custo 563 (B) não é o melhor plano de execução pois o seu tempo de execução foi cinco vezes maior que o plano “A” com custo 954, Isto ocorre porque o otimizador faz uma série de estimativas e suposições para criar o plano de execução e em alguns casos essas estimativas ou suposições não são tão precisas como deveriam ser. Assim podemos concluir que trata-se de um mito a afirmação de que um plano de execução com o menor custo sempre será o melhor.
Referências
https://docs.oracle.com/cd/E18283_01/server.112/e10831/overview.htm#sthref6
