Otimização de ambiente Oracle – SQL Performance and Tuning de SQL’s
Encontramos poucas coisas sobre performance de aplicação (SQL’s) e por isso, resolve compartilhar meus conhecimentos para uma possível resolução de problemas para SQL’s com baixa performance.
Primeiramente precisamos entender o seguinte:
Ao identificar e ajustar as instruções SQL maiores, você pode melhorar o desempenho de todo o sistema. Determinar a extensão do problema para concentrar seus esforços nas soluções que proporcionam o máximo benefício. O problema de desempenho do SQL às vezes é causada pela falta de recursos (slaves) em uma execução paralela.
O que é um SQL mal escrito??
Usar mais recursos do que o necessário;?Pode ter as seguintes características:
Excessivo parse, excessivo I/O, excessivo tempo de CPU e excessivas esperas.
As alterações que podem desencadear um problema de performance:?
- Upgrade de Database;?
- Coleta de estatísticas;?
- Esquema alterado;?
- Alteração de parametros do Database;?
- Mudança da aplicação;?
- Alteração no SO ou hardware;?
- Volume de Dados alterados ou mais conexões simultâneas.
Sempre verifique o básico primeiro, certifique-se:?
- Que as estatísticas estão atualizadas e são coletadas corretamente;?
- Que os parâmetros de inicialização estão definidos corretamente;?
- Que o otimizador esta definido adequadamente;?
- Que o uso de hints são válidos e usados apropriadamente.
Para resultados rápidos que necessitem intervenção rápida podemos primeiramente, tenar as seguintes alterações, elas são propensas a dar melhores resultados no menor tempo possível:
- OPTIMIZER_MODE: Se o modo do otimizador esta em ALL_ROWS, use FIRST_ROWS_N (escolha um valor para N, que reflete o número de linhas que o usuário precisa ver de imediato) ou vice-versa.?
- OPTIMIZER_FEATURES_ENABLE: Se uma consulta é melhor executada em uma versão mais antiga (exemplo, antes da migração), use este parâmetro para “reverter” o otimizador para a versão antiga. A partir do 10G é possivel você definir este parametro a nível de sessão, melhor do que em todo o sistema.?
- Dynamic sampling: Aborda amostras do número de linhas retornadas pela consulta e determina as estimativas de seletividade muito preciso que muitas vezes levam a bons planos de execução
Podemos também, coletar as estatísticas do Hardware, isso deverá ocorrer durante o período onde o sistema é mais utilizado.
Essa coleta ajudará ao otimizador Oracle a encontrar o melhor acesso entre indices e full scan de tabelas
Coletar informações durante um intervalo de tempo (minutos)
SQL> EXECUTE DBMS_STATS.gather_system_stats('interval', interval => 180);Para checarmos se as estatísticas foram realmente coletadas:
SQL> select pname, pval1 from sys.aux_stats$;Devemos também, checar o seguinte:
- SQL’s mal escritos – podemos utilizar o AWR onde será apresentado os SQL que mais consomem recursos no ambiente?
- Indices ( usados / não usados ) Index Monitoring – Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command (Doc ID 136642.1)?
- Falta de indice – Tabelas sem indices?- Order de join errada?
- Tipo errado – conversão de dados no momento da pesquisa (WHERE)?
- Views
Para a checagem acima, podemos considerar as possíveis soluções:
- Coletar estatatisticas corretamente;?
- Criar novo indice ou recriar um indice existente;?
- Utilizar o Adivsors SQL com a opção de Tuning Pack;?
- Use hints para obter o melhor plano;?
- Remova hints para que influenciam na escolha do indice;?
- Eliminar conversões de tipos de dados;?
- Criar um indice baseado em função;?
- Colocar os dados na ordem da chave;?
- Coletar estatísticas de SO.
Existem várias maneiras que você pode melhorar a eficiência da instrução SQL:
- Verificando as estatísticas de otimização;?
- Rever o plano de execução;?
- Reestruturar as instruções SQL ineficientes;?
- Reestruturação dos índices;?
- Modificação ou desativar os gatilhos e restrições;?
- Reestruturar os dados;?
- Manter os planos de execução estáveis ao longo do tempo.
Para ajustarmos um bom SQL devemos entender o que é Seletividade e Cardinalidade:
- Seletividade é a proporção estimada de um conjunto de linhas recuperadas por um predicado particular ou combinação de predicados.
- Cardinalidade é o numero total de linhas de uma tabela, view ou o resultado de uma junção ou um operador GROUP BY.
Agora uma dica que ajudará, em muito ajustes de SQL’s, Histograma.
Podemos definir histograma no Oracle como uma cesta de frutas, ou seja, imaginem uma cesta de frutas com: 12 bananas, 2 peras, 1 maça e 1 Kiwi. Quando olhamos para a cesta de frutas pensamos: Nossa só tem banana?
Para o otimizador do Oracle isso não acontece, o otimizador parte do principio que há valores uniformes onde a distribuição das frutas são parecidas (não há só bananas). Quando utilizamos o histograma para esta coluna, o otimizador parte do príncipio de que a distribuição de dados não corresponde às suposições default ( 4 tipos de frutas ) somente.
Precisamos coletar histograma para esta coluna, pois precisamos informar ao otimizador que 75% desta coluna é de bananas.
Como devemos coletar histogramas para determinadas colunas?
Quando esta coluna é frequentemente usada na da clausula WHERE de uma consulta.?Maiores detalhes podem ser vistos em – Histograms: An Overview (10g and Above) (Doc ID 1445372.1)
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS (NULL,'EMP', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');Um detalhe importante:?
Um histograma é muito eficiente quando a coluna possui um indice, ou seja, se a coluna é indexada, o histograma pode dizer ao otimizador que, para um valor de baixa cardinalidade o acesso do índice será mais eficiente ( mais rápido ), enquanto que para um valor de alta cardinalidade uma varredura completa da tabela será mais eficiente.
Um exemplo de Histograma
Podemos observar abaixo:
[SYS@orcl] select * from v$version;
BANNER
-----------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
[SYS@orcl]Informações das colunas para a tabela SEM histograma
[SH@orcl] select
2 column_name, num_distinct, histogram, num_buckets,
3 to_char(last_analyzed,'yyyy-dd-mm hh24:mi:ss') last_analyzed
4 from user_tab_col_statistics
5 where table_name='SALES'
6 /
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ --------------- ----------- -------------------
QUANTITY_SOLD 1 NONE 1 2013-19-08 08:31:02
AMOUNT_SOLD 3586 NONE 1 2013-19-08 08:31:02
PROMO_ID 4 NONE 1 2013-19-08 08:31:02
CHANNEL_ID 4 NONE 1 2013-19-08 08:31:02
TIME_ID 1460 NONE 1 2013-19-08 08:31:02
CUST_ID 7059 NONE 1 2013-19-08 08:31:02
PROD_ID 72 NONE 1 2013-19-08 08:31:02
7 rows selected.
Elapsed: 00:00:00.02Agora uma consulta simples, selecionamos os registros para um determinado prod_id (136) e podemos verificar abaixo o plano de execução, a quantidade de linhas e bytes utilizados.
[SH@orcl] set autotrace traceonly
[SH@orcl] select prod_id, amount_sold from sales where prod_id=136
2 /
710 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 511273406
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12762| 112K| 447 (0)| 00:00:06 | | |
| 1 | PARTITION RANGE ALL | | 12762| 112K| 447 (0)| 00:00:06 | 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 12762| 112K| 447 (0)| 00:00:06 | 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| | | | | 1 | 28 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=136)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
7154 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
710 rows processedAgora coletamos as estatísticas para as colunas com histograma (utilizamos a opção skewonly na opção method_opt do dbms_stats como no exemplo abaixo:
[SH@orcl] begin
2 dbms_stats.gather_table_stats(
3 ownname => 'SH' ,
4 tabname => 'SALES' ,
5 estimate_percent => 100 ,
6 method_opt => 'for all columns size skewonly' ,
7 cascade => true);
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.98Podemos observar abaixo, que as colunas HISTOGRAM e NUM_BUCKETS alteraram.
[SH@orcl] select
2 column_name, num_distinct, histogram, num_buckets,
3 to_char(last_analyzed,'yyyy-dd-mm hh24:mi:ss') last_analyzed
4 from user_tab_col_statistics
5 where table_name='SALES'
6 /
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ --------------- ----------- -------------------
QUANTITY_SOLD 1 FREQUENCY 1 2013-19-08 08:35:52
AMOUNT_SOLD 3586 HEIGHT BALANCED 254 2013-19-08 08:35:52
PROMO_ID 4 FREQUENCY 4 2013-19-08 08:35:52
CHANNEL_ID 4 FREQUENCY 4 2013-19-08 08:35:52
TIME_ID 1460 NONE 1 2013-19-08 08:35:52
CUST_ID 7059 HEIGHT BALANCED 254 2013-19-08 08:35:52
PROD_ID 72 FREQUENCY 72 2013-19-08 08:35:52
7 rows selected.
Elapsed: 00:00:00.02Agora executamos novamente o mesmo SELECT e podemos observar que a quantidade de linhas e bytes diminui e a consulta ficará mais rápida. Agora imagine essa consulta sendo executada por muitos usuários, o ganho de tempo será excelente.
[SH@orcl] select prod_id, amount_sold from sales where prod_id=136
2 /
710 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 511273406
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 710 | 6390 | 150 (0)| 00:00:02| | |
| 1 | PARTITION RANGE ALL | | 710 | 6390 | 150 (0)| 00:00:02| 1 | 28 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 710 | 6390 | 150 (0)| 00:00:02| 1 | 28 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=136)
Statistics
---------------------------------------------------------
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
7154 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
710 rows processed
[SH@orcl]Referências
- How to Collect and Display System Statistics (CPU and IO) for CBO use (Doc ID 149560.1)
- Bug 9842771 – Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID 9842771.8]
- Oracle Database Performance Tuning Guide and the Oracle Performance Diagnostic Guide (MOS note 390374.1)
- SQL tuning, MOS master note 199083.1, SQL Query Performance Overview
Espero ter contribuido para as possíveis dúvidas e ajudar na identificação de problemas de performance em um ambiente de Banco de Dados Oracle.
Até mais.
