【技术文章】service stats monitor

云贝教育 2023-04-19

265

原文链接：TDPUB数加社区

配置多个service,监控每个连接服务的负载

1.查询当前服务名

select * from v$services
SYS$BACKGROUND  
SYS$USERS  
PROD_PRIMARY  PROD_PRIMARY
DUPDB_CFG  DUPDB_CFG


SQL> show parameter service_names
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      PROD_PRIMARY


select * from v$parameter p where p.name='service_names'

2.添加一个服务

alter system set service_names='PROD_PRIMARY','PROD_PRIMARY_01'

添加服务后会自动创建动态监听

Service "PROD_PRIMARY_01" has 1 instance(s).
  Instance "DUPDB", status READY, has 1 handler(s) for this service..

如果要配置静态监听

xxxx =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MyHost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_xxxx =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD_PRIMARY_01)
      (ORACLE_HOME =/u01)
      (SID_NAME =DUPDB)
      (ENVS="TNS_ADMIN=/u01/network/admin")
    )
  )

3.修改tnsnames--可以用一个端口，不会报错

PRIMARY_01 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = MyHost)(Port = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = PROD_PRIMARY_01)
 )
)

4.确认连接

[oracle@MyHost ~]$ sqlplus sys/manager@PRIMARY_01 as sysdba

5.查询会话以及服务信息

select * from v$session s where s.service_name='PROD_PRIMARY_01';
select * from v$services s where s.name='PROD_PRIMARY_01'


select * from v$services

6.开关跟踪

begin
dbms_monitor.serv_mod_act_stat_disable(service_name => 'PROD_PRIMARY_01',module_name => 'test02');
end;
begin
dbms_monitor.serv_mod_act_stat_enable(service_name => 'PROD_PRIMARY_01',module_name => 'test02');
end;

7.设置当前会话标识

这个有点坑，必须在sqplus中执行，而不能在begin end中执行,否则返回的value都是0，那这个意义就不是很大了，暂时没找到应用场景

execute dbms_application_info.set_action('test_insert1');--单独设置动作

8.执行一些sql，在7的会话sqlplus中执行

select sum(-1*object_id) from dba_objects;

9.查询服务统计信息

select * from v$serv_mod_act_stats s where s.service_name not in ('SYS$BACKGROUND','SYS$USERS');--在匿名块里面都是0，必须在sqlplus中执行
SELECT * from v$service_stats s--有值，不需要设置7
select * from v$serv
ice_event s--有值，不需要设置