Oracle SQL no parse

parsing is something controlled by your programmers.Oracle, when told to parse, will (must, cannot deny you) parse.  The parse will be one of three kinds:a) hard (bad)b) soft (bad)c) softer soft (still bad)the only good parse is NO parse -- and your application developers totally control that!以上文字摘自http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30433844824883

 

 

怎样才能达到NO parse?

 

不解析：直接复用已经打开过的游标。打开游标后多次执行，或者存储过程中的静态游标的多次执行，都不需要解析。

我们用实验来验证一下：

********************************************************************************

beginfor i in 5..10 loopinsert into a1 values(i,'abc');end loop;commit;end;

call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.02          0          0          0           0Execute      1      0.00       0.04          0          5          0           1Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.00       0.06          0          5          0           1

Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 58 

Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  SQL*Net message from client                     2        0.00          0.00  log file sync                                   1        0.00          0.00********************************************************************************

INSERT INTO A1 VALUES(:B1 ,'abc')

call         count       cpu    elapsed       disk      query    current      rows-------      ------    --------    ----------   ---------- ----------  ----------  ----------Parse        1        0.00       0.00            0            0            0             0Execute     6       0.01        0.03            2            5           10            6Fetch         0       0.00        0.00            0            0            0             0-------      ------    -------- ----------     ----------   ---------- ----------  ----------total          7        0.01       0.03            2             5           10           6

Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 58     (recursive depth: 1)

Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  db file sequential read                         2        0.01          0.01********************************************************************************以上来自tkprof文件。只有执行了6次，只parse 1次。