一種利用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