Como verificar fragmentação de índices

 1.--
 2.-- Check for Index Fragmentation
 3.--
 4. 
 5.ACCEPT MySchema PROMPT 'Enter schema to be analysed: '
 6. 
 7.set term off
 8.set feedback off
 9. 
 10.drop table analyze_strut_commands
 11. 
 12.create table analyze_strut_commands (
 13.line_id  number,
 14.sql_text varchar2(2000));
 15. 
 16.drop table frag_stats_all
 17. 
 18.create table frag_stats_all as
 19.select *
 20.from   index_stats
 21.where  1 = 2;
 22. 
 23.set term on
 24.prompt creating validation scripts ...
 25.set term off
 26. 
 27.declare
 28.    cursor ind_cur IS
 29.       select owner
 30.       ,      index_name
 31.       from   dba_indexes where owner=upper('&&MySchema')
 32.       order by owner
 33.       ,        index_name;
 34. 
 35.    l_sql_text         varchar2(500);
 36.    l_curr_line_id     number(38) := NULL;
 37. 
 38.begin
 39.    declare
 40.           function write_out (
 41.           p_line_id     IN  NUMBER,
 42.           p_sql_text    IN  VARCHAR2 ) return NUMBER
 43.           is
 44.           l_line_id   number(38) := null;
 45.           begin
 46.             insert into analyze_strut_commands
 47.             values(p_line_id,p_sql_text);
 48.             commit;
 49.             l_line_id := p_line_id + 1;
 50.             return(l_line_id);
 51.           end write_out;
 52.    begin
 53.           l_curr_line_id := write_out(1,'-- start');
 54.           for ind_rec in ind_cur LOOP
 55.                --
 56.                -- Firs get the prompts
 57.                --
 58.                l_sql_text := 'prompt ... processing index '||
 59.                               ind_rec.owner||
 60.                               '.'||
 61.                               ind_rec.index_name||
 62.                               ' ...';
 63.                l_curr_line_id := write_out(l_curr_line_id,l_sql_text);
 64.                --
 65.                -- Second get the analyze commands
 66.                --
 67.                l_sql_text := 'analyze index '||
 68.                               ind_rec.owner||
 69.                               '.'||
 70.                               ind_rec.index_name||
 71.                               ' validate structure;';
 72.                l_curr_line_id := write_out(l_curr_line_id,l_sql_text);
 73.                --
 74.                -- Third get the current statistics before it gets overwritten
 75.                --
 76.                l_sql_text := 'insert into frag_stats_all select * from index_stats;';
 77.                l_curr_line_id := write_out(l_curr_line_id,l_sql_text);
 78.           end loop;
 79.           --
 80.           -- commit the whole thing
 81.           --
 82.           l_curr_line_id := write_out(l_curr_line_id,'commit;');
 83.    end;
 84.end;
 85./
 86. 
 87.set pages 0
 88.col sql_text format a132
 89. 
 90.select sql_text from analyze_strut_commands
 91.order by line_id
 92. 
 93.spool ind.tmp
 94./
 95.spool off
 96. 
 97.set term on
 98.prompt running validation scripts ...
 99. 
 100.@ind.tmp
 101. 
 102.drop table analyze_strut_commands
 103./
 104. 
 105.!rm ind.tmp
 106. 
 107.set pause on;
 108.pause Press any key to review the results
 109.set pause off
 110. 
 111.SET VERIFY OFF
 112.SET FEEDBACK OFF
 113. 
 114.COL name HEA 'Index Name' FOR a30
 115.COL del_lf_rows HEAd 'Deleted|Leaf Rows' FOR 99999999
 116.COL lf_rows_used HEA 'Used|Leaf Rows' FOR 99999999
 117.COL ratio HEAd '% Deleted|Leaf Rows' FOR 999.99999
 118. 
 119.SET VERIFY ON
 120.SET FEEDBACK ON
 121. 
 122.SELECT
 123.   name, del_lf_rows, lf_rows - del_lf_rows lf_rows_used,
 124.   DECODE(lf_rows,0,0,TO_CHAR( del_lf_rows / (lf_rows)*100,'999.99999' )) ratio
 125.FROM
 126.   frag_stats_all
 127.ORDER BY
 128.   4
 129./

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em INDEX TUNING, ORACLE 11gR2, PLSQL SCRIPTS e marcado , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s