Oracle Library Cache深入解析
案例1：业务运行前：
17:07:30 SYS@ prod>select name,GETS,MISSESfrom v$latch where upper(name) like '%LIBRARY%'OR upper(name) like '%SHARE%';
NAME GETS MISSES
---------------------------------------------------------------- ---------- ----------
test shared non-parent l0 0 0
ksxp shared latch 0 0
kcfis stats shared latch 0 0
shared pool 126676 61
library cache load lock 0 0
shared pool simulator 6576 0
shared pool sim alloc 45 0
Shared B-Tree 302 0
shared server configuration 6 0
shared server info 1 0
运行业务：
17:08:34 SCOTT@ prod>begin
17:08:38 2for i in 1..100000 loop
17:08:52 3execute immediate 'insert into t1 values ('||i||')';
17:09:18 4end loop;
17:09:26 5end;
17:09:27 6/
PL/SQL procedure successfully completed.
业务运行后：
17:11:05 SYS@ prod>select name,GETS,MISSESfrom v$latch where upper(name) like '%LIBRARY%'OR upper(name) like '%SHARE%'
NAME GETS MISSES
---------------------------------------------------------------- ---------- ----------
test shared non-parent l0 0 0
ksxp shared latch 0 0
kcfis stats shared latch 0 0
shared pool 4526672 214
library cache load lock 0 0
shared pool simulator 1086437 0
shared pool sim alloc 2048 0
Shared B-Tree 316 0
shared server configuration 6 0
shared server info 1 0
10 rows selected.
17:15:42 SYS@ prod>select sid,event,WAIT_TIME,state from v$session_wait where sid=42
SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
42 latch: shared pool -1 WAITED SHORT TIME
Elapsed: 00:00:00.08
案例2：
业务运行前：
17:18:35 SYS@ prod>select sid,EVENT,TOTAL_WAITS,AVERAGE_WAITfrom v$session_event where sid in (42,46);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
42 Disk file operations I/O 4 .03
42 log file switch (private strand flush incomplete) 1 10.03
42 log file sync 4 1.76
42 db file sequential read 385 .23
42 latch: row cache objects 5 .44
42 latch: shared pool 194 .25
42 SQL*Net message to client 24 0
42 SQL*Net message from client 23 5318.9
42 SQL*Net break/reset to client 2 .08
42 events in waitclass Other 1 0
46 Disk file operations I/O 1 .03
46 db file sequential read 33 .02
46 SQL*Net message to client 13 0
46 SQL*Net message from client 12 79.9
14 rows selected.
运行业务：
17:16:39 SYS@ prod>select sid ,username from v$session where username is not null;
SID USERNAME
---------- ------------------------------
1 SYS
42 SCOTT
46 HR
17:17:22 SCOTT@ prod>begin
17:20:46 2for i in 1..100000 loop
17:20:52 3execute immediate 'insert into t1 values ('||i||')';
17:20:58 4end loop;
17:21:02 5end;
17:21:05 6/
PL/SQL procedure successfully completed.
17:17:42 HR@ prod>begin
17:21:16 2for i in 1..100000 loop
17:21:24 3execute immediate 'insert into scott.t1 values ('||i||')';
17:21:49 4end loop;
17:21:51 5end;
17:21:52 6/
PL/SQL procedure successfully completed.
业务运行后：
17:22:32 SYS@ prod>select sid,EVENT,TOTAL_WAITS,AVERAGE_WAITfrom v$session_event where sid in (42,46);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
42 Disk file operations I/O 4 .03
42 latch: cache buffers chains 16 .18
42 buffer busy waits 2 .15
42 log file switch (private strand flush incomplete) 1 10.03
42 log file sync 4 1.76
42 db file sequential read 413 .21
42 latch: row cache objects 58 .13
42 latch: shared pool 1008 .19
42 library cache: mutex X 123 .33
42 SQL*Net message to client 24 0
42 SQL*Net message from client 24 6044.43
42 SQL*Net break/reset to client 2 .08
42 events in waitclass Other 87 .09
46 Disk file operations I/O 3 .03
46 latch: cache buffers chains 13 .21
46 buffer busy waits 1 .35
46 latch: redo copy 1 1.26
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
46 db file sequential read 38 .02
46 enq: HW - contention 1 .01
46 latch: row cache objects 58 .14
46 row cache lock 1 .08
46 latch: shared pool 666 .17
46 library cache: mutex X 99 .29
46 SQL*Net message to client 13 0
46 SQL*Net message from client 13 2010.63
46 events in waitclass Other 68 .14
26 rows selected.
Elapsed: 00:00:00.37
17:22:42 SYS@ prod>
17:22:02 SYS@ prod>select sid,event,WAIT_TIME,state from v$session_wait where sid=42
17:22:25 2 or sid=46;
SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
42 latch: shared pool -1 WAITED SHORT TIME
46 latch: shared pool -1 WAITED SHORT TIME
页:
[1]