1. 首页
  2. IT资讯

oracle10g rac_dbms_service_dba_service系列视图学习笔记

—dbms_service与dba_services_v$service的学习
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

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code