Translate

quarta-feira, 22 de maio de 2019

☕🔥 DB2 z/OS — COMO IDENTIFICAR PROBLEMAS EM ÍNDICES, ANALISAR A SAÚDE E CRIAR ÍNDICES EFICIENTES

 

Bellacosa Mainframe e a saude do Db2

☕🔥 DB2 z/OS — COMO IDENTIFICAR PROBLEMAS EM ÍNDICES, ANALISAR A SAÚDE E CRIAR ÍNDICES EFICIENTES

No Db2 for z/OS, índices são literalmente o “GPS” do otimizador.
Quando um índice está ruim, fragmentado, mal desenhado ou inconsistente, os sintomas aparecem rapidamente:

  • CPU alta

  • GETPAGE excessivo

  • LOCKS maiores

  • Deadlocks

  • Elapsed Time absurdo

  • SORT desnecessário

  • RUNSTATS inconsistentes

  • ACCESS PATH inesperado

  • Tablespace em CHECK/RBDP/RECP

  • RID List Overflow

  • REORG frequente

🔥 COMO IDENTIFICAR PROBLEMAS EM ÍNDICES

1 — Verificando Fragmentação do Índice

Um dos principais indicadores.

Consultas importantes

SELECT
    NAME,
    CLUSTERING,
    CLUSTERRATIOF,
    LEAFDIST,
    NLEVELS,
    FULLKEYCARDF,
    FIRSTKEYCARDF
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'SEU_SCHEMA'
AND TBNAME = 'SUA_TABELA';

🔎 O QUE OBSERVAR

CLUSTERRATIOF

Mostra o quanto os dados seguem a sequência do índice clustering.

Valores

ValorSituação
> 95Excelente
80–95Aceitável
< 80Fragmentação séria

Baixo CLUSTERRATIO gera:

  • Mais I/O

  • Mais Sync Read

  • Mais Random Access

  • Mais CPU

LEAFDIST

Distância média entre páginas leaf.

Quanto maior:

  • pior a localidade física

  • mais page split ocorreu

NLEVELS

Quantidade de níveis B-Tree.

NívelInterpretação
2-3Normal
4+Índice muito grande ou mal estruturado

Mais níveis = mais GETPAGE.

🔥 PAGE SPLIT — O GRANDE VILÃO

Quando páginas do índice enchem:

  • Db2 divide páginas

  • reorganiza ponteiros

  • aumenta fragmentação

Sintomas:

  • CPU cresce

  • bufferpool sofre

  • random I/O aumenta

🔎 COMO IDENTIFICAR PAGE SPLIT

SELECT
    NAME,
    SPACEF,
    STATSTIME
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE DBNAME = 'SEU_DB';

🔥 REORGCHECK — O TESTE CLÁSSICO

No Db2 LUW existe REORGCHK.

No z/OS normalmente usamos:

  • RUNSTATS

  • REORG TABLESPACE/INDEX

  • Estatísticas catalogadas

  • RTS (Real Time Statistics)

🔥 REAL TIME STATISTICS (RTS)

Tabela importantíssima:

SYSIBM.SYSINDEXSPACESTATS

Campos críticos:

CampoSignificado
REORGINSERTSInserts desde último REORG
REORGDELETESDeletes
REORGUPDATESUpdates
LEAFDISTFragmentação
FARINDREFReferência distante
NEARINDREFReferência próxima

🔥 FARINDREF — UM DOS MELHORES INDICADORES

Mostra quantos acessos ao índice apontam para linhas longe fisicamente.

Quanto maior:

  • pior clustering

  • pior cache

  • pior bufferpool hit ratio

🔥 COMO SABER SE O ÍNDICE NÃO ESTÁ SENDO USADO

Pacotes e explain.

EXPLAIN

EXPLAIN PLAN SET QUERYNO = 100
FOR
SELECT *
FROM CLIENTES
WHERE CPF = ?;

Depois consulte:

SELECT
    ACCESSNAME,
    ACCESSTYPE,
    MATCHCOLS
FROM PLAN_TABLE
WHERE QUERYNO = 100;

🔎 INTERPRETAÇÃO

CampoSignificado
ACCESSTYPE='I'Uso de índice
MATCHCOLSQuantas colunas casaram
ACCESSNAMEÍndice usado

🔥 INDICADORES DE ÍNDICE RUIM

1 — MATCHCOLS baixo

Índice composto mal desenhado.

2 — ACCESSTYPE = 'R'

Tablespace Scan.

Ruim para tabelas grandes.

3 — RID LIST PROCESSING

Pode indicar:

  • excesso de índices

  • índices ruins

  • baixa seletividade

🔥 COMO DESENHAR UM ÍNDICE FORTE

REGRA #1 — COLUNA MAIS SELETIVA PRIMEIRO

Exemplo ruim:

CREATE INDEX IX1
ON CLIENTES
(SEXO, ESTADO);

Baixa cardinalidade.

Melhor:

CREATE INDEX IX1
ON CLIENTES
(CPF, ESTADO);

🔥 REGRA #2 — RESPEITAR O PREDICATE

Db2 usa LEFTMOST MATCHING.

Exemplo:

INDEX(A,B,C)

Funciona bem para:

WHERE A=?
WHERE A=? AND B=?
WHERE A=? AND B=? AND C=?

Ruim para:

WHERE B=?
WHERE C=?

🔥 REGRA #3 — EVITAR ÍNDICES DEMAIS

Cada índice:

  • aumenta INSERT

  • aumenta UPDATE

  • aumenta DELETE

  • aumenta LOG

  • aumenta LOCKING

  • aumenta CPU

🔥 QUANTOS ÍNDICES UMA TABELA DEVE TER?

Não existe número mágico.

Mas no mundo real:

TipoRecomendação
OLTP3–7
Tabelas críticas10–15 máximo
Acima de 20normalmente problema de modelagem

Já vi tabelas com:

  • 80 índices

  • 120 índices

Resultado:

  • INSERT sofrendo

  • Deadlock

  • log monstruoso

  • CPU absurda

🔥 ÍNDICE CLUSTERING

Muito importante.

CREATE INDEX IXCLI1
ON CLIENTES (CPF)
CLUSTER;

Só pode existir UM clustering index por tabela.

Ele influencia:

  • ordem física

  • prefetch

  • sequential detection

  • range scan

🔥 RUNSTATS — FUNDAMENTAL

Sem RUNSTATS:

  • optimizer fica “cego”

  • access path piora

Exemplo

RUNSTATS TABLESPACE DB1.TSCLI
TABLE(ALL)
INDEX(ALL)
KEYCARD
FREQVAL
HISTOGRAM
UPDATE ALL

🔥 O QUE O RUNSTATS FAZ

Atualiza:

  • cardinalidade

  • clustering

  • distribuição

  • frequência

  • histogramas

  • seletividade

🔥 RUNSTATS COM SHRLEVEL CHANGE

Permite online.

SHRLEVEL CHANGE

Muito usado em produção.

🔥 REORG INDEX

Quando usar:

  • fragmentação

  • page split

  • baixa cluster ratio

Exemplo

REORG INDEX (ALL) TABLESPACE DB1.TSCLI
SHRLEVEL CHANGE

🔥 REBUILD INDEX

Mais pesado.

Usado quando:

  • índice corrompido

  • recover

  • inconsistency

  • rebuild pós LOAD REPLACE

🔥 RUNSTATS x REORG

UtilitárioFunção
RUNSTATSAtualiza estatísticas
REORGReorganiza fisicamente
REBUILD INDEXReconstrói índice

🔥 RUNSTATUS / RESTRICTIVE STATES

Estados importantes no Db2.

🔴 CHECK PENDING (CHKP)

Db2 exige CHECK DATA.

Exemplo:

-904 RESOURCE UNAVAILABLE

Resolver:

CHECK DATA TABLESPACE DB1.TS1

🔴 REORG PENDING (REORP)

Db2 exige REORG.

Muito comum após:

  • ALTER

  • compress

  • partition change

Resolver:

REORG TABLESPACE DB1.TS1

🔴 RECOVER PENDING (RECP)

Objeto precisa RECOVER.

🔴 AUX CHECK PENDING

LOB inconsistente.

🔴 ADVISORY REORG PENDING (AREO*)

Db2 recomenda REORG.

Não bloqueia uso.

🔥 QUIESCE — O “PONTO DE RESTORE”

Cria ponto consistente para recover coordenado.

Exemplo

QUIESCE TABLESPACE DB1.TS1
WRITE YES

🔎 O QUE ELE FAZ

Sincroniza:

  • logs

  • páginas

  • buffers

Muito usado antes de:

  • LOAD

  • grandes mudanças

  • backup crítico

🔥 DISPLAY DATABASE

Comando essencial.

-DISPLAY DATABASE(DB1) SPACENAM(TS1) RESTRICT

Mostra:

  • REORP

  • CHKP

  • AREO*

  • COPY pending

  • advisory states

🔥 DISPLAY INDEX

-DISPLAY DATABASE(DB1) SPACENAM(TS1) USE

🔥 IFCID E MONITORAMENTO

Sysprog geralmente usa:

  • IFCID 199

  • IFCID 376

  • IFCID 389

  • OMEGAMON

  • MainView

  • Query Monitor

Para detectar:

  • index scan ruim

  • getpages altos

  • synchronous I/O

  • random read

  • RID overflow

🔥 SINAIS CLÁSSICOS DE ÍNDICE PROBLEMÁTICO

SintomaPossível causa
CPU altaÍndice ruim
Tablespace Scanfalta índice
GETPAGE altoexcesso de níveis
Sync I/O altofragmentação
Deadlockexcesso índices
INSERT lentomuitos índices
REORG frequentepage split
Bufferpool ruimclustering baixo

☕ MELHOR ESTRATÉGIA ENTERPRISE

Fluxo saudável

RUNSTATS
   ↓
EXPLAIN
   ↓
ANÁLISE RTS
   ↓
REORG
   ↓
MONITORAMENTO IFCID
   ↓
AJUSTE DE ACCESS PATH

🔥 RESUMO ENTERPRISE

Índice saudável:

✅ Alta seletividade
✅ Baixo NLEVELS
✅ CLUSTERRATIO alto
✅ Poucos page splits
✅ MATCHCOLS alto
✅ Bom clustering
✅ Estatísticas atualizadas
✅ Poucos índices redundantes

☕ REGRA DE OURO NO Db2 z/OS

“Índice demais mata INSERT.
Índice de menos mata SELECT.
Índice ruim mata os dois.”

Publicada por à(s)
Etiquetas: , , , , , , , , , , , , , , , ,

Sem comentários:

Enviar um comentário

Subscrever: Enviar feedback (Atom)