Índices maiores que as tabelas

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Responder
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 157
Registrado em: Sex, 30 Nov 2007 1:17 pm
Localização: Porto Alegre - RS

Pessoal,

Usando o comando abaixo tenho os índices que estão maiores que as tabelas,porém, ao reexecutar os mesmos sempre aparecem...

Qual pode ser o motivo?

Selecionar tudo

declare
cursor CTAB is
SELECT SEGMENT_NAME,BYTES FROM user_SEGMENTS WHERE SEGMENT_TYPE='TABLE';
begin
dbms_output.enable(90000);
for CTABDET in CTAB loop
  for CINDEX in (select S.SEGMENT_NAME,S.BYTES from user_INDEXES I, user_SEGMENTS S where I.TABLE_NAME=CTABDET.SEGMENT_NAME AND S.SEGMENT_NAME=I.INDEX_NAME) loop
     IF CINDEX.BYTES>CTABDET.BYTES THEN
        dbms_output.put_line('INDEX '||CINDEX.SEGMENT_NAME||' = '||CINDEX.BYTES/1024/1024||' MAIOR QUE A TABELA '||CTABDET.SEGMENT_NAME||' = '||CTABDET.BYTES/1024/1024);
     END IF;
  end loop;
end loop;
end;
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Mariogus,

Tudo bem?

Talvez vocês esteja efetuando muitas operações DML (INSERT/DELETE/UPDATE) nas tabelas aos quais estes índices perteçam. Ou então, você tem colunas indexadas e está efetuando muito update nelas.

Se os seus índices forem do tipo BITMAP e você estiver fazendo UPDATE nas colunas que o compõe, estes índices irão crescer. Uma situação similar ocorre com o CONTEXT/DOMAIN INDEXES (os indices do ORACLE TEXT): quanto mais UPDATES/DELETE/INSERT você efetuar, maior o tamanho que estes índices irão alcançar.

Uma sugestão seria você eleger um destes índices relacionados em sua querie. Avalie o tamanho que ele tem agora e então efetue uma REORGANIZACAO/REBUILD do mesmo (ex: ALTER INDEX ... REBUILD). Em seguida, execute novamente a querie para ver se ele ainda apresenta tamanho maior que o da tabela.

Consultando os OTN foruns, verifiquei que indicaram uma nota do METALINK para avaliar quando se deveria fazer ou não a reorganização dos índices. Não tive tempo de consultar o link para esta nota, mas pesquise por Note: 989093.1 - Index Rebuild, the Need vs the Implications

Se o tamanho dos índices lhe preocupa e isso istá impactando no seu ambiente (falta de espaço) seria interessante avaliar se o ORACLE está usando mesmo todos os índices criados para sua tabela. Existem artigos que mencionam sobre como avaliar o uso dos índices.

Existe um artigo interessante sobre este tema em : http://www.palominodb.com/blog/2011/10/ ... ctual-data

Abraços,

Sergio Coutinho
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:
Fábio Prado
www.fabioprado.net

Mariogus,

Só para complementar a resposta do Coutinho, é normal alguns índices serem maiores que a tabela e para verificar quando reconstruir índices btree veja no MOS a nota c/ ID 122008.1. Resumindo ... índices btree só devem ser reconstruídos se tiverem blevel maior que 4 e/ou 20% ou mais de linhas apagadas (DEL_LF_ROWS). Segue abaixo um script p/ te ajudar a avaliar isso:

Selecionar tudo

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  V_SCHEMANAME VARCHAR2(30):=UPPER('&SCHEMA_NAME');
  V_INDEXNAME VARCHAR2(30):=UPPER('&INDEX_NAME');
  V_RESULTADO VARCHAR2(4);
  V_BLEVEL NUMBER;
  V_PERC_LD NUMBER;
BEGIN
  
    FOR I IN (SELECT  INDEX_NAME, OWNER
              FROM    DBA_INDEXES
              WHERE   OWNER = V_SCHEMANAME
              AND     INDEX_NAME = NVL(V_INDEXNAME,INDEX_NAME)
              AND     INDEX_TYPE = 'NORMAL'
              AND     OWNER NOT IN ('SYS','SYSTEM')
              )
    LOOP
        EXECUTE IMMEDIATE 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' COMPUTE STATISTICS';
        EXECUTE IMMEDIATE 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' VALIDATE STRUCTURE';
        
        BEGIN
            SELECT      CASE                               
                              WHEN DECODE(S.DEL_LF_ROWS,0,0,S.DEL_LF_ROWS/S.LF_ROWS*100) >= 20 OR IND.BLEVEL > 4  THEN 'SIM'
                              ELSE 'NÃO' 
                        END AS REORGANIZE,
                        BLEVEL, 
                        DECODE(S.DEL_LF_ROWS,0,0,S.DEL_LF_ROWS/S.LF_ROWS*100)                          
                        INTO V_RESULTADO, V_BLEVEL, V_PERC_LD
            FROM        INDEX_STATS S
            INNER JOIN  DBA_INDEXES IND
                ON      S.NAME = IND.INDEX_NAME
            WHERE       IND.OWNER = I.OWNER;
            
            IF V_RESULTADO = 'SIM' THEN
                DBMS_OUTPUT.PUT_LINE('É necessário reconstruir o índice ' || I.OWNER || '.' || I.INDEX_NAME || '(blevel=' || TO_CHAR(V_PERC_LD) 
                                  || ', % linhas del.=' || to_char(V_PERC_LD) || ')');
            ELSE
                DBMS_OUTPUT.PUT_LINE('NÃO é necessário reconstruir o índice ' || I.OWNER || '.' || I.INDEX_NAME);
            END IF;
			
			DBMS_STATS.GATHER_INDEX_STATS(i.OWNER, i.INDEX_NAME); -- coleta estatistica do indice (necessario apos o ANALYZE)
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(' Erro ao calcular indice ' || i.owner || '.' || i.index_name);
        END;
    END LOOP;
END;
[]s
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 2 visitantes