数据库笔试题及答案

阿里巴巴的Oracle DBA笔试题参考答案- SQL tuning类

SQL tuning类参考解答：

一：SQL tuning 类

1:列举几种表连接方式

程序代码

hash join/merge join/nest loop(cluster join)/index join

2:不借助第三方工具，怎样查看sql的执行计划

程序代码

set autot on

explain plan set statement_id = &item_id for &sql;

select * from table(dbms_xplan.display);

https://www.360docs.net/doc/7117104923.html,/ ... /b10752/ex_plan.htm

3:如何使用CBO,CBO与RULE的区别

在optimizer_mode=choose时,如果表有统计信息(分区表外),优化器将选择CBO,否则选RBO。RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目, 然后选择最佳级别(最少的数量)的执行路径来运行查询。

CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同的执行计划的成本，并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。

4:如何定位重要(消耗资源多)的SQL

程序代码

select sql_text

from v$sql

where 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_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) 索引通常能提高select/update/delete的性能,会降低insert的速度,

8:使用索引查询一定能提高查询的性能吗?为什么

索引就是为了提高查询性能而存在的, 如果在查询中索引没有提高性能, 只能说是用错了索引,或者讲是场合不同

9:绑定变量是什么?绑定变量有什么优缺点?

绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件，这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，查询bind value在运行时传递，然后绑定执行。优点是减少硬解析,降低CPU的争用,节省shared_pool ;缺点是不能使用histogram,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 outlines 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. Y ou can create stored outlines for specific statements using the Create 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=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

for i in 1 .. 300 loop

insert into

t 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 < 30

minus

select * from (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 join

2:不借助第三方工具，怎样查看sql的执行计划

答:autotrace /utlxplan.sql

10046 event/sqltrace

3:如何使用CBO,CBO与RULE的区别

答:在初始化参数里面设置optimizer_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影响、对查询影响、为什么提高查询性能）答:默认的索引是b-tree.

对insert的影响.(分裂，要保证tree的平衡)

对delete的影响.(删除行的时候要标记改节点为删除).

对update的影响，如果更新表中的索引字段，则要相应的更新索引中的键值。

查询中包含索引字段的键值和行的物理地址。

8:使用索引查询一定能提高查询的性能吗？为什么

答:不一定。如果返回的行数目较大，或者聚簇因子(clusting_factor)大小接近于行的数量,使用全表扫描的性能较好。

9:绑定变量是什么？绑定变量有什么优缺点？

答:通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的语句的解析。

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),要根据字段c排序后取第21—30条记录显示，请给出sql

select a,b,c,d from (select a,b,c,d from T order by c) where rownum<=30

minus

select a,b,c,d from (select a,b,c,d from T order by c) where rownum <=20; 二：数据库基本概念类

1ctused and pctfree 表示什么含义有什么作用

答:表示数据块什么时候重联接或者从freelist中删除。

pctused:如果数据块的使用小于pctused的值，则该数据块重新加入到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是物理上的概念。

一个tablespace可以由多个datafile组成，一个datafile不能跨越多个tablespace。4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点

答:一个使用freelist管理，一个使用位图管理。

5:回滚段的作用是什么

答：保存数据的前像，保证数据读取的时间点一致性。

6:日志的作用是什么

答：纪录对数据库的操作，维护数据完整性及数据恢复。

7:SGA主要有那些部分，主要作用是什么

答：db_cache(缓存数据块),shared_pool(缓存sql,执行计划，数据字典信息

等),large_pool(rman或者shared server、并行查询要用到),java pool(java程序时要用到)

8：oracle系统进程主要有哪些，作用是什么

答：smon（合并空间，实例恢复）,pmon（清理失败的进程）,归档进程（负责在日志切换的时候归档日志文件）,lgmr（日志书写器进程,负责写日志咯）,ckpt(检查点进程，触发检查点),dbwr(数据库写入器，负责把数据写入导datafile)

三：备份恢复类

1:备份如何分类

答：逻辑备份(exp)与物理备份。或者冷备份与热备份。

2:归档是什么含义

答：在日志切换时，旧的日志组被覆盖写入前，把日志文件内容备份另一个地方，以备将来恢复。

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)，你如何设计备份策略答：每天一个全备份。

四：系统管理类

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

答:1、我常用的方法，先在系统级使用top察看何种资源占用严重，然后酌情查询动态性能视图

v$session_wait,v$system_event,v$sysstat,v$sesstat,v$waitstat,v$lock,v$sort_usag e等。针对主要的等待事件做相应的处理。如10046 event做sql追踪等。

2、做一个statspack，根据top 5,system load,top sql等来做相应的调整。

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

答: iostat -x 1 5;

top/vmstat/glance/sar

3:对statspack有何认识

答: 一个性能诊断工具而已咯，其本质就是在两个时间点采样两个系统数据。(动态性能视图)

然后根据两个snapshot，产生一个报告。

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

答: 1:增大sort_area_size(8i)/pga_aggregate_target(9i)值。

2:用并行的方式来建。 3:系统空闲的时候建。

5:对raid10 和raid5有何认识

答:raid5耗费少量磁盘实现冗余，但因为奇偶校验而写入慢。raid10通过镜像来保证冗余，速度快但耗费的磁盘容量大。