三、表关联顺序的HINT
1、leading hint
在多表关联中，这个hint指定由哪个表作为驱动表，告诉优化器先访问哪个表的数据。
SQL> create table t1 as select 1 id,object_name name from dba_objects;
Table created.
SQL> create index ind_t1 on t1(id,name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> drop table t;
Table dropped.
SQL> create table t as select object_id id,object_name name from dba_objects;
Table created.
SQL> create index ind_t on t(id,name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
要求先访问T1，再访问T
SQL> select /*+ leading(t1,t) */ t.* from t,t1 where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1444793974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 116 (5)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 31 | 116 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T1 | 50604 | 148K| 55 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 50604 | 1383K| 59 (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T"."ID")
要求先访问T，再访问T1
SQL> select /*+ leading(t,t1) */ t.* from t,t1 where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2914261090
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | | 249 (3)| 00:00:03 |
|* 1 | HASH JOIN | | 1 | 31 | 1984K| 249 (3)| 00:00:03 |
| 2 | TABLE ACCESS FULL| T | 50604 | 1383K| | 59 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 50604 | 148K| | 55 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T"."ID")
为什么先访问T1的代价低？
因为T1表的ID列重复率比较高，意味着生成的哈西簇会比较少，
而hash join这个过程的代价主要是耗在生成hash簇的过程，
意味着只要哈西簇少代价就会降低；
T表的ID列重复率是很低的，生成的hash簇会比较多，此时代价自然比先访问T1要高。
结论——hash join的性能主要取决于哈西簇的生成。
2、orderd hint
告诉优化器按照from后面的顺序来选择驱动表，oracle建议用leading，比较灵活。
SQL> select /*+ ordered */ t.* from t,t1 where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2914261090
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | | 249 (3)| 00:00:03 |
|* 1 | HASH JOIN | | 1 | 31 | 1984K| 249 (3)| 00:00:03 |
| 2 | TABLE ACCESS FULL| T | 50604 | 1383K| | 59 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 50604 | 148K| | 55 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T"."ID")
SQL> select /*+ ordered */ t.* from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1444793974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 116 (5)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 31 | 116 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T1 | 50604 | 148K| 55 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 50604 | 1383K| 59 (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T"."ID")
四、表关联方式的hint
/*+ use_nl(t t1) */
/*+ use_hash(t t1) */
/*+ use_merge(t t1) */
当使用很多hint的时候
SQL> select /*+ leading(t,t1) index(t1 ind_t1) index(t ind_t) use_nl(t t1) */ t.* from t,t1 where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2389647923
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 50949 (1)| 00:10:12 |
| 1 | NESTED LOOPS | | 1 | 31 | 50949 (1)| 00:10:12 |
| 2 | INDEX FULL SCAN | IND_T | 50604 | 1383K| 286 (1)| 00:00:04 |
|* 3 | INDEX RANGE SCAN| IND_T1 | 1 | 3 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ID"="T"."ID")
研究一下三种连接方式的sql trace
SQL> drop table t1;
Table dropped.
SQL> create table t1 as select * from t;
Table created.
SQL> create index ind_t1 on t1(id,name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> set autotrace off
SQL> alter session set sql_trace=true;
Session altered.
SQL> select /*+ use_nl(t1 t) */ count(1) from t1,t where t1.id=t.id;
COUNT(1)
----------
50604
SQL> select /*+ use_hash(t1 t) */ count(1) from t1,t where t1.id=t.id;
COUNT(1)
----------
50604
SQL> select /*+ use_merge(t1 t) */ count(1) from t1,t where t1.id=t.id;
COUNT(1)
----------
50604
SQL> alter session set sql_trace=false;
Session altered.
[oracle@oracle253 udump]$ tkprof law_ora_7171.trc law_ora_7171_text.txt
TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 27 16:34:57 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
*******************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select /*+ use_nl(t1 t) */ count(1)
from
t1,t where t1.id=t.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.22 0.23 0 51131 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.22 0.23 0 51131 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=51131 pr=0 pw=0 time=234288 us)
50604 NESTED LOOPS (cr=51131 pr=0 pw=0 time=253094 us)
50604 TABLE ACCESS FULL T1 (cr=243 pr=0 pw=0 time=48 us)
50604 INDEX RANGE SCAN IND_T (cr=50888 pr=0 pw=0 time=174963 us)(object id 52948)
********************************************************************************
select /*+ use_hash(t1 t) */ count(1)
from
t1,t where t1.id=t.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.05 0 486 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.05 0 486 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=486 pr=0 pw=0 time=55377 us)
50604 HASH JOIN (cr=486 pr=0 pw=0 time=131253 us)
50604 TABLE ACCESS FULL T1 (cr=243 pr=0 pw=0 time=47 us)
50604 TABLE ACCESS FULL T (cr=243 pr=0 pw=0 time=33 us)
********************************************************************************
select /*+ use_merge(t1 t) */ count(1)
from
t1,t where t1.id=t.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.16 0.17 0 486 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.16 0.17 0 486 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=486 pr=0 pw=0 time=175407 us)
50604 MERGE JOIN (cr=486 pr=0 pw=0 time=195280 us)
50604 SORT JOIN (cr=243 pr=0 pw=0 time=29292 us)
50604 TABLE ACCESS FULL T1 (cr=243 pr=0 pw=0 time=54 us)
50604 SORT JOIN (cr=243 pr=0 pw=0 time=101547 us)
50604 TABLE ACCESS FULL T (cr=243 pr=0 pw=0 time=48 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 6 0.43 0.46 0 52103 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.43 0.47 0 52103 0 3
Misses in library cache during parse: 3
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
********************************************************************************
比较一致性读：
NL方式——51131
HASH方式——486
MERGE方式——486
一致性读一样但是执行计划不一样
SQL> set autotrace trace exp
SQL> select /*+ use_hash(t1 t) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 949044725
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 119 (5)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | HASH JOIN | | 50604 | 494K| 119 (5)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T1 | 50604 | 247K| 58 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 50604 | 247K| 58 (2)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T"."ID")
SQL> select /*+ use_merge(t1 t) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 712353386
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | | 442 (4)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 10 | | | |
| 2 | MERGE JOIN | | 50604 | 494K| | 442 (4)| 00:00:06 |
| 3 | SORT JOIN | | 50604 | 247K| 1208K| 221 (4)| 00:00:03 |
| 4 | TABLE ACCESS FULL| T1 | 50604 | 247K| | 58 (2)| 00:00:01 |
|* 5 | SORT JOIN | | 50604 | 247K| 1208K| 221 (4)| 00:00:03 |
| 6 | TABLE ACCESS FULL| T | 50604 | 247K| | 58 (2)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T"."ID")
filter("T1"."ID"="T"."ID")
选择cost较小的hash join方式。
结论：大表间的访问，HASH方式性能更高，NL最差，当驱动表是小表的时候，NL的性能才会体现出来。
改造表，将T表变为小表
SQL> truncate table t;
Table truncated.
SQL> insert into t select object_id,object_name from dba_objects where rownum<10;
9 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
SQL> select count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2183851067
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 9 | 72 | 10 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T | 9 | 27 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IND_T1 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T"."ID")
强制hash join
SQL> select /*+ use_hash(t1 t) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 596805104
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 61 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN | | 9 | 72 | 61 (5)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T | 9 | 27 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 50604 | 247K| 58 (2)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T"."ID")
禁用连接方式
A 禁用使用NL，会在剩下的两种连接方式中选择次优的。
SQL> select /*+ no_use_nl(t1 t) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 596805104
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 61 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN | | 9 | 72 | 61 (5)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T | 9 | 27 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 50604 | 247K| 58 (2)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T"."ID")
B 禁止使用hash join
/*+ no_use_hash(t1 t) */
C 禁止使用merge join
/*+ no_use_merge(t1 t) */
五、与并行相关的hint
SQL> select /*+ parallel(t1 4) parallel(t 3) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 3903087583
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 5 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS | | 9 | 72 | 5 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T | 9 | 27 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | IND_T1 | 1 | 5 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("T1"."ID"="T"."ID")
PX出现表示用了并行的方式。
SQL> conn /as sysdba
Connected.
SQL> select table_name,degree from dba_tables where owner='SCOTT' and table_name like 'T%';
TABLE_NAME DEGREE
------------------------------------------------------------------------------
T 1
T1 1
默认的并行度
SQL> conn scott/scott
Connected.
SQL> alter table t parallel 3;
Table altered.
SQL> alter table t1 parallel 4;
Table altered.
SQL> select table_name,degree from dba_tables where owner='SCOTT' and table_name like 'T%';
TABLE_NAME DEGREE
------------------------------------------------------------------------------
T 3
T1 4
SQL> select count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 3903087583
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 5 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS | | 9 | 72 | 5 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T | 9 | 27 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | IND_T1 | 1 | 5 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("T1"."ID"="T"."ID")
禁止并行
SQL> select /*+ no_parallel(t1) no_parallel(t) */ count(1) from t1,t where t1.id=t.id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2183851067
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | NESTED LOOPS | | 9 | 72 | 10 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T | 9 | 27 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IND_T1 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T"."ID")
六、动态采样
/*+ dynamic_sampling(t 4) */
七、其他的
cache hint
在全表扫描的操作中，如果使用这个提示，oracle会将扫到的数据块放在LRU链表最被使用端，
这样的话，数据块可以长时间驻留内存。
场合：如果一个经常被访问的小表，这个设置可以提高查询的性能。
SQL> select /*+ full(emp) cache(emp) */ * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
与KEEP池的区别，这个hint是好用db buffer的默认池大小，
keep池是另外的区域，长时间驻留在内存中KEEP池区域中。
区别在于在内存中的位置不一样。
相同点——效果是一样的，为了放到内存，提高缓存的命中率。
