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)。

从上面的执行计划可以看出表之间的连接关系基本上就是采用排序合并连接技术，所以下面对连接技术做出一些说明

适合于大批量数据处理的连接技