Oracle 面试题（一）

原文：http://clocker321.blog.163.com/blog/static/63505772009715103012917/

 

修改了一部分描述不太详细的答案，有问题请留言。

一:SQL tuning 类

1.      列举几种表连接方式

相等连接（内连接）、非等连接、自连接、外连接（左、右、全）

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

 

 

相等连接： select * from a, b where a.id = b.id; 这个就属于内连接。

 

对于外连接：

Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN

 

LEFT OUTER JOIN：左外关联

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

等价于

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id=d.department_id(+)

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

 

RIGHT OUTER JOIN：右外关联

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id);

等价于

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+)=d.department_id

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

 

FULL OUTER JOIN：全外关联

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

 

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

    如果PLAN_TABLE表不存在，执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建plan_table表。

　　如果PLUSTRACE角色不存在，执行$ORACLE_HOME/sqlplus/admin/plustrce.sql

　　1.sqlplus / as sysdba

　　set autotrace on;

    执行SQL

     

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

  IF 初始化参数 OPTIMIZER_MODE = CHOOSE THEN  --(8I DEFAULT)

     IF 做过表分析

        THEN 优化器 Optimizer=CBO(COST);         

     ELSE

        优化器 Optimizer=RBO(RULE);               

     END IF;

  END IF;

 

  区别：

  RBO根据规则选择最佳执行路径来运行查询。

  CBO根据表统计找到最低成本的访问数据的方法确定执行计划。

  使用CBO需要注意：

  I)  需要经常对表进行ANALYZE命令进行分析统计;

  II) 需要稳定执行计划;

  III)需要使用提示(Hint);

  使用RULE需要注意：

I)  选择最有效率的表名顺序

II) 优化SQL的写法;

 

 

在optimizer_mode=choose时,如果表有统计信息（分区表外）,优化器将选择CBO,否则选RBO。 

RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目,然后选择最佳级别（最少的数量）的执行路径来运行查询。 

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

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

使用CPU多的用户session

SELECT a.SID, spid, status, SUBSTR (a.program, 1, 40) prog, a.terminal,a.SQL_ID, osuser, VALUE / 60 / 100 VALUE

FROM v$session a, v$process b, v$sesstat c

WHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addr

ORDER BY VALUE DESC;

 

select sql_text from v$sql 

where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

5.      如何跟踪某个session的SQL

利用TRACE 跟踪

  ALTER SESSION SET SQLTRACE ON;

  COLUMN SQL format a200;

  SELECT   machine, sql_text SQL

      FROM v$sqltext a, v$session b

     WHERE address = sql_address

       AND machine = '&A'

  ORDER BY hash_value, piece;

 

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,'');