Difference between revisions of "Plugin:Oracle"

From collectd Wiki
Jump to: navigation, search
(Documented the license exception.)
(break long lines + more consistent use of upper/lower case)
Line 19: Line 19:
 
  <Plugin oracle>  
 
  <Plugin oracle>  
 
   <Query "out_of_stock">
 
   <Query "out_of_stock">
     Statement "SELECT category, COUNT(*) AS value FROM products WHERE in_stock = 0 GROUP BY category"
+
     Statement "SELECT category, COUNT(*) AS value \
 +
                    FROM products \
 +
                    WHERE in_stock = 0 \
 +
                    GROUP BY category"
 
     <Result>
 
     <Result>
 
       Type "gauge"  
 
       Type "gauge"  
Line 42: Line 45:
  
 
  <Query "io_per_tablespace">
 
  <Query "io_per_tablespace">
   Statement "select sum(vf.PHYBLKRD)*8192 as PHY_BLK_R, sum(vf.PHYBLKWRT)*8192 as PHY_BLK_W, 'tablespace' AS i_prefix, dt.tablespace_name from ((dba_data_files dd join v$filestat vf on dd.file_id = vf.file# ) join dba_tablespaces dt on dd.tablespace_name = dt.tablespace_name) group by dt.tablespace_name"
+
   Statement "SELECT sum(vf.PHYBLKRD)*8192 AS PHY_BLK_R, \
 +
                    sum(vf.PHYBLKWRT)*8192 AS PHY_BLK_W, \
 +
                    'tablespace' AS i_prefix, \
 +
                    dt.tablespace_name \
 +
                  FROM ((dba_data_files dd JOIN v$filestat vf ON dd.file_id = vf.file# ) \
 +
                        JOIN dba_tablespaces dt ON dd.tablespace_name = dt.tablespace_name) \
 +
                  GROUP BY dt.tablespace_name"
 
   <Result>
 
   <Result>
 
     Type "io_octets"
 
     Type "io_octets"
Line 57: Line 66:
  
 
  <Query "db_efficientcy">
 
  <Query "db_efficientcy">
   Statement "select  round(sum(decode(METRIC_NAME, 'Database Wait Time Ratio', value)),2) as DATABASE_WAIT_TIME_RATIO, round(sum(decode(METRIC_NAME, 'Database CPU Time Ratio', value)),2) as DATABASE_CPU_TIME_RATIO, 'DB_EFFICIENCY' AS DB_EFFICIENCY from SYS.V_$SYSMETRIC where  METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC)"
+
   Statement "SELECT round(sum(decode(METRIC_NAME, 'Database Wait Time Ratio', value)),2) AS DATABASE_WAIT_TIME_RATIO, \
 +
                    round(sum(decode(METRIC_NAME, 'Database CPU Time Ratio', value)),2) AS DATABASE_CPU_TIME_RATIO, \
 +
                    'DB_EFFICIENCY' AS DB_EFFICIENCY \
 +
                  FROM SYS.V_$SYSMETRIC \
 +
                  WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') \
 +
                      AND INTSIZE_CSEC = (SELECT max(INTSIZE_CSEC) FROM SYS.V_$SYSMETRIC)"
 
   <Result>
 
   <Result>
 
     Type "disk_time"
 
     Type "disk_time"

Revision as of 12:39, 4 May 2009

Oracle plugin
Type: read
Status: supported
First version: 4.6
Copyright: 2008–2009 Florian octo Forster
License: GPLv2 (with exception)
Manpage: collectd.conf(5)
List of Plugins

Description

The Oracle interface uses the "Oracle call interface" (OCI) to connect to one or more Oracle database systems, execute SQL statements on them and read back the result. Depending on the configuration, the returned values are then converted into collectd "value lists" (the data structure used internally to pass statistics around). This plugin is a generic plugin, i.e. it cannot work without configuration, because there is no reasonable default behavior. Please read the Plugin oracle section of the collectd.conf(5) manual page for an in-depth description of the plugin's configuration.

The configuration syntax of the Oracle, DBI, and PostgreSQL plugins is very similar, because the configuration of those plugins is handled by the same module. Also, we tried to keep the syntax similar to that of the SNMP plugin. So if you use any of those plugins already, most of the following will look familiar.

Synopsis

<Plugin oracle> 
  <Query "out_of_stock">
    Statement "SELECT category, COUNT(*) AS value \
                   FROM products \
                   WHERE in_stock = 0 \
                   GROUP BY category"
    <Result>
      Type "gauge" 
      # InstancePrefix "foo"
      InstancesFrom "category"
      ValuesFrom "value" 
    </Result>
  </Query>
  <Database "product_information">
    ConnectID "db01"
    Username "oracle"
    Password "secret"
    Query "out_of_stock"
  </Database>
</Plugin>

Example graphs

IO per Tablespace

Based on the following query:

<Query "io_per_tablespace">
  Statement "SELECT sum(vf.PHYBLKRD)*8192 AS PHY_BLK_R, \
                    sum(vf.PHYBLKWRT)*8192 AS PHY_BLK_W, \
                    'tablespace' AS i_prefix, \
                    dt.tablespace_name \
                 FROM ((dba_data_files dd JOIN v$filestat vf ON dd.file_id = vf.file# ) \
                       JOIN dba_tablespaces dt ON dd.tablespace_name = dt.tablespace_name) \
                 GROUP BY dt.tablespace_name"
  <Result>
    Type "io_octets"
    InstancesFrom "i_prefix" "TABLESPACE_NAME"
    ValuesFrom "PHY_BLK_R" "PHY_BLK_W"
  </Result>
</Query>

Io per tbs.png

DB efficiency

Based on the following query:

<Query "db_efficientcy">
  Statement "SELECT round(sum(decode(METRIC_NAME, 'Database Wait Time Ratio', value)),2) AS DATABASE_WAIT_TIME_RATIO, \
                    round(sum(decode(METRIC_NAME, 'Database CPU Time Ratio', value)),2) AS DATABASE_CPU_TIME_RATIO, \
                    'DB_EFFICIENCY' AS DB_EFFICIENCY \
                 FROM SYS.V_$SYSMETRIC \
                 WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') \
                     AND INTSIZE_CSEC = (SELECT max(INTSIZE_CSEC) FROM SYS.V_$SYSMETRIC)"
  <Result>
    Type "disk_time"
    InstancesFrom "DB_EFFICIENCY"
    ValuesFrom "DATABASE_WAIT_TIME_RATIO" "DATABASE_CPU_TIME_RATIO"
  </Result>
</Query>

Db efficiency.png

License

Because the GPLv2 (the license of the Oracle plugin itself) and the “Oracle® Technology Network License” (OTN) are not compatible, an exception to the GPL has been added to the licensing terms of the Oracle plugin, allowing users to link against the “Oracle® Call Interface” (OCI).

The exception reads:

Linking src/oracle.c ("the oracle plugin") statically or dynamically with
other modules is making a combined work based on the oracle plugin. Thus,
the terms and conditions of the GNU General Public License cover the whole
combination.

In addition, as a special exception, the copyright holders of the oracle
plugin give you permission to combine the oracle plugin with free software
programs or libraries that are released under the GNU LGPL and with code
included in the standard release of the Oracle® Call Interface (OCI) under
the Oracle® Technology Network (OTN) License (or modified versions of such
code, with unchanged license). You may copy and distribute such a system
following the terms of the GNU GPL for the oracle plugin and the licenses of
the other code concerned.

Note that people who make modified versions of the oracle plugin are not
obligated to grant this special exception for their modified versions; it is
their choice whether to do so. The GNU General Public License gives
permission to release a modified version without this exception; this
exception also makes it possible to release a modified version which carries
forward this exception. However, without this exception the OTN License does
not allow linking with code licensed under the GNU General Public License.

Oracle® is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.

Dependencies

  • OCI
  • libclntsh
    (An Oracle library with a mystic name. Should be part of OCI.)
  • Suitable values for LD_LIBRARY_PATH and ORACLE_HOME have to be provided.