Oracle程序员面试分类模拟37

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

什么是索引分裂?索引分裂有哪几种类型?正确答案：索引分裂(IndexBlockSplit)就是索引块的分裂。当一次DML操作修改了索引块上的数据，（江南博哥）但是旧有的索引块没有足够的空间去容纳新修改的数据时，将分裂出一个新的索引块，旧有块的部分数据放到新开辟的索引块上，这个过程就称为索引块的分裂，简称索引分裂。

在分裂的过程中前台进程需要等待分裂完成之后才能继续操作。如果此时其他会话也要修改这个索引块的数据，那么将会出现索引块的竞争，等待以“enq:TX-indexcontention”的形式体现，该事件是一个与索引分裂直接相关的等待事件。一般索引块的分裂持有资源和释放非常迅速，并不会对数据库造成严重的性能影响，但是对表操作并发量很大的情况下可能导致严重的竞争。当索引分裂发生时，负责实施分裂的进程会持有相关的队列锁，直到该进程完成分裂操作才会释放该队列锁。在这个过程中负责分裂的进程需要找到合适的新块并将对应的数据移动到该新块中。若在此分裂的过程中，有其他进程INSERT数据到该索引块中，则将进入enq:TX-indexcontention等待，直到分裂结束锁被释放。

索引分裂有如下几种情况：

(1)按照分裂对象分

1)叶子节点分裂：叶子节点上没有足够的空间容纳新插入的数据。叶子节点分裂的情况最频繁发生，对性能影响最直接。

2)枝节点分裂：其下层的节点分裂，会导致在该节点上增加一条记录指向新加的节点，当该节点空间不足时，会发生分裂。

3)根节点分裂：特殊的枝节点分裂，分裂需要两个新的数据块，将原有数据转移至两个新节点，原有节点上生成两条记录分别指向新增的数据块。

(2)按照分裂数据块比例分

1)9-1分裂：当事务向索引的最右侧的叶节点上插入一条大于或等于现有索引块上最大值的数据，且该索引块上不存在其他未提交的事务，如果没有足够的空间，那么就会发生9-1分裂。绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。

2)5-5分裂：当发生5-5分裂时，有一半索引记录仍存在当前块，而另一半数据移动到新的节点中，旧节点和新节点上的数据比例几乎是持平的。5-5分裂发生的条件：

①当左侧节点发生新值插入时(插入到叶子节点中的索引键值小于该块中的最大值)。

②当发生DML操作时，索引块上没有足够空间分配新的ITL槽。

③当新插入数据大于或等于索引中最大值时，但是数据块上还存在其他未提交的事务。

对性能来说，无论是9-1分裂，还是5-5分裂，都会影响系统的性能。通过10224事件可以生成索引块分裂及删除的trace：

索引分裂常常发生在主键列上，在这种情况下，可以考虑将该主键修改为反转(REVERSE)类型的主键。若主键是通过序列、时间戳或按某种规则单调生成的主键，则可以使用反转索引来有效地降低索引“单向右增长”(Right-GrowingIndex)的可能性。语句如下：

在发生索引分裂等待的时候，也可以根据需要将索引改造为分区索引。通过HASH将索引分成一个一个小块，这样竞争就不会聚集在最右边的节点上。通过HASH分区索引在一定程度上就可以缓解插入数据的竞争问题。

2.

有如下两张表：

1)用户资料表：SERV(SERV_IDNUMBER(10),PROD_IDNUMBER(L0),USER_TYPEVARCttAR2(30),TERMINAL_NAMEVARCHAR2(30))，其中：SERV_ID为用户标识，是SERV表的主键；PROD_ID为产品标识；USER_TYPE为用户类型；TERMINAL_NAME为终端类型。

2)终端类型临时表：TERMINAL(SERV_IDNUMBER(10),TERMINAL_NAMEVARCHAR2(30))。

在初始化情况下，SERV表的SERV_ID、PROD_ID、USER_TYPE字段值是已知的，TERMINAL_NAME字段是空的，现在要根据PROD_ID，USER_TYPE字段的值来更新NAME字段。

更新条件为：

1)当条件满足“PROD_ID=1ANDUSER_TYPE='A'”时，TERMINAL_NAME更新为“固话”。

2)当条件满足“PROD_ID=1ANDUSER_TYPE='B'”时，TERMINAL_NAME更新为“小灵通”。

3)当条件满足“PROD_ID=2”时，TERMINAL_NAME更新为“宽带”。

4)当条件满足“USER_TYPE='C'”时，TERMINAL_NAME更新为“CDMA”。

5)以上条件均不满足时，TERMINAL_NAME更新为-1。

根据以上条件，完成存储过程，存储过程的名称为：SPTERMINAL。正确答案：存储过程如下：

3.

通过DBMS_SCHEDULER如何调用SHELL脚本?正确答案：可以直接创建JOB来调用SHELL脚本，如下：

若是以普通用户执行上述代码的话，在执行过程中会遇到ORA-27486：insufficientprivileges和ORA-27399：jobtypeEXECUTABLErequirestheCREATEEXTERNALJOBprivilege的错误，这是由于权限不足的缘故导致的。

解决方法：以SYSDBA连接，然后执行上述代码，或者用SYS用户进行赋权操作，这里假设普通用户的用户名为LHR：

赋权之后接着执行就好了：

4.

分区表有什么优点?分区表有哪几类?如何选择用哪种类型的分区表?正确答案：当表中的数据量不断增大时，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。当对表进行分区后，在逻辑上，表仍然是一张完整的表，只是将表中的数据在物理上可能存放到多个表空间或物理文件上。当查询数据时，不至于每次都扫描整张表。Oracle可以将大表或索引分成若干个更小、更方便管理的部分，每一部分称为一个分区，这样的表称为分区表。SQL语句使用分区表比全表能提供更好的数据处理与访问的性能。即使某些分区不可用，其他分区仍然可用，这称为分区独立性。

分区表的一些限制条件：①簇表不能进行分区。②不能分割含有LONG或LONGRAW列的表。③索引组织表不能进行范围分区。

(1)何时考虑分区?对大表进行分区，将有益于大表操作的性能和大表的数据的维护。官方文档说通常当表的大小超过2GB，或对于OLTP系统，当表的记录超过1000万条时，都应考虑对表进行分区。

(2)分区表有什么优点?分区表有如下的优点：

1)增强可用性：如果表的一个分区由于系统故障而不能使用，那么表的其余好的分区仍可以使用。

2)减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，可能比整个大表修复花的时间更少。

3)维护轻松：单独管理每个分区比管理单个大表要轻松得多。

4)均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O，改善性能。

5)改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快。

6)分区对用户透明，最终用户感觉不到分区的存在。

(3)有哪些类型的分区?如何选择用哪种类型的分区表?Oracle的分区可以分为：

1)范围分区(RANGEPARTITION)。

2)哈希分区(HASHPARTITION)。

3)列表分区(LISTPARTITION)。

4)复合分区(组合分区)。

5)INTERVAL分区(间隔分区)。

6)系统分区。

INTERVAL分区和系统分区是Oracle11g的新特性，由于篇幅原因，本书中不讲解这2个分区，读者可自行查阅官方文档进行学习。尤其对于INTERVAL分区在生产环境中还是比较实用的。下面作者将对范围、哈希、列表和复合分区分别进行讲解。

(1)RAN(讵(范围)分区在如下几种情况下会使用到范围分区：

1)频繁地被一个时间范围谓词扫描。

2)维护一个时间滚动的数据窗口(RollingWindowOfData)。

3)保存历史数据的表。

(2)HASH(哈希)分区HASH分区有如下的优点：

1)提高了大表的高可用性和可管理性。

2)可以避免数据倾斜，将数据均匀地分布在多个物理设备上，最大化I/O吞吐量。

3)分区修剪和分区智能连接。

4)要求分区键是高基数列。

5)分区修剪不支持基于分区字段的范围查询，只支持等值查询或IN-LIST查询。

HASH分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这几种情况下，使用HASH分区比RANGE分区更好：

1)事先不知道需要将多少数据映射到给定范围的时候。

2)分区的范围大小很难确定，或者很难平衡的时候。

3)RANGE分区使数据得到不期望的聚集时。

4)性能特性，如并行DML、分区修剪和分区连接很重要的时候。

(3)LIST(列表)分区列表分区(LISTPARTITION)提供了一种按照字段的值来进行分区的方法，这种方法非常适合于有高重复率字段值的表。通过这种方法，可以非常方便地控制将某些特定的数值存放到一个分区。

列表分区有如下特点：

1)列表值是离散的。

2)列表值是无序的，例如：PARTITIONPART_01VALUES('A'，'E'，'F')等。

3)分区键仅能包含一个列。

4)列表值是低基数的。

(4)复合分区如果某表按照某列分区之后，仍然较大，或者是有一些其他的需求，那么还可以通过在分区内再建子分区的方式将分区冉分区，即复合分区的方式。

复合分区在Oracle11g之前有两种：RANGE-HASH与RANGE-LIST。需要注意的是其顺序，根分区只能是RANGE分区，子分区可以是HASH分区或LIST分区，而Oracle11g在复合分区功能这块有所增强，又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH，这就相当于除HASH外3种分区方式的笛卡尔形式都有了，即目前一共有6种分区，但根分区只能是RANGE分区或LIST分区。

在某些时候按照业务要求，上面的几种分区也可以按照一定的目的创建复合分区，或者称为子分区。

对于海量数据的数据库设计，分区的设计非常重要。例如，对于一个大表，应该采用哪种类型的分区，对于以后数据库的性能和管理至关重要。

其实，范围分区、HASH分区和列表分区这3种分区的特点都非常明显，如下：

1)如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。

2)如果需要数据的均匀分布，那么可以考虑使用HASH分区。

3)如果数据的值可以很好地对应于某个分区，那么就可以考虑使用列表分区。

在上面的原则基础上，再结合性能的影响因素，来最终确定使用哪种类型的分区。

如果选择的分区不能确保各分区内数据量的基本平均，那么这种分区方式有可能是不恰当的。比如对于RANGE分区，假设分了10个分区，而其中一个分区中的记录数占总记录数的90%，其他9个分区只占总记录数的10%，则这个分区方式就起不到数据平衡的作用。

5.

SQL如何优化?SQL优化关注点有哪些?正确答案：随着数据库中数据量的增长，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上千倍。对于一个系统不是简单地能实现其功能就可以了，而是要写出高质量的SQL语句，提高系统的可用性。

在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL语句的WHERE子句中写的SQL条件不合理，那么就会造成优化器舍去索引而使用全表扫描，一般这种SQL语句的性能都是非常差的。在编写SQL语句时，应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。

SQL的优化主要涉及如下几个方面的内容：

1)索引问题。是否可以使用组合索引；限制条件、连接条件的列是否有索引；能否使用到索引避免全表扫描。一般情况下，尽量使用索引，因为索引在很多情况下可以提高查询效率。排序字段有正确的索引，驱动表的限制条件有索引，被驱动表的连接条件有索引。

2)相关的统计信息缺失或者不准确。在确保统计信息正确后，再结合统计信息查看执行计划是否正确以及是否最优化。

3)直方图使用错误。

4)SQL本身的效率问题，例如使用绑定变量，批量DML采用BULK等，这个就考验写SQL的基本功了。

5)数据量大小。如果就是几百条数据，那么就没有所谓效率之分，一般情况下怎么写效率都不低。如果数据量很大，那么就得考虑是否要分页或排序。

6)绑定变量：大多数情况绑定变量能提高查询效率，但也有降低效率的情况。

7)批量和并行的考虑。

8)业务需求需要正确理解，实现业务的逻辑需要正确，减少一些重复计算。有可能是设计的不合理、业务需求的不合理，而问题SQL并非根本原因。

9)查询特别频繁的结果是否可以缓存，比如Oracle的/*+result_cache*/。

10)分析表的连接方式。若是NL连接，则根据业务或表的数据质量情况，分析能否减少驱动表的结果集。

11)是否可以固定执行计划。

12)大表是否存在高水位。

13)在创建表的时候，应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数。

6.

如果$GRID_HOME下的权限被人为修改过，那么如何来修复该权限问题?正确答案：使用chown-R误操作将整个$GRID_BASE的权限修改了，或者删除了$GRID_HOME/log文件夹下的所有内容，导致集群不能启动。在这种情况下可以根据MOS文档：TipsforcheckingfilepermissionsonGRIDenvironment(ID1931142.1)来修复该问题。

该文档中描述到，在$GRlD_HOME/crs/utl(Oracle11.2及12.1.0.1)或＜GRID_HOME＞crs/utl＜hostname＞(Oracle12.1.0.2)下面的crsconfig_dirs和crsconfig_fileperms文件中记录了整个$GRID_HOME下面的文件和目录的相关权限。

可以通过命令“cluvfycompsoftware-nall-verbose”来校验集群的权限是否正确。

所以要解决这个问题其实并不难，大致可以通过如下几种方法来解决：

1)根据MOS文档提供的建议通过$GRID_HOME/crs/install/rootcrs.pl-init或roothas.pl-init进行解决。rootcrs.pl-init是在PSU＞11.2.0.3.6下执行的，如果PSU＜11.2.0.3.6可以执行如下两条命令来实现同样的效果。

2)采用MOS文档1515018.1上提供的脚本在正常库上生成脚本，然后将生成的脚本在异常库上执行从而来修复权限问题。

3)Oracle11gR2可以deconfigcrs的配置，然后重新运行root.sh即可。如果OCR和数据库不在同一个磁盘组里，那么重新运行root.sh脚本并不影响数据库，所以无需担心。

4)MOS文档1515018.1上提供了一个修复脚本：permission.pl。可以根据该脚本来修复。

在安装有GI的环境下，权限、属主是严格被设定的，任何对于它们的错误修改容易引发一系列的问题，而且这些问题往往都很诡异很难按照常规的思路去诊断。一旦出现权限的问题，个人建议通过第1和第3种方式来修改。

如果可执行文件$ORACLE_HOME/bin/oracle的属主或权限设定出了问题，那么可能会造成很多问题。例如：无法登录到数据库、ora-600错误、“TNS-12518:TNS:listenercouldnothandoffclientconnection”、“LinuxError:32:Brokenpipe”、“ORA-12537:TNS:connectionclosed”、访问ASM磁盘出错等。解决办法很简单，可以在grid用户下运行setasmgidwrap命令重新配置$ORACLE_HOME/bin/oracle可执行文件的权限和属主或者直接将oracle文件的权限修改为6751。$ORACLE_HOME/bin/oracle可执行文件正确属主应该是oracle:asmadmin，并且权限必须有s共享才可以，如下：

7.

数据库中有一张如下所示的表，表名为SALES。年季度销售量19911111991212199131319914141992121199222219923231992424要求：写一个SQL语句查询出如下所示的结果。年一季度二季度三季度四季度199111121314199221222324正确答案：这是一道行转列的题目，首先建立表SALES：

此题若使用聚合函数+DECODE或CASE来回答，如下：

此题若使用PIVOT函数，如下：

此题若使用临时表的方式，如下：

8.

怎么设置存储过程的调用者权限?正确答案：普通存储过程都是定义者权限，如果想设置调用者权限，那么需要声明“AUTHIDCURRENT_USER”，参考如下语句：

9.

如何查找逻辑读、物理读、执行次数和解析次数最多以及执行时间最长的SQL语句呢?如何查找或监控效率低下的SQL语句?正确答案：效率低下的SQL一般是执行时间较长的SQL语句，可以通过如下几种方式来监控分析：

1)当前系统的SQL可以通过监控V$SQL_MONITOR、V$SESSION视图来实现，记录执行时间较长的SQL语句。对于历史SQL可以通过分析DBA_HIST_SQLSTAT视图。

2)分析现有的系统中的执行计划，重点检查驱动表与被驱动表顺序、表连接算法、排序是否有索引、索引使用。

3)分析AWR、ASH和ADDM。

4)通过OEM中性能监控的捕获执行时间5s以上的SQL程序。

5)通过GV$SQLAREA视图来分析。

10.

RMAN、NBU备份分别是什么?正确答案：(1)RMAN为了更好地实现数据库的备份和恢复工作，Oracle提供了恢复管理器(RecoveryManager，简写为RMAN)。RMAN是一个可以用来备份、恢复和还原数据库的应用程序，是随Oracle服务器软件一同安装的Oracle工具软件，通过执行相应的RMAN命令可以实现备份和恢复操作。RMAN可以用来备份和恢复数据库文件、归档日志、控制文件和系统参数文件，也可以用来执行完全或不完全的数据库恢复。

在Oracle数据库中，通过RMAN工具，可以将数据备份到磁盘或磁带上。在需要时，可以通过RMAN工具将备份的文件进行恢复。使用RMAN可以减少DBA在对数据库进行备份与恢复时产生的错误，提高备份与恢复的效率。在默认情况下，标准版和企业版的Oracle都会安装RMAN程序。

RMAN有三种不同的用户接口：COMMANDLINE方式、GUI方式(集成在OEM中的备份管理器)和API方式(用于集成到第三方的备份软件中)。

RMAN具有如下特点：

1)功能类似物理备份，但比物理备份强大很多。

2)可以把备份打包成备份集，也可以按固定大小分割备份集。

3)备份与恢复的过程可以自动管理。

4)可以使用脚本(存在RecoveryCatalog中)。

5)RMAN会检测和报告损坏的数据块。

6)可以跳过未使用的数据块。

在执行RMAN备份时，RMAN不会备份从未被写入的数据块。而传统的备份方法无法知道已经使用了哪些数据块。

7)备份压缩，也可以压缩空块。RMAN使用一种Oracle特有的二进制压缩模式来节省备份设备上的空间。尽管传统的备份方法也可以使用操作系统的压缩技术，但RMAN使用的压缩算法是定制的，能够最大程度地压缩数据块中一些典型的数据。

8)执行增量备份，可以在块级别上实现增量。如果不使用增量备份，那么每次RMAN都备份已使用块；如果使用增量备份，那么每次RMAN都备份上次备份以来变化的数据块，这样可以节省大量的磁盘空间、I/O时间、CPU时间和备份时间。

9)块级别的恢复。可以只还原或修复标识为损坏的少量数据块。在RMAN修复损坏的数据块时，表空间的其他部分以及表空间中的对象仍可以联机。

10)在备份时不需要将表空间置于热备份模式。

11)可以使用RMAN来测试备份而不需要实际还原它们。

RMAN可以用来备份：①数据库：包括数据文件、控制文件、SPFILE(ServerParameterFile)文件；②表空间；③归档文件；④备份集。

RMAN不能用来备份：①联机日志文件(OnlineRedoLogs)；②非READ/WRITE状态的可传输表空间；③PFILE(ParameterFile)文件。

(2)NBUNBU(NetBackup)是VERITAS公司提供的企业级备份管理软件，它支持多种操作系统，包括UNIX、Windows、OS/2(OperatingSystem/2)以及Mac等。目前，NBU是国际上使用最广的备份管理软件。

NBU采用全图形的管理方式，同时提供命令行接口，适应不同的用户需求。它提供了众多的性能调整功能，从管理角度看，其高性能特征十分明显。如提供多作业共享磁带机、大作业并行数据流备份、完善的监控报警、动态备份速度调整等能力，为用户的集中式存储管理提供了灵活和卓有成效的手段。

NBU还能与BMR(BootMasterRecord，主引导记录)集成在一起为用户提供关键业务系统的智能灾难恢复，即无需安装系统，只需执行一条命令就能达到全自动系统恢复，整个过程也无需人工干预，是个简单易行的“傻瓜”过程。NBU简化了用户的恢复操作，尽快将应用投入使用，大大减少了用户因停机带来的巨大损失。

通过管理界面，管理员可以设置网络自动备份策略，这些备份可以是完全备份，也可以是增量备份。管理员也可以手动备份客户端数据。客户端用户可以从客户端备份、恢复和归档自己的数据。同时，NBU还可以管理存储设备。

11.

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

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

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

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

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

12.

触发器的作用、优缺点有哪些?正确答案：触发器(TRIGGER)是数据库提供给程序员和DBA用来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，是用户定义在表上的一类由事件驱动的特殊过程。触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发的，其中，事件是指用户对表的增(INSERT)、删(DELETE)、改(即更新UPDATE)等操作。触发器经常被用于加强数据的完整性约束和业务规则等。

触发器与存储过程的区别在于：存储过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的，而是由一个事件来触发运行，即触发器是当某个事件发生时自动地隐式运行。

具体而言，触发器有如下作用：

1)可维护数据库的安全性、一致性和完整性。

2)可在写入数据表前，强制检验或转换数据。

3)当触发器发生错误时，异常的结果会被撤销。

4)部分数据库管理系统可以针对数据定义语言(DDL)使用触发器，称为DDL触发器，还可以针对视图定义替代触发器(INSTEADOF)。

触发器的优点：触发器可通过数据库中的相关表实现级联更改。从约束的角度而言，触发器可以定义比CHECK更为复杂的约束。与CHECK约束不同的是，触发器可以引用其他表中的列。例如，触发器可以使用另一个表中的数据来比较更新的数据以及执行其他操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表的状态，并根据其差异采取对策。一个表中的多个同类触发器(INSERT、UPDATE或DELETE)允许采取多个不同的对策以响应同一个修改语句。

当然，虽然触发器功能强大，可以轻松可靠地实现许多复杂的功能，但是它也具有一些缺点，滥用会造成数据库及应用程序的维护困难。在数据库操作中，可以通过关系、触发器、存储过程、应用程序等来实现数据操作。同时，规则、约束、默认值也是保证数据完整性的重要保障。如果对触发器过分地依赖，那么势必会影响数据库的结构，同时增加了维护的复杂性。

对于触发器，需要特别注意以下几点：

1)触发器在数据库里以独立的对象存储。

2)存储过程通过其他程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。

3)触发器被事件触发。运行触发器称为触发或点火(FIRING)，用户不能直接调用触发器。

4)触发器不能接收参数。

13.

COUNT(1)比COUNT(*)在执行效率上要快吗?正确答案：错。COUNT(1)和COUNT(*)在执行效率上是一样的。COUNT()函数是Oracle中的聚合函数，用于统计结果集的行数。其语法形式如下：

可以把COUNT的使用情况分为以下3类：

1)COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)。

2)COUNT(允许为空列)。

3)COUNT(DISTINCT列名)。

下面分别从查询结果和效率方面做个比较：

(1)结果区别

1)COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)这几种方式统计的行数是表中所有存在的行的总数，包括值为NULL的行和非空行。所以，这几种方式的执行结果相同。这里的常量可以为数字或字符串，例如，COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是：这里的COUNT(1)中的“1”并不表示表中的第一列，它其实是一个表达式，可以换成任意数字或字符或表达式。

2)COUNT(允许为空列)这种方式统计的行数不会包括字段值为NULL的行。

3)COUNT(DISTINCT列名)得到的结果是除去值为NULL和重复数据后的结果。

4)“SELECTCOUNT("),COUNT(NULL)FROMT_COUNT_LHR;”返回0行。

(2)效率、索引

1)如果存在主键或非空列上的索引，那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)会首先选择主键上的索引快速全扫描(INDEXFASTFULLSCAN)。若主键不存在则会选择非空列上的索引。若非空列上没有索引则肯定走全表扫描(TABLEACCESSFULL)。其中，COUNT(ROWID)在走索引的时候比其他几种方式要慢。通过10053事件可以看到这几种方式除了COUNT(ROWID)之外，其他最终都会转换成COUNT(*)的方式来执行。

2)对于COUNT(COL1)来说，只要列字段上有索引则会选择索引快速全扫描(INDEXFASTFULLSCAN)。而对于“SELECTCOL1”来说，除非列上有NOTNULL约束，否则执行计划会选择全表扫描。

3)对于COUNT(列)来说，随着列的偏移位置越大，COUNT(列)的速度越来越慢。在设计表时，把经常访问的列尽量设计在表的前几列。

4)COUNT(DISTINCT列名)若列上有索引，且有非空约束或在WHERE子句中使用ISNOTNULL，则会选择索引快速全扫描。其余情况选择全表扫描。

14.

等待事件的分类?常见等待事件?正确答案：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。

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

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

15.

什么是ASMM和AMM?正确答案：ASMM(AutomaticSharedMemowManagement，自动共享内存管理)是Oracle10g引入的概念。通过使用ASMM，就不需要手工设置相关内存组件的大小，而只为SGA设置一个总的大小，Oracle的MMAN进程(MemoryManagerProcess，内存管理进程)会随着时间推移，根据系统负载的变化和内存需要，自动调整SGA中各个组件的内存大小。ASMM的SGA包含的组件及对应参数见表。

在Oracle10g中，必须将STATISTICS_LEVEL参数设置为TYPICAL(默认值)或者ALL才能启用ASMM功能，如果将其值设置为BASIC，那么会禁用很多新特性，比如像AWR、ASMM等。如果使用SQL*Plus来设置ASMM，那么必须先把SGA中包含的组件的值设置为0。通过设置SGA_TARGET参数为非零值来启用ASMM功能。

可以通过以下SQL来计算SGA_TARGET的值：

设置SGA_TARGET的值，可以直接修改初始化参数文件后重启数据库，或者通过下面SQL命令进行修改：

示例如下：

在启用ASMM后，Oracle会自动调整SGA内部组件大小。若再手动指定某一组件值，则该值为该组件的最小值。例如，手动设置SGA_TARGET=8G，SHARED_POOL_SIZE=1G，则ASMM在自动调整SGA内部组件大小时，保证SharedPool不会低于1G。当设置了SGA_TARGET参数后，Oracle将会收集SGA相关的统计数据，并通过V$SGA_TARGET_ADVICE呈现出来，因此，可以根据这些指导SGA_TARGET做相关的调整，以达到最佳情况。

Oracle10g的ASMM实现了自动共享内存管理，但是具有一定的局限性。所以，在Oracle11g中，Oracle引入了AMM(AutomaticMemoryManagement，自动内存管理)的概念，实现了全部内存的自动管理。DBA可以仅仅通过设置一个目标内存大小的初始化参数(MEMORY_TARGET)和可选最大内存大小初始化参数(MEMORY_MAX_TARGET)就可以在大多数平台上实现AMM。AMM可以使实例总内存保持相对稳定的状态，Oracle基于MEMORY_TARGET的值来自动调整SGA和PGA的大小。如果内存发生变化，实例会自动在SGA和PGA之间做调整。若启用了AMM功能，而SGA_TARGET和PGA_AGGREGATE_TARGET没有显式的被设置，则默认SGA_TARGET为MEMORY_TARGET的60%，PGA_AGGREGATE_TARGET为MEMORY_TARGET的40%。

MEMORY_TARGET是动态初始化参数，可以随时修改该参数的值而不用重启数据库。MEMORY_MAX_TARGET作为一个内存上限值，是一个静态参数，它是MEMORY_TARGET可以被配置的最大值。

当LOCK_SGA初始化参数的值设置为TRUE时，不能启用AMM，该参数的值默认为FALSE。

如果在创建数据库的时候未启用AMM，那么可以在建库后启用它，启用AMM需要重启数据库，具体步骤如下：

1)查询SGA_TARGET和PGA_AGGREGATE_TARGET的值，从而确定MEMORY_TARGET的最小值。

2)确定白系统启动以来PGA的最大值，单位为bytes。

3)通过以下方法来计算出SGA_TARGET的最大值。

例如：在这里，SGA_TARGET的值为1648M，PGA_AGGREGATE_TARGET的值为409M，PGA的最大值为248586240/1024/1024=237M，所以，MEMORY_TARGET的值至少为1648+409=2057M。

4)设置系统参数启用AMM。

另外需要说明的一点是，使用AMM经常出现的一个错误是“ORA-00845:MEMORY_TARGETnotsupportedonthissystem”。

这个错误原因是操作系统不支持MEMORY_TARGET参数或/dev/shm的大小设置不正确。解决方法就是将/dev/shm的值增大，至少需要大于数据库参数MEMORY_MAX_TARGET的值。修改步骤如下：

再次启动数据库就可以正常启动了。

由于AMM不支持HugePage，而ASMM支持HugePage，所以，在生产库上强烈推荐使用ASMM。有关ASMM和AMM的区别见表。

16.

本地管理表空间(LMT)和字典管理表空间(DMT)的特点有哪些?正确答案：在表空间中以区为单位分配空间。在创建表空间后可使用本地管理表空间(LMT)或字典管理表空间(DMT)来跟踪空闲空间和已用空间。

字典管理表空间(DictionaryManagedTablespace，DMT)，它是Oracle8i及以前版本使用的一种表空间管理模式，不过在Oracle8i及以后的版本中仍然保存有该特性。DMT是通过数据字典管理表空间的空间使用(其实是管理区)。每当分配或取消分配区后，Oracle服务器会更新数据字典中的相应表。用于管理的两个数据字典表分别是：UET$(UsedExtents)和FET$(FreeExtents)。DMT是为了实现向后兼容而提供的，建议使用本地管理的表空间。

本地管理表空间(LocallyManagedTablespace，LMT)是从Oracle8i出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用(其实是管理区)。位图中的每个位都对应于一个块或一组块。Oracle在分配区或释放区后可以重新使用，Oracle服务器通过更改位图值来显示块的新状态。LMT在Oracle9i及以后版本中成了默认选项。

表空间管理模式的查询如下，其中EXTENT_MANAGEMENT列表示表空间的管理模式：

将字典管理的表空间转换为本地管理：

将本地管理的表空间转换为字典管理：

17.

什么是专用服务器和共享服务器?正确答案：在连接到Oracle数据库的时候，可以有两种连接模式，一种称为专用服务器连接(DedicatedServer)，另外一种称为共享服务器连接(SharedServer)。下面将分别讲解这两种连接方式的不同点。

专用服务器：每次在对Oracle进行访问的时候，Oracle服务器的Listener会得到这个访问请求，然后会为这个访问创建一个新的进程来进行服务。所以说，对于每一个客户端的访问，都会生成一个新的进程进行服务，是一种类似一对一的映射关系。这种连接模式的一个很重要的特点就是UGA(UserGlobalArea，用户全局区)是存储在PGA(ProgramGlobalArea，程序全局区)中的，这个特性也很好说明了当前用户的内存空间是按照进程来进行分配的。专用服务器连接模式是Oracle默认的连接模式。

共享服务器：也称为多线程服务器(Multi-ThreadedServer，MTS)。若采用共享服务器模式的话，则在数据库初始化的时候就会创建一批服务器连接的进程，然后把这些连接进程放入一个连接池来进行管理。初始化的池中的进程数量在数据库初始化建立的时候是可以手动设置的。在连接建立的时候，Listener首先接收到客户端的建立连接的请求，然后Listener去生成一个称为调度器(Dipatcher)的进程与客户端进行连接。调度器把客户端的请求放在SGA(SystemGlobalArea，系统全局区)的一个请求队列中，然后在共享服务器连接池中查找有无空闲的连接，然后让这个空闲的服务器进行处理。处理完毕以后再把处理结果放在SGA的相应队列中。调度器通过查询相应队列，得到返回结果，再返回给客户端。这种连接模式的优点在于服务器进程的数量可以得到控制，不大可能出现因为连接数过多而造成服务器内存崩溃。但是由于增加了复杂度以及请求相应队列，可能在性能上有所下降。

如果在前端使用了Weblogic的连接池，那么在数据库级别就没有必要再使用共享服务器了，因为这时的用户连接已经在Weblogic层面上得到了控制。其实专用服务器和共享服务器是可以并存的，即使使用了共享服务器，某些管理操作也是必须在专用服务器模式下来做的，比如STARTUP或SHUTDOWN。

(1)启用共享服务器

要切换到共享模式，可以使用以下步骤：

1)设置初始化参数SHAREDSERVERS大于0，然后重启数据库即可启动共享模式，SQL如下：

其中，SHARED_SERVERS指定了当实例启动的时候共享服务器进程启动的数量。如果这个参数的值为0，那么表示数据库没有启动共享服务模式。这个参数是配置共享服务器必需的，而且只有这个参数是必需的。MAX_SHARED_SERVERS是Oracle在同一个时刻最大能够使用的共享服务器进程数量。如果SHARED_SERVERS大于MAX_SHARED_SERVERS，那么Oracle会以SHARED_SERVERS参数的值覆盖MAX_SHARED_SERVERS。

2)设置DISPATCHERS参数，可以采用下面的方法设置：

其中，“PROTOCOL=TCP”表示的是协议，“SERVICE=lhrdbXDB”指定要采用共享服务器模式的服务名称。使用上面的模式指定只启动某个服务的共享模式，如果要设置所有服务都使用共享模式，则设置为：

3)客户端的tnsnames.ora的文件中，在CONNECT_DATA设置一项中增加“(server=shared)”一项，即可使用共享服务器连接，如下：

其中，SERVICE_NAME的值可以通过命令“lsnrctlservices＜listenername＞”来查看，如下：

如果服务器端没有启动共享服务器模式，而客户端使用shared方式连接的话，那么会出现错误提示：“ORA-12520:TNS:监听程序无法找到需要的服务器类型的可用句柄”。

如果同时把SHARED_SERVERS和MAX_SHARED_SERVERS都设置成0，那么共享服务器将关闭。

(2)判断会话的连接模式

可以从V$SESSION视图的SERVER列里查看：

若显示的结果中有NONE或SHARED，则说明当前启动了共享服务器模式。其中，状态为NONE的会话表示当前共享服务器进程没有任务处理，若进程正在执行某些任务则会表现为SHARED状态。如果只显示有DEDICATED，则不能说明服务器就一定工作在专用服务器下面，此时只能说明有可能启动了共享模式，但是无连接。

此外，还可以通过视图V$SHARED_SERVER、V$DISPATCHER及V$CIRCUIT视图查询是否启用了共享服务器连接。若V$SHARED_SERVER有记录，且STATUS字段为“WMT(COMMON)”，则说明启动共享；若STATUS为TERMINATED或者无记录，则说明没有启动共享服务器。V$DISPATCHER有无记录都不能说明启动共享服务器，只能说明是配置了DISPATCHERS参数。V$CIRCUIT有记录说明当前有使用共享模式的连接，无记录则不能判定服务器模式。其实通过命令“lsnrctlservices＜listenername＞”也可以判断是否启用了共享服务器连接。

当服务器采用专用服务器模式时，客户端只能使用专用模式连接，也就是在CONNECT_DATA数据中只能使用“SERVER=DEDICATED”。当服务器采用共享服务器模式时，客户端可以选择建立共享连接或是专用连接，只要在CONNECT_DATA中指定“SERVER=DEDICATED”或“SERVER=SHARED”。

18.

在Oracle中，有哪些常用的分析函数?正确答案：在分析函数出现以前，实现相同的功能必须使用自联查询、子查询或者内联视图，甚至需要复杂的存储过程来实现。有了分析函数后，只要一条简单的SQL语句就可以实现数据分析了，而且在执行效率方面也有相当大的提高。Oracle的分析函数主要用于报表开发和数据仓库。分析函数的功能强大，可以用于SQL语句的优化，在某些情况下，能达到事半功倍的效果。

分析函数的一般格式是：函数名(参数列表)OVER([PARTITIONBY字段名或表达式][ORDERBY字段名或表达式])，其中OVER()部分称为开窗函数，它是可以选填的。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。分析函数的写法比较复杂，下面将讲解几个常用的分析函数。

(1)RANK()分析函数该函数的作用是根据ORDERBY子句中表达式的值，从查询返回的每一行中，计算它们与其他行的相对位置。该函数的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果为：1114。RANK()分析函数可以用于Top-N查询中。

需要注意的是，除了ORDERBY子句的运算外，分析函数在SQL语句中将会最后执行。因此，分析函数只能应用于SELECT的列或ORDERBY子句中。

还有一个类似的函数为：DENSE_RANK()OVER(ORDERBY列名排序)，它的排序结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果为：1112。

如果不想并列排序，那么可以使用ROW_NUMBER分析函数。

(