# $Header: idxrebuild.tcl 14-apr-00.04:54:35 jmansur Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  idxrebuild.tcl : Event tcl file that monitors indexes identifying ones that
#               suffer from index stagnation and would benefit from a rebuild.
#
# ARGUMENTS:
#           argv(0) == filter of the index names to be monitored, or *
#                      for all indexes. 
#           argv(1) == filter of the index owners to be monitored, or *
#                      for all owners. Owners SYS and SYSTEM are excluded.
#           argv(2) == filter of the names of indexed objects to be monitored,
#                      or * for all objects. 
#           argv(3) == filter of the owner names of indexed objects to be 
#                      monitored, or * for all owners. 
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           A list of the indexes.
#
# $Log:  $
#

# Event definition
oradefine event /oracle/rdbms/perf/idxrebuild description=VOC-01318 \
report=VOC-01319
oraarguments connect_str index_name index_owner indexed_object_name indexed_object_owner
oravardesc connect_str oracle_signon
oravardesc index_name string default=* message=VOC-01320
oravardesc index_owner string default=* message=VOC-01321
oravardesc indexed_object_name string default=* message=VOC-01322
oravardesc indexed_object_owner string default=* message=VOC-01323
oraresults rebuild_indexes 
oravardesc rebuild_indexes string
oradefine end


# Initialize global variables
#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
oraeventpersist last_rebuild_indexes {}


# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg
    global last_rebuild_indexes

    # initialize the return code and output
    set ret_code $CLEAR_EVENT
    set output ""
    set err ""
    set db_version ""

    # connect to the database and open a cursor
    set connect [format "%s@%s" [lindex $argv 0] $oramsg(oraobject)]
    if {[catch {oralogon $connect} lda]} {
        lappend output [msgtxt [RDBMS] ora $oramsg(rc)] ""
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }
    if {[catch {set cur [oraopen $lda]} err]} {
	lappend output $err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    if {[catch {set cur2 [oraopen $lda]} err]} {
	lappend output $err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
       if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    # get the database version
    if {[catch {set db_version [DB_VERSION]} err]} {
	lappend output $err
        catch {oraclose $cur} err
        catch {oraclose $cur2} err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    # determine indexes which need to be monitored
    if {[string compare $db_version "80"] >= 0} {
        set sql {select owner, index_name, partitioned from sys.dba_indexes
                 where owner not in ('SYS', 'SYSTEM') }
    } else {
        set sql {select owner, index_name from sys.dba_indexes  
                 where owner not in ('SYS','SYSTEM')}
    }
    if {[string compare [lindex $argv 1] *] != 0} {
        lappend sql and index_name
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 2]]
        set sql [concat $sql [lindex $argv 1]]
    }
    if {[string compare [lindex $argv 2] *] != 0} {
        lappend sql and owner
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 2]]
        set sql [concat $sql $tmp]
    }
    if {[string compare [lindex $argv 3] *] != 0} {
        lappend sql and table_name
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 3]]
        set sql [concat $sql $tmp]
    }
    if {[string compare [lindex $argv 4] *] != 0} {
        lappend sql and table_owner
        set tmp [convertin $oramsg(db_characterset) [lindex $argv 4]]
        set sql [concat $sql $tmp]
    }

    if {[catch {orasql $cur $sql}] == 1} {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        catch {oraclose $cur2} err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
	lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }
    if {[catch {set row [orafetch $cur]} err]} {
	lappend output $err
        catch {oraclose $cur2} err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }

    if {$oramsg(rows) == 0} {
        lappend output [msgtxt [NETWORK] nms 1007] $sql
        catch {oraclose $cur2} err
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
	lappend output $err
        if {$last_report == $SCRIPT_FAIL} {
            return $NO_EVENT
        } else {
            set last_report $SCRIPT_FAIL
            return $SCRIPT_FAIL
        }
    }


    # print out event trace info 
    ORATCL_DEBUG "idxrebuild : $oramsg(oraobject) : [oratime] : "


    while {$oramsg(rc) == 0} {
        if {[string compare $db_version "80"] >= 0} {
            if {[string compare [lindex $row 2] "YES"] == 0} {
                set sql2 {select partition_name from sys.dba_ind_partitions }
                set sql2 [concat $sql2 [format "where index_owner = '%s' and index_name = '%s'" \
                                               [lindex $row 0] \
                                               [lindex $row 1]]]

                if {[catch {orasql $cur2 $sql2}] == 1} {
                    lappend output [convertout $oramsg(db_characterset) \
                        $oramsg(errortxt)] $sql2
		    catch {oraclose $cur2} err
		    catch {oraclose $cur} err
		    catch {oralogoff $lda} err
		    lappend output $err
                    if {$last_report == $SCRIPT_FAIL} {
                            return $NO_EVENT
                    } else {
                            set last_report $SCRIPT_FAIL
                            return $SCRIPT_FAIL
                    }
                }
		if {[catch {set row2 [orafetch $cur2]} err]} {
	            lappend output $err
		    catch {oraclose $cur2} err
	            catch {oraclose $cur} err
        	    catch {oralogoff $lda} err
		    if {$last_report == $SCRIPT_FAIL} {
	                return $NO_EVENT
		    } else {
		        set last_report $SCRIPT_FAIL
	                return $SCRIPT_FAIL
        	    }
    		}
                if {$oramsg(rows) == 0} {
                    lappend output [msgtxt [NETWORK] nms 1019] $sql2
                    catch {oraclose $cur2} err
		    catch {oraclose $cur} err
		    catch {oralogoff $lda} err
                    lappend output $err 
		   if {$last_report == $SCRIPT_FAIL} {
                        return $NO_EVENT
                    } else {
                        set last_report $SCRIPT_FAIL
                        return $SCRIPT_FAIL
                    }
                }
                while {$oramsg(rc) == 0} {
                    lappend index_owner [lindex $row 0]
                    lappend index [lindex $row 1]
                    lappend index_part [lindex $row2 0]

                    if {[catch {set row2 [orafetch $cur2]} err]} {
	                lappend output $err
		        catch {oraclose $cur2} err
		        catch {oraclose $cur} err
		        catch {oralogoff $lda} err
	                if {$last_report == $SCRIPT_FAIL} {
	                    return $NO_EVENT
		        } else {
		            set last_report $SCRIPT_FAIL
	                    return $SCRIPT_FAIL
        	        }
    		    }
                }
            } else {
              lappend index_owner [lindex $row 0]
              lappend index [lindex $row 1]
              lappend index_part ""
            }

        } else {
            lappend index_owner [lindex $row 0]
            lappend index [lindex $row 1]
            lappend index_part ""
        }

        if {[catch {set row [orafetch $cur]} err]} {
	    lappend output $err
            catch {oraclose $cur2} err
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }
    }


    set number_of_alerts 0
    set rebuild_indexes ""
    set all_rebuild_indexes ""

    # analyze all indexes
    set i 0
    set loop [llength $index]
    while {$i < $loop} { 
        set found_alert 0
        set partitioned_index [string compare [lindex $index_part $i] ""]

        if {$partitioned_index != 0} {
            set sql [format "analyze index \"%s\".\"%s\" partition (\"%s\") validate structure"  \
                     [lindex $index_owner $i] \
                     [lindex $index $i] \
                     [lindex $index_part $i]]
        } else {
            set sql [format "analyze index \"%s\".\"%s\" validate structure"  \
                     [lindex $index_owner $i] \
                     [lindex $index $i]]
        }

        if {[catch {orasql $cur $sql}]} {
          if { ($oramsg(rc) == 1418 } {
            set tmp [format %s.%s [lindex $index_owner $i] [lindex $index $i]]
            ORATCL_DEBUG "idxrebuild : $oramsg(oraobject) : ORA-01418"
            ORATCL_DEBUG "             index $tmp does not exist"
            incr i
            continue
          } else {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            catch {oraclose $cur2} err
            catch {oraclose $cur} err
	    catch {oralogoff $lda} err
	    lappend output $err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
          }
        }

        # query the index_stats table for the results of analyze
        set sql {select height,lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len 
                 from INDEX_STATS}
        if {[catch {orasql $cur2 $sql}]} {
            lappend output [convertout $oramsg(db_characterset) \
                $oramsg(errortxt)] $sql
            catch {oraclose $cur2} err
	    catch {oraclose $cur} err
	    catch {oralogoff $lda} err
	    lappend output $err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }
        if {[catch {set stat_row [orafetch $cur2]} err]} {
	    lappend output $err
            catch {oraclose $cur2} err
            catch {oraclose $cur} err
            catch {oralogoff $lda} err
            if {$last_report == $SCRIPT_FAIL} {
                return $NO_EVENT
            } else {
                set last_report $SCRIPT_FAIL
                return $SCRIPT_FAIL
            }
        }

        set height [lindex $stat_row 0]
        set lf_rows [format "%f" [lindex $stat_row 1]]
        set lf_rows_len [format "%f" [lindex $stat_row 2]]
        set del_lf_rows [format "%f" [lindex $stat_row 3]]
        set del_lf_rows_len [format "%f" [lindex $stat_row 4]]

        # rebuild may be beneficial if:
        #
        #   height is greater than 4
        #   percentage wasted space on deleted entries compared to active 
        #       entries is greater than 20%
        #   percentage of deleted entries compare to active entries is
        #       greater than 20%
        if {$height > 4} {
            set ret_code $ALERT_EVENT
            set found_alert 1
        } elseif {$lf_rows_len != 0 && [expr [divide $del_lf_rows_len $lf_rows_len] * 100] > 20} {
            set ret_code $ALERT_EVENT
            set found_alert 1
        } elseif {$lf_rows != 0 && [expr [divide $del_lf_rows $lf_rows] * 100] > 20} {
            set ret_code $ALERT_EVENT
            set found_alert 1
        }


        if { $found_alert == 1 } {
            if {$partitioned_index != 0} {
                set tmp [format "%s.%s(%s)" \
                                [lindex $index_owner $i] \
                                [lindex $index $i] \
                                [lindex $index_part $i]]
            } else {
                set tmp [format "%s.%s" [lindex $index_owner $i] [lindex $index $i]]
            }
            set tmp [convertout $oramsg(db_characterset) $tmp]

            if {$number_of_alerts < 20} {
                lappend rebuild_indexes $tmp
            }
            lappend all_rebuild_indexes $tmp

            incr number_of_alerts
            # only send first 20 to oms, but compare all so don't miss diffs
            if {$number_of_alerts == 20} {
                lappend rebuild_indexes ...
            }
        }

        incr i
    } 
    catch {oracommit $cur} err

    # log off
    catch {oraclose $cur2} err
    catch {oraclose $cur} err
    catch {oralogoff $lda} err

    if { $ret_code == $last_report && [string compare $last_rebuild_indexes $all_rebuild_indexes] == 0 } {
        return $NO_EVENT
    } else {
        if {$ret_code == $ALERT_EVENT ||
            $ret_code == $WARNING_EVENT} {
            append output $rebuild_indexes
        }
        ORATCL_DEBUG "idxrebuild : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        set last_rebuild_indexes $all_rebuild_indexes
        return $ret_code 
    }
}

