Skip to main content

Oracle New

info

Oracle New uses SmartAgent + Integration Agent to collect Oracle metrics and DBM data for SQL performance, execution-plan, active-session, blocking, and service-correlation analysis.

1. Support Scope​

Supported database versions:​

Oracle 11g (11.2.0.1.0) / 19c (19.3.0.0.0) / 21c (21.3.0.0.0)

Note: Other database versions are theoretically supported but must be verified by the user.

Supported system architectures:​

Linux x86_64 (amd64), requires glibc ≥ 2.17. Verified versions: CentOS 7, CentOS 8, RHEL 8.2

Note: Other system versions are theoretically supported but must be verified by the user.

Supported agent versions:​

SmartAgent 10.3.0+

2. Deployment​

Install the SmartAgent probe and enable integration-agent.

3. Oracle-side Configuration: User and Privileges​

3.1 Create the bonree user​

'bonree' / 'c##bonree': the read-only user for the plugin probe to access the database; <UNIQUEPASSWORD>: the database access password you set.

3.1.1 Multi-tenant (CDB + PDB)​

Multi-tenant:

Log in as sysdba to the CDB root container and run:

CREATE USER c##bonree IDENTIFIED BY "<UNIQUEPASSWORD>" CONTAINER = ALL;
ALTER USER c##bonree SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

3.1.2 Non-CDB / Oracle 11g​

Non-multi-tenant or under Oracle 11g:

Log in as sysdba and run:

CREATE USER bonree IDENTIFIED BY "<UNIQUEPASSWORD>";

Check that access works:

# Always quote the password if it contains special characters
sqlplus bonree/"<UNIQUEPASSWORD>"@<HOST>:1521/<SERVICE_NAME>
SELECT user FROM dual;
SELECT name, open_mode FROM v$database;  -- Non-CDB
-- or
SELECT name, open_mode FROM v$pdbs;      -- CDB/PDB

3.2 Basic privileges​

Log in as sysdba and run the corresponding GRANTs for your architecture.

3.2.1 Multi-tenant (c##bonree)​

GRANT CREATE SESSION TO c##bonree;

GRANT SELECT ON v_$session TO c##bonree;
GRANT SELECT ON v_$database TO c##bonree;
GRANT SELECT ON v_$containers TO c##bonree;
GRANT SELECT ON v_$sqlstats TO c##bonree;
GRANT SELECT ON v_$instance TO c##bonree;
GRANT SELECT ON dba_feature_usage_statistics TO c##bonree;
GRANT SELECT ON v_$sql_plan_statistics_all TO c##bonree;
GRANT SELECT ON v_$process TO c##bonree;
GRANT SELECT ON v_$con_sysmetric TO c##bonree;
GRANT SELECT ON cdb_tablespace_usage_metrics TO c##bonree;
GRANT SELECT ON cdb_tablespaces TO c##bonree;
GRANT SELECT ON v_$sqlcommand TO c##bonree;
GRANT SELECT ON v_$datafile TO c##bonree;
GRANT SELECT ON v_$sysmetric TO c##bonree;
GRANT SELECT ON v_$sgainfo TO c##bonree;
GRANT SELECT ON v_$pdbs TO c##bonree;
GRANT SELECT ON cdb_services TO c##bonree;
GRANT SELECT ON v_$osstat TO c##bonree;
GRANT SELECT ON v_$parameter TO c##bonree;
GRANT SELECT ON v_$sql TO c##bonree;
GRANT SELECT ON v_$pgastat TO c##bonree;
GRANT SELECT ON v_$asm_diskgroup TO c##bonree;
GRANT SELECT ON v_$rsrcmgrmetric TO c##bonree;
GRANT SELECT ON v_$dataguard_config TO c##bonree;
GRANT SELECT ON v_$dataguard_stats TO c##bonree;
GRANT SELECT ON v_$transaction TO c##bonree;
GRANT SELECT ON v_$locked_object TO c##bonree;
GRANT SELECT ON v_$lock TO c##bonree;
GRANT SELECT ON gv_$lock TO c##bonree;
GRANT SELECT ON dba_objects TO c##bonree;
GRANT SELECT ON cdb_data_files TO c##bonree;
GRANT SELECT ON dba_data_files TO c##bonree;

-- Execution-plan object_owner collection (recommended for DBM)
GRANT SELECT ON v_$sql_plan TO c##bonree;

If custom queries need to run on a PDB, grant additionally:

ALTER SESSION SET CONTAINER = <YOUR_PDB>;
GRANT SET CONTAINER TO c##bonree;

If some data still cannot be collected after granting, add the following:

GRANT EXECUTE ON sys.dbms_lob TO c##bonree;

3.2.2 Non-CDB (bonree)​

GRANT CREATE SESSION TO bonree;

GRANT SELECT ON v_$session TO bonree;
GRANT SELECT ON v_$database TO bonree;
GRANT SELECT ON v_$containers TO bonree;
GRANT SELECT ON v_$sqlstats TO bonree;
GRANT SELECT ON v_$instance TO bonree;
GRANT SELECT ON dba_feature_usage_statistics TO bonree;
GRANT SELECT ON v_$sql_plan_statistics_all TO bonree;
GRANT SELECT ON v_$process TO bonree;
GRANT SELECT ON v_$con_sysmetric TO bonree;
GRANT SELECT ON cdb_tablespace_usage_metrics TO bonree;
GRANT SELECT ON cdb_tablespaces TO bonree;
GRANT SELECT ON v_$sqlcommand TO bonree;
GRANT SELECT ON v_$datafile TO bonree;
GRANT SELECT ON v_$sysmetric TO bonree;
GRANT SELECT ON v_$sgainfo TO bonree;
GRANT SELECT ON v_$pdbs TO bonree;
GRANT SELECT ON cdb_services TO bonree;
GRANT SELECT ON v_$osstat TO bonree;
GRANT SELECT ON v_$parameter TO bonree;
GRANT SELECT ON v_$sql TO bonree;
GRANT SELECT ON v_$pgastat TO bonree;
GRANT SELECT ON v_$asm_diskgroup TO bonree;
GRANT SELECT ON v_$rsrcmgrmetric TO bonree;
GRANT SELECT ON v_$dataguard_config TO bonree;
GRANT SELECT ON v_$dataguard_stats TO bonree;
GRANT SELECT ON v_$transaction TO bonree;
GRANT SELECT ON v_$locked_object TO bonree;
GRANT SELECT ON v_$lock TO bonree;
GRANT SELECT ON gv_$lock TO bonree;
GRANT SELECT ON dba_objects TO bonree;
GRANT SELECT ON cdb_data_files TO bonree;
GRANT SELECT ON dba_data_files TO bonree;

GRANT SELECT ON v_$sql_plan TO bonree;

-- If some data still cannot be collected after granting, add as needed
-- GRANT EXECUTE ON sys.dbms_lob TO bonree;

3.2.3 Oracle 11g (bonree)​

Oracle 11g has no CDB/PDB-related views.

grant create session to bonree ;
grant select on v_$session to bonree ;
grant select on v_$database to bonree ;
grant select on v_$sqlstats to bonree ;
grant select on v_$instance to bonree ;
grant select on dba_feature_usage_statistics to bonree ;
grant select on V_$PROCESS to bonree ;
grant select on V_$SQLCOMMAND to bonree ;
grant select on V_$DATAFILE to bonree ;
grant select on V_$SYSMETRIC to bonree ;
grant select on V_$SGAINFO to bonree ;
grant select on V_$OSSTAT to bonree ;
grant select on V_$PARAMETER to bonree ;
grant select on V_$SQL_PLAN_STATISTICS_ALL to bonree ;
grant select on V_$SQL to bonree ;
grant select on V_$PGASTAT to bonree ;
grant select on dba_tablespace_usage_metrics to bonree ;
grant select on dba_tablespaces to bonree ;
grant select on v_$asm_diskgroup to bonree ;
grant select on v_$rsrcmgrmetric to bonree ;
grant select on v_$dataguard_config to bonree ;
grant select on v_$dataguard_stats to bonree ;
grant select on v_$lock to bonree ;
grant select on gv_$lock to bonree ;

3.3 DBM Activity Sampling: the bonree_session View​

After DBM is enabled, the Agent performs activity sampling (query samples) through the sys.bonree_session view by default. Create the view in the corresponding container as sysdba and grant access.

Multi-tenant:

Note: When run as sysdba, the view is created under the sys schema.

CREATE OR REPLACE VIEW bonree_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
  s.indx as sid,
  s.ksuseser as serial#,
  s.ksuudlna as username,
  DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
  s.ksuseunm as osuser,
  s.ksusepid as process,
  s.ksusemnm as machine,
  s.ksusemnp as port,
  s.ksusepnm as program,
  DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
  s.ksusesqi as sql_id,
  sq.force_matching_signature as force_matching_signature,
  s.ksusesph as sql_plan_hash_value,
  s.ksusesesta as sql_exec_start,
  s.ksusesql as sql_address,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
  s.ksusepsi as prev_sql_id,
  s.ksusepha as prev_sql_plan_hash_value,
  s.ksusepesta as prev_sql_exec_start,
  sq_prev.force_matching_signature as prev_force_matching_signature,
  s.ksusepsq as prev_sql_address,
  s.ksuseapp as module,
    s.ksuseact as action,
    s.ksusecli as client_info,
    s.ksuseltm as logon_time,
    s.ksuseclid as client_identifier,
    s.ksusstmbv as op_flags,
    decode(s.ksuseblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
        'VALID'
    ) as blocking_session_status,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
    ) as blocking_instance,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
    ) as blocking_session,
    DECODE(s.ksusefblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
    ) as final_blocking_session_status,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
    ) as final_blocking_instance,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
    ) as final_blocking_session,
    DECODE(w.kslwtinwait,
        1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
        decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
    ) as STATE,
    e.kslednam as event,
    e.ksledclass as wait_class,
    w.kslwtstime as wait_time_micro,
    c.name as pdb_name,
    sq.sql_text as sql_text,
    sq.sql_fulltext as sql_fulltext,
    sq_prev.sql_fulltext as prev_sql_fulltext,
    comm.command_name
FROM
  x$ksuse s,
  x$kslwt w,
  x$ksled e,
  v$sql sq,
  v$sql sq_prev,
  v$containers c,
  v$sqlcommand comm
WHERE
  BITAND(s.ksspaflg, 1) != 0
  AND BITAND(s.ksuseflg, 1) != 0
  AND s.inst_id = USERENV('Instance')
  AND s.indx = w.kslwtsid
  AND w.kslwtevt = e.indx
  AND s.ksusesqi = sq.sql_id(+)
  AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
  AND s.ksusepsi = sq_prev.sql_id(+)
  AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
  AND s.con_id = c.con_id(+)
  AND s.ksuudoct = comm.command_type(+)
;

-- After creating the bonree_session view in the CDB root container as sysdba:
GRANT SELECT ON bonree_session TO c##bonree;

Non-CDB:

Note: When run as sysdba, the view is created under the sys schema; the Agent queries sys.bonree_session by default.

CREATE OR REPLACE VIEW bonree_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
  s.indx as sid,
  s.ksuseser as serial#,
  s.ksuudlna as username,
  DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
  s.ksuseunm as osuser,
  s.ksusepid as process,
  s.ksusemnm as machine,
  s.ksusemnp as port,
  s.ksusepnm as program,
  DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
  s.ksusesqi as sql_id,
  sq.force_matching_signature as force_matching_signature,
  s.ksusesph as sql_plan_hash_value,
  s.ksusesesta as sql_exec_start,
  s.ksusesql as sql_address,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
  s.ksusepsi as prev_sql_id,
  s.ksusepha as prev_sql_plan_hash_value,
  s.ksusepesta as prev_sql_exec_start,
  sq_prev.force_matching_signature as prev_force_matching_signature,
  s.ksusepsq as prev_sql_address,
  s.ksuseapp as module,
    s.ksuseact as action,
    s.ksusecli as client_info,
    s.ksuseltm as logon_time,
    s.ksuseclid as client_identifier,
    s.ksusstmbv as op_flags,
    decode(s.ksuseblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
        'VALID'
    ) as blocking_session_status,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
    ) as blocking_instance,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
    ) as blocking_session,
    DECODE(s.ksusefblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
    ) as final_blocking_session_status,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
    ) as final_blocking_instance,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
    ) as final_blocking_session,
    DECODE(w.kslwtinwait,
        1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
        decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
    ) as STATE,
    e.kslednam as event,
    e.ksledclass as wait_class,
    w.kslwtstime as wait_time_micro,
    c.name as pdb_name,
    sq.sql_text as sql_text,
    sq.sql_fulltext as sql_fulltext,
    sq_prev.sql_fulltext as prev_sql_fulltext,
    comm.command_name
FROM
  x$ksuse s,
  x$kslwt w,
  x$ksled e,
  v$sql sq,
  v$sql sq_prev,
  v$containers c,
  v$sqlcommand comm
WHERE
  BITAND(s.ksspaflg, 1) != 0
  AND BITAND(s.ksuseflg, 1) != 0
  AND s.inst_id = USERENV('Instance')
  AND s.indx = w.kslwtsid
  AND w.kslwtevt = e.indx
  AND s.ksusesqi = sq.sql_id(+)
  AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
  AND s.ksusepsi = sq_prev.sql_id(+)
  AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
  AND s.con_id = c.con_id(+)
  AND s.ksuudoct = comm.command_type(+)
;

-- After running the bonree_session view creation script as sysdba in the CDB root container:
GRANT SELECT ON bonree_session TO c##bonree;

Oracle 11g:

Note: When run as sysdba, the view is created under the sys schema; the Agent queries sys.bonree_session by default.

CREATE OR REPLACE VIEW bonree_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
  s.indx as sid,
  s.ksuseser as serial#,
  s.ksuudlna as username,
  DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
  s.ksuseunm as osuser,
  s.ksusepid as process,
  s.ksusemnm as machine,
  s.ksusemnp as port,
  s.ksusepnm as program,
  DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
  s.ksusesqi as sql_id,
  sq.force_matching_signature as force_matching_signature,
  s.ksusesph as sql_plan_hash_value,
  s.ksusesesta as sql_exec_start,
  s.ksusesql as sql_address,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
  CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
  s.ksusepsi as prev_sql_id,
  s.ksusepha as prev_sql_plan_hash_value,
  s.ksusepesta as prev_sql_exec_start,
  sq_prev.force_matching_signature as prev_force_matching_signature,
  s.ksusepsq as prev_sql_address,
  s.ksuseapp as module,
    s.ksuseact as action,
    s.ksusecli as client_info,
    s.ksuseltm as logon_time,
    s.ksuseclid as client_identifier,
    s.ksusstmbv as op_flags,
    decode(s.ksuseblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
        'VALID'
    ) as blocking_session_status,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
    ) as blocking_instance,
    DECODE(s.ksuseblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
        4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
    ) as blocking_session,
    DECODE(s.ksusefblocker,
        4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
    ) as final_blocking_session_status,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
    ) as final_blocking_instance,
    DECODE(s.ksusefblocker,
        4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
        4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
    ) as final_blocking_session,
    DECODE(w.kslwtinwait,
        1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
        decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
    ) as STATE,
    e.kslednam as event,
    e.ksledclass as wait_class,
    w.kslwtstime as wait_time_micro,
    sq.sql_text as sql_text,
    sq.sql_fulltext as sql_fulltext,
    sq_prev.sql_fulltext as prev_sql_fulltext,
    comm.command_name
FROM
  x$ksuse s,
  x$kslwt w,
  x$ksled e,
  v$sql sq,
  v$sql sq_prev,
  v$sqlcommand comm
WHERE
  BITAND(s.ksspaflg, 1) != 0
  AND BITAND(s.ksuseflg, 1) != 0
  AND s.inst_id = USERENV('Instance')
  AND s.indx = w.kslwtsid
  AND w.kslwtevt = e.indx
  AND s.ksusesqi = sq.sql_id(+)
  AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
  AND s.ksusepsi = sq_prev.sql_id(+)
  AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
  AND s.ksuudoct = comm.command_type(+)
;

GRANT SELECT ON bonree_session TO bonree ;

3.5 Self-check script​

sqlplus bonree/"<UNIQUEPASSWORD>"@<HOST>:1521/<SERVICE_NAME> <<'SQL'
SELECT user FROM dual;
SELECT sql_id, object_owner, object_name FROM v$sql_plan WHERE ROWNUM <= 3;
SELECT COUNT(*) AS bonree_session_accessible FROM bonree_session WHERE ROWNUM <= 1;
EXIT;
SQL

Expected output:

  • SELECT user returns BONREE or C##BONREE.
  • v$sql_plan can return object_owner / object_name (execution-plan privileges are correct).
  • bonree_session is accessible (if the view has not been created, ORA-00942 is raised).

4. Agent-side Configuration: oracle.d/conf.yaml​

4.1 Minimal configuration​

OSPath
Linux${APM_HOME}/integration/conf/integration.d/oracle.d/conf.yaml
instances:
  - server: 127.0.0.1:1521        # Database host IP or domain:port; default port 1521
    service_name: <SERVICE_NAME>  # Required when connecting via server (e.g. XE, ORCL); to find available service names: SELECT value FROM v$parameter WHERE name='service_names'
    username: bonree              # Probe username; must match the database side
    password: '<PASSWORD>'        # Quote the password if it contains special characters such as @
    dbm: false                    # true: enable database performance monitoring; false: disable; default false
    # tags:
    #   - cluster:cluster_name     # Custom cluster tag to attach when reporting data
  # Multiple instances can be configured, e.g.:
  # - server: 10.1.1.1:1521
  #   service_name: ORCL
  #   username: c##bonree      # Multi-tenant CDB scenario
  #   password: '<PASSWORD>'
  #   dbm: true
  #   tags:
  #     - cluster:prod-oracle-cdb

4.3 Applying and Verifying the Configuration​

The configuration takes effect hot. If a change does not take effect, restart SmartAgent (run the bash command: systemctl restart bonree-agent).

5. Service and Trace Correlation​

When an application service (e.g. a Java service) accesses a monitored database, you can enable different propagation modes on the Bonree ONE platform, turning SQL-comment injection on/off to associate or disassociate services or traces.

Note: Correlation currently supports only Java services monitored by the Java probe; services monitored by other probes will be supported later.

5.1 Configuration steps​

Go to the Bonree ONE platform home page -> Deployment Configuration -> Rule Configuration -> Data Collection -> Database -> Create (create an end-to-end correlation rule) -> Select service scope -> Select database type -> Select propagation mode (i.e. correlation mode; default is Off; options are Off, Full, and Service).

5.2 Propagation mode descriptions​

  • Off mode: No comment is injected; database activity cannot be traced back to upstream services or traces.
  • Service mode: Only service-level information is injected into the SQL comment, providing basic correlation analysis; trace correlation is not supported.
  • Full mode: Complete service and Trace information is injected; database activity can be traced back to upstream services, traces, and related information.

5.3 Notes​

  • Correlation prerequisite: Not all statements can be correlated. Correlation information can only be collected once the SQL comment has been successfully written into the database performance tables (e.g. long-running or blocking statements).
  • Performance impact: The SQL comment itself usually adds little overhead to network transmission, parsing, and CPU; however, Full mode may affect the execution-plan cache (e.g. cache misses, hard parses), so enable it with caution.
  • Prepared Statement injection: Applies to PostgreSQL only. When enabled, Trace information is carried via the connection-level ApplicationName before each PreparedStatement execution. This may cause extra session-attribute updates and network round-trips, increasing execution latency in high-frequency SQL scenarios; evaluate performance before enabling.
  • Comment position: For special compatibility in complex environments; by default the comment is injected before the SQL.
  • For Oracle versions earlier than 12.2.0.1, or for Oracle versions 12.2.0.1 and later where LONG_MODULE_ACTION=FALSE, traceparent injection fails due to inherent length limitations; therefore, Oracle version 12.2.0.1 or later with LONG_MODULE_ACTION=TRUE is required.