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