1. 查看具體語句的執行計劃及消耗的內存/CPU量
當前兩張表中各有數據1508875條
原始語句為：
SELECT/*+rule*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ, ZP.ZP
FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP
WHEREJB.RYID = ZP.RYID
AND ( JB.RYID>= 1 AND JB.RYID < 10001)
采用以下方式可以獲得該語句的執行計劃：
執行計劃
----------------------------------------------------------
Plan hashvalue: 2567408823
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB |
|* 4 | SORT JOIN | |
|* 5 | TABLE ACCESS FULL| HZCZRK_JBXXB |
--------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 -access("JB"."RYID"="ZP"."RYID")
filter("JB"."RYID"="ZP"."RYID")
5 -filter("JB"."RYID"<10001 AND"JB"."RYID">=1)
Note
-----
- rule based optimizer used (consider usingcbo)
統計信息
----------------------------------------------------------
2 recursive calls
1 db block gets
544 consistent gets
0 physical reads
176 redo size
11999 bytes sent via SQL*Net to client
811 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
79 rows processed
如果不設置總行數，則執行計劃是：
執行計劃
----------------------------------------------------------
Plan hashvalue: 2567408823
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| HZCZRK_JBXXB |
--------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 - access("JB"."RYID"="ZP"."RYID")
filter("JB"."RYID"="ZP"."RYID")
Note
-----
- rule based optimizer used (consider usingcbo)
統計信息
----------------------------------------------------------
2 recursive calls
1 db block gets
1103 consistent gets
0 physical reads
176 redo size
23915 bytes sent via SQL*Net to client
866 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts(disk)
164 rows processed
這裡我們主要需要關注兩個點：consistent gets代表內存消耗，physical reads代表磁盤I/O消耗，單位都是數據塊(DB_BLOCK_SIZE)。
從上面的執行計劃可以看出表之間的連接關系基本上就是采用排序合並連接技術，所以下面對連接技術做出一些說明
適合於大批量數據處理的連接技術只有兩種：
1． 排序合並連接(Sort/Merge)技術
兩個表先按連接字段進行排序，再將兩個表的排序結果進行順序匹配，將合並結果返回給客戶。
2． 哈希連接(HASH)技術
A hashjoin is executed as follows:
Bothtables are split into as many partitions as required, using a full table scan.
For eachpartition pair, a hash table is built in memory on the smallest partition.
The otherpartition is used to probe the hash table.
兩種技術都適合於大表與大表的查詢，而且通常情況下，HASH優於Merge，更優於嵌套循環(Nested_Loop)連接技術，尤其是當HASH與Oracle並行處理技術相結合的情況下，將極大地提高系統的整體吞吐量。
2. 對兩張表做一個分析
由於從Oracle10G開始已經不采用基於規則優先的算法，而是交由RBO來進行，所以需要在優化前對表進行分析
execute dbms_stats.gather_table_stats(ownname =>'SYSTEM',tabname => 'HZCZRK_JBXXB' ,estimate_percent => null ,method_opt=> 'for all indexed columns' ,cascade => true);
分析結果可以在這裡看：
