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?

 

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

我们用实验来验证一下：

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

begin
for i in 5..10 loop
insert 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           0
Execute      1      0.00       0.04          0          5          0           1
Fetch        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: 1
Optimizer mode: ALL_ROWS
Parsing 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             0
Execute     6       0.01        0.03            2            5           10            6
Fetch         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: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing 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次。