1,通过dbms_service可以灵活定制service一些指标，更适应业务的需要
2,dba_services的信息要比v$service更为丰富，多了taf方面
dtp
Declares the service to be for DTP or distributed transactions including XA transactions —对于这个dtp也仅止于概念而已，变更它们的值true/false有本质区别
由于对plsql语法掌握不牢固，处理方法如下：
SQL> exec dbms_service.modify_service(service_name => ‘service2′,failover_method=>’0’);
begin dbms_service.modify_service(service_name => ‘service2′,failover_method=>’0’); end;
ORA-44316: invalid argument
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_SERVICE”, line 394
ORA-06512: at “SYS.DBMS_SERVICE”, line 280
ORA-06512: at line 2
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_method=>dbms_service.failover_method_none);—
PL/SQL procedure successfully completed
附上dbms_service.mofify_service的测试笔记,供后期参考与深入掌握
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_method=>dbms_service.failover_method_none);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 NONE SERVICE_TIME Y NO NO LONG
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_method=>dbms_service.failover_method_basic);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 BASIC SERVICE_TIME Y NO NO LONG
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_method=>dbms_service.failover_method_none);
PL/SQL procedure successfully completed
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_type=>dbms_service.failover_type_none);
PL/SQL procedure successfully completed
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_type=>dbms_service.failover_type_select);
PL/SQL procedure successfully completed
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_type=>dbms_service.failover_type_session);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 SESSION SERVICE_TIME Y NO NO LONG
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_retries=>10);
PL/SQL procedure successfully completed
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_delay=>3);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 3 SERVICE_TIME Y NO NO LONG
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_delay=>20);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 20 SERVICE_TIME Y NO NO LONG
SQL> exec dbms_service.modify_service(service_name => ‘service2’,failover_retries=>29);
PL/SQL procedure successfully completed
SQL> select * from dba_services where name=’service2′;
SERVICE_ID NAME NAME_HASH NETWORK_NAME CREATION_DATE CREATION_DATE_HASH FAILOVER_METHOD FAILOVER_TYPE FAILOVER_RETRIES FAILOVER_DELAY MIN_CARDINALITY MAX_CARDINALITY GOAL DTP ENABLED AQ_HA_NOTIFICATIONS CLB_GOAL
———- —————————————————————- ———- ——————————————————————————– ————- —————— —————————————————————- —————————————————————- —————- ————– ————— ————— ———— — ——- ——————- ——–
8 service2 1180431924 service2 2010-4-11 上午 589942238 29 SERVICE_TIME Y NO NO LONG
SQL>
—以上测试发现，retries与delay不能同时配置，也就是这两个参数可能是互斥的；理解oracle底层有个限制，如果配置不合理，可能就不会生效
dbms_service其它 子过程同理，不再测试
分析与调整service一些视图
V$SERVICEMETRIC
–根据每5秒和1分钟时间对service不同的度量值进行采样
V$SERVICEMETRIC displays metric values measured on the most recent time interval period for services executing inside the database.
Service metrics are measured in 5-second and 1-minute intervals.
Column Datatype Description
BEGIN_TIME DATE Begin timestamp for the interval period –采样间隔开始时间
END_TIME DATE End timestamp for the interval period –采样间隔结束时间
INTSIZE_CSEC NUMBER Interval size (in hundredths of a second) —采样间隔时间大小
GROUP_ID NUMBER Group ID for the service metric group —服务度量组的group id,同类型的service进行分组管理
SERVICE_NAME_HASH NUMBER Service name hash –服务名hash,oracle好多视图采用了如何机制，比如v$session,v$sqlarea and etc
SERVICE_NAME VARCHAR2(64) Service name –服务名
CTMHASH NUMBER Service create timestamp hash value—服务创建时间的hash值
ELAPSEDPERCALL NUMBER Elapsed time per call (in microseconds). This column is deprecated in favor of the DBTIMEPERCALL column.—此列已不用，被dbtimepercall列替换;
—-每个用户调用花费的时间
CPUPERCALL NUMBER CPU time per call (in microseconds)—每个调用花费的cpu time
DBTIMEPERCALL NUMBER Elapsed time per call (in microseconds)—每个调用花费的时间(花费时间>cpu time)
CALLSPERSEC NUMBER Number of user calls per second —每秒内发生多少次用户调用
DBTIMEPERSEC NUMBER DB time per second —每秒产生的db time 是多少
—友好度，从字面可以这样译；这个值越低越好；oracle根据goal(long及short，请参考dbms_service章节）的不同值，对不同的service，从内部计算它的值。通俗来讲，就是不同的服务在处理工作负荷时，对于各个rac实例的吸引力;我也没有测试，理解不深
GOODNESS NUMBER Indicates how attractive a given instance is with respect to processing the workload that is presented to the service. A lower number is better.
This number is internally computed based on the GOAL (LONG or SHORT) that is specified for the particular service.
—这个值表明，每个新增的会话接入到某个rac实含例的可以预侧到的增长数
DELTA NUMBER Indicates the predicted increase in the goodness for every additional session that is routed to this instance
—是个列表类型的列
FLAGS NUMBER Can be any of the following values:
*
0x01 – service is BLOCKED from accepting new connections —服务接受新的连接时，可能正处于阻塞
*
0x02 – service is VIOLATING the set threshold on some metric —服务违反某些度量上面的阀值
*
0x04 – goodness is UNKNOWN —服务目前的状态为未知
—以下含义与以上差不多，区别在于取样是最近一段时间的，记录数更多，便于分析service的运行趋势
V$SERVICEMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY displays a recent history of the metric values measured in predefined time interval periods for services executing inside the database. Service metrics are measured in 5-second and 1-minute intervals.
Column Datatype Description
BEGIN_TIME DATE Begin timestamp for the interval period
END_TIME DATE End timestamp for the interval period
INTSIZE_CSEC NUMBER Interval size (in hundredths of a second)
GROUP_ID NUMBER Group ID for the service metric group
SERVICE_NAME_HASH NUMBER Service name hash
SERVICE_NAME VARCHAR2(64) Service name
CTMHASH NUMBER Service create timestamp hash value
ELAPSEDPERCALL NUMBER Elapsed time per call (in microseconds). This column is deprecated in favor of the DBTIMEPERCALL column.
CPUPERCALL NUMBER CPU time per call (in microseconds)
DBTIMEPERCALL NUMBER Elapsed time per call (in microseconds)
CALLSPERSEC NUMBER Number of user calls per second
DBTIMEPERSEC NUMBER DB time per second
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-659620/，如需转载，请注明出处，否则将追究法律责任。
主题测试文章，只做测试使用。发布者：℅傍ㄖ免沦陷dε鬼，转转请注明出处：http://www.cxybcw.com/192790.html
微信扫一扫 
支付宝扫一扫 
