SQL tuning 类 的面试题

1.      列举几种表连接方式

内连接、外连接（左外连接，右外连接，全连接），等值链接，非等值链接，自连接

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

1. 使用explain plan，查看plan_table

Explain plan

Setstatement_id=’wlp_test’ ---其中wlp_test自定义的名称，方便后面查看plan_table

For

Select * fromemp;

select id,operation,options,object_name,object_typefrom plan_table where statement_id=’wlp_test’;

2. 使用set autotrace on
3. select * from table(dbms_xplan.display);

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

如果optimizer_mode=choose（9i的默认值）时，如果有统计信息就使用CBO，没有就使用RBO。All_rows是10g以及后续版本的默认值，它表示使用CBO来解析目标sql；

区别：

RBO使用简单的分级规则来选择执行计划，而CBO则根据最低成本来选择执行计划。RBO一旦执行计划出了问题很难对其做调整。甚至sql的写法和sql中对象出现的先后顺序都能影响执行计划的选择。

Oracle 10gR2及以后版本不再支持基于规则（RULE）的优化器。

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

Select sql_textfrom v$sql where disk_reads > 1000 or executions >0 andbuffer_gets/executions >30000;

5.      如何跟踪某个session的SQL

selectsid,serial# from v$session where sid = (select sid from v$mystat where rownum =1);

execdbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);

execdbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

6.      SQL调整最关注的是什么

查看该sql的响应时间，即responsetime（db blockgets/consistent gets/physical reads/sorts(disk);

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

使用分为B树索引，位图索引，复合索引，哈希索引，位图连接索引，分区索引，反键索引，基于函数的索引。索引通常会提高select/update/delete的性能，降低insert的性能。因为查询通过索引时，会先通过索引查找需要行的rowid，让后通过rowid把需要的块读入内存，而全表扫描则是把所有的行都读入内存，然后再查找需要的行，所以索引可以提高查询性能。

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

不一定，但是索引就是为了提高性能而创建的，如果查询中索引没有提高性能，只能说用错了索引，或者场合不对。

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

绑定变量是指在sql文本中使用的变量，改变变量的值来得到不同的查询结果。优点：使用绑定变量来减少硬解析，避免cpu争用，降低磁盘读，避免sharedpool latch的争用。绑定变量的缺点就是，不能使用histogram（直方图）。

10.  如何稳定(固定)执行计划

A．使用sql profile来稳定执行计划(10g以及以前版本)

B．  使用SPM来稳定执行计划（11g以及以后版本）

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/groupby/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

a.Select * from(Select t1.*,rownum rn from (Select * from T order by c) t1) where rn between21 and 30;

b.select * from(select * from T order by c) where rownum<31

minus

 select * from (select * from T order by c)where rownum<20;