create table test
(it int);
insert into test
values(10);
commit;
create or replace procedure do_soft_parse(p_idx in number)
is
v_value number;
v_cursor sys_refcursor;
begin
execute immediate 'alter session set session_cached_cursor=0';
for idx in 1..100000 loop
open v_cursor for 'select 1 from test test'||p_idx||' where rownum=1';
fetch v_cursor into v_value;
close v_cursor;
end loop;
end;
alter system flush shared_pool;
同时执行大量的软解析。
var job_no number;
begin
for idx in 1..49 loop
dbms_job.submit(:job_no,'do_soft_parse('||idx||');');
commit;
end loop;
end;
本会话执行
execute do_soft_parse(50);
测试使用9I latch free 出现但是9I中的LATCH FREE P2可以对应v$latch 中的LATCH#找到是LIBRARY CACHE，
使用session_cached_cursors参数后可以发现library 有明显减少大约1半
使用前
SQL> select * from v$session_event a,(select SID from v$mystat where rownum
2 where a.SID=b.sid order by TIME_WAITED desc;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO SID
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ----------
54 SQL*Net message from client 19 0 8167 430 8062 81667520 54
54 latch free 237 0 4700 20 63 47001876 54
54 SQL*Net message to client 19 0 0 0 0 44 54
SQL>
SQL> select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like '%parse%') and a.VALUE0 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
54 parse time cpu 8
54 parse time elapsed 3898
54 parse count (total) 100018
54 parse count (hard) 5
使用后
SQL>
SQL> select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum
阅读(1873) | 评论(0) | 转发(0) |
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like '%parse%') and a.VALUE0 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
51 parse time cpu 3
51 parse time elapsed 1977
51 parse count (total) 100012
51 parse count (hard) 4
SQL>
SQL> select * from v$session_event a,(select SID from v$mystat where rownum
2 where a.SID=b.sid order by TIME_WAITED desc;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO SID
---------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ----------
51 SQL*Net message from client 23 0 6620 288 6143 66195328 51
51 latch free 144 0 2993 21 47 29926551 51
51 SQL*Net message to client 23 0 0 0 0 54 51
