Oracle程序员面试分类模拟19

Oracle程序员面试分类模拟19简答题1.

什么是基数反馈(CardinalityFeedback)?正确答案：基数反馈(CardinalityFeedback，CFB)是Oracle11（江南博哥）gR2出现的一个新特性，它的出现是为了帮助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进行开启和关闭，如下：

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

2.

如何查询表的历史统计信息正确答案：从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语句如下：

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

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

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

下面的查询返回统计信息已经被删除到的日期(所以只有在这日期之后的统计信息才可能被恢复)。任何恢复到比这日期更早的统计信息的请求都会失败：“ORA-20006:Unabletorestorestatistics,statisticshistorynotavailable”：

查询到可以恢复统计信息到某一个比较好的时间之后，可以执行下面的命令进行恢复：

可以通过如下的命令返回2次统计信息的比较结果：

3.

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

开启方式为：

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

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

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

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

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

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

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

4.

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

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_NVALIDATE，那么Oracle自己决定SharedCursor失效动作，当SQL再次执行时间距离上次收集统计信息的时间超过5h(隐含参数“_OPTIMIZER_INVALlDATION_PERIOD”决定)则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时，没有使用NO_INVALIDATE=＞FALSE选项，所以，即使收集了统计信息，执行计划也不会立即改变。

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

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

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

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

5.

等待事件的分类?常见等待事件?正确答案：Oracle的等待事件主要可以分为两类：空闲(Idle)等待事件和非空闲(Non-Idle)等待事件。

1)空闲等待事件是指Oracle正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。

2)非空闲等待事件专门针对Oracle的活动，是指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。

通过如下的SQL语句可以查询等待事件的类型：

一些常见的、重要的等待事件如下：

(1)数据文件I/O相关的等待事件

dbfilesequentialread、dbfilescatteredread、dbfileparallelread、directpathread、directpathwrite。

(2)控制文件I/O相关的等待事件

controlfileparallelwrite、controlfileseauentialread、controlfilesinglewrite。

(3)Redo日志文件I/O相关的等待事件

logfileparallelwrite、logfilesync、logfilesequentialread、logfilesinglewrite、switchlogfilecommand、logfileswitchcompletion、logfileswitch(clearinglogfile)、logfileswitch(checkpointincomplete)、logswitch/archive、logfileswitch(archivingneeded)。

(4)高速缓存区I/O相关的等待事件

dbfileparallelwrite、dbfilesinglewrite、writecompletewaits、freebufferwaits。

下表列出一些常见等待事件用以抛砖引玉，实际的数据库管理中需要掌握和了解的等待事件非常多，也比较复杂，只需要记住一些常见的面试知识点，其他的等待事件需要在工作中慢慢积累。

除了上表中列举出来的等待事件还有很多其他常见的等待事件，这里就不再列举了，读者可以关注作者的微信公众号或博客，里面会有所有等待事件的详细介绍。

6.

ROWID和ROWNUM有什么区别?正确答案：Oracle有两个著名的伪列ROWID和ROWNUM，下面分别来介绍它们。

(1)ROWID

ROWID是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个ROWID的伪列，但是表中并不物理存储ROWID列的值。不过可以像使用其他列那样使用它，但是不能删除该列，也不能对该列的值进行修改、插入。

ROWID对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。当创建一个索引时，该索引不但存储索引列的值，而且也存储索引值所对应的行的ROWID，这样通过索引就可以快速找到相应行的ROWID，通过该ROWID，就可以迅速将数据查询出来。这也就是在使用索引查询时，速度比较快的原因。

一般来说，当表中的行确定后，ROWID就不会发生变化，一旦一行数据插入数据库，ROWID在该行的生命周期内是唯一的，即使该行产生行迁移，行的ROWID也不会改变，UPDATE不会改变ROWID，INSERT更不会。从ROWID定义可知，只有当数据行的物理位置改变时才会导致ROWID改变，所以，只需要关心那些会导致数据物理位置变化的操作即可。

ROWID可以分为以下几种类型：

1)物理ROWID：存储堆组织表、表簇、表分区、和索引分区中的行地址。

2)逻辑ROWID：存储索引组织表中的行地址。

3)外部ROWID：是外来表(如通过网关访问的DB2表)中的标识符。它们不是标准的Oracle数据库ROWID。

有一种数据类型称为通用ROWID或UROWID，支持各种ROWID。

当如下情况发生时，ROWID将发生改变，即当数据迁移到其他块的时候，ROWID就会改变：

1)对一个表做表空间的移动或重建后。

2)对一个表进行了exp/imp或expdp/impdp后。

3)MOVE、FLASHBACKTABLE、修改分区键值到另一个分区、分区表的分区数据转移到其他分区、SHRINKTABLE等。

通过DBMS_ROWID可以获取文件号、块号等信息，如下：

(2)ROWNUM

ROWNUM是一个伪列，不是真正的列，在表中并不真实存在，它是Oracle数据库从数据文件或缓冲区中读取数据的顺序。切勿理解成记录的行号(很多人一直这样认为的)，例如想查询第二行记录按下面的方法是查询不到的：

ROWNUM主要应用于Top-N查询中。

7.

Oracle健康检查有哪些方面?正确答案：要想对数据库进行全面检查，内容比较多，下面列举部分检查项目：

1)数据库的实例是否运行，最近是否有自动重启现象。

2)ASM实例是否正常运行，剩余ASM磁盘空间有多大。

3)数据库的参数是否正常，数据库的参数近期是否被修改过。

4)数据库的表空间大小，是否有表空间快满了，表空间增长是否过快(系统表空间是否增长过快)。

5)是否有业务表创建在了SYSTEM表空间上。审计表是否在SYSTEM表空间上。

6)RMAN备份是否过期，备份是否可用，是否有控制文件的备份。

7)数据库JOB是否有运行错误。

8)数据库的告警日志是否有异常告警，例如ORA-4030、ORA-4031、ORA-60、ORA-600、ORA-01555等。

9)数据库归档空间、闪回恢复区是否足够。

10)是否有非常耗费资源的SQL曾经运行过，系统是否有VERSIONCOUNT过高的SQL。

11)DG、OGG是否运行正常，归档日