Tunning Oracle

7/27/2019 Tunning Oracle

1/130

Oracle Performance Diagnostics & Tuning

9iR1 a 11gR2

Ricardo Portilho [email protected]

1


2/130

Performance de Sistemas Computacionais s pode ser medida em TEMPO. Performance Tuning deve ser reativa. Performance Tuning deve ter ROI.

Apenas os maiores gargalos devem ser solucionados. O processo deve ser Diagnostics, e depois Tuning. Alto consumo de CPU no um problema. O usurio no executa um SQL por prazer. O desenvolvedor no deveria saber como fazer um bom SQL (COBOL?). Ferramentas Grficas / Enterprise Manager / Wizards / Automao so bons auxiliares.

Bancos com bom desempenho devem ser observados. Conhea outros RDBMSs: TI no lugar para paixes. No acredite em nada (separar tabelas e ndices?). Teste.Se houvesse um parmetro que sempre deixasse o Oracle mais rpido, sem nenhum efeitocolateral, ele j viria habilitado. Desenvolva um mtodo de convencimento gerencial. Por algo chamar-se Storage, no quer dizer que ele no tenha problemas.

KISS (Keep It Simple, Stupid): a probabilidade de falha cresce linearmente com o aumento decomplexidade. Saiba diser No. Saiba dizer No sei.

2

Minha abordagem


3/130

Performance Diagnostics & Tuning

3


4/130

4

Mistificao


5/130

Mtodos Antigos

5


6/130

Experincia Intuio

Impreciso Tempo Sorte Recursos

6

Requisitos


7/1307

TOP Tuning

Verificar maior consumidor de CPU; Verificar o SQL agressor;

Alterar o SQL e esperar que o desempenho melhore; Adicionar ndices e esperar que o desempenho melhore; Se no melhorar, matar a sesso. Se o desempenho no melhorar, voltar ao incio.


8/130

Verificar Sistema Operacional (free / taskmgr / Performance Monitor); Verificar Sistema Operacional (vmstat / taskmgr / Performance Monitor);

Verificar Sistema Operacional (iostat / taskmgr / Performance Monitor); Verificar SGA; Verificar PGA; Verificar coleta de estatsticas; Verificar parmetros do Oracle; Verificar fragmentao de tabelas;

Verificar LOCKs; Verificar SQLs que consomem mais recursos; Construir uma teoria baseada nos dados observados; Alterar algo e esperar que o desempenho melhore; Se o cliente no gostar da teoria, apenas cite e altere alguns parmetros

relacionados; Se o desempenho no melhorar, voltar ao incio.

8

Checklist Tuning


9/130

Verificar Buffer Cache Hit Ratio; Verificar Data Dictionary Hit Ratio;

Verificar SQL Cache Hit Ratio; Verificar Library Cache Hit Ratio; Construir uma teoria baseada nos dados observados; Alterar algo (geralmente aumentar) e esperar que o desempenho melhore; Se o desempenho no melhorar, voltar ao incio.

9

Ratios Tuning


10/130

KIWI = Kill It With Iron; Adicionar Memria RAM;

Adicionar CPUs; Melhorar o I/O; Migrar para um Servidor maior; Migrar para RAC; Adicionar Ns no RAC;

Pagar a conta, e esperar que o desempenho melhore. Se o desempenho no melhorar, voltar ao incio.

10

KIWI Tuning


11/130

Migrar Banco para outro servidor; Executar Upgrade de Banco de Dados;

Executar Upgrade da Aplicao; Executar Upgrade do Middleware; Juntar Aplicao e Banco de Dados; Separar Aplicao e Banco de Dados; Voltar Backups;

Se o desempenho no melhorar, tentar outra coisa, at melhorar.

11

Manager Tuning


12/130

O que est errado?

12


13/130


14/13014

Lendas do Oracle


15/130

Todo teu SELECT dever utilizar um ndice, para que ele seja rpido. Ters uma rea de SWAP com o dobro de tua RAM. No utilizars mais que 50% de tua RAM para a SGA. Utilizars HINTs, pois tu s mais sbio que o Oracle. No coletars estatsticas do dicionrio de dados. Devers separar teus dados e ndices. Devers separar teus dados em diversas TABLESPACEs. Teus DATAFILEs devero ter no mximo 2GB / 10GB / xGB. No habilitars AUTOEXTEND ON. Devers executar COMMIT a cada N linhas. Utilizars RAID 5, pois mais rpido para leituras

No permitirs mais que um SWITCH a cada 20 minutos. Mas no ters grandes REDO LOGs. Executars REBUILD de ndices regularmente. Executars MOVE de tabelas regularmente. Se grande a tabela tornar-se, a particionars. Se quiseres mais velocidade, usars RAC. Quanto mais CPUs, mais rpido teu banco de dados ser. Se teus RATIOS estiverem altos, felizes estaro teus usurios.

Sempre que possvel, aumentars seu DB_CACHE_SIZE e SHARED_POOL. Desabilitars o AWR, pois ele causa lentido. No utilizars memria automtica. Tu s mais sbio que o Oracle. Se usar, deixars a SGA_TARGET um pouco menor que a SGA_MAX_SIZE. AUTOMATIC SQL TUNING um dos cavaleiros do apocalipse.

15

Lendas do Oracle


16/130

Seu filho leva 2 horas para comprar leite na padaria, de carro.

Como melhorar este tempo? necessrio um carro mais rpido? So necessrios dois carros? necessrio tornar a estrada mais larga? melhor s comprar 1 litro de leite de cada vez?

Deve-se utilizar uma padaria que s tenha 1 tipo de leite? A porta da garagem deve estar sempre aberta?

16

O carro e o leite


17/130

O carioca precisa acordar s 03:30 para chegar em SP s 09:00.

Como melhorar este tempo?Mas Utilizar um Concorde? Voar mais rente ao solo? Tornar o avio mais leve, transportando menos pessoas e bagagem?

17

Ponte Area


18/13018

O chefe e o atraso


19/13019

Como saber quem vence?


20/130

O Mtodo Correto

20


21/130

21

A Peste Negra


22/130

22

Tempo


23/130

Tempo Computacional

23

R = S + W

OU

Response Time = Service Time + Wait Time


24/130

24

Instrumentao: Mainframe


25/130

25

Instrumentao: Solaris


26/130

Oracle Wait Interface

26

O l W it I t f


27/130

27

Oracle Wait Interface

N i t d OWI


28/130

Benchmark 7.0.12: Juan Loainza YAPP Paper: Anjo Kolk

28

Nascimento da OWI

E l d OWI


29/130

Verso 7.0.12: 104 Wait Events Verso 8: 140 Wait Events Verso 8i: 220 Wait Events Verso 9i: 400 Waits Events Verso 10gR1: >800 Wait Events Verso 11gR2: >1100 Wait Events

29

Evoluo da OWI

E t i M


30/130

30

Enterprise Manager


31/130

Conceitos Bsicos

31

A it t O l


32/130

32

Arquitetura Oracle

P t l t


33/130

db_block_size db_file_multiblock_read_count

memory_max_target memory_target sga_max_size sga_target pga_aggregate_target

db_cache_size (db_2k_cache_size, db_4k_cache_size, db_8k_cache_size...) buffer_pool_keep, buffer_pool_recycle shared_pool_size, shared_pool_reserved_size large_pool_size java_pool_size streams_pool_size

result_cache_max_result, result_cache_max_size, result_cache_mode log_buffer fast_start_mttr_target log_checkpoint_interval, log_checkpoint_timeout

33

Parmetros elementares


34/130

Hands ON !

Parmetros elementares

34

Lab 1 1: Parmetros elementares


35/130

Verifique os parmetros elementares em seu banco de dados. Altere o parmetro memory_max_targetpara 0; Altere o parmetro memory_targetpara 0; Altere o parmetro sga_max_size para metade da RAM da mquina; Altere o parmetro sga_targetpara 0; Altere o parmetro db_cache_size para metade do sga_max_size. Altere o parmetro shared_pool_size para metade do db_cache_size. Altere o parmetropga_aggregated_targetpara 100M;

35

Lab 1.1: Parmetros elementares

Granularidades de Anlise


36/130

SQL Statement Session Instance

36

Granularidades de Anlise

Cenrios de Anlise


37/130

37

Cenrios de Anlise

H lentido agora Tivemos lentido ondem

Ferramentas de Anlise


38/130

Dynamic Performance Views Extended SQL Trace (Event 10046) Statspack / AWR

38

Ferramentas de Anlise

Wait Classes


39/130

Administrative Application Cluster Commit Concurrency Configuration Idle Network

Other Queueing Scheduler System I/O User I/O

39

Wait Classes


40/130

Limitaes da OWI

40

Limitaes: OWI


41/130

No um monitoramento End-to-End Sem dados de consumo de CPU Sem dados de consumo de Memria Bugs Imprecises

41

Limitaes: OWI

Limitaes: Views


42/130

Sem histrico

42

Limitaes: Views

Limitaes: Extended SQL Trace


43/130

Muitos dados Altssima granularidade Desempenho Correlao de informaes Sesses PARALLEL Sesses SHARED SERVER Waits s disponveis em >=9iR1 Suporte oficial s em >10gR1

43

Limitaes: Extended SQL Trace

Limitaes: Statspack / AWR


44/130

Baixa granularidade Apenas histrico

44

Limitaes: Statspack / AWR


45/130

Hands ON !

Dynamic Performance Views

45

Lab 2 1: Views


46/130

V$SYSTEM_EVENT V$SESSION_EVENT

V$SESSION_WAIT

Verifique as Dynamic Performance Views da OWI em seu banco de dados. Quais suas colunas mais importantes? Que Waits voc tem em seu banco de dados? Habitue-se a seu contedo.

46

Lab 2.1: Views


47/130

Wait Events mais comuns

47



48/130

buffer busy free buffer read by oher session

control file single write / control file parallel write / control file sequential read db file single write / db file parallel read / db file parallel write db file scatteread read / db file sequential read direct path read / direct path write enqueue free buffer latch free / latch: library cache / latch: cache buffers chains

library cache pin / library cache lock log buffer space log file parallel write / log file single write / log file sequential read log file switch (archiving needed) log file switch (checkpoint incomplete) / log file switch completion log file sync SQL*Net message from client / SQL*Net message to client SQL*Net more data from client / SQL*Net more data to client SQL*Net break/reset from client / SQL*Net break/reset to client

48



49/130

Hands ON !

Simulando Wait EventsGravaes

49

Lab 3.1: Gravaes


50/130

Habilite o usurio SCOTT.SQL> ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY TIGER;SQL> GRANT SELECT ANY DICTIONARY TO SCOTT;

Abra uma sesso com o SCOTT com SET TIMING ON.SQL> CONN SCOTT/TIGERSQL> SET TIMING ON

Em outra sesso, com o SYS, verifique (vrias vezes seguidas) o contedo

da V$SESSION_WAIT durante a execuo dos comandos do SCOTT aseguir.

Com o usurio SCOTT, crie uma grande tabela, com pelo menos 4GB.SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;SQL> INSERT INTO T SELECT * FROM T;SQL> INSERT INTO T SELECT * FROM T;SQL> ...SQL> INSERT INTO T SELECT * FROM T;SQL> COMMIT;

50

Lab 3.1: Gravaes

Lab 3.2: Gravaes


51/130

Feche e abra a sesso com o SCOTT com SET TIMING ONSQL> CONN SCOTT/TIGERSQL> SET TIMING ON

Em outra sesso, com o SYS, verifique o contedo da V$SESSION_EVENTrelacionado a sesso do SCOTT.SQL> SELECT SID FROM V$SESSION WHERE USERNAME = 'SCOTT';SQL> SELECT EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, AVERAGE_WAIT FROMV$SESSION_EVENT WHERE SID = 17 ORDER BY 4;

Com o usurio SCOTT, duplique a grande tabela.SQL> CREATE TABLE T2 AS SELECT * FROM T;

Na sesso do SYS, aps a duplicao da tabela, verifique novamente ocontedo da V$SESSION_EVENT relacionado a sesso do SCOTT

Remova a tabela T2, feche e abra a sesso com o SCOTT, e repita aoperao. Durante a repetio da operao, verifique as mudanas docontedo da V$SESSION_EVENT relacionado a sesso do SCOTT.

51

Lab 3.2: Gravaes

Lab 3.3: Gravaes


52/130

Responda as seguintes perguntas:- Onde foi gasto mais tempo nesta sesso?- A que se referem os maiores Wait Events?

- Qual dos maiores Wait Events podem ser reduzidos?- A eliminao de um Wait Event que pode ser reduzido, causar uma melhoria de quanto tempo?- Como reduzir este Wait Event?

Corrija a causa deste Wait Event.

Remova a tabela T2, feche e abra a sesso com o SCOTT, e repita a operao.

52

Lab 3.3: Gravaes

Lab 3.4: Gravaes


53/130

Responda as seguintes perguntas:- Onde foi gasto mais tempo nesta sesso?- A que se referem os maiores Wait Events?

- Qual dos maiores Wait Events podem ser reduzidos?- A eliminao de um Wait Event que pode ser reduzido, causar uma melhoria de quanto tempo?- Como reduzir este Wait Event?

Corrija a causa deste Wait Event.

Remova a tabela T2, feche e abra a sesso com o SCOTT, e repita a operao.

53

Lab 3.4: Gravaes

Lio de casa


54/130

Verifiquem as Dynamic Performance Views de seus servidores. Tragam suas dvidas para a aula.

54

Lio de casa


55/130

Extended SQL Trace

55

Nveis Extended SQL Trace


56/130

0 = Standard Trace 4 = Bind Variables 8 = Wait States 12 = Bind Variables + Wait States

56

SQ

Ativar Extended SQL Trace


57/130

Em toda a instncia Em sua sesso Em outra sesso

57

Q

Detalhes Extended SQL Trace


58/130

58

Q

*** 2010-03-22 11:43:12.276WAIT #9: nam='db file scattered read' ela= 183330 file#=4 block#=9124 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 2528 file#=4 block#=9150 blocks=26 obj#=74574

WAIT #9: nam='db file scattered read' ela= 170358 file#=4 block#=9176 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 96261 file#=4 block#=9202 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 1669 file#=4 block#=9228 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 26055 file#=4 block#=9254 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 4760 file#=4 block#=9280 blocks=26 obj#=74574WAIT #9: nam='db file scattered read' ela= 108783 file#=4 block#=9306 blocks=26 obj#=74574tim=1269268992840594

=====================

tkprof


59/130

59

p

Limitaes: Extended Trace


60/130

No um monitoramento End-to-End Sem dados de consumo de CPU Sem dados de consumo de Memria

Bugs Imprecises Muitos dados Altssima granularidade Desempenho Correlao de informaes Sesses PARALLEL Sesses SHARED SERVER Waits s disponveis em >=9iR1 Suporte oficial s em >10gR1

60


61/130

Hands ON !

Extended SQL TraceFull Table Scan

61

Lab 4.1: Extended SQL Trace


62/130

Feche e abra a sesso com o SCOTT com SET TIMING ONSQL> EXIT$ sqlplus SCOTT/TIGER

SQL> SET TIMING ON

Com o usurio SYS, habilite o Extended Trace para a sesso do SCOTT:SQL> SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_IDFROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.USERNAME= 'SCOTT';SQL> oradebug setospid 8708;SQL> oradebug tracefile_name;SQL> oradebug unlimit;SQL> oradebug event 10046 trace name context forever, level 12;

Em outro terminal, verifique o contedo do Trace.$ tail -f /u01/app/oracle/diag/rdbms/test11gr2/TEST11GR2/trace/TEST11GR2_ora_8708.trc

62

Lab 4.2: Extended SQL Trace


63/130

Com o usurio SCOTT, apague o contedo da grande tabela, altere o valordo parmetro db_file_multiblock_read_count (apenas na sesso) e reinsiraos dados.

SQL> TRUNCATE TABLE T2;SQL> ALTER SESSION SET db_file_multiblock_read_count = 8;SQL> INSERT INTO T2 SELECT * FROM T;SQL> COMMIT;

Continue verificando o contedo do Trace durante a execuo da operao.

Ao trmino da execuo, verifique os valores de V$SESSION_EVENT dasesso do SCOTT. Guarde este resultado.

Execute o tkprof nos Trace gerado.$ tkprof /u01/app/oracle/diag/rdbms/test11gr2/TEST11GR2/trace/TEST11GR2_ora_8708.trc

Analise o relatrio gerado pelo tkprof.

Repita a operao, mas com db_file_multiblock_read_count de 50 e 1000.

63


64/130

Wait Events - Detalhes

64

Ensinar a Pescar


65/130

65

Referncia


66/130

66

Performance Tuning Guide


67/130

67

MOS


68/130

68

buffer busy


69/130

Explicao: O bloco solicitado est em uso, pois outra sesso est carregando obloco para o DB_CACHE_SIZE, ou outra sesso est utilizando o bloco noDB_CACHE_SIZE em um modo incompatvel.

Causa: DB_CACHE_SIZE insuficiente, ou SQL ineficiente.Correo: Aumente o DB_CACHE_SIZE ou altere o SQL.

P1: Nmero do DATAFILE.P2: Nmero do bloco.P3: ID a solicitao vem de diferentes locais da sesso.

69

buffer busy


70/130

70

free buffer


71/130

Explicao: O RDBMS aguarda blocos de DB_CACHE_SIZE livres.Causa: DB_CACHE_SIZE insuficiente.Correo: Aumente o DB_CACHE_SIZE.

P1: Nmero do DATAFILE.P2: Nmero do bloco.

71

read by other session


72/130

Explicao: O bloco solicitado est em uso, pois outra sesso est carregando obloco para o DB_CACHE_SIZE, ou outra sesso est utilizando o bloco noDB_CACHE_SIZE em um modo incompatvel.

Causa: DB_CACHE_SIZE insuficiente, ou SQL ineficiente.Correo: Aumente o DB_CACHE_SIZE ou altere o SQL.

P1: Nmero do DATAFILE.P2: Nmero do bloco.P3: Razo (=10g).

72

control file parallel write


73/130

Explicao: Espera de I/O para gravar em CONTROLFILEs.Causa: Excesso de gravao nos CONTROLFILEs ou I/O ineficiente.Correo: Minimize as gravaes nos CONTROLFILEs ou melhore o mecanismo

de I/O.

P1: Quntidade de CONTROLFILEs.P2: Quantidade de blocos.P3: Quantidade de solicitaes de I/O.

73

control file single write


74/130

Explicao: Espera de I/O para gravar em CONTROLFILEs.Causa: Excesso de gravao nos CONTROLFILEs ou I/O ineficiente.Correo: Minimize as gravaes nos CONTROLFILEs ou melhore o mecanismo

de I/O.

P1: Nmero do CONTROLFILE.P2: Nmero do bloco.P3: Quantidade de blocos.

74

control file sequential read


75/130

Explicao: Espera de I/O para ler os CONTROLFILEs.Causa: Excesso de leitura nos CONTROLFILEs ou I/O ineficiente.Correo: Minimize as leituras nos CONTROLFILEs ou melhore o mecanismo de

I/O.

P1: Nmero do CONTROLFILE.P2: Nmero do bloco.P3: Quantidade de blocos.

75

db file parallel write


76/130

Explicao: Gravaes de dados nos DATAFILEs esperam pelo I/O.Causa: Excesso de gravaes ou lentido de I/O.Correo: Minimize as gravaes ou melhore o mecanismo de I/O.

P1: Quantidade de requisies.P2: Interrupt.P3: Timeout.

76

db file single write


77/130

Explicao: Uma gravao no HEADER do DATAFILE esperam pelo I/O.Causa: Excesso de gravaes no HEADER dos DATAFILEs ou lentido de I/O.Correo: Minimize as gravaes no HEADER dos DATAFILEs ou melhore o

mecanismo de I/O.

P1: Quantidade de requisies.P2: Interrupt.P3: Timeout.

77

db file parallel read


78/130

Explicao: Durante RECOVER ou durante PREFETCHING, leituras deDATAFILEs esperam pelo I/O.Causa: RECOVER muito longo, PREFETCHING excessivo, ou lentido de I/O.

Correo: Acelere o RECOVER, minimize o PREFETCHING, ou melhore omecanismo de I/O.

P1: Quantidade de DATAFILEs.P2: Quantidade de blocos.P3: Quantidade de requisies.

78

User I/O


79/130

79

Influenciando o Otimizador


80/130

CURSOR_SHARING DB_FILE_MULTIBLOCK_READ_COUNT OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MODE PGA_AGGREGATE_TARGET STAR_TRANSFORMATION_ENABLED

80

db file scattered read


81/130

Explicao: Durante FTS, leituras de DATAFILEs esperam pelo I/O.Causa: DB_CACHE_SIZE insuficiente, FTS desnecessrio ou lentido de I/OCorreo: Aumente o DB_CACHE_SIZE, elimine o FTS, ou melhore o

mecanismo de I/O.

P1: Nmero do DATAFILE.P2: Bloco inicial.P3: Quantidade de blocos.

81

db file sequential read


82/130

Explicao: Durante a leitura de um bloco, leituras de DATAFILEs esperam pelomecanismo de I/O.Causa: DB_CACHE_SIZE insuficiente, leitura desnecessria ou lentido de I/O

Correo: Aumente o DB_CACHE_SIZE, elimine a leitura desnecessria, oumelhore o mecanismo de I/O.

P1: Nmero do DATAFILE.P2: Bloco inicial.P3: Quantidade de blocos.

82

direct path read / direct path write


83/130

Explicao: Leitura / gravao entre DATAFILEs / TEMPFILEs e PGA.Causa: PGA insuficiente, ou lentido de I/O.Correo: Aumente a PGA, ou melhore o mecanismo de I/O.

P1: Nmero do arquivo (DATAFILE ou TEMPFILE).P2: Bloco inicial.P3: Quantidade de blocos.

83

enqueue


84/130

Explicao: Mecanismo de fila ordenada do RDBMS.Causa: Diversas, dependendo do tipo de fila.Correo: Diversas, dependendo do tipo de fila.

P1: Tipo ou modo da enqueue.P2: ID1 (como na V$LOCK).P3: ID2 (como na V$LOCK).

Problemas mais comuns: TX, Transaction TM, DML Enqueue HW, High-Water Lock SQ, Sequence Number Enqueue CF, Controlfile Transaction

84

latch free


85/130

Explicao: Mecanismo de fila desordenada do RDBMS.Causa: Diversas, dependendo do tipo de fila.Correo: Diversas, dependendo do tipo de fila.

P1: Endereo da Latch (como na V$LATCH).P2: Nmero da Latch (como na V$LATCH).P3: Quantidade de tentativas.

Problemas mais comuns: shared pool library cache cache buffers lru chain cache buffers chains row cache objects

85

library cache pin / library cache lock


86/130

Explicao: Uso incompatvel do objeto entre duas sesses.Causa: Uso do objeto de forma incompatvel entre duas sesses.Correo: Finalizar o uso do objeto por uma das sesses.

P1: Endereo do objeto.P2: Endereo do load lock.P3: Mode + Namespace.

SQL> SELECT /*+ ORDERED */ W1.SID WAITING_SESSION, H1.SID HOLDING_SESSION,

W.KGLLKTYPE LOCK_OR_PIN, W.KGLLKHDL ADDRESS,DECODE(H.KGLLKMOD,0,None,1,Null,2,Share,3,Exclusive,'Unknown) MODE_HELD,DECODE(W.KGLLKREQ,0,None,1,Null,2,Share,3,Exclusive,'Unknown) MODE_REQUESTEDFROM DBA_KGLLOCK W, DBA_KGLLOCK H, V$SESSION W1, V$SESSION H1 WHERE(((H.KGLLKMOD != 0) AND (H.KGLLKMOD != 1) AND ((H.KGLLKREQ = 0) OR (H.KGLLKREQ = 1)))

AND (((W.KGLLKMOD = 0) OR (W.KGLLKMOD= 1)) AND ((W.KGLLKREQ != 0) AND (W.KGLLKREQ !=1)))) AND W.KGLLKTYPE = H.KGLLKTYPE AND W.KGLLKHDL = H.KGLLKHDL AND W.KGLLKUSE =

W1.SADDR AND H.KGLLKUSE = H1.SADDR;SQL> SELECT TO_NAME FROM V$OBJECT_DEPENDENCY WHERE TO_ADDRESS =0700000010F62750';

86

log buffer space


87/130

Explicao: Mais espao no LOG_BUFFER necessrio para gravaes.Causa: LOG_BUFFER insuficiente, REDO LOGs insuficientes, ou I/O lento.Correo: Aumente o LOG_BUFFER, aumente a quantidade / tamanhode REDO

LOGs, ou melhore o mecanismo de I/O.

P1: Quantidade de REDO LOGs.P2: Quantidade de blocos do sistema operacional.P3: Quantidade de requisies de I/O.

87

log file parallel write


88/130

Explicao: Durante gravao de REDO LOGs, o LGWR espera pelo I/O.Causa: Excesso de membros nos grupos de REDO LOGs ou lentido de I/O.Correo: Reduza a quantidade de membros nos grupos de REDO LOGs ou

melhore o mecanismo de I/O.

P1: Quantidade de REDO LOGs.P2: Quantidade de blocos de sistema operacional.P3: Quantidade de requisies de I/O.

88

log file single write


89/130

Explicao: Durante gravao no HEADER de um REDO LOGs, o LGWRespera pelo I/O.Causa: Excesso de gravaes no HEADER do REDO LOG ou lentido de I/O.

Correo: Reduza a quantidade de gravaes no HEADER do REDO LOG oumelhore o mecanismo de I/O.

P1: Nmero do REDO LOG.P2: Nmero do bloco.P3: Quantidade de blocos.

89

log file sequential read


90/130

Explicao: Durante leitura de REDO LOGs, o LGWR espera pelo I/O.Causa: Lentido de I/O.Correo: Melhore o mecanismo de I/O.

P1: Nmero do REDO LOG.P2: Nmero do bloco.P3: Quantidade de blocos.

90


91/130

log file sync


92/130

Explicao: Um CHECKPOINT foi executado, e precisa ser registrado no REDOLOG, e o LGRW est aguardando pelo mecanismo de I/O.Causa: COMMIT em quantidade excessiva, ou I/O ineficiente.

Correo: Reduzir a quantidade de COMMITs ou otimizar o mecanismo de I/O.

P1: Nmero do Log Buffer.P2: No utilizado.P3: No utilizado.

92

SQL*Net message to / from client


93/130

Explicao: Espera durante comunicao via rede com o protocolo SQL*Net.Causa: Sesso inativa, latncia de rede ou limitao do cliente.Correo: Eliminar a sesso inativa, minimizar a latncia na rede ou minimizar a limitaodo cliente.

P1: Driver de rede.P2: Quantidade de bytes.P3: No utilizado.

Variaes

SQL*Net message from client SQL*Net message to client SQL*Net more data from client SQL*Net more data to client SQL*Net break/reset to client SQL*Net message from dblink SQL*Net message to dblink SQL*Net more data from dblink SQL*Net more data to dblink SQL*Net break/reset to dblink

93


94/130

Hands ON !

Simulando Wait Events

LGWR x DBWR

94

Lab 5.1: LGWR x DBWR


95/130

Feche e abra a sesso com o SCOTT com SET TIMING ONSQL> CONN SCOTT/TIGERSQL> SET TIMING ON

Com o usurio SCOTT, apague o contedo da grande tabela, e reinsira os

dados.SQL> TRUNCATE TABLE T2;SQL> INSERT INTO T2 SELECT * FROM T;SQL> COMMIT;

Ao trmino da execuo, verifique os valores de V$SESSION_EVENT da sessodo SCOTT. Guarde o resultado.

Altere o valor do parmetro log_buffer para 512k, repita a operao, e compare.Altere o valor do parmetro log_buffer para 10m, repita a operao, e compare.

Altere o valor do parmetro log_buffer para 100m, repita a operao, e compare.Mantenha log_buffer com a configurao mais otimizada.Altere o parmetro FAST_START_MTTR_TARGET para 1, repita a operao, ecompare.

95


96/130

Parallel SQL

96

Parallel SQL


97/130

Permite Query, DML e DDL.

Um objeto pode ter Parallel permanente, independente do SQL:SQL> ALTER TABLE SCOTT.T PARALLEL DEGREE 4;

O Parallel SQL pode ser utilizado diretamente no SQL:SQL> SELECT /*+ PARALLEL(T2,4) */ COUNT(*) FROM T2;

97

Parallel SQL


98/130

Parmetros:PARALLEL_ADAPTIVE_MULTI_USER = true ou false.PARALLEL_AUTOMATIC_TUNING: Deprecated.

PARALLEL_DEGREE_LIMIT = CPU, IO ou Nmero.PARALLEL_DEGREE_POLICY = MANUAL, LIMITED ou AUTO.PARALLEL_EXECUTION_MESSAGE_SIZE = De 2148 a 32768PARALLEL_FORCE_LOCAL = true ou false.PARALLEL_INSTANCE_GRouP = Oracle RAC service_name ou group_name.PARALLEL_IO_CAP_ENABLED = Deprecated.PARALLEL_MAX_SERVERS = De 0 a 3600.

PARALLEL_MIN_PERCENT = De 0 a 100.PARALLEL_MIN_SERVERS = Nmero entre 0 e PARALLEL_MAX_SERVERS.PARALLEL_MIN_TIME_THRESHOLD = AUTO | Segundos.PARALLEL_SERVERS_TARGET = Nmero entre 0 e PARALLEL_MAX_SERVERS.PARALLEL_THREADS_PER_CPU = Qualquer nmero.

98


99/130

Hands ON !

Simulando Wait Events

Design de Aplicao

99

R i i i I t

Lab 6.1: Design de Aplicao


100/130

Reinicie a Instance.Verifique o contedo da V$SYSTEM_EVENT. Guarde esta consulta.

Abra a sesso com o SCOTT com SET TIMING ON.Em seguida, faa um SELECT da tabela toda.$ sqlplus SCOTT/TIGERSQL> SET TIMING ONSQL> SELECT COUNT(*) FROM T;

Ao trmino da execuo, verifique os valores de V$SESSION_EVENT dasesso do SCOTT. Guarde o resultado.

Repita a operao com PARALLEL, e compare.SQL> SELECT /*+ PARALLEL(T 4) */ COUNT(*) FROM T;SQL> SELECT /*+ PARALLEL(T 20) */ COUNT(*) FROM T;

100


C i t t b l i SCOTT


101/130

101

Crie esta tabela com o usurio SCOTT:SQL> CREATE TABLE T3 (NUMERO NUMBER);

Observe o contedo dos seguintes scripts Perl, os execute, e compare:$ perl /home/oracle/CommitNOK_BindsNOK.pl$ perl /home/oracle/CommitNOK_BindsOK.pl$ perl /home/oracle/CommitOK_BindsNOK.pl$ perl /home/oracle/CommitOK_BindsOK.pl


Crie um ndice BITMAP na tabela T3 com o usurio SCOTT:


102/130

102

Crie um ndice BITMAP na tabela T3 com o usurio SCOTT:SQL> CREATE BITMAP INDEX IDX_BITMAP_T3 ON T3(NUMERO);

Execute um INSERT nesta tabela, sem executar COMMIT ou fechar asesso.:SQL> INSERT INTO T3 VALUES (1);

Abra outra sesso com o SCOTT, e faa outro INSERT na tabela T3:SQL> INSERT INTO T3 VALUES (1);

Com o usurio SYS, verifique a V$SESSION_WAIT.

Repita o exerccio, mas utilizando um ndice BTREE:SQL> DROP INDEX IDX_BITMAP_T3;SQL> CREATE INDEX IDX_T3 ON T3(NUMERO);


Abra uma sesso com o usurio SCOTT com SET TIMING ON:


103/130

103


Crie um ndice BTREE na coluna OWNER da tabela T:

SQL> CREATE INDEX IDX_T ON T(OWNER);

Execute este SQL:SQL> SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

Com o usurio SYS, verifique o contedo da V$SESSION_EVENT da sessodo SCOTT. Guarde o resultado.

Feche e abra a sesso com o SCOTT com SET TIMING ON

Altere a sesso para utilizar o Rule Based Optimizer:SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

Execute este SQL:SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHEREOBJECT_NAME = 'T';


104/130

Estatsticas

104

O i i S i i

Estatsticas


105/130

Optimizer StatisticsTable statistics

Number of rows

Number of blocksAverage row lengthColumn statistics

Number of distinct values (NDV) in columnNumber of nulls in columnData distribution (histogram)Extended statistics

Index statisticsNumber of leaf blocksLevelsClustering factor

System StatisticsI/O performance and utilizationCPU performance and utilization

105

DBMS AUTO TASK ADMIN DISABLE

Estatsticas


106/130

DBMS_AUTO_TASK_ADMIN.DISABLE

DBMS_STATS.GATHER_DATABASE_STATSDBMS_STATS.GATHER_DICTIONARY_STATSDBMS_STATS.GATHER_SCHEMA_STATSDBMS_STATS.GATHER_TABLE_STATSDBMS_STATS.GATHER_INDEX_STATS

DBMS_STATS.DELETE_TABLE_STATSDBMS_STATS.LOCK_TABLE_STATS

DBMS_STATS.EXPORT_*_STATSDBMS_STATS.IMPORT_*_STATS

OPTIMIZER_DYNAMIC_SAMPLING

DBMS_STATS.GATHER_SYSTEM_STATS

106


107/130

Hands ON !

DBMS_SQLTUNE

107

Lab 7.1: DBMS_SQLTUNE

Escolha um dos SQLs mais lentos na V$SQL e o analise com DBMS SQLTUNE


108/130

108

Escolha um dos SQLs mais lentos na V$SQL e o analise com DBMS_SQLTUNE.

DECLARE RET_VAL VARCHAR2(4000);BEGIN

RET_VAL := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => '12345678910', SCOPE =>DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, TIME_LIMIT => 60, TASK_NAME => 'Portilho Tuning Task',DESCRIPTION => 'Portilho Tuning Task');END;/

BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK('Portilho Tuning Task'); END;

/

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('Portilho Tuning Task') RECOMMENTATION FROMDUAL;SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('Portilho Tuning Task') RECOMMENTATION FROMDUAL;

BEGIN DBMS_SQLTUNE.DROP_TUNING_TASK('Portilho Tuning Task'); END;

/


109/130

Fragmentao

109

Blocos logicamente contg os espalhados fisicamente

Fragmentao


110/130

Blocos logicamente contguos espalhados fisicamente. Espao livre na TABLESPACE / DATAFILEs. Espao livre da TABELA.

Espao livre no NDICE. Row Chaining. Migrated Rows. EXTENTs.

110

Fragmentao: COALESCE


111/130

111

Fragmentao: COALESCE


112/130

112

Fragmentao: Row Chaining


113/130

113

Fragmentao: Row Migration


114/130

114


115/130

Hands ON !

DBMS_ADVANCED_REWRITE

115

Lab 8.1: DBMS_ADVANCED_REWRITE



116/130

116


Altere a sesso para utilizar o Rule Based Optimizer:SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

Execute este SQL e anote seu tempo de execuo:SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHEREOBJECT_NAME = 'T';

Execute este SQL e anote seu tempo de execuo:

SQL> SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

Com o usurio SYS, d as permisses necessrias para que o usurioSCOTT utilize o DBMS_ADVANCED_REWRITE:$ sqlplus / AS SYSDBASQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO SCOTT;

SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;

Lab 8.2: DBMS_ADVANCED_REWRITE

Na sesso do usurio SCOTT, execute o DBMS_ADVANCE_REWRITE:


117/130

117

, _ _BEGIN

SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (NAME => 'PORTILHO_REWRITE',

SOURCE_STMT => 'SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHEREOBJECT_NAME = ''T''',DESTINATION_STMT => 'SELECT COUNT(OBJECT_NAME) FROM T T_ALIAS WHERE

OBJECT_NAME = ''T''',VALIDATE => FALSE,REWRITE_MODE => 'TEXT_MATCH');

END;/

Execute novamente este SELECT e verifique seu tempo de execuo:SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

Remova o REWRITE, execute novamente o SELECT e verifique seu tempo deexecuo:SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE (NAME =>'PORTILHO_REWRITE');SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

O Banco de Dados est lento agora

Cenrios de Anlise


118/130

O Banco de Dados est lento agora. Encontrar indcios do gargalo na V$SESSION_WAIT. Encontrar o SID na V$SESSION_WAIT.

Encontrar o maior Wait Event na V$SESSION_EVENT. Corrigir o maior Wait Event possvel.

O Banco de Dados estava lento ontem. Encontrar indcios do gargalo na V$SYSTEM_EVENT. Encontrar o maior Wait Event via Statspack / AWR.

Corrigir o maior Wait Event possvel.

Este SQL est lento. Executar com Extended SQL Trace. Encontrar o maior Wait Event via tkprof. Corrigir o maior Wait Event possvel.

118


119/130

Statspack / AWR

119

Statspack / SPreport

Statspack / AWR


120/130

p p$ sqlplus / AS SYSDBASQL> @?/rdbms/admin/spcreate.sql

SQL> @?/rdbms/admin/spauto.sqlSQL> EXECUTE STATSPACK.SNAP;SQL> @?/rdbms/admin/spreport.sql

AWR / ASH ReportSQL> EXEC dbms_workload_repository.create_snapshot;

SQL> @?/rdbms/admin/awrrpt.sql

120


121/130

Hands ON !

Statspack / AWR

121

Crie uma TABLESPACE chamada SOE, com AUTOEXTEND ON.

Lab 9.1: AWR


122/130

,

Tire um SNAPSHOT avulso.

$ sqlplus / AS SYSDBASQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Execute a criao do esquema SOE.$ cd swingbench/bin./oewizard

Tire outro SNAPSHOT avulso.$ sqlplus / AS SYSDBASQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Tire um relatrio AWR comparando os dois SNAPSHOTs.SQL> @?/rdbms/admin/awrrpt.sql

Analise o relatrio AWR.

122


123/130

Resource Plan

123

Separao de Recursos por:

Resource Plan


124/130

p p ORACLE_USER SERVICE_NAME CLIENT_OS_USER CLIENT_PROGRAM CLIENT_MACHINE MODULE_NAME MODULE_NAME_ACTION SERVICE_MODULE SERVICE_MODULE_ACTION

Controle dos Recursos: CPU Sesses Ativas Paralelismo I/O (>= 11gR1)

124

Resource Plan


125/130

125

Mudanas de planos:

Resource Plan


126/130

pALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PEAKTIME';ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF-PEAK';

Monitorao: DBA_RSRC_CONSUMER_GROUP_PRIVS DBA_RSRC_PLANS V$SESSION V$RSRC_PLAN V$RSRC_CONSUMER_GROUP

V$RSRC_SESSION_INFO V$RSRC_PLAN_HISTORY V$RSRC_CONS_GROUP_HISTORY V$RSRCMGRMETRIC V$RSRCMGRMETRIC_HISTORY

126


127/130

LAB 11 Resource Plan

Hands On !

127

Analise o cdigo do arquivo ResourcePlan.sql.

Lab 10.1 Resource Plan


128/130

Altere o arquivo para suprir as necessidades de trs tipos de uso do banco

de dados: Usurio SOE: OLTP, deve ter muita prioridade durante o dia, e pouca durante a noite. Usuario HR: BI, deve ter pouca pridoridade durante o dia e muita durante a noite. Usurio SCOTT: s pode utilizar CPU que nenhum dos usurios acima estiver utilizando. Outros: s podem utilizar CPU que nenhum dos usurios acima estiver utilizando.

128


129/130

Reviso

129


130/130

Documents

Tunning Oracle