等待模擬-library cache 軟解析
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<=1) b
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<=1) c
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like '%parse%') and a.VALUE<>0 ;
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<=1) c
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like '%parse%') and a.VALUE<>0 ;
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<=1) b
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-764950/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 等待模擬-library cache shared pool 硬解析
- library cache pin等待事件的模擬事件
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 等待模擬-cache buffer chainAI
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 等待事件事件
- latch:library cache lock等待事件事件
- 定位Library Cache pin,Library Cache lock等待的解決方法
- 解決library cache pin等待事件事件
- enq:Library cache lock/pin等待事件ENQ事件
- 查詢Library Cache Pin等待原因
- 模擬library cahe lock/pin等待事件以及問題定位事件
- 分析解決因”library cache pin”等待
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- zt_library cache pin和lock等待分析
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 俺也談談 library cache lock 等待事件事件
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- oracle library cache相關的等待事件及latchOracle事件
- 共享池之八:軟解析、硬解析、軟軟解析 詳解一條SQL在library cache中解析涉及的鎖SQL
- library cache lock和cursor: pin S wait on X等待AI
- 轉貼_Oradebug hanganalyze分析library cache等待
- 【效能調整】等待事件(八) library cache locks and pins事件
- 'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'型別的等待事件Mutex型別事件
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- 等待模擬-read by other sessionSession
- 等待模擬-BUFFER BUSY WAITAI
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- 11.2資料庫登入出現library cache lock等待(二)資料庫
- 11.2資料庫登入出現library cache lock等待(一)資料庫
- 【EM】鎖等待故障模擬及排查
- read by other session等待事件模擬Session事件