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

Using Safari5 Reader for Reading Technical Posts

最新Release的Mac下的浏览器Safari5除了全面支持HTML5之外,还有一个着重提到的功能就是Reader。

关于Reader的赞美之辞可以参看《改变阅读习惯的Safari 5阅读器》

那么实际上Reader对于我个人的用处是在浏览DBAsupport.com站点的文章,此站点文章质量较高而且还时常出现不少新奇有趣的文章。但是这个站点恼人的地方在于:1.在Google Reader中无法看到全文 2.在站点上直接浏览文章的话要承受大量的广告。

比如原站点中的文章大概看上去是这样五颜六色的。

使用了Safari5 Reader以后,变成了一篇白底黑字的清爽文章。

但是目前这个版本的Reader仍然有需要改进的地方,特别是对于可能会有大量代码的技术文章,那些代码在原文中有rss格式化,但是Reader转换以后格式就不正确了,这样会导致较长的代码有一部分无法看到。

仍然是上面这片技术文章。原文中的格式如下图,虽然也不尽美观,但是注意那些小字体的部分,至少可以看到全部文字。

而在Reader中最后超出页面的那些文字都无法看到了,而且似乎是苹果太在意用户阅读体验了,甚至连横向的滚动栏也没有。是的,我知道Reader中的字体可以缩小,但是即使缩到最小也仍然有一部分在页面之外。

Hope will be fixed soon.

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

Exp Empty Table in Oracle11gR2

先来看一下例子。我们创建一张表T2。

[sourcecode language=”sql” light=”true”]SQL> create table t2 (n number);

Table created.

SQL> desc t2
Name Null? Type
—————————————– ——– —————————-
N NUMBER[/sourcecode]

尝试使用exp将此表导出。

D:\Temp>exp kamus/oracle tables=t2

Export: Release 11.2.0.1.0 - Production on Fri Apr 16 18:11:51 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing opt
ions
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: KAMUS.T2 does not exist
Export terminated successfully with warnings.

报错说这张表并不存在。这是让很多客户费解的地方,在测试库中创建应用的表结构,然后再将表结构exp到产品库中去,这是很多客户常用的方法,但是在11gR2中如果这些表是新创建的没有插入过任何一条记录,那么将会碰到上面这样的错误。

原因在于11gR2中的新功能 – Deferred Segment Creation(延迟段创建),默认情况下这个功能是启用的。

[sourcecode language=”sql” light=”true”]SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
———————————— ——————– ——————–
deferred_segment_creation boolean TRUE[/sourcecode]

延迟段创建的含义是当此新创建一个可能会有Segment的对象时(比如表、索引、物化视图等),如果这个对象中还没有任何记录需要消耗一个Extent,那么将不会在创建对象时自动创建Segment,这样做的好处无疑是在创建对象时大大提高了速度。

对于上例中的T2表,我们在创建结束就立刻检查DBA_SEGMENTS视图,会发现没有任何记录。

[sourcecode language=”sql” light=”true”]SQL> select segment_name from user_segments where segment_name=’T2′;

no rows selected[/sourcecode]

而对于exp程序而言,当仅仅存在Object的定义而没有相应的Segment时,就会报出EXP-00011对象不存在的错误。

解决方法就很简单了,以下方法任选其一。

1. 设置DEFERRED_SEGMENT_CREATION为FALSE,这样创建对象时就会自动创建Segment

2. 在创建对象时,明确指定立刻创建Segment
[sourcecode language=”sql” light=”true”]create table t2 (n number) SEGMENT CREATION IMMEDIATE;[/sourcecode]

3. 使用expdp替代exp(Datapump本身就是Oracle10g以后的推荐工具)

D:\Temp>expdp kamus/oracle tables=t2

Export: Release 11.2.0.1.0 - Production on Fri Apr 16 18:14:41 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing opt
ions
Starting "KAMUS"."SYS_EXPORT_TABLE_01":  kamus/******** tables=t2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "KAMUS"."T2"                                    0 KB       0 rows
Master table "KAMUS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for KAMUS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\ADMIN\ORCL\DPDUMP\EXPDAT.DMP
Job "KAMUS"."SYS_EXPORT_TABLE_01" successfully completed at 18:15:10

Update@2012-05-29
在最新的11.2.0.3中,该问题已经不再存在,即使没有segment,用exp也仍然可以正常导出。

SQL> host exp kamus/oracle tables=t_test

Export: Release 11.2.0.3.0 - Production on Tue May 29 14:56:21 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                         T_TEST          0 rows exported
Export terminated successfully without warnings.

SQL> select segment_name from user_segments where segment_name='T_TEST';

no rows selected

How to Use Oracle Restart in Oracle 11gR2

Oracle Restart是数据库组件监控方法向单实例数据库中的延展,之前srvctl命令可以用来管理RAC数据库中的各个组件,安装了Oracle Restart之后,对于单实例数据库组件(包括数据库实例,监听,ASM实例,ASM磁盘组,自建Service等)也可以使用srvctl命令来管理,同时Oracle会在这些组件发生意外关闭的时候自动重新启动。对于在Linux单机上安装的Oracle Instance,以前需要配置dbstart或者dbstop脚本来实现在操作系统起停时自动起停数据库实例的操作,现在通过Oracle Restart则无需这两个脚本了。

详细文档请参看 About Oracle Restart

安装其实很简单,实际上没有单独安装Oracle Restart的过程,只需要安装Oracle Grid Infrastructure就可以,在安装的时候选择“仅安装网格基础结构软件”,我们的测试环境没有使用ASM,因此选择只安装软件可以跳过配置ASM的步骤。

如何在Windows单机上测试ASM,感兴趣的朋友可以参看 – How to use Files in place of Real Disk Devices for ASM

在安装完GI以后,还需要使用root用户运行以下脚本,否则在$GI_HOME/bin目录中不会生成例如crsctl,crs_start之类的脚本,当然Oracle Restart也不会启动。

# $GRID_HOME/crs/install/roothas.pl

在运行完roothas.pl以后,可以使用crsctl命令来查看HAS(High Availability Services)的运行情况。

$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ons
               OFFLINE OFFLINE      solaris                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                                                   
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.evmd
      1        ONLINE  ONLINE       solaris 

我们是在创建完数据库之后再安装Grid Infrastructure的,因此还需要手工将数据库实例和监听加入Oracle Restart配置中,根据文档如果是在装完Grid Infrastructure之后再用netca创建监听,dbca创建数据库的话,将会自动加入Oracle Restart中,我个人没有测试。

添加数据库实例:

srvctl add database -d orcl -o D:\oracle\product\11.2.0\dbhome_1

检查配置情况:

D:\Temp>srvctl config database -d orcl
数据库唯一名称: orcl
数据库名:
Oracle 主目录: D:\oracle\product\11.2.0\dbhome_1
Oracle 用户: system
Spfile:
域:
启动选项: open
停止选项: immediate
数据库角色: PRIMARY
管理策略: AUTOMATIC
磁盘组:
服务:

检查数据库实例状态:

D:\Temp>srvctl status database -d orcl
数据库正在运行。

注意:如果在添加数据库实例之前数据库实例已经启动了,那么需要重新启动一次数据库,Oracle Restart才能正确监视到数据库实例状态。

添加监听:

srvctl add listener -o D:\oracle\product\11.2.0\dbhome_1

注意,如果是分用户安装(比如在Linux操作系统中),比如GI以grid用户安装,Oracle Database以oracle用户安装,那么在添加监听的时候应该指定GI_HOME,而非ORACLE_HOME。命令如下:

$ srvctl add listener -o /u02/app/oracle/product/11.2.0/grid

否则在用srvctl start listner命令启动监听的时候,会报错。

$ srvctl start listener
PRCR-1079 : Failed to start resource ora.LISTENER.lsnr
CRS-5010: Update of configuration file "/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora" failed: details at "(:CLSN00014:)" in "/u02/app/oracle/product/11.2.0/grid/log/solaris/agent/ohasd/oraagent_grid/oraagent_grid.log"
CRS-2674: Start of 'ora.LISTENER.lsnr' on 'solaris' failed

如果在添加监听的时候,监听已经启动,那么会遇到PRCN-2065错误,停止监听以后再次添加即可。

D:\Temp>srvctl add listener -o D:\oracle\product\11.2.0\dbhome_1
PRCN-2061 : 无法添加监听程序 ora.LISTENER.lsnr
PRCN-2065 : 端口 1521 在指定的节点上不可用
PRCN-2035 : 端口 1521 在节点或虚拟主机上不可用: Kamus-PC

配置完毕以后,就可以通过srvctl命令起停数据库实例和监听了。

srvctl start|stop database -d 
srvctl start|stop listener [-l ]

可以通过crs_stat命令查看Oracle Restart管理的各个资源情况,这以前是我们查看Oracle Clusterware的方式。

D:\Temp>crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....ER.lsnr ora....er.type ONLINE    ONLINE    kamus-pc
ora.cssd       ora.cssd.type  ONLINE    ONLINE    kamus-pc
ora.orcl.db    ora....se.type ONLINE    ONLINE    kamus-pc

测试Oracle Restart的功能,我们可以在后台进程中直接杀死oracle.exe,可以观察到数据库实例立刻会重新启动。

注意:所有在SQL*PLUS中发出的关闭数据库命令均不会导致Oracle Restart重启实例,包括shutdown abort。

如果暂时不想用Oracle Restart管理某个资源,可以通过srvctl disable命令暂时禁用,需要的时候再enable即可。

srvctl disable database -d orcl

如果机器名称发生了改变,需要重新配置Oracle Restart,请参考Metalink Note [ID 986740.1]

Issues when Add Nodes into RAC 10g

近期在客户处将原先的4节点RAC扩充到8节点。期间碰到一些问题,总结一下。

系统环境:
Redhat Enterprise Server 4,Oracle RAC 10.2.0.4,OCFS2

一。现象:
往CRS中添加节点时运行rootaddnode.sh会报PRKC-1044错误。
PRKC-1044 – FAILED TO CHECK REMOTE COMMAND EXECUTION

原因:
在配置ssh对等性的时候,设置的passphrase非空。

解决方法:
重新配置ssh对等性,在Enter passphrase (empty for no passphrase): 时直接回车,不要输入任何密码。

二。现象:
在使用dbca往RAC中添加新节点时报TNS-04602错误。
TNS-04414: File Error caused by: TNS-04602: Invalid syntax error: Expected “LITERAL” before or at…

原因:
数据库中没有设置remote_listener参数。

解决方法:
[sourcecode language=”sql” light=”true”]alter system set remote_listener=’LISTENERS_RAC’ scope=both;[/sourcecode]

三。总结
分别使用CRS_HOME和ORACLE_HOME中的addNode.sh脚本往CRS和RAC中添加节点是方便高效的。

How to trace another session using PL/SQL Package

1.DBMS_SYSTEM
[sourcecode language=”sql” light=”true”]EXECUTE dbms_system.set_sql_trace_in_session (sid,serial,TRUE);[/sourcecode]
这样的trace相当于event 10046 level 1。

停止:EXECUTE dbms_system.set_sql_trace_in_session (sid,serial,FALSE);

[sourcecode language=”sql” light=”true”]EXECUTE dbms_system.set_ev (sid,serial,10046,12,”);[/sourcecode]
这样直接设置某个会话的10046 event level 12,相当于level 4+level 8,也就是binds和waits都记录下来。

停止:EXECUTE dbms_system.set_ev (sid,serial,10046,0,”);

2. DBMS_SUPPORT
[sourcecode language=”sql” light=”true”]EXECUTE dbms_support.start_trace_in_session (sid,serial,binds=>true,waits=>true);[/sourcecode]
这样的trace相当于event 10046 level 12。

停止:dbms_support.stop_trace_in_session(sid,serial);

备注:dbms_support包默认不存在,需要执行下述SQL来安装。
$ORACLE_HOME/rdbms/admin/dbmssupp.sql

3. 在Oracle10g以后可以使用DBMS_MONITOR
[sourcecode language=”sql” light=”true”]EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(sid,serial, binds=>true,waits=>true);[/sourcecode]
这样的trace相当于event 10046 level 12。

停止:EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(sid,serial);

Videocast Recording for Oracle11gR2

为了给即将发布的Oracle11gR2预热,昨天跟eygle两个人被Oracle公司产品战略部邀请去做一次访谈录影。可以看做谈论Oracle数据库的锵锵三人行,其实是4个人了。

收到的邀请邮件中对于此次访谈有这样的描述。

本次访谈的形式是四至五人参加的圆桌讨论,由甲骨文公司产品战略部总监刘松担任主持人。围绕主持人的提问,大家可以各抒己见,畅谈对企业信 息基础架构的看法,分享成功经验,探讨具体技术实现,展望Oracle数据库解决方案帮助企业实现业务创新和管理创新的前景。

本次访谈摄像、剪辑等工作均由专业视频制作团队完成,录制好的视频时长约一个小时。 完成录制之后,访谈视频将会在搜狐、新浪、凤凰等门户网站,以及 CIO360、51CTO、计世网、IT168等信息化专业论坛的视频访谈版块发布,并由专业的传媒公司负责后期推广。相信会引发网友热议,并对相关解决方案和客户品牌起到很好的宣传推广作用。

访谈的整个过程很轻松,就像是在跟朋友畅谈Oracle数据库从古至今的发展演变以及让大家期待的Oracle11gR2新功能对于企业级IT应用框架将产生的影响与帮助。

录制结束以后,Oracle的赠品 – 一把扇子很有诗情画意。

在扇子背脊上的“Oracle 甲骨文”字样显得颇有中国古风。

录制完毕,又正好赶上老万在Oracle人寿大厦Office举办的DBA Round Table结束,谢谢老万请客,欢迎老万再次回到北京生活,并预祝新工作顺利。

备注:如果看不到上面的图片,那么就是Picasa Web Album还未被解封,请看官在自己机器的hosts文件中添加如下字样。(hosts文件通常位于C:\WINDOWS\system32\drivers\etc目录下)

203.208.39.104 picasaweb.google.com
203.208.39.99 lh1.ggpht.com
203.208.39.99 lh2.ggpht.com
203.208.39.99 lh3.ggpht.com
203.208.39.99 lh4.ggpht.com
203.208.39.99 lh5.ggpht.com
203.208.39.99 lh6.ggpht.com

Something about Oracle Load Balance

虽然在Oracle的立场上,总是建议客户能够更好地规划自己的应用,在有其它负载平衡方法的时候,尽量不要依赖于Oracle的Load Balance方法,但是往往在给客户配置完Oracle RAC数据库以后,客户都会要求要测试负载平衡(Load Balance)和TAF(Transparent Application Failover),并且将这两个测试作为RAC是否安装成功的标准。

这是一件很无奈的事情,像把旁枝末节看作了主要功能,甚至有些买椟还珠的感觉,但是毕竟这是客户,更了解Oracle Load Balance(后文用LB表示),才可以更好满足客户需求。

本文不牵涉TAF(可以参看老熊关于TAF的系列文章PartIPartIIPartIII),如何在Oracle10g之后版本中在服务器端service层面设置TAF,可以参看Metalink Note: 404644.1

对于LB,在Oracle10g之前有Client端和Server端两种,在Oracle10g之后又推出了Server端Service层面的LB配置,本文也不涉及Service层面的LB。

在Oracle9i,10g,11g版本中都适用的LB配置分为以下两种。

(1) Client Side Connect Time Load Balance
(2) Server Side Listener Connection Load Balance (此处的Listener用以跟10g之后的Server Side Service Load Balance区分开)

1. Client Side Connect Time Load Balance
既然是Client端的LB,那么也就是不需要在数据库服务器端配置任何参数,完全由客户端机器上的tnsnames.ora文件中对于TNS的配置来决定,实际上也就是LOAD_BALANCE参数。

看一个例子,下面这样的TNS配置就是启用了客户端的LB。

CLIENT_LOADBALANCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac_lb)
    )
  )

(LOAD_BALANCE = yes)指示SQLNet随机选择ADDRESS_LIST列表中的任意一个监听,将客户端请求发送到此监听上,通过这种方法来实现负载平衡。如果(LOAD_BALANCE = no)那么将会按照ADDRESS_LIST列表中的顺序选择监听,只要这个监听能够正常连接那么就使用该监听。

因此在某些负载平衡的解决方案中会使用(LOAD_BALANCE = no)但是在多个客户端或者应用服务器端配置顺序不同的ADDRESS_LIST,以此来实现人为的负载平衡。

如果在TNS配置时使用的是ADDRESS_LIST语法,那么必须显示设置LOAD_BALANCE = yes,默认值LOAD_BALANCE = no。

2. Server Side Listener Connection Load Balance
要实现server side load balance要求监听能够知道在整个RAC环境中的各节点负载情况,节点负载情况是由PMON进程来定期更新的,而要让PMON进程能够通知其它节点自己节点的负载情况则需要设置数据库初始化参数REMOTE_LISTENER。

在RAC数据库服务器端的每个节点上的tnsnames.ora文件中设置如下的TNS名称。

LISTENERS_RAC =
(DESCRIPTION =  
 (ADDRESS_LIST =   
  (ADDRESS = (PROTOCOL = TCP)(HOST=node1-vip)(PORT = 1521))   
  (ADDRESS = (PROTOCOL = TCP)(HOST=node2-vip)(PORT = 1521))   
 )  
) 

然后设置REMOTE_LISTENER参数值为LISTENERS_RAC。

sql> alter system set REMOTE_LISTENER='LISTENERS_RAC' scope=both;

当某个实例的监听收到客户端连接请求,将会统筹考虑RAC环境中各个节点的负载情况,然后将该连接传递到具有最小负载的节点的最小负载实例上。

在10gR2版本之后,节点和实例的负载计算通过lbscore来完成,而lbscore则由两个动态值决定:”goodness” 和 “delta”,这两个值均由PMON来定期更新,计算公式如下:
Lbscore = Goodness (Received from PMON update)
Delta = New Delta (Received from PMON update)
在PMON定期更新的间隔,如果有新连接进入,那么lbscore的计算公式是:
Lbscore = Lbscore(previous) + Listener Delta

可以通过添加监听的trace(TRACE_LEVEL_LISTENER = 16)来获取lbscore的产生情况,更详细的文档请参看Metalink Note: 263599.1。

关于Failover,多说两句,其实Failover也有两种。
一种是Client-side Failover。细分为Client-side Connect Time Failover,由客户端TNS配置的(failover=on)参数控制,或者Client-side TAF,由客户端TNS配置中的failover_mode参数控制。
另外一种是Server-side Failover。通常跟Server-side Service LB同时配置,通过modify service来完成。