总结一下这半个来月面试遇到的笔试题和面试题,希望能给要面试的同学们一点帮助!废话不多说,开整.
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);
未完待续.......