Where is Sample Schema in Oracle 11gR2

在Oracle10g的时候如果我们在创建了数据库以后想单独安装一些示例用户数据,那么可以直接执行:

$ORACLE_HOME/demo/schema/mksample.sql

这样会创建包括HR,OE等在内的一系列示例用户。这些用户数据可以让我们简单地进行一些功能测试,并且Oracle很多文档的示例也是使用这些用户的。

但是在11gR2中我们在$ORACLE_HOME/demo/schema/目录下已然找不到mksample.sql文件,并且每个单独的子目录中也不再有创建示例用户的SQL脚本。

实际上,Oracle将这些示例用户的安装独立到了一个安装盘中,比如对于Linux x86-64的安装文件可以从OTN上直接下载:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

其中Oracle Database 11g Release 2 Examples就是我们需要的示例用户安装文件。

但是现在这份安装文件有500多M,如果你并不期望下载这么大的内容,或者说你仅仅需要HR和OE用户,那么可以直接从以下URL中下载现成的SQL脚本,解压以后运行即可。
http://st-curriculum.oracle.com/obe/jdev/obe11jdev/11/common/files/sample_schema_scripts.zip

以HR用户为例:

$ ls
hr_cre.sql  hr_main.sql  hr_popul.sql
$ sqlplus / as sysdba
SQL> @hr_main.sql

How to use Database File System (DBFS) in Oracle 11gR2

简单的来说,DBFS就是Oracle数据库11gR2中提供的能够在Linux操作系统中将Oracle数据库当成文件系统来使用的功能。在DBFS内部,文件是以SecureFiles LOBs(对比与以前的BasicFiles LOBs)的形式存储在数据表中。

本文简单介绍在Oracle11gR2中使用DBFS的方法。
参考文档:Oracle® Database SecureFiles and Large Objects Developer’s Guide 11g Release 2 (11.2) – 6 DBFS File System Client

本文使用的数据库是Oracle 11.2.0.1,操作系统是Oracle Enterprise Linux 5.3:

$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)
$ uname -r
2.6.18-128.el5

1. 首先需要安装kernel-devel和FUSE包。实际上现在最新的FUSE版本是2.8.5,但是为了防止有兼容性问题,仍然按照文档所述选择了2.7.4版本。kernel-devel包在OEL的安装光盘中就可以找到,如果你的Linux系统中已经安装过,无需再次安装。

# rpm -qa| grep kernel-devel
kernel-devel-2.6.18-128.el5

安装FUSE也同样很简单。
将下载成功的fuse-2.7.4.tar.gz文件解压,生成fuse-2.7.4目录。

# ./configure --prefix=/usr --with-kernel=/usr/src/kernels/`uname -r`-`uname -p`
# make
# make install
# /sbin/depmod
# /sbin/modprobe fuse
# chmod 666 /dev/fuse
# echo "/sbin/modprobe fuse" >> /etc/rc.modules
# chmod +x /etc/rc.modules

2. 在数据库中创建文件系统。创建文件系统的数据库用户至少需要拥有以下权限。

SQL> grant connect, create session, resource, create table, 
create procedure, dbfs_role to kamus;

在$ORACLE_HOME/rdbms/admin目录中执行dbfs_create_filesystem.sql来创建文件系统。其中mytbs为文件系统所在的表空间,dbfs_area为文件系统的名称。

cd $ORACLE_HOME/rdbms/admin
sqlplus kamus
SQL> @dbfs_create_filesystem.sql mytbs dbfs_area

通过观察屏幕显示,可以知道dbfs_create_filesystem.sql实际上是调用了一些package,创建了DBFS文件系统。而这种默认方式对于Securefile LOB的一些特性都是没有启用的,比如压缩,去重,分区,加密等,如果要启用这些特性,可以使用dbfs_create_filesystem_advanced.sql。

--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_DBFS_AREA', tbl_name =>
'T_DBFS_AREA', tbl_tbs => 'users', lob_tbs => 'users', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_DBFS_AREA', provider_name
=> 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_DBFS_AREA',
store_mount=>'dbfs_area'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/dbfs_area', 16895); end;

3. 将数据库文件系统mount到操作系统中。

--添加一个新的库目录到库文件加载路径中
# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
--将必须的库文件link到该目录中
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
# cd /usr/local/lib 
# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 
# ln -s $ORACLE_HOME/lib/libnnz11.so
# ln -s /usr/lib/libfuse.so
--创建运行时动态库链接
# ldconfig

如果不执行以上步骤,则运行dbfs_client将会报错。

dbfs_client: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

实际上到此为止,DBFS已经可以正常运转了,即使我们不将DBFS挂载到操作系统中的某个目录下,也同样可以通过dbfs_client程序来创建目录,copy文件。

比如用dbfs_client来列出初始的DBFS目录结构。

$ dbfs_client dbfs@localhost:1521/orcl --command  ls -a -R dbfs:/dbfs_area
Password:
dbfs:/dbfs_area/.sfs
dbfs:/dbfs_area/.sfs/content
dbfs:/dbfs_area/.sfs/RECYCLE
dbfs:/dbfs_area/.sfs/snapshots
dbfs:/dbfs_area/.sfs/tools
dbfs:/dbfs_area/.sfs/attributes

创建新目录。

$ dbfs_client dbfs@localhost:1521/orcl --command mkdir dbfs:/dbfs_area/dir1

copy文件。

$ dbfs_client dbfs@localhost:1521/orcl --command cp test.txt dbfs:/dbfs_area/dir1/
Password:
test.txt -> dbfs:/dbfs_area/dir1/test.txt

可以通过以下方式从数据字典中查看DBFS的目录结构和属性。

SQL> select * from table(dbms_dbfs_content.listmounts);
SQL> select * from table(dbms_dbfs_content.listallcontent);
SQL> select * from dbfs_content;
SQL> select * from dbfs_content_properties;

不过为了更加方便使用,我们将DBFS挂载到/dbfs目录中。

--创建挂载点目录,修改目录属主
# mkdir /dbfs
# chown oracle:dba /dbfs
--以oracle用户挂载DBFS文件系统
# su - oracle
$ dbfs_client kamus@dbserver:1521/orcl -o rw,user,direct_io /dbfs

以上命令并没有使用在后台mount DBFS,因此在执行完最后命令的时候,要求输入数据库用户kamus的密码,之后并不会回到命令行状态,这是正常的。

$ dbfs_client kamus@dbserver:1521/orcl -o rw,user,direct_io /dbfs
Password:

如果要以后台的方式mount,则需要执行以下命令,其中pwd.f中保存数据库用户的密码:

$ nohup dbfs_client kamus@dbserver:1521/orcl -o rw,user,direct_io /dbfs < pwd.f &

更安全的方法是使用wallet,方法如下。

--创建wallet目录,该目录位置随意
mkdir $ORACLE_HOME/wallet
--创建Oracle wallet,回车以后会要求指定wallet的密码,要求至少为8个字符。
mkstore -wrl $ORACLE_HOME/wallet -create
--更新$ORACLE_HOME/network/admin/sqlnet.ora文件,添加wallet的位置。
vi $ORACLE_HOME/network/admin/sqlnet.ora
--添加如下行
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/product/11.2.0/dbhome_1/wallet)
     )
   )
SQLNET.WALLET_OVERRIDE = TRUE
--将密码证书添加到wallet中。其中orcl为tnsnames.ora中的条目,kamus为用户名,oracle为密码。
mkstore -wrl $ORACLE_HOME/wallet -createCredential orcl kamus oracle
--添加成功以后,可以通过如下命令查看密码证书
mkstore -wrl $ORACLE_HOME/wallet -listCredential
--挂载DBFS文件系统
dbfs_client -o wallet /@orcl /dbfs

通过wallet方式,如果要挂载不同数据库用户下的DBFS,则需要mkstore -createCredential命令添加多个密码证书,通过不同的tnsnames条目来区分。

--添加scott用户的密码证书
mkstore -wrl $ORACLE_HOME/wallet -createCredential orcl_scott scott tiger
--在tnsnames.ora中添加orcl_scott条目
--挂载DBFS文件系统
dbfs_client -o wallet /@orcl_scott /dbfs

如果要卸载文件系统,则使用:

$ fusermount -u /dbfs

4. 检查文件系统是否已经mount成功。

$ ls -l /dbfs
total 0
drwxrwxrwx 5 root root 0 Apr 12 00:21 dbfs_area

可以看到之前创建的名称为dbfs_area的文件系统已经以目录的形式存在于挂载点/dbfs中了。

5. 创建一个测试目录,直接往目录中copy文件。

$ cd /dbfs/dbfs_area
$ mkdir test
$ echo "A great user experience" > test/dbfs.txt

6. 在数据库中查看该文件是如何存储的。这里我们使用SQL Devloper来更方便地查看LOB数据。

可以注意到:表T_DBFS_AREA是Oracle自动创建的,该表的PATHNAME为文件系统路径,FILEDATA字段为LOB类型,存储真正的文件内容,并且在SQL Developer中也可以看到我们刚才在操作系统中直接echo进dbfs.txt文本文件中的内容了。

再测试一个图片文件。

7. 创建一个新文件系统。

cd $ORACLE_HOME/rdbms/admin
sqlplus kamus/oracle
SQL> @dbfs_create_filesystem.sql mytbs dbfs_pics

8. 新文件系统会立刻以目录的形式出现在操作系统中。

$ ls -l /dbfs
total 0
drwxrwxrwx 4 root root 0 Apr 12 02:20 dbfs_area
drwxrwxrwx 3 root root 0 Apr 12 00:42 dbfs_pics

9. 远程使用sftp从本地机器中上传一个jpg图片,上传到/dbfs/dbfs_pics目录中。

$ ls -l /dbfs/dbfs_pics/
total 33
-rw-r--r-- 1 oracle dba 33565 Apr 12 00:42 zombie2.jpg

10. 在SQL Developer中查看该图片。

11. 查看用户LOB视图,确实是以SecureFile LOBs的形式存储的。

SQL> select table_name,column_name,segment_name,securefile from user_lobs;

TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   SEC
-------------------- -------------------- ------------------------------ ---
T_DBFS_AREA          FILEDATA             LOB_SFS$_FST_1                 YES
T_DBFS_PICS          FILEDATA             LOB_SFS$_FST_17                YES

至此,完成了最基本的DBFS测试,这是很奇妙的用户体验,不是吗?

【备注1】
在DBFS被使用的时候,也仍然可以正常关闭数据库,这一点与ACFS不同,毕竟这仅仅是通过FUSE框架展现出来的用户接口而已。在关闭数据库以后,再次尝试读取DBFS中的内容,将报IO错误。

$ pwd
/dbfs/dbfs_area/test
$ ls
ls: .: Input/output error

【备注2】
如果在Linux X86-64操作系统中使用dbfs_client,需要额外打上一些Critical Patches,具体参看MOS Note 1150157.1

【备注3】
更多的资料请阅读:

11.2.0.2 Patch Set? Really a Patch Set?

Oracle最新为Linux x86和x86-64平台发布了Oracle 11.2.0.2 patch set [Patch set ID: 10098816],这个庞大的patch set拥有5G的超大容量。所以几乎全世界的人都在问,这真的是一个补丁集吗?不仅仅是体积庞大,甚至还一改往日补丁集仅仅是作为Bug修复的角色,在11.2.0.2中还包含了很多New Feature

Oracle专门为此补丁写了一篇文章-Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1],着重提到了几点。

1. Oracle 11.2.0.2 patch set是一个完整的安装包,以后Oracle也会如此发布后续的补丁包。所以在以后如果要安装新数据软件,不再需要安装一个Base版本然后再打补丁集到最新版本,而是直接使用最新补丁集安装包即可。
2. Oracle建议今后的补丁集安装都安装在一个与当前环境目录不同的全新目录下(Out-of-place upgrade),在安装完成以后再通过此份软件启动数据库实例。实际上以后安装补丁集的过程就等同于从一个版本升级到一个新版本的过程。
3. 为什么做这种改变,是因为Oracle认为Out-of-place upgrade是最佳实践,比较安全。当然这实际上符合Oracle的作风,他认为合理且最好的通常都会武断地改变并且说服客户去接受。
4. Oracle仅仅计划在11.2.0.2中提供一些新功能,今后的补丁集仍然会以修复bug为首要任务。

我个人承认确实Out-of-place upgrade是更加安全的,但是至少这种做法目前存在两点缺陷:
1. 补丁尺寸急剧增加,下载2G的补丁和下载5G的补丁还是有很大区别,但是这不是大问题。当然,由于需要安装在新Oracle Home中,在打补丁时需要额外的硬盘空间,但是不到10G的空间需求也不是大问题。
2. 由于在打完补丁以后,Oracle Home路径实际上发生了变化,那么在脚本中,服务定义中牵涉到的原ORACLE_HOME路径必须都要做相应改动。这是个大问题。不过,可以将老目录rename成一个新名字,再将新目录rename成原目录。

今后的补丁集发布计划可以参看Release Schedule of Current Database Releases [ID 742060.1]

Oracle Resource Manager Concepts

这两天仔细阅读了Oracle Administrator Guide中关于Resource Manager的部分,基本上理清了之前我认为繁杂的概念和多个概念互相之间的关系。

在阅读的过程中,将自己认为重要的部分做了一份PPT,希望可以在下个月的ACOUG活动中跟大家分享。

在Database Consolidation时,Resource Manager可以解决我们心中的疑问,如果我将多个应用都放在同一个硬件服务器的同一个数据库中,那么如何防范互相之间的影响?另外一个更加实用且有需求的场景是在Oracle EBS中如果有大量的Report,如果设置Resource Manager来防止单个报表占用启用并行进程,占用过多CPU?

虽然在国内目前真正应用Resource Manager的客户几乎没有见到,但是我期望能够为今后我们的客户部署该项功能,资源管理在整个世界趋向虚拟化的过程中很有意义并且十分必要。

Oracle Histogram Investigation

这段时间有客户的10gR2数据库经常遭遇执行计划不稳定的现象,应该是直方图信息(Histogram)+绑定变量窥视(Bind Variable Peeking)造成的问题,藉此分享一下直方图的使用经验,但是我非常希望能听到针对本文的不同声音。测试环境Oracle 11.2.0.1 for Windows X64。

创建一个测试表。

[sourcecode language=”sql”]DROP TABLE t;
CREATE TABLE t AS
SELECT rownum n1,
ROUND(rownum/100) n2,
‘xxxx’ n3
FROM dba_objects
WHERE rownum<=10000;[/sourcecode]

这样我们生成了一张表T,其中有一万行记录。
N1字段可以认为是主键,有10000个distinct值(1-10000),这是一个数据分布均匀的字段;
N2字段有101个distinct值,除了第一个值0和最后一个值101之外,其它的值都有100行记录,我们可以认为这是一个数据分布均匀的字段;
N3字段有10000个值都是xxxx,实际上这也仍然是一个分布均匀的字段。

对于这三种类型的字段,我们都不应该去收集直方图信息,因为没有意义。那么直方图信息之所以需要存在的根本意义在哪里?

一. 我们何时该做
直方图究其根本实际上就是一个数据分布的图示,这个图示是为了在生成SQL执行计划的时候给Oracle的CBO更多的信息,换句话说,就是当在where条件中的某些列可能由于列值的不同而希望CBO制定出不同的执行计划时,我们需要直方图。

反过来说,什么情况下我们不需要直方图呢?或者说直方图的存在是没有意义的呢?
1. 当此列不用于查询时,也就是这个字段永远不会出现在where条件中(注意:即使是用于表连接那也算是出现在where条件中)。
2. 当此列无论给予什么比较值,我们都希望永远是一种执行计划时。上面例子中的3个字段都属于这种情况。
对于N1或者N2出现在where条件中,我们希望永远是用该列上的索引扫描(当然需要在该列上先创建索引),对于N3出现在where条件中,我们希望永远是全表扫描。这样的执行计划的制定,只要有表级别的统计信息就足够了,直方图信息的出现不但不会对制定正确的执行计划有帮助,甚至会出现奇怪的现象导致执行计划不稳定。

二. 我们可以怎样做
那么我们继续看一下收集直方图信息的方法,众所周知,是需要使用DBMS_STATS.GATHER_TABLE_STATS存储过程的,并且是由这个存储过程中的method_opt参数决定的。可以由以下几种选择。

method_opt=>’for columns size skewonly [column_name]’
什么是SKEWONLY?先看一下Oracle官方文档中的定义。

Oracle determines the columns to collect histograms based on the data distribution of the columns.

也就是要看列上的数据分布,那么按照我们前面提到的,T表中的三列数据分布都是均匀的,并不应该收集任何列上的直方图信息,实际呢?

[sourcecode language=”sql”]EXEC dbms_stats.gather_table_stats(ownname=>’KAMUS’,tabname=>’T’,estimate_percent=>NULL,method_opt=>’for all columns size skewonly’,CASCADE=>true);

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name=’T’;

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
—————————— ———- ———– —————
N3 .00005 1 FREQUENCY
N2 .00005 101 FREQUENCY
N1 .0001 1 NONE
[/sourcecode]

可以看到在N2上收集了直方图信息,收集了101个bucket,而N3上则收集了1个bucket的直方图信息(实际上也只能收集一个,因为只有一个distinct值xxxx)。收集直方图信息是耗费资源的,因此SKEWONLY不推荐使用。实际上经历了这么多版本,SKEWONLY的算法一直很奇怪,我没有找到一份文档描述Oracle到底是如何定义SKEWONLY的收集凭据的。

method_opt=>’for columns size auto [column_name]’
什么是AUTO?再看一下Oracle官方文档中的定义。

Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

也就是在SKEWONLY的基础上额外增加了workload限制,何谓workload?实际上就是我们前面提到的是否在where条件中出现。如果一个列从来没有出现在where条件中,Oracle就认为是没有workload的,那么即使此列上的数据被认为是SKEW的,也不会收集直方图信息,只有出现过至少一次,Oracle才会收集。[via Comment by Dbsnake] 该workload信息可以从数据字典COL_USAGE$中获得,在数据字典中存在的列才是有workload的。

[sourcecode language=”sql”]
–T表是新创建的,创建完毕以后还没有进行过任何select,因此AUTO选项不会收集任何直方图信息。
EXEC dbms_stats.gather_table_stats(ownname=>’KAMUS’,tabname=>’T’,estimate_percent=>NULL,method_opt=>’for all columns size auto’,CASCADE=>true);

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name=’T’;

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
—————————— ———- ———– —————
N3 1 1 NONE
N2 .00990099 1 NONE
N1 .0001 1 NONE

–执行一次select,再次收集,可以看到N2字段上的直方图信息了。
SQL> SELECT COUNT(*) FROM t WHERE n2=10;

COUNT(*)
———-
100

SQL> EXEC dbms_stats.gather_table_stats(ownname=>’KAMUS’,tabname=>’T’,estimate_percent=>NULL,method_opt=>’for all columns size auto’,CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name=’T’;

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
—————————— ———- ———– —————
N3 1 1 NONE
N2 .00005 101 FREQUENCY
N1 .0001 1 NONE
[/sourcecode]

因此AUTO也是有问题的,N2字段上的直方图信息仍然会收集,这是我们不需要的。注意,在Oracle10g之后的自动统计信息收集任务中,默认的是FOR ALL COLUMNS SIZE AUTO,这是很有问题的,因此即使是不禁用自动统计信息收集,也应该通过DBMS_STATS.SET_PARAM存储过程(在11gR2版本中被SET_GLOBAL_PREFS存储过程替代)来修改该默认值。通常我们经历的执行计划莫名其妙改变,很多场合都是因为Oracle10g之后的这个统计信息自动收集任务导致的。关于自动统计信息收集,可以参看:Automatic Statistics Gathering

method_opt=>’for columns size repeat [column_name]’
只有在已有直方图信息的列上再次收集直方图信息。这是我们推荐设置为默认值的方式。具体的执行方法见后文。

method_opt=>’for columns size 1 [column_name]’
size 1将删除列上的直方图信息,同样也意味着不收集。

[sourcecode language=”sql”]SQL> EXEC dbms_stats.gather_table_stats(ownname=>’KAMUS’,tabname=>’T’,estimate_percent=>NULL,method_opt=>’for all columns size 1′,CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name=’T’;

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
—————————— ———- ———– —————
N3 1 1 NONE
N2 .00990099 1 NONE
N1 .0001 1 NONE[/sourcecode]

method_opt=>’for all columns size 30′
size自定义大于1的数值,最大为254,如例表示要求收集30个bucket的直方图信息。

[sourcecode language=”sql”]SQL> EXEC dbms_stats.gather_table_stats(ownname=>’KAMUS’,tabname=>’T’,estimate_percent=>NULL,method_opt=>’for all columns size 30′,CASCADE=>true);

PL/SQL procedure successfully completed.

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name=’T’;

COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
—————————— ———- ———– —————
N3 .00005 1 FREQUENCY
N2 .00995002 30 HEIGHT BALANCED
N1 .0001 30 HEIGHT BALANCED[/sourcecode]

那么现在到了最重要的部分,对于一个数据库系统,我们到底应该如何收集直方图信息呢?

三. 我们应该怎样做
首先我们要明确直方图信息是有存在的必要的,但是只应该存在在那些应该要存在的列上,Oracle知道哪些列上应该存在吗?Oracle一直在致力于想知道,可惜的是现在做的仍然不够好,全部交给Oracle去做的话(SIZE SKEWONLY或者SIZE AUTO),可能得到的结果就是该收集的没收集,不该收集的收集了一堆。只有我们的DBA才最知道哪些列上应该收集直方图,这实际上已经远远不仅仅是技术问题了,而是一个业务问题,因此DBA应该去熟悉业务,DBA应该知道自己的应用的数据分布特点,应该知道哪些列会常被用在where条件中。

直方图信息收集是一个漫长而持续的过程,没有一蹴而就的方法,也不可能一劳永逸。

我们推荐的方法是:
1. 第一次收集统计信息时,设置method_opt=>FOR ALL COLUMNS SIZE 1,这意味删除所有列上的直方图。
2. 在测试阶段或者在真实生产环境中,在调优SQL的过程中,DBA将会逐渐得知每个需要直方图信息的字段,在这些字段上人工收集统计信息,method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME],如果你能够明确知道应该收集多少个bucket而手工指定SIZE值那更好。保留收集所有这些字段的脚本,以备数据库系统升级或者迁移时候使用。
3. 在每次数据分布有所变化的时候,更新统计信息,使用method_opt=>FOR ALL COLUMNS SIZE REPEAT,这样只会收集已经存在了直方图信息的字段。

重复2,3步骤,直到系统稳定。这是一个可控的步骤,只有可控,才可能避免不可预知的错误。

[备注1] dba_tab_col_statistics.histogram字段值的含义。
该字段可能包含三个值:NONE,FREQUENCY或者HEIGHT BALANCED。

NONE:就是没有直方图

FREQUENCY:当该列的distinct值数量<=bucket数量时,为此类型。对于此类型而言,在dba_tab_histograms视图中的会存在相当于distinct值数量的记录,该视图的ENDPOINT_VALUE字段记录了这些distinct值,而ENDPOINT_NUMBER字段则记录了到此distinct值为止总共有多少条记录,注意这是一个累加值,实际上我们可以用一条记录的ENDPOINT_NUMBER减去上一条记录的ENDPOINT_NUMBER来获知对应于本记录的ENDPOINT_VALUE值有多少条记录。我知道这听上去很拗口,可能也有些难于理解,但是请尽量去理解吧。 HEIGHT BALANCED:当该列的distinct值数量>bucket数量时,为此类型。比如我们上面的例子收集了SIZE 30的直方图信息,对于N1,N2字段来说都超过了30个distinct值,因此为HEIGHT BALANCED类型。此时dba_tab_histograms视图中的ENDPOINT_NUMBER字段就不再是表示有多少条记录了,而仅仅表示bucket编号,SIZE 30的话,就是简单的0-30(需要31个bucket才可以表示SIZE 30)。主要是在于ENDPOINT_VALUE字段,实际上是这样分的,分了30个bucket,现在T表总共是10000条记录,那么每个bucket里面大概会是333条记录。

第一个ENDPOINT_VALUE的值相当于如下SQL的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=333[/sourcecode]
第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=333*2[/sourcecode]
第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。
[sourcecode language=”sql” light=”true”]select max(n2) from (select n2 from t order by n2) where rownum<=333*3[/sourcecode]

依次类推。正是这样的信息告诉了Oracle数据的分布情况,试想一下,如果连续3个bucket的ENDPOINT_VALUE值都10,那么也就是说至少有2个bucket中的记录N2字段都是10,也就是说N2=10的记录至少有666条,越多的bucket有越多相同的ENDPOINT_VALUE值,就表明数据分布越不均匀。
有一点需要额外注意的是:如果某几个bucket的ENDPOINT_VALUE值相同,那么在视图中只会记录最后一个bucket的信息。

[备注2] dba_tab_col_statistics.density字段值的含义。
官方文档中的解释“Density of the column”就跟没说一样。Density的含义是“密度”。

还是看上面的例子,当HISTOGRAM=NONE的时候,N1字段的密度是.0001,一万分之一,1万条记录在某字段上有1万个distinct值,那么该字段的密度就是一万分之一;N3字段的密度是1,1万条记录在某字段上都是相同的,只有1个distinct值,那么该字段的密度就是1。到这里应该可以比较形象的理解Density了。那么对于N2字段来说呢,密度是.00990099,很简单了,1万条记录里面有101个distinct值,10000/101/10000=.00990099。

DENSITY值是会影响CBO判断执行计划的,而回到前文的例子,我们比较一下有直方图和没有直方图时候的同一列的DENSITY值,就会发现很要命的事情,直方图很大地影响到了密度值,目前还没有更科学的方法去研究直方图是如何影响密度的,从而又会对CBO的判断产生多大影响(至少我还没有研究到),但是至少我们可以知道直方图只应该存在在必须存在的列上,因为除了不必要的收集会消耗不必要的资源,它有更多不可预知的影响会导致性能问题。

[备注3] 参阅其它的文章
Choosing An Optimal Stats Gathering Strategy
DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS

ADRCI Purge is NOT What You Think It Should Be

ADRCI是在Oracle11g以后提供的实用程序,用来更加灵活的检查和分析各个ORACLE_HOME的告警文件,跟踪文件。

根据文档和帮助,ADRCI是具有purge功能的。

adrci> help purge

  Usage: PURGE [[-i  |  ] |
               [-age  [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:

  Purpose: Purge the diagnostic data in the current ADR home. If no
           option is specified, the default purging policy will be used.

  Options:
    [-i id1 | id1 id2]: Users can input a single incident ID, or a
    range of incidents to purge.

    [-age ]: Users can specify the purging policy either to all
    the diagnostic data or the specified type. The data older than 
    ago will be purged

    [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
    data to be purged.

  Examples:
    purge
    purge -i 123 456
    purge -age 60 -type incident

仅仅看这个帮助,我们认为的ADRCI Purge功能是怎样的呢?比如我们执行了下面这个命令,那么Oracle应该将我们的告警日志中在距现在这个时刻1440分钟(也就是1天)以前的所有日志内容删除,是这样吧?可是确实是这样吗?

adrci> PURGE -age 1440 -type ALERT

我们测试一下。

adrci> show home
ADR Homes: 
diag/rdbms/orcl/orcl
diag/tnslsnr/dbserver/listener
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail
2010-05-02 20:04:56.447000 +08:00
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2010-05-02 20:04:58.182000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2010-05-02 20:04:59.607000 +08:00
Starting background process QMNC
QMNC started with pid=18, OS id=1405 
2010-05-02 20:05:00.653000 +08:00
Completed: ALTER DATABASE OPEN
2010-05-02 20:05:07.033000 +08:00
Starting background process CJQ0
CJQ0 started with pid=19, OS id=1426 
2010-05-02 20:10:00.609000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=1587 
2010-05-02 20:11:47.241000 +08:00
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: /app/oracle/oradata/orcl/redo01.log

现在告警日志中存在的某两次记录时间是2010-05-02 20:04:56和2010-05-02 20:11:47。我们准备删除掉20:04前后的告警信息,保留20:11的。

[oracle@dbserver alert]$ date
Sun May  2 20:14:20 CST 2010
[oracle@dbserver alert]$ pwd
/app/oracle/diag/rdbms/orcl/orcl/alert
[oracle@dbserver alert]$ ls -l
total 24
-rw-r----- 1 oracle dba 20925 May  2 20:11 log.xml

当前的时间是2010-05-02 20:14。检查了Oracle11g中告警日志的默认存储位置,最后的一次更新时间是20:11。

[oracle@dbserver alert]$ date
Sun May  2 20:15:51 CST 2010

adrci> PURGE -age 5 -type ALERT
adrci> show alert -tail
2010-05-02 20:04:56.447000 +08:00
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
2010-05-02 20:04:58.182000 +08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2010-05-02 20:04:59.607000 +08:00
Starting background process QMNC
QMNC started with pid=18, OS id=1405 
2010-05-02 20:05:00.653000 +08:00
Completed: ALTER DATABASE OPEN
2010-05-02 20:05:07.033000 +08:00
Starting background process CJQ0
CJQ0 started with pid=19, OS id=1426 
2010-05-02 20:10:00.609000 +08:00
Starting background process SMCO
SMCO started with pid=20, OS id=1587 
2010-05-02 20:11:47.241000 +08:00
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: /app/oracle/oradata/orcl/redo01.log

[oracle@dbserver alert]$ ls -l
total 24
-rw-r----- 1 oracle dba 20925 May  2 20:11 log.xml

我们执行的命令成功了,但是很明显我们希望的目的没有达到,实际上,就好似没有执行这条命令一样,我们想要删除5分钟以前的内容,执行purge命令的时刻是20:15,那么理应在20:10之前的告警日志内容都被删除掉,但是实际上告警日志中20:04的内容依然还在,并且我们也可以看出log.xml文件的最后更新时间仍然是20:11。

那么purge命令到底有没有效果呢?

[oracle@dbserver alert]$ date
Sun May  2 20:28:34 CST 2010

adrci> PURGE -age 5 -type ALERT
adrci> show alert -tail
adrci> 

[oracle@dbserver alert]$ ls -l
total 0

在20:28的时候我们再次执行了purge命令,仍然是尝试删除5分钟以前的内容,这次的结果是所有的告警日志内容都被清空了,告警日志文件log.xml也被删除了。这条命令实际上有效果的,但是为什么不是我们期望的那样灵敏呢?为什么第一次没有成功,而第二次却成功了呢?

原因在于,ADRCI Purge命令的操作单元是文件而不是文件内容,只有整个文件的最后更新时间在我们指定的purge命令条件之前,该文件才会被删除,也就是说,要不整个文件都删除,要不一点儿也不删除。对于我们后一次执行的命令,因为文件的最后更新时间是20:11,这个时间点在我们执行purge命令时间点的5分钟以前,因此整个告警日志都被删除。而对于我们第一次执行的命令,因为告警日志中不但包含了执行purge命令时间点5分钟之前的内容,还包括了5分钟之前到执行purge命令时间点之间的内容(20:11的内容位于20:10和20:15之间),因此文件无法被删除。

无论是purge的什么TYPE,或者说purge -i 命令也同样是这样,对于INCIDENT来说,每个Incident ID在INCIDENT目录中都是一个子目录,因此很容易做到整个子目录的删除。

不得不说这样的purge命令并不是我们期望的,我也希望看到在今后的版本升级中ADRCI Purge可以变得如我们一开始期望的那样,直接删除符合条件的文件内容,而不是现在这样删除文件。

我们可以预见到对于特别繁忙的系统,假设每分钟都有告警日志内容生成,那么我们始终不可能执行成功purge,我们只能使用purge -age 0 -type ALERT这样的命令来清除所有的log内容。那么现在这样的purge命令我们该如何精确使用呢?

实际上仍然需要另外的客户化脚本,比如在crontab中定义每天晚上的某个时刻将现在的log.xml文件更名为log_`date`.xml,然后统一使用purge命令删除符合条件的告警日志文件备份。特别需要注意的是,更名规则要符合purge命令的检查规范,purge命令只会对相应目录中以log开头以xml结尾的文件进行检查。

PS:purge命令只会清除xml文件,对于trace目录中的和Oracle11g之前版本兼容的alert_.log文件并不会有任何改动。

[oracle@dbserver trace]$ pwd
/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@dbserver trace]$ ls -l alert*
-rw-r----- 1 oracle dba 41444 May  2 20:20 alert_orcl.log

How to Generate AWR Differences Report

生成系统正常期间内和故障期间内的AWR性能数据比较报告,可能可以帮助我们更加简单的定位问题所在。

可以通过awrddrpt.sql脚本生成。

SQL> @?/rdbms/admin/awrddrpt.sql

脚本会要求输入需要比较的第一份和第二份AWR报告的起始结束Snap ID,生成的AWR报告基本上如下图所示。

在普通的AWR报告各个部分都增加了1st, 2nd以及%Diff,这表示第一份时,第二份时各自的情况以及两次的相差百分比。

如果我们因为某些原因(比如权限问题)无法读取awrddrpt.sql,那么也可以通过DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT函数来完成。

DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(
   dbid1     IN NUMBER,
   inst_num1 IN NUMBER,
   bid1      IN NUMBER,
   eid1      IN NUMBER,
   dbid2     IN NUMBER,
   inst_num2 IN NUMBER,
   bid2      IN NUMBER,
   eid2      IN NUMBER)
  RETURN awrdrpt_text_type_table PIPELINED;

dbid通过以下SQL获取。

SQL> select DBID from v$database;
 
      DBID
----------
 777920305

inst_num通过以下SQL获得。

SQL> select instance_number from v$instance;
 
INSTANCE_NUMBER
---------------
              1

bid和eid分别是Begin Snap ID和End Snap ID,通过以下SQL获得。

SQL> select snap_id, end_interval_time
  2  from dba_hist_snapshot
  3  where end_interval_time > trunc(sysdate-1)
  4  order by snap_id;
 
   SNAP_ID END_INTERVAL_TIME
---------- --------------------------------------------------------------------------------
       377 25-FEB-10 01.10.10.657 PM
       378 25-FEB-10 02.00.21.884 PM
       379 25-FEB-10 04.49.00.861 PM

将上述获得值全部作为参数值传入AWR_DIFF_REPORT_TEXT函数,执行即可。由于输出结果较长,使用spool打印到文件中方便查看。

SQL>spool awrdiff_1_377_1_378.txt
SQL> select * from 
  2  TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(777920305,1,377,378,
  3  777920305,1,378,379));
SQL>spool off

About RAW Devices Not Supported in Oracle 11g release 2

很早之前就有传闻Oracle12g(也许不是g)版本将不再支持裸设备。而这份声明在最新发布的Oracle11gR2中就已经有所体现。以下完全通过阅读文档而得,并非实践经验,各位自行取舍。

Block and Raw Devices Not Supported with OUI
With this release, OUI no longer supports installation of Oracle Clusterware files on
block or raw devices. Install Oracle Clusterware files either on Automatic Storage
Management diskgroups, or in a supported shared file system.

不再支持将文件存储在裸设备上,但是仅仅局限于使用OUI图形界面创建数据库,那么命令行方式应该还是可以的。

For new installations, OCR and voting disk files can be placed either on ASM, or on a
cluster file system or NFS system. Installing Oracle Clusterware files on raw or block
devices is no longer supported, unless an existing system is being upgraded.

Oracle Clusterwae需要的OCR和Voting disk可以存储在ASM或者集群文件系统或者NFS中,对于全新安装,裸设备不再被支持,但是如果是升级而来的话(比如从10g升级到11g),仍然支持。

Voting Disk Backup Procedure Change
In prior releases, backing up the voting disks using a dd command was a required
postinstallation task. With Oracle Clusterware release 11.2 and later, backing up and
restoring a voting disk using the dd command is not supported.
Backing up voting disks manually is no longer required, as voting disks are backed up
automatically in the OCR as part of any configuration change and voting disk data is
automatically restored to any added voting disks.

之前Voting disk因为是存储在裸设备中的,因此备份需要使用dd命令来手动执行,在11gR2中,通过dd备份Voting disk不再被支持,同时,也无需通过任何方法手动备份Voting disk了,备份将自动进行。

Oracle Database 11gR2 Released on Linux

依然是优先发布了再Linux平台上的Oracle Database 11gR2。在OTN上的下载页面可以看到包含了比以前更多的内容,而有些仅仅是称呼发生了变化。

oracle11gR2

1. Database安装盘变为了2张,之前的版本一直只有1张,11gR1的安装盘容量为1.8G,而11gR2已经超过2G。解压两个zip文件到同一个database目录下,再运行runInstaller进行数据库安装。

2. Client安装盘比以前容量更大了,但是可以认为基本上变化不大。

3. Grid Infrastructure安装盘应该就是之前的Oracle Clusterware安装盘,但是容量明显增加,从之前的300M到现在900M,我自己还没有下载完毕,所以具体增加了哪些内容还不知道,有已经安装了的给些注解吧。在之前需要用dbca创建的ASM实例功能,已经挪动到Grid Infrastructure安装中,也同时包括了Oracle Clusterware以及ASMFS等群集相关软件的安装。

4. Examples安装盘就是以前的Companion,内容也应该就是之前的Oracle Express的那些内容。

5. Gateways安装盘基本上没有变化。

6. De-install Utility安装盘是全新的内容,从字面上看应该卸载安装的一些工具,莫非将Universal Installer做了专门的产品?具体内容等下载完毕再见分晓。

Update@2009-09-28
对于De-install Utility,在Oracle11gR2的Installation Guide中有如下一段介绍。

Deinstallation Tool
Oracle Universal Installer no longer removes Oracle software. Use the new Deinstallation Tool (deinstall) available on the installation media before installation as well as in the Oracle home directory after installation.

Run this tool from ORACLE_HOME first. It is located in:
$ORACLE_HOME/deinstall.

An error occurs if there are any missing files and the tool prompts you to download the standalone version available in the deinstall directory on the installation media.

实际上在安装完Oracle Software之后,$ORACLE_HOME/deinstall就已经存在了,基本上是一个字符界面的完全删除安装的工具,按照提示运行完deinstall将会删除包括ASM、database instance、software、oraInventory以及oraInst.loc在内的所有内容,基本上是一次非常干净的反安装。