oracle_sysstat_

#!/bin/bash
# -*- sh -*-

: << =cut

=head1 NAME

  oracle_sysstat_* - Munin plugin to monitor Oracle Statistics

    execute                 - To monitor Oracle Sysstat Execute Count
    parse                   - To monitor Oracle Sysstat Parse Count
    tablefetch              - To monitor Oracle Sysstat Table Fetch Rows
    tablescan               - To monitor Oracle Sysstat Table Scans
    transaction             - To monitor Oracle Sysstat Transactions
    sort                    - To monitor Oracle Sysstat Sorts
    logon                   - To monitor Oracle Sysstat User Logons
    cursor                  - To monitor Oracle Sysstat Open Cursor
    enqueue                 - To monitor Oracle Sysstat Enqueues
    redolog                 - To monitor Oracle Sysstat Redo Log
    physicaliops            - To monitor Oracle Sysstat Physical I/O Requests
    physicalrw              - To monitor Oracle Sysstat Physical Read/Write Bytes
    netrw                   - To monitor Oracle Sysstat Network Send/Receive Bytes
    sgainfo                 - To monitor Oracle Memory SGA
    pgastat                 - To monitor Oracle Memory PGA
    cachehit                - To monitor Oracle Cache Hit Ratio
    sessionuser             - To monitor Oracle Session Users
    sessionwait             - To monitor Oracle Session Wait
    eventwait               - To monitor Oracle Wait Events
    eventwaitapplication    - To monitor Oracle Wait Events Application
    eventwaitnetwork        - To monitor Oracle Wait Events Network
    eventwaitconcurrency    - To monitor Oracle Wait Events Concurrency
    eventwaituserio         - To monitor Oracle Wait Events User I/O
    eventwaitsystemio       - To monitor Oracle Wait Events System I/O
    eventwaitcluster        - To monitor Oracle Wait Events Cluster
    eventwaitadministrative - To monitor Oracle Wait Events Administrative
    eventwaitconfiguration  - To monitor Oracle Wait Events Configuration
    tablespace              - To monitor Oracle Table Space Usage
    asmusage                - To monitor Oracle ASM Disk Group Usage

=head1 CONFIGURATION

  To get a list of symlinks that can be created, run:   

    ./oracle_sysstat_ suggest

  Make symlinks:

    munin-node-configure --families=contrib --suggest --shell
    ...

  The following shows example settings for this plugin:

    [oracle_sysstat_*]
      user  oracle
      env.oracle_auth  / as SYSDBA
      env.ORACLE_HOME  /path/to/oracle/product/version
      env.ORACLE_SID   SOMESID

    [oracle_sysstat_asmusage]
      user  grid
      env.oracle_auth  / as SYSASM
      env.ORACLE_HOME  /path/to/grid/home/version
      env.ORACLE_SID   SOMESID

=head1 NOTES

  Uses the command "sqlplus".
  Tested with Oracle Database 12c R1.

=head1 AUTHOR

  K.Cima https://github.com/shakemid

=head1 LICENSE

  GPLv2

=cut

# Magic markers
#%# family=contrib
#%# capabilities=autoconf suggest

# Include plugin.sh
. "${MUNIN_LIBDIR}/plugins/plugin.sh"

# Like perl 'use strict;' 
#set -o nounset

# Environments
: "${ORACLE_HOME:=$( echo /opt/oracle/product/* )}"
: "${ORACLE_SID:=orcl}"
: "${oracle_auth:=/ as SYSDBA}"

PATH=$PATH:$ORACLE_HOME/bin
export PATH ORACLE_HOME ORACLE_SID

# Module name
module=$( basename "$0" | sed -e 's/^.*_//' )

# Graph settings
declare -A global_attrs  # required
declare -A data_attrs    # required (format: field type draw label)
declare -A getfield_func # optional
declare -A getvalue_func # required

key=execute
global_attrs[$key]="
    graph_title Oracle Sysstat Execute Count
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Execute Count
"
data_attrs[$key]="
    execute_count    DERIVE LINE execute count
    user_calls       DERIVE LINE user calls
    recursive_calls  DERIVE LINE recursive calls
"
getvalue_func[$key]=getvalue_sysstat

key=parse
global_attrs[$key]="
    graph_title Oracle Sysstat Parse Count
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Parse Count
"
data_attrs[$key]="
    parse_count_total     DERIVE LINE parse count (total)
    parse_count_hard      DERIVE LINE parse count (hard)
    parse_count_failures  DERIVE LINE parse count (failures)
    parse_count_describe  DERIVE LINE parse count (describe)
"
getvalue_func[$key]=getvalue_sysstat

key=tablefetch
global_attrs[$key]="
    graph_title Oracle Sysstat Table Fetch Rows
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Table Scans
"
data_attrs[$key]="
    table_fetch_by_rowid   DERIVE LINE table fetch by rowid
    table_scan_rows_gotten DERIVE LINE table scan rows gotten
"
getvalue_func[$key]=getvalue_sysstat

key=tablescan
global_attrs[$key]="
    graph_title Oracle Sysstat Table Scans
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Table Scans
"
data_attrs[$key]="
    table_scans_short_tables DERIVE LINE table scans (short tables)
    table_scans_long_tables  DERIVE LINE table scans (long tables)
"
getvalue_func[$key]=getvalue_sysstat

key=transaction
global_attrs[$key]="
    graph_title Oracle Sysstat Transactions
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Transactions
"
data_attrs[$key]="
    user_commits   DERIVE LINE user commits
    user_rollbacks DERIVE LINE user rollbacks
"
getvalue_func[$key]=getvalue_sysstat

key=sort
global_attrs[$key]="
    graph_title Oracle Sysstat Sorts
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat - Sorts
"
data_attrs[$key]="
    sorts_memory DERIVE LINE sorts (memory)
    sorts_disk   DERIVE LINE sorts (disk)
"
getvalue_func[$key]=getvalue_sysstat

key=logon
global_attrs[$key]="
    graph_title Oracle Sysstat User Logons
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat User Logons
"
data_attrs[$key]="
    logon DERIVE LINE logons cumulative
"
getvalue_func[$key]=getvalue_sysstat

key=cursor

global_attrs[$key]="
    graph_title Oracle Sysstat Open Cursors
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count
    graph_info Oracle Sysstat Open Cursors
"
data_attrs[$key]="
    open_cursor GAUGE LINE opened cursors current
"
getvalue_func[$key]=getvalue_sysstat

key=enqueue
global_attrs[$key]="
    graph_title Oracle Sysstat Enqueues
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Enqueues
"
data_attrs[$key]="
    enqueue_requests    DERIVE LINE enqueue requests
    enqueue_releases    DERIVE LINE enqueue releases
    enqueue_conversions DERIVE LINE enqueue conversions
    enqueue_waits       DERIVE LINE enqueue waits
    enqueue_timeouts    DERIVE LINE enqueue timeouts
    enqueue_deadlocks   DERIVE LINE enqueue deadlocks
"
getvalue_func[$key]=getvalue_sysstat

key=redolog
global_attrs[$key]="
    graph_title Oracle Sysstat Redo Log
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count per second
    graph_info Oracle Sysstat Redo Log
"
data_attrs[$key]="
    redo_entries                   DERIVE LINE redo entries
    redo_buffer_allocation_retries DERIVE LINE redo buffer allocation retries
    redo_log_space_requests        DERIVE LINE redo log space requests
"
getvalue_func[$key]=getvalue_sysstat

key=physicaliops
global_attrs[$key]="
    graph_title Oracle Sysstat Physical I/O Requests
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel iops
    graph_info Oracle Sysstat Physical I/O Requests
"
data_attrs[$key]="
    physical_read_total        DERIVE LINE2 physical read total IO requests
    physical_read              DERIVE LINE  physical read IO requests
    physical_read_total_multi  DERIVE LINE  physical read total multi block requests
    physical_write_total       DERIVE LINE2 physical write total IO requests
    physical_write             DERIVE LINE  physical write IO requests
    physical_write_total_multi DERIVE LINE  physical write total multi block requests
"
getvalue_func[$key]=getvalue_sysstat

key=physicalrw
global_attrs[$key]="
    graph_title Oracle Sysstat Physical Read/Write Bytes
    graph_category db
    graph_args --base 1024 --lower-limit 0 --rigid
    graph_vlabel bytes per second
    graph_info Oracle Sysstat Physical Read/Write Bytes
"
data_attrs[$key]="
    physical_read_total  DERIVE LINE2 physical read total bytes
    physical_read        DERIVE LINE  physical read bytes
    physical_write_total DERIVE LINE2 physical write total bytes
    physical_write       DERIVE LINE  physical write bytes
"
getvalue_func[$key]=getvalue_sysstat

key=netrw
global_attrs[$key]="
    graph_title Oracle Sysstat Network Send/Receive Bytes
    graph_category db
    graph_args --base 1024 --lower-limit 0 --rigid
    graph_vlabel bytes per second
    graph_info Oracle Sysstat Network Send/Receive Bytes
"
data_attrs[$key]="
    bytes_sent_via_sql_net_to_client       DERIVE LINE bytes sent via SQL*Net to client
    bytes_received_via_sql_net_from_client DERIVE LINE bytes received via SQL*Net from client
    bytes_sent_via_sql_net_to_dblink       DERIVE LINE bytes sent via SQL*Net to dblink
    bytes_received_via_sql_net_from_dblink DERIVE LINE bytes received via SQL*Net from dblink
"
getvalue_func[$key]=getvalue_sysstat

key=sgainfo
global_attrs[$key]="
    graph_title Oracle Memory SGA
    graph_category db
    graph_args --base 1024 --lower-limit 0 --rigid
    graph_vlabel bytes
    graph_info Oracle Memory SGA
"
data_attrs[$key]="
    maximum_sga_size    GAUGE LINE      Maximum SGA Size
    fixed_sga_size      GAUGE AREASTACK Fixed SGA Size
    redo_buffers        GAUGE AREASTACK Redo Buffers
    shared_pool_size    GAUGE AREASTACK Shared Pool Size
    large_pool_size     GAUGE AREASTACK Large Pool Size
    java_pool_size      GAUGE AREASTACK Java Pool Size
    streams_pool_size   GAUGE AREASTACK Streams Pool Size
    shared_io_pool_size GAUGE AREASTACK Shared IO Pool Size
    buffer_cache_size   GAUGE AREASTACK Buffer Cache Size
    in_memory_area_size GAUGE AREASTACK In-Memory Area Size
"
getvalue_func[$key]=getvalue_sgainfo

key=pgastat
global_attrs[$key]="
    graph_title Oracle Memory PGA
    graph_category db
    graph_args --base 1024 --lower-limit 0 --rigid
    graph_vlabel bytes
    graph_info Oracle Memory PGA
"
data_attrs[$key]="
    pga_target    GAUGE LINE aggregate PGA auto target
    pga_allocated GAUGE LINE total PGA allocated
    pga_inuse     GAUGE AREA total PGA inuse
"
getvalue_func[$key]=getvalue_pgastat

key=cachehit
global_attrs[$key]="
    graph_title Oracle Cache Hit Ratio
    graph_category db
    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid
    graph_vlabel %
    graph_info Oracle Cache Hit Ratio - The graph shows cache hit ratio between munin-update intervals (5 minutes in most cases).
    graph_scale no
"
data_attrs[$key]="
    buf_hitratio  GAUGE LINE Buffer Cache Hit Ratio
    lib_hitratio  GAUGE LINE Library Cache Hit Ratio
    dict_hitratio GAUGE LINE Dictionary Cache Hit Ratio
"
getvalue_func[$key]=getvalue_cachehit

key=sessionuser
global_attrs[$key]="
    graph_title Oracle Session Users
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count
    graph_info Oracle Session Users
"
data_attrs[$key]=""
getfield_func[$key]=getfield_sessionuser
getvalue_func[$key]=getvalue_sessionuser

key=sessionwait
global_attrs[$key]="
    graph_title Oracle Session Wait
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel count
    graph_info Oracle Session Wait
"
data_attrs[$key]=""
getfield_func[$key]=getfield_sessionwait
getvalue_func[$key]=getvalue_sessionwait

key=eventwait
global_attrs[$key]="
    graph_title Oracle Wait Events
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events - It may looks wierd that Y-axis indicates 'microseconds per second'. Although number of times of wait event looks easier to understand, in many cases the number of events does not matter, but wait time become more important to analyze bottle necks.
"
data_attrs[$key]=""
getfield_func[$key]=getfield_eventwait
getvalue_func[$key]=getvalue_eventwait

key=eventwaitapplication
global_attrs[$key]="
    graph_title Oracle Wait Events Application
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Application
"
data_attrs[$key]=""
getfield_func[$key]="getfield_eventwait2 Application"
getvalue_func[$key]="getvalue_eventwait2 Application"

key=eventwaitnetwork
global_attrs[$key]="
    graph_title Oracle Wait Events Network
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Network
"
data_attrs[$key]=""
getfield_func[$key]="getfield_eventwait2 Network"
getvalue_func[$key]="getvalue_eventwait2 Network"

key=eventwaitconcurrency
global_attrs[$key]="
    graph_title Oracle Wait Events Concurrency
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Concurrency
"
data_attrs[$key]=" "
getfield_func[$key]="getfield_eventwait2 Concurrency"
getvalue_func[$key]="getvalue_eventwait2 Concurrency"

key=eventwaituserio
global_attrs[$key]="
    graph_title Oracle Wait Events User I/O
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events User I/O
"
data_attrs[$key]=""
getfield_func[$key]="getfield_eventwait2 User I/O"
getvalue_func[$key]="getvalue_eventwait2 User I/O"

key=eventwaitsystemio
global_attrs[$key]="
    graph_title Oracle Wait Events System I/O
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events System I/O
"
data_attrs[$key]="
"
getfield_func[$key]="getfield_eventwait2 System I/O"
getvalue_func[$key]="getvalue_eventwait2 System I/O"

key=eventwaitcluster
global_attrs[$key]="
    graph_title Oracle Wait Events Cluster
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Cluster
"
data_attrs[$key]=" "
getfield_func[$key]="getfield_eventwait2 Cluster"
getvalue_func[$key]="getvalue_eventwait2 Cluster"

key=eventwaitadministrative
global_attrs[$key]="
    graph_title Oracle Wait Events Administrative
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Administrative
"
data_attrs[$key]=" "
getfield_func[$key]="getfield_eventwait2 Administrative"
getvalue_func[$key]="getvalue_eventwait2 Administrative"

key=eventwaitconfiguration
global_attrs[$key]="
    graph_title Oracle Wait Events Configuration
    graph_category db
    graph_args --base 1000 --lower-limit 0 --rigid
    graph_vlabel microseconds
    graph_info Oracle Wait Events Configuration
"
data_attrs[$key]=" "
getfield_func[$key]="getfield_eventwait2 Configuration"
getvalue_func[$key]="getvalue_eventwait2 Configuration"

key=tablespace
global_attrs[$key]="
    graph_title Oracle Table Space Usage
    graph_category db
    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid
    graph_vlabel %
    graph_info Oracle Table Space Usage
    warning ${warning:=92}
    critical ${critical:=98}
"
data_attrs[$key]=""
getfield_func[$key]=getfield_tablespace
getvalue_func[$key]=getvalue_tablespace

key=asmusage
global_attrs[$key]="
    graph_title Oracle ASM Disk Group Usage
    graph_category db
    graph_args --base 1000 --lower-limit 0 --upper-limit 100 --rigid
    graph_vlabel %
    graph_info Oracle ASM Disk Group Usage
    warning ${warning:=92}
    critical ${critical:=98}
"
data_attrs[$key]=""
getfield_func[$key]=getfield_asmusage
getvalue_func[$key]=getvalue_asmusage

# End of Graph Settings

# sqlplus options
: "${sqlplus:=sqlplus -S -L}"
sqlplus_variables="
    set pagesize 0
    set feed off
    set head off
    set linesize 256
    set numwidth 20
"

# functions 

autoconf() {
    if [ -x $( which "${sqlplus}" ) ]; then
        echo yes
    else
        echo "no (failed to find executable 'sqlplus')"
    fi
}

suggest() {
    echo "${!global_attrs[@]}" | tr ' ' '\n' | sort
}

config() {
    # print global attributes
    sed -e 's/^  *//' -e '/^$/d' <<< "${global_attrs[$module]}"

    # print data source attributes
    # split line into field,type,draw,label 
    local fields field type draw label
    while read -r field type draw label
    do
        [ -z "${field:-}" ] && continue
        fields="${fields:-} ${field}"

        echo "${field}.type"  "$type"
        echo "${field}.draw"  "$draw"
        echo "${field}.label" "$label"
    done <<< "${data_attrs[$module]}"

    echo graph_order "$fields"
}

# wrapper for getfield_*
getfield() {
    local func arg
    if [ -n "${getfield_func[$module]:-}" ]; then
        # call getfield_* function with argument if necessary
        read -r func arg <<< "${getfield_func[$module]}"
        $func "$arg"
    fi
}

# wrapper for getvalue_*
getvalue() {
    local func arg
    # call getvalue_* function with argument if necessary
    read -r func arg <<< "${getvalue_func[$module]}"
    $func "$arg"
}

getvalue_sysstat() {
    local field type draw label
    while read -r field type draw label
    do
        [ -z "$field" ] && continue

        cat <<EOF
${sqlplus_variables}
VAR vf VARCHAR2(64)
VAR vl VARCHAR2(64)
EXEC :vf := '${field}' 
EXEC :vl := '${label}' 
SELECT
  :vf || '.value ' || value
FROM
  v\$sysstat
WHERE
  name = :vl;
EOF
    done <<< "${data_attrs[$module]}" | ${sqlplus} "${oracle_auth}"
}

getvalue_sgainfo() {
    local field type draw label
    while read -r field type draw label
    do
        [ -z "$field" ] && continue

        cat <<EOF
${sqlplus_variables}
VAR vf VARCHAR2(64)
VAR vl VARCHAR2(64)
EXEC :vf := '${field}' 
EXEC :vl := '${label}' 
SELECT
  :vf || '.value ' || bytes
FROM
  v\$sgainfo
WHERE
  name = :vl;
EOF
    done <<< "${data_attrs[$module]}" | ${sqlplus} "${oracle_auth}"
}

getvalue_pgastat() {
    local field type draw label
    while read -r field type draw label
    do
        [ -z "$field" ] && continue

        cat <<EOF
${sqlplus_variables}
VAR vf VARCHAR2(64)
VAR vl VARCHAR2(64)
EXEC :vf := '${field}' 
EXEC :vl := '${label}' 
SELECT
  :vf || '.value ' || value
FROM
  v\$pgastat
WHERE
  name = :vl;
EOF
    done <<< "${data_attrs[$module]}" | ${sqlplus} "${oracle_auth}"
}

getvalue_cachehit() {
    local buf_physical_reads buf_db_block_gets buf_consistent_gets
    local lib_pins lib_reloads
    local dict_gets dict_getmisses

    local prev_buf_physical_reads prev_buf_db_block_gets prev_buf_consistent_gets
    local prev_lib_pins prev_lib_reloads
    local prev_dict_gets prev_dict_getmisses

    local buf_hitratio lib_hitratio dict_hitratio

    local statefile="${MUNIN_PLUGSTATE:-}/oracle_sysstat_cachehit.state"
    local is_initial=0

    # get previous values from state file
    if [ -r "$statefile" ];then
        . "$statefile"
    else
        # looks initial run
        is_initial=1
    fi

    # get current values
    buf_physical_reads=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(value) FROM v\$sysstat WHERE name = 'physical reads cache';
EOF
)
    buf_db_block_gets=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(value) FROM v\$sysstat WHERE name = 'db block gets from cache';
EOF
)
    buf_consistent_gets=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(value) FROM v\$sysstat WHERE name = 'consistent gets from cache';
EOF
)
    lib_pins=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(pins) FROM v\$librarycache;
EOF
)
    lib_reloads=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(reloads) FROM v\$librarycache;
EOF
)
    dict_gets=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(gets) FROM v\$rowcache;
EOF
)
    dict_getmisses=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT SUM(getmisses) FROM v\$rowcache;
EOF
)

    # calc values
    if [ "$is_initial" = 0 ]; then
        buf_hitratio=$( bc <<< "scale=2; \
            p=${buf_physical_reads} - ${prev_buf_physical_reads}; \
            d=${buf_db_block_gets} - ${prev_buf_db_block_gets}; \
            c=${buf_consistent_gets} - ${prev_buf_consistent_gets}; \
            ( 1 - p / ( d + c ) ) * 100" \
            | sed -e 's/\..*$//' )
        lib_hitratio=$( bc <<< "scale=2; \
            p=${lib_pins} - ${prev_lib_pins}; \
            r=${lib_reloads} - ${prev_lib_reloads}; \
            p / ( p + r ) * 100" \
            | sed -e 's/\..*$//' )
        dict_hitratio=$( bc <<< "scale=2; \
            g=${dict_gets} - ${prev_dict_gets}; \
            m=${dict_getmisses} - ${prev_dict_getmisses}; \
            ( g - m ) / g * 100" \
            | sed -e 's/\..*$//' )

        # validation 
        if [[ $buf_hitratio =~ [^0-9] || $buf_hitratio -gt 100 ]]; then
            buf_hitratio=U
        fi
        if [[ $lib_hitratio =~ [^0-9] || $lib_hitratio -gt 100 ]]; then
            lib_hitratio=U
        fi
        if [[ $dict_hitratio =~ [^0-9] || $dict_hitratio -gt 100 ]]; then
            dict_hitratio=U
        fi
    else
        buf_hitratio=U
        lib_hitratio=U
        dict_hitratio=U
    fi

    # print values
    echo "buf_hitratio.value ${lib_hitratio}"
    echo "lib_hitratio.value ${lib_hitratio}"
    echo "dict_hitratio.value ${dict_hitratio}"

    # store current values to state file
    cat <<EOF >"$statefile"
prev_buf_physical_reads="${buf_physical_reads}"
prev_buf_db_block_gets="${buf_db_block_gets}"
prev_buf_consistent_gets="${buf_consistent_gets}"
prev_lib_pins="${lib_pins}"
prev_lib_reloads="${lib_reloads}"
prev_dict_gets="${dict_gets}"
prev_dict_getmisses="${dict_getmisses}"
EOF
}

getfield_sessionuser() {
    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( username, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' GAUGE LINE ' ||  username
FROM
  dba_users
WHERE
  account_status = 'OPEN'
ORDER BY
  username;
EOF
)
}

getvalue_sessionuser() {
    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( du.username, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||
  count(vs.username)
FROM 
  ( SELECT
      username
    FROM
      dba_users
    WHERE
      account_status = 'OPEN'
  ) du
  LEFT JOIN v\$session vs
ON
  du.username = vs.username
GROUP BY
  du.username;
EOF
}

getfield_sessionwait() {
    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' GAUGE AREASTACK ' || wait_class
FROM
  v\$event_name
WHERE
  wait_class NOT IN ( 'Other', 'Idle' )
GROUP BY
  wait_class
ORDER BY
  wait_class;
SELECT 'Other GAUGE AREASTACK Other' from dual;
SELECT 'Idle  GAUGE AREASTACK Idle' from dual;
EOF
)
}

getvalue_sessionwait() {
    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( en.wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||
  count(se.wait_class)
FROM
  ( SELECT
      wait_class
    FROM
      v\$event_name
    GROUP BY
      wait_class
  ) en
  LEFT JOIN v\$session se
ON
  en.wait_class = se.wait_class AND 
  se.username is not null
GROUP BY
  en.wait_class;
EOF
}

getfield_eventwait() {
    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' DERIVE LINE ' || wait_class
FROM
  v\$event_name
WHERE
  wait_class NOT IN ( 'Other', 'Idle' )
GROUP BY
  wait_class
ORDER BY
  wait_class;
SELECT 'Other DERIVE LINE Other' from dual;
EOF
)
}

getvalue_eventwait() {
    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( en.wait_class, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||
  NVL( SUM(se.time_waited_micro), 0 )
FROM
  ( SELECT
      wait_class
    FROM
      v\$event_name
    WHERE
      wait_class NOT IN ( 'Idle' )
    GROUP BY
      wait_class
  ) en
  LEFT JOIN v\$system_event se
ON
  en.wait_class = se.wait_class
GROUP BY
  en.wait_class;
EOF
}

getfield_eventwait2() {
    local waitclass="$1"

    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
VAR vl VARCHAR2(64)
EXEC :vl := '${waitclass}' 
SELECT
  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' DERIVE LINE ' || SUBSTR( name, 1, 45 )
FROM
  v\$event_name 
WHERE
  wait_class = :vl
ORDER BY
  name;
EOF
)
}

getvalue_eventwait2() {
    local waitclass="$1"

    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
VAR vl VARCHAR2(64)
EXEC :vl := '${waitclass}' 
SELECT
  REGEXP_REPLACE( en.name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' || 
  NVL( se.time_waited_micro, 0 )
FROM
  v\$event_name en LEFT JOIN v\$system_event se
ON
  en.name = se.event
WHERE
  en.wait_class = :vl;
EOF
}

getfield_tablespace() {
    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( tablespace_name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' GAUGE LINE ' || tablespace_name
FROM
  dba_data_files
ORDER BY
  tablespace_name;
EOF
)
}

getvalue_tablespace() {
    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( tablespace_name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||
  ROUND( (total_bytes - free_total_bytes) / total_bytes * 100, 0 )
FROM
  ( SELECT
      tablespace_name,
      SUM(bytes) total_bytes
    FROM
      dba_data_files
    GROUP BY
      tablespace_name
  ),
  ( SELECT
      tablespace_name free_tablespace_name,
      SUM(bytes) free_total_bytes
    FROM
      dba_free_space
    GROUP BY
      tablespace_name
  )
WHERE
  tablespace_name = free_tablespace_name;
EOF
}

getfield_asmusage() {
    data_attrs[$module]=$( ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) ||
  ' GAUGE LINE ' || name
FROM
  v\$asm_diskgroup
ORDER BY
  name;
EOF
)
}

getvalue_asmusage() {
    ${sqlplus} "${oracle_auth}" <<EOF
${sqlplus_variables}
SELECT
  REGEXP_REPLACE( name, '^[^A-Za-z_]|[^A-Za-z0-9_]', '_' ) || '.value ' ||
  ROUND( ( total_mb - free_mb ) / total_mb * 100 )
FROM
  v\$asm_diskgroup
ORDER BY
  name;
EOF
}

# main
case ${1:-} in
autoconf)
    autoconf
    ;;
suggest)
    suggest
    ;;
config)
    getfield
    config
    ;;
*)
    getvalue
    ;;
esac

exit 0