checks/oracle_performance

   1 #!/usr/bin/python
   2 # -*- encoding: utf-8; py-indent-offset: 4 -*-
   3 # +------------------------------------------------------------------+
   4 # |             ____ _               _        __  __ _  __           |
   5 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   6 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   7 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   8 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   9 # |                                                                  |
  10 # | Copyright Mathias Kettner 2016             mk@mathias-kettner.de |
  11 # +------------------------------------------------------------------+
  12 #
  13 # This file is part of Check_MK.
  14 # The official homepage is at http://mathias-kettner.de/check_mk.
  15 #
  16 # check_mk is free software;  you can redistribute it and/or modify it
  17 # under the  terms of the  GNU General Public License  as published by
  18 # the Free Software Foundation in version 2.  check_mk is  distributed
  19 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  20 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  21 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  22 # tails. You should have  received  a copy of the  GNU  General Public
  23 # License along with GNU Make; see the file  COPYING.  If  not,  write
  24 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  25 # Boston, MA 02110-1301 USA.
  26
  27 # In cooperation with Thorsten Bruhns from OPITZ Consulting
  28
  29 # <<oracle_performance:sep(124)>>>
  30 # TUX12C|DB CPU|64
  31 # TUX12C|DB time|86
  32
  33 # server-linux-oracle-12:
  34 # <<<oracle_performance:sep(124)>>>
  35 # ENLT1|sys_time_model|DB CPU|223408
  36 # ENLT1|sys_time_model|DB time|630525
  37 # ENLT1|buffer_pool_statistics|DEFAULT|207456769|194044148|3075188333|126417048|10935918|0|419514
  38 # ENLT1|librarycache|SQL AREA|84972008|84406451|196493113|193867707|791310|39140
  39 # ENLT1|librarycache|TABLE/PROCEDURE|13196582|12937687|120405491|118546232|869542|0
  40 # ENLT1|librarycache|BODY|8682469|8666221|11047659|11025730|3932|0
  41 # ENLT1|librarycache|TRIGGER|21238|19599|21238|19599|0|0
  42 # ENLT1|librarycache|INDEX|192359|171580|173880|112887|22742|0
  43 # ENLT1|librarycache|CLUSTER|287523|284618|297990|294967|118|0
  44 # ENLT1|librarycache|DIRECTORY|647|118|1297|232|0|0
  45 # ENLT1|librarycache|QUEUE|6916850|6916397|14069290|14068271|367|0
  46 # ENLT1|librarycache|APP CONTEXT|32|15|63|35|11|0
  47 # ENLT1|librarycache|RULESET|2|1|15|9|4|0
  48 # ENLT1|librarycache|SUBSCRIPTION|63|59|123|84|31|0
  49 # ENLT1|librarycache|LOCATION|388|277|388|277|0|0
  50 # ENLT1|librarycache|TRANSFORMATION|3452154|3451741|3452154|3451741|0|0
  51 # ENLT1|librarycache|USER AGENT|24|15|12|2|1|0
  52 # ENLT1|librarycache|TEMPORARY TABLE|45298|33939|45298|0|33939|0
  53 # ENLT1|librarycache|TEMPORARY INDEX|18399|3046|18399|0|3046|0
  54 # ENLT1|librarycache|EDITION|4054576|4054369|7846832|7846023|366|0
  55
  56
  57 def parse_oracle_performance(info):
  58     parsed = {}
  59     for line in info:
  60         if len(line) < 3:
  61             continue
  62         parsed.setdefault(line[0], {})
  63         parsed[line[0]].setdefault(line[1], {})
  64         counters = line[3:]
  65         if len(counters) == 1:
  66             parsed[line[0]][line[1]].setdefault(line[2], int(counters[0]))
  67         else:
  68             parsed[line[0]][line[1]].setdefault(line[2], map(int, counters))
  69
  70     return parsed
  71
  72
  73 def inventory_oracle_performance(parsed):
  74     for sid in parsed:
  75         yield sid, None
  76
  77
  78 def check_oracle_performance(item, _no_params, parsed):
  79     data = parsed.get(item, None)
  80
  81     # In case of missing information we assume that the login into
  82     # the database has failed and we simply skip this check. It won't
  83     # switch to UNKNOWN, but will get stale.
  84     if not data:
  85         raise MKCounterWrapped("Login into database failed")
  86
  87     now = time.time()
  88     perfdata = []
  89     infotexts = []
  90
  91     # old agents deliver no data for sys_time_model!
  92     # sys_time_model: only DB_CPU and DB_Time!
  93     map_db_time_vars = {
  94         "DB CPU": "oracle_db_cpu",
  95         "DB time": "oracle_db_time",
  96     }
  97     sys_time_model = data.get("sys_time_model", {})
  98     for what, val in sys_time_model.items():
  99         perfvar = map_db_time_vars.get(what)
 100         rate = get_rate("oracle_perf.%s.sys_time_model.%s" % (item, perfvar), now, val)
 101         infotexts.append("%s: %.1f/s" % (what, rate))
 102         perfdata.append((perfvar, rate))
 103
 104     # old agents delivery not the needed data...
 105     # sgainfo
 106     sga_info = data.get('SGA_info', None)
 107     if sga_info:
 108         for what, val, perfvar in [
 109             ('SGA', sga_info["Maximum SGA Size"], 'oracle_sga_size'),
 110             ('DB Cache', sga_info["Buffer Cache Size"], 'oracle_sga_buffer_cache'),
 111             ('Shared Pool', sga_info["Shared Pool Size"], 'oracle_sga_shared_pool'),
 112             ('Redo Buffers', sga_info["Redo Buffers"], 'oracle_sga_redo_buffer'),
 113             ('Java Pool Size', sga_info["Java Pool Size"], 'oracle_sga_java_pool'),
 114             ('Large Pool Size', sga_info["Large Pool Size"], 'oracle_sga_large_pool'),
 115             ('Streams Pool Size', sga_info["Streams Pool Size"], 'oracle_sga_streams_pool'),
 116         ]:
 117             yield 0, "%s: %s" % (what, get_bytes_human_readable(val))
 118             perfdata.append((perfvar, val))
 119
 120     # PDB is <SID>.<PDB>
 121     # ignore more perf-data for PDBs except CDBROOT!
 122     if '.' in item and '.CDB$ROOT' not in item:
 123
 124         # PDB does not support more performance data at the moment...
 125         infotexts.append("limited performance data for PDBSEED and non CDBROOT")
 126         yield 0, ", ".join(infotexts), perfdata
 127         return
 128
 129     if "buffer_pool_statistics" in data and "DEFAULT" in data["buffer_pool_statistics"]:
 130         buffer_pool_stats = data["buffer_pool_statistics"]
 131         db_block_gets, db_block_change, consistent_gets, physical_reads, \
 132             physical_writes, free_buffer_wait, buffer_busy_wait = \
 133             buffer_pool_stats["DEFAULT"]
 134
 135         for what, val in [('oracle_db_block_gets', db_block_gets),
 136                           ('oracle_db_block_change', db_block_change),
 137                           ('oracle_consistent_gets', consistent_gets),
 138                           ('oracle_physical_reads', physical_reads),
 139                           ('oracle_physical_writes', physical_writes),
 140                           ('oracle_free_buffer_wait', free_buffer_wait),
 141                           ('oracle_buffer_busy_wait', buffer_busy_wait)]:
 142             rate = get_rate("oracle_perf.%s.buffer_pool_statistics.%s" % (item, what), now, val)
 143             perfdata.append((what, rate))
 144
 145         if db_block_gets + consistent_gets > 0:
 146             hit_ratio = (1 - (float(physical_reads) /
 147                               (float(db_block_gets) + float(consistent_gets)))) * 100
 148             yield 0, "Buffer hit ratio: %.1f%%" % hit_ratio, \
 149                   [('oracle_buffer_hit_ratio', hit_ratio)]
 150
 151     if "librarycache" in data:
 152         pins_sum = 0
 153         pin_hits_sum = 0
 154         for what, vals in data["librarycache"].items():
 155             _gets, _gethits, pins, pin_hits, _reloads, _invalidations = vals
 156             pins_sum += pins
 157             pin_hits_sum += pin_hits
 158
 159         for what, val in [("oracle_pins_sum", pins_sum), ("oracle_pin_hits_sum", pin_hits_sum)]:
 160             rate = get_rate("oracle_perf.%s.librarycache.%s" % (item, what), now, val)
 161             perfdata.append((what, rate))
 162
 163         if pins_sum > 0:
 164             pin_ratio = float(pin_hits_sum) / pins_sum * 100
 165             yield 0, "Library cache hit ratio: %.1f%%" % pin_ratio, \
 166                   [('oracle_library_cache_hit_ratio', pin_ratio)]
 167
 168     yield 0, ", ".join(infotexts), perfdata
 169     return
 170
 171
 172 check_info['oracle_performance'] = {
 173     "parse_function": parse_oracle_performance,
 174     "inventory_function": inventory_oracle_performance,
 175     "check_function": check_oracle_performance,
 176     "service_description": "ORA %s Performance",
 177     "has_perfdata": True,
 178 }