数据库程序员面试分类真题15

数据库程序员面试分类真题15简答题1.

执行计划里的access和filter有什么区别?正确答案：如下所示：

PredicateInformation(identifiedbyop（江南博哥）erationid):

-------------------------------------------------------

4-access("A""EMPNO"="B"."MGR")

filter("A"."EMPNO"="B"."MGR")

5-filter("B"."MGR"ISNOTNULL)

一般而言，access表示这个谓词条件的值将会影响数据的访问路径(表还是索引)；filter表示谓词条件的值不会影响数据的访问路径，只起到过滤的作用。NOTIN或MIN函数等容易产生filter操作。

对于filter而言，如果只有一个子节点，那么就是简单过滤操作(独立操作)。如果有两个或更多子节点，那么就是类似NestedLoops操作，只不过与NestedLoops差别在于，filter内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，filter操作将严重影响性能的操作，可能会导致目标SQL几天都执行不完。[考点]SQL优化

2.

统计信息包括哪几类?正确答案：Oracle数据库里的统计信息是一组存储在数据字典里，且从多个维度描述了数据库里对象的详细信息的一组数据。当Oracle数据库工作在CBO(CostBasedOptimization，基于代价的优化器)模式下时，优化器会根据数据字典中记录的对象的统计信息来评估SQL语句的不同执行计划的成本，从而找到最优或者是相对最优的执行计划。所以，可以说，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式来生成执行计划。统计信息决定着SQL的执行计划的正确性，属于SQL执行的指导思想。

统计信息主要包括6种类型，其中表、列和索引的统计信息也可以统称为普通对象的统计信息，如下图所示。

[考点]统计信息

3.

Oracle10g和11g的自动统计信息收集机制有哪些不同?正确答案：在Oracle10g之前并没有自动收集统计信息的机制，从Oracle10g开始引入了自动收集统计信息的功能，这个功能在Oracle10g中被称为自动统计信息收集(AutomaticStatisticsGathering)，在Oracle11g中被称为自动优化器统计信息收集(AutomaticOptimizerStatisticsCollection)。自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集x$系列表的内部对象统计信息。Oracle的初始化参数STATISTICS_LEVEL控制收集统计信息的级别，有三个参数值：

1)BASIC：收集基本的统计信息。

2)TYPICAL：收集大部分统计信息(数据库的默认设置)。

3)ALL：收集全部统计信息。

当使用Oracle自动收集统计信息时，必须要确保Oracle的参数STATISTICS_LEVEL的值为TYPICAL或者ALL。默认值为TYPICAL，该值可以确保数据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值能胜任大多数的环境，并且Oracle不推荐去修改该值。

DBA可以根据Oracle提供的脚本$ORACLE_HOME/rdbms/admin/catmwin.sql查看统计信息收集作业的整体搭建流程。有兴趣的读者可以研究下此脚本的内容。

Oracle10g和11g的自动统计信息收集机制有所不同，详见下表。

在Oracle11g中对统计信息自动收集的功能进行了加强。在Oracle10g中，如果表中变更的行数(字典表SYS.MON_MODS_ALL$中记录的INSERT+UPDATE+DELETE的总数)超过表的总行数(SYS.TABS中记录的目标表总记录数)的10%时或自上次自动统计信息收集作业完成之后目标表被执行过TRUNCATE操作，那么Oracle就会在指定时间段自动收集统计信息。在Oracle10g中，这个10%(STALE_PERCENT)是无法修改的，如果表非常大，那么10%其实是非常多的数据，这就造成统计信息不准确。在Oracle11g中，这个10%(STALE_PERCENT)是可以修改的，分为全局(DBMS_STATS.SET_GLOBAL_PREFS)、数据库级别(DBMS_STATS.SET_DATABASE_PREFS)、用户级别(DBMS_STATS.SET_SCHEMA_PREFS)和表级别(DBMS_STATS.SET_TABLE_PREFS)。其中，数据库级别和用户级别都是调用表级别的存储过程DBMS_STATS.SET_TABLE_PREFS来对表进行设置的。

表级别的设定如下：

1)修改为5%(范围为1～100)：EXECDBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME',''STALL_PERCENT',5);

2)恢复为10%：EXECDBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);

3)查询表百分比：SELECTDBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME')FROMDUAL;

4)查询全局百分比：SELECTDBMS_STATS.GET_PREFS('STALE_PERCENT')FROMDUAL;

Oracle10g的自动统计信息收集功能没有资源限制，但Oracle11g的统计信息收集功能在资源管理上面限制了对系统资源使用，其对应的RESOURCE_PLAN的名称为DEFAULT_MAINTENANCE_PLAN。用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。

DBA_TAB_MODIFICATIONS视图(基表为SYS.MON_MODS_ALL$)记录了从上次收集统计信息以来表中DML操作变化的数据量，包括执行INSERT、UPDATE和DELETE影响的行数，以及是否执行过TRUNCATE操作。该特性依赖于表的MONITORING属性是否打开，可以查询DBA_TABLES视图的MONITORING列获取该属性值。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以将内存中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中。[考点]统计信息

4.

怎样收集表的统计信息?怎样收集分区表的统计信息?正确答案：主要采用DBMS_STATS.GATHER_TABLE_STATS包进行统计信息的收集，如下：

DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',CASCADE=＞TRUE);--普通表

DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',PARTNAME=＞'PT_PART_NAME',GRANULARITY=＞'PARTITION',CASCADE=＞TRUE);--针对分区表的单个分区进行收集统计信息

除此之外，还有一些其他的用法，如下：

1)EXECDBMS_STATS.GATHER_DATABASE_STATS(USER)；——收集当前数据库下所有用户的统计信息。

2)EXECDBMS_STATS.GATHER_SCHEMA_STATS(USER)；——收集当前数据库用户下所有对象的统计信息。

当系统的分区表数据量很大时，如果每次都收集全部的分区必然会导致统计信息的收集非常慢，在Oracle11g之后可以通过设置INCREMENTAL来只针对数据有变动的分区做收集：

EXECDBMS_STATS.SET_TABLE_PREFS(USER,'TABLE_NAME','INCREMENTAL','TRUE');--只收集数据变动的分区

SELECTDBMS_STATS.GET_PREFS('INCREMENTAL',NULL,'TABLE_NAME')FROMDUAL;

--查看分区表INCREMENTAL的值[考点]统计信息

5.

什么是动态采样(DynamicSampling)?正确答案：对于没有收集统计信息的表，Oracle为了能够得到相对准确的执行计划，会在执行SQL之前对SQL语句涉及的表做动态采样(DynamicSampling，从Oracle11gR2开始称为DynamicStatistic)。

有两种方法可以开启动态采样：

1)将参数OPTIMIZER_DYNAMIC_SAMPLING的值设为大于或等于1。从Oracle10g开始，该值默认为2，若设置为0，则禁用动态采样。

2)使用动态采样的Hint：DYNAMIC_SAMPLING(TLEVEL)。该Hint表示对目标表T强制使用等级为参数level指定值的动态采样。

默认采样数据块数量受隐含参数“_OPTIMIZER_DYN_SMP_BLKS”的控制，其默认值是32，表示动态采样时默认采样数据块数量为32。[考点]统计信息

6.

动态采样的作用有哪几个方面?正确答案：引入动态采样有如下几方面的作用：

1)CBO依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle需要使用动态采样技术来帮助CBO获取尽可能多的信息。

2)全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。

3)为了相对准确地估算出当目标SQL语句WHERE条件中出现有关联关系的列时整个WHERE条件的组合可选择率，进而能相对准确地估算出返回结果集的Cardinality。动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还可以对不同列之间的相关性做统计。[考点]统计信息

7.

直方图(Histogram)的意义是什么?直方图的适用场合有哪些?正确答案：在Oracle数据库中，CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加WHERE查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，可以很容易地发现有一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的，所以此时应该收集列的直方图。

直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。例如，表中的某个列上，其中的某个值占据了数据行的80%(数据分布倾斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80%的记录。如果对目标列收集了直方图，那么意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。

通常情况下在以下场合中建议使用直方图：

1)当WHERE子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于WHERE子句中的值将会使优化器选择不同的执行计划，这时应该使用直方图来帮助优化器来修正执行路径。(注意：若查询不引用该列，则在该列上创建直方图没有意义。)

2)当列值导致不正确的判断时，这种情况通常会发生在多表连接时。例如，假设有一个五张表的连接操作，其目标SQL最终结果集只有10行。Oracle将会以一种使第一个连接的结果集(集合基数)尽可能小的方式将表连接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在SQL执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器做出正确的决策。如优化器对中间结果集的大小做出不正确的判断，则它可能会选择一种未达到最优化的表连接方法。因此向该列添加直方图经常会向优化器提供使用最佳连接方法所需的信息。[考点]统计信息

8.

直方图存储的数据字典表有哪些?正确答案：直方图实际上存储在数据字典基表SYS.HISTGRMS中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。[考点]统计信息

9.

直方图有哪些类型?正确答案：Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。Bucket(桶)是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列中的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT_NUMBER和ENDPOINT_VALUE，Oracle会将每个Bucket的这两个维度记录在数据字典基表SYS.HISTGRM$中。列的直方图的类型可以通过查询视图DBA_TAB_COL_STATISTICS的HISTOGRAM列来获取，一般情况下包含3类：None(没有直方图)、Frequency(频率直方图，也叫等频直方图)、HeightBalanced(高度平衡直方图，也叫等高直方图)。在Oracle12c中，又新增了两种类型的直方图，分别是顶级频率直方图(TopFrequencyHistogram)和混合直方图(HybridHistogram)。本书只讨论频率和高度平衡直方图。

(1)频率(Frequency，Freq)直方图

在Oracle12c之前，如果存储在数据字典里描述目标列直方图的Bucket的数量等于目标列的DISTINCT值的数量，那么这种类型的直方图就是频率(Frequency)直方图。频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形。需要注意的是，在Oracle12c中频率直方图所对应的Bucket的数量可以超过254。

对于频率直方图而言，目标列直方图的Bucket的数量等于目标列的DISTINCT值的数量，此时目标列有多少个DISTINCT值，Oracle在数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS(分别对应于表、分区和子分区的直方图统计信息)中就会存储多少条记录，每一条记录就代表了对其中的一个Bucket的描述，上述数据字典中的字段ENDPOINT_VALUE记录了这些DISTINCT值，而字段ENDPOINT_NUMBER则记录了到此DISTINCT值为止总共有多少条记录。需要注意的是，对频率直方图而言，ENDPOINT_NUMBER是一个累加值，可以用一条记录的ENDPOINT_NUMBER值减去它上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数，SQL如下：

SELECTSAL,COUNT(*)COUNTS，SUM(COUNT(*))OVER(ORDERBYSALRANGEUNBOUNDEDPRECEDING)CURR_ROWSFROMT_ST_20170604_LHRTGROUPBYT.SAL;--从表中获取累加值

SELECTTABLE_NAME，COLUMN_NAME，ENDPOINT_NUMBER,ENDPOINT_VALUE，NVL((ENDPOINT_NUMBER-(LAG(ENDPOINT_NUMBER)OVER(ORDERBYENDPOINT_VALUE)))，ENDPOINT_NUMBER)COUNTSFROMDBA_TAB_HISTOGRAMSWHERETABLE_NAME='T_ST_20170604_LHR';

--从直方图中获取分组记录数

(2)高度平衡(HeightBalanced，HtBal)直方图

如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的DISTINCT值的数量，那么这种类型的直方图就是高度平衡(HeightBalanced)直方图。在高度平衡直方图中，执行计划的列的选择性没有频率直方图精确，而在现实很多时候，列的唯一值是超过254的，那么只能使用高度平衡直方图。在高度平衡直方图中，DBA_TAB_HISTOGRAMS视图中的EDNPOINT_NUMBER代表桶号，且自动省去与EDNPOINT_VALUE值相同且与ENDPOINT_NUMBER相邻的桶的值。ENDPOINT_VALUE表示每一个桶中的最大值，而第一个桶记录的是最小值(Bucket为0的行，即EDNPOINT_NUMBER为0的行)。重复出现为ENDPOINT_VALUE的值称为PopularValue。若PopulValue所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该PopularValue在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。在高度平衡直方图中，除了最后1个桶可能包含的数据比其他的桶少以外，所有其他的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量。

(3)频率和高度平衡直方图的比对

频率直方图高度平衡直方图简介目标列直方图的Bucket的数量=目标列的DISTINCT值的数量目标列直方图的Bucket的数量＜目标列的DISTINCT值的数量使用场合频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形在高度平衡直方图中，除了最后1个桶可能包含的数据比其他的桶少以外，所有其他的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量ENDPOINT_VALUE的含义若为数值类型则代表不同的DISTINCT值；若为字符类型则记录的是DISTINCT值转换后的值若为数值类型则表示每一个桶中的最大值，而第一个桶记录的是最小值(Bucket为0的行，即EDNPOINT_NUMBER为0的行)；若为字符类型则记录的是转换后的值ENDPOINT_NUMBER的含义记录了到此DISTINCT值为止累加的行数代表桶号，且自动省去与EDNPOINT_VALUE值相同且与ENDPOINT_NUMBER相邻的桶的值(节省空间，合并存储)[考点]统计信息

10.

如何收集直方图?正确答案：默认情况下，数据库会为列收集基本统计信息，但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以接受如下的输入值：

FORALL[INDEXED|HIDDEN]COLUMNS[size_clause]

FORCOLUMNS[size_clause]column|attribute[size_clause][,column|attribute[size_clause]...]

其中的size_clause必须符合如下的格式：

SIZE{integer|REPEAT|AUTO|SKEWONLY}

含义如下：

1)SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。

2)REPEAT：只对已经有直方图统计信息的列收集直方图统计信息。

3)AUTO：让Oracle白行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。

4)integer：直方图的Bucket的数量，必须在1～254的范围内，1表示删除该目标列上的直方图统计信息。

METHOD_OPT参数的默认值为“FORALLCOLUMNSSIZEAUTO”，“FORALLCOLUMNSSIZE1”表示删除所有列直方图统计信息。下面是一些常用的收集方法：

1)对T表上所有有索引的列以自动收集的方式收集直方图：FORALLINDEXEDCOLUMNSSIZEAUTO。

2)对T表上的列A和列B以自动收集的方式收集直方图：FORCOLUMNSSIZEAUTOAB。

3)对T表上的列A和列B收集直方图统计信息，同时指定BUCKET数量均为10：FORCOLUMNSSIZE10AB。

4)对T表上的列A和列B收集直方图统计信息，同时指定列A的BUCKET数量为10，列B的BUCKET数量为5：FORCOLUMNSASIZE10BSIZE5。

5)只删除表T上列A的直方图统计信息：FORCOLUMNSASIZE1。

6)删除表T上所有列的直方图统计信息：FORALLCOLUMNSSIZE1。

如果需要删除某个列SAL的直方图信息，在Oracle10g中可以通过设置“METHOD_OPT=＞'FORCOLUMNSSALSIZE1'”，但这却得再次收集表的统计信息，十分不合理。所以，在Oracle11g中，有如下方法可以直接删除直方图信息：

EXECDBMS_STATS.DELETE_COLUMN_STATS(USER,'T_ST_20170604_LHR','SAL',COL_STAT_TYPE=＞'HISTOGRAM');

其中，COL_STAT_TYPE默认为ALL，表示删除列的基本统计信息和直方图信息。[考点]统计信息

11.

基表COL_USAGE$的作用是什么?正确答案：从Oracle9i开始引入了SYS.COLUSAGE$表用来跟踪列的使用情况，该功能通过隐含参数“_COLUMN_TRACKING_LEVEL”来控制。若隐含参数“_COLUMN_TRACKING_LEVEL”的值为0则取消该功能，若隐含参数“_COLUMN_TRACKING_LEVEL”的值为1则表示该功能生效。默认情况下，该功能是生效的，并且CBO负责将SQL语句中WHERE条件的查询谓词信息保存在该表中，数据库在执行SHUTDOWNNORMAL或者SHUTDOWNIMMEDIATE会自动将该表中的数据清空。[考点]统计信息

12.

新建索引后统计信息是否自动收集?正确答案：在Oracle10g后有个隐含参数“_OPTIMIZER_COMPUTE_INDEX_STATS”，意思是是否对新建索引收集统计信息，该参数默认是TRUE，表示默认收集新建索引的统计信息。[考点]统计信息

13.

什么是基数反馈(CardinalityFeedback)?正确答案：基数反馈(CardinalityFeedback，CFB)是Oracle11gR2出现的一个新特性，它的出现是为了帮助Oracle优化器依据更精准的基数生成更加优秀的执行计划。基数的评估准确与否，对于优化器异常重要，直接影响到后续的JOINCOST等重要的成本计算评估。若评估不当则会造成CBO选择不当的执行计划。此技术对于仅执行一次的SQL无效，在SQL第一次执行时，记录存储实际的基数和评估的基数之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的基数重新决策生成执行计划，但是需要注意的是，当使用更准确的基数重新生成执行计划时，生成的执行计划与第一次时使用的执行计划完全有可能是相同的。这个技术的出现是由于优化器在一些情况下不能很好地去计算基数的数值，比如统计信息缺失或陈旧、多谓词、直方图缺失等。

Oracle只针对下面情况开启CFB：

1)没有收集表的统计信息，并且动态采样(DynamicSampling)也没有开启。

2)查询条件复杂(比如条件有函数)或者涉及多列，但却没有收集扩展的统计信息(ExtendedStatistics)。

在这几种情况下，CBO是无法估算出准确的Cardinality的。针对上述情况，Oracle会监控操作的实际行数(A-Row)，然后对比CBO估算的行数(E-Row)。如果两个值相差很大，那么就记录实际行数(A-Row)，做上标记。下次执行时再次进行硬解析，根据实际行数来重新生成执行计划。如果两个值相差不大，那么CBO就不再监控这条SQL语句。

Oracle11gR2针对此特性，也专门在V$SQL_SHARED_CURSOR中增加了USE_FEEDBACK_STATS列来记录SQL是否使用了基数反馈。基数反馈的开启和关闭通过一个隐含参数“_OPTIMIZER_USE_FEEDBACK”来控制，该参数默认为TRUE，表示开启基数反馈特性。此参数除了可以在SESSION和SYSTEM级别进行设置之外，还可以在SQL语句级使用Hint进行开启和关闭，如下：

SELECT/*+OPT_PARAM('_OPTIMIZER_USE_FEEDBACK''FALSE')*/COUNT(*)FROMTEST;

SELECT/*+OPT_PARAM('_OPTIMIZER_USE_FEEDBACK''TRUE')

*/COUNT(*)FROMTEST;

需要注意的是，如果动态采样被启用，那么是不会使用基数反馈特性的。若使用了该特性则在执行计划的Note部分可以看到“cardinalityfeedbackusedforthisstatement”字样。基数反馈在Oracle12c上得到更进一步的扩展，称为统计反馈(StatisticsFeedback)，并且成为Oracle12c自动重新优化(AutomaticReoptimization)的一部分。但是由于CFB的评估结果数据只存在内存中(重启之后就会丢失)，在会话之间是不可共用的，并且由于在Oracle11g中存在过多的Bug，常见的问题就是在第二次执行SQL时候性能下降很多。因此在Oracle11g的数据库中往往会对11.2.0.4以下的数据库将该特性关闭。[考点]统计信息

14.

如何查询表和索引的历史统计信息?正确答案：从Oracle10g开始，当收集表的统计信息的时候，旧的统计数据被保留，如果因为新的统计信息而出现性能问题，旧的统计信息就可以被恢复。历史统计信息保存在以下几张表中：

1)WRI$_OPTSTAT_TAB_HISTORY表的统计信息。

2)WRI$_OPTSTAT_IND_HISTORY索引的统计信息。

3)WRI$_OPTSTAT_HISTHEAD_HISTORY列的统计信息。

4)WRI$_OPTSTAT_HISTGRM_HISTORY直方图的信息。

从视图DBA_TAB_STATS_HISTORY可以查询历史收集统计信息的时间，但是不能查询到行数，所以需要结合基表来查询，查询的SQL语句如下：

SELECTB.OWNER,B,OBJECT_NAMETABLE_NAME,TO_CHAR(D.ANALYZETIME,'YYYY-MM-DDHH24:MI:SS')LAST_ANALYZETIME,

TO_CHAR(D.SAVTIME,'YYYY-MM-DDHH24:MI:SS')CURR_ANALYZETIME,D.ROWCNT

FROMSYS.WRI$_OPTSTAT_TAB_HISTORYD,DBA_OBJECTSB

WHERE

D.OBJ#=B.OBJECT_IDANDB.OBJECTNAMEIN('TEST_STAT','TPCCBOKBAL_TMP','TPCCBOKBALJN','PK_TPCCBOKBAL')

ORDERBYD.OBJ#,D.SAVTIME;

查询索引的历史统计信息的SQL语句如下：

SELECTB.OWNER,B.OBJECT_NAMEINDEX_NAME,TO_CHAR(D.ANALYZETIME,'YYYY-MM-DDHH24:MI:SS')LAST_ANALYZEIIME,

TO_CHAR(D.SAVTIME,'YYYY-MM-DDHH24:MI:SS')CURR_ANALYZETIME,D.ROWCNT,D.BLEVEL,D.LEAFCNT,D.DISTKEY,D.CLUFAC

FROMSYS.WRIS_OPTSTAT_IND_HISTORYD,DBA_OBJECTSB

WHERED.OBJ#=B.OBJECT_IDANDB.OBJECT_NAMEIN('IND_TEST)

ORDERBYD.OBJ#,D.SAVTIME;

默认情况下统计信息将被保留31天，可以使用下面的命令修改：

EXECUTEDBMS_STATS.ALTER_STATSHISTORY_RETENTION(XX);

--xx是保留的天数

注意：这些统计信息在SYSAUX表空间中占有额外的存储开销，所以应该注意并防止统计信息将表空间填满。

SELECTDBMS_STATS.GET_STATS_HISTORY_RETENTIONFROMDUAL;--查询统计信息当前保留的天数。[考点]统计信息

15.

Oracle如何并发地收集统计信息?正确答案：对于大表的统计信息收集可以通过DEGREE参数使得扫描大表的时候进行并行扫描，从而加快扫描速度，缩短了收集统计信息的时间。但是，即使加了DEGREE参数，在收集统计信息的时候，还是进行一个表一个表地扫描，并没有并发地同时扫描多个表。在Oracle11.2.0.2之后，有了一个参数，可以并发扫描表，这就是CONCURRENT参数。可以通过以下SQL语句查询数据库是否启用了CONCURRENT收集统计信息，默认为FALSE，表示没有开启并发收集统计信息：

SELECTDBMS_STATS.GET_PREFS('CONCURRENT')FROMDUAL;

开启方式为

EXECDBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

开启CONCURRENT之后，收集统计信息就会以并发的形式进行，会并发出多个JOB进程。在并发收集统计信息时，数据库生成的JOB数会根据具体情况来分配。在大多数情况下，DBMS_STATS程序会给每个对象分配一个JOB；但如果对象(表或者分区)的大小太小，为了节省资源，Oracle会合并多个表和分区在一个JOB中执行。为了防止同时处理多个分区表的分区时发生死锁，对于分区表的处理机制是每次只能处理一个分区表，其他的分区表需要等待，待前一个分区表处理完后再处理下一个。在Oracle11.2.0.2到11.2.0.4的版本上，CONCURRENT可取的值为TRUE(开启并发)和FALSE(关闭并发)。在Oracle12c的版本上，可以设置以下的值：

MANUAL:只有当手动收集时,并发有效

AUTOMATIC:只有当自动收集时,并发有效

ALL:手动/自动收集,并发都有效

OFF:并发无效

可以通过以下的视图，对并发统计信息收集进行监视：

DBA_OPTSTAT_OPERATION_TASKS:当前和历史的统计信息收集的执行任务

DBA_OPTSTAT_OPERATIONS:当前和历史的统计信息收集的执行操作

DBA_SCHEDULER_JOBS:SCHEDULERJOBS信息

监控并发收集统计信息JOB的SQL代码如下：

SELECTJOB_NAME,STATE,COMMENTSFROMDBA_SCHEDULER_JOBSWHEREJOB_CLASSLIKE'CONC%';

SELECTSTATE,COUNT(*)FROMDBA_SCHEDULER_JOBSWHEREJOB_CLASSLIKE'CONC%'GROUPBYSTATE;

对于并发收集统计信息需要注意如下几点：

1)用CONCURRENT收集统计信息，需要收集统计信息的用户具有CREATEJOB、MANAGESCHEDULER和MANAGEANYQUEUE权限。即使是该用户具有了DBA角色，也还是需要显式授权上述权限；否则执行JOB的时候，可能会报错：“ORA-27486insufficientprivileges”“ORA-20000:Statisticscollectionfailedfor32235objectsinthedatabase”。

2)因为CONCURRENT不能控制并发度的大小，所以，如果数据库的初始化参数JOB_OUEUEPROCESSES设置得太高(在Oracle11.2.0.3之后，这个值的默认值是1000，所以就可能并发出1000个JOB)，那么对数据库的性能影响较大。所以开启CONCURRENT的另外一个建议就是使用ResourceManager来控制资源的使用。

3)下表列出了并发和并行在收集统计信息方面的一些区别。

[考点]统计信息

16.

当收集表的统计信息时应该注意哪些问题?正确答案：关于收集统计信息需要注意以下几点：

1)对于数据量不大的OLTP类型的系统，建议使用自动收集统计信息，并对一些特殊的大表写JOB定时收集统计信息。如果是数据量很大的OLAP或者DSS系统，那么建议DBA自己写JOB脚本来收集统计信息。

2)在导入大量数据后应及时收集统计信息，才能进行相关的后续业务处理(包括查询和修改)，否则可能会由于实际数据量和统计信息里记录的数据量存在巨大差异而导致CBO选择错误的执行计划。

3)全局临时表默认不能收集统计信息，在生成执行计划时采用动态采样比较好。

4)对于某些新上线或新迁移的系统，建议进行全库收集一次统计信息。

5)建议及时对包含日期型字段的表收集统计信息，避免出现谓词越界现象。

6)统计信息收集作业采样比例。对于Oracle11g及其以上的版本收集统计信息的采样比例建议采用DBMS_STATS.AUTO_SAMPLESIZE。如果是Oracle10g，那么建议将采样比例的初始值设为30%，然后根据目标SQL的实际执行情况再做调整。

7)系统统计信息。如果系统的硬件环境发生了变化，那么建议要额外收集一次系统统计信息。

8)内部对象统计信息。在明确诊断出系统已有的性能问题是因为X$表的内部对象统计信息不准确引起的，这个时候就应该收集X$表的内部对象统计信息，其他情形就不要收集了。

9)表的大小、是否并行。若表很大，而系统空闲，则可以使用并行来收集统计信息。

10)表是否分区。若是分区表则建议收集全局的统计信息并且收集数据量有变更的单个分区(加GRANULARITY和参数并设置属性INCREMENTAL)的统计信息。

11)是否收集索引的统计信息。一般情况下都应该收集索引的统计信息。

12)是否收集直方图。对直方图统计信息的收集策略是对已经存在直方图统计信息的列才收集直方图统计信息，而目标列的初次直方图统计信息则是由了解系统的DBA手工来收集直方图。设置METHOD_OPT的值为“FORALLCOLUMNSSIZEREPEAT”。

13)是否可以并发收集统计信息。若系统有很多小表，则可以考虑并发收集统计信息。

14)系统的负载情况。在手动收集统计信息的时候需要注意系统的负载情况。

15)预估多久可以收集完成。对OLAP系统的大表而言，根据平时收集统计信息的经验要预估出收集统计信息要花费多长的时间。

16)基于数据库、SCHEMA或者表级别：根据情况判断是否有必要在数据库或SCHEMA级别来收集统计信息。

17)是否需要收集扩展列的统计信息。如果表中的数据倾斜度较大，那么收集直方图能最大限度地帮助优化器计算出准确的Cardinality，从而避免产生差的执行计划；再进一步，如果存在倾斜的多个列共同构成了Predicate里的等值连接且这些列间存在较强的列相关性，那么生成带有直方图的多列统计信息是一个上佳的选择，能够最大限度地帮助优化器准确预测出Cardinality。

18)是否设置NO_INVALIDATE为FALSE。该选项有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE这3个值。如果取值为TRUE，那么表示收集统计信息后不进行游标失效动作，原有的SharedCursor保持原有状态：如果取值为FALSE，那么表示将统计信息对象相关的所有Cursor全部失效；如果设置为AUTO_INVALIDATE，那么Oracle自己决定SharledCursor失效动作，当SQL再次执行时间距离上次收集统计信息的时间超过5小时(隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定)则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时，没有使用NO_INVALIDATE=＞FALSE选项，所以，即使收集了统计信息，执行计划也不会立即改变。

19)对于OLTP类型的数据库，需要特别关注DML比较频繁的以及数据加载比较大的表及分区表。

20)检查是否有邻近统计信息收集窗口的数据加载工作，如果有，是否能在数据库统计信息的窗口时间完成，如果不能在窗口时间完成，那么应该针对这段时间加载的数据，特别是大量的数据，在相关加载脚本完成之后，加入统计信息的收集。

21)如果加载数据量比较大，并且是分区表，每个分区的业务数据呈现的是均匀的，在Oracle11g可以考虑采用DBMS_STATS.COPY_TABLE_STATS先把统计信息做个快速的设置，然后，再收集该分区的统计信息。

其实，上述几点是没有一个普适性的标准答案的，因为不同的系统其数据量、数据分布情况都不尽相同，甚至可能会有很大的区别，所以适合于某套系统的统计信息收集策略并不一定能适用于另外一套系统。收集统计信息总的原则就是量体裁衣，即要找到适合自己系统的统计信息收集策略，用尽量小的代价收集到能稳定生成正确执行计划的统计信息即可，也就是说，收集到的统计信息不一定要特别准，只要具备代表性，能稳定生成正确的执行计划就可以了。[考点]统计信息

17.

什么是基数(Cardinality)和可选择率(Selectivity)?正确答案：基数(Cardinality)是Oracle预估的返回行数，即对目标SQL的某个具体执行步骤的执行结果所包含记录数的估算值。如果是针对整个目标SQL，那么此时的Cardinality就表示该SQL最终执行结果所包含记录数的估算值。例如，一张表T有1000行数据，列COL1上没有直方图，没有空值，并且不重复的值(DistinctValue)有500个。那么，在使用条件“WHERECOL1=＜VALUE＞”去访问表的时候，优化器会假设数据均匀分布，它估计出会有1000/500=2行被选出来，2就是这步操作的Cardinality。通常情况下，Cardinality越准确，生成的执行计划就会越高效。

可选择率(Selectivity)是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的取值范围显然是0～1，它的值越小，就表明可选择性越好。当可选择率为1时的可选择性是最差的。CBO就是用可选择率来估算对应结果集的Cardinality的，可选择率和Cardinality之间的关系如下：

cardinality=NUM_ROWS*selectivity

其中，NUM_ROWS表示表的总行数。

在Oracle数据库中，Oracle会默认SQL语句的WHERE条件中出现的各列彼此之间是独立的，是没有关联关系的。所以，如果目标SQL语句各列之间是以AND来组合的，那么该sQL语句整个WHERE条件的组合可选择率就等于各个列各自施加查询条件后可选择率的乘积。在得到了SQL语句整个WHERE条件的组合可选择率后，Oracle会用它来估算整个SQL语句返回结果集的Cardinality，估算的方法就是用目标表的总记录数(NUM_ROWS)乘以组合可选择率。但Oracle默认的各列之间是独立的、没有关联关系的前提条件并不总是正确的，在实际的应用中各列之间有关联关系的情况并不罕见。在这种情况下如果还用上述计算方法来计算目标SQL语句整个WHERE条件的组合可选择率并用它来估算返回结果集的Cardinalty，那么估算结果可能会与实际结果有较大的偏差，进而可能导致CBO选错执行计划，所以Oracle又引入了动态采样和多列统计信息。[考点]统计信息

18.

ASM磁盘有几种冗余方式?正确答案：ASM使用独特的镜像算法，它不镜像磁盘而是镜像盘区。一个磁盘组可以由两个或多个故障组(Failgroup)组成，一个故障组由一个或多个ASM磁盘组成。故障组提供了共享相同资源的冗余，ASM磁盘组有三种不同的冗余方式。

外部冗余(ExternalRedundancy)默认冗余(NormalRedundancy)高度冗余(HighRedundancy)简介表示Oracle不提供镜像，镜像功能由外部存储系统实现，比如通过RAID技术。外部冗余的有效磁盘空间是所有磁盘设备空间之和。创建外部冗余的磁盘组最少需要1块ASM磁盘。故障组不能与外部冗余类型的磁盘组一起使用也叫标准冗余或正常冗余，表示Oracle提供两份镜像(提供双向镜像)来保护数据。默认冗余的有效磁盘空间是所有磁盘设备大小之和的1/2。创建默认冗余的磁盘组最少需要两块ASM磁盘，两个故障组。这也是使用最多的一种冗余模式表示Oracle提供3份镜像(提供三向镜像)来保护数据，以提高性能和数据的安全。高度冗余的有效磁盘空间是所有磁盘设备大小之和的1/3。创建高度冗余的磁盘组则最少需要3块ASM磁盘，3个故障组用于普通磁盘组时所需的ASM磁盘数量123用于OCR和VF时所需的ASM磁盘数量135

需要注意的是，一旦磁盘组被创建，就不可以改变它的冗余方式。若想改变磁盘组的冗余方式，则必须创建具有适当冗余的另一个磁盘组，然后必须使用RMAN还原的方式或使用DBMS_FILE_TRANSFER将数据文件移动到这个新创建的磁盘组。[考点]RAC

19.

与ASM相关的有哪些进程?正确答案：AsM实例除了传统的DBWn、LGWR、CKPT、SMON和PMON等进程，还包含如下几个新后台进程：进程表现形式简介RBAL(ASMRebalanceMasterProcess，ASM再平衡主进程)asm_rbal+ASMora_rbal_ora11g用于协调和管理磁盘组之间的动态平衡，RBAL用来进行全局调用，以打开某个磁盘组内的磁盘ASMB(ASMBackgroundProcess，ASM后台进程)ora_asmb_ora11goracle+ASM_asmb_ora11g(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))借助服务器进程建立普通RDBMS实例到ASM实例的会话，并传递磁盘文件的相关信息。每个数据库实例同时只能与一个ASM实例连接，因此数据库只会有一个ASMB舌台进程。如一个节点上有多个数据库实例，它们只能共享一个ASM实例。ASMB进程与该节点的CSS守护进程进行通信，并接收来自ASM实例的文件区间映射信息。ASMB还负责为ASM实例提供I/O统计数据GMON(ASMDiskGroupMonitorProcess，磁盘组监控进程)asm_gmon_+ASM这个进程负责维护磁盘组中的各个磁盘状态的一致性。当磁盘组中的磁盘成员发生改变时(例如，添加、删除磁盘，或者磁盘出现故障)，该进程负责离线(Offline)或者上线(Online)磁盘。可以说，这是ASM实例中最重要的后台进程之一ARBn(ASMRebalanoeProcess，ASM再平衡进程)asm_arba_+ASM在同一时刻可以存在许多此类进程，它们的名字分别为ARB0、ARB1、…、ARBA，以此类推，执行实际的重新平衡分配单元移动进程Onnn(ASMConnectionPoolProcess，ASM连接池进程)ora_o000_ora11gOracle+ASM_o000_Ora11g(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))这组进程建立了与ASM实例的连接，某些长时间操作比如创建数据文件操作，RDBMS会通过这些进程向ASM发送信息[考点]RAC

20.

什么是RAC?RAC有哪些优缺点?正确答案：RAC(RealApplicationClusters，实时应用集群)是Oracle数据库中采用的一项新技术，是高可用性的一种，也是Oracle数据库支持网格计算环境的核心技术。如何在一个7*24的系统上实现数据库的高可用性呢?RAC就是最好的解决方案，RAC也成为高级DBA的必备技能之一。

从Oracle9i版本开始就支持RAC了，可以支持7*24有效的数据库应用系统，在低成本服务器上构建高可用性数据库系统，并且自由部署应用，无须修改代码。在Oracle9i之前，RAC称为OPS(OracleParallelServer)。RAC与OPS之间的一个较大区别是，RAC采用了CacheFusion(缓存融合)技术，节点已经取出的数据块更新后没有写入磁盘前，可以被另外一个节点更新，然后以最后的版本写入磁盘；在OPS中，节点间的数据请求需要先将数据写入磁盘，然后发出请求的节点才可以读取该数据。使用CacheFusion时，RAC的各个节点间数据缓冲区通过高速、低延迟的内部网络进行数据块的传输。在OracleRAC环境下，Oracle提供了集群软件和存储管理软件，为用户降低了应用成本。当应用规模需要扩充时，用户可以按需扩展系统，以保证系统的性能。

(1)优点

1)RAC是一种双机并行模式，并非主备模式。也就是说，RAC集群的所有成员都可以同时接收客户端的请求。所以，RAC实现了容错、单点故障解决(如果有节点挂掉，那么其他节点可以继续提供服务)和多节点负载均衡(不同节点可以相互配合，分担负载)。

2)提供高可用性、故障容错和无缝切换功能，将硬件和软件错误造成的影响最小化，能够保证在集群中只要有一个节点存活，就能正常对外提供服务。

3)通过并行执行技术提高事务响应时间，通常被用于OLAP系统。

4)通过横向扩展提高每秒交易数和连接数，通常被用于OLTP系统。

5)扩展了机器的负载能力，节约了硬件成本，可以用多个廉价PC(PersonalComputer)服务器代替昂贵的小型机或大型机，同时节约相应维护成本。

6)易伸缩、可扩展性好，可以方便添加、删除节点，扩展硬件资源。

7)实现了业务分割处理。

8)低成本。能使用较低廉的服务器来实现高可用性、高吞吐量的集群环境，这要比通过对某台高端服务器增加硬件实现高可用性、高吞吐量花费的成本低很多。

9)高吞吐量。随着节点数的增加，整个RAC的吞吐量也在不断增长。

(2)缺点

1)相对单机，由于底层技术复杂，所以，管理更复杂，对DBA的技术要求更高。

2)可能会增加软件成本(如果使用高配置的PC服务器，那么Oracle一般按照CPU个数收费)。

3)在RAC系统规划设计较差时性能可能会不如单节点，存在资源争用(CacheFusion)。

(3)RAC的特点如下：

1)每一个节点的实例都有自己的SGA。

2)每一个节点的实例都有自己的后台进程。

3)每一个节点的实例都有自己的联机Redo日志文件。

4)每一个节点的实例都有自己的Undo表空间。

5)所有节点都共享一份DataFiles和ControlFiles。[考点]RAC

21.

用哪个参数可以判断一个数据库是否是RAC库?正确答案：在SQL中查询CLUSTER_DATABASE这个参数即可以看出当前库是否是RAC库，示例如下：

CLUSTER_DATABASE的值为TRUE，代表当前DB为RAC库，参数CLUSTER_DATABASE_INSTANCES的值为2，代表是2个节点的RAC。[考点]RAC

22.

启动和关闭集群的命令是什么?正确答案：关闭集群命令：crsctlstopcluster-all。

启动集群命令：crscflstartcluster-all。

高可用性进程查看命令：ps-ef|grepd.bin，该命令输出结果如下：

[ZHLHRDB01:root]/]＞ps-ef|grepd.bin

root12910838

13116:12:12

-0:00/u01/app/11.2.O/grid/bin/ologgerd-mzlhrcb2101-r-d/u01/app/11.2.0/grid/crf/db/zlhrcb1101

root6553890

1

616:11:35

-0:01/u01/app/11.2.0/grid/bin/ohasd.binreboot

grid6684956

1

516:12:03

-0:00/u01/app/11.2.0/grid/bin/gipcd.bin

root6750494

1

816:12:03

-0:00/u01/app/11.2.0/grid/bin/osysmond.bin

root6816030

1

016:12:05

-0:00/bin/sh/u01/app/11.2.0/grid/bin/ocssd

root3342930

1

016:12:05

-0:00/u01/app/11.2.0/grid/bin/cssdagent

root340844868160301116:12:05

-0:00/u01/app/11.2.0/grid/bin/ocssd.bin

root3670608

1

016:12:03

-0:00/u01/app/11.2.0/grid/bin/orarootagent.bin

grid3736148

1

016:11:59

-0:00/u01/app/11.2.0/grid/bin/mdnsd.bin

grid8061810

1

016:11:59

-0:00/u01/app/11.2.0/grid/bin/oraagent.bin

root8127338

1

016:12:04

-0:00/u01/app/11.2.0/grid/bin/cssdmonitor

rood8192874

1

616:12:01

-0:00/u01/app/11.2.0/grid/bin/gpnpd.bin[考点]RAC

23.