跳到主要内容

Oracle New

信息

Oracle New 使用 SmartAgent + Integration Agent 采集 Oracle 指标及 DBM 数据，支持分析 SQL 性能、执行计划、活动会话、阻塞关系以及数据库与应用服务的关联。

快速上手​

  1. 安装 SmartAgent 10.3.0 或更高版本，并启用 Integration Agent。
  2. 根据数据库架构选择 CDB/PDB、Non-CDB 或 Oracle 11g 的用户创建与授权方式。
  3. 如需采集 DBM 活动样本，以 sysdba 创建 bonree_session 视图并授予监控用户查询权限。
  4. 编辑 ${APM_HOME}/integration/conf/integration.d/oracle.d/conf.yaml，填写连接信息并将 dbm 设置为 true。
  5. 运行自检命令确认用户、执行计划和活动视图均可访问，然后进入 基础设施 → 数据库 查看实例及 Query 数据。
注意

此页面介绍新的 Integration Agent 接入方式。现有 Oracle Legacy 使用 SmartGate，部署与配置流程不同，请勿混用。

1. 支持范围​

数据库支持版本：​

Oracle 11g（11.2.0.1.0）/ 19c（19.3.0.0.0）/ 21c（21.3.0.0.0）

注：其他数据库版本理论上支持，需要自行验证。

支持系统架构：​

Linux x86_64（amd64），需glibc ≥2.17；已验证版本：CentOS 7、CentOS 8、RHEL8.2

注：其他系统版本理论上支持，需要自行验证

探针支持版本：​

SmartAgent 10.3.0+

2. 部署​

安装 SmartAgent 探针，并开启 integration-agent

3. Oracle 端配置：用户与权限​

3.1 创建 bonree 用户​

'bonree' / 'c##bonree'：用于插件探针访问数据库的只读用户；<UNIQUEPASSWORD>：设置的数据库访问密码。

3.1.1 Multi-tenant（CDB + PDB）​

多租户：

以 sysdba 登录 CDB 根容器执行：

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​

非多租户或oracle11下：

以 sysdba 登录执行：

CREATE USER bonree IDENTIFIED BY "<UNIQUEPASSWORD>";

检查是否可以访问：

# 密码含特殊字符时务必加引号
sqlplus bonree/"<UNIQUEPASSWORD>"@<HOST>:1521/<SERVICE_NAME>
SELECT user FROM dual;
SELECT name, open_mode FROM v$database;  -- Non-CDB
-- 或
SELECT name, open_mode FROM v$pdbs;      -- CDB/PDB

3.2 基础授权​

以 sysdba 登录，按架构执行对应 GRANT。

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;

-- 执行计划 object_owner 采集（DBM 推荐）
GRANT SELECT ON v_$sql_plan TO c##bonree;

若自定义查询需在 PDB 上执行，额外授权：

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

若授权后仍无法采集部分数据，可补充下面配置：

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;

-- 授权后仍无法采集部分数据时，按需补充
-- GRANT EXECUTE ON sys.dbms_lob TO bonree;

3.2.3 Oracle 11g（bonree）​

oracle 11g 无 CDB/PDB 相关视图

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 活动采样：bonree_session 视图​

开启 DBM 后，Agent 默认通过 sys.bonree_session 视图做活动采样（query samples）。以 sysdba 在对应容器创建视图并授权。

Multi-tenant：

注： 以 sysdba 执行，视图将创建在 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(+)
;

-- 在 CDB 根容器以 sysdba 执行 bonree_session 视图创建脚本后：
GRANT SELECT ON bonree_session TO c##bonree;

Non-CDB：

注： 以 sysdba 执行，视图将创建在 sys schema 下，Agent 默认查询 sys.bonree_session

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(+)
;

-- 创建 bonree_session 视图后：
GRANT SELECT ON bonree_session TO bonree;

Oracle 11g：

注： 以 sysdba 执行，视图将创建在 sys schema 下，Agent 默认查询 sys.bonree_session

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.4 自检脚本​

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

期望输出：

  • SELECT user 返回 BONREE 或 C##BONREE。
  • v$sql_plan 能查出 object_owner / object_name（执行计划权限正常）。
  • bonree_session 可访问（未创建视图时会报 ORA-00942）。

4. Agent 端配置：oracle.d/conf.yaml​

4.1 最小配置​

OS路径
Linux${APM_HOME}/integration/conf/integration.d/oracle.d/conf.yaml
instances:
  - server: 127.0.0.1:1521        # 数据库主机 IP 或域名:端口，默认端口 1521
    service_name: <SERVICE_NAME>  # 使用 server 连接时必填（如 XE、ORCL）；查可用服务名：SELECT value FROM v$parameter WHERE name='service_names'
    username: bonree              # 探针用户名，需与数据库端一致
    password: '<PASSWORD>'        # 密码含 @ 等特殊字符时建议加引号
    dbm: false                    # true：开启数据库性能监控，false：关闭，默认 false
    # tags:
    #   - cluster:cluster_name     # 上报数据时携带的自定义集群标签
  # 可配置多个实例，例如：
  # - server: 10.1.1.1:1521
  #   service_name: ORCL
  #   username: c##bonree      # Multi-tenant CDB 场景
  #   password: '<PASSWORD>'
  #   dbm: true
  #   tags:
  #     - cluster:prod-oracle-cdb

4.2 配置生效与验证​

配置热生效，修改后如未生效，可重启 SmartAgent（执行bash命令：systemctl restart bonree-agent）。

常见场景​

接入 CDB/PDB 架构的 Oracle 数据库

在 CDB 根容器创建公共监控用户 c##bonree，设置容器数据访问范围，完成 Multi-tenant 授权，并在正确容器创建活动采样视图。

需要分析活动会话与阻塞关系

创建并授权 bonree_session 视图，将 Agent 实例的 dbm 设置为 true，再通过自检确认视图可访问。

需要将 SQL 关联到应用服务或调用链

在 ONE 平台创建数据库端到端关联规则，根据分析深度选择 Service 或 Full 传播模式，并在业务低峰期评估 Full 模式的性能影响。

5. 服务、调用链关联​

应用服务（比如java服务）访问受监控的数据库时，可以在Bonree ONE平台开启不同传播模式，开启/关闭注入SQL注释达到服务或调用链的关联/取消关联。

注：当前关联仅支持受java探针监控的java服务，后续会支持其他探针监控的服务

5.1 配置步骤​

进入Bonree ONE平台首页->部署配置->规则配置->数据采集->数据库->创建（创建端到端关联规则）->选择服务范围->选择数据库类型->选择传播模式（即关联模式，默认是关闭，可选关闭、Full、Service三种模式）

5.2 传播模式说明​

  • 关闭模式：代表不注入任何注释，数据库功能无法追溯到上游服务以及调用链。
  • Service模式：代表仅在SQL注释中注入服务级信息，提供基础的关联分析能力，不支持关联调用链。
  • Full模式：代表注入完整服务和Trace信息，数据库功能可以追溯到上游服务以及调用链等相关信息。

5.3 注意事项​

  • 关联前提：并非所有语句都能关联。仅当 SQL 注释成功写入数据库性能表后，才能采集到关联信息（例如长耗时、阻塞类语句）。
  • 性能影响：SQL 注释本身对网络传输、解析与 CPU 开销通常较小；但 Full 模式可能影响执行计划缓存（如缓存不命中、硬解析），请谨慎开启。
  • Prepared Statement 注入：仅对 PostgreSQL 生效。开启后，每次 PreparedStatement 执行前都会通过连接级 ApplicationName 携带 Trace 信息。该操作可能产生额外的 session 属性更新和网络往返，在高频 SQL 场景下增加执行延迟，建议评估性能后开启。
  • 注释位置：用于复杂环境的特殊兼容；默认在 SQL 之前注入注释。
  • Oracle< 12.2.0.1及Oracel≥ 12.2.0.1且LONG_MODULE_ACTION=FALSE时，因为自身限制长度的问题，会导致traceparent注入不成功，需要Oracel≥ 12.2.0.1且LONG_MODULE_ACTION=True。