September 2, 2009
11gR2新特性之一-列式压缩(Columnar Compression)
作者:eygle
出处:http://blog.eygle.com
在11gR2发布之前，已经从一些Beta测试中知道，新版本中包含了列式压缩（Columnar Compression）的新特性，这一特性是被期待已久的，Sybase IQ产品尤以列式存储而著称，现在Oracle也在列式压缩上引入了类似技术。显然对于具有大量重复值的Column，按照列来压缩可以极大缩减存储空间的耗用，这是Oracle在压缩技术上的又一增强，也是为了在细粒度解决用户问题的一个改进。
我们先看看此前Beta文档（文档号E10881-01）中的描述：
Columnar compression is a new feature being introduced in Oracle Database 11g
Release 2 (11.2). Using columnar compression, data can be compressed during bulk
load operations.
使用列式压缩，在批量加载数据时，可以将数据压缩。
During the load process, data is transformed into a column-oriented
format and then compressed using a set of new compression algorithms. Different
levels of compression can be specified. Oracle Database selects the compression
algorithm according to the desired level of compression. Data transformation is
handled by Oracle Database internally and no application changes are required to use
columnar compression.
这一改变对应用透明，Oracle可以根据用户的定义来决定压缩级别和算法，选择列式压缩的数据将以基于列的格式存储。
Columnar compression provides significantly higher compression ratios as compared
to previously available compression features of Oracle Database. It also has a higher
CPU cost both at load time and at access time. Due to higher compression ratios, data
consumes less space in the buffer cache, providing I/O benefits that offset the CPU
costs.
列式压缩的压缩率可能相当高，这也是以CPU换IO的做法之一，在节省了IO的同时会消耗更多的CPU资源。
The overhead is relatively low for full table scans than for single row access.
All database operations (SQL queries, indexing, DMLs, DDLs, as well as features like
partitioning) work transparently against tables compressed using columnar
compression. This makes it possible to grow databases to large sizes and to store and
query that data at a fraction of cost.
在正式发布的文档上，也可以找到相应的描述：
During bulk-load operations, Oracle Database will compress the data being loaded when it is beneficial for performance. For small segments with very little data, no compression will occur even if you specify it. Oracle Database handles data transformation and compression internally and requires no application changes to use compression.
No special installation is required to configure this feature.
However, in order to use this feature, the database compatibility
parameter must be set to 11.2.0 or higher.
Note:
Additional compression technologies, including hybrid columnar compression, are available with Oracle Exadata Storage Server. See the Oracle Exadata documentation for more information.在这里，Oracle声明，列式压缩的特性，仅在Exadata存储上可用，这一特性显然对数据仓库大有益处。
在创建数据表时，可以通过增加新的COMPRESS FOR QUERY或者COMPRESS FOR ARCHIVE来启用混合列式压缩（hybrid columnar compression）特性，文档说明如下：
When you specify
COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable hybrid columnar compression.
With hybrid columnar compression, data can be compressed during bulk
load operations. During the load process, data is transformed into a
column-oriented format and then compressed. Oracle Database uses a
compression algorithm appropriate for the level you specify. In
general, the higher the level, the greater the compression ratio.
Hybrid columnar compression can result in higher compression ratios, at
a greater CPU cost. Therefore, this form of compression is recommended
for data that is not frequently updated.
COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.
COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.
新的语法结构是这样的：
{ COMPRESS [ BASIC
| FOR { OLTP
| { QUERY | ARCHIVE } [ LOW | HIGH ]
}
]
| NOCOMPRESS
}
同样这个错误提示让很多人为之一震，Exadata据说目前国内尚未有用户引入：
ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.
那么也就是说，这一吸引人的特性离我们还有点距离。
-The End-
Posted by eygle at 1:57 PM | Comments (6)
并行查询的 PX Deq: reap credit 等待
作者:eygle
出处:http://blog.eygle.com
PX Deq: reap credit在并行查询里被认为是一个空闲等待，Deq = DEQUEUE，这是一个关于出队的等待，reap credit指在等待传输一个认证信息或这说响应信息，以确认并行通道的畅通性。Oracle文档这样描述这个等待：
This wait event indicates that we are doing a non-blocking test to see if any channel has returned a message. We should see that although there is a high number of these events, the time accumulated to it should be zero (or really low).
然而在某些环境下，这个等待却的确意味着异常，在以下一个小时的采样中，数据库的并行等待出现在第一位，此时数据库已经认为这个RAC节点出现了异常，不再尝试连接这个节点：
Top 5 Timed Events服务受到了影响，经过诊断，发现是由于一个对于GV$SESSION的频繁查询引发了这一等待，导致数据库在频繁的校验Channel信息时出现异常，数据库将负载自动balance到另外实例。
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
PX Deq: reap credit ############ 727 20.52
latch free 3,924 694 19.59
CPU time 661 18.66
async disk IO 58,388 373 10.52
IPC send completion sync 724 300 8.47
-------------------------------------------------------------
跟踪一下对于gv$session的查询，可以获得如下输出，输出显示"PX Deq: reap credit"以及"PX Deq: Join ACK"等待交替出现：
PARSING IN CURSOR #1 len=31 dep=0 uid=0 oct=3 lid=0 tim=6801278325606 hv=3192025662 ad='41773c00'在正常情况下，这些查询并不意味着什么，但是一旦系统出现负荷升高或性能波动，则易引起数据库问题。
select count(*) from gv$session
END OF STMT
PARSE #1:c=10000,e=14597,p=0,cr=11,cu=3,mis=1,r=0,dep=0,og=4,tim=6801278325605
BINDS #1:
WAIT #1: nam='PX Deq: reap credit' ela= 26 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 220 p1=268500992 p2=1 p3=504403163461461976
WAIT #1: nam='PX Deq: reap credit' ela= 5 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 76 p1=268500992 p2=2 p3=504403163461461976
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 21 p1=268566528 p2=1 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 758 p1=268566528 p2=2 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 92 p1=268566528 p2=1 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 78 p1=268566528 p2=2 p3=504403163461461336
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 234 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 16675 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 5 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 12601 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
EXEC #1:c=0,e=31484,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=6801278357141
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 51 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 511 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 155 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='DFS lock handle' ela= 303 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 237 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 92 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 462 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 19 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 3 p1=0 p2=1 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 3 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 134 p1=10 p2=2 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
FETCH #1:c=0,e=2454,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=6801278359638
WAIT #1: nam='DFS lock handle' ela= 222 p1=1128857605 p2=9 p3=1
WAIT #1: nam='DFS lock handle' ela= 254 p1=1128857605 p2=9 p3=3
WAIT #1: nam='DFS lock handle' ela= 74 p1=1128857605 p2=9 p3=2
WAIT #1: nam='DFS lock handle' ela= 476 p1=1128857605 p2=9 p3=2
WAIT #1: nam='PX Deq: reap credit' ela= 4 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 17 p1=0 p2=0 p3=0
WAIT #1: nam='PX Deq: reap credit' ela= 1 p1=0 p2=0 p3=0
WAIT #1: nam='enqueue' ela= 128 p1=1347616774 p2=2 p3=0
WAIT #1: nam='enqueue' ela= 5070 p1=1347616774 p2=2 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 149 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=6801278366394
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
*** 2009-09-01 10:55:19.637
WAIT #1: nam='SQL*Net message from client' ela= 10040665 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=0 op='VIEW '
STAT #1 id=3 cnt=0 pid=2 pos=1 obj=16 op='FIXED TABLE FULL X$KSUSE '
所以我们应该获得的经验是：在RAC环境中，应当避免频繁查询GV$全局视图。而Metalink上可以找到几则关于访问GV$SESSION视图的Bug。
-The End-
Posted by eygle at 8:20 AM | Comments (0)
Oracle 11g R2 For Linux发布 - 2009-08-31
作者:eygle
出处:http://blog.eygle.com
在美国时间2009年8月31日，Oracle公司率先发布了Oracle Database 11gR2的For Linux版本。刚刚有Oracle的朋友也告诉我这个消息，现在这个版本可以在OTN上下载了：
http://www.oracle.com/technology/software/products/database/index.html
文档地址：
http://www.oracle.com/pls/db112/homepage
http://download.oracle.com/docs/cds/E11882_01.zip
新版本的发布号为：11.2.0.1，分两个DIsk，共计2G左右，以下是下载链接，需要OTN注册：
Oracle Database 11g Release 2
Standard Edition, Standard Edition One, and Enterprise Edition
(11.2.0.1.0)
Linux x86 | Disk
1, Disk
2 (2.1 GB) | See
All (Including Client, Gateways, Grid Infrastructure, more)
Linux x86-64 | Disk
1, Disk
2 (2.2 GB) | See
All (Including Client, Gateways, Grid Infrastructure, more)拥抱Oracle 11g，从今天开始！
-The End-
Posted by eygle at 12:02 AM | Comments (8)