雲貝教育 |【技術文章】service stats monitor

作者：劉曉峰

原文連結：http://www.tdpub.cn/Blog/detail/id/1360.html

配置多個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$service_event s--有值，不需要設定