[20180814]慎用檢視錶壓縮率指令碼.txt
[20180814]慎用檢視錶壓縮率指令碼.txt
--//最近看exadata方面書籍,書中提供1個指令碼,檢視某些表採用那些壓縮模式壓縮比能達到多少.
--//透過呼叫DBMS_COMPRESSION.get_compression_ratio確定壓縮比.例子如下:
--//測試版本11.2.0.4.
declare
blockct_comp number;
blockct_uncomp number;
rows_comp number;
rows_uncomp number;
comp_rat number;
comp_type varchar2(40);
begin
dbms_compression.get_compression_ratio('&&tblspc','&&ownr','&&tblname',null, dbms_compression.comp_for_oltp, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line('Compression type: '||comp_type||' Compression ratio (est):'||comp_rat);
dbms_compression.get_compression_ratio('&&tblspc','&&ownr','&&tblname',null, dbms_compression.comp_for_query_low, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line('Compression type: '||comp_type||' Compression ratio (est):'||comp_rat);
dbms_compression.get_compression_ratio('&&tblspc','&&ownr','&&tblname',null, dbms_compression.comp_for_query_high, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line('Compression type: '||comp_type||' Compression ratio (est):'||comp_rat);
dbms_compression.get_compression_ratio('&&tblspc','&&ownr','&&tblname',null, dbms_compression.comp_for_archive_low, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line('Compression type: '||comp_type||' Compression ratio (est):'||comp_rat);
dbms_compression.get_compression_ratio('&&tblspc','&&ownr','&&tblname',null, dbms_compression.comp_for_archive_high,blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line('Compression type: '||comp_type||' Compression ratio (est):'||comp_rat);
end;
/
--//好奇心我想看看生產系統一張大表能達到多少.我執行上面的指令碼,結果等大約2-3分鐘沒有結果出來,我馬上中斷處理.
--//我當時想既然大表可能分析資料量大,換1個點的表看看.
--//結果執行後包如下錯誤:
ERROR at line 1:
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP3$97116 TABLE!
ORA-06512: at line 6
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1136
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1114
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$97116 TABLE!
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 9
--//BTW:我們生產系統有系統觸發器,禁止使用者drop和truncate表.這樣導致指令碼執行報錯.
--//我看了一下CMP3$97116,CMP1$97116表結果,和分析表結構一致.
CREATE TABLE xxxxxx_yyy.CMP4$97116
(
ZYH NUMBER(18) NOT NULL,
....
YB_DBZ VARCHAR2(4 BYTE)
)
TABLESPACE xxxxxx_yyy
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOLOGGING
COMPRESS FOR OLTP
~~~~~~~~~~~~~~~
NOCACHE
NOPARALLEL
MONITORING;
--//可以看出DBMS_COMPRESSION.get_compression_ratio操作很簡單,先建立與分析表一樣的表結構以及對應壓縮模式的表,然後
--//匯入資料後比較分析壓縮比.
--//這樣要耗費大量表空間與資源做這個工作,在生產系統要小心謹慎.
--//我事後認真看了<深入理解ORACLE Exadata> P98頁.而是講樣本資料插入一個臨時表中.同時壓縮版本的臨時表也被建立,比較壓縮
--//版本和非壓縮版本的大小就可以得到壓縮率.
--//(注:我看到的不是臨時表,而是真實的表,看上面的表定義.或許作者理解的臨時表非我理解的臨時表)
--//我不知道取樣比例是多少,總之在生產系統執行該指令碼還是要小心.
--//另外書P101提到 壓縮助手的一大亮點是能夠在非exadata平臺上執行,在真正遷移資料到exadata平臺之前,它能夠提供足夠的資訊
--//幫助你做出合理的選擇.這麼講非exadata平臺還是能夠建立hcc壓縮模式的相關資料,只不過你不能檢視.
--//我曾經在dg上檢視壓縮表資訊,連結[20150727]exadata壓縮HCC與dataguard.txt=>http://blog.itpub.net/267265/viewspace-1753362/
XXXX@zzzzdg2> select * from t where rownum<=1;
select * from t where rownum<=1
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
--//在家裡測試的結果.
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t as select * from all_objects ;
--//反覆插入,提示要大於1000000rows才可以.
SCOTT@test01p> select count(*) from t;
COUNT(*)
----------
1437952
--//佔用192M.
Compression type: "Compress Advanced" Compression ratio (est):3.5
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: "Compress Query Low" Compression ratio (est):8.6
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: "Compress Query High" Compression ratio (est):16.3
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: "Compress Archive Low" Compression ratio (est):16.6
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: "Compress Archive High" Compression ratio (est):21.7
PL/SQL procedure successfully completed.
--//如果單獨執行如下:
--//在sys使用者下建立觸發器禁止drop表.
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
BEFORE TRUNCATE OR DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'TABLE' AND ora_dict_obj_owner = 'SCOTT' and ORA_DICT_OBJ_NAME not like 'SYS\_JOURNAL\_%' escape '\'
THEN
raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
END IF;
END;
/
--//如果單獨執行如下:
/* Formatted on 2018/8/14 8:49:08 (QP5 v5.269.14213.34769) */
set serveroutput on
DECLARE
blockct_comp NUMBER;
blockct_uncomp NUMBER;
rows_comp NUMBER;
rows_uncomp NUMBER;
comp_rat NUMBER;
comp_type VARCHAR2 (40);
BEGIN
DBMS_COMPRESSION.get_compression_ratio
(
'&&tblspc'
,'&&ownr'
,'&&tblname'
,NULL
,DBMS_COMPRESSION.comp_archive_high
,blockct_comp
,blockct_uncomp
,rows_comp
,rows_uncomp
,comp_rat
,comp_type
);
DBMS_OUTPUT.put_line
(
'Compression type: '
|| comp_type
|| ' Compression ratio (est):'
|| comp_rat
);
END;
/
--//注:12c引數DBMS_COMPRESSION.comp_archive_high與11g不同.11g寫成DBMS_COMPRESSION.comp_for_archive_high
--//由於觸發器建立,報錯如下:
SCOTT@test01p> @ exadata/comp_radio12x.sql
old 11: '&&tblspc'
new 11: 'USERS'
old 12: ,'&&ownr'
new 12: ,'SCOTT'
old 13: ,'&&tblname'
new 13: ,'T'
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
DECLARE
*
ERROR at line 1:
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$107873 TABLE!
ORA-06512: at line 4
ORA-06512: at "SYS.PRVT_COMPRESSION", line 2134
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1108
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$107873 TABLE!
ORA-06512: at "SYS.PRVT_COMPRESSION", line 237
ORA-06512: at "SYS.DBMS_COMPRESSION", line 215
ORA-06512: at line 9
SCOTT@test01p> select owner,object_name,CREATED from dba_objects where owner=user and object_name like 'CMP%';
OWNER OBJECT_NAME CREATED
-------------------- -------------------- -------------------
SCOTT CMP4$107873 2018-08-14 20:58:05
SCOTT CMP3$107873 2018-08-14 20:57:57
SCOTT CMP2$107873 2018-08-14 20:57:51
SCOTT CMP1$107873 2018-08-14 20:57:48
--//這次測試建立4張表.
SCOTT@test01p> select * from CMP4$107873;
select * from CMP4$107873
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
--//可以發現oracle建立hcc表在非exadata是可行的,但是裡面的資料不能看.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2200068/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- Oracle檢視錶空間使用率SQL指令碼OracleSQL指令碼
- 指令碼實現檢視錶空間使用情況指令碼
- 檢視網頁是否壓縮gzip+編碼方式網頁
- 使用jsmin壓縮javascript指令碼JSJavaScript指令碼
- [20160501]檢視包引數指令碼.txt指令碼
- 使用man ascii檢視ascii碼錶ASCII
- 檢視ORACLE AS 埠指令碼Oracle指令碼
- 檢視錶大小
- 檢測壓縮包並處理的Linux指令碼（解壓、批量修改檔名、匯入）分析Linux指令碼
- 【Node】簡單快捷的圖片壓縮指令碼指令碼
- Sql server 檢視錶引用、依賴項，刪除表及約束 指令碼SQLServer指令碼
- [20171109]檢視隱含引數指令碼.txt指令碼
- 使用SQL指令碼檢視錶空間使用率和使用dba_tablespace_usage_metrics檢視的區別SQL指令碼
- 在EMR中使用snappy壓縮的時候快速檢視壓縮前文字的內容APP
- 檢視備份資訊指令碼指令碼
- 動態建立檢視指令碼指令碼
- mysql檢視錶大小MySql
- Linux下各壓縮方式測試（壓縮率和使用時間）Linux
- 11g 資料庫rman壓縮備份壓縮率測試資料庫
- FTP自動下載並解壓縮檔案指令碼FTP指令碼
- win10 如何壓縮視訊 win10怎麼壓縮視訊Win10
- 檢視錶並行度並行
- 小視訊原始碼，java使用Thumbnails壓縮圖片原始碼JavaAI
- Shell指令碼 – 檢視網路介面資訊指令碼
- 透過shell指令碼檢視鎖資訊指令碼
- rman的備份檢視的指令碼指令碼
- 檢視鎖定的session資訊指令碼Session指令碼
- oracle 檢視隱含引數指令碼Oracle指令碼
- 通過shell指令碼檢視鎖資訊指令碼
- 獲取單個檢視DDL指令碼指令碼
- 【管理】Oracle 常用的V$ 檢視指令碼Oracle指令碼
- Nginx網路壓縮 CSS壓縮 圖片壓縮 JSON壓縮NginxCSSJSON
- mysql檢視錶建立的索引MySql索引
- Oracle檢視錶空間大小Oracle
- db2檢視錶大小DB2
- JAVA壓縮和解壓縮Java
- zip壓縮和解壓縮