一種利用Oracle腳本自動(dòng)生成盤活方案的方法

蕭 穎

（中國(guó)電信股份有限公司廣州分公司網(wǎng)絡(luò)資源中心 廣州 510630）

1 引言

2012年，廣州電信的窄帶設(shè)備繼續(xù)維持“零投資”，在大力推進(jìn)光網(wǎng)建設(shè)的同時(shí)，不再建設(shè)銅纜設(shè)備，但銅纜接入尤其是銅纜ADSL因其價(jià)格較低及覆蓋較廣，仍然是未來(lái)一段時(shí)間內(nèi)中國(guó)電信的主營(yíng)產(chǎn)品，這就要求盤活工作的重心由支撐建設(shè)，轉(zhuǎn)向做好板卡資源的盤活調(diào)配以解決銅纜待裝需求。

[1]提出了一種以業(yè)務(wù)需求為導(dǎo)向的網(wǎng)絡(luò)資源盤活法，闡述了如何快速得出一臺(tái)節(jié)點(diǎn)機(jī)可以盤活幾塊板，但卻沒(méi)有說(shuō)明接下來(lái)如何快速選擇具體哪幾塊板，而這正是盤活方案的具體內(nèi)容。在實(shí)際生產(chǎn)中，資源盤活崗位在人工制作方案時(shí)需進(jìn)入綜合資源管理—管線子系統(tǒng)（以下簡(jiǎn)稱GIS）中人工制作板卡盤活方案。在盤活節(jié)點(diǎn)機(jī)眾多時(shí)，此項(xiàng)工作將耗費(fèi)資源分析人員大量的精力和時(shí)間。而此項(xiàng)工作多是重復(fù)機(jī)械勞動(dòng)，且盤活規(guī)則是統(tǒng)一的，這就使其實(shí)現(xiàn)自動(dòng)化成為必要和可能。

目前三大運(yùn)營(yíng)商之間市場(chǎng)競(jìng)爭(zhēng)異常激烈，提升客戶感知成為服務(wù)的重中之重。盤活多數(shù)情況會(huì)伴隨割接，而割接引起故障，直接影響用戶感知，甚至引發(fā)投訴，這就要求應(yīng)盡可能選擇割接量少的板卡進(jìn)行盤活，從根本上減少割接量，以降低割接成本，同時(shí)在源頭上降低用戶投訴的概率，但人工制作的盤活方案，難以實(shí)現(xiàn)割接量最小。

鑒于GIS中的數(shù)據(jù)均通過(guò)Oracle數(shù)據(jù)庫(kù)進(jìn)行管理，本文將在參考文獻(xiàn)[1]所述盤活原則的基礎(chǔ)上，以AD節(jié)點(diǎn)機(jī)板卡盤活為例，提出一種利用Oracle腳本自動(dòng)生成盤活方案的方法，不僅極大地提高了資源盤活崗位的工作，也使盤活割接量達(dá)到最小，從而節(jié)約了盤活割接所帶來(lái)的經(jīng)濟(jì)成本，進(jìn)而從源頭上降低了盤活割接帶來(lái)的投訴風(fēng)險(xiǎn)。

2 Oracle腳本編寫思路

2.1 數(shù)學(xué)基礎(chǔ)

在參考文獻(xiàn)[1]中，假設(shè)某一AD節(jié)點(diǎn)的總端口數(shù)為Z，目前占用端口數(shù)為A，空閑端口數(shù)為B，相應(yīng)機(jī)型單位板卡線數(shù)為Q，盤活后的端口空閑率為C，則可盤活端口數(shù)X滿足：

即最大盤活線數(shù)為：

廣州電信經(jīng)過(guò)多年盤活，大部分節(jié)點(diǎn)機(jī)占用率都較高，難以達(dá)到盤活后的空閑率，實(shí)際生產(chǎn)中常以預(yù)留y數(shù)量的端口作為式（2）的替代，即：

按進(jìn)行相應(yīng)機(jī)型單位板卡線數(shù)進(jìn)行取整，得到最終可盤活板卡數(shù)：

以式（3）和式（4）為盤活板卡數(shù)量的依據(jù)，設(shè)計(jì)一個(gè)Oracle腳本，以實(shí)現(xiàn)自動(dòng)生成盤活方案。

2.2 腳本編寫

如圖1所示，整個(gè)腳本分為盤活估算模塊、板卡選擇模塊和方案生成模塊，按順序執(zhí)行。其中盤活估算模塊根據(jù)式（3）和式（4）估算出全網(wǎng)每臺(tái)AD節(jié)點(diǎn)機(jī)可盤活板卡的數(shù)量；板卡選擇模塊則根據(jù)估算模塊得出的數(shù)量，選擇端口占用總數(shù)最少的板卡組合；方案生成模塊則根據(jù)板卡選擇模塊的結(jié)果，生成最終的盤活方案表格。

圖1 腳本編寫生成流程

2.2.1 盤活估算模塊

盤活估算模塊的基本思路是利用腳本統(tǒng)計(jì)全網(wǎng)各臺(tái)AD節(jié)點(diǎn)機(jī)的空閑端口數(shù)，在預(yù)留一定量后，得出可盤活端口數(shù)，再根據(jù)這臺(tái)節(jié)點(diǎn)機(jī)的單位板卡端口數(shù)進(jìn)行取整，最終得到全網(wǎng)每臺(tái)節(jié)點(diǎn)機(jī)可盤活板卡數(shù)。

首先創(chuàng)建臨時(shí)表panhuo_count，即：

create table panhuo_1 as

select[]from rm_gz.dev_ad_info R

其中rm_gz.dev_ad_info是GIS上所有廣州AD節(jié)點(diǎn)機(jī)的信息表，接下來(lái)設(shè)計(jì)[]里的腳本。本模塊的作用是計(jì)算該AD節(jié)點(diǎn)機(jī)在預(yù)留一定數(shù)量空閑端口后可以盤活板卡數(shù)量，因此臨時(shí)表panhuo_1必須包含AD節(jié)點(diǎn)機(jī)的ID和編碼，即AD_ID、AD_CODE以及各種狀態(tài)的端口統(tǒng)計(jì)：

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

where A.PORT_STAT=’100’and A.AD_ID=R.AD_ID)as空閑端口數(shù)

其中rm_gz.dev_ad_port是GIS上所有廣州AD端口的信息表，將上面這條腳本復(fù)制并更改A.PORT_STAT=‘101’里面101這個(gè)值，即可統(tǒng)計(jì)其他狀態(tài)的端口總數(shù)，如101為占用端口，209為預(yù)留端口，201為預(yù)占端口等。同時(shí)計(jì)算可盤活線數(shù)：

((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可盤活板卡數(shù)

注意以上腳本中select count語(yǔ)句就是空閑端口數(shù)，‘-100’即為預(yù)留100個(gè)空閑端口后可盤活的端口數(shù)，再由decode語(yǔ)句依據(jù)該節(jié)點(diǎn)機(jī)單位用戶板的端口數(shù)進(jìn)行取整，就得出該AD節(jié)點(diǎn)機(jī)可盤活板卡數(shù)。將以上腳本完成并執(zhí)行，完成后就可以進(jìn)入板卡選擇模塊。

2.2.2 板卡選擇模塊

板卡選擇模塊的基本思路就是將每臺(tái)節(jié)點(diǎn)機(jī)的板卡，按照各自的占用端口數(shù)從少到多進(jìn)行排序并編號(hào)，然后根據(jù)盤活估算模塊得到的可盤活板卡的數(shù)量，取這個(gè)排序的前幾位，存入臨時(shí)表，得到板卡選擇方案。具體來(lái)說(shuō)，腳本的設(shè)計(jì)將按以下步驟進(jìn)行。

步驟1將所有AD板卡先按所屬節(jié)點(diǎn)機(jī)升序排序，再按端口占用數(shù)升序排序。建立臨時(shí)表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該板卡占用端口總數(shù)，

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板卡的信息表。執(zhí)行完后，在網(wǎng)所有正常在用的AD板卡，都會(huì)先統(tǒng)計(jì)其占用端口數(shù)，按照各自所屬的AD節(jié)點(diǎn)機(jī)進(jìn)行分組，然后在組內(nèi)再根據(jù)其占用端口的數(shù)量從少到多進(jìn)行升序排序并產(chǎn)生序號(hào)，完成后暫存在臨時(shí)表panhuo_selection_1內(nèi)。

步驟2根據(jù)盤活估算模塊得到的每臺(tái)AD節(jié)點(diǎn)機(jī)可盤活板卡數(shù)n，取每個(gè)節(jié)點(diǎn)機(jī)分組里面前n行，建立臨時(shí)表panhuo_selection_2：

create table panhuo_selection_2 as

select

c.frunit_id,

c.frunit_code,

c.own_devid,

c.占用端口總數(shù),

c.rn,

from panhuo_selection_1 c

where((select(可盤活板卡數(shù))

from panhuo_count

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

執(zhí)行完后，臨時(shí)表panhuo_selection_1中的全網(wǎng)AD板卡，將以AD節(jié)點(diǎn)機(jī)為分組得到篩選，只剩下占用端口數(shù)最少的板卡，且數(shù)量等于該節(jié)點(diǎn)機(jī)可盤活的板卡數(shù)，完成后暫存在臨時(shí)表panhuo_selection_2內(nèi)。

步驟3由于前面步驟2所產(chǎn)生的臨時(shí)表，仍然是以板卡為維度的盤活方案，有必要對(duì)其進(jìn)行整合，形成以AD節(jié)點(diǎn)機(jī)為維度的板卡選擇方案，這也是生成最終盤活方案的要求。建立臨時(shí)表panhuo_selection_3：

create table panhuo_selection_3 as

select

own_devid,

wm_concat(frunit_code)板卡選擇方案,

sum(占用端口總數(shù))方案占用端口總數(shù)

from panhuo_selection_2

group by own_devid

至此，已經(jīng)根據(jù)每臺(tái)AD節(jié)點(diǎn)機(jī)的可盤活板卡數(shù)，選擇了該節(jié)點(diǎn)機(jī)占用數(shù)最少的相應(yīng)數(shù)量板卡，并計(jì)算了占用的總數(shù)，這個(gè)占用總數(shù)將成為最終方案中的預(yù)計(jì)割接數(shù)。接下來(lái)將由方案生成模塊自動(dòng)完成全網(wǎng)AD節(jié)點(diǎn)機(jī)的標(biāo)準(zhǔn)盤活方案。

2.2.3 方案生成模塊

方案生成模塊的基本思路就是將板卡選擇模塊得到的板卡選擇結(jié)果，再加上該AD所屬區(qū)域、所屬局站，合并輸出為標(biāo)準(zhǔn)的盤活方案，僅用select語(yǔ)句完成，即：

select

(select REGIONNAME from rm_gz.bas_region

where rm_gz.dev_ad_info.REGIONID=rm_gz.bas_redion.

REGIONID)所屬區(qū)域，

(select STNAME from rm_gz.bas_station

where rm_gz.dev_ad_info.STATIONID=rm_gz.bas_station.

FACID)所屬局站,

AD_CODE AD節(jié)點(diǎn)機(jī)編碼，

（select板卡選擇方案from panhuo_selection_3

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

（select方案占用端口總數(shù)from panhuo_selection_3

where rm_gz.dev_ad_info.AD_ID=panhuo_selection_3.own_devid）預(yù)計(jì)割接數(shù)

from rm_gz.dev_ad_info

執(zhí)行完畢后，將自動(dòng)輸出標(biāo)準(zhǔn)格式的全網(wǎng)AD節(jié)點(diǎn)機(jī)板卡盤活方案，如表1所示。

3 實(shí)踐與分析

自2012年廣州電信第二批盤活工作啟動(dòng)，就已經(jīng)開(kāi)始使用本文所述方法寫成的腳本，對(duì)全網(wǎng)可盤活的19 658臺(tái)AD節(jié)點(diǎn)機(jī)進(jìn)行盤活分析，并輸出標(biāo)準(zhǔn)盤活方案。

圖2是手工制作與自動(dòng)生成一條盤活方案的平均耗時(shí)對(duì)比，可以看到自動(dòng)生成在生產(chǎn)效率上的提升非常明顯。這是因?yàn)槟_本運(yùn)行時(shí)間穩(wěn)定為37 min，平均每條方案耗時(shí)為0.11 s。而以往盤活工作人員，手工制作一條標(biāo)準(zhǔn)盤活方案的平均耗時(shí)為10 min，是自動(dòng)生成方案的5 400余倍。

圖2 制作一條盤活方案的平均耗時(shí)

圖3 是人工制作和自動(dòng)生成在盤活割接比例上的對(duì)比。盤活割接比例的定義為當(dāng)批盤活割接線數(shù)除以盤活出來(lái)的線數(shù)，它展示了為了盤活1線資源，平均需割接的線數(shù)。其中人工制作的比例以2011年全年盤活割接線數(shù)與盤活線數(shù)作為統(tǒng)計(jì)，自動(dòng)生成以2012年第二批的盤活割接線數(shù)和盤活線數(shù)作為統(tǒng)計(jì)。從圖中可以看出，自動(dòng)生成方案的盤活割接比例為4%（即盤活1線所需平均割接線數(shù)為0.04線），遠(yuǎn)遠(yuǎn)小于人工的制作方案的57%（即盤活1線所需平均割接線數(shù)為0.57線），這就意味著即使盤活線數(shù)的總量相同，自動(dòng)生成方案的割接量也會(huì)遠(yuǎn)遠(yuǎn)小于手工制作。這是因?yàn)槟_本輸出的標(biāo)準(zhǔn)盤活方案一定是割接量最少的方案，而人工受限于耗時(shí)太長(zhǎng)，在實(shí)際生產(chǎn)中不可能把每臺(tái)節(jié)點(diǎn)機(jī)的每塊板卡上面的占用數(shù)記錄并比較，因此方案帶來(lái)的割接量是不可控的。

圖3 手工制作與自動(dòng)生成方案的盤活割接比例

4 結(jié)束語(yǔ)

針對(duì)以往人工制作盤活方案耗時(shí)長(zhǎng)、效率低且方案割接量不可控等缺點(diǎn)，本文提出了一種利用Oracle腳本自動(dòng)生成盤活方案的方法，首先利用腳本估算每臺(tái)AD節(jié)點(diǎn)機(jī)的可盤活板卡數(shù)量n，然后將每臺(tái)AD節(jié)點(diǎn)機(jī)的板卡按照它們各自的占用數(shù)由少到多進(jìn)行排序，并選擇前n塊板卡，最后在板卡選擇的基礎(chǔ)上整合AD節(jié)點(diǎn)機(jī)的所屬區(qū)域和所屬局站，自動(dòng)生成最終的板卡盤活方案。實(shí)踐表明，自動(dòng)生成方案不僅效率遠(yuǎn)遠(yuǎn)高于手工制作，且割接量實(shí)現(xiàn)了最小化，不僅直接減少了割接的經(jīng)濟(jì)成本，也從源頭上減少了割接可能帶來(lái)的用戶投訴成本。

本文所述方法并不需要進(jìn)行系統(tǒng)功能開(kāi)發(fā)，也不需要特殊的軟硬件支持，只需一臺(tái)裝有Oracle 9以上版本的PC即可完成，對(duì)于需要進(jìn)一步推進(jìn)盤活工作的區(qū)域，具有較高的可移植性和可行性。

參考文獻(xiàn)

1 蕭穎，林擁勝，李毅.一種以業(yè)務(wù)需求為導(dǎo)向的網(wǎng)絡(luò)資源盤活法.電信科學(xué)，2012（5A）

2 Morton K.Oracle SQL高級(jí)編程.朱浩波譯.北京：人民郵電出版社，2011

3 Feuerstein S,Pribyl B.Oracle PL/SQL程序設(shè)計(jì)（第5版）.張曉明譯.北京：人民郵電出版社，2011