面试过程中的问题

转自：http://space.itpub.net/10710960/viewspace-417388

1：使用索引查询一定能提高查询的性能吗?举出实例

2：数据库设计的一、二、三范式、BCNF是什么？

3：StoreProcedure和Function有何区别？

4：如何跟踪某个session的SQL？

5：如何使用CBO,CBO与RULE的区别？

6：描述tablespace和datafile之间的关系

每个数据库可以有多个表空间，每个表空间可以有多个数据文件（并且可以放在不同的磁盘上），但每个表空间只能属于一个数据库，每个数据文件只能属于一个数据库。

7：truncate和delete有何区别？哪一种操作性能更快？

truncate是DDL语句，自动提交。可以降低HWM。立即释放物理存储空间。不写日志，不能回滚。

delete是DML语句，只有通过commit提交后，才能删除数据；可以使用rollback进行回滚，要写日志。无法降低HWM。无法立即释放物理存储空间，因为delete操作只是将记录打上了删除标记。

8：什么是Snapshot?与View有何区别？

9：pctused and pctfree 表示什么含义有什么作用

10：cursor一般用在什么地方？有何弱点？

管理篇

1：SQL调整最关注的是什么？

2：Oracle的系统进程有哪些？作用是什么？

SMON:系统监督进程
     当实例崩溃，自动做实例恢复。主要做前滚（将db buffer cache中的脏数据写到数据文件中）和回滚（没提交的数据）。

PMON:进程监督进程
     在没有正确退出oracle等造成的进程崩溃，PMON会做清理工作：如释放资源，释放锁，回滚事务，重启dispatcher等。

LGWR:日志写进程
     工作原理：将redo buffer cache中的redo记录，顺序的 写到redo log file里
     触发LGWR的条件：
        1.每3秒
     2.redo buffer cache里的数据，超过1MB
     3.redo buffer cache里的数据超过总容量的1/3。
     4.在DBWR写之前，会写
     5.当事务commit时 

DBWR/DBWn：数据库写进程
   工作原理：将db buffer cache中的脏数据，写到数据文件中
   触发DBWR的条件：
     1.当发生checkpoint
     2.db buffer cache中的脏数据到达规定的限度
     3.超时3秒（在db buffer cache中里3秒没有找到空闲空间时）
     4.truncate或delete表中数据时
     5.将表空间设置为read only时
     6.使用alter tablespace users begin backup进行联机备份时
     7.当表空间被offline或重新修改成online时

CKPT:检验点进程
   工作原理：产生校验点，保持数据的一致性。

可选进程：
ARCH/ARCn：归档日志进程
   工作原理：备份redo log file。
   触发机制：当redo log file进行完switch log后，将写满的redo log file写到归档日志文件中。

3：本地管理表空间和字典管理表空间的特点，ASSM有什么特点？

字典管理表空间（dmt）：当数据库繁忙时，会产生数据字典的竞争，在做完修改后，会做undo和redo信息，对系统性能造成严重的影响。

空间碎片对数据库造成的影响：

1) 导致系统性能衰减：大大降低空间分配中的速度。
2) 大量空间被浪费：有一部分自由空间将会被SMON周期性地合并，但始终有一部分自由空间无法得以自动合并，从而浪费空间。要考虑pctincrease的值是否为0。
3) 空间释放成为难题：在用DMT管理表空间时，drop一个大表时可能要花很长，因为在删除表时，Oracle同样要维护其空间分配，uet$和fet$也要做相应的大规模的维护。

本地管理表空间（lmt）：

1) 通过位图代替字典管理。本地管理的表空间用数据文件头的位图块来记录和管理空间的分配和回收，避免了递归的空间管理操作，从而避免了字典操作以及因为字典操作而带来的性能问题。
2) 避免了碎片问题及空间浪费。本地化管理可以通过UNIFORM或AUTOALLOCATE的方式进行区间管理，通过对Extent进行一致性约定，传统的空间碎片问题得以解决，进而空间的分配效率也大大提高。

ASSM：自动段空间管理

1) 位图数组能够减轻缓冲区忙等待（buffer busy wait）的负担；
2) 位图数组显著地消除所有段头的竞争；
3) 获得超快的并发插入性能。

4：备份如何分类？
1. 逻辑备份：通过逻辑导出对数据进行备份，逻辑备备份的数据只能基于备份时刻进行数据转储，所以恢复时也只能恢复到备份时保存的数据。
 1) 对于故障点和备份点之间的数据，逻辑备份无能为力，逻辑备份适合备份那些很少变化的数据库。
 2) 如果通过逻辑备份进行全库恢复，通常需要重建数据库，导入备份数据来完成，这样恢复时间长，一般对于高可用数据库不被采用。
 3) 逻辑备份与平台无关，常被作为一个数据迁移及移动的主要手段。
2. 物理备份：通过备份物理文拷贝的方式对数据库进行备份，物理备份又可以分为冷备份和热备份。
 1) 冷备份：对数据库进行关闭后的拷贝备份，这样的备份具有一致和完整的时间点数据，恢复时只需要恢复所有文件就可以启动数据库，冷备对故障点和备份点之间的数据变化无能为力。
 2) 热备份：最常用在生产系统备份。进行再热备份的数据库必须运行在归档模式下，且不需要关闭数据库，能保证系统的持续运行，在进行恢复时，通过备份的数据文件及归档日志等文件，数据库可以进行完全恢复，恢复可以一直进行到最后一个归档日志，如果联机日志存在，则恢复可以继续，实现务数据损失的完全恢复。
 3) 如果为了恢复某些用户错误，热备份的恢复完全可以在某一个时间点上停止恢复，这就是不完全恢复。

5：如果一个表被drop，在有完善的归档和备份的情况下，如何恢复

6：rman是什么，有何特点？
RMAN:Oracle Recovery Manager。Oracle的恢复管理器。
特点：
1.备份执行期间不需要人工介入，从而减少了误操作的可能
2.可以有效地将备份和恢复结合起来
3.支持除逻辑备份以外的所有备份类型，包括完全备份，增量备份，表空间备份，数据文件备份，控制文件备份及归档日志文件备份等
4.可以通过RMAN识别corrupted block，并可以通过RMAN进行块级恢复
5.方便的实现定期（定时）备份
6.自动生成备份日志
7.RMAN的备份脚本和OS无关，方便移植
8.强大的报表功能可以方便地获悉备份的可用性
9.RMAN备份可以跳过未使用过的数据块，从而缩减备份集大小。当使用系统工具拷贝Oracle文件进行备份时，是无法区分Oracle数据块是否适用的，RMAN则可以根据高水位标记来识别从未使用过的数据块，在备份时这些数据块可以被跳过
10.从Oracle 10g开始，Oracle可以对备份集进行压缩，从而缩减备份空间的占用。备份压缩会消耗额外的CPU，但可以节省存储，具体应该根据系统情况进行考虑。
11.从10g开始，通过RMAN可以实现跨平台的表空间迁移。

7：standby的特点

8：对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略

9：对于一个存在系统性能的系统，说出你的诊断处理思路

10：列举几种诊断IO、CPU、性能状况的方法

11：对statspack有何认识

12：如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响

13：对raid10 和raid5有何认识

14：SGA主要有那些部分，主要作用是什么
 SGA：参数：sga_max_size。包含share_pool,large_pool,java_pool,db buffer cache,redo buffer cache
   shared_pool:存放最近使用的SQL，PL/SQL。shared_pool_size
   db buffer cache：存放一些查询结果。db_block_size * db_cache_size
   redo buffer cache：存放对数据库操作的记录.
   large_pool：为了减少shared pool里的负担。RMAN的备份恢复信息,并发查询进程信息,当数据库是共享服务器模式时,UGA(user session information + cursor state)large_pool_size
   java_pool：存放一些java代码。java_pool_size

15：简单描述table / segment / extent / block之间的关系
table是由segment组成，一个table就是一个segment
一个segment是由一个或多个extent组成的
一个extent是由一个或多个db_block组成的
一个db_block是由一个或多个os_block组成的

16：说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)

17.表空间产生碎片的原因？

表空间碎片主要是由于dml管理的表空间导致的：

1) 空间碎片：在DMT管理时，当要满足一个空间要求时，数据库会寻找表空间的自由空间，如果自由空间大于请求空间，那么自由空间就会被划分，，这样就会逐渐形成越来越多的离散的，分割的，小块的自由空间，即空间碎片。
2) 表空间中的自由空间不连续，每块自由空间都很小，所以导致无法创建表等操作。

18.什么是行迁移，行连接？

19.如何降低hwm？
1) Move对象：通过move对象，可降低HWM，但move后需要重建index，会影响到在线用户，所以很少用。
2) 导出/导入与Truncate结合：如果删除所有记录，用Truncate会降低HWM；如果表中剩余的是少量数据，可以通过EXP导出数据或者使用CTAS创建一张临时表，然后Truncate表，再导入或insert回数据，此方法对于不间断服务的数据表不合适。
3) rename + insert结合：如果是连续使用的以写为主的日志类数据，通过rename将数据表更名，然后按原来的结构重建数据表，此时插入操作可以恢复，然后将rename表中的数据插入回来，可降低HWM。此方法适用于写为主的业务类型，不适合增删改查频繁的对象。
4) 在线重定义（DBMS_REDEFINITION）：对表进行在线重定义，如修改字段名称，增加字段等，还可以通过此包进行空间重整。过程类似于3。
5) 联机段空间回收（Shrink）：用于支持在线空间重整，仅对ASSM表空间中的表有效。Shrink本质就是对表执行一系列的DML操作，对于在表的末端找到的行，就删除它们，并在表的顶端重新插入。这样可以填满表段中的空闲空间，而把所有剩余空间留在表的末端，然后Oracle可以重置该表的HWM，释放自由空间。Shrink是对数据行进行操作的，所以会在表上加行级排他锁，所以不影响全表的DML操作，这就是online的意思。对于大数据量的表，用此操作由于产生大量redo，所以对于使用在生产库要考虑清楚。
6) 做空间重整的各种办法都要以一定的性能牺牲为代价，使很多放在在生产环境不适用。
7) 分区是常用的方法，涉及到大量数据变更的数据表，很多可以通过分区来处理，分区表可以针对分区进行drop，truncate等操作，可以很容易对分区进行维护。但此方法也要分情况，分环境来做。

20.如何对表或索引做分析？

可以使用analyze命令，也可以使用Oracle自带的包。

analyze index(table) *** computer statistics;

analyze index inx_name validate struture; 

dbms_stats.gather_table_stats

dbms_stats.gather_index_stats