Difference between revisions of "Plugin:Oracle"

Revision as of 22:22, 17 December 2011

The Oracle plugin 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>

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>

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.

Caveats

Native Language Support

collectd uses strtod and strto[u]ll to parse the strings returned from the database. If the database returns values with a different language setting, parsing may fail. You should therefore set the environment variable NLS_LANG to specify how floating point numbers are returned. For example by setting the appropriate environment variable in a file that is sourced by the init script:

 # /etc/default/collectd
 NLS_LANG=AMERICAN_AMERICA
 export NLS_LANG

Building with Oracle Instant Client

Oracle provides an "Instant Client" software package which provides all the necessary libraries and headers required by the Oracle plugin to build successfully.

For some reason Oracle chose to store the files in locations that differ from a regular installation of Oracle Client in these packages.

Here is how you get going

Install the following packages:

• oracle-instantclient11.2-basic
• oracle-instantclient11.2-devel

With super user privileges execute the following commands:

mkdir /usr/lib/oracle/11.2/client64/rdbms
ln -s /usr/include/oracle/11.2/client64 /usr/lib/oracle/11.2/client64/rdbms/public
export ORACLE_HOME=/usr/lib/oracle/11.2/client64

Now the plugin should build.