1.理解parse
There are two types of parses (well, actually "three" these days).
They are
o 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).
o 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)
o 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.
--------