Oracle性能调优自己总结的18条经验

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开始已经不采