淺析Oracle 11g中對資料列預設值變化的最佳化
在日常的運維工作中,對生產資料表進行DDL操作是一件需要謹慎對待的事情。運維DBA們在進行資料DDL操作的時候,通常要全域性考慮,諸如對生產影響、執行時間長度和影響儲存資料等等。
資料列預設值的新增,是DBA們經常頭疼的一個問題。傳統的執行語句,消耗時間長、資源使用量大,對生產環境影響程度高。採用其他的一些變通方法,又存在操作步驟繁瑣的問題。如何快速的新增一個有預設值的資料列,同時對現有生產環境影響最小,是我們希望達到的一個目標。
本文從操作入手,探討新增default資料列的問題點,最後介紹Oracle 11g中對其進行的“革命性”最佳化。
1、從10g的資料列新增談起
為了實現對比效果,我們首選選擇10g版本的Oracle進行試驗,構造一個相對較大的資料表。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t as select object_id from dba_objects;
表已建立。
SQL> select count(*) from t;
COUNT(*)
----------
3220352
資料表t只包括一個資料列,但是資料量大約為320萬條。我們從體積上進行評估如下:
SQL> set timing on;
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';
BYTES/1024/1024 BLOCKS
--------------- ----------
39 4992
已用時間: 00: 00: 00.03
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.35
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
BLOCKS
----------
4883
已用時間: 00: 00: 00.01
Oracle分配給這個段segment的中空間為4992個資料塊,高水位線HWM下的格式化過資料塊為4883。總體積約40M。
下面進行兩種方式的新增資料表預設值列方法,一起觀察一下變化情況。首先是允許為空預設值列的操作。
SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT';
表已更改。
已用時間: 00: 34: 37.15
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 03.86
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';
BYTES/1024/1024 BLOCKS
--------------- ----------
208 26624
已用時間: 00: 00: 00.06
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
BLOCKS
----------
25864
已用時間: 00: 00: 00.01
果然是一個費時的操作,新增一個資料列預設值,總共消耗了近30分鐘時間。原有資料表的體積也發生的膨脹,從原來的不到40M,上升到了208M。
這個現象告訴我們,當我們新增一個有default值的資料列,並且是直接新增的時候,一些資料被插入到了資料塊中,引起空間膨脹。
在原有的結構下,資料新增到資料塊上是必需的,只有這樣才能將資料列default新增到裡面去。
除了這個字句,我們是還可以提供資料列的not null選項,也是可以實現相同的功能的。
SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' not null;
表已更改。
已用時間: 00: 15: 58.85
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 36.87
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segme
nt_name='T';
BYTES/1024/1024 BLOCKS
--------------- ----------
256 32768
已用時間: 00: 00: 00.14
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
BLOCKS
----------
32448
已用時間: 00: 00: 00.04
也是消耗了15分鐘,空間發生了很大程度變化。新空間分配,同時資料行數沒有發生變化,潛在的行遷移(Row Migration)和行連結(Row Chaining)是嚴重惡化的!
綜合分析Oracle 10g下的操作:為了新增上資料欄位的預設值,Oracle會去訪問每個資料塊上的每個資料行進行資料列擴充工作,這個過程中還伴隨著新空間分配和多餘資料行復制。
這型別操作對於生產環境是恐怖的,在整個作業過程中,資料表結構被鎖定,相關業務處理操作阻塞或者緩慢。所以,運維DBA都是選擇在維護視窗或者變通的方法進行處理。
在Oracle 11g環境下,事情有了一些不同。
2、11g下的預設值配置
我們在11g上進行相似操作。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
構建相似規模的資料表。
SQL> set timing on;
SQL> create table t as select object_id from dba_objects;
Table created
SQL> select count(*) from t;
COUNT(*)
----------
3323167
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';
SEGMENT_NA BYTES/1024/1024 EXTENTS BLOCKS
---------- --------------- ---------- ----------
T 40 55 5120
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';
NUM_ROWS BLOCKS
---------- ----------
3323167 5041
11g下我們準備了約330萬資料,進行新增非空帶預設值的資料列。
SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' ;
alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT'
ORA-01013: 使用者請求取消當前的操作
在新增defalut列,不指定not null的時候,資料持續時間超過了我們的想象。筆者主動將其斷開了。下面試試新增not null時候。
--1s不到完成操作;
SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT' not null;
Table altered
Executed in 0.047 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';
NUM_ROWS BLOCKS
---------- ----------
3323167 5041
Executed in 0 seconds
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';
SEGMENT_NA BYTES/1024/1024 EXTENTS BLOCKS
---------- --------------- ---------- ----------
T 40 55 5120
SQL> select * from t where rownum<10;
OBJECT_ID VC
---------- --------------------------------------------------------------------------------
20 TTTTTTTTTTTT
46 TTTTTTTTTTTT
28 TTTTTTTTTTTT
15 TTTTTTTTTTTT
(篇幅原因,有省略……)
9 rows selected
我們發現,當執行not null的時候,Oracle以超乎想象的速度完成了過程。並且注意:資料表的體積沒有發生任何變化!!但是,我們檢查資料表的時候,卻發現了對應列的預設值已經新增。
這個事情是比較奇怪的,有一個道理必然是可以說通:就是這個預設值在執行過程中,是絕對沒有真正新增到資料塊中的,因為只有這樣才不會影響資料段的體積。
3、11g預設值處理的最佳化
那麼,11g這個過程中是如何處理的呢?而且為什麼只有新增Not null的時候才會有這個特點。我們從select資料行的trace進行入手。
我們選擇10046跟蹤一下select的全過程,看看顯示出來的預設值從哪裡來。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6177.trc
SQL> alter session set events '10046 trace name context forever, level 12';
會話已更改。
SQL> select * from t where rownum<10;
OBJECT_ID
----------
VC
-----------------------------------------------------------------------------
SQL> alter session set events '10046 trace name context off';
會話已更改。
對生成的trace檔案進行處理,獲取到tkprof結果。
D:\des>tkprof wilson_ora_6177.trc
output = res.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期五 8月 24 22:07:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
在分析的結果中,我們發現很多的recursive語句,也就是Oracle為了執行這個SQL,連帶執行了很多的語句,其中我們發現了一個“可疑”物件。
***********************************************************************
select binaryDefVal, length(binaryDefVal)
from
ecol$ where tabobj# = :1 and colnum = :2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 2 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 2 2 0 1
ecol$是sys使用者下的一個新新增的字典基表,其中內容如下:
SQL> desc ecol$;
Name Type Nullable Default Comments
------------ ------ -------- ------- --------
TABOBJ# NUMBER Y
COLNUM NUMBER Y
BINARYDEFVAL BLOB Y
SQL> select * from ecol$;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- ------------
76046 2
Executed in 0.031 seconds
SQL> col owner for a10;
SQL> col object_name for a10;
SQL> select owner, object_name, object_id from dba_objects where object_id in (76046);
OWNER OBJECT_NAM OBJECT_ID
---------- ---------- ----------
SCOTT T 76046
Executed in 0 seconds
從ecol$資料表中,我們發現了對資料表T物件第二列(column=2)的一個物件引用,引用的值binarydefval是一個blob型別。從直觀上,我們已經可以猜出這個就是記錄了資料表vc列的預設值。
此處,我們說一個問題,在Oracle中,預設值都是透過大物件型別進行儲存。在資料字典col$中,預設值是透過long類進行儲存。而進入11g的ecol$表,這個值是使用blob型別進行儲存。
另一個需要注意的,就是這個資料表中只有一個資料行,也就是隻有我們建立資料表T的預設值。這說明什麼呢?
此時,我們已經可以猜出Oracle的良苦用心。首先,Oracle注意到了在生產online的時候,新增帶預設值列資料的困難。但是,從現有的體系結構和儲存結構下,將預設值逐行插入、從而引起行遷移的情況是不能避免的。所以,Oracle採用了一種“障眼法”。
如果我們在建立資料表的時候就指定了資料列的預設值、或者沒有要求將所有資料空值一次性全都變成預設值的時候,Oracle還是按照原有的儲存策略進行管理。如果出現了要求新增資料列,並且一次性將所有預設值列都加入的情況,Oracle索性就不進行插入資料和挪行的操作,而是將這個預設值儲存在ecol$中。
接下來,如果要進行檢索資料,首先oracle會利用recursive call的方法,儲存提取出預設值。在檢索資料的過程中,如果遇到預設值列為空的情況(沒有插值),就將取出的預設值輸出到介面上進行顯示。其實,資料行對應的預設值列是沒有這個值的。
這就解釋了為什麼只有在新增not null預設值列的時候,才會有這個最佳化。因為Oracle需要確認這個列不會有空值,才會將出現的空值全都進行“障眼法”匹配。
4、結論
藉助了11g這個特性,我們說在online生產環境下,臨時加入預設值列就不是一件恐怖的工作了。不過,處於謹慎的考慮,還是希望有條件的時候,將該資料表進行重構。這種特性屬於應急環境下考慮使用。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17203031/viewspace-741931/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 淺析Oracle 11g中對資料列預設值變化的優化Oracle優化
- 淺析Oracle 10gR2中最佳化器對錶關聯的最佳化(轉)Oracle 10g
- 淺析SQL Server datetime資料型別設計與最佳化誤區SQLServer資料型別
- 網站訪問變慢最佳化,淺析10種網站訪問變慢最佳化方法的作用網站
- Oracle資料表預設值列新增與行遷移(Row Migration)Oracle
- Oracle中NLS_LANG的預設值Oracle
- Oracle資料庫字符集淺析Oracle資料庫
- MySQL效能最佳化淺析及線上案例MySql
- oracle 資料庫對於多列求最大值Oracle資料庫
- Oracle 11g比10g及以前 增加列,並帶預設值的新特性Oracle
- sqlserver 修改列的預設值SQLServer
- MYSQL中給時間列設定預設值MySql
- 淺析:網站SEO最佳化站內連結的作用及其最佳化?網站
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- 讀TiDB原始碼聊設計:淺析HTAP的SQL最佳化器TiDB原始碼SQL
- 淺析資料字典
- 大資料淺析大資料
- 深入淺析nginx部署及簡單最佳化Nginx
- 淺析number型別的值型別
- mysql 5.7 vs 8.0預設值變化(筆記)MySql筆記
- MYSQL中怎樣設列的預設值為Now()的介紹MySql
- 淺談資料結構最佳化DP資料結構
- oracle的TAF淺析Oracle
- MapReduce資料序列化讀寫概念淺析!
- 淺析數字化價值,如何保障數字化價值實現?
- 【效能最佳化】ORACLE資料庫效能最佳化概述Oracle資料庫
- 淺析大資料時代的資料安全合規體系建設大資料
- SQL SERVER檢視列的預設值SQLServer
- Netty 中的記憶體分配淺析-資料容器Netty記憶體
- 淺析 Hadoop 中的資料傾斜(R0.1)Hadoop
- Oracle基本資料型別儲存格式淺析[zt]Oracle資料型別
- Sql最佳化(十三)分散式環境中的最佳化(1)合理設計資料流SQL分散式
- oracle時間欄位預設值,hibernate對映Oracle
- 淺析Java中的雜湊值HashCode的作用及用法Java
- 淺析前端的模組化前端
- 資料安全運營淺析
- 資料庫的讀現象淺析資料庫
- oracle資料庫最佳化基礎Oracle資料庫