# $Header: freebuf.tcl 31-mar-00.10:43:38 sgrover Exp $
#
# copyright (c) 1999 by the Oracle Corporation
#
# NAME:
#
#  freebuf.tcl : Event tcl file that checks if the free buffer waits
#                ratio is too high
#
# ARGUMENTS:
#           argv(0) == connect string
#           argv(1) == no of samples
#           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 free buffer waits ratio.
#
# $Log:  $
#
# Revision 1.1  1998/02/16  groyal
# Initial revision
#

# Event definition
oradefine event /oracle/rdbms/perf/freebuf description=VOC-01308 \
report=VOC-01309
oraarguments connect_str no_sample alert_threshold warning_threshold
oravardesc connect_str oracle_signon
oravardesc no_sample unsigned default=10 message=VOC-01310
oravardesc alert_threshold number minimum=0 maximum=100 default=20 message=VOC-01311
oravardesc warning_threshold number minimum=0 maximum=100 default=10 message=VOC-01312
oraresults ratio db_file_simultaneous_writes
oravardesc ratio number
oravardesc db_file_simultaneous_writes unsigned
oradefine end


# Initialize global variables

#comment out to fix bug#606739
#set last_report $CLEAR_EVENT
set output ""
set initial 1
set prev_value 0
set i 0
set deltas {}


# The main event checking functions
proc EvalEvent {} {


    # Declare globals we will use
    global argv last_report output initial i
    global SCRIPT_FAIL CLEAR_EVENT NO_EVENT WARNING_EVENT ALERT_EVENT
    global oramsg prev_value deltas


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

    ORATCL_DEBUG "freebuf: $oramsg(oraobject) : [oratime] "


    # get current value
    # 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 free buffer waits 
    set sql {select total_waits from v$system_event 
             where event = 'free buffer waits'}
    if { [catch { orasql $cur $sql }] } {
        lappend output [convertout $oramsg(db_characterset) \
            $oramsg(errortxt)] $sql
        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 $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} {
        set curr_value 0
    } else {
        set curr_value [lindex $row 0]
    }


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



    # during initialization, copy current value to previous value
    if {$initial == 1} {
        set prev_value $curr_value
        set initial 0
        return $NO_EVENT
    }


    # calculate delta 
    set delta_value [expr $curr_value - $prev_value]
    set prev_value $curr_value


    # deal with rollovers
    if { $delta_value < 0 } {
        ORATCL_DEBUG "freebuf : $oramsg(oraobject) : [oratime] : rollover : $delta_value"
        set delta_value 0
    }


    # build or update list of samples
    set no_samples [lindex $argv 1]

    if {[llength $deltas] < $no_samples} {
        lappend deltas $delta_value
        if {[llength $deltas] < $no_samples} {
            return $NO_EVENT
        }
    } else {
        set deltas [lreplace $deltas $i $i $delta_value]
        incr i
        if {$i == $no_samples} {
            set i 0
        }
    }


    set increase 0
    foreach value $deltas {
        if {$value > 0} {
            incr increase
        }
    }


    # calculate ratio 
    set ratio [expr [divide $increase [llength $deltas]] * 100.0]
    set ratio [format "%3.2f" $ratio]


    # Check for alert and warning threshold
    if {$ratio >= [lindex $argv 2]} {
        set ret_code $ALERT_EVENT
    } elseif {$ratio >= [lindex $argv 3]} {
        set ret_code $WARNING_EVENT
    } 


    # return
    if {$last_report == $ret_code} {
        return $NO_EVENT
    } else {
        if {($ret_code == $WARNING_EVENT) || ($ret_code == $ALERT_EVENT) } {
            # retrieve current value for the db_file_simultaneous_writes instance parameter
            if [catch {set temp_list [oradbsnmp get rdbmsSrvParamCurrValue.$oramsg(oraindex).27.100.98.95.102.105.108.101.95.115.105.109.117.108.116.97.110.101.111.117.115.95.119.114.105.116.101.115.1]} err] {
                # on 8.1+ databases, db_file_simultaneous_writes
		# has been deprecated.  Pass 0 for now.
		set db_file_simultaneous_writes 0
	    } else {
		set db_file_simultaneous_writes [lindex $temp_list 1]
	    }

            lappend output $ratio $db_file_simultaneous_writes
        }

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

}

