Oracle - compress table 表壓縮

Oracle在11g之前版本中的壓縮技術應用場景非常的有限，多用於只讀的數據庫倉庫中，要求表中的數據是只讀的。因此在OLTP的環境中很少見到數據壓縮技術的身影。

11g推出的數據壓縮技術已經打破了這個束縛，Oracle通過只存儲在保存壓縮元數據的特定表（符號表，symbol table）中有重複的列值的單個副本，消除了block中所有重複的值。Select 壓縮數據時的I/O也大大減少，進而，壓縮技術延展到了OLTP領域。

1. 10G與11G的效能比較

1.1 10G的測試

使用版本 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1.1.1 創造一個 300M的一般Table

SQL> create table t as select * from all_objects;

Table created.

SQL> insert into test select * from test;

197424 rows created.

SQL> /

394848 rows created.

SQL> /

789696 rows created.

SQL> /

1579392 rows created.

SQL> commit;

SQL> select segment_name,bytes/1024/1024 MB from DBA_segments where segment_name='T';

SEGMENT_NAME                         MB

------------------------------      ----------

T                                                      343

1.1.2 使用CTAS 來創造Table

SQL> create table test_compress compress as select * from t;

Table created.

SQL>  select segment_name,bytes/1024/1024 MB from DBA_segments where segment_name = 'TEST_COMPRESS';

SEGMENT_NAME                         MB

------------------------------      ----------

TEST_COMPRESS                         104

1.1.3 比較一般table 與 壓縮過後table 的佔用空間

SEGMENT_NAME                         MB

------------------------------         ----------

T                                                      343

TEST_COMPRESS                        104

幾乎節省了 1/3的空間 ，Oracle號稱可以達到 3.5 : 1 的壓縮比

1.1.4 比較一下select 效率 (因排版關係則使用截圖

未壓縮

有壓縮

cost 從9556 降到 2875 > 少了69%

consistent gets 從 43320 降到 12909 > 少了70%

壓縮過後 select 的 I/O 讀取數與Cpu使用率大幅度減少

1.1.5 比較一下 update 效率 

Update 壓縮過後的 Table 增加了非常多的時間 , 這是因為 Oracle進行 Update時會進行解壓縮所以才導致處理時間太長

1.1.6 update 後 compress table 是否會被解壓縮 ?!

很明顯的 Update 後資料被解壓縮了, 而且並不會再自動壓縮 , 除非有啟動一些條件 Oracle 才會再進行壓縮 ( 資料量接近 PCTFREE )

以上測試証明了10G使用壓縮技術對倉儲的 Table 效果最好, 很常在Update 的 Table 就不建議了 

1.2. 11G的測試

使用版本 

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.2.1 創造一個 300M的一般Table

SQL> create table test as select * from all_objects;

Table created.

SQL> insert into test select * from test;

92640 rows created.

SQL> /

185280 rows created.

SQL> /

370560 rows created.

SQL> /

741120 rows created.

SQL> /

1482240 rows created.

SQL> commit;

Commit complete.

SQL>  select segment_name,bytes/1024/1024 MB from DBA_segments where segment_name='TEST' ;

SEGMENT_NAME          MB                                                    
-----------------           ----------                                                                    
TEST                                 336                                

1.2.2 建立 COMPRESS FOR OLTP 壓縮技術的Table

SQL>  create table test_compress COMPRESS FOR OLTP as select * from test;

Table created.    

SQL>  select segment_name,bytes/1024/1024 MB from DBA_segments where segment_name='TEST_COMPRESS' ;

SEGMENT_NAME               MB                                                  
----------------------        ----------                                                                    
TEST_COMPRESS              120        

1.2.3 比較一般table 與 壓縮過後table 的佔用空間

SEGMENT_NAME              MB                                                    
-----------------               ----------                                                                    
TEST                                     336      
TEST_COMPRESS              120                                                                  

      

幾乎節省了 1/3的空間 ，Oracle號稱可以達到3.5 : 1 的壓縮比

1.2.4 比較一下select 效率 (因排版關係則使用截圖

未壓縮

有壓縮

cost 從12457 降到 4156 > 少了66%

consistent gets 從 42244 降到 15615 > 少了63%

很明顯的 壓縮過後 select 的 I/O 讀取數與Cpu使用率大幅度減少

1.2.5 比較一下 update 效率 

壓縮過的 table update執行時間比較快


結論：因為 11G 在壓縮技術的提升進行 Update 異動時不用在對 Table解壓縮就可以直接對block 做異動，所以整體處理時間大幅下降，因此對於重覆值相當多的 Big Table 使用壓縮技術比建立一般的table還來得有效率的。

2 壓縮的種類 

Basic compression：只能用直接路徑加載插入的數據，並支持有限的數據類型和SQL操作。
OLTP compression：用於OLTP應用程序和壓縮任何SQL操作操縱 。
Warehouse compression和Archive compression：達到最高的壓縮級別，因為它們使用混合列壓縮技術 。(僅限於建立在 Exadata存儲上

2.1 11g壓縮技術 BASIC 與 OLTP 的效能比較

2.1.1 先建立TABLE

SQL>create table Compression_BASIC COMPRESS as select * from t; 

Table created.

SQL>create table Compression_OLTP COMPRESS FOR OLTP as select * from t; 

Table created.

看一下兩個Table 壓縮的種類
SQL> SELECT owner,table_name, compression, compress_for FROM dba_tables where compression = 'ENABLED';

TABLE_NAME                     COMPRESS         COMPRESS_FOR
------------------------------       --------                  ------------
COMPRESSION_BASIC      ENABLED             BASIC
COMPRESSION_OLTP       ENABLED             OLTP


SQL> SELECT segment_name, bytes / 1024 / 1024 MB
  2    FROM DBA_segments
  3   WHERE segment_name IN ('T', 'COMPRESSION_OLTP', 'COMPRESSION_BASIC') order by 2 desc;

SEGMENT_NAME                              MB
------------------------------                 ----------
T                                                           198
COMPRESSION_OLTP                       72
COMPRESSION_BASIC                      58

*BASIC 的壓縮比則是較 OLTP 優

2.1.2 Select 效能比較 (因排版關係則使用截圖

BASIC 

OLTP

*BASIC 的 CPU 使用率與 I/O讀取數則較優

2.1.3 Update效能比較 (因排版關係則使用截圖

蝦米!!!! 還是 BASIC 較優

2.1.4 模擬實際的狀況

說明一下之前的實驗都是以 CTAS (Create table as select * from ) 的方式建立資料
現在模擬一下符合實際 OLTP DB 的 table 被分條式 insert 的狀況

SQL>  truncate table Compression_BASIC;

Table truncated.

SQL>  declare
  2     type t_list is table of t%rowtype index by binary_integer;
  3     i integer;
  4
  5     t_infos t_list;
  6   begin
  7     select *
  8     bulk collect into t_infos
  9     from t;
 10
 11     for i in 1..t_infos.count loop
 12        insert into Compression_BASIC values t_infos(i);
 13
 14        if (mod(i,1000)=0) then
 15           commit;
 16        end if;
 17     end loop;
 18
 19     commit;
 20   end;
 21   /

PL/SQL procedure successfully completed.


SQL> truncate table Compression_OLTP;

Table truncated.

SQL>  declare
  2     type t_list is table of t%rowtype index by binary_integer;
  3     i integer;
  4
  5     t_infos t_list;
  6   begin
  7     select *
  8     bulk collect into t_infos
  9     from t;
 10
 11     for i in 1..t_infos.count loop
 12        insert into Compression_OLTP values t_infos(i);
 13
 14        if (mod(i,1000)=0) then
 15           commit;
 16        end if;
 17     end loop;
 18
 19     commit;
 20   end;
 21   /

PL/SQL procedure successfully completed.


SQL> SELECT segment_name, bytes / 1024 / 1024 MB
  2    FROM DBA_segments
  3   WHERE segment_name IN ('T', 'COMPRESSION_OLTP', 'COMPRESSION_BASIC') order by 2 desc;

SEGMENT_NAME                              MB
------------------------------                   ----------
T                                                           198
COMPRESSION_BASIC                     176
COMPRESSION_OLTP                       80

這時候會發現 BASIC 的壓縮比變的比較差

2.1.5 比較 Select 的效能

BASIC

OLTP

cost 從6070 降到 2752 > 少了54%

consistent gets 從 22606 降到 9458 > 少了58%

最後再補一張未壓縮 table 的情形 ( 使用 OLTP 壓縮過的效能都優於未壓縮的2~3倍  )

2.1.6 結論

Oracle 11g推出的Advanced Compression 突出表現在兩個方面的優勢：其一是對OLTP系统操作的高效壓縮上，另一方面是現在對RMAN、DataPump和Dataguard等多範圍壓縮支持。



查詢有壓縮的table
SELECT owner,table_name, compression, compress_for FROM dba_tables where compression = 'ENABLED';

查詢有壓縮的 partition table
SELECT table_owner,table_name, partition_name, compression, compress_for FROM dba_tab_partitions where compression='ENABLED';

alter index rebuild online 所引起 DB hang 事件

大家都知道 alter index rebuild online  是可以同時進行DML 操作的，但要是操作不正確有可能造成 DB hang 現象

以下做一個實驗  (DB版本 10.2.0)
--建一個 table
SQL> create table test(a int,b varchar2(64));

Table created.

--插入資料
SQL> begin
    for i in 1..1000000 loop
    insert into test values(i,'ok');
    commit;
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

--建立index
SQL> create index idx_a1 on test(a);

Index created.

--更新資料 , 建立一個lock  , 不要commit
SQL> update test set a=1000000 where a=110;


--開啟另外一個視窗,在線重建索引
SQL> alter index idx_a1 rebuild online;

發現此命令過了很久還不能執行完畢
查一下 v$lock
select * from v$lock where block >0;

看來  rebuild online 正在等待 sid 160 ，也就是一開始的 update 的指令， rebuild online 之前的未完成的事務它會去等待它，假設這時候又有一個DML操作 ?!   我們再繼續實驗下去。

--開啟另外一個視窗，進行 update

SQL> update test set a=1000000 where a=111;   

過了10分中，還沒執行完畢，果真整個 hang 住

我們查一下 lock 

可以明顯的看到 text 這個table 目前鎖的來源(TX)是 SID 160 也就是 一開始的 update 的指令，只要這個SID 160 事務不提交的話，後面的業務也無法作業。這邊也看到 rebuild online 對表加了個Level 4 的鎖(share: 共享鎖 禁止其他DML的操作)把整個 table lock 住後面的業務都再等 rebuild online ，導致系統 hang 在這邊

第一 kill 掉 rebuild online  就可以運作
第二 kill 掉 在 update 的業務就可以運作


同樣的實驗在11G 上驗證  (DB版本 11GR2)

--建一個 table
SQL> create table test(a int,b varchar2(64));

Table created.

--插入資料
SQL> begin
  2  for i in 1..1000000 loop
  3  insert into test values(i,'ok');
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

--建立index
SQL> create index idx_a1 on test(a);

Index created.

--更新資料 , 模擬出一個lock  , 不要commit
SQL> update test set a=1000000 where a=110;


--開啟另外一個視窗,在線重建索引
SQL> alter index idx_a1 rebuild online;

發現此命令過了很久還不能執行完畢
查一下 v$lock
select * from v$lock where block >0;

看來  rebuild online 正在等待 sid 33 ，也就是一開始的 update 的指令

這時候 開啟另外一個視窗
SQL> update test set a=1000000 where a=111;

1 row updated.

結論是: 11G 修正了這個問題 ，rebuild online  之後的業務可以正常運作系統不會 hang ，但 rebuild online 還是會等到所有 update 完成後，才能執行完成

Oracle - 自定義 function based index for TO_CHAR

建立  function based index
 CREATE INDEX test_idx ON test(TO_CHAR (systime, 'yyyy-mm-dd'));



比較一下 使用 function index 與一般index 的效能

這是使用一般index 的Plan

這是使用 function index 的Plan  , Cost 很明顯的降下來 

Oracle - Archive log file 某一個目錄發生儲存失敗 ?!

我們知道 Archive log file 可以設定 log_archive_dest_x  來多路徑存放

某一天腦袋有一個疑問，要是某一個儲存點壞掉了，Oracle 會怎麼處理呢？！

DB 有一個參數可以調整  LOG_ARCHIVE_MIN_SUCCEED_DEST，表示設定的路徑裡成功的最小數量，設定１表示只要有一個成功就可以！

再設定 log_archive_dest_x　時也有一個參數，設定為mandatory則表示強制必須歸檔，參數 mandatory 會忽略 LOG_ARCHIVE_MIN_SUCCEED_DEST 參數

指令如下:

alter system set log_archive_dest_2='location=C:\archive\ mandatory' scope=both;

查詢的指令：
select  destination,binding,target,status from  v$archive_dest; 

在Binding 值 為 OPTIONAL 選擇性歸檔(預設)，值為mandatory為強制必須歸檔


但要是 mandatory 的目錄發生儲存點壞掉，Oracle就會忽略該會掉的目錄即使是mandatory 也是一樣





参考官方文檔
Managing Archive Destination Failure
http://docs.oracle.com/cd/B19306 ... chredo.htm#i1006719

Oracle 在 object 和 public synonym 相同名稱時的讀取順序

Oracle 在 object、view、synonyms和 public  synonym 相同名稱 讀取順序的實驗

首先先創造測試用的table
SQL>  create table test ( dest varchar2(20));

Table created.

SQL>  create table test2 ( dest varchar2(20));

Table created.


並且insert into 值進去
SQL> insert into test values('test');

1 row created.

SQL> insert into test2 values('test2');

1 row created.

SQL> commit;

Commit complete.


然後建立 Synonym 出來 , Public Oracle才能允許建立相同名稱
SQL> create public synonym test for vincentyuan.test2;

Synonym created.


驗證開始
SQL> select * from test;

DEST                                                                          
--------------------                                                          
test                                                                          

SQL> drop table test;

Table dropped.

SQL> select * from test;

DEST                                                                          
--------------------                                                          
test2                      




結論是：Oracle在user 的object 與 public synonym 名稱相同時，會先讀取時user 的object
　　　　要是user 沒有此名稱object時，才會讀取 public synonym。



以下是優先順序：
(1) 本用戶下是否存在 table 或 view
(2) 如果 table 或 view 不存在，則解析 synonym
(3) 如果 Private synonym 不存在，則查找同名的  public synonym 是否存在
(4) 如果同名的  public synonym 不存在，則返回00942錯誤

Oracle - exp 出現了 exp-00006 的解決方案

今天DB在exp 備份時錯誤失敗, 檢查一下 log 發現了

EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

查了一下原因是, 昨天有替partition table 增加了 INTERVAL 的功能 ( 自動新增partition 分割區)

exp 並不支援此功能 必須使用 expdp

後來更改script後就備分成功！！

Oracle - job 突然不能運行

某一天 DB 的監控job 狀態是 STOPPED , error msg :REASON="Job slave process was terminated"

alert.log 出現了 , Ora-07445 的錯誤 , 網路上並無明顯的解決方案

這時又發現會主動監控DB並會發信給DBA的job 還有 PROCEDURE 裡有使用到 UTL_INADDR.get_host_name 、UTL_INADDR.get_host_address　的job都的會出現以上的狀況 ,其餘的job 都是正常。

一開始並無頭緒, 後來想想既然不能發信 會不會是ACL 出問題!!!查了一下 ACL授權名單, 發現 user 並無連線的ACL授權

進行ACL授權時發現出現 ORA-03113 end of file on communication channel , 重新restart db 狀況也是一樣會出現斷線

在查一下 db 各參數 發現 java_pool_size 只有 4M (太少 , 雖然有設memory_target > 0
但還是把相關pool 設定一個最小size

 shared_pool > 450m
 java_pool_size > 150M
 streams_pool_size > 100M
 large_pool_size > 100M 

restart db 後 在重新授權ACL, 執行job 發信就一切正常



總結： java_pool_size 設太小 也會造成db的一些問題!!!!