OPatch failed with error code 73 when patch Oracle 10.2.0.4 on Mac OS X

现在Mac OS X中的Oracle数据库最新版本是10.2.0.4 (没有10.2.0.5更没有11g)。如果想给该版本数据库打上PSU补丁的时候,会遇到以下错误。

Running prerequisite checks...
Prerequisite check "CheckPatchApplicableOnCurrentPlatform" failed.
The details are:
Patch (  ) is not applicable on current platform.
Platform ID needed is : 46
Platform IDs supported by patch are: 293
UtilSession failed: Prerequisite check "CheckPatchApplicableOnCurrentPlatform" failed.

OPatch failed with error code 73

这个错误是由于Bug # 8647770引起的。解决方法如下:
1. 下载patch 8647770.
2. 安装补丁

$ export OPATCH_PLATFORM_ID=293
$ cd 8647770
$ opatch apply

安装完此补丁以后,再重新运行PSU Patch,就可以正常打上补丁了。

Notes for Oracle Database PSU/CPU

1. 什么是PSU/CPU?
CPU: Critical Patch Update
Oracle对于其产品每个季度发行一次的安全补丁包,通常是为了修复产品中的安全隐患。

PSU: Patch Set Updates
Oracle对于其产品每个季度发行一次的补丁包,包含了bug的修复。Oracle选取被用户下载数量多的,并且被验证过具有较低风险的补丁放入到每个季度的PSU中。在每个PSU中不但包含Bug的修复而且还包含了最新的CPU。

2. 如何查找最新的PSU?
每个数据库版本都有自己的PSU,PSU版本号体现在数据库版本的最后一位,比如最新的10.2.0.5的PSU是10.2.0.5.3,而11.2.0.2的最新PSU则是11.2.0.2.2。
MOS站点中Oracle Recommended Patches — Oracle Database [ID 756671.1] 文档中查到各个产品版本最新的PSU。
如果你记不住这个文档号,那么在MOS中以“PSU”为关键字搜索,通常这个文档会显示在搜索结果的最前面。

注意:必须购买了Oracle基本服务获取了CSI号以后才有权限登陆MOS站点。

3. 如何正确安装PSU?
每个PSU安装包中都包含一个README.html文档,其中描述了如何安装该PSU,有些PSU是可以直接安装的,而有些PSU则必须要求安装了上一个版本的PSU之后才能继续安装。比如对于10.2.0.4版本的数据库来说,PSU 10.2.0.4.4可以直接安装在最原始的10.2.0.4.0版本中,而最新的PSU 10.2.0.4.8则必须要求先安装10.2.0.4.4。这些信息在README.html中都可以找到,所以请仔细阅读该文档。

通常安装PSU是比较简单的,步骤如下:
1) 安装PSU需要使用到opatch,在README.html中有描述该PSU需要的最低版本opatch,如果当前opatch版本过低,则需要先下载Patch 6880880,该Patch中包含最新的opatch,只需要解压覆盖原先的$ORACLE_HOME/OPatch目录即可。

查看当前的opatch版本,可以使用opatch version命令。

$ opatch version
Invoking OPatch 10.2.0.5.2

OPatch Version: 10.2.0.5.2

OPatch succeeded.

2)安装PSU,请仔细阅读README.html,确认安装命令,通常是简单的opatch apply。

opatch apply

3)更新数据库,将修改过的SQL文件应用到数据库中,很多DBA在执行完上述安装命令以后就不再进行这一步,那么实际上PSU是没有完整安装的。

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

注意:如果PSU是overlay PSU,比如10.2.0.4.8,则需要执行@catbundle.sql opsu apply,同样这些在README.html中都有详细描述。

4)重新编译CPU相关视图。该步骤在一个数据库上永远只需要执行一次,是为了完成在2008年1月份第一次发布CPU补丁时的后续工作,如果在安装以前的PSU或者CPU时执行过这个步骤那么就可以无需再次执行,另外,即使不执行该步骤,数据库也是正常运行的,只不过意味着2008年1月份的CPU补丁没有正常结束安装。

cd $ORACLE_HOME/cpu/view_recompile
sqlplus / as sysdba
SQL> @recompile_precheck_jan2008cpu.sql
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT

注意:该步骤由于需要重新编译大量视图,因此要启动数据库到upgrade状态才可以完成。也就是将引起停机时间。

4. 如何确认当前数据库已经安装了什么PSU/CPU?
无论是从V$VERSION或者DBA_REGISTRY或者PRODUCT_COMPONENT_VERSION视图中,都无法查找到PSU的信息,这些视图中始终显示的是最原始的版本,比如10.2.0.4.0。

最常用的方法是使用opatch命令。在打完最新的PSU 10.2.0.4.8的10.2.0.4数据库中会有以下显示。

$ opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU'
9654991    11724977  Wed May 25 16:37:17 CST 2011   DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 
9952234    11724977  Wed May 25 16:37:17 CST 2011   DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 
10248636   11724977  Wed May 25 16:37:17 CST 2011   DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 
11724977   11724977  Wed May 25 16:37:17 CST 2011   DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 
8576156    9352164   Wed May 25 15:10:48 CST 2011   DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
8833280    9352164   Wed May 25 15:10:48 CST 2011   DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
9119284    9352164   Wed May 25 15:10:48 CST 2011   DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
9352164    9352164   Wed May 25 15:10:48 CST 2011   DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)

备注:在11gR2中,opatch lsinventory命令就可以显示已经打过的PSU名称了,而无需再增加-bug_fixed参数。

$ opatch lsinventory | grep "Patch Set Update"
Patch Description:  "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Patch Description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)"

另外的方法是查看registry$history表。

SQL> select action,comments from registry$history;

ACTION			       COMMENTS
------------------------------ --------------------
APPLY			       PSU 10.2.0.4.4
APPLY			       PSU 10.2.0.4.8
CPU			       view recompilation

注意:该表的内容是在上述安装PSU步骤的第三步中运行catbundle.sql才会插入的,因此如果该步骤忘记执行,则此表中无记录。因此我们在作数据库健康检查的时候不但要用opatch检查当前数据库最新的PSU补丁,也要检查registry$history表,以确认其它DBA是否正确地完成了PSU的安装。

如果多个PSU的安装都忘记了执行上述第三步,可以通过以下方法依次补作。

$ ls -l $ORACLE_HOME/psu
total 0
drwxrwxrwx   2 oracle     dba             96 Oct 16  2010 10.2.0.4.4
drwxrwxrwx   2 oracle     dba             96 Oct 16  2010 10.2.0.4.5
$sqlplus / as sysdba
SQL> @?/psu/10.2.0.4.4/catpsu.sql
SQL> @?/psu/10.2.0.4.5/catopsu.sql

更多关于CPU的信息,可以参看:Maclean的了解Oracle Critical Patch Update

5. 参考文档。
Oracle Recommended Patches — Oracle Database [ID 756671.1]
Patch Set Updates for Oracle Products [ID 854428.1]
Introduction To Oracle Database catbundle.sql [ID 605795.1]
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]

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