Skip to main content
Sumo Logic

Set Up Oracle Performance Metrics Script on Windows

This page has instructions for setting up the performance metrics script on Windows for the Oracle app. 

The script was developed and tested on:

  • Microsoft Windows Server 2012 R2 Base, Python (2.7.12), Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

About the performance metric script

The Sumo app for Oracle uses a Python script to  query database tables to collect Oracle server performance metrics.

The script connects the database using a database user account, runs a set of example SQL queries, and prints the query results in JSON format to the console. The account credentials and SQL queries are specified in the script configuration file, oracle-perf-monitor.cfg.

The SQL queries, in the [queries]  section of the configuration file are provided as examples. You can add, modify, and remove queries, as desired. We recommend having the queries you plan to use reviewed by your DBA.

The database user that you use to run the SQL queries should have permission to access and query specific databases, tables and views. The table below shows the SQL commands to grant the required permissions for each query.

Query  SQL Command to Grant Permissions
For Queries 1 through 3 SQL> grant select on sys.v_$tablespace to <username>;
SQL> grant select on sys.dba_free_space to <username>;
SQL> grant select on sys.v_$datafile to <username>;
SQL> grant select on v_$sysstat to <username>;
For Query 4 SQL> grant select on sys.v_$session to <username>;
SQL> grant select on sys.v_$process to <username>;
For Queries 5 and 6 SQL> grant select on sys.v_$session_wait to <username>;
SQL> grant select on sys.dba_jobs to <username>;

Step 1. Install Python 2.7.12 for Windows

  1. Download and  Python 2.7.12 for Windows from:
    https://www.python.org/downloads/release/python-2712/

  2. Install Python in the C:\Python27\ folder.

  3. Add C:\Python27\ to your Path system  variable.  
    Control Panel > Systems > Advanced System Settings > Advanced Tab > Environment Variable > System Variables > Path.

  4. To confirm that you have successfully installed Python 2.7.12 and added it to your path, open a new command window and run the following command:
    C:\Users\Administrator>python --version

    You should see this response:
    Python 2.7.12

Step 2. Install Oracle Instant Client for Windows 64bit 

  1. Download and setup Oracle Instant Client for Win 64bit.
    http://www.oracle.com/technetwork/topics/winx64soft-089540.html
    Instantclient-basic-windows.x64-12.2.0.1.0.zip
    Instantclient-sqlplus-windows.x64-12.2.0.1.0.zip
    Instantclient-sdk-windows.x64-12.2.0.1.0.zip

  2. Unzip the packages into a single directory such as C:\oracle\instantclient_12_2\

  3. Add this directory to the PATH environment variable. If you have multiple versions of Oracle libraries installed, make sure the new directory occurs first in the path.

  4. Download and install the correct Visual Studio Redistributable from Microsoft. Instant Client 12.2 requires the Visual Studio 2013 redistributable.

  5. Test the client
    sqlplus username/password@//databasehost:1521/sidvalue
    C:\Users\Administrator>sqlplus nitin/pande@//10.0.0.197:1521/NITINSID

    SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 25 17:13:20 2018

    Copyright (c) 1982, 2016, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    2.6) Run some sample SQL query to test the connection is working
    SQL> select * from v$version;

    BANNER
    --------------------------------------------------------------------------------

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    SQL>

Step 3. Install cx_Oracle on Windows

For related information, see http://cx-oracle.readthedocs.io/en/l...cle-on-windows.

  1. Use Python’s pip package to install cx_Oracle.
    python -m pip install cx_Oracle --upgrade

  2. Check the cx_Oracle version.
    c:\Application\sumo_OracleDB_Scripts>python
    Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit
    AMD64)] on win32
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import cx_Oracle
    >>> print cx_Oracle.version
    6.2.1
    >>>

Step 4. Set up performance metrics script

In this step you set up the performance metrics script.

  1. Download Sumo Python script and configuration files:
    oracle-perf-monitor.cfg  (https://s3.amazonaws.com/script-collection/oracle/r1.0.0/oracle-perf-monitor.cfg)
    oracle-perf-monitor.py (https://s3.amazonaws.com/script-collection/oracle/r1.0.0/oracle-perf-monitor.py)

  2. Copy oracle-perf-monitor.py to a folder, for example c:\Application\sumo_OracleDB_Scripts

  3. Make the following updates to the script configuration file (oracle-perf-monitor.cfg). In the [dbLogin] section, supply values for each parameter:
    [dbLogin]

  • oraUser= database user id             example myuser123
  • oraPassword= user password        example mypwd123
  • oraHost=server name                     example ip-101-25-17-22
  • oraPort=port number                      example 1521
  • oraInstance=oracle instance SID (SID_NAME)           example XE
  1. Test the script:
    c:\Application\sumo_OracleDB_Scripts>python oracle-perf-monitor.py

Step 5. Configure the script source 

  1. In Sumo Logic, go to Manage Data > Collection > Collection.

  2. Find the name of the installed collector to which you'd like to add a Source. Click Add... then choose Add Source from the pop-up menu.

  3. Select Script for the Source type.

  • For Name enter any name you like, for instance Oracle Server Script.
  • The Description is optional.
  • For Source Category, enter the desired category. It can be any value you like, for example  DB/Oracle/DBQueryScript.
  • For Frequency, select desired frequency, for instance 5 minutes.
  • For Specify a timeout for your command, select a value that is long enough that long-running queries can complete, for instance 30 sec.
  • For Command, select Windows Script.
  • For Script, select Type a path to the script to execute, then enter: for instance. C:\Application\sumo_OracleDB_Scripts\oracle-perf-monitor.py
  • For Working Directory, enter: for instance C:\Application\sumo_OracleDB_Scripts
  • Click Save.