一种利用Oracle脚本自动生成盘活方案的方法

萧 颖

（中国电信股份有限公司广州分公司网络资源中心 广州 510630）

1 引言

2012年，广州电信的窄带设备继续维持“零投资”，在大力推进光网建设的同时，不再建设铜缆设备，但铜缆接入尤其是铜缆ADSL因其价格较低及覆盖较广，仍然是未来一段时间内中国电信的主营产品，这就要求盘活工作的重心由支撑建设，转向做好板卡资源的盘活调配以解决铜缆待装需求。

[1]提出了一种以业务需求为导向的网络资源盘活法，阐述了如何快速得出一台节点机可以盘活几块板，但却没有说明接下来如何快速选择具体哪几块板，而这正是盘活方案的具体内容。在实际生产中，资源盘活岗位在人工制作方案时需进入综合资源管理—管线子系统（以下简称GIS）中人工制作板卡盘活方案。在盘活节点机众多时，此项工作将耗费资源分析人员大量的精力和时间。而此项工作多是重复机械劳动，且盘活规则是统一的，这就使其实现自动化成为必要和可能。

目前三大运营商之间市场竞争异常激烈，提升客户感知成为服务的重中之重。盘活多数情况会伴随割接，而割接引起故障，直接影响用户感知，甚至引发投诉，这就要求应尽可能选择割接量少的板卡进行盘活，从根本上减少割接量，以降低割接成本，同时在源头上降低用户投诉的概率，但人工制作的盘活方案，难以实现割接量最小。

鉴于GIS中的数据均通过Oracle数据库进行管理，本文将在参考文献[1]所述盘活原则的基础上，以AD节点机板卡盘活为例，提出一种利用Oracle脚本自动生成盘活方案的方法，不仅极大地提高了资源盘活岗位的工作，也使盘活割接量达到最小，从而节约了盘活割接所带来的经济成本，进而从源头上降低了盘活割接带来的投诉风险。

2 Oracle脚本编写思路

2.1 数学基础

在参考文献[1]中，假设某一AD节点的总端口数为Z，目前占用端口数为A，空闲端口数为B，相应机型单位板卡线数为Q，盘活后的端口空闲率为C，则可盘活端口数X满足：

即最大盘活线数为：

广州电信经过多年盘活，大部分节点机占用率都较高，难以达到盘活后的空闲率，实际生产中常以预留y数量的端口作为式（2）的替代，即：

按进行相应机型单位板卡线数进行取整，得到最终可盘活板卡数：

以式（3）和式（4）为盘活板卡数量的依据，设计一个Oracle脚本，以实现自动生成盘活方案。

2.2 脚本编写

如图1所示，整个脚本分为盘活估算模块、板卡选择模块和方案生成模块，按顺序执行。其中盘活估算模块根据式（3）和式（4）估算出全网每台AD节点机可盘活板卡的数量；板卡选择模块则根据估算模块得出的数量，选择端口占用总数最少的板卡组合；方案生成模块则根据板卡选择模块的结果，生成最终的盘活方案表格。

图1 脚本编写生成流程

2.2.1 盘活估算模块

盘活估算模块的基本思路是利用脚本统计全网各台AD节点机的空闲端口数，在预留一定量后，得出可盘活端口数，再根据这台节点机的单位板卡端口数进行取整，最终得到全网每台节点机可盘活板卡数。

首先创建临时表panhuo_count，即：

create table panhuo_1 as

select[]from rm_gz.dev_ad_info R

其中rm_gz.dev_ad_info是GIS上所有广州AD节点机的信息表，接下来设计[]里的脚本。本模块的作用是计算该AD节点机在预留一定数量空闲端口后可以盘活板卡数量，因此临时表panhuo_1必须包含AD节点机的ID和编码，即AD_ID、AD_CODE以及各种状态的端口统计：

(select count(A.portid)from rm_gz.dev_ad_port A

where A.PORT_STAT=’100’and A.AD_ID=R.AD_ID)as空闲端口数

其中rm_gz.dev_ad_port是GIS上所有广州AD端口的信息表，将上面这条脚本复制并更改A.PORT_STAT=‘101’里面101这个值，即可统计其他状态的端口总数，如101为占用端口，209为预留端口，201为预占端口等。同时计算可盘活线数：

((select floor(((select count(A.portid)from rm_gz.dev_ad_port A

where A.PORT_STAT=’100’andA.AD_ID=R.AD_ID)-100))

/(decode((R.BOX_TYPE),‘ISAM7302’，48,139,48,123,48,122,64,101,32,116,32,121,32,141,32,136,32,113,32,127,64,10 000)))from dual))as可盘活板卡数

注意以上脚本中select count语句就是空闲端口数，‘-100’即为预留100个空闲端口后可盘活的端口数，再由decode语句依据该节点机单位用户板的端口数进行取整，就得出该AD节点机可盘活板卡数。将以上脚本完成并执行，完成后就可以进入板卡选择模块。

2.2.2 板卡选择模块

板卡选择模块的基本思路就是将每台节点机的板卡，按照各自的占用端口数从少到多进行排序并编号，然后根据盘活估算模块得到的可盘活板卡的数量，取这个排序的前几位，存入临时表，得到板卡选择方案。具体来说，脚本的设计将按以下步骤进行。

步骤1将所有AD板卡先按所属节点机升序排序，再按端口占用数升序排序。建立临时表panhuo_selection_1：

create table panhuo_selection_1 as

select*from

(select frunit_id,frunit_code,own_devid,

(select count(A.PORTID)from rm_gz.dev_ad_port A where A.PORT_STAT in(201,101)and A.FRUNIT_ID=B.FRUNIT_ID)as该板卡占用端口总数，

row_number()over(partition by own_devid order by(select count(A.PORTID)

from rm_gz.dev_ad_port A where A.PORT_STAT in(201,101)and A.FRUNIT_ID=B.FRUNIT_ID))rn

from rm_gz.dev_com_frame_unit B,rm_gz.dev_ad_info R

where B.OWN_DEVID=R.AD_ID

and B.FRUNIT_STAT in(100,101,105))

where rn<1000

其中rm_gz.dev_com_frame_unit是GIS上所有广州电信AD板卡的信息表。执行完后，在网所有正常在用的AD板卡，都会先统计其占用端口数，按照各自所属的AD节点机进行分组，然后在组内再根据其占用端口的数量从少到多进行升序排序并产生序号，完成后暂存在临时表panhuo_selection_1内。

步骤2根据盘活估算模块得到的每台AD节点机可盘活板卡数n，取每个节点机分组里面前n行，建立临时表panhuo_selection_2：

create table panhuo_selection_2 as

select

c.frunit_id,

c.frunit_code,

c.own_devid,

c.占用端口总数,

c.rn,

from panhuo_selection_1 c

where((select(可盘活板卡数)

from panhuo_count

where c.own_devid=panhuo_count.ad_id)-rn)>=0

执行完后，临时表panhuo_selection_1中的全网AD板卡，将以AD节点机为分组得到筛选，只剩下占用端口数最少的板卡，且数量等于该节点机可盘活的板卡数，完成后暂存在临时表panhuo_selection_2内。

步骤3由于前面步骤2所产生的临时表，仍然是以板卡为维度的盘活方案，有必要对其进行整合，形成以AD节点机为维度的板卡选择方案，这也是生成最终盘活方案的要求。建立临时表panhuo_selection_3：

create table panhuo_selection_3 as

select

own_devid,

wm_concat(frunit_code)板卡选择方案,

sum(占用端口总数)方案占用端口总数

from panhuo_selection_2

group by own_devid

至此，已经根据每台AD节点机的可盘活板卡数，选择了该节点机占用数最少的相应数量板卡，并计算了占用的总数，这个占用总数将成为最终方案中的预计割接数。接下来将由方案生成模块自动完成全网AD节点机的标准盘活方案。

2.2.3 方案生成模块

方案生成模块的基本思路就是将板卡选择模块得到的板卡选择结果，再加上该AD所属区域、所属局站，合并输出为标准的盘活方案，仅用select语句完成，即：

select

(select REGIONNAME from rm_gz.bas_region

where rm_gz.dev_ad_info.REGIONID=rm_gz.bas_redion.

REGIONID)所属区域，

(select STNAME from rm_gz.bas_station

where rm_gz.dev_ad_info.STATIONID=rm_gz.bas_station.

FACID)所属局站,

AD_CODE AD节点机编码，

（select板卡选择方案from panhuo_selection_3

where rm_gz.dev_ad_info.AD_ID=panhuo_selection_3.own_devid）板卡盘活方案，

（select方案占用端口总数from panhuo_selection_3

where rm_gz.dev_ad_info.AD_ID=panhuo_selection_3.own_devid）预计割接数

from rm_gz.dev_ad_info

执行完毕后，将自动输出标准格式的全网AD节点机板卡盘活方案，如表1所示。

3 实践与分析

自2012年广州电信第二批盘活工作启动，就已经开始使用本文所述方法写成的脚本，对全网可盘活的19 658台AD节点机进行盘活分析，并输出标准盘活方案。

图2是手工制作与自动生成一条盘活方案的平均耗时对比，可以看到自动生成在生产效率上的提升非常明显。这是因为脚本运行时间稳定为37 min，平均每条方案耗时为0.11 s。而以往盘活工作人员，手工制作一条标准盘活方案的平均耗时为10 min，是自动生成方案的5 400余倍。

图2 制作一条盘活方案的平均耗时

图3 是人工制作和自动生成在盘活割接比例上的对比。盘活割接比例的定义为当批盘活割接线数除以盘活出来的线数，它展示了为了盘活1线资源，平均需割接的线数。其中人工制作的比例以2011年全年盘活割接线数与盘活线数作为统计，自动生成以2012年第二批的盘活割接线数和盘活线数作为统计。从图中可以看出，自动生成方案的盘活割接比例为4%（即盘活1线所需平均割接线数为0.04线），远远小于人工的制作方案的57%（即盘活1线所需平均割接线数为0.57线），这就意味着即使盘活线数的总量相同，自动生成方案的割接量也会远远小于手工制作。这是因为脚本输出的标准盘活方案一定是割接量最少的方案，而人工受限于耗时太长，在实际生产中不可能把每台节点机的每块板卡上面的占用数记录并比较，因此方案带来的割接量是不可控的。

图3 手工制作与自动生成方案的盘活割接比例

4 结束语

针对以往人工制作盘活方案耗时长、效率低且方案割接量不可控等缺点，本文提出了一种利用Oracle脚本自动生成盘活方案的方法，首先利用脚本估算每台AD节点机的可盘活板卡数量n，然后将每台AD节点机的板卡按照它们各自的占用数由少到多进行排序，并选择前n块板卡，最后在板卡选择的基础上整合AD节点机的所属区域和所属局站，自动生成最终的板卡盘活方案。实践表明，自动生成方案不仅效率远远高于手工制作，且割接量实现了最小化，不仅直接减少了割接的经济成本，也从源头上减少了割接可能带来的用户投诉成本。

本文所述方法并不需要进行系统功能开发，也不需要特殊的软硬件支持，只需一台装有Oracle 9以上版本的PC即可完成，对于需要进一步推进盘活工作的区域，具有较高的可移植性和可行性。

参考文献

1 萧颖，林拥胜，李毅.一种以业务需求为导向的网络资源盘活法.电信科学，2012（5A）

2 Morton K.Oracle SQL高级编程.朱浩波译.北京：人民邮电出版社，2011

3 Feuerstein S,Pribyl B.Oracle PL/SQL程序设计（第5版）.张晓明译.北京：人民邮电出版社，2011