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…
Quer mais performance? Faça upgrade para o 19c
O Otimizador do Oracle vem sendo aprimorado a cada nova versão, a partir da 12c foram introduzidas muitas melhorias que tornaram o Otimizador muito mais eficente na escolha dos planos de execução, nas versões anteriores o Otimizador tinha algumas limitações que o impedia de escolher planos de execução melhores. Na versão 19c o Otimizador não alcançou a perfeição, mas houve um avanço gigantesco comparado com as versões 11g e anteriores.
Neste artigo vamos fazer uma demonstração prática de uma dessas melhorias, mostrando uma limitação do Otimizador existente na versão 11g e como ele superou esta limitação produzindo planos de execução com melhor desempenho na versão 18c.
Na primeira simulação prática vamos verificar a ineficiência do Otimizador na escolha do plano de execução na versão 11gR2:
1.1) Criar os objetos para simular a consulta
1.2) Executar a consulta com plano de execução ineficiente
1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho
Na segunda simulação prática vamos verificar como o Otimizador resolve esta limitação da versão 11gR2 produzindo um plano de execução com melhor desempenho na versão 18c:
2.1) Criar os objetos para simular a consulta
2.2) Executar a consulta com plano de execução eficiente
PRIMEIRA SIMULAÇÃO (11gR2)
1.1) Criar os objetos para simular a consulta
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 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 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > SQL > create table dbtw_grupo (key_id number, id_grupo number, nome_grupo varchar(32)); Tabela criada. SQL > SQL > Declare 2 3 num_lo01 INTEGER := 0; 4 num_lo02 INTEGER := 0; 5 num_ocor INTEGER := 167; 6 row_count INTEGER := 1; 7 8 begin 9 10 FOR c1 in (select trunc(dbms_random.value(0,1000)) num_dist from dual connect by level <= 1000) 11 LOOP 12 FOR num_lo02 in 0 .. num_ocor 13 LOOP 14 INSERT INTO dbtw_grupo values(row_count,c1.num_dist,'GRUPO_NUMERO-'||c1.num_dist); 15 row_count := row_count + 1; 16 END LOOP; 17 num_ocor := num_ocor - 1; 18 if num_ocor = 0 then 19 num_ocor := 1; 20 end if; 21 END LOOP; 22 commit; 23 24 end; 25 / Procedimento PL/SQL concluído com sucesso. SQL > SQL > create index dbtw_id_grupo_idx on dbtw_grupo(id_grupo); Índice criado. SQL > SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > SQL > create table dbtw_usuario (login varchar(10), id_grupo number, status varchar(1), codigo number, note varchar(256)); Tabela criada. SQL > SQL > Declare 2 3 num_lo01 INTEGER := 0; 4 num_ocor INTEGER := 167; 5 l_id_grupo INTEGER; 6 log_id INTEGER := 1; 7 l_key_id INTEGER; 8 9 begin 10 11 FOR num_lo01 in 1 .. 350000 12 LOOP 13 l_key_id := trunc(dbms_random.value(1,14999)); 14 select id_grupo into l_id_grupo from DBTW_GRUPO where key_id = l_key_id; 15 INSERT INTO dbtw_usuario values('LOG_'||LPAD(log_id,6,0),l_id_grupo,'I',LPAD(log_id,6,0),LPAD('X',256,'X')); 16 log_id := log_id + 1; 17 END LOOP; 18 commit; 19 20 end; 21 / Procedimento PL/SQL concluído com sucesso. SQL > SQL > create index dbtw_usuario_id_grupo_idx on dbtw_usuario(id_grupo); Índice criado. SQL > create index dbtw_usuario_status_idx on dbtw_usuario(status); Índice criado. SQL > create index dbtw_usuario_login_idx on dbtw_usuario(login); Índice criado. SQL > SQL > update dbtw_usuario set status = 'N' where codigo between 60000 and 120000; 60001 linhas atualizadas. SQL > update dbtw_usuario set status = 'F' where codigo between 150000 and 153000; 3001 linhas atualizadas. SQL > update dbtw_usuario set status = 'A' where codigo between 210000 and 212000; 2001 linhas atualizadas. SQL > update dbtw_usuario set status = 'R' where codigo between 280000 and 280500; 501 linhas atualizadas. SQL > SQL > commit; Commit concluído. SQL > SQL > SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS STATUS SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > |
1.2) Executar a consulta com plano de execução ineficiente
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 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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable LOGIN VARCHAR2(6); SQL > variable SYS_B_3 VARCHAR2(32); SQL > variable SYS_B_4 VARCHAR2(32); SQL > SQL > exec :SYS_B_3 := 'I'; Procedimento PL/SQL concluído com sucesso. SQL > exec :SYS_B_4 := 'R'; Procedimento PL/SQL concluído com sucesso. SQL > exec :LOGIN := '076955'; Procedimento PL/SQL concluído com sucesso. SQL > SQL > select /* dbtw-062.1 */ u.codigo, u.login 2 FROM dbtw_usuario u 3 INNER JOIN dbtw_grupo g 4 ON u.id_grupo = g.id_grupo 5 WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") 6 AND u.login like '%'||:LOGIN; CODIGO LOGIN ---------- ---------- 76955 LOG_076955 76955 LOG_076955 76955 LOG_076955 .. .. 76955 LOG_076955 157 linhas selecionadas. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.1%' AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ drc438b9c12ty 0 SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('drc438b9c12ty', 0,'BASIC ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ select /* dbtw-062.1 */ u.codigo, u.login FROM dbtw_usuario u INNER JOIN dbtw_grupo g ON u.id_grupo = g.id_grupo WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") AND u.login like :"B0"||:LOGIN Plan hash value: 2044621122 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 157 |00:00:02.70 | 14230 | 14211 | | | | |* 1 | HASH JOIN | | 1 | 345K| 157 |00:00:02.70 | 14230 | 14211 | 1321K| 1321K| 721K (0)| |* 2 | TABLE ACCESS FULL | DBTW_USUARIO | 1 | 3242 | 1 |00:00:02.66 | 14181 | 14177 | | | | | 3 | INDEX FAST FULL SCAN| DBTW_ID_GRUPO_IDX | 1 | 15861 | 15861 |00:00:00.04 | 49 | 34 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("U"."ID_GRUPO"="G"."ID_GRUPO") 2 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."LOGIN" LIKE :B0||:LOGIN AND "U"."STATUS"<>:SYS_B_4)) 23 linhas selecionadas. SQL > |
Observe no plano de execução acima que o Otimizador utilizou a operação HASH JOIN para juntar as duas tabelas quando a melhor opção seria uma operação NESTED LOOPS, uma vez que foi selecionada somente 1 linha na tabela DBTW_USUARIO (A-Rows = 1). O Otimizador escolheu a operação HASH JOIN pois a estimativa de linhas que seriam lidas da tabela DBTW_USUARIO seriam 3242 linhas (E-Rows = 3242), este erro de estimativa foi causado pela complexidade dos filtros da cláusula WHERE.
1.3) Executar a mesma consulta com HINTs para melhorar o seu desempenho
Com o conhecimento mais aprofundado dos dados da tabela DBTW_USUARIO sabemos que apesar da complexidade do filtro da cláusula WHERE, o numero de linhas selecionadas será sempre próximo de 1, logo podemos dizer para o Otimizador, através de HINTs, que estime 1 linha para cardinalidade da operação de leitura da tabela DBTW_USUARIO e utilize o indice DBTW_USUARIO_LOGIN_IDX.
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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable LOGIN VARCHAR2(6); SQL > variable SYS_B_3 VARCHAR2(32); SQL > variable SYS_B_4 VARCHAR2(32); SQL > SQL > exec :SYS_B_3 := 'I'; Procedimento PL/SQL concluído com sucesso. SQL > exec :SYS_B_4 := 'R'; Procedimento PL/SQL concluído com sucesso. SQL > exec :LOGIN := '076955'; Procedimento PL/SQL concluído com sucesso. SQL > SQL > select /*+ OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U DBTW_USUARIO_LOGIN_IDX) */ /* dbtw-062.1 */ u.codigo, u.login 2 FROM dbtw_usuario u 3 INNER JOIN dbtw_grupo g 4 ON u.id_grupo = g.id_grupo 5 WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") 6 AND u.login like '%'||:LOGIN; CODIGO LOGIN ---------- ---------- 76955 LOG_076955 76955 LOG_076955 76955 LOG_076955 .. .. 76955 LOG_076955 157 linhas selecionadas. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.1%' AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ a2dkyxr0qdc08 0 SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'BASIC ALLSTATS LAST')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('a2dkyxr0qdc08', 0,'BASIC ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ select /*+ OPT_ESTIMATE(TABLE U ROWS=1) INDEX(U DBTW_USUARIO_LOGIN_IDX) */ /* dbtw-062.1 */ u.codigo, u.login FROM dbtw_usuario u INNER JOIN dbtw_grupo g ON u.id_grupo = g.id_grupo WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") AND u.login like :"B0"||:LOGIN Plan hash value: 3055727543 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 157 |00:00:00.62 | 1091 | 19 | | 1 | NESTED LOOPS | | 1 | 107 | 157 |00:00:00.62 | 1091 | 19 | |* 2 | TABLE ACCESS BY INDEX ROWID| DBTW_USUARIO | 1 | 1 | 1 |00:00:00.62 | 1078 | 19 | |* 3 | INDEX RANGE SCAN | DBTW_USUARIO_LOGIN_IDX | 1 | 17500 | 1 |00:00:00.61 | 1077 | 18 | |* 4 | INDEX RANGE SCAN | DBTW_ID_GRUPO_IDX | 1 | 107 | 157 |00:00:00.01 | 13 | 0 | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."STATUS"<>:SYS_B_4)) 3 - access("U"."LOGIN" LIKE :B0||:LOGIN) filter("U"."LOGIN" LIKE :B0||:LOGIN) 4 - access("U"."ID_GRUPO"="G"."ID_GRUPO") 28 linhas selecionadas. SQL > |
Após a execução da consulta podemos verificar no plano de execução acima que o desempenho melhorou significativamente, enquanto na primeira execução foram acessados 14.230 Buffers, nesta execução a quantidade Buffers acessados caiu para 1.091.
SEGUNDA SIMULAÇÃO (18C)
Agora vamos utilizar esta mesma consulta numa base 18c e verificar como o Otimizador lida com esta questão de predicados ou filtros da cláusula WHERE complexos.
2.1) Criar os objetos para simular a consulta
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 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | SQL > SELECT BANNER_FULL FROM V$VERSION where rownum < 2; BANNER_FULL ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 SQL > create table dbtw_grupo (key_id number, id_grupo number, nome_grupo varchar(32)); Tabela criada. SQL > SQL > Declare 2 3 num_lo01 INTEGER := 0; 4 num_lo02 INTEGER := 0; 5 num_ocor INTEGER := 167; 6 row_count INTEGER := 1; 7 8 begin 9 10 FOR c1 in (select trunc(dbms_random.value(0,1000)) num_dist from dual connect by level <= 1000) 11 LOOP 12 FOR num_lo02 in 0 .. num_ocor 13 LOOP 14 INSERT INTO dbtw_grupo values(row_count,c1.num_dist,'GRUPO_NUMERO-'||c1.num_dist); 15 row_count := row_count + 1; 16 END LOOP; 17 num_ocor := num_ocor - 1; 18 if num_ocor = 0 then 19 num_ocor := 1; 20 end if; 21 END LOOP; 22 commit; 23 24 end; 25 / Procedimento PL/SQL concluído com sucesso. SQL > SQL > create index dbtw_id_grupo_idx on dbtw_grupo(id_grupo); Índice criado. SQL > SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_GRUPO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > SQL > create table dbtw_usuario (login varchar(10), id_grupo number, status varchar(1), codigo number, note varchar(256)); Tabela criada. SQL > SQL > Declare 2 3 num_lo01 INTEGER := 0; 4 num_ocor INTEGER := 167; 5 l_id_grupo INTEGER; 6 log_id INTEGER := 1; 7 l_key_id INTEGER; 8 9 begin 10 11 FOR num_lo01 in 1 .. 350000 12 LOOP 13 l_key_id := trunc(dbms_random.value(1,14999)); 14 select id_grupo into l_id_grupo from DBTW_GRUPO where key_id = l_key_id; 15 INSERT INTO dbtw_usuario values('LOG_'||LPAD(log_id,6,0),l_id_grupo,'I',LPAD(log_id,6,0),LPAD('X',256,'X')); 16 log_id := log_id + 1; 17 END LOOP; 18 commit; 19 20 end; 21 / Procedimento PL/SQL concluído com sucesso. SQL > SQL > create index dbtw_usuario_id_grupo_idx on dbtw_usuario(id_grupo); Índice criado. SQL > create index dbtw_usuario_status_idx on dbtw_usuario(status); Índice criado. SQL > create index dbtw_usuario_login_idx on dbtw_usuario(login); Índice criado. SQL > SQL > update dbtw_usuario set status = 'N' where codigo between 60000 and 120000; 60001 linhas atualizadas. SQL > update dbtw_usuario set status = 'F' where codigo between 150000 and 153000; 3001 linhas atualizadas. SQL > update dbtw_usuario set status = 'A' where codigo between 210000 and 212000; 2001 linhas atualizadas. SQL > update dbtw_usuario set status = 'R' where codigo between 280000 and 280500; 501 linhas atualizadas. SQL > SQL > commit; Commit concluído. SQL > SQL > SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS ID_GRUPO SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW_USUARIO',method_opt=>'FOR COLUMNS STATUS SIZE 254',cascade=>true,force=>true,degree=>16); Procedimento PL/SQL concluído com sucesso. SQL > |
2.2) Executar a consulta com plano de execução eficiente
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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable LOGIN VARCHAR2(6); SQL > variable SYS_B_3 VARCHAR2(32); SQL > variable SYS_B_4 VARCHAR2(32); SQL > SQL > exec :SYS_B_3 := 'I'; Procedimento PL/SQL concluído com sucesso. SQL > exec :SYS_B_4 := 'R'; Procedimento PL/SQL concluído com sucesso. SQL > exec :LOGIN := '076955'; Procedimento PL/SQL concluído com sucesso. SQL > SQL > select /* dbtw-062.3 */ u.codigo, u.login 2 FROM dbtw_usuario u 3 INNER JOIN dbtw_grupo g 4 ON u.id_grupo = g.id_grupo 5 WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") 6 AND u.login like '%'||:LOGIN; CODIGO LOGIN ---------- ---------- 76955 LOG_076955 76955 LOG_076955 .. .. 76955 LOG_076955 72 linhas selecionadas. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%dbtw-062.3%' AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 6v7qnmxnyf0ru 0 SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ADAPTIVE ALLSTATS LAST')); antigo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ADAPTIVE ALLSTATS LAST')) novo 1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('6v7qnmxnyf0ru', 0,'ADAPTIVE ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 6v7qnmxnyf0ru, child number 0 ------------------------------------- select /* dbtw-062.3 */ u.codigo, u.login FROM dbtw_usuario u INNER JOIN dbtw_grupo g ON u.id_grupo = g.id_grupo WHERE u.status NOT IN (:"SYS_B_3", :"SYS_B_4") AND u.login like '%'||:LOGIN Plan hash value: 1119022536 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 72 |00:00:00.06 | 1086 | |- * 1 | HASH JOIN | | 1 | 369K| 72 |00:00:00.06 | 1086 | | 2 | NESTED LOOPS | | 1 | 369K| 72 |00:00:00.06 | 1086 | |- 3 | STATISTICS COLLECTOR | | 1 | | 1 |00:00:00.06 | 1078 | | * 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW_USUARIO | 1 | 3271 | 1 |00:00:00.06 | 1078 | | * 5 | INDEX RANGE SCAN | DBTW_USUARIO_LOGIN_IDX | 1 | 17500 | 1 |00:00:00.06 | 1077 | | * 6 | INDEX RANGE SCAN | DBTW_ID_GRUPO_IDX | 1 | 113 | 72 |00:00:00.01 | 8 | |- 7 | INDEX FAST FULL SCAN | DBTW_ID_GRUPO_IDX | 0 | 15861 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("U"."ID_GRUPO"="G"."ID_GRUPO") 4 - filter(("U"."STATUS"<>:SYS_B_3 AND "U"."STATUS"<>:SYS_B_4)) 5 - access("U"."LOGIN" LIKE '%'||:LOGIN) filter("U"."LOGIN" LIKE '%'||:LOGIN) 6 - access("U"."ID_GRUPO"="G"."ID_GRUPO") Note ----- - this is an adaptive plan (rows marked '-' are inactive) 34 linhas selecionadas. SQL > |
Observe no plano de execução acima, a seção NOTE apresenta a mensagem “this is an adaptive plan”, isto significa que o Otimizador utilizou um novo recurso chamado ADAPTIVE QUERY OPTIMIZATION que permite ao Otimizador alterar o plano de execução durante a execução da instrução SQL. Se você analisar o plano de execução com mais cuidado vai verificar que existem algumas operações que foram assinaladas com o caracter “-” na coluna ID, isto significa que estas operações foram desativadas ou desconsideradas. Como resultado podemos observar que o desempenho desta consulta foi igual ao desempenho da consulta executada na base 11g com os HINTs para melhoria de performance.
CONCLUSÃO
Esta é uma pequena amostra de como o Otimizador do Oracle Database evoluiu nas ultimas versões, se você ainda esta em dúvida se vale a pena fazer o Upgrade para a versão 19c, saiba que alem da manutenção do suporte e das melhorias de segurança, você esta perdendo a oportunidade de melhorar a performance de suas aplicações.
Referências
https://docs.oracle.com/database/121/
Simplesmente sensacional, parabéns pelos ótimos artigos de excelente qualidade.
Obrigado pelo reconhecimento Vilela.
Perfeito, ser´muito aproveitado. Excelente artigo.
Obrigado pelo reconhecimento Michael.
Parabens pelo artigo, muito bem detalhado.
Obrigado pelo reconhecimento, Arnaldo.