Oracle Rebuild Index shell script, SQL script sharing _oracle

Index is a powerful weapon to improve database query performance. No index, just like the library without book tags, find a book they want to book than Heaven is difficult. However, in the process of using the index, especially in the case of batch DML, the corresponding fragments will be produced, and the B-tree height will change accordingly, so that these changing indexes can be refactored to improve performance. N long ago Oracle advised us to periodically rebuild the indexes on those tables where the height is 4 and the deleted index entries occupy at least 20% of the total number of existing index entries. But Oracle now strongly recommends that you do not rebuild the index regularly. You can refer to the article: the need for Oracle to rebuild the index. Although it is still necessary to rebuild the index, it is not recommended on a regular basis. In this paper, we give a script to reconstruct the index for your reference.

1, rebuild the index shell script

robin@szdb:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh # +------------------------------------
-------------------+ # + rebulid unblanced Indices | 
# + Author:leshami |
# + Parameter:no | # +-------------------------------------------------------+ #!/bin/bash #--------------------# Define Variable #---- ----------------if [f ~/.bash_profile]; Then.       ~/.bash_profile fi dt= ' Date +%y%m%d '; Export DT retention=1 log_dir=/tmp log=${log_dir}/rebuild_unbalanced_indices_${dt}.log dba=leshami@12306.cn #-------  -----------------------------# Loop All instance on current server #-------------------------------------Echo ' current Date and time are: '/bin/date ' ' >>${log} to DB in ' Ps-ef | grep Pmon |  grep-v grep |grep-v asm |awk ' {print $} ' |cut-c 10-' do echo "$db" Export oracle_sid= $db echo "The current db is $db" >>${log} echo "===============================================" >≫${log} $ORACLE _home/bin/sqlplus-s/nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql

>>${log} done; echo "End of rebuilding index to all instance at: '/bin/date '" >>${log} #-------------------------------------# C Heck log file #-------------------------------------status= ' grep ' ora-' ${log} ' if [-Z $status];then mail-s ' Succe  eded rebuilding indices on ' hostname '!!! ' ${DBA} <${log} else Mail-s ' Failed rebuilding indices on ' hostname '!!! '  
${DBA} <${log} fi #------------------------------------------------# removing files older than $RETENTION parameter #------------------------------------------------Find ${log_dir}-name "rebuild_unb*"-mtime + $RETENTION-exec rm {}

\;

 Exit

2, rebuilding the SQL script for an index call

robin@szdb:~/dba_scripts/custom/sql> more Rebuild_unbalanced_indices.sql conn/as sysdba set serveroutput on;
  DECLARE Resource_busy EXCEPTION;
  PRAGMA Exception_init (Resource_busy,-54);
  C_max_trial CONSTANT Pls_integer: = 10;
  C_trial_interval CONSTANT Pls_integer: = 1;
  Pmaxheight CONSTANT INTEGER: = 3;

  pmaxleafsdeleted CONSTANT INTEGER: = 20; 
    CURSOR Csrindexstats is SELECT NAME, height, lf_rows as leafrows, del_lf_rows as leafrowsdeleted

  From Index_stats;

  Vindexstats Csrindexstats%rowtype; 
    CURSOR csrglobalindexes is SELECT owner,index_name, tablespace_name from dba_indexes WHERE partitioned = ' NO '

  and owner in (' Gx_admin ');
    CURSOR csrlocalindexes is SELECT index_owner,index_name, Partition_name, tablespace_name from Dba_ind_partitions

  WHERE status = ' USABLE ' and Index_owner in (' Gx_admin ');
  Trial Pls_integer;
Vcount INTEGER: = 0; BEGIN trial : = 0; /* Global Indexes * * Vindexrec in csrglobalindexes loop EXECUTE IMMEDIATE ' analyze index ' | | Vindexrec.owner | | | Vindexrec.index_name | |

   ' Validate structure ';

   OPEN Csrindexstats;

   FETCH csrindexstats into Vindexstats;
        If Csrindexstats%found THEN if (Vindexstats.height > Pmaxheight) or (vindexstats.leafrows > 0
            and vindexstats.leafrowsdeleted > 0 and (vindexstats.leafrowsdeleted * 100/vindexstats.leafrows) >
      pmaxleafsdeleted) THEN Vcount: = Vcount + 1; Dbms_output. Put_Line (' Rebuilding index ' | | | | vindexrec.owner | |

      '...'); <<alter_index>> BEGIN EXECUTE IMMEDIATE ' alter index ' | |
         Vindexrec.owner | | '. ' || Vindexrec.index_name | | ' Rebuild ' | | ' Parallel nologging compute statistics ' | | ' Tablespace ' | |
      Vindexrec.tablespace_name; EXception when Resource_busy OR timeout_on_resource THEN dbms_output.
         Put_Line (' Alter INDEX-BUSY and wait for 1 sec ');

         Dbms_lock.sleep (C_trial_interval);
         IF trial <= c_max_trial THEN GOTO alter_index; ELSE Dbms_output. Put_Line (' alter index busy and waited-quit after ' | | To_char (c_max_trial) | |
           ' Trials ');
         RAISE;
        End IF; When others THEN Dbms_output. Put_Line (' alter index err ' | |
         SQLERRM);
      RAISE;
     End;
   End IF;

   End IF;
  Close csrindexstats;

  End LOOP; Dbms_output. Put_Line (' Global indices rebuilt: ' | |
  To_char (Vcount));
  Vcount: = 0;

  Trial: = 0; /* Local Indexes * * Vindexrec in csrlocalindexes loop EXECUTE IMMEDIATE ' analyze index ' | |
     vindexrec.index_owner| | '. ' || Vindexrec.index_name | | ' Partition (' | | vindexrec.partition_naMe | |

   ') Validate structure ';

   OPEN Csrindexstats;

   FETCH csrindexstats into Vindexstats;
        If Csrindexstats%found THEN if (Vindexstats.height > Pmaxheight) or (vindexstats.leafrows > 0
            and vindexstats.leafrowsdeleted > 0 and (vindexstats.leafrowsdeleted * 100/vindexstats.leafrows) >
      pmaxleafsdeleted) THEN Vcount: = Vcount + 1; Dbms_output. Put_Line (' Rebuilding index ' | | | | vindexrec.index_owner| |

      '...'); <<alter_partitioned_index>> BEGIN EXECUTE IMMEDIATE ' alter index ' | |
         vindexrec.index_owner| | '. ' || Vindexrec.index_name | | ' Rebuild ' | | ' Partition ' | | Vindexrec.partition_name | | ' Parallel nologging compute statistics ' | | ' Tablespace ' | |
      Vindexrec.tablespace_name; EXCEPTION when Resource_busy OR timeout_on_resource THEN
         Dbms_output.
         Put_Line (' Alter partitioned index-busy and wait for 1 sec ');

         Dbms_lock.sleep (C_trial_interval);
         IF trial <= c_max_trial THEN GOTO alter_partitioned_index; ELSE Dbms_output. Put_Line (' alter partitioned index busy and waited-quit after ' | | To_char (c_max_trial) | |
           ' Trials ');
         RAISE;
        End IF; When others THEN Dbms_output. Put_Line (' alter partitioned index err ' | |
         SQLERRM);
      RAISE;
     End;
   End IF;

   End IF;
  Close csrindexstats;

  End LOOP; Dbms_output. Put_Line (' Local indices rebuilt: ' | |
To_char (Vcount));
End;

 /exit;

3, input log samples

Current date and time Is:sun APR 02:00:02 HKT 2014 The current 
DB is SYBO2 ========================================== =====
Rebuilding index gx_admin.
syn_out_data_tbl_pk ... Rebuilding index Gx_admin.
idx_tdbk_splnk_parent_ref ... Rebuilding index Gx_admin.
idx_tdbk_splnk_child_ref ... Rebuilding index Gx_admin.
pk_trade_broker_tbl ... Rebuilding index Gx_admin.
  idx_tdbk_input_date ... ................

4, PostScript

A, if there are multiple instances on the same server, and each instance has the same schema, the script rounds all instances and rebuild based on analyze results.
A, you should make appropriate adjustments as needed, such as the path information of the script, and so on.
b, the corresponding schema name needs to be modified.
D, the corresponding degree of parallelism can be adjusted according to the system environment.