Symptom

check_oracle_health bietet vier Check-Modi zur Überwachung von User-Objekten:

[/home/nie/projects/check_oracle_health/check_oracle_health-1.6.7/plugins-scripts]
[nie@bubuntu]$ ./check_oracle_health | grep top10
 seg-top10-logical-reads     (user objects among top 10 logical reads)
 seg-top10-physical-reads    (user objects among top 10 physical reads)
 seg-top10-buffer-busy-waits (user objects among top 10 buffer busy waits)
 seg-top10-row-lock-waits    (user objects among top 10 row lock waits)

Auf meinen Datenbanken führen diese Checks regelmäßig zu einem Timeout und sind daher nicht nutzbar.

Beispiel:

OMD[mysite]:~$ /opt/omd/versions/0.44/lib/nagios/plugins/check_oracle_health \
               --connect=//bigbox:1521/nie1.world \
               --username=nagios --password=geheim \
               --mode=seg-top10-physical-reads
UNKNOWN - check_oracle_health timed out after 60 seconds

timeout bei check_oracle_health --mode seg-top10-busy-waits (ohne Patch)

kein timeout mehr bei check_oracle_health --mode seg-top10-busy-waits (mit Patch)

Patch

Der Patch check_oracle_health-top10.patch.tgz kann für check_oracle_health V 1.6.6.1 (in OMD 0.44 enthalten) und auch für die aktuelle Version (1.6.7) verwendet werden. Dies ist kein offizieller Patch.

Einspielen des Patches

$ tar xzf check_oracle_health-top10.patch.tgz
$ patch –backup check_oracle_health < check_oracle_health-top10.patch

Was macht der Patch?

Die verwendeten Statistiken aus v$segstat sind:

WHERE s.statistic_name IN
(‘physical reads’, ‘logical reads’, ‘buffer busy waits’, ‘row lock waits’)

Der jeweilige statistic_name wird als Platzhalter (?) an die unten stehenden Statements übergeben.

Original Statement

In der Originalversion holt sich check_oracle_health die betroffenen Statistiken aus v$segstat, filtert Systemuser heraus und ermittelt die Top10.

        SELECT DO.owner, DO.object_name, DO.object_type, SS.VALUE,
            SS.statistic_name
            FROM dba_objects DO, v$segstat SS
            WHERE DO.object_id = SS.obj#
            AND statistic_name = ?

Jede Statistik hat (auf meiner DB) über 4000 Einträge. Diese müssen alle mit der Tabelle dba_objects verbunden (join) und auf den Client transferiert werden. Dies führt dann zum timeout.

Statement nach Patch

Die Abfrage kann nur für Oracle-Datenbanken >= 10.x angewendet werden, da die Analytic-Function rank() over() verwendet wird (eine Oracle Spezialität).

SELECT DO.owner,
       DO.object_name,
       DO.object_type,
       SS.VALUE,
       SS.statistic_name
  FROM dba_objects DO,
       (SELECT *
          FROM (SELECT S.OBJ#,
                       s.VALUE,
                       s.statistic_name,
                       RANK () OVER (ORDER BY s.VALUE DESC) rk
                  FROM v$segstat s
                 WHERE s.statistic_name = ?
                       /* reduce data to significant values */
                       AND VALUE <>; 0)
         WHERE rk <= 10   /* top 10 */
                       ) SS
 WHERE DO.object_id = SS.obj#

Das Statement ermittelt zuerst die Top10 aus v$segstat (10 Werte von über 4000) und macht dann den Join auf dba_objects.
Es müssen nur 10 Rows an den Client zurückgegeben werden.

Bedeutung der Alarme

Die Meldung “WARNING: 3 user processes aong the top 10 busy waits” ist etwas irreführend.
Tatsächlich handelt es sich um User-Objekte also z.B. Tabellen und Indices.

Die Fehlermeldung in Nagios ist nicht aussagekräftig genug, da sie nur eine Zahl ausgibt.
Oracle-Administratoren können mit diesem Statement Klarheit über die betroffenen Objekte bekommen.
Das Statement ermittelt gleich alle vier Werte
… OVER (PARTITION BY s.statistic_name
ORDER BY s.VALUE DESC).

-- analysis: which objects are
 --               affected by check_oracle_health -mode seg-top10-XXXX ?
 --
  SELECT DO.owner,
	 DO.object_name,
	 DO.object_type,
	 SS.VALUE,
	 SS.statistic_name
    FROM dba_objects DO,
	 (SELECT *
	    FROM (SELECT S.OBJ#,
			 s.VALUE,
			 s.statistic_name,
			 RANK ()
			 OVER (PARTITION BY s.statistic_name
			       ORDER BY s.VALUE DESC)
				 rk
		    FROM v$segstat s
		   WHERE s.statistic_name IN
				 ('physical reads',
				  'logical reads',
				  'buffer busy waits',
				  'row lock waits')
			 /* reduce data to significant values */
			 AND VALUE <> 0)
	   WHERE rk <= 10	/* top 10 */
			 ) SS
   WHERE DO.object_id = SS.obj#
	 AND owner NOT IN    /* Systemuser herausfiltern */
		     ('CTXSYS',
		      'DBSNMP',
		      'MDDATA',
		      'MDSYS',
		      'DMSYS',
		      'OLAPSYS',
		      'ORDPLUGINS',
		      'ORDSYS',
		      'OUTLN',
		      'SI_INFORMTN_SCHEMA',
		      'SYS',
		      'SYSMAN',
		      'SYSTEM')
ORDER BY statistic_name, VALUE DESC;

Verfügbare Statistiken in v$segstat (oracle10)

select distinct statistic_name from v$segstat order by 1;

STATISTIC_NAME
buffer busy waits
db block changes
gc buffer busy
gc cr blocks received
gc current blocks received
ITL waits
logical reads
physical reads
physical reads direct
physical writes
physical writes direct
row lock waits
segment scans
space allocated
space used

Fazit

Bei mir funktioniert der Patch mit Oracle 10er Datenbanken. Ich bitte um Rückmeldungen.

Links

• check_oracle_health-top10.patch.tgz (inoffizieller) Patch für check_oracle_health auf dieser Seite
• check_oracle_health Hompage auf labs.consol.de

Weiterführende Links zu v$segstat

• Ask Tom: How to find object I/O contention
• STATISTICS_LEVEL in Oracle 10gR2 wie werden die Statistiken ein-/ausgeschaltet?
• Dynamic Performance Views eine Auflistung
• Instance Tuning Using Performance Views Oracle Tuning Guide (10.1)

Update 26.05.2011: Patch aufgenommen

Der hier vorgeschlagene Patch wurde in check_oracle_health 1.6.8 aufgenommen (siehe ChangeLog) und ist z.B. auch in OMD 0.48 enthalten. Selbst einspielen also nicht mehr nötig .