Instance Efficiency Percentages (Target 100%)

http://asktom.oracle.com/pls/ask/f?p=4950:8:15133282895453016021::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:801662252143 

1.理解parse
There are two types of parses (well, actually “three” these days).

They are

• HARD parse — the query has never been seen before, isn’t in the shared pool. We must parse it, hash it, look in the shared pool for it, don’t find it,security check it, optimize it, etc (lots of work).
• SOFT parse — the query has been seen before, is in the shared poo.  We have to parse it, hash it, look in the shared pool for it and find it (less work then a hard parse but work none the less)
• A kinder, softer SOFT parse — you are using session_cached_cursors (search this site for that word for info).  We take your query, look in the sessions cursor cache — find the cursor and reuse it.  Very very little work.

So, after “startup”, you go into sqlplus and do this:

SQL> alter session set session_cached_cursors=0;         -------------- just making sure this is off
SQL> select * from emp;                                                           ---------------hard parse
SQL> select * from emp;                                                          ----------------soft parse
SQL> select * from emp;                                                          ----------------soft parse
SQL> alter session set session_cached_cursors=100;   ---------------- enable this feature
SQL> select * from emp;                                                          ----------------soft parse
SQL> select * from emp;                                                         -----------------kinder, gentler, soft parse

2.避免soft parse
how you avoid a soft parse varies by language.

In PLSQL — you get it for free.  If you have code that does:
  for i in 1 .. 100
  loop
        for x in ( selct * from t where zzz = i )
        loop
           …..

the query “select * from t where zzz = :BIND” is parsed ONCE per session — not per execution.
In Java — you would use a prepared/callable statement and prepare it ONCE per
session — not once per execution.

In VB — same, don’t be in such a hurry to close those statments, reuse them
over and over and over.
In SQLPlus — being a very generic tool — it canot cache open cursors for us
like that.  You can play with session_cached_cursors to achieve some caching by
the server. 

PLSQL being very very specific — can cache the (static) cursors for us. (but
not ref cursors)

3.在plsql中必须使用store procedure才能避免soft parse

Hi

 When you execute multiple times BEGIN END block with insert in we get :
INSERT INTO SIEBEL.S_ACCNT_POSTN  (
      CREATED,
      ASGN_MANL_FLG,
      LAST_UPD_BY,
      OU_EXT_ID,
      MODIFICATION_NUM,
      ROW_ID,
      CONFLICT_ID,
      ASGN_DNRM_FLG,
      ROW_STATUS,
      LAST_UPD,
      CREATED_BY,
      ASGN_SYS_FLG,
      POSITION_ID)
VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse       19      0.46       1.40          0          0          0           0
Execute     19      0.02       0.02          0         22        285          19
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total       38      0.48       1.42          0         22        285          19

Misses in library cache during parse: 0
Optimizer goal: RULE
It means 19 soft parsing of insert INSIDE plsql block…

                                      
Followup: 
Sorry — should have been more specific.

Use a stored procedure, only stored procedures can do this caching.  If you
execute:

begin
   insert into t …..
end;

it’ll parse the insert once for every execute.  OTOH, if you put the insert into
a procedure:

create or replace procedure p( … )
as
begin
   insert into ….;
end;
/

then the insert will be parsed upon the first execution of the procedure in your
session and not again.