中 華 大 學

中 華 大 學

碩 士 論 文

以系統模擬探討資料庫綱要對資料庫系統效 能的影響

Use simulation to explore the effects of database schemas on the performance of database systems

系 所 別：資訊管理學系碩士班 學號姓名：E09610009 蔡宏修 指導教授：李之中 博士

中華民國 九十九 年 八 月

摘要

本研究的目的為探討企業使用水平式綱要、垂直式綱要與混合式綱要做為資料庫 綱要時，這三種綱要對資料庫效能的影響。傳統上，資料庫綱要多為水平式綱要，使 用水平式綱要的資料表即便是稀疏資料表，仍然需要逐個記錄資料表中的虛值。此外 水平式綱要不易進行綱要維護，所以有了垂直式綱要資料庫的設計產生。垂直式綱要 相對於水平式綱要，綱要維護較為容易。但是垂直式綱要的查詢命令較為複雜。混合 式綱要可將上述兩者的優點保留下來。必要或是常用的欄位屬性使用水平式綱要做記 錄；其它非必要或是不一定有記錄的屬性欄位則是使用垂直式綱要記錄。在過去研究 工作中，有關在現實生活中使用這三個綱要對資料庫系統效能的影響，未曾使用企業 真實資料做過深入的討論。本研究使用企業的真實資料以系統模擬的方式探討在同一 資料庫內容的情形下使用上述三個資料庫綱要時的資料庫效能。

本研究將模擬過程分成七個步驟，分別為決定模擬範圍、取得模擬範圍內資料庫 綱要跟資料與應用、資料庫綱要跟資料與應用轉換，模擬環境建置、撰寫模擬程式、

模擬程式執行與模擬結果分析等。 來做為實驗的架構。

本研究依據上述模擬過程並取得網路通訊企業實際生產線記錄資料庫進行效能 分析，除了將水平式綱要資料庫分別轉換成垂直式綱資料庫要與混合式綱要資料庫之 外，並且依照日常常態的 SQL 查詢頻率進行模擬，以評估這三種綱要的效能。實驗結 果顯示能在這三種綱要之中，在邏輯讀取、實體讀取、實體寫入以及查詢回應時間這 些效能指標上整體效能以混合式綱要效能最佳、水平式綱要次之、垂直式綱要最差。

關鍵詞：水平式綱要、垂直式綱要、混合式綱要、系統模擬

ABSTRACT

The main purpose of this study is to develop a simulation methodology for evaluating the performance of the database systems under different database schemas. The simulation methodology is organized as seven steps including (1) simulation scope determination, (2) database schema selection, (3) data import and transformation, (4)simulation environment construction, (5)simulation program coding, (6) simulation program execution, and (7)simulation output analysis.

This study further conducts a case study that uses the proposed simulation methodology and the real data of the company to investigate the performance discrepancy among three schemas, namely, horizontal schema, vertical schema, and hybrid schema.

The performance evaluation followed the steps of the proposed simulation methodology is made in the case study. In the case study, this study first obtains the database from the shop flow system of the company, and then transfers the horizontal schema database to the vertical schema database and the hybrid schema database. Third, this study finds the queries and the transactions for the daily operations of the company and executes them on the simulation. Finally, this study evaluates the performance of these three schemas in terms of the number of logical reads, the number of physical reads, the number of physical writes and elapsed time. The results show that the hybrid schema has the best performance when compared to the horizontal schema and the vertical schema.

keyword：Horizontal schema，Vertical schema，Hybrid schema，

System simulation

誌謝

三年研究所生涯，有太多人的支持與太多人的關懷，才得以使我順利地拿取這個 學位。唯有親自走過，才能體驗其中蘊涵的酸甜苦辣。研究所畢業是人生一小段的結 束，但也是另一段的開始。

在這過程中，首先要感謝的人是指導教授 李之中博士。由於有李老師不辭辛勞 的全力教導，才得以使我的論文計劃順利完成。而一路走來一起三年的同窗好友連小 婷，李逸儒，袁輝偉，戴維志同學，你們對我的幫助與鼓勵，讓我體會到進修的溫暖 與樂趣，共同求學的時光使我永誌難忘。還有我的學長，吳鼎元先生，有他提供過去 研究的資料與寶貴的實驗經驗，讓我有更不同的觀點在研究領域上更深一層的發展。

尤其感謝友人 劉誌堯博士，給予我相當多的指導與建議，讓我在論文的細緻度上更 臻完美。

最後還是要感謝我的家人，有你們的鼓勵與體諒，汗顏於求學期間對家庭與孩子 的疏於照料，辛苦你們了。

蔡宏修 謹識于中華大學資管所 中華民國 九十九年 七月

目錄

摘要 ... i 

ABSTRACT ... ii 

誌謝 ... iii 

第一章 序論 ... 1 

第一節  簡介 ... 1 

第二節  研究背景與動機 ... 1 

第三節  研究目的 ... 3 

第四節  研究範圍 ... 4 

第五節  論文架構 ... 5 

第二章 文獻探討 ... 6 

第一節  綱要設計 ... 6 

第二節  資料庫系統效能 ... 11 

第三節  系統模擬 ... 12 

第三章 系統模擬方法論 ... 15 

第一節  決定模擬範圍 ... 15 

第二節  取得模擬範圍內資料庫綱要、資料與應用 ... 16 

第三節  資料庫綱要、資料與應用轉換 ... 16 

第四節  模擬環境建置 ... 24 

第五節  撰寫模擬程式 ... 25 

第六節  模擬程式執行 ... 25 

第七節  模擬結果分析 ... 25 

第四章 實驗設計 ... 26 

第一節  決定模擬範圍、取得綱要、資料與應用 ... 26 

第二節  垂直式綱要資料庫綱要、資料與應用轉換 ... 29 

第三節  混合式綱要資料庫綱要、資料與應用轉換 ... 30 

第四節  模擬環境建置 ... 32 

第五節  撰寫模擬程式與執行 ... 33 

第五章 實驗結果與分析 ... 37 

第一節  邏輯讀取 ... 37 

第二節  實體讀取 ... 39 

第三節  實體寫入 ... 41 

第四節  磁碟機忙碌狀態 ... 42 

第五節  查詢回應時間 ... 44 

第六節  討論 ... 45 

第六章 結論與建議 ... 47 

文獻參考 ... 49 

附錄 ... 54 

A.  實驗工具介紹 ... 54 

B.  系統模擬程式碼 ... 57 

C.  查詢命令 ... 65 

表目錄

表 1-1 水平式綱要表 ... 2 

表 1-2 水平式綱要新增欄位屬性後查詢結果 ... 2 

表 1-3 垂直式綱要表 ... 3 

表 2-1 水平式綱要查詢結果 ... 7 

表 2-2 垂直式綱要查詢結果 ... 10 

表 3-1 水平式綱要轉垂直式綱要前 ... 17 

表 3-2 水平式綱要轉垂直式綱要後 ... 17 

表 3-3 水平式綱要與垂直式綱要應用指令差異 ... 18 

表 3-4 垂直式綱要轉水平式綱要前 ... 18 

表 3-5 垂直式綱要轉水平式綱要後 ... 19 

表 3-6 混合式綱要前的水平式綱要 ... 19 

表 3-7 混合式綱要後的水平式綱要 ... 20 

表 3-8 混合式綱要後的垂直式綱要 ... 20 

表 3-9 混合式綱要與水平式綱要應用指令差異 ... 20 

表 3-10 應用一覽表 ... 23 

表 5-1 各綱要 Logical Reads 比較 ... 37 

表 5-2 各綱要 Physical Reads 比較 ... 39 

表 5-3 各綱要 Physical Writes 比較 ... 41 

表 5-4 各綱要 sda3 Busy 比較 ... 42 

表 5-5 各綱要 Elapsed Time 比較 ... 44 

表 b-1 h/run.sh ... 57 

表 b-2 h/ makestarttime.php ... 58 

表 b-3 h/ oracle_delete.php ... 58 

表 b-4 h/insert/horizontal_model.php ... 59 

表 b-5 h/insert/oracle_insert.php ... 60 

表 b-6 h/1/random_parameters.php ... 61 

表 b-7 h/1/horizontal_model.php ... 62 

表 b-8 h/1/exponential.php ... 63 

表 b-9 h/4/horizontal_model.php ... 63 

表 b-10 h/1/oracle_query.php ... 64 

C-1 三種綱要的 24 道查詢命令 ... 65 

圖目錄

圖 2-1 模擬的理論基礎 ... 13 

圖 2-2 回饋評估模式 ... 14 

圖 3-1 系統模擬步驟 ... 15 

圖 4-1 水平式綱要設計的 ER-Model ... 28 

圖 4-2 水平式資料庫綱要 ... 28 

圖 4-3 垂直式綱要設計的 ER-Model ... 30 

圖 4-4 垂直式資料庫綱要 ... 30 

圖 4-5 水平垂直混合式綱要設計的 ER-Model ... 31 

圖 4-6 水平垂直混合式資料庫綱要 ... 32 

圖 4-7 程式模型架構圖 ... 35 

圖 5-1 Horizontal Logical Reads ... 37 

圖 5-2 Vertical Logical Reads ... 38 

圖 5-3 Hybrid Logical Reads ... 38 

圖 5-4 Horizontal Physical Reads ... 39 

圖 5-5 Vertical Physical Reads ... 40 

圖 5-6 Hybrid Physical Reads ... 40 

圖 5-7 Horizontal Physical Writes ... 41 

圖 5-8 Vertical Physical Writes ... 41 

圖 5-9 Hybrid Physical Writes ... 42 

圖 5-10 Horizontal sda3 Busy ... 43 

圖 5-11 Vertical sda3 Busy ... 43 

圖 5-12 Hybrid sda3 Busy ... 43 

圖 5-13 各綱要每個查詢之回應時間分佈 ... 45 

圖 A-1 資料庫效能監控系統架構圖 ... 57 

第一章 序論

本章主要分為研究背景與動機、研究目的、研究範圍以及論文架構四大部分。首 先說明本研究之研究背景以及研究動機，再透過研究目的的設立，訂定出本研究之研 究範圍，完成上述研究步驟後，再制定出整體的研究流程，以對本研究有完整的說明。

第一節 簡介

在現代的企業裡面，資料庫(Database)已經是資訊提供不可或缺的一個角色。資 料庫的技術演變確實幫助人類解決管理資料以及儲存資料的問題，但隨著資料庫廣泛 的應用與實務上需求的演變，隨之而來的是在資料庫上的設計(Design)與效能 (Efficacy)問題的產生。過去資料儲存只要能正確的存入與正確的找出就可以滿足使 用者的需求。如今面臨到的是資料庫查詢的成本(Cost)付出，綱要(Schema)設計的彈 性 (Flexible) 。 過 去 典 型 的 資 料 表 綱 要 (Table Schema) 設 計 方 式 是 水 平 式 (Horizontal)，但是由於此種設計方式若要滿足彈性與可擴充性的條件是有困難的，

因此另一種的設計概念－垂直式(Vertical)(楊欣哲，2007)綱要設計被發表出來。不論 是水平式綱要設計或是垂直式綱要設計，他們都是為了滿足真實世界的需求而抽象化 的概念，各都有其優劣之處，本研究為此探討而產生，希望能提供在選擇綱要設計時 有參考依據來協助選擇決定。

第二節 研究背景與動機

本研究採用某電子企業真實(Production)狀況資料庫。此公司創立自西元 1992 年 至今，所生產主要產品網路通訊設備軟、硬體產品整合服務與網際網路通訊協定 (Internet Protocol；IP Protocol)等核心技術之研發。主要產品包括了：ADSL 寬頻分享 器、寬頻路由器(Router)、印表機列印伺服器(Printer Server)、網路監視系統(IP Camera)、網路儲存設備(Network Access Storage；NAS)。其中印表機列印伺服器以

及 ADSL 寬頻分享器為最大宗生產產品，未來發展則以網路多元化整合商品為主。例 如多媒體結合寬頻網路之應用、Cable Modem/光纖交換器、跨足通訊(Telecomm)業 界使用的 PSTN Server。

隨著產品的應用與規格越來越越多樣化，使用的製程越來越複雜，在生產流程上 也越來越彈性化。資料庫的水平綱要已經不敷類似像這樣的生產方式使用。當生產的 產品版本更新加入新功能時，資料庫為了要忠實的記錄產品生產資訊，也面對到要變 更資料綱要的欄位與屬性。舉例來說：當接獲產品路由器訂單時，生產線得要組裝半 成品，再來半成品需要透過工單去尋找產品測試程式，接著開始測試該產品是否為良 品，PcbaNo 為產品的製造序號，Domain 為產品的出貨國別，FwVer 為產品的韌體版 本(Firmware version)，Result 為產品的測試結果(Pass/Fail)，Router 產品的測試結果 記錄在電腦主機的水平式綱要的橫式表式架構。在過去，如果需要新增ㄧ資料屬性則 需要改變綱要架構。例如：新增是否對 IPV6(Internet Protocol Version 6)的支援時，

則需要新增一個欄位。

表 1-1 水平式綱要表

PcbaNo Domain FwVER Result S1.123 USA a.bin Pass S1.234 Japan b.bin Fail S1.345 Taiwan c.bin Pass

……… ……… ……… ………

表 1-2 水平式綱要新增欄位屬性後查詢結果 PcbaNo Domain FwVER Result IPV6 S1.123 USA a.bin Pass Yes S1.234 Japan b.bin Fail Yes S1.345 Taiwan c.bin Pass No

……… ……… ……… ……… ………

如果在垂直式綱要設計的資料庫裡，當屬性時有所變動時，只要改變架構內的屬 性即可，並不需要重新規劃綱要。所以本研究最主要的動機在於，既然有水平式綱要、

垂直式綱要與混合式綱要，如果在最初設計資料庫綱要時應該以什麼樣的一個標準來

決定資料庫綱要的型式，能提供一個參考方向。

表 1-3 垂直式綱要表 PcbaNo ItemID Value S1.123 Domain USA S1.234 Domain Japan S1.345 Domain Taiwan S1.123 FwVER a.bin S1.234 FwVER b.bin S1.345 FwVER c.bin S1.123 Result Pass S1.234 Result Fail S1.345 Result Pass S1.123 IPV6 Yes S1.234 IPV6 Yes S1.345 IPV6 No

……… ……… ………

第三節 研究目的

在網路通訊設備廠商之中，為了滿足每項產品不同之特性之生產記錄，測試記 錄，韌體版本之記錄，所以傳統水平式綱要必須要建立很多的欄位屬性來記錄各種產 品不同特性的各種資料。但是往往會有很多紀錄並不是每樣產品都會有同樣的特性資 料。這樣的記錄會造成資料的稀疏。雖然水平式綱要的查詢語法較一般大眾所知與接 受，但是經年累月下來資料稀疏所造成儲存空間的資源浪費也會是一個為數不小的數 量。反之如果只純粹使用垂直式綱要則是需要較多的資料表來記錄不同屬性的不同記 錄，並且垂直式綱要的資料庫查詢語法一般人較於陌生，撰寫查詢命令時必須要知道 有哪些資料表可供 join 查詢。也因為經常會使用到 join，所以在查詢尤其是寫入資料 時所使用的磁碟輸出入(disk I/O)使用成本上會較高於水平式綱要，但是垂直式綱要 則是沒有水平式綱要的資料稀疏問題。混合式綱要則是取兩者綱要之優點特色混合成 一種新型式的綱要。

這樣的資料庫綱要架構不只在網路通訊設備的生產線上會造成資料稀疏與綱要 異動上的不便，在其他業界的生產記錄，網路拍賣平台，設備代理商等都會遭遇到因 為每個時期的產品不同而需要變更資料屬性，所以本研究在探討用一般的日常的存取 頻率對水平式綱要，垂直式綱要還有混合式綱要的查詢，新增與刪除效能上的一個比 較。而本研究所期待的實驗結果是能展現出水平式綱要、垂直式綱要與混合式綱要效 能上的差異，並解釋出這些綱要效能上差異的原因去提供設計資料庫綱要的策略。

第四節 研究範圍

本研究是以某網路通訊設備生產公司真實線上生產資料為研究背景。將該公司的 生產製程需求設計，轉換成垂直式綱要資料庫與混合式綱要資料庫模型。並且依照企 業的實務方面分成兩種不同頻率的應用工作，一種是固定頻率應用；依照企業日常常 態，會在固定的時間內做固定內容查詢，通常是在做即時的狀況回報。例如：每分鐘 都要顯示出某條生產線的工單生產數量，良率與不良率的顯示。另一種是隨機應用頻 率，不會因為在固定的時間就必須要做固定的查詢，通常在做生產數據的分析。例如：

某張歷史工單的生產記錄，平均工時，測試記錄等等。總數有 24 個應用命令，常態 應用命令有 12 個命令，隨機應用也是 12 個命令。觀察耗費系統成本的變化作效能評 比，本研究有下列幾項條件：

 考慮新增、刪除等指令對效能的變化，但是不考慮更新對效能的變化。

 模擬一天八個小時工作量的查詢。生產線通常都是 24 小時輪班在製作生產產 品，本研究測試資料庫綱要效能模擬其中一班八個小時的查詢樣型。

 假設實際工單的生產製造路徑(Routing)與資料庫定義的產品路徑是相同的。

為了使資料庫單純化不將維修與重工程序(Repair and Rework Procedures)納入研究範 圍。

第五節 論文架構

本論文的架構內容如下：

第二章『文獻探討』

本章分四節來討論資料的水平式綱要、垂直式綱要、混合式綱要與這些綱要的特 性，並介紹業界彈性綱要在資料庫是如何設計；最後式介紹資料庫的效能指標。

第三章『系統模擬方法論』

本章分兩節，將介紹本研究實驗的設計概念；實驗邏輯，效能指標收集方式。

第四章『實驗設計』

本章分四節來介紹實驗的規格與程序，測試環境，程式碼的介紹與獲得的效能指 標數據展現。

第五章『實驗結果與分析』

本章討論水平式綱要、垂直式綱要、混合式綱要所展現的效能與實驗結果。

第六章『結論與建議』

本章討論這些綱要每個效能上的差異與整體之解釋。

第二章 文獻探討

雖然技術上存在著許多不同資料儲存的方式，但大部份的公司幾乎都選擇了關聯 式資料庫存放公司重要的營運資料，關聯式資料庫有著容易使用與管理等優點，同時 支援結構化查詢語言(Structured Query Language，SQL)，使得前端程式的開發與應用 更為方便。然而關聯式資料庫如何存放與支援生產線產品製作資料呢？事實上，已有 許多研究在探討這樣的議題，以下將常用的 Table Schema 加以說明。

第一節 綱要設計

一、水平式綱要

水平式綱要是在資料庫中一種非常常見的設計方式，此種設計方式直覺而且容易 設計。它的特性是所有可能用來描述實體屬性(entity attribute)都包含在該 table 的欄 位(Columns)中(如表 2-1) 所示，然而這樣的設計可能會有下列問題存在：

 當屬性過多時會形成一個寬(Wide)資料表。

就在業界的實際運作經驗，資料庫綱要不可能是不會變動的，乃是會隨著實體屬 性的改變(例如新產品開發、產品改版、功能改良等、生產製程)而改變。當實體 屬性改變就必須更改資料表綱要，若原資料表的屬性無法描述實體的變動時，即 須新增欄位以滿足完整描述實體屬性改變的事件。當屬性增多欄位也就越多，即 形成一個寬的資料表。在後面的章節中將說明寬資料表所造成的問題。

 寬資料表可能形成稀疏資料集(Sparse Data Sets)。

由於每種實體(例如產品)包含的屬性會因實體特性(property)而有所不同，而每 種實體並不需要用到寬資料表中的所有欄位來描述實體屬性，因此這會造成資料 表的儲存格(cell)存有Null值的情況發生，而當資料表的Null值存在過多時就形成 了稀疏資料集。在後面的章節中將說明稀疏資料集所造成的問題。

 隨著實體特性的改變(例如產品不斷推陳出新)，資料表綱要就必需不斷的更新與

維護(例如新增資料表欄位、更改欄位屬性等)，資料表綱要一旦改變，在大部分 的情況下需要修改應用程式(applications)，這樣的大工程都將付出大量的工時、

效率的浪費以及昂貴的人工成本。

表 2-1 水平式綱要查詢結果

PcbaNo Domain FwVER Result Att1 Att2 Att3 Att4 Att…

S1.123 Pass Value ……

S1.234 Japan b.bin Fail Value Value ……

S1.345 Taiwan Value ……

……… ……… ……… ……… ……… ……… ……… ……… ………

稀疏資料集會造成索引(Index)的建立以及整個資料表佔用很大的儲存空間，並 且 Null 值會造成空間上的浪費。儘管業界有一些方法途徑可廣泛的應用在稀疏資料 的處理與管控(handle)，但卻沒有令人滿意的完美技術。由於水平式綱要直覺而且容 易設計，在一般正常(normal)的情況下如果實體屬性變動不大還是最佳的選擇。缺點 是一旦實體特性改變就必需更改資料表綱要且甚至極有可能要修改程式，若要在最短 時間內完成修改並上線，那麼擴充資料表的欄位是在最快的方式了。但是若產品屬性 變動頻繁(例如多樣化的產品)，在水平式綱要設計的方式之下，只能一味的增加屬性 欄位，這樣的方式結果就如前面所述會造成浪費磁碟空間以及高度不佳的效能。要解 決這樣的問題有一個折衷的方式那就是採用垂直式綱要設計的資料表，它排除了 Null 值的問題且資料表屬性不多故不會形成稀疏資料集以及寬資料表。因此，垂直 式綱要對於上述的問題開啟更廣泛應用的可能性，因為它突破了在寬資料表的限制，

改變資料表綱要對垂直式綱要而言反而是多餘的工程(task)。

為了解決上述問題，學者 Daniel (2007) 在 Column-Stores For Wide and Sparse Data 研究中指出，水平式綱要當屬性非常多的時候，可能會造成效能上的問題。例 如，有一個電子商務市集含有 1000 家製造廠商，每一家廠商至少有 500 項產品分 類，每一項分類有 4000 種產品，故總共有 2,000,000,000 種可能，那麼不難想像排 序一個具有 4000 種屬性的寬資料表是一件多麼恐怖的事情。而當使用者查詢這個寬

資料表時，業界經驗顯示出使用者所利用到的屬性常常僅佔有所有屬性中少的百分 比。另外，從磁碟讀取一筆資料時，其所佔的 block 數遠大於一個屬性所佔的 block。

再者如果只是需要從中讀取一個屬性在水平式綱要的設計之下勢必連同旁邊周圍的 卻用不到的屬性要一併讀進來，便造成磁碟頻寬的浪費。

二、垂直式綱要

與 Horizontal Table Schema 相似，Vertical Table Schema 將所有的產品描述全部都 集中在一個 Table 中。Daniel(2007)在 Column-Stores for Wide and Sparse Data 提到垂 直式導向設計(vertical-oriented)資料庫不同於水平式綱要，垂直式綱要雖然也是將所 有實體屬性描述全部儲存在一個資料表中且每個實體屬性是分開的，卻又是以像連續 的資料一樣儲存在資料表中(如表 2-2)，一個實體有多少屬性就會在這個資料表存在 有多少筆的資料。(水平式綱要乃是各個屬性值儲存在同一列紀錄(tuple))。對於這樣 的設計，有著以下的優點存在：

1. 增進磁碟頻寬的利用率

在垂直式綱要設計中，只需從磁碟(或記憶體)讀取查詢所需要的屬性；而在水平 式綱要設計中，縱使只需要一個屬性，周圍的屬性也需自該屬性一併讀取，資料才可 以被存取。

2. 增進資料密度

資料是一起存放在相同的屬性領域(domain)以增加局部性(locality)與壓縮率 (compress ratio 尤其若該屬性有經過排序的時候)，也可以進一步降低資料傳送時的頻 寬。

3. 降低 Table Schema 維護成本

在水平式綱要設計中，當實體特性改變時若資料表屬性無法完整描述實體屬性，

則必須修改資料表綱要，然而在垂直式綱要設計則無此問題。

4. 降低應用端程式維護成本

承(3)在水平式綱要設計中，資料表綱要一旦改變，在大部分的情況下應用端的 程式也必須修改(包括新增、修改、刪除、查詢與維護模組)，造成昂貴的人工成本，

而在垂直式綱要設計則無此問題。

5. 滿足實體特性多變的彈性

實體(例如產品)多樣化或者生產製程彈性等多變的特性在水平式綱要設計中需 不斷的維護資料表綱要，然而在垂直式綱要設計中可以滿足多變的彈性。

6. 增進快取的局部性

快取記憶體的空間需大於一筆紀錄所有屬性(tuple attributes)的總和，在水平式綱 要設計中快取記憶體需包含所有的屬性但不見得所有屬性都是用得到的，這樣會造成 空間浪費以及降低快取的擊中率(hit rates)，而在垂直式綱要設計則無此問題。

然而 Vertical Schema 也存在一個缺點，當在搜尋生產記錄電路板號碼時，通常會根據 許多不同的參數來查詢，使得傳送到關聯式資料庫伺服器的 SQL 語法，將包含許多 的 AND/OR，容易造成 SQL 語法上的錯誤及影響查詢的執行效能。而垂直式綱要的 有下列的缺點存在：

1. 增加磁碟搜尋時間

垂直式綱要的設計是將屬性當成資料存放，故磁碟在搜尋讀取block時，就 如同並行讀取複合欄位(multiple columns)一樣，才能將一筆紀錄讀完。然而如果 磁碟已經預先讀取(pre-fetches)以預備使用，那麼就可以維持較低的I/O 成本。

2. 增加 insert 的成本

承(1)垂直式綱要的設計是將屬性當成資料(data)存放，故若有100 個屬性 就會至少有100 筆資料。在執行插入命令(insert queries)時需要將每個屬性存放 在獨自的tuple位置為每個屬性insert tuple，所以當屬性的數量多時，就會造成 insert的執行效能不佳。

3. 增加查詢績效的成本

同前面所述，當屬性是以資料型態存放時，從關聯式資料庫的特性資料表沒 有欄位可以join，所以在查詢命令中就需要以較多的and、or等邏輯運算元來完成 查詢的任務，難度較高容易寫錯且會形成複雜的查詢命令，那麼在執行複雜的查 詢時往往要忍受不佳的查詢效能(poor query performance)。

4. 漏失屬性型態

在垂直式綱要設計中所有的值需以VARCHARS型態填入欄位才能支援所有 需求的資料屬性，儘管它具有容易擴充延展的特性設計，但卻付上漏失資料屬性 的代價。(Agrawal et al., 2001)

表 2-2 垂直式綱要查詢結果

PcbaNo PrgID ItemID Value S1.123 MAS Domain USA S1.123 MAS FwVer a.bin S1.123 MAS Result Pass S1.123 DL Domain US S1.123 DL FwVer b.bin S1.123 DL Result Pass

三、水平式與垂直式的比較

綜合前面所述，不同特質的實體可以儲存在相同的水平式寬資料表(即一個屬性 對應一個欄位)。業界一般情況會選擇使用水平式資料表是因為

 查詢時只要參考一個資料表(擁有較垂直式資料表好的效能)。

 增加一個屬性(property)需要一個欄位(直覺且容易設計)。

 更新資料時只要在一個資料表即可完成。

但這樣的設計卻可能帶來很多欄位的值是稀疏的且查詢的結果集也是稀疏的。不過就 一般而言，若實體屬性變動不大以及考慮到未來的擴充性(例如保留欄位)，這樣的設 計方式也未嘗不可。若因 Null 值造成小部分的稀疏資料集，就總體而言是可以被接 受的。不過，若變動太大，為了要符合運作的環境，那麼就必需用垂直式綱要的解決 方法(solutions)來管控 Null 值以避免稀疏資料集所造成的空間浪費並且也可以免於

維護資料表綱要所造成的人工成本。最後且最重要的就是垂直式綱要只需一個屬性群 (property bag)以及一個資料表就可以真實的描述實體特性，並且支援(support)擴充性 與彈性。(Acharya et al., 2008a)資料模式一般可分成概念資料模式與邏輯資料模式，

因此有關資料模式對使用者查詢績效的影響的研究，可分為概念性資料模式彼此之間 的比較研究(Chan and Teo, 2005; Debreceny and Bowen;2005)、邏輯性資料模型彼此 之間的比較研究(Borthick et al., 2001)，以及概念性資料模式與邏輯性資料模型之間 的比較研究(Allen and March, 2006; Chan et al., 1993; Siau et al., 2004; Jih et al., 1989)。

第二節 資料庫系統效能

在本節中將介紹資料庫效能評估的方法。一個應用需要的 CPU、I/O 資源越多，

應用執行的速度就越慢。因此，調教效能的方式除了以一種使用更少的 CPU、I/O 資 源的方式重寫應用命令，另一種方式就是調教資料庫綱要。調教效能的目的是盡可能 的耗費更少的伺服器資源，而不單單只是查詢耗費時間最短，尚需考慮磁碟 I/O 成本，

尤其是在資源利用不斷變化的伺服器上更是如此。一般而言評估資料庫效能有兩大指 標，一為查詢耗費的時間(ElapsedTime)；另一為磁碟 I/O 成本(Disk I/O Cost)。

 查詢回應時間(Elapsed Time)

表示執行此次查詢時使用多少時間(elapsed time)。CPU 執行時間是對執行查詢 所需要 CPU 資源的一種相對穩定的測量方法，與 CPU 的忙閒程度沒有關係。但是，

每次執行應用時這個數字也會有所不同，只是變化的範圍沒有總時間變化大。經過時 間是執行應用所需要的時間(不計算阻塞或讀數據的時間)，由於伺服器上的負載 (loading)是不斷在變化的，因此這一數據的變化範圍有時會相當地大。

 Disk I/O

Logical Reads

邏輯讀取，此項目是提供最有用的資訊。在SQL Server對任何資料進行運作前，

首先必須把資料讀取到buffer中。此外，SQL Server會從buffer中讀取資料，並把資料

讀取到大小為8K位元組的頁(page)中。而邏輯讀取乃是指SQL Server為得到查詢中的 結果而必須從buffer讀取的頁數。在執行查詢時，SQL Server不會讀取比實際需求多 或少的資料，因此，當在相同的資料集上執行同一個查詢，得到的邏輯讀取的數字總 是相同的(偶而會不同但差異很小)。所以如果邏輯讀取值下降，那麼就表示查詢時所 使用的伺服器資源減少，效能就有所提高；反之則效能減低。故在其他條件不變的情 況下，查詢所使用的邏輯讀取越少，其效能就越好速度就越快。

Physical Reads

實體讀取。在SQL Server開始執行查詢前，首先它要作的就是檢查所需要的資料 是否存在buffer中，如果在就從中讀取，如果不在SQL Server就會先將所需要的資料 從磁碟讀到buffer中。不難想像SQL Server在執行實體讀取時比執行邏輯讀取需要更 多的伺服器資源。因此，在理想情況下應當儘量避免實體讀取操作。值越小表示效能 越好；反之則越差。

Physical Writes

監視資料庫中之資料檔的實體讀取和實體寫入的數量。實體讀取是從磁碟讀取的 資料區塊。若實體讀取相對於傳回的資料數較高，這可能表示執行查詢的資料庫應被 檢閱以進行最佳化。實體寫入是寫入磁碟的資料區塊。使用此資源模型來判斷是否有 特定的資料檔案的實體讀取或寫入異常過高。這項資料非常有用， 可以判斷資料檔 是否應該因為其中一個磁碟 (由於從資料檔讀取或寫入過多而導致I/O瓶頸)，而移至 另一個磁碟。透過將隨時間變化的I/O增長圖表化，該資料也可用於規劃容量。

第三節 系統模擬

監視資料庫中之資料檔的實體讀取和實體寫入的數量。實體讀取是從磁碟讀取的 資料區塊。若實體讀取相對於傳回的資料數較高，這可能表示執行查詢的資料庫應被 檢閱以進行最佳化。實體寫入是寫入磁碟的資料區塊。使用此資源模型來判斷是否有 特定的資料檔案的實體讀取或寫入異常過高。這項資料非常有用， 可以判斷資料檔

是否應該因為其中一個磁碟 (由於從資料檔讀取或寫入過多而導致I/O瓶頸)，而移至 另一個磁碟。 透過將隨時間變化的I/O增長圖表化，該資料也可用於規劃容量。

模擬為數學模式中的一種，也是屬於未確定性模式的一種方法。它是建立在三種 基本理論上：

1. 統計與機率，

2. 資訊技術，

3. 系統理論

如圖 2-1 所示，系統模擬三者之交集。由於隨機性質，模擬的輸入資料(人工化歷史 資料)與輸出結果，必須以機率與統計之觀念來處理與解釋。

圖2-1 模擬的理論基礎

資訊技術則在模擬之過程中扮演執行模式動態行為的主要角色，也由於資訊技術 的快速發展使得模擬越來越成熟。而系統理論之方法則幫助人們將一複雜的系統對 象，如何分析與建構方式，將問題對象的組成及其功能行為轉換為模擬模式。而其最 重要的便是針對目標系統的相關知識(Domain Knowledge)的認知。

如前述，模擬亦為解決系統問題的方法，在此對模擬給予以下之定義：模擬是針對某 一已存在或構想中之操作性系統行為，建構一個以電腦為基礎之數學或邏輯模式，然 後在此實驗模式上：

1. 評估各不同組合之決策

2. 透過模擬運做的過程瞭解(Understand)整體系統的操作行為

換言之，模擬是以建構於電腦中虛擬的環境代表實際系統。用以研究實際系統行為的 一種模式。一般而言，模擬均利用電腦的程式語言來表達實際系統的行為，再由程式 的執行產生一結果，透過結果去瞭解與分析一個系統。由模式本質而言，模擬基本上 為一評估模式(Evaluative Model)，而非一般之最佳化模式(Optimal Model)。圖 2-2 所表示即為一回饋評估模式，在此種模式上，所輸入的是一組決策參數，模式輸出則 為系統績效，例如平均產出率、平均等候時間等。用此模式一般難以求得最佳解，只 有靠改變不同組決策參數，以比較所以得系統績效來決定何組決策參數為較佳。

圖2-2 回饋評估模式

第三章 系統模擬方法論

本章分為六個章節來描述本研究整體架構之模型，讓後續的研究人員瞭解到本研 究實驗方式與流程，進而可以銜接後續的研究工作。或者在真實企業上實作時可以了 解到如何重塑出一個實驗來評估各綱要效能之評估。

圖3-1 系統模擬步驟

本研究分為七大步驟，藉由這七大步驟來完成整個實驗的模擬，從圖 3-1 實驗模 型來看，這七大步驟包含了兩個層面。的一個為前面五個步驟，這是在做模擬前的資 料準備與應用程式的設計，後面的兩個步驟則是在處理系統模擬與資料分析，而這七 大步驟的各功能分別為：

1. 決定模擬範圍

2. 取得模擬範圍內資料庫綱要、資料與應用 3. 資料庫綱要、資料與應用轉換

4. 模擬環境建置 5. 撰寫模擬程式 6. 模擬程式執行 7. 模擬結果分析

以下為各區塊主要功能之介紹。

第一節 決定模擬範圍

評估一套綱要系統之前最主要的工作就是要先決定模擬系統的範圍為何。因為隨 著資料量的大小、資料庫設計複雜程度、模擬所需資源、耗費時間等種種因素都會影

響到現有資料庫的轉換資料、模擬複雜度與數據分析的結果。假設模擬範圍過小，資 料量也不甚巨大，資料稀疏程度也不高，做出來的實驗結果與可供評估價值可能不會 很高。反之，如果資料量過大，所要模擬的範圍也是很大，可能光要轉換綱要與資料 就得耗費更多的時間與系統資源而影響到正常在線資料庫的正常運作。所以在決定一 個模擬範圍時必須要考慮到上述所說的因素，如果能先劃分出現有的資料庫與應用程 式之瓶頸關鍵點出來當模擬範圍則會是一個較好的範圍劃分之依據。

第二節 取得模擬範圍內資料庫綱要、資料與應用

在來就是要了解到當一個模擬範圍劃分好之後的所有資料表的綱要，裡面有什麼 樣的資料，這些資料有甚麼樣的特性。例如某個資料表是否為資料寫入頻繁的特性，

或者是屬於大量查詢還是經常有被資料異動甚至是刪除。然後對應到這些資料表的應 用程式，擷取出這些應用程式對這些資料表作動的SQL指令並且這些SQL命令都要有 查詢頻率的記錄，因為後續實驗得要依據這些SQL命令來對模擬範圍的資料表做模擬 的一個依據。

第三節 資料庫綱要、資料與應用轉換

本節在介紹這三種綱要的交換方式。第一段在介紹最常見的水平式綱要轉換成垂 直式綱要，這也是最常使用的轉換方式，第二段在介紹垂直式綱要轉換成水平式綱要 的概念，雖然最常見的是水平式綱要，但是已經有業界在使用垂直式綱要做為主要的 資料庫綱要設計，所以在此特別介紹垂直式綱要轉換成水平式綱要的概念，第三段就 是水平式綱要轉換混合式綱要的介紹。

1.

本節將要介紹的概念是將使用最廣泛的水平式綱要轉換成垂直式綱要，在水平式 綱要轉成垂直式綱要後，所有水平式綱要的欄位屬性將會消失，但是在儲存資料的方 式對於磁碟空間的使用會變得比較有效率。

表 3-1 水平式綱要轉垂直式綱要前

Horizontal UID A1 A2 A3 A4 1 Data1

Null Null Null

Null Null

Null

表 3-1 是 一 個 水 平 式 綱 要 的 概 念 ， UID 屬 性 是 Number(4) ， A1 屬 性 是 Numbar(4,2)，A2 屬性是 Varchar(20)，A3 屬性是 Varchar(30)，A4 屬性則是 Varchar(40)。

表 3-2 水平式綱要轉垂直式綱要後 Vertical UID Attrib Data

1 A1 Data1

2 A1 Data2 2 A2 Data4 3 A1 Data3 3 A2 Data5 3 A3 Data6

轉換成水平式綱要之後，UID 欄位屬性依然是 Number，但是 Attrib 欄位則是在 描述水平式綱要的屬性名稱，所以 Attrib 欄位屬性會是 Varchar(10)因為要囊括所有 A1~A4 最長的欄位名稱長度，Data 欄位是在描述資料內容，因為被轉換成純文字，

所以 Data 欄位也會是 Varchar(40)，因為欄位長度要服從水平式綱要最大長度的欄位，

所以 Varcahr 長度則為 40。因為水平式綱要因為轉換成垂直式綱要之後，所有資料屬 性都會消失，所以 Attrib 欄位與 data 欄位都會變成 Varchar。

也因為水平式綱要轉換成垂直式綱要，綱要的欄位屬性都會變動，因此資料庫的 應用指令也必須得要重新寫過，下列表 3-3 以第 18 題應用為範例，該應用目的為用 MAC 號碼與測試站找出生產紀錄，下列為水平式綱要與垂直式綱要的應用指令比較。

表 3-3 水平式綱要與垂直式綱要應用指令差異

水平式綱要應用指令 垂直式綱要應用指令

Select testrecord.uid_, testrecord.pcbano from TestRecord,TestPrg

where replace('

','',TestRecord.TestPrgID)=replace(' ','',TestPrg.TestPrgID)

and PcbaNo in (Select PcbaNo from TestRecord where MAC=:query_p)

and TestPrg.TestPrgName='Download'

Select b.TestPrgName,a.*

from

(Select * from TestRecord where PcbaNo in

(Select PcbaNo from TestRecord where TestItemID='MAC' and Value=:query_p) )a left join (Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Download'

2. 垂直轉水平式綱要

本節是示範一個垂直式綱要變成水平式綱要轉換的影響。從下面的表 3-4 可以看 到資料 A 只有一個 A1 屬性與資料、資料 B 擁有 A1 A2 屬性與各所屬的資料，資料 C 則有 A1 A2 A3 屬性與各所屬之資料。

表 3-4 垂直式綱要轉水平式綱要前 Vertical UID Attrib Data

A A1 Data1 B A1 Data2 B A2 Data4 C A1 Data3 C A2 Data5 C A3 Data6

從垂直式綱要轉成水平式綱要之後可以把表 3-5 與表 3-1 做一個比較，A4 欄位 不見了，因為在垂直式綱要的特性就是，沒有資料就不會有欄位屬性，所以依照表 3-1 來看，A4 欄位所有資料都是 Null，自然 A4 欄位就不會在垂直式綱要存在，因為 垂直式綱要的設計就是在解決資料稀疏的問題，如表 3-5 的水平式綱要，A4 欄位消 失。

資料從垂直式綱要轉換成水平式綱要之後還有一個特性是要特別注意的，那就是 所有的資料屬性都會變成 varchar。因為垂直式綱要是在同一個欄位裡面記錄各種不 同屬性的資料，而要能相容各式各樣不同屬性的資料就只有 varchar 才能達到此目 的，所以當垂直式綱要轉換成水平式綱要時因為沒有欄位屬性特性，所以轉換後所有 的水平式綱要欄位屬性都會是 varchar。

表 3-5 垂直式綱要轉水平式綱要後 Horizontal UID A1 A2 A3 A Data1

Null Null

B Data2 Data4

Null

C Data3 Data5 Data6

3. 混合式綱要

基於第二章所述之水平式綱要與垂直式綱要的各優點與缺點，既然要去掉水平式 綱要的資料稀疏的問題，又想要水平式綱要比較易懂的資料庫應用指令，所以本節介 紹混合式綱要。混和式綱要在必要的欄位使用水平式綱要資料表，不一定會用到的欄 位則使用垂直式綱要來記錄資料，下面表 3-6 是傳統式的水平綱要，表 3-7 是垂直綱 要，依照表 3-6 的特性欄位 UID 與 A1 是必要填寫資料的欄位，A2 與 A3 則是不一定 會有資料的欄位，故如果變成垂直式綱要則會把所有屬性拉在同一列，但是這樣會增 加磁碟搜尋效率緩慢，所以在表 3-7 與表 3-8 裡把 Horizotal 拆成 Hybrid 1 與 Hybrid 2，Hybrid 1 資料表是使用水平式綱要資料表，放置一定會有資料的欄位 UID、A1 來 避免掉資料稀疏問題的產生，Hybrid 2 則是放置不一定會有資料的欄位 A2、A3，來 縮短資料庫資料搜尋與寫入的實體成本，以提高資料庫效益，並且都會用不到的欄位 A4 也不會保留在任何一個資料表上來浪費空間。

表 3-6 混合式綱要前的水平式綱要 Horizontal UID A1 A2 A3

A Data1

Null Null

Null

表 3-7 混合式綱要後的水平式綱要 Hybrid 1

UID

A Data1

B Data2 C Data3

Table 2 的 UID 外來鍵來自 table 1 的 UID，當 table 1 的 c 記錄被搜尋到之後 就會拿 UID 的資料去 table 2 的 UID 欄位去繼續搜尋，如果有搜尋到相符資料，則繼 續顯示出(A2 A3)欄位的資料。

表 3-8 混合式綱要後的垂直式綱要 Hybrid 2

UID

B A2 Data4 C A2 Data5 C A3 Data6 表 3-9 為混合式綱要的應用指令與垂直式綱要應用指令比較表

表 3-9 混合式綱要與水平式綱要應用指令差異

混合式綱要應用指令 水平式綱要應用指令

Select TestPrg.TestPrgName,TestRecord.*

from TestRecord,PcbaMapping,TestPrg where

TestRecord.PcbaNo=PcbaMapping.PcbaNo and replace(TestRecord.TestPrgID,'

','')=replace(TestPrg.TestPrgID,' ','') and PcbaMapping.MAC=:query_p and TestPrg.TestPrgName='Download'

Select testrecord.uid_, testrecord.pcbano from TestRecord,TestPrg

where replace('

','',TestRecord.TestPrgID)=replace(' ','',TestPrg.TestPrgID)

and PcbaNo in (Select PcbaNo from TestRecord where MAC=:query_p)

and TestPrg.TestPrgName='Download'

4. 應用轉換

在應用程式區塊裡面，整個應用程式是模擬應用的重心所在。程式所肩負的 角色在模擬整個綱要被應用的狀態，應用狀態分成兩種方式，常態應用與隨機應 用，用這兩種法模擬在現實生活中應用的頻率。在撰寫模擬程式之前要先設計好 每個綱要模型的應用命令，在將應用命令包含在模擬程式裡，在模擬應用之前還 必須要有應用前的條件查詢以供正式應用的動態條件。並且為了取得穩定的應用 狀態，還要把應用時間延長一倍，取後段模擬應用的效能監視並獲得數據以提供 分析之用。

5. 實驗查詢題目設計

依據之前的研究所得知企業最關心的資訊列舉並說明如下：

1. 生產良率：為某產品在指定條件下的良率。例如依工單號碼、指定的時間區間、

某條生產線或指定機種等條件計算的良率。反之不良率即為 100%良率即可得。

2. 測試記錄：指的是生產過程的產測紀錄。最常查詢的條件是依工單號碼、指定機 種、指定某一台產品、不良的產測紀錄、指定的測試站等條件。

3. 生產效率：為在單位時間下的生產量。例如依單位時間(by 小時)、時間區間計 算得到的生產數量。生產效率也包含了各個機種的生產量(因每個機種所需耗費 的生產工時不同)、各生產線的生產量、各站的生產量與產線作業人員的生產量 等。

4. 不良原因排行：在生產過程中無可避免不良品的出現，不良原因是一個提供製程 或產品良率改善的重要資訊。根據 80/20 法則，從不良原因的排行中只要改善 了大部分的 80%不良等於良率提昇整體的 80%。

5. 重測次數：穩定的產品與測試環境(包含測試條件、人員及設備)在經過測試的時 候一次就可以 PASS 了，但有時候因產品本身的不穩定性或者是外在環境的變化 造成測試無法一次 PASS 時就會再執行二次以內的測試(依照對象企業規範，最 多不得測試超過三次，超過即判為不良品)。重測次數越多所代表的隱憂是，(1)

生產工時耗費越多就會造成效率不佳；(2)產品本身可能有品質上的瑕疵；(3) 外在環境的變異等。

6. 不良發生次數：承第 5 點，無論重測是否有 PASS，發生不良的次數提供一項很 重要的參考資訊，那就是表示產品或者是某機種有品質上的瑕疵(可能是硬體或 韌體的設計 issue)。另外，這也涉及到生產效率的問題。

7. 其他統計項目：根據使用者的需求客製所需要的統計資訊。

本研究的實驗查詢測試題目是根據對象企業所關心的資訊來設計查詢目的，並結合本 研究最關心的重點—不同資料庫綱要設計的效能來設計 26 道測試題目，並且依照實 際狀況與 PHP 程式所能完成模擬程度再縮減到 24 道測試項目。列舉如下，其中括弧 內的值表示資料表的欄位名稱。

1. 找出某工單不良率。

2. 找出某工單在某測試站(TestStation)的不良率。

3. 找出 2008/06/26 某工單在某測試站(TestStation)八個小時之間的每分鐘的累 積生產數量。

4. 找出某 PCBANO 在各測試程式(TestPrg)的測試記錄。

5. 找出某 PCBANO 在某測試站(TestStstion)的測試記錄。

6. 找出某工單的不良記錄。

7. 找出 2008/06/26 某測試人員在某工單於某測試站八個鐘頭的每分鐘累積生產數 量。

8. 列出某產品料號(PartNo)的不良(ErrorCode)種類並統計各類的數量由大到小排 序取前十名。

9. 找出 2008/06/26 某生產線某工單八個鐘頭內每分鐘的生產數量累積。

10. 計算某年某月不良率最高的產品料號，並列出其生產的投入數與不良數。

11. 計算 2008/06/26 八個鐘頭內每分鐘各生產線生產數量累積由大到小排序。

12. 計算某年某月所有工單的不良率由大到小取前 10 名。

13. 計算某料號(PartNo)在某年某月在各測試程式的平均工時。

14. 計算某 PcbaNo 在各測試程式的測試次數。

15. 找出某 PcbaNO 的產品在 Assign 製程的生產記錄。

16. 找出某 PcbaNO 在 Download 製程的生產記錄。

17. 找出某網卡卡號(MAC)在 Assign 完成製程後的全部資料。

18. 找出某網卡卡號(MAC)在 Download 完成製程後的全部資料。

19. 找出某工單在 Final Test 製程的平均工時。

20. 找出某工單在 Assign 製程時所測得產品錯誤資訊(意即測得錯誤的錯誤代碼與 該錯誤的產品數量)。

21. 找出某月份在進行 Final Test 製程時所測得產品錯誤形式與該錯誤形式所發生 的次數，並將該結果依照發生錯誤的次數由大到小排序。

22. 找出某 PcbaNO 在第三道製程完成時的測試狀態。

23. 找出某工單已完成 Assign 製程，但尚未進行 Download 製程的 MAC 號碼。

24. 找出某工單各製程測試次數小於兩次的紀錄。

將上述所有應用題目彙整如下表 3-10：

表 3-10 應用一覽表

應用編號 應用型態 應用目的 條件 執行頻率(秒)

1 良率 不良率工 工單號碼 28800

2 良率 不良率 工單號碼、測試站 60

3 生產效率 生產數量 工單號碼、時間區

間、測試站

60

4 產測紀錄 產測紀錄 PCBA 序號、產測程 式

隨機

5 產測紀錄 產測紀錄 PCBA 序號、測試站 隨機

6 產測紀錄 不良紀錄 工單號碼 7200

7 生產效率 生產數量 測試人員、工單號

碼、測試站、時間區 間

28800

8 統計 錯誤型式與 產品料號 7200

數量

9 生產效率 生產數量 生產線別、工單號

碼、時間區間

7200

10 統 計 + 指 定屬性

最高不良的 產品、投入 數、不良數

時間區間 14400

11 生產效率 各產線數量 時間區間 7200

12 良率 工單不良率 時間區間 14400

13 統計 平均工時 產品料號、時間區間 隨機

14 統計 測試次數 PCBA 序號 隨機

15 產測紀錄 生產紀錄 PCBA 序號、測試站 隨機 16 產測紀錄 生產紀錄 PCBA 序號、測試站 隨機 17 產測紀錄 生產紀錄 MAC 號碼、測試站 隨機 18 產測紀錄 生產紀錄 MAC 號碼、測試站 隨機 19 統計 平均工時 工單號碼、測試站 隨機

20 統計 錯誤型式與

數量

工單號碼、測試站 14400

21 統計 錯誤型式與

數量

時間區間、測試站 28800

22 指定 屬性測試狀

態(結果)

PCBA 序號、指定製 程

隨機

23 指定 屬 性 MAC 號碼

工單號碼、測試站 隨機

26 統 計 + 產 測紀錄

測試次數>2 之紀錄

工單號碼、測試次數 隨機

第四節 模擬環境建置

當以上區塊步驟都完成了之後，就開始模擬環境實做的部分。在模擬環境建置這 個區塊裡面，主要在硬體環境的建置，評估所需要的作業系統，資料庫平台，所要使 用模擬測的語言，儲存裝置的容量，記憶體的配置，應用前的查詢主機建置、網路環 境都會在此步驟去佈置完成。以上這些因素都是影響一個系統模擬的測試反映出對真 實資料庫環境的效能品質。

第五節 撰寫模擬程式

實體環境建置完以後，在此就開始撰寫模擬程式。依照使用者熟悉慣用的程式語 言加入每個綱要的SQL指令，去測試是否能正確查詢、刪除、新增預期中的綱要資料。

決定每個綱要的每個SQL指令的觸發條件是否為定時應用或是隨機應用，或者是只有 執行一次，務求所有SQL指令能接近真實生活中日常活動行為，正確擷取可供評斷的 數據。

第六節 模擬程式執行

當所有環境建置完成之後，就是執行模擬的程式，決定模擬的時間長度。如果模 擬時間過短，則有可能會因為資料庫的工作狀態尚未穩定而有損監測數據正確性。但 如果監測時間過長則可能監視效能數據過於邊際效應而過於浪費時間在模擬系統 上。在此之前必須要安裝與設定監視程式，決定監視效能的評斷選項，並且啟動效能 監視程式來收集系統模擬的效能數據。

第七節 模擬結果分析

最後，當所有綱要的效能數據都收集完成之後，經過資料編排，整理出效能數據，

甚至繪出效能圖型，這時候就可以提供資料庫使用的特性與效能數據去做一個比對。

如果此實際工作上的應用方式對綱要的資料是比較著重在資料寫入、異動與刪除，就 可能要注重在實體 I/O 的寫入效能。反之如果是以查詢較為主要行為，則可能就要比 較注意在資料在記憶體的快取命中率，邏輯 I/O 的查詢效能，實體磁碟機的資料詢效 能等等。

第四章 實驗設計

本研究是以無線網路寬頻產業的產品多樣化以及彈性的生產製程模式，依照對象 企業的資料庫綱要設計與生產模式，分別設計以水平式、垂直式與水平垂直混合式綱 要儲存產測紀錄的資料庫設計，就這三種綱要設計的資料庫作效能評估，以邏輯讀 取、實體讀取、實體寫入以及經過時間作為效能評估指標。

第一節 決定模擬範圍、取得綱要、資料與應用

經由實際訪談本研究對象企業以及參考半導體公司專業人士的建議綱要規劃得 知，要滿足多樣化產品、彈性生產製程以及擴充性至少要有下列資料表。茲說明如下：

 工單(Working Order)資料

儲存工單的資料，包含工單號碼、工單需求數量、生產產品料號等。

 產品(Product)資料

儲存產品資訊，包含產品料號、產品名稱、客戶代號等。

 生產製程路徑(Routing)

此資料表儲存每一種產品的生產製程測試路徑資訊，包含產品料號、產測程式、產測 順序等。

 產測程式(Test Program)資料

儲存生產測試程式資訊，包含產測程式的 ID、產測程式名稱、測試項目 ID 等。

 測試項目(Test Item)資料

儲存測試項目資訊，包含測試項目 ID、測試項目名稱、對應的產測程式等。

 測試站(Station)

儲存測試站資訊，包含測試站 ID、測試站名稱、對應產測程式與作業員等。

 生產測試紀錄(Production and Test Record)

此資料表真實記錄產品產測過程的所有資料，包含 PCBA 序號、工單號碼、產測程式

ID、測試項目、測試值、測試站 ID、測試人員、測試工時與產測日期等項目。綱要 一採用水平式的設計，截自該企業 2008 年 2 月至 8 月的產測紀錄經過統計之後得知 測試項目有 76 項，故在水平式資料表就需要有 76 個欄位對應這 76 項的屬性；綱要 二是以學界提出的垂直式綱要概念設計，依照企業的生產模式，經過正/反規化之後 產生的資料庫綱要；綱要三是以水平垂直混合式綱要的概念並參考某半導體封測公司 專業人士的建議所產生的資料庫綱要。一般在學界的研究資料庫綱要是以理論式的設 計，且資料筆數不大，在沒有實際運作的情況之下，相對顯得抽象而不具體。在本研 究中，本研究實際取得企業提供的資料庫綱要設計以及 Shop Floor 系統運作的資 料，這也是一般在研究中資料來源最難取得的部份，因涉及商業機密企業通常不願意 提供。在本研究中設計了三種資料庫綱要，故將取得的來源資料分別以三種綱要的型 式存在也是本研究的工作重點項目之一。本研究中的三種綱要，主要的差異在於產測 紀錄(test record)的儲存方式不同，儲存產測紀錄的資料表稱之為生產測試紀錄資 料表。在水平式綱要設計中是在水平式資料表內建立所有測試項目的欄位，以整合所 有產測紀錄所有的屬性；在垂直式綱要設計中是將所有產測紀錄屬性以資料的型態存 在；而水平垂直混合式綱要的設計是結合水平式與垂直式綱要的概念，除了有垂直式 資料表之外並設計一個水平式資料表儲存垂直式資料表中固定不變的屬性並附加彙 總欄位。

為了能更正確的評比三種 schema 模型的效能，擷取該企業在 2005/02~2008/11 的測試資料，水平式綱要的資料筆數為 5,858,066 筆；垂直式綱要資料筆數為 8,704,283 筆。在第二章的文獻探討已經有提過，水平式綱要的設計是所有屬性存在 同一列的 tuple；而垂直式綱要是一個屬性就一列 tuple，故在資料筆數上的差異是 可以接受的。

如前面所述，本研究統計了產測紀錄中的測試項目總共有 76 項加上 2 個固定屬 性(PcbaNo 以及 TestPrgID)，設計了含有 78 個屬性的水平式資料表，換句話說，這 個資料表整合了所有產測程式的屬性。由於此資料表含有所有的測試項目，而每一道

產測程式有所屬的測試項目，所以可想而知每一筆產測紀錄並非所有的欄位都是非 Null 值。因此，本研究計算了此生產測試紀錄資料表的稀疏度，得到總稀疏度為 97.71%，符合稀疏資料集的條件，為本研究的效能評估創造有利的實驗環境。

ER-Model 如圖4-1 所示。其中經正規化的程序將Testing Record 資料表重複的 屬性抽離出來成為Testing Result 資料表。之間以Uid 作為參考外來鍵，資料庫綱 要如圖4-2所示。

圖4-1 水平式綱要設計的ER-Model

圖4-2 水平式資料庫綱要

第二節 垂直式綱要資料庫綱要、資料與應用轉換

此綱要的設計主要是將Testing Record 的儲存方式從水平式轉換為垂直式。另 外，考量垂直式效能可能不佳的問題，在綱要中設計了一個WorkingIP 的資料表，主 要目的是記錄PCBA 在製造的狀況，在某些查詢的條件下可以參考此資料表以增進效 能。再來要說明的是，在第一種綱要設計中，一個產品是需要一個測試路徑(routing)，

但有些產品是相同的路徑，在實際運用上每種產品都需要設定測試路徑就生產管理的 觀念而言就會增加工時而沒有效率。故在此綱要中更改為以TestRouingID 為主鍵 (primary key)，從TestRoutingID 對應到產品料號，那麼在實際運用上每種產品只需要 以TestRoutingID 設定即可，而不需要重新設定測試路徑，就可以增加工時效率。

ER-Model 的表示如圖4-3。在真實世界中由於有眾多屬性，故在垂直式綱要的設計 上不可能如學者所提出的垂直式綱要理論，以三個屬性欄位就可以完整描述真實世界 所發生的事件，故必須再添加屬性才能完整表達。依照本研究的對象企業的彈性生產 型態，產測程式(TestPrgID)、測試項目(TestItemID)與測試值(Value)一定是彈性變動 的，故將這三個屬性轉換以垂直式方式記錄，但是若資料表只有這三個屬性是不足以 完整表達企業生產時所發生的事件。故需再加上PcbaNo(PCBA 序號)、WoNo(工單號 碼)、TestStationID(測試站ID)、Tester(測試人員)、Result(測試結果)、ErrorCode(錯誤 代碼)、Duration(測試耗費工時)以及CDT(紀錄建立日期時間)等屬性。資料庫綱要如 圖4-4。

圖4-3 垂直式綱要設計的ER-Model

圖4-4 垂直式資料庫綱要

第三節 混合式綱要資料庫綱要、資料與應用轉換

此綱要結合了水平式與垂直式綱要的設計。前面的章節已經提過，水平式綱要適 合變動不大的屬性，垂直式綱要適合多變彈性的屬性，故歸納了企業提供的產測紀錄

檔案裡的內容項目，將不變動者(PcbaNo, TestPrgID, TestStationID, WoNo, Result, ProductName)以水平式資料表(Testing Result)的型態存在，並在該資料表中附加一 個測試次數(ReTest)的屬性記錄 PcbaNo 出現的次數。由於產測程式、測試項目與測 試值會隨著產品的製程而有所不同，是擁有多變的特性，故將之規劃採用垂直式設計 的產測紀錄資料表(Testing Record)，它們之間以 PcbaNo 作為參考外來鍵。承前面 垂直式綱要設計的說明，混合式綱要的垂直式資料表如同前面垂直式綱要的設計，除 需要 TestPrgID、TestItemID、Value 等屬性外，還要將 WoNo、TestStationID、

Tester、Result、PcbaNo、ErrorCode、Duration、CDT 等屬性規劃進來。

經此設計之後可以發現在 Testing Result 與 Testing Record 資料表中會有一 些相同的屬性(PcbaNo、TestPrgID、TestStationID、WoNo、Result)。這樣的設計好 處就是可以減少如 COUNT 等 SQL 函數的運算，直接以 Select [欄位名稱] from [Testing Result]就可以取得，可以增加查詢效能。另外，此綱要中的測試路徑乃是 延用第一種綱要的設計方式。ER-Model 如圖 4-5 所示，資料庫綱要如圖 4-6 所示。

圖4-5 水平垂直混合式綱要設計的ER-Model

圖4-6 水平垂直混合式資料庫綱要

第四節 模擬環境建置

本實驗所用的工具都是業界常見並且被廣泛應用的軟體。後端資料庫管理系統為 Oracle 10g；前端作業系統為 Oracle Linux。實驗環境是使用兩台電腦完成，一台 是 Dell PowerEdge R610 機架式 Server 來當主要的資料庫伺服器。另一台則虛擬主 機 VMWare 伺服器。主要資料庫伺服器硬體規格如下：

 CPU ： 雙核心 Intel Xeon 2.0 GHz 5504 處理器 * 2

 RAM ： 2G DDR3 * 2

 Hare Disk ： SAS 146G 10K 硬碟 * 2 (Mirror)

而在軟體方面，本研究準備了四種程式來當測試環境，如下列表：

 OS : Oracle Linux，Kernel 2.6.18

 Database ： Oracle 10g，版本為 10.2.0.1.0 for Linux

 Program Langrage : PHP 5.3.2

 資料庫轉換程式 : Oracle SQL Developer 2.1.1.64

另一台 VMWare Server 所建構的虛擬主機當實驗應用前的查詢伺服器。查詢伺服 器的功能在於本實驗因為每個綱要都要跑長達 16 個鐘頭的資料庫應用，所以需要大 量的 SQL Where 條件當隨機變數，但是因為這樣的一個行為也會產生資料庫的 Cache、Memory、Disk I/O 變化，造成在效能監視上的不客觀，這時候就必須再準備 一台備有一模一樣的資料庫伺服器來當應用之前的 where 條件查詢。

第五節 撰寫模擬程式與執行

本章所設計的實驗概念是一天二十四小時三班制，一班八個小時的上班時間的線 上作業應用模擬。這個模擬所包含的項目有：

時間模擬

 查詢條件模擬

 資料寫入模擬

 資料刪除模擬

除了資料刪除之外，其餘的模擬項目都是在重現一條生產線對資料庫活動狀態展現。

從之前章節可以得知本研究是要對水平式綱要、垂直式綱要與混合式綱要這三種不同 類型的綱要來做效能的測試

因為之前研究所使用的資料庫是執行再 Microsoft Windows XP 上的 Microsoft SQL Server 2005 Development Version。而本研究是在 Linux 環境下測試，無法使 用 Microsoft SQL Server，所以在開始實驗之前，先使用 Oracle SQL Developer 程 式把 Microsoft SQL Server 的水平式、垂直式與混合式綱要匯入到 Linux Oracle 10g，並且安裝 PHP 來當作自動執行稿(script)。對於資料庫除了安裝與資料轉換之 外，本研究特地關閉 Oracle 資料庫的 Schedule 預設排程來降低其他因素對於資料庫 讀寫的影響，還有 Linux OS 本身所預設的 Cron Jon 也是停止啟動。

當資料從 Microsoft SQL Server 匯入到 Oracle 之後，再來就是程式部分。本研 究使用 PHP 的 Command Line Interface(CLI)來當執行稿。而模擬的方式是一個綱要 執行一次 16 個小時，前面八個小時是讓資料庫達到一個穩定的狀態，然後取後面八

個小時的效能數據，最後，一個綱要模擬執行完之後把 Oracle Server 重新開機，再 執行另一個綱要模擬，以此類推把三個綱要模擬完成。不論是水平式綱要、垂直式綱 要或是混合式綱要，所有的模擬都是同樣的流程步驟，而程式的執行主要有下列幾個 步驟：

1. 決定所有 Query 啟動時間

因為這 24 個 Query 的前置查詢時間所得到的回應數量很大，所以回應時間不一 致，因此在最一開始就要決定了正式應用的同步啟動時間，本研究是設定在 10 鐘後啟動所有正式應用，在此之間是做前置查詢與空迴圈去等待正式執行應用時 間到來。

I. 刪除掉 2008/06/26 日所有的記錄

因為有以時間做為 where 條件的定時應用來模擬線上資料庫記錄的產生，所 以本研究模擬資料寫入之前必須先刪除資料。而選擇 2008/06/26 這天來模 擬是因為這天的資料記錄是整個資料庫裡面資料最多的一天。

II. 查詢 VMWare 上的 where 條件

在做正式應用之前，每個綱要的 24 個 Model 都會先去 VMWare Oracle Server 查詢回他們所需要的 where 條件

2. 同時啟動模擬應用程式與 2008/06/26 資料寫入程式

3. 決定觸發應用頻率。在觸發頻率上分為兩類，第一類是定時觸發，第二類是隨機 觸發

I. 定時觸發。應用程式所針對的是常務性質的應用工作。例如：生產線的每分 鐘生產狀況、一個輪班的生產不良率狀況等等資訊。

II. 隨機觸發。隨機觸發則是非常務性工作的執行，例如：統計錯誤的形式與數 量、生產良率等等資訊來模擬一般工作時間業務隨機應用。

本研究的隨機頻率是採用指數分布來決定隨機觸發應用時間的長度。在機率 論和統計學中，指數分布（Exponential distribution）是一種連續機率分布。

指數分布可以用來表示獨立隨機事件發生的時間間隔；比如旅客進機場的時間間 隔、或是速食商店排隊客戶等待服務的時間間隔等等。

4. 啟動正式應用與 2008/06/26 資料寫入

I. 每分鐘都去查詢 VMWare Oracle Server 上 2008/06/26 的生產記錄的此刻 時間，並把查詢結果寫入到正式應用的 Oracle Server 上，做為生產紀錄的 寫入模擬。

II. 把前置查詢放在陣列變數上的資料用 PHP 所提供的亂數函數取出一組供正 式應用使用。

5. 判斷是否超過程式持續執行時間

下列圖 4-7 為整個完整的程式模型架構圖。

圖4-7 程式模型架構圖

應用結束後所得到的數據結果，在本研究中以執行應用所耗費的時間(Elapsed

Time)、實體讀取時間(Physical Reads)、邏輯讀取(Logical Reads)、以及實體寫入 (Physical Writes)為評比指標，數據比較與結果分析將在下一章做一個說明。

第五章 實驗結果與分析

本章分為六節，前五節分別在於每個綱要的 Logical Reads、Physical Reads、

Physical Writes、sda3 Busy、Elapsed Time 數據展現與效能評比，第六節則在這 五個數據上之分析。依據前面章節所述各校能指標，我們所關心的資料庫效能就是在 於查詢與資料異動，當這些效能指標越低，表示效能越好，越不會去耗費磁碟機資源，

所以得到資料的等候時間會越短，得到資料的速度會越快。

第一節 邏輯讀取

在資料庫邏輯讀取方面，Logical Reads 是當應用程式對資料庫做搜尋時，資料 庫會對記憶體裡面的 Buffer 去做搜尋，資料庫會把所搜尋到符合條件的資料擺放在 記憶體裡，以便當資料庫再次搜尋相同資料庫時可以直接從記憶體讀取資料，增加查 詢速度。而邏輯讀取上的效能表現也是在資料庫綱要中最主要的效能指標。

表 5-1 各綱要 Logical Reads 比較 平均值(Blocks/sec) 名次 Horizontal 87492.83 3 Vertical 9869.54 2 Hybrid 6179.66 1

圖5-1 Horizontal Logical Reads

圖5-2 Vertical Logical Reads

圖5-3 Hybrid Logical Reads

在邏輯讀取方面可以看到表 5-1，Hybrid 效能最好，Vertical 綱要次之，

Horizontal 綱要效能表現最差。因為混合式綱要是利用水平式綱要的優點與垂直式 綱要的優點所提升出效能的表現。當一個應用程式提出需求後，在混合式綱要裡的水 平式綱要會搜尋出在裡面符合條件的資料。再來會去搜尋混合式綱要裡面的垂直剛要 是否有符合條件的資料，如果有符合條件資料，則只讀取出符合條件的資料，不會像 水平式綱要會讀取到 Null 屬性資料。也不會像垂直式綱要要一直搜尋出相關資料的 屬性條件。

第二節 實體讀取

Physical Reads 是對磁碟機本身做查詢動作，這是所有查詢最基本的讀取動作，

因為資料都是存放在磁碟機裡，當資料庫接收到應用程式的指令之後，會先做查詢的 就是在實體磁碟機。而且對於實體讀取效能上的直接影響還有直接寫入磁碟因素，這 兩項因素都是對磁碟機直接做存取動作，所以會有交互影響的因素在。而且實體磁碟 機的動作是機械式的動作，所以在電腦所有周邊裝置裡，磁碟機的讀取動作相對比較 慢，因此在本章與接下來在第三節會對磁碟機實體寫入做數據說明。

表 5-2 各綱要 Physical Reads 比較 平均值(Blocks/sec) 名次 Horizontal 9547.81 3 Vertical 2448.29 1 Hybrid 6566.45 2

圖5-4 Horizontal Physical Reads