automating the index reorganization

With the upgrade from 7.24xxx to 7.30xxx, it appears that one of our performance problems can be alleviated by rebuilding our indexes. Apparently, some index pages are incorrectly tagged and are kept in buffers LONG after their usefulness has ended. Unfortunately, we are running SAP which has over 8000 tables, so a manual rebuild of indexes appears to be prohibitive. Has anyone got a script or program to: a) drop each index for a table (including implied indices from primary key constraints) b) recreate the indexes c) update statistics for the table when all indexes are done. Thanks, Doug Agnew dagnew@charlottepipe.com

I've seen similar problems with Peoplesoft (5000 odd tables) and 7.3x. There's even been one occasion when an index got out of sync with the table resulting in incorrect values being returned by SELECT statements depending on whether a key-only access was done (returned info.) or a data access was forced (No rows found). PeopleSoft also uses a number of 'permanent' work tables that are particularly susceptible because of the way they are used (inserts-deletes-insert...) Consequently, rebuilding of indexes has become an important weekly task. I have built a Unix script, but it is customized to our needs with some features and, some known limitations. Features 1. Can be give a time parameter - does what it can in that time! 2. Index rebuilding is automatically prioritized with the oldest ones tackled first. 3. Index definitions are never removed. 4. Handles indexes which support Primary key constraints. 5. Can be run in the 'report only' mode Limitations 1. Does not Update statistics 2. If a failure occurs (for example, because it is user-terminated), one (but not more) index & primary key constraint could be left in a disabled state. Additionally, a work table will be left in the database (which will be cleaned out in the next successful run). 3. Will cater to indexes supporting primary key constraints, but NOT those whose primary key is linked to foreign keys (Peoplesoft v7.5 does not use foreign key constraints). Having said all this, I must point out that you may be able to accomplish what you want to do using the following statements, which will rebuild all indexes of <table>, provided the primary key, if any, does not 'support' any foreign keys; SET INDEXES, CONSTRAINTS FOR <table> DISABLED; SET INDEXES, CONSTRAINTS FOR <table> ENABLED; UPDATE STATISTICS <whatever> FOR TABLE <table>; All the same, if you are the adventurous sort, here's my script which, BTW, is fully support by emails to rferdy@pathcom.com #!/bin/ksh # rudy Mon Feb 15 13:43:15 EST 1999 # Rebuilds Indexes in a database. Indexes need rebuilding from time to time. # Questions # 1. How does one determine when an index needs to be rebuilt? # Ans : Can't say. oncheck -pT free space is not a good indicator # 2. Assuming that we have a process to rebuild indexes, but we cannot rebuild all # indexes in a single reorg session, how do we prioritize the rebuilding to the # oldest first? # Ans : Cannot be determined in the database for attached indexes. # Logic : Based on the above, this program will work as follows # 1. Source data on when an index was last re-created will be stored in a unix file # 2. When invoked, the program will load that data into a temporary table # 3. It will then select indexes for rebuilding factoring in the data in the # temp table. # 4. If a constraint is associated with an index, it will disable the constraint # before disabling the index and enable it after enabling the index. # 5. Everytime an index gets rebuilt, it will update/insert into the temp table # 6. Completion occurs when all selected indexes get rebuilt, or we run out of time # 7. On completion, it will unload the temp table for use the next time. usage() { echo " " echo " Usage: `basename $0` [ -g ] [ -t time ] database " echo " -g Actually reorganize the Indexes" echo " -t Total time (Minutes) allowed to reorganize" echo " " exit 2 } RECREATE_LIMIT=20 # Recreate only if older than this number of days IDX_DATA_FILE=$0.dat # Data file where previous rebuilding information kept IDX_TABLE_NAME=tmp_dba_idx_stats # Name of temporary (non-temp) work table TMP_FILE=`basename $0`.$$ TMP_FILE1=${TMP_FILE}.1 integer TIME_ALLOTTED=10800 # Default time is 3 hours if [ $# -lt 1 ]; then usage exit 1 fi CMD_LINE="$0 $*" DO_REORG=0 integer i=1 integer argc=$# while [ $i -le argc ] do case $1 in -g ) DO_REORG=1 shift 1 ;; -t ) TIME_ALLOTTED=`expr $2 \\* 60` shift 2 i=i+1 ;; esac i=i+1 done LOG=`basename $0`.$DO_REORG.log # Separate logs when reorg actually done mv $LOG ${LOG}.old echo `date` 'Index Reorg started with following command line' > $LOG echo "$CMD_LINE" >> $LOG # in case of abnormal exit, clear up junk trap "echo Abnormal exit. Cleaning up...;rm -f ${TMP_FILE}*; exit 1" 1 2 13 15 DB=$1 START_TIME=`echo "select connected from syssessions where sid = dbinfo('sessionid');" \\ | dbaccess sysmaster 2>/dev/null | sed '1,4d' | awk '{ print $1}'` END_TIME=`expr $START_TIME + $TIME_ALLOTTED` echo "DO_REORG (1=Yes) = $DO_REORG" >> $LOG echo "Start Time parameter = $START_TIME" >> $LOG echo "End Time parameter = $END_TIME" >> $LOG echo "TIME_ALLOTTED - secs = $TIME_ALLOTTED" >> $LOG unload_data() { dbaccess $DB << eo_db 2> /dev/null 1>/dev/null unload to $IDX_DATA_FILE select * from $IDX_TABLE_NAME; drop table $IDX_TABLE_NAME; eo_db echo `date` "Index information unloaded to $IDX_DATA_FILE" >> $LOG } do_we_have_time() { END_TIME=$1 CURRENT_TIME=`echo "select connected from syssessions where sid = dbinfo('sessionid');" \\ | dbaccess sysmaster 2>/dev/null | sed '1,4d'` if [ $CURRENT_TIME -gt $END_TIME ]; then echo "Time Expired at `date`" >> $LOG unload_data exit 1 fi } update_reorg_info() { dbaccess $DB << eo_db 2> /dev/null 1>/dev/null update $IDX_TABLE_NAME set recreated = CURRENT where idxname = "$1"; -- Insert should fail if row already exists. If not (first time index reorged) -- insert succeeds. insert into $IDX_TABLE_NAME VALUES ("$1", CURRENT); eo_db } set_constraint() { INDEX=$1 MODE=$2 DO_IT=$3 dbaccess $DB << eo_db 2> /dev/null 1>/dev/null unload to $TMP_FILE1 delimiter " " select constrname from sysconstraints where idxname = "$1" and constrtype in ('P','F');eo_db for CONSTRAINT in `cat $TMP_FILE1` do if [ $DO_IT = 0 ]; then echo "SET CONSTRAINTS $CONSTRAINT $MODE;" else echo `date` "$CONSTRAINT set to $MODE" >> $LOG dbaccess $DB << eo_db 1>> $LOG 2>>$LOG SET CONSTRAINTS $CONSTRAINT $MODE; eo_db fi done } # Check that the database exists. dbaccess $DB << eo_db 2> /dev/null 1>/dev/null select count(*) from systables;eo_db if [ $? != 0 ]; then echo "# Database $DB not found" >> $LOG exit 1 fi echo "`date` Loading previous indexing information" >> $LOG dbaccess $DB << eo_db 2> /dev/null 1>/dev/null drop table $IDX_TABLE_NAME; eo_db dbaccess $DB << eo_db 2> /dev/null 1>/dev/null create table $IDX_TABLE_NAME ( idxname char(18), recreated datetime year

Doug Agnew wrote: > > With the upgrade from 7.24xxx to 7.30xxx, it appears that one of our > performance problems can be alleviated by rebuilding our indexes. > Apparently, some index pages are incorrectly tagged and are kept in buffers > LONG after their usefulness has ended. Unfortunately, we are running SAP > which has over 8000 tables, so a manual rebuild of indexes appears to be > prohibitive. > > Has anyone got a script or program to: > a) drop each index for a table (including implied indices from primary key > constraints) > b) recreate the indexes > c) update statistics for the table when all indexes are done. > > Thanks, > Doug Agnew > dagnew@charlottepipe.com We have a small SQL script which helps to report on errant indexes. Unfortunately I can't find it in my mail, so I'll have to get it from someone else around here or get them to post it. Dominic -- Dominic Binks/Systems Engineer/Logica-Aldiscon 400 Park Avenue/Aztec West/Bristol/BS32 4TR/United Kingdom Tel: +44 1454 614455/Fax: +44 1454 620527/Mobile: +44 498 693964 E-mail: dominic.binks@aethos.co.uk