# $Header: rollback.tcl 30-mar-1999 dholail Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  rollback.tcl : Event tcl file that checks for rollback segment contention
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == no of occurrance
#           argv(2) == threshold value for alert
#           argv(3) == threshold value for warning
#
# RETURN:
#           $SCRIPT_FAIL or
#           $CLEAR_EVENT or
#           $NO_EVENT or
#           $WARNING_EVENT or
#           $ALERT_EVENT
#
# OUTPUT:
#           The current rollback contention.
#
# $Log:  $
# Revision 1.1  1998/02/17  groyal
# Initial revision
#

# Event definition
oradefine event /oracle/rdbms/perf/rollback description=VOC-01313 \
report=VOC-01314
oraarguments connect_str no_occurrence alert_threshold warning_threshold
oravardesc connect_str oracle_signon
oravardesc no_occurrence unsigned default=3 message=VOC-01315
oravardesc alert_threshold number minimum=0 maximum=100 default=1 message=VOC-01316
oravardesc warning_threshold number minimum=0 maximum=100 default=0 message=VOC-01317
oraresults ratio active_trans rbs_count
oravardesc ratio number
oravardesc active_trans unsigned
oravardesc rbs_count unsigned
oradefine end


# Initialize global variables

#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
set no_occurrence 0
set initial 1
set prev_values(system_undo_header) 0
set prev_values(system_undo_block) 0
set prev_values(undo_header) 0
set prev_values(undo_block) 0
set prev_values2(consistent_gets) 0
set prev_values2(block_gets) 0


# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output no_occurrence initial
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg prev_values prev_values2


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

    # 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
        }
    }

    # get system undo header waits 
    set sql {select count from v$waitstat where class = 'system undo header'}
    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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 {[catch {set curr_values(system_undo_header) [orafetch $cur]} 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 system undo block waits 
    set sql {select count from v$waitstat where class = 'system undo block'}
    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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 {[catch {set curr_values(system_undo_block) [orafetch $cur]} 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 undo header waits 
    set sql {select count from v$waitstat where class = 'undo header'}
    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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 {[catch {set curr_values(undo_header) [orafetch $cur]} 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 undo block waits 
    set sql {select count from v$waitstat where class = 'undo block'}
    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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 {[catch {set curr_values(undo_block) [orafetch $cur]} 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 current value
    if {[catch {set curr_values2(consistent_gets) [lindex [oradbsnmp get \
        oraDbSysConsistentGets.$oramsg(oraindex)] 1]} 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
        }
    }

    if {[catch {set curr_values2(block_gets) [lindex [oradbsnmp get \
        oraDbSysDbBlockGets.$oramsg(oraindex)] 1]} 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
        }
    }

    # During initialization, copy current values to previous values
    if {$initial == 1} {
        foreach att_name [array names curr_values] {
            set prev_values($att_name) $curr_values($att_name)
        }
        foreach att_name [array names curr_values2] {
            set prev_values2($att_name) $curr_values2($att_name)
        }
        set initial 0

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


    # calculate delta 
    foreach att_name [array names curr_values] {
        set delta($att_name) [expr $curr_values($att_name) - $prev_values($att_name)]
        set prev_values($att_name) $curr_values($att_name)

    }
    foreach att_name [array names curr_values2] {
        set delta2($att_name) [expr $curr_values2($att_name) - $prev_values2($att_name)]
        set prev_values2($att_name) $curr_values2($att_name)
    }


    set data_requests [expr double ([expr $delta2(consistent_gets) + $delta2(block_gets)])]


    # watch out for divide by 0
    if { $data_requests == 0 } {
        ORATCL_DEBUG "rollback: $oramsg(oraobject) : [oratime] : divide by zero : $data_requests"
        # log off
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        return $NO_EVENT
    }


    # determine if any of the wait classes exceed threshold
    set ratio -1.0
    foreach att_name [array names curr_values] {
        set v1 [expr double ($delta($att_name))]

        # deal with rollovers
        if { ($v1 < 0) } {
            ORATCL_DEBUG "rollback : $oramsg(oraobject) : [oratime] : rollover : $att_name,$v1"
            continue
        }


        # calculate ratio and keep track of highest ratio
        set tmp_ratio [expr [divide $v1 $data_requests] * 100]
        set tmp_ratio [format "%3.2f" $tmp_ratio]


        # ignore negative ratios
        if {$tmp_ratio < 0} {
            ORATCL_DEBUG "rollback : $oramsg(oraobject) : [oratime] : negative ratio : $att_name,$v1,$data_requests,$tmp_ratio"
            continue
        }

        ORATCL_DEBUG "rollback : $oramsg(oraobject) : [oratime] : $att_name,$v1,$data_requests,$tmp_ratio"

        if {$ratio < $tmp_ratio} {
            set ratio $tmp_ratio
        }
    }


    # ignore negative ratios
    if {$ratio < 0} {
        # log off
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        return $NO_EVENT
    }

  
    # Check for alert and warning threshold
    if {$ratio > [lindex $argv 2]} {
        if {$no_occurrence < [expr [lindex $argv 1]-1]} {
            incr no_occurrence
        } else {
            set ret_code $ALERT_EVENT
            set output $ratio
        }
    } elseif {$ratio > [lindex $argv 3]} {
        if {$no_occurrence < [expr [lindex $argv 1]-1]} {
            incr no_occurrence
        } else {
            set ret_code $WARNING_EVENT
            set output $ratio
        }
    } else {
        set no_occurrence 0
    }


    # return
    if {$last_report == $ret_code} {
        catch {oraclose $cur} err
        catch {oralogoff $lda} err
        return $NO_EVENT
    } else {
        if {($ret_code == $WARNING_EVENT) || ($ret_code == $ALERT_EVENT) } {
            set sql {select sum(xacts) from v$rollstat}
            if { [catch { orasql $cur $sql }] } {
                lappend output [convertout $oramsg(db_characterset) \
                    $oramsg(errortxt)] $sql
	        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 {[catch {set active_trans [orafetch $cur]} 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
	        }
	    }
            lappend output $active_trans
            set sql {select count(*) from v$rollstat where status = 'ONLINE'}
            if { [catch { orasql $cur $sql }] } {
                lappend output [convertout $oramsg(db_characterset) \
                    $oramsg(errortxt)] $sql
                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 {[catch {set rbs_count [orafetch $cur]} 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
	        }
	    }

            lappend output $rbs_count
        }
        catch {oraclose $cur} err
        catch {oralogoff $lda} err

        ORATCL_DEBUG "rollback : $oramsg(oraobject) : [oratime] : MESSAGE - $ret_code, $output"
        set last_report $ret_code
        return $ret_code 
    }
}

