ORACLE 報表資料庫開發設想

OLAP 稱為線上分析,其實就是報表系統,和BI系統. BI系統是套產品在這裡不談. 分析和報表其實都是用預存程序開發出來的,一個是線上提供給使用者使用,另一個是離線提供給同事使用的.

 線上分析目前來看應用不廣,所涉及到的資料量相對比較小,只是使用者量比較大

1 使用者只關心自己的. 比如購買次數,購買總額,等使用者所關心的資料

2 產品關聯,比如說購買該產品的使用者還購買了其他什麼產品!

3 產品火紅度;

而報表涉及到所有的資料,包含曆性資料. 每個部門有不同的報表要求,每個同事,每個部門領導都會提些自己關心的報表.

ORACLE 資料庫 是從交易型資料庫發展過來的,處理分析型資料時候總有點力不從心!

1 開始安裝資料庫時候選擇OLAP 它會自動調整下必要的參數

2 設定64-128KB的資料區塊 而不是預設的8KB

3 分層設計, 因為報表眾多,如果直接從原始表擷取必然造成效能大阻塞. 因此要把基礎的,共同的做成資料表,其他報表直接從這些基數表裡擷取資料. 這樣就極大減少了數量.

 a 抽取源表層  b  基礎資料表層 C 共同層 D 部門層

如何分? 哪些資料做在哪裡,是需要多業務瞭解和熟悉,對公司和各個部門的報表瞭解,方能有大概的想法,  這些不一定一開始就能搞定的,需要不斷地最佳化中.因為短時間內無法對業務的徹底熟悉.

4  任務調度:

 採用儲存過程和軟體包來做每個報表,每個表的資料產生. 那麼這些任務之間必然產生了依賴.

 採用ORACLE 本身的JOB來調度,採用預存程序裡麵包含預存程序,也就是說JOB調度啟動預存程序,啟動預存程序把相關的預存程序包含在一起.

該方法不太靈活,擴充性比較差,維護比較難!

應該採用crontab 方式的調度. 比如說寫個輪休的JOB 該JOB每隔5-10分鐘運行一次. 該JOB只調用一個預存程序. 預存程序啟動任務,任務是軟體包或者是預存程序.

該預存程序 讀取任務資訊表, 任務依賴表,何時啟動該任務, 並監督任務健全狀態和警示.

5 軟體包裡 一般包含 a 抽取預存程序; b 清單預存程序;c 日資料存放區過程; d 周資料存放區過程; e 月預存程序;f 移動到結果表的預存程序;g 復原的預存程序;h清理到期資料的過程

a 抽取預存程序 把源表的資料幫浦到暫存資料表中,這裡指任務所需資料的表; 這裡的暫存資料表是物理的 以_TMP命名的.

之所以採用暫存資料表法,因為ORACLE 對錶串連成本很高, 尤其是多表的LEFT JOIN +LEFT JOIN . 採用暫存資料表可以把必要的欄位,必要的行形成較小的資料區塊.

b 清單預存程序

清單的意思是 這部分資料要臨時存上1-3個月,主要的是去重的要求, 求一個月的人數不能從每天的人數SUM過來. 以_LST命名 這個清單要做成分區表 月,日或者小時的分區.

C 日資料過程 是從清單裡擷取資料進行統計,當然如果沒有清單直接從抽取的暫存資料表中獲得

D 周過程, 周這個時間很麻煩的事情 尤其涉及到跨年的周. 如果不去重可以直接從日資料中提取

E 月過程 同上.

F 過程: 是避免結果表的更新影響到領導的查詢, 所以先把所有的資料整合在一個臨時匯總表中,再移動到結果表

G過程:是個重要的過程,它主要功能是實現復原UNDO操作,因為依靠ORACLE自身的UNDO機制是很慢的.

   處理月報表每天都累加一次的情況,或者是清單過於龐大,保留一個月太多了,或者說掃描一個月的資料太久了.那麼採取每天跑一次,每天加一次.

 類似是 update table set value=value+new_value;

這樣的情境,如果運算過程中發生了故障,就會發生前後資料不一致,只更新了30%的資料就故障了. 所以更新前,把新的值儲存在復原表中.每次運行前調用復原過程,檢查復原標誌

如果非正常結束,那麼提取相應的資料 對資料進行 UPDATE TABLE SET VALUE=VALUE-NEW_VALUE 操作;

H 清理過程: 這裡主要是清理暫時保留一段時間的清單表.

 每個過程運行前 都要做 TRUNCATE TABLE XXXXX_TMP 的清空表的操作. 如果涉及到清單和目的表,那麼要DELETE TABLE  WHERE YYYY= XXXX  因為避免得到重複的資料.

 

6 遊標批處理

 因為資料量很大成百上千萬行, 不可能一次性地提交上去. 比如  insert into table_name  (xx,yyy,zz,hhh,) select xx,yy,zz,hh from table_tmp left jion table_tmp2; 會很慢滴

採用遊標和批提取方式

cursor  cur_day_result is  --計算月登入人數和次數  

         select      provcode from table_b group by 1;

  type type_provcode        is table of oss_openplat_truslogin_day_lst.provcode%type index by binary_integer;

  l_ary_provcode            type_provcode;

begin

    open cur_day_result;
    loop
      fetch cur_day_result bulk collect into

        l_ary_provcode 

    limit g_batch_size_n;   --- 這裡可以控制提取行數

      forall i in 1..l_ary_provcode.count
          insert into login_day_lst
          ( provcode)

          values(l_ary_provcode )

       commit;   -- 這裡把一部分資料提交到資料上

 end loop

 

7 複雜的要求:

經常有 連續三個月的購買使用者人數, 日增加額和增加率, 當天與上個月當天的比 即同比; 月累加值.

採用MERG INTO和 UPDATE 的方式會比較慢. 直接採用INSERT 和DELETE

比如 日期, 分類1,分類2,分類3,統計值,統計值月累加;

通過 日資料過程和月資料過程 分別產生了資料

日期, 分類1,分類2,分類3,統計值;

日期, 分類1,分類2,分類3,統計值月累加;

分別insert into 到 匯總表 (日期, 分類1,分類2,分類3,統計值,統計值月累加)

insert into 匯總表 (日期, 分類1,分類2,分類3,統計值,統計值月累加)  select 日期, 分類1,分類2,分類3,統計值,0 from table_day_tmp;

把不屬自己的欄位值0

最後 匯總表在移動結果表時

select  日期, 分類1,分類2,分類3, sum(統計值),sum(統計值月累加) from 匯總表 group by 日期, 分類1,分類2,分類3

 

8  寬表 行轉列

思想是 通過增加列的數量來減少行的數量.  比如解決 連續三個月的購買使用者人數 的報表需求

我們有 使用者表,使用者購買記錄表;  如果我們的使用者相對比較少 有1百萬吧 如果這1百萬人中 12個月購買記錄行數達到2億行.平均每個月有1千6百萬行;

從3個月的記錄中大約5千4百萬統計連續3個月的使用者,應該會比較慢的.

假如做個寬表  使用者 1月購買次數,2月購買次數.......12月購買次數, 第一次購買時間,最後次購買時間

那麼這個表只有1百行的記錄

select  使用者

from table

where 1月購買次數 > 0  and 2月購買次數>0 and 3月購買次數>0

 

9 報表分等級

如果說 所有的報表要在早上上班9前跑出來,這是個比較難以完成的任務. 在資料量非常少的情況下 比如20G 用 1台機器 32G記憶體 8個CPU 多個硬碟的RAID

確實可以達到要求. 如果資料量達到500GB級以上 就會出現麻煩事了.

因此 覺得要把報表分層級 實現優先順序處理

A 級報表 在9:00前跑出 這一般都是公司業務核心報表 高層和老闆 CTO CEO 這類人要看的

B級報表 在中午12:00前跑出 這個各部門領導關心的

C級報表 在下午下班6:00前  這個就是普通員工

D級報表 在晚上跑出來的;  比如監控之類的

 

10 RAC叢集

RAC並不能提升效能 使用RAC關鍵是把任務分在不同節點上

A節點做主要的管理節點;

B節點做資料幫浦同步節點,一當資料大的話必須24小時全天候時時抽取,時時同步;

C節點報表節點 ; 主要跑各個報表的任務過程

D節點頁面節點  報表如果以HTML方式展現來,那麼頁面伺服器訪問的資料庫必須單獨的節點,避免其他動作影響到該節點.

E節點隨機查詢節點: 這個節點基本上做自己人查詢資料,核對資料,更改資料的節點.

 

A 節點是RAC的管理節點 負責整個叢集塊的管理和鎖的處理. 所以為了不影響效能必須單獨用一個節點來負責整個叢集的通訊

B 節點 要做24小時資料插入工作 也要單獨使用一個

C 節點 重量級節點 該節點使用的機器比其他節點效能高出數倍. 記憶體達要更大 才能記憶體進行大量資料區塊的操作,而不是被LINUX交換分區掉了

D節點 面子節點 領導老闆同事 訪問頁面的快慢體驗就在這個節點上,如果跟其他節點合并在一起,容易被其他節點的任務把記憶體給佔了.

 

7 分區表

一般分區達到2層 就是雙分區.當有的情況下要達3層 物理月表 月表下日分區 日分區下是LIST分區. 物理月表 是人工給表起名字 "TABLE_201206 "

這樣要不斷地人工建新表, 而預存程序訪問時候需要從資料字典裡獲得該表名, 要不採用時間拼接法 然後採用動態語句.編寫起來比較繁瑣.

分區表 ORACLE建議 大於2G的表進行分區. 那麼最小的分區應該是容量多大? 這要涉及到機器效能和IO輸送量,以及一個分區全表掃描時間的忍受程度.

如果分區1個G  而全掃一次要10分鐘,那麼自然不可接受. 那麼一個分區應該在1分鐘內完成全掃描

 

11 索引

基本上不建議在表裡建索引,採用多層分區表,實現全表掃描. 因為索引會導致反而比全掃描慢,索引在大規模資料更新的時候維護成本高. 會極大影響各個報表的已耗用時間.

索引大部分用在結果表上,因為結果表插入的資料量最少,更新的頻率最低,維護成本最小.查詢效率最高.