Baixe Dicas para otimização de consultasÍndice1.. e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity! Dicas para otimização de consultas Índice 1 .................................................................................................................. 1 .................................................................................................................. 2 .................................................................................................................. 3 Introdução................................................................................................. 3 4 1. Método de Acesso................................................................................ 4 4.1 1.1 Full Table Scan................................................................................... 4 4.2 .................................................................................................................. 4.3 1.2 Table Access by Rowid...................................................................... 4 4.4 1.3 Index Scan.......................................................................................... 5 5 .................................................................................................................. 6 .................................................................................................................. 7 2. Tipos de Join......................................................................................... 8 7.1 2.1 Nested Loops...................................................................................... 8 7.2 2.2 Sort-Merge.......................................................................................... 8 7.3 2.3 Hash Join............................................................................................ 10 7.4 2.5 Star Join.............................................................................................. 11 7.5 2.6 Star transformation............................................................................. 12 8 3. Hints..................................................................................................... 15 8.1 3.1 Tipo de Otimizador............................................................................. 15 8.2 3.2 Subquery / View................................................................................. 15 8.3 3.3 Acesso................................................................................................. 16 8.4 3.4 Join..................................................................................................... 17 8.5 3.5 Parallel Query (PQ)............................................................................ 18 8.6 3.7 Variados.............................................................................................. 18 9 4. Dicas..................................................................................................... 19 9.1 .................................................................................................................. 9.2 4.1 Evitar função em coluna indexada..................................................... 20 9.3 4.2 Join com colunas do mesmo tipo....................................................... 21 9.4 4.3 Coletar as estatísticas com regularidade............................................. 23 9.5 4.4 Sql Trace ............................................................................................ 25 1.3 Index Scan • Realiza o acesso aos dados através de um índice • A maioria é do tipo b*-tree (padrão) Estrutura interna da B*-tree • Possui os seguintes tipos: Unique Scan: é usado quando a tabela possui uma chave única ou primária. Retorna apenas 1 rowid. OPERATION OPTIONS OBJECT_NAME ------------------------- -------------------- ------------------- 0 SELECT STATEMENT . 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC 2 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK Range Scan: é usado para obter um determinado intervalo de dados. Retorna 0 ou mais rowids. OPERATION OPTIONS OBJECT_NAME ------------------------- -------------------- -------------------- 0 SELECT STATEMENT . 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA Full Scan: é usado quando a query usa pelo menos uma coluna do índice. Também é usado quando a consulta não possui predicado e se deseja evitar a operação de sort. Fast Full Scan: é um Full Table Scan alternativo usado quando o índice possui todas colunas necessárias para a query. Para ler todos dados do índice, é mais rápido que o Index Scan por possibilitar o Multiblock I/O e a paralelização. Obs: não pode ser usado para evitar a operação de sort (os dados não são retornados de forma ordenada). OPERATION OPTIONS OBJECT_NAME ------------------------- -------------------- ------------------- 0 SELECT STATEMENT . 1 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA Index Join: é um hash join com alguns índices da tabela. Juntos obtêm todas colunas referenciadas na query. OPERATION OPTIONS OBJECT_NAME ------------------------- ----------------- ----------------------- 0 SELECT STATEMENT . 1 VIEW TESTE.index$_join$_001 2 HASH JOIN . 3 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA 4 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK Bitmap: utiliza um bitmap como chave para cada valor distinto da coluna e uma função de mapeamento que converte cada bit para um rowid. Se a coluna indexada tiver baixa cardinalidade, são rápidos e ocupam pouco espaço. Obs: uma coluna é considerada de baixa cardinalidade se o número de valores distintos for menor ou igual a 1% do total de registros. OPERATION OPTIONS OBJECT_NAME ------------------------- -------------------- -------------------- 0 SELECT STATEMENT . 1 TABLE ACCESS BY INDEX ROWID TESTE.VENDAS 2 BITMAP CONVERSION TO ROWIDS . 3 BITMAP INDEX SINGLE VALUE TESTE.VENDAS_TIPO_BM Exemplo: tabela Customer com 6 registros Índice Bitmap sobre a coluna Region Consulta de clientes casados e da região central e oeste. SELECT COUNT(*) FROM customer WHERE STATUS = 'married' AND REGION IN ('central','west'); 2.3 Hash Join • O Oracle escolhe uma tabela menor para construir a tabela hash e o maior para verificar a tabela hash • Geralmente o Hash Join é mais rápido que o Sort-Merge select /*+ use_hash(l) */ * from tab_loc l, tab_loc_peq p where l.codigo = p.codigo; OPERATION OPTIONS OBJECT_NAME ------------------------- -------------------- -------------------- 0 SELECT STATEMENT . 1 HASH JOIN . 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ 3 TABLE ACCESS FULL TESTE.TAB_LOC 2.5 Star Join • Join realizado entre uma tabela fato e algumas dimensões (tabelas lookup com informações dos atributos da tabela fato) • Geralmente é usado quando o schema tem tabelas fato grandes e dimensões bem pequenas • Cada tabela lookup possui um join com a tabela fato (PK_Lookup = FK_Fato_Lookup), mas as tabelas lookups não têm join entre si. • Os joins são realizados nas seguintes etapas: •..1 produto cartesiano entre as dimensões •..2 join com a tabela fato usando um índice concatenado por nested loops Exemplo de um star join • Não confunfir o start join com o star transformation 2.6 Star transformation • Transformação de uma consulta realizada entre uma tabela fato e algumas dimensões • É realizado apenas se houver redução no custo • Não há necessidade de criar índices concatenados para atender a combinação de tabelas referenciadas, pois combina índices bitmap da tabela fato • Pré-requisitos - Deve existir um índice bitmap para cada FK - STAR_TRANSFORMATION_ENABLED = TRUE - CBO deve estar sendo usado • A consulta é realizada da em 2 fases: 1. O Oracle transforma a consulta e utiliza os índices bitmap das colunas FKs da tabela fato para obter o result set (conjunto exato dos registros necessários para avaliar a consulta). Em seguida os registros da tabela fato são obtidos. 2. O join entre a tabela fato e as dimensões é realizado. Geralmente o hash join é o mais eficiente. Exemplo: Comando original Tabela fato: sales Dimensões : store, time e product SELECT store.sales_district, time.fiscal_period, SUM(sales.dollar_sales) revenue, SUM(dollar_sales) - SUM(dollar_cost) income FROM sales, store, time, product WHERE sales.store_key = store.store_key AND sales.time_key = time.time_key AND sales.product_key = product.product_key AND time.fiscal_period IN ('3Q95', '4Q95', '1Q96') AND product.department = 'Grocery' AND store.sales_district IN ('San Francisco', 'Los Angeles') GROUP BY store.sales_district, time.fiscal_period; Transformação 1ª parte: obtenção do result set SELECT ... FROM sales WHERE store_key IN (SELECT store_key FROM store WHERE sales_district IN ('WEST','SOUTHWEST')) AND time_key IN (SELECT time_key FROM time WHERE quarter IN ('3Q96', '4Q96', '1Q97')) AND product_key IN (SELECT product_key FROM product WHERE department = 'GROCERY'); 2ª parte: join entre o result set e as dimensões 3. Hints Hint é um comentário padronizado que se inclui no meio do comando para modificar o plano de execução. Geralmente ele é utilizado nos casos em que o Oracle não consegue definir uma boa estratégia. Sintaxe: select /*+ Hint... */ colunas ... from tabelas ... Abaixo segue a listagem dos hints para consultas: 3.1 Tipo de Otimizador All_rows – força o cost-based optimizer(CBO) a otimizar o comando para retornar todos registros com o menor tempo possível (maior throughput) First_rows – força o CBO a otimizar o comando para obter o melhor tempo de resposta Rule – força a utilização do rule-based optimizer(RBO) Choose – deixa para o Oracle optar por CBO ou RBO. Se pelo menos uma das tabelas possuir estatísticas coletadas então CBO é optado, senão é considerado o RBO. 3.2 Subquery / View Push_Subq – executa as subqueries antes. Normalmente são executadas por último. Exemplo select /*+ push_subq */ count(*) from tabela_teste where codigo in (select codigo from tabela_teste2 where tipo = ‘T’); Merge(v) – força o merge da view com a query principal Exemplo select /*+ merge(v) */ count(*) from view_teste v, tabela_teste t where v.codigo = t.codigo; No_Merge(v) – previne o merge da view com a query principal Exemplo select /*+ no_merge(v) */ count(*) from view_teste v, tabela_teste t where v.codigo = t.codigo; Merge_Aj – transforma o NOT IN subquery em sort-merge anti- join Hash_Aj – transforma o NOT IN subquery em hash anti-join Merge_Sj - transforma o EXISTS em sort-merge semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops. Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o sort-merge semi- join for usado. Hash_Sj – transforma o EXISTS em hash semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops. Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o hash semi-join for usado. Push_Join_Pred(v) – força a view usar o join predicate da query principal 3.3 Acesso Full(tab) – força o full table scan para tabela ‘tab’ Cache(tab) – força que blocos retornados da consulta sejam colocados na lista LRU do buffer cache quando o full table scan for executado, se o tamanho da tabela for menor ou igual ao valor do parâmetro CACHE_SIZE_THRESHOLD. Nocache(tab) – força que os blocos retornados da consulta não sejam colocados na cache, mesmo que a tabela tenha o parâmetro cache habilitado. Rowid(tab) – força o acesso a tabela ‘tab’ pelo rowid Index(tab index) – força o acesso a tabela ‘tab’ pelo índice ‘index’ Index_Asc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ com um range scan Index_Desc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ na ordem decrescente Ndex_Ffs(tab index) – opta pelo fast full scan ao invés do full table scan Index_Combine(tab i1.. i5) – realiza a combinação booleana de índices bitmap com melhor custo. Index_join(tab i1.. i5) – induz a utilização index join And_Equal(tab i1.. i5) – realiza o merge de 2 a 5 índices com apenas 1 coluna. Use_Concat – transforma a combinação de ORs/IN na cláusula WHERE em uma query composta com operador de conjunto UNION ALL. No_Expand – não permite a concatenação Driving_Site(tab) – a query é executada no site que a tabela ‘tab’ se encontra 3.4 Join Use_Nl(tab) – induz o Oracle a optar a tabela ‘tab’ como sendo a inner table. Use_Merge(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método sort-merge. Use_Hash(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método hash join. Star – induz o Oracle a realizar o start query Star_Transformation – induz o Oracle a optar pelo melhor plano com Star Transformation. A transformação só é realizada se for benéfico. Ordered – induz o Oracle a executar o join na ordem em que as tabelas se encontram na cláusula FROM (da esquerda para direita e de cima para baixo). Leading(tab) - induz o Oracle iniciar o join com a tabela ‘tab’. 4.1 Evitar função em coluna indexada Exemplo: consultar todos registros de um determinado dia Consulta ineficiente select count(*) from tab_loc where trunc(data) = to_date(‘10/10/2003’,‘dd/mm/yyyy’); Resultado Tempo: 2,5s OPERATION OPTIONS OBJECT_NAME ------------------------------ -------------------- ------------------ 0 SELECT STATEMENT . 1 SORT AGGREGATE . 2 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA Statistics --------------------------------------------------- 0 recursive calls 6 db block gets 1196 consistent gets 1196 physical reads 0 redo size 205 bytes sent via SQL*Net to client 261 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed Consulta eficiente select count(*) from tab_loc where data >= to_date(‘10/10/2003’,‘dd/mm/yyyy’) and data < to_date(‘10/10/2003’,‘dd/mm/yyyy’) + 1; Resultado Tempo: 0,1s OPERATION OPTIONS OBJECT_NAME ------------------------------ -------------------- ------------------ 0 SELECT STATEMENT . 1 SORT AGGREGATE . 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA Statistics --------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 205 bytes sent via SQL*Net to client 310 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed 4.2 Join com colunas do mesmo tipo Se isto for inevitável, converta (aplique uma função to_char, to_number, to_date,...) no lado do join que não prejudique o plano de execução. A conversão implícita é custosa, além de ter o mesmo efeito que a utilização de funções em campos indexados. Consulta ineficiente select count(*) from tab_loc l, tab_loc_gra g where l.codigo = g.codigo /* l.codigo é VARCHAR2 e g.codigo é NUMBER */ and g.data = to_date('10/10/2003','dd/mm/yyyy'); Resultado Tempo: 1s OPERATION OPTIONS OBJECT_NAME ------------------------------ -------------------- ---------------------- 0 SELECT STATEMENT . 1 HASH JOIN . 2 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA 3 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA 4 TABLE ACCESS FULL TESTE.TAB_LOC Statistics --------------------------------------------------- 0 recursive calls 4 db block gets 1061 consistent gets 0 physical reads 0 redo size 207 bytes sent via SQL*Net to client 378 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Inclusão da conversão explícita de tipo select count(*) from tab_loc_gra g, tab_loc l where l.codigo = to_char(g.codigo,'fm99999999') /* ambos VARCHAR2 */ and g.data = to_date('10/10/2003','dd/mm/yyyy'); Resultado Tempo: 0,4s Obs: Apenas incluindo a conversão de tipo, o tempo foi reduzido pela metade. Note que o plano de execução não foi alterado. OPERATION OPTIONS OBJECT_NAME ------------------------------ -------------------- ----------------------- 0 SELECT STATEMENT . 1 SORT AGGREGATE . 2 HASH JOIN . 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA 4 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA 5 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK Statistics ----------------------------------------------------- 0 recursive calls 4 db block gets 1061 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 360 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Consulta incluindo a conversão de tipo e Hints select /*+ ordered use_nl(l) */ count(*) from tab_loc_gra g, tab_loc l where l.codigo = to_char(g.codigo,'fm9999999') /* ambos c/ tipo VARCHAR2 */ and g.data = to_date('10/10/2003','dd/mm/yyyy'); Resultado Tempo: 0,01s OPERATION OPTIONS OBJECT_NAME ---------------------------- -------------------- -------------------- 0 SELECT STATEMENT . 1 SORT AGGREGATE . 2 NESTED LOOPS . 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA 4 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA 5 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK Statistics --------------------------------------------------- 0 recursive calls 0 db block gets 39 consistent gets 0 physical reads 0 redo size 206 bytes sent via SQL*Net to client 407 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 4.4 Sql Trace Utilize o sql trace para identificar comandos ineficientes de uma determinada sessão. 1º) Ative o timed_statistics alter system set timed_statistics=true; 2º) Ative o trace • Se for a própria sessão execute: alter session set sql_trace=true; • Se for outra sessão execute: exec sys.dbms_system.set_sql_trace_in_session(<sid>,< serial#>,true); 3º) Depois de um certo tempo desative o trace • Se for a própria sessão execute: alter session set sql_trace=false; • Se for outra sessão execute: exec sys.dbms_system.set_sql_trace_in_session(<sid>,< serial#>,false); 4º) Desative o timed_statistics alter system set timed_statistics=false; 5º) Localize o trace gerado (sempre no servidor) • Os traces serão gerados no diretório definido para o parâmetro USER_DUMP_DEST. Para identificá-lo execute: select value from v$parameter where name = 'user_dump_dest'; • O nome do arquivo pode ser obtido com o sguinte comando: select 'ora_'||spid||'_'||lower(i.instance_name)||'.trc' arquivo from v$process p, v$session s, v$instance i where p.addr = s.paddr and s.sid = <sid>; 6º) Formate o arquivo trace com o tkprof. tkprof arq_trace arq_saida explain=usuário/senha sys=no sort=opção arq_trace – nome do arquivo trace arq_saida – nome do arquivo de saída (formatado) opção - pode ser qualquer um dos valores abaixo: prscnt number of times parse was called prscpu cpu time parsing prsela elapsed time parsing prsdsk number of disk reads during parse prsqry number of buffers for consistent read during parse prscu number of buffers for current read during parse prsmis number of misses in library cache during parse execnt number of execute was called execpu cpu time spent executing exeela elapsed time executing exedsk number of disk reads during execute exeqry number of buffers for consistent read during execute execu number of buffers for current read during execute exerow number of rows processed during execute exemis number of library cache misses during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchela elapsed time fetching fchdsk number of disk reads during fetch fchqry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchrow number of rows fetched userid userid of user that parsed the cursor Exemplo: formatar o trace de forma que os comandos estejam ordenados pelo tempo gasto no fetch. tkprof ora_33_mig.trc saida.txt explain=usr/usr sys=no sort=fchela