oracle dba 面试题集锦1

总结一下这半个来月面试遇到的笔试题和面试题，希望能给要面试的同学们一点帮助！废话不多说，开整.

1.      列举几种表连接方式：

答：

嵌套循环（Nested Loop（NL））、哈希连接（HashJoin（HJ））、排序合并连接（Sort Merge Join（SMJ））

NL：做的是一个外表驱动内表的2层嵌套循环，外表一般是张较小的表或返回子集较小的表（<10000）, 内表的连接字段上一定要有索引        ------需要索引，百搭

适用于：NL是行源连接方式，可以先返回已经连接的行，而不必等待所有的连接处理完才返回数据。

HJ：哈希连接的内部机制我也不是很了解，不好意思。             -----不需要索引，没索引的时候走容易走这个，添加索引对其没有用，对hj调优是增大pga中的hash_area

适用于：HJ是大数据集连接时常用的方式，适用于等值连接（效率最高），大多数情况下，HJ的效率比其他的连接方式高。

SMJ：先将两个表排序，然后将排序后的两个表合并。

适用于：如果连接的行源已经被排序过，SML效率高于HJ，否侧HJ更高

*Nl是行源连接方式（适用于小数据量）、HJ和SMJ都是集合连接方式（适用于大数据量）

具体见：http://www.itpub.net/thread-1611025-1-1.html

 

2.      使用索引查询一定能提高查询的性能吗?为什么？

答：不一定

     （1） 对于大表而言，如果要查询的行数过多（比如30%以上），走全表扫描可能性能会更好一些，成本会更低。

（2） 对于小表而言，也许就用了一个8k的block，加载一次就可以了，此时再走索引，在查询索引的过程中还要加载索引块，反而没有全表扫描快。

 

3.      索引有哪些种类？各有什么特点？索引一般建立在什么特点的字段上？编写SQL应注意什么以免导致无法用上索引？

答：

（1）B-Tree索引 ：查询比较时，比较的是键值，适用于OLTP

            单键索引、复合键索引、唯一索引、反键索引

       BITMAP：索引查询比较时，比较的是0和1这样的位图，并发（DML）性差，适用OLAP

 （2）索引一般建立在

       a、高基数列 b、频繁出现在where子句中的字段 c、大表的外键列

 （3）a、where子句中使用is null或is not null导致不能使用索引

         b、where子句中有连接列：

如（last_name上有索引） where first_name||’  ’||last_name=‘MichaelJackson’

         可改为：where frist_name =’Michael’ andlast_name=’Jackson’

     c、通配符（%）出现在like的词首：

         如 where last_name like ’%ackson’ 这样优化器是不会走索引的！

        d、NOT/’<>’的使用

         如 where salary <>5000  不使用索引！ Where salary<5000 orsalary>5000使用索引！

     e、在使用子查询中尽量用exist代替in

              wherecol in(select * from ...where...)   换成

              whereexists(select ‘X’ from ...where...)

 

4、怎样查看sql的执行计划?

答：(1) set autot on/off （结果+计划+统计）、setautot trace（计划+统计）、set autot trace exp/stat

  (2) utlxplan 创建plan_table表，explain plan for select count(*) from emp;

  （3）alter session set sql_trace=true;

              Select.....

              altersession set sql_trace=false;

              Udump下  $tkprof ###.trc  1.txt

   (4) select sql_id from v$sqlarea;

         Select plan_table_output fromtable(dbms_xplan.dispaly_cursor(‘sql_id’));

   (5) 借助第三方工具——plsql developer

 

5、如何定位消耗资源的SQL

答：(1)可以通过v$sqlarea中的DISK_READS、OPTIMIZER_COST、CPU_TIME、ELAPSED_TIME等字段综合考虑定位消耗资源的sql

       (2)或者通过v$session找出cpu使用过量的session, 抓取其等待时间和sql_id

       (3)通过分析AWR报告中的 SQLstatistics

 

6、如何跟踪某个session的SQL

答：select sid,serial# from v$session where username = ‘SCOTT’;

Execdbms_system.set_sql_trace_in_session(sid,serial#,ture);

然后在udump下

ll –rt

$tkprof ###.trc1.txt

 

7、如何稳定(固定)执行计划（这个是我自己的理解可能不对，详情看http://server.chinabyte.com/482/2648482.shtml）

答:用outlines

首先打开执行计划的跟踪：setautot on

然后为session创建一个category（category用于存outlines）

alter session set create_stored_outlines = my_demo;   (这个my_demo就是个category；当然你可以 =true 系统默认的category)

然后执行你的SQL：如 select * fromemp;

然后关闭category：alter session setcreate_stored_outlines = false;

最后用exp导出这个category注入到的你目标库（这个导出category的exp格式没找到...），在目标库使用这个category

 

8、和排序相关的内存怎么调整？临时表空间的作用是什么？

（参考http://blog.chinaunix.net/uid-21267615-id-2406052.html）

答：

（1）Sort_area_size这块内存是用来排序的。

        在9i以后使用pga_aggregate_target（pga的内存总和，前提workarea_size_policy=auto这个值才有意思）来统一管理。

Pga自动管理后，所有*_area_size的值都将被忽略

（2）临时表空间的作用：1、sort_area_size中不能完成工作在临时表空间完成（存储排序的中间结果）

                                      2、临时表存在临时表空间中，rebuild索引用到临时表空间

                                     3、hash join不能在内存中完成的也在临时表空间中做

9、回滚段的作用

答：回滚段用于存放数据修改之前的值（包括位置和值）

       作用：1、事物回滚：

                       当事物修改表的数据时，该数据修改前的值（即改前镜像）会存放在回滚段中。当事物rollback时，oracle会利用改前镜像来恢复数据原来的值。

          2、事物恢复（其实也是事物的回滚）：

                       当事物正在处理时（未commit），实例失败，回滚段的信息保存在联机重做日志文件中，oracle会在下次打开库时利用回滚段的信息rollback未提交的事物。

          3、读一致性

                       分为事务级读一致性和语句级的读一致性；

                       前者(构造CR块)：说的就是提交即存在：比如说我session1删掉t2表的一条数据，没有提交，这时候我内存中的块已经被修改了，对应的行删除了。

这时我session2要访问这条数据，发现这条数据库没有提交，那它就不会读这条数据对应的块，而是在buffer_cache中从新构造一个块，把那个快中提交的数据读到这个新建的块中，没提交的数据中undo段中读过来，这个新构造的块就是CR块！

                       后者：比如你执行一个select，oracle会记录当前的scn，这个scn以后提交的数据不会被查询到。（有时候执行一个大的查询，就可能会报1555这个错误（快照过旧），就是说你那个SCN点的数据在回滚段中没了，增大回滚段的保留时间还有大小）

 

10、rman是什么,有何特点?

答：RMAN(RecoveryManager)是一种常用的用来备份、还原、恢复数据库的工具

特点：1、功能类似物理备份，但比物理备份强大N倍

      2、可以压缩备份

      3、可能完成增量备份

      4、引入了备份片、备份集的概念，可以按固定大小分割备份集

     5、备份和恢复的过程可以自动管理

     6、可以使用脚本（存在recoverycatalog中）

         7、做坏块检测

 

11、pctfree和pctused的作用

（参考http://zhangyafeng0917.blog.163.com/blog/static/4443641220127841433388/）

答：pctfree和pctused是控制一个块进出freelist的参数

       Pctfree（默认10）： freespace<pctfree  这个块被移出freelist

Pctused（默认40）：当使用率的小于了40%  这个块重新回到freelist

 

注：pctused设置较高，块的利用率（空间利用率）提高，i/o下降；设置较低反之。

       Pctfree设置较低，会导致产生较多的行迁移，行迁移很影响性能。

       Freelist存在段头中。

 

12、本地管理表空间和字典管理表空间的特点，ASSM有什么特点

（参考http://www.itpub.net/thread-142629-1-1.html）

答：表空间内的空间管理-----datefile的管理

1、  字典管理模式-----在system表空间存储表空间内有哪些空闲

2、  本地管理模式-----在文件头，自己来管理文件内部的空闲

       字典管理表空间（DMT）：

              通过数据字典管理表空间的空间使用,通过两张字典SYS.FET$和SYS.UET$来记录空闲和使用的空间，在修改这两张字典时，必须先获得space transaction(ST) enqueuelatch，索引会引起竞争和等待

              Createtablespace test1 datafile ‘/u01/app/oracle/oradata/test/test1.dbf’size 50M

              Extent management dictionary

              DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

       本地管理表空间（LMT）

              8i之后出的一种新的表空间管理模式，通过位图来管理(每个数据文件的前3-8个块)。

              LMT比DMT在管理效率上有所提高：

              1、避免了递归的空间管理操作

              2、减少了数据字典表的竞争，提高了空间管理的并发性

              3、减少了手工合并剩余空间的需要

              4、减少了空间碎片

              5、不产生回滚信息

              Create tablespace test2 datafile ‘/u01/app/oracle/oradata/test/test2.dbf’size 50M

              Extent management localautoallocate;

              (Extent management local uniformsize 128K;)

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

       段内的空间管理有两种模式

1、  手动管理----使用空闲列表system表空间里的段

2、  自动管理----使用位图  user等表空间的段    ASSM

ASSM的特点：ASSM首次出现在9i中，空闲列表被位图取代，它是一个二进制的数组，在ASSM表空间上创建的段叫bitmap managed segments（BMB段）

 

注：select tablespace_name, extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME   EXTENT_MANAGEMENT  SEGMENT_SPACE_MANAGE

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

SYSTEM                         LOCAL                   MANUAL

UNDOTBS1                       LOCAL                  MANUAL

SYSAUX                         LOCAL                                AUTO

TEMP                           LOCAL                            MANUAL

USERS                          LOCAL                                 AUTO

 

 

 

 

 

 

 

 

 

13、怎么查看表空间存储使用情况

selectdbf.tablespace_name,

       dbf.totalspace "总量(G)",

       dbf.totalblocks as 总块数,

       dfs.freespace "剩余总量(G)",

       dfs.freeblocks "剩余块数",

       (dfs.freespace / dbf.totalspace) * 100"空闲比例"

  from (select t.tablespace_name,

               sum(t.bytes) / 1024 / 1024 /1024 totalspace,

               sum(t.blocks) totalblocks

          from dba_data_files t

         group by t.tablespace_name) dbf,

       (select tt.tablespace_name,

               sum(tt.bytes) / 1024 / 1024 /1024 freespace,

               sum(tt.blocks) freeblocks

         from dba_free_space tt

         group by tt.tablespace_name) dfs

 where trim(dbf.tablespace_name) =trim(dfs.tablespace_name);

14、捕获运行时间较长的sql

答：1、selectSQL_TEXT,ELAPSED_TIMEfromv$sqlareaorderby ELAPSED_TIME desc;

2、在AWR报告中的‘SQL order by Elapsed Time’中看

谁有更好的办法请留言啊~~谢谢

15、检查数据文件间的 I/O活动、确定当前I/O活动频繁的dbfile

答： select name,phyrds,phywrts,readtim,writetim

 from v$filestat a,v$dbfile b

where a.file# = b.file# order by readtim desc

 

16、监控表空间的I/O比例

select df.tablespace_name name,df.file_name"file",f.phyrds pyr,

           f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw

     from v$filestat f, dba_data_files df

     wheref.file# = df.file_id

order by df.tablespace_name;

 

PHYRDS：已完成的物理读次数

 

PHYBLKRD：BLOCK读取数

 

PHYWRTS：DBWR完成的物理写次数

 

PHYBLKWRT：写入磁盘的BLOCK数

 

17、查看碎片程度高的表

SELECTsegment_name table_name, COUNT(*) extents

    FROM dba_segments

WHERE ownerNOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

    HAVING COUNT(*) =(SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

 

未完待续.......

 

 

http://blog.sina.com.cn/s/blog_61c006ea0100m5cr.html   健康检查