1，创建2个sequence.
create sequence seq_0;
create sequence seq_1;

2.创建过程，循环打开并关闭游标，模拟大量的parse。

create or replace procedure p( p_n in number )
as
        l_cursor sys_refcursor;
begin
        for i in 1 .. p_n
        loop
                open l_cursor for 'select seq_' || mod(i,2)||'.nextval from dual';
                close l_cursor;
        end loop;
end;
/

3.执行以下脚本parse.sh。

sqlplus '/as sysdba'<alter session set events '10046 trace name context forever,level 12';
exec p(200000);
quit;
EOF
exit

nohup sh parse.sh &
nohup sh parse.sh &
nohup sh parse.sh &

4.查看awr报告里可见

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                            186          34.2
latch: library cache                      6           1    116    0.1 Concurrenc
control file parallel write              67           1      8    0.1 System I/O
latch: library cache pin                  2           0    146    0.1 Concurrenc
log file parallel write                  24           0      6    0.0 System I/O

5。结论

过量软解析一样会导致latch: library cache等待。