分类目录归档：Oracle性能优化

Nested loop join

步骤：确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）。需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

cost   = outer access cost + (inner access cost * outer cardinality)


| 2 | NESTED LOOPS             |              |     3 | 141 |     7   (15)|
| 3 | TABLE ACCESS FULL       | EMPLOYEES |     3 | 60 |     4   (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS       | 19 | 513 |     2   (50)|
| 5 |     INDEX UNIQUE SCAN       | JOB_ID_PK |     1 |    |          |
EMPLOYEES为outer table, JOBS为inner table.

Hash join

步骤：将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost
--------------------------------------------------------------------------
| Id   | Operation          |   Name        | Rows   | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |              | 665 | 13300 |     8   (25)|
| 1 |   HASH JOIN           |              | 665 | 13300 |     8   (25)|
| 2 |     TABLE ACCESS FULL   | ORDERS    | 105 | 840 |     4   (25)|
| 3 |     TABLE ACCESS FULL   | ORDER_ITEMS   | 665 |   7980 |     4   (25)|
--------------------------------------------------------------------------
ORDERS为HASH TABLE，ORDER_ITEMS扫描

Sort merge join

步骤：将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式：

1.RBO模式

2.不等价关联（>,<,>=,<=,<>）

3.HASH_JOIN_ENABLED=false

4.数据源已排序

cost = (outer access cost * # of hash partitions) + inner access cost

1、设置sql跟踪

--系统级别
alter system set sql_trace=true;
alter system set sql_trace=false;
--会话级别
alter session set sql_trace=true;
alter session set sql_trace=false;
--其他会话
exec sys.dbms_system.set_sql_trace_in_session(16737 , 39196 , true);
exec sys.dbms_system.set_sql_trace_in_session(16737 , 39196 , false);

2、执行相关sql语句
需要跟踪的sql

3、查询trace 文件

select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from 
(select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
(select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,
(select value from v$parameter where name = 'user_dump_dest') d

4、使用TKPROF命令

tkprof trace_file_name out_file explain=username/password
TKPROF DLSUN12_JANE_FG_SVRMGR_007.TRC OUTPUTA.PRF
EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A
INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU)

5、排序选项说明
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor