Oracle程序员面试分类模拟39

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

TCP/IP模型是什么?正确答案：TCP/IP(TransmissionControlProtocol/InternetProtocol，传输控（江南博哥）制协议/因特网互联协议)是最基本的Internet协议，由网络层的IP与传输层的TCP构成。现在人们常提到的TCP/IP并不一定是指TCP和IP两个具体的协议，而是指的TCP/IP协议簇。

TCP/IP定义了电子设备如何连入Internet，以及数据如何在它们之间传输的标准。它基于四层参考模型，分别是网络接口层、网际层、传输层、应用层，每一层都呼叫它的下一层所提供的网络来完成自己的需求。

其中网络接口层负责底层的传输，常见的协议有Ethernet802.3、TokenRing802.5、X.25、HDLC、PPPATM等。网络层负责不同计算机之间的通信，一般包括IP、ICMP等内容。传输层提供应用程序间的通信，主要包括格式化信息流、提供可靠传输等。应用层用于向用户提供应用服务，如电子邮件、远程登录等。应用层协议一般有FTP、TELNET、SMTP等。属于TCP/IP协议簇的所有协议都位于该模型的上面三层。

TCP佃并不完全符合OSI七层模型，它的每一层都对应于OSI七层模型中的一层或多层，下图所示是TCP/IP四层模型和OSI七层模型对应图。

TCP/IP四层模型和OSI七层模型对应图

2.

什么是基数反馈(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进行开启和关闭，如下：

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

3.

AWR报告中主要关注哪些方面内容?正确答案：AWR报告中常常需要关注如下的内容：

(1)DBTime/Elapsed该部分位于AWR报告的头部，如下图所示，需要特别关注DBTime和Elapsed的比值。

DBTime/Elapsed位置

Elapsed：60.03(mins)表明采样时间大约是60min，任何数据都要通过这个时间来衡量，离开了这个采样时间，任何数据都毫无意义，Elapsed为该AWR性能报告的自然时间跨度，所谓自然时间的跨度，例如前一个快照是4点生成的，后一个快照是6点生成的，如果使用“@?/rdbms/admin/awrrpt”脚本中指定这2个快照的话，那么其Elapsed=(6-4)=2h。一个AWR报告至少需要2个AWR快照才能生成(注意在这2个快照之间实例不能重启过，否则指定这2个快照生成AWR报告会报错)。AWR性能报告中的指标往往是后一个快照和前一个快照的指标的delta值，这是因为累计值并不能反映某段时间内的系统负载情况。如果为了诊断特定时段性能问题，那么采用时间不宜过长。如果是看全天负载，那么可以长一些。最常见是60min或120min。

DBTime：427.44(mins)表明用户操作花费的时间，包括CPU时间和活动的非后台进程的等待时间，也许有人会觉得奇怪，为什么在采样的60min过程中，用户操作时间竟然有427min呢?远远超过了采样时间，原因是AWR报告是一个数据的集合，例如在一分钟之内，一个用户等待了30s，那么10个用户就等待了300s。对于CPU的话，一个CPU处理了30s，16个CPU就是480s。这些时间都是以累积的方式记录在AWR报告中的。DBTime不包括Oracle后台进程消耗的时间。一般来说，如果DBTime除以CPU个数大于Elapsed时间，那么说明数据库比较繁忙。

(2)LoadProfile该部分位于AWR报告的总览部分(ReportSummary)，AWR报告总览部分包括了5个部分：缓存尺寸(CacheSizes)、负载性能(LoadProfile)、数据库效率(InstanceEfficiencyPercentages)、共享池统计(SharedPoolStatistics)、TOP5事件(Top5TimedEvents)。这5个部分是整个AWR报告的核心部分，记录了数据库系统的关键性能参数和状况。其中，LoadProfile代表负载性能，即系统负载信息，从每秒钟和每个事务两个维度统计的，单纯的数字也无太大意义，需要与Baseline(基线)做比较才有意义。

下表是LoadProfile部分内容。

LoadProfile部分内容

对LoadProfile中的每个指标的解析如下：

1)Redosize：每秒/每事务产生的日志大小(单位是字节)，可标志数据变更频率，数据库任务的繁重与否。

2)Logicalreads：平均每秒/每事务产生的逻辑读的块数(单位是Block)。LogicalReads=ConsistentGets+DBBlockGetS。

3)Blockchanges：每秒/每事务修改的块数，即数据库事务改变数据块的数量。

4)Physicalreads：每秒/每事务物理读(磁盘读)的块数(单位是Block)。

5)Physicalwrites：每秒/每事务物理写的块数。

6)Usercalls：每秒/每事务用户调用次数。

7)Parses：SQL每秒/每事务解析的次数，包括FastParse、SoftParse和HardParse三种解析的综合。

8)Hardparses：每秒/每事务硬解析的次数，硬解析太多，说明SQL重用率不高。每秒产生的硬解析次数超过100次，就可能说明绑定变量使用地不好，也可能是共享池设置不合理。

9)Sorts：每秒/每事务的排序次数，对于Sorts大于每秒100次，表明排序过多，需要减少SQL代码中排序操作，或调整排序空间。

10)Logons：每秒/每事务登录的次数，大于每秒1～2个，表明可能有争用问题。

11)Executes：每秒/每事务SQL执行次数，反应负载大小。

12)Transactions：每秒事务数，反映数据库任务繁重与否。

13)BlockschangedperRead：表示逻辑读用于修改数据块的比例，在每一次逻辑读中更改的块的百分比。

14)RecursiveCall：递归调用占所有操作的比率。

15)Rollbackpertransaction：每个事务的回滚率。用来观察回滚率是不是很高，因为回滚很占用资源，如果回滚率过高，那么可能说明数据库有太多的无效操作，过多的回滚可能还会带来UndoBlock的竞争。

16)RowsperSort：每次排序的行数。

(3)InstanceEfficiencyPercentages(Target100%)该部分包含了Oracle关键指标的内存命中率及其他数据库实例操作的效率。其中，BufferHitRatio也称CacheHitRatio,LibraryHitRatio也称LibraryCacheHitRatio。同LoadProfile相同，这也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。在一个使用大型并行查询的DSS(DecisionSupportSystem，决策支持系统)环境中，20%的BufferHitRatio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。根据针对Oracle的经验，对于OLTP系统，BufferHitRatio理想应该在90%以上。

下表是该部分的一个示例表格。

示例

该部分的各个指标解析如下：

1)缓冲区未等待率(BufferNowait%)：表示在内存获得数据的未等待比率。BufferNowait的这个值一般需要大于99%，否则可能存在争用，可以在后面的等待事件中进一步确认。如果该值较低，那么可能要增大BUFFERCACHE，期望值是100%，不应该低于99%。

2)缓冲区命中率(BufferHit%)：表示进程从内存中找到数据块的比率，即数据块在数据缓冲区中的命中率，通常应在95%以上。监视这个值是否发生重大变化比仅仅观察这个值本身更重要。如果小于95%，那么就需要调整重要的参数，如果小于90%，那么就可能需要加DB_CACHE_SIZE。对于一般的OLTP系统，如果此值低于80%，那么应该给数据库分配更多的内存。命中率的突变，往往是一个不好的信息。如果命中率突然增大，那么可以检查topbuffergetSQL，查看导致大量逻辑读的语句和索引：如果命中率突然减小，那么可以检查topphysicalreadsSQL，检查产生大量物理读的语句，主要是那些没有使用索引或者索引被删除的SQL语句。

3)Redo缓冲区未等待率(RedoNowait%)：表示在LOG缓冲区(RedoLogBuffer)获得BUFFER的未等待比率，该指标的值应接近100%。如果该值较低，那么有两种可能的情况：①联机Redo日志没有足够的空间；②LOG切换速度较慢。如果太低(可参考90%阀值)，那么考虑增加LOGBUFFER。

4)库缓存命中率(LibraryHit%)：表示Oracle从LibraryCache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查LibraryCache确定是否存在解析过的版本，如果存在，那么Oracle立即执行语句；如果不存在，那么Oracle解析此语句，并在LibraryCache中为它分配共享SQL区。该值过低说明有过多的解析，CPU消耗增加，性能降低。如果该值低于90%，那么可能需要调大SharedPool区。

5)闩锁命中率(LatchHit%)：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。要确保LatchHit大于99%，否则意味着SharedPoollatch争用，可能由于未共享的SQL或LibraryCache太小，可使用绑定变更或调大SharedPool解决。当该值出现问题的时候，可以借助后面的等待事件和Latch来分析查找解决问题。

6)CPU时间占整个解析时间比率(ParseCPUtoParseElapsd%)：表示在解析SQL语句过程中，CPU占整个的解析时间比例，期望值是100%，说明解析没有产生等待，计算公式为：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，该值越大越好。如果该值为100%，那么意味着CPU等待时间为0，没有任何等待。

7)CPU非解析时间百分比(Non-ParseCPU%)：即SQL实际运行时间/(SQL实际运行时间+SQL解析时间)。该值太小表示解析消耗CPU时间过多，该值越大越好，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。

8)解析与执行的比率(ExecutetoParse%)：指的是SQL语句解析与执行的比例，如果SQL重用率高，那么这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。该值越大越好，说明一次解析，到处执行。计算公式为：ExecutetoParse=100*(1-PARSES/EXECUTIONS)。如果系统PARSES大于EXECUTIONS，那么就可能出现该比率小于0的情况。若该值小于0，则通常说明SharedPool设置或者语句效率存在问题，造成反复解析，REPARSE可能较严重。

9)内存排序率(In-memorySort%)：表示在内存中排序的比率，如果过低，那么说明有大量的排序在临时表空间中进行，此时可以考虑调大PGA。该指标的值应接近100%，如果低于95%，那么可以通过适当调大初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE来解决，注意，这两个参数设置作用的范围是不同的，SORT_AREA_SIZE是针对每个SESSION设置的，PGA_AGGREGATE_TARGET则是针对所有的SESSION的。

10)软解析的百分比(SoftParse%)：表示软解析的百分比，近似当作SQL在共享区的命中率。若该值小于95%，则需要考虑绑定变量，如果低于80%，那么就可以认为SQL基本没有被重用。该指标的值通常应在95%以上，期望值是100%，有一点要说明的是，不要单方面的追求软解析的高比例，而去绑定变量，要看性能的瓶颈在哪里。

(4)Top5TimedEvents该部分的一个示例见下表。

等待事件示例

该部分显示了系统中最严重的5个等待事件，按所占等待时间的比例倒序显示。当调优时，该部分是必须要分析的，应当从这里入手确定下一步做什么。例如，“bufferbusywaits”是较严重的等待事件，那么应当继续研究报告中BufferWait和File/TablespaceI/O区的内容，识别哪些文件导致了问题。如果最严重的等待事件是I/O事件，那么应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，那么就需要察看详细的LATCH统计识别哪些LATCH产生的问题。

一个性能良好的系统，CPUTIME应该在TOP5之内，否则说明系统大部分时间都用在等待上。

(5)SQLStatishcsSQLStatistics分别从执行时间、物理读、逻辑读、子游标个数、执行次数等方面罗列出TOP语句，从该部分可以迅速获取有性能问题的SQL语句，如下：

1)SQLorderedbyElapsedTime。

2)SQLorderedbyCPUTime。

3)SQLorderedbyGets，

4)SQLorderedbyReads。

5)SQLorderedbyExecutionS。

6)SQLorderedbyParseCalls。

7)SQLorderedbyVersionCount。

以“SQLorderedbyElapsedTime”为例，该部分记录了执行总时间的SQL语句，记录的是监控范围内该SQL的执行时间总和，需要综合分析CPU时间(CPUTime)和执行次数(Executions)才能得到单个SQL的代价。单次执行开销较大的SQL属于重点优化之列。

该部分的一个示例见下表。

“SQLorderedbyElapsedTime”示例

对于每个指标的解析如下：

1)ElapsedTime(s)：SQL语句执行总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL运行的时间，而是监控范围内SQL执行次数的总和时间。单位为秒。ElapsedTime=CPUTime+WaitTime。

2)CPUTime(s)：SQL语句执行时CPU占用总时长，此时间会小于等于ElapsedTime时间。单位为秒。

3)Executions：SQL语句在监控范围内的执行次数总和。若Executions为0，则说明语句没有正常执行完成，被中间停止，需要关注。

4)ElapsedTimeperExec(s)：执行一次SQL的平均时间。单位为秒。

5)%Total：SQL的ElapsedTime时间占数据库总时间(DBTime)的百分比。

6)SQLId：SQL语句的ID编号，单击之后就能导航到下面的SQL详细列表中，点击浏览器的返回按钮可以回到当前SQLId的地方。

7)SQLModule：显示该SQL是用什么方式连接到数据库的。

8)SQLText：简单的SQL文本。

(6)SegmentStatistics该部分从段(表段、索引段)的角度描述了数据库的繁忙程度，包含了逻辑读、物理读、ITL等方面。若等待事件“enq：TX-rowlockcontention”发生次数比较多，则可以查看“SegmentsbyRowLockWaits”部分内容，找到发生行锁的表。若等待事件“enq:TX-allocateITLentry”发生次数比较多，则可以查看“SegmentsbyITLWaits”部分内容，找到发生ITL等待的表。若等待事件“BufferBusyWaits”发生次数比较多，则可以查看“SegmentsbyBufferBusyWaits”部分内容，找到那些对象访问频繁，从而导致热块的产生。

还有其他的一些需要关注的内容，这里就不详细介绍了。

4.

什么是手动建库?手动建库有哪些步骤?正确答案：有时候因为环境的缘故不能使用图形界面或者不能使用DBCA的静默方式来创建一个新库，那么这个时候可以考虑使用“CREATEDATABASE”这种SQL命令行来创建数据库，该方式称为手动建库方式。使用手动建库的优点是：可以用脚本来创建数据库。另外，在OCM的考试中也要求DBA进行手动建库。

CREATEDATABASE命令详解如下：

CREATEDATABASE的其他子句：

手动建库的一般过程包括这几个步骤：①指定实例标示符(SID)；②确保环境变量设置正确；③创建密码文件；④创建初始化参数文件和相关路径；⑤连接实例；⑥创建服务器参数文件(SPFILE)；⑦启动实例；⑧执行CREATEDATABASE命令；⑨创建其他表空间；⑩运行脚本创建数据字典视图。

最后一步需要运行如下脚本创建数据字典，该过程比较慢：

下表是有关手动建库过程中常见脚本的用途。

手动建库过程中常见脚本的用途

安装完成后，可以根据需要安装一些组件。另外，在DBCA静默安装中有个参数是sampleSchema，若设置为true，则安装后数据库中有EXAMPLE表空间，有HR、OE、PM、SH、Ⅸ用户，大约占用350M的空间，若设置为FALSE，则后续可以参考官方文档来安装。

5.

RMAN中有哪几种保留策略?正确答案：保留策略说明了要保留的备份冗余数量及保留的时间长度。有两类保留策略：恢复窗口保留策略和冗余保留策略，这两类保留策略互相排斥。可以通过使用RMAN的CONFIGURE命令或OEM(OracleEnterpriseManager)来设置保留策略的值。

6.

哪些操作会导致索引失效?正确答案：当某些操作导致数据行的ROWID改变，索引就会完全失效。可以分普通表和分区表来讨论哪些操作将导致索引失效。

(1)普通表索引失效的情形

1)手动置索引无效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

2)如果对表进行MOVE操作(包含移动表空间和压缩操作)或在线重定义表后，那么该表上所有的索引状态会变为UNUSABLE。MOVE操作的SQL语句为：ALTERTABLETTMOVE;。

3)若将表中LONG数据类型修改为CLOB类型，则会导致表上索引失效。

4)SQL*Loader加载数据。

在SQL*Loader加载过程中会维护索引，由于数据量比较大，在SQL*Loader加载过程中出现异常情况，也会导致Oracle来不及维护索引，导致索引处于失效状态，影响查询和加载。异常情况主要有：在加载过程中杀掉SQL*Loader进程、重启或表空间不足等。

(2)分区表索引失效的情形

1)对分区表的某个含有数据的分区执行了TRUNCATE、DROP操作可以导致该分区表的全局索引失效，而分区索引依然有效，如果操作的分区没有数据，那么不会影响索引的状态。需要注意的是，对分区表的ADD操作对分区索引和全局索引没有影响。

2)执行EXCHANGE操作后，全局索引和分区索引都无条件地会被置为UNUSABLE(无论分区是否含有数据)。但是，若包含INCLUDINGINDEXES子句(缺省情况下为EXCLUDINGINDEXES)，则全局索引会失效，而分区索引依然有效。

3)如果执行SPLIT的目标分区含有数据，那么在执行SPLIT操作后，全局索引和分区索引都会被置为UNUSABLE。如果执行SPLIT的目标分区没有数据，那么不会影响索引的状态。

4)对分区表执行MOVE操作后，全局索引和分区索引都会被置于无效状态。

5)手动置其无效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

对于分区表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均会导致全局索引失效，但是可以加上UPDATEGLOBALINDEXES子句让全局索引不失效。重建分区索引的命令为：ALTERINDEXIDX_RANG_LHRREBUILDPARTITIONP1;。

分区表的索引是否失效的情形见表。

7.

SQL优化在写法上有哪些常用的方法?正确答案：一般在书写SQL时需要注意哪些问题，如何书写可以提高查询的效率呢?可以从以下几个方面去考虑：

1)减少对数据库的访问次数。当执行每条SQL语句时，Oracle在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等。由此可见，减少访问数据库的次数，就能实际上减少Oracle的工作量。充分利用表索引，避免进行全表扫描；充分利用共享缓存机制，提高SQL工作效率；充分利用结构化编程方式，提高查询的复用能力。常用的方法为把对数据库的操作写成存储过程，然后应用程序通过调用存储过程，而不是直接使用SQL。

2)减少对大表的扫描次数。可以利用WITH对SQL中多次扫描的表来进行修改。采用各种手段来避免全表扫描。

3)SELECT子句中避免使用“*’，应该写出需要查询的字段。当想在SELECT子句中列出所有的列时，可以使用“*”来返回所有的列，但这是一个非常低效的方法。实际上，Oracle在解析的过程中，会将“*”依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。不需要的字段尽量少查，多查的字段可能有行迁移或行链接(timesten还有行外存储问题)。少查LOB类型的字段可以减少I/O。

4)尽量使用表的别名(ALIAS)。当在SQL语句中连接多个表时，请使用表的别名，并把别名前缀于每个列上。此时就可以减少解析的时间并减少那些由列歧义引起的语法错误。

5)对于数据量较少又有主键索引的情况，可以考虑将关联子查询或外连接的SQL修改为标量子查询。

6)避免隐式类型转换(ImplicitTypeConversion)。如果进行比较的两个值的数据类型不同，那么Oracle必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle在运行时会在索引字符列使用TO_NUMBER函数强制转化字符类型为数值类型。由于添加函数到索引列所以导致索引不被使用。实际上，Oracle也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进行的，这会导致性能问题。一般情况下，当比较不同数据类型的数据时，Oracle自动地从复杂向简单的数据类型转换。所以，字符类型的字段值应该加上引号。例如，假设USER_NO是一个字符类型的索引列，则：

这个语句在执行的时候被Oracle在内部自动的转换为：

因为内部发生的类型转换，这个索引将不会被使用，所以正确的写法应该是：

7)避免使用耗费资源的操作，包括DISTINCT、UNION、MINUS、INTERSECT、ORDERBY、GROUPBY等。能用DISTINCT的就不用GROUPBY。能用UNIONALL就不要用UNION。

8)用TRUNCATE替代DELETE。若要删除表中所有的数据，则可以用TRUNCATE替代DELETE。

9)根据查询条件建立合适的索引，利用索引可以避免大表全表扫描(FULLTABLESCAN)。

10)合理使用临时表。

11)避免写过于复杂的SQL，不一定非要一个SQL解决问题。将一个大的SQL改写为多个小的SQL来实现功能。条件允许的情况下可以使用批处理来完成。

12)在不影响业务的前提下尽量减小事务的粒度。

13)当使用基于规则的优化器(RBO)时，在多表连接查询的时候，记录数少的表应该放在右边。

14)避免使用复杂的集合函数，像NOTIN等。通常，要避免在索引列上使用NOT，NOT会产生和在索引列上使用函数相同的影响。当Oracle遇到NOT操作符时，它就会停止使用索引转而执行全表扫描。很多时候用EXISTS和NOTEXISTS代替IN和NOTIN语句是一个好的选择。需要注意的是，在Oracle11g之前，若NOTIN的列没有指定非空的话(注意：是主表和子表的列未同时有NOTNULL约束，或都未加ISNOTNULL限制)，则NOTIN选择的是filter操作(如果指定了非空，那么会选择ANTI的反连接)，但是从Oracle11g开始有新的ANTINA(NULLAWARE)优化，可以对子查询进行UNNEST，NOTIN和NOTEXISTS都选择的是ANTI的反连接，所以效率是一样的。在一般情况下，ANTI的反连接算法比filter更高效。对于未UNNEST的子查询，若选择了filter操作，则至少有两个子节点，执行计划还有个特点就是Predicate谓词部分有“:B1”这种类似绑定变量的内容，内部操作走类似NestedLoops操作。如果在Oracle11g之前，遇到NOTIN无法UNNEST，那么可以将NOTIN部分的匹配条件均设为NOTNULL约束。若不添加NOTNULL约束，则需要两个条件均增加ISNOTNULL条件。当然也可以将NOTIN修改为NOTEXISTS。

15)尽量避免使用UNION关键词，可以根据情况修改为UNIONALL。

16)在Oracle数据库里，IN和OR是等价的，优化器在处理带IN的目标SQL时会将其转换为带OR的等价SQL。例如，“DEPTNOIN(10,20)”和“DEPTNO=10ORDEPTNO=20”是等价的。

17)选择合适的谓词进行过滤。

18)避免使用前置通配符(%)。在WHERE子句中，如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始，索引将不被采用。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。若前置通配符实在无法取消，则可以从几个方面去考虑。①去重和去空。应该把表中的重复记录或者为空的记录全部去掉，这样可以大大减少结果集，因而提升性能，这里也体现了大表变小表的思想。②考虑建立文本索引。③做相关的转换。

19)应尽量避免在WHERE子句中对字段进行函数、算术运算或其他表达式等操作，因为这样可能会使索引失效，查询时要尽可能将操作移至等号右边。见如下例子：

在以上SQL中，即使NAME字段建有唯一索引，该SQL语句也无法利用索引进行检索数据，而是走全表扫描的方式。一些常见的改写见表。

需要注意的是，如果SELECT需要检索的字段只包含索引列且WHERE查询中的索引列含有非空约束的时候，以上规则并不适用。例如，SQL语句“SELECTCREATEDFROMT1WHERETRUNC(CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空约束或在WHERE子句中加上“CREATEDISNOTNULL”，则该SQL语句仍然会进行索引，如下：

20)合理使用分析函数。

21)应尽量避免在WHERE子句中使用不等操作符(!=或＜＞)，否则引擎将放弃使用索引而进行全表扫描。

22)避免不必要和无意义的排序。

23)尽可能减少关联表的数量，关联表尽量不要超过3张。

24)在建立复合索引时，尽量把最常用、重复率低的字段放在最前面。在查询的时候，WHERE条件尽量要包含索引的第一列即前导列。

25)应尽量避免在WHERE子句中对字段进行ISNULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。可以通过加伪列创建伪联合索引来使得ISNULL使用索引。例如语句：“SELECTIDFROMTWHERENUMISNULL;”可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：“SELECTIDFROMTWHERENUM=0;”。

26)IN要慎用，因为IN会使系统无法使用索引，而只能直接搜索表中的数据。如：

对于连续的数值，能用BETWEEN就不要用IN了：

27)必要时使用Hint强制查询优化器使用某个索引，如在WHERE子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

28)在条件允许的情况下，只访问索引，从而可以避免索引回表读(TABLEACCESSBYINDEXROWID，通过索引再去读表中的内容)。当索引中包括处理查询所需要的所有数据时，可以执行只扫描索引操作，而不用做索引回表读操作。因为索引回表读开销很大，能避免则避免。避免的方法就是：①根据业务需求只留下索引字段；②建立联合索引。这里的第二点需要注意平衡，如果联合索引的联合列太多，必然导致索引过大，虽然消减了回表动作，但是索引块变多，在索引中的查询可能就要遍历更多的BLOCK了，所以需要全面考虑，联合索引列不宜过多，一般来说超过3个字段组成的联合索引都是不合适的，需要权衡利弊。

29)选择合适的索引。Oracle在进行一次查询时，一般对一个表只会使用一个索引。例如，某表有索引1(POLICYNO)和索引2(CLASSCODE)，如果查询条件为POLICYNO='XX'ANDCLASSCODE='XX'，则系统有可能会使用索引2，相较于使用索引1，查询效率明显降低。

30)优先且尽可能使用分区索引。

31)在删除(DELETE)、插入(INSERT)、更新(UPDATE)频繁的表中，建议不要使用位图索引。

32)对于分区表，应该减少需要扫描的分区，避免全分区扫描。对于单分区扫描，在分区表后加上PARTITION(分区名)；对于多分区扫描，使用分区关键字来限制需要扫描的范围，从而可以避免全分区扫描。

33)使用分批处理、DBMS_PARALLEL_EXECUTE进行处理。

34)删除重复记录尽量采用ROWID的方法，如下：

35)SQL中慎用自定义函数。如果自定义函数的内容，只是针对函数输入参数的运算，而没有访问表这样的代码，那么这样的自定义函数在SQL中直接使用是高效的；否则，如果函数中含有对表的访问的语句，那么在SQL中调用该函数很可能会造成很大的性能影响，需要谨慎!在这种情况下，往往会将函数中访问表的代码取出和调用它的SQL整合成新的SQL。

36)使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，这对于大表非常有效，如下：

若使用DECODE函数则对SCOTT.EMP表只访问一次，如下：

类似的，DECODE函数也可以运用于GROUPBY和ORDERBY子句中。

37)在计算表的行数时，若表上有主键，则尽量使用COUNT(*)或COUNT(1)。

38)用WHERE子句替换HAVING子句。避免使用HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那么就能提高SQL的性能。如下：

39)减少对表的查询，尤其是要避免在同一个SQL中多次访问同一张大表。可以考虑如下的改写方法：

①先根据条件提取数据到临时表中，然后再做连接，即利用WITH进行改写。

②有的相似的语句可以用MAX+DECODE函数来处理。

③在含有子查询的SQL语句中，要特别注意减少对表的查询，例如形如“UPDATEAAATSETTA=(...)T.B=(....)

WHERE....;”该更新的SQL语句中小括号中的大表都是一样的，且查询非常相似，这个时候可以修改为：“UPDATEAAATSET(T.A,T.B)=(....)

WHERE....;”。

40)SQL语句统一使用大写。因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

41)对于一些固定性的查询结果可以使用结果集缓存(ResultCache)，对于一些常用的小表可以使用保留池(KeepPool)。

42)如果在一条SQL语句中同时取最大值和最小值，那么需要注意写法上的差异：

43)在PL/SQL中，在定义变量类型时尽量使用%TYPE和%ROWTYPE，这样可以减少代码的修改，增加程序的可维护性。

以上讲解的每点优化内容希望读者可以通过实验来加深理解。

8.

如何跟踪expdp和SQL*Plus命令?正确答案：可以使用trace选项来跟踪expdp命令，如下：

若想跟踪exp命令的话，可以使用操作系统命令strace，如下：

操作系统命令strace也可以跟踪SQL*Plus命令，跟踪的命令很简单：

Unix系统下跟踪SQL*Plus命令使用truss命令，如下：

9.

如何有效地删除一个大表(即表的EXTENT数很多)?正确答案：一个有很多EXTENT(100k+)的表，如果只是简单地用DROPTABLE的话，那么会大量消耗CPU(在DMT管理下，Oracle要对FET$、UET$数据字典进行操作)，可能会用上几天的时间，较好的方法是分多次删除EXTENT，以减轻这种消耗：

10.

如何把相邻记录合并到一条记录?正确答案：可以利用分析函数LAG与LEAD，它们可以提取后一条或前一天记录到本记录，如下：

11.

简述Oracle数据库的参数正确答案：Oracle数据库根据SPEILE或PFILE中设置的参数来启动数据库。Oracle中的参数(下图)根据系统使用情况可以简单分为两大类：

Oracle参数分类

1)