Knowledge Base >> Configuring Ignite for Oracle to Collect SQL_ID

Configuring Ignite for Oracle to Collect SQL_ID - Confio Article 1609

Confio Article: 1609 Published: Aug 26 2011 9:50 AM
Version: 4.0+ Topic(s): Install and Setup Wait Time Analysis Database Problems Ignite Management Ignite Features
Product(s): Ignite
Database(s): Oracle

 

Introduction

 

Many of our Ignite for Oracle customers use other tools that rely on the SQL_ID value to identify a SQL statement. Ignite does not collect it by default, but a few tweaks to the backend can make this easily possible.

When Ignite executes the query to collect active session data, it queries the X$KSUSE table which is actually a synonym in the Ignite schema that points to the SYS.X_$KSUSE view. This synonym can be replaced with a view that substitutes the SQL_ID value for another piece of data Ignite collects. For example, many customers have replaced the MODULE or ACTION data with SQL_ID.

Notes: the following steps assume the SQL_ID data will replace the MODULE data. If you would like to replace another piece of data, email support@confio.com.

 

Configuration Steps


Ignite can be configured to collect the SQL_ID value by doing the following - perform these steps on each monitored database:

 

  1. Stop the monitor for this database.
  2. Run these SQL statements while logged in as a DBA user on the monitored database.
  3. Start the monitor for this database.
  4. After a couple minutes, drill into the Ignite data and ensure the Module tab shows the SQL_ID value for your SQL statements.

 

Screenshot Showing SQL_ID Values in Module Tab

 

Tags: oracle SQL module action sql_id id oem grid control