数据库笔试题及答案

1、阿里巴巴的 Oracle DBA 笔试题参考答案 - SQL tuning 类SQL tuning 类参考解答：一： SQL tuning 类1:列举几种表连接方式程序代码hash join/merge join/nest loop(cluster join)/index join2:不借助第三方工具，怎样查看sql 的执行计划程序代码set autot onexplain plan set statement_id = &item_id for &sql;select * from table(dbms_xplan.display);http:/download- . /b1

2、0752/ex_plan.htm3:如何使用 CBO,CBO 与 RULE 的区别在optimizer_mode=choose时,如果表有统计信息(分区表外)，优化器将选择 CBO,否则选RBO。RBO遵循简单的分级方法学，使用15种级别要点，当接收到查询，优化器将评估使 用到的要点数目 , 然后选择最佳级别 (最少的数量 )的执行路径来运行查询。CBO 尝试找到最低成本的访问数据的方法 ,为了最大的吞吐量或最快的初始响应时间 , 计算使用不同 的执行计划的成本， 并选择成本最低的一个 ,关于表的数据内容的统计被用于 确定执行计划。4:如何定位重要 (消耗资源多 )的 SQL程序代码selec

3、t sql_textfrom v$sqlwhere disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);5:如何跟踪某个session的SQL程序代码exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);exec dbms

4、_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');6:SQL 调整最关注的是什么查看该 SQL 的 response time(db block gets/consistent gets/physical reads/sorts (disk)7: 说说你对索引的认识 (索引的结构、对 dml 影响、为什么提高查询性能 )b-tree index/bitmap index/function index/patitional index(local/global) 索 引 通 常 能 提

5、高 select/update/delete 的性能，会降低 insert 的速度，8:使用索引查询一定能提高查询的性能吗?为什么索引就是为了提高查询性能而存在的 ， 如果在查询中索引没有提高性能 ， 只能说是用错了索 引， 或者讲是场合不同9:绑定变量是什么 ?绑定变量有什么优缺点?绑定变量是相对文本变量来讲的，所谓文本变量是指在SQL 直接书写查询条件，这样的SQL 在不同条件下需要反复解析 ，绑定变量是指使用变量来代替直接书写条件，查询bindvalue 在运行时传递， 然后绑定执行。 优点是减少硬解析 ，降低 CPU 的争用 ，节省 shared_pool ; 缺点是不能使用 hist

6、ogram，sql 优化比较困难10:如何稳定 (固定 )执行计划程序代码 query_rewrite_enabled = true star_transformation_enabled = true optimizer_features_enable = 9.2.0 创建并使用 stored outline oracle can automatically create outlines for all SQL statements, or you can create them for specific SQL statements. In either case, the outlin

7、es derive their input from the optimizer. oracle creates stored outlines automatically when you set the initialization parameter Create_STORED_OUTLINES to true. When activated, oracle creates outlines for all compiled SQL statements. You can create stored outlines for specific statements using the C

8、reate OUTLINE statement. Creating Outlines ：11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么Oracle 8i 中 sort_area_size/sort_area_retained_size决定了排序所需要的内存如果排序操作不能在sort_area_size中完成，就会用到temp表空间Oracle 9i 中如果 workarea_size_policy=auto 时 ,排序在pga内进行，通常pga_aggregate_target的1/20可以用来进行 disk sort;女口果 workarea_size_policy=

9、manual 时,排序需要的内存由sort_area_size 决定在执行order by/group by/distinct/union/create index/index rebuild/minus 等操作时 ,如果在pga或sort_area_size中不能完成，排序将在临时表空间进行(disk sort),临时表空间主要作用就是完成系统中的 disk sort.12:存在表T(a,b,c,d)，要根据字段c排序后取第21 30条记录显示，请给出 sql 程序代码create table t(a number(,b number(,c number(,d number();/begin

10、 for i in 1 . 300 loop insert intot values(mod(i,2),i/2,dbms_random.value(1,300),i/4);end loop;end;/select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;/select * from (select * from test order by c desc) x where rownum < 30minusselect * from

11、 (select * from test order by c desc) y where rownum < 20 order by 3 desc相比之 minus 性能较差DBA知识接下面 (2008-03-14 15:32:56)SQL tuning 类1: 列举几种表连接方式答:nested loop 、Sort-merge join 、hash join2: 不借助第三方工具，怎样查看 sql 的执行计划答:autotrace /utlxpla n. sql10046 event/sqltrace3:如何使用CBO,CBO与RULE的区别答: 在初始化参数里面设置optimize

12、r_mode=choose/all_rows/first_row等可以使用 cbo.rbo 会选择不合适的索引 ,cbo 需要统计信息。4: 如何定位重要 ( 消耗资源多 )的 SQL答: 根据 v$sqlarea 中的逻辑读 /disk_read 。5: 如何跟踪某个 session 的 SQL答: 先找出对应的 "sid,serial", 然后调用 system_system.set_sql_trace_in_session(sid,serial,true);6:SQL 调整最关注的是什么答: 逻辑读。7: 说说你对索引的认识(索引的结构、对 dml 影响、对查询影响、

13、为什么提高查询性能) 答: 默认的索引是 b-tree.对 insert 的影响 .( 分裂，要保证 tree 的平衡 )对 delete 的影响 .( 删除行的时候要标记改节点为删除 ).对 update 的影响，如果更新表中的索引字段，则要相应的更新索引中的键值。 查询中包含索引字段的键值和行的物理地址。8: 使用索引查询一定能提高查询的性能吗？为什么答: 不一定。如果返回的行数目较大， 或者聚簇因子 (clusting_factor) 大小接近于行的数量 使用全表扫描的性能较好。9: 绑定变量是什么？绑定变量有什么优缺点？答: 通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少

14、只有变量值不同的 语句的解析。10: 如何稳定 ( 固定 ) 执行计划答: 使用 stored outline.11: 和排序相关的内存在 8i 和 9i 分别怎样调整，临时表空间的作用是什么答:8i :使用sort_area_size , hash_area_size,每个session 分配相同的值，不管有无使 用。9i: 使用 pga_aggregate 来统一管理。临时表空间的作用 :在 sort_area_size 中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临 时表等都要用到。还有 hash join 不能完成的也在临时表空间中做。12: 存在表 T(a,b,c,d

15、), 要根据字段 c 排序后取第 2130 条记录显示，请给出sqlselect a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30minusselect a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20;二:数据库基本概念类1ctused and pctfree 表示什么含义有什么作用答: 表示数据块什么时候重联接或者从freelist 中删除。pctused: 如果数据块的使用小于 pctused 的值，则该数据块重新加

16、入到 fresslist 中。 pctused ：如果数据块的空闲空间小于 pctfree 的值，则该数据块从 freelist 中删除。2: 简单描述 table / segment / extent / block 之间的关系答:table 是一个逻辑上的概念。segment 表示结构的相同的一段空间。extent。多个 block 组成一个 extent，便于dbms分配。block ，多个 os 块组成一个 block ，是 oracle i/o 的单位。3: 描述 tablespace 和 datafile 之间的关系答: tablespace 是逻辑上的概念， datafile 是

17、物理上的概念。一个 tablespace 可以由多个 datafile 组成，一个 datafile 不能跨越多个 tablespace 。4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点答: 一个使用 freelist 管理，一个使用位图管理。5: 回滚段的作用是什么答：保存数据的前像，保证数据读取的时间点一致性。6: 日志的作用是什么答：纪录对数据库的操作，维护数据完整性及数据恢复。7:SGA 主要有那些部分，主要作用是什么答： db_cache（ 缓存数据块 ）,shared_pool（ 缓存 sql, 执行计划，数据字典信息等）,large_pool（rman 或者shar

18、ed server 、并行查询要用到）,java pool（java 程序时要用 到）8：oracle 系统进程主要有哪些，作用是什么答：smon （合并空间，实例恢复）,pmon （清理失败的进程），归档进程（负责在日志切换的 时候归档日志文件） ,lgmr （日志书写器进程 , 负责写日志咯） ,ckpt（ 检查点进程， 触发检查 点 ）,dbwr（ 数据库写入器，负责把数据写入导 datafile）三：备份恢复类1: 备份如何分类答：逻辑备份 （exp） 与物理备份。或者冷备份与热备份。2: 归档是什么含义答：在日志切换时， 旧的日志组被覆盖写入前， 把日志文件内容备份另一个地方， 以备

19、将来 恢复。3:如果一个表在 2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复 答：拷贝备份，recove database until time 2004-08-04 10:30:00 alter database open resetlogs;4:rman 是什么，有何特点答： rman 叫恢复管理器 .特点很多： 1. 热备份。2. 可以存储脚本。3. 可以增量备份。4. 自动管理备份集。5. 可实现块级备份。6. 自动检测坏块5.standby 的特点 答：利用传输重做日志流来达到同步的目的。6:对于一个要求恢复时间比较短的系统(数据库50G，每天归档5G),你如何设计备份策略答：