通过案例学调优之--和SHAREDPOOL相关的主要Latch

通过案例学调优之--和SHAREDPOOL相关的主要Latch3.1、和SHAREDPOOL相关的主要Latch有:Latch:sharedpoolLatch:librarycache我们知道Oracle通过SHAREDPOOL来实现SQ

的相关信息, 如:

, 也是通过将不同的

的相 关

Library Cache LatchLibrary Cache SHRAE POOL 。 接下来就开始进行硬解析过程,将执行解析后的执行计划等信息记录到 Library Cache ,一 直到硬解析结束。(硬解析)

的话,根据上面的逻辑那说明数 据库中存在大量的硬解析,这个时候就要查找那些

10:56:01 SCOTT@ prod >show parameter cursor NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string similar cursor_space_for_time boolean FALSE open_cursors integer 300 session_cached_cursors integer 50 1、session1：以scott建立测试表 11:44:26 SYS@ prod >conn scott/tiger Connected. 11:01:41 SCOTT@ prod >select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 1 0 0 10:56:09 SCOTT@ prod >create table test as select rownum as col1 ,rownum col2 from user_objects 10:58:38 2 ; Table created. 2、建立测试表直方图 10:58:51 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'for columns col1 size 3'); PL/SQL procedure successfully completed. 10:59:36 SCOTT@ prod >select column_name,num_buckets,histogram from user_tab_col_statistics 11:00:43 2 where table_name='TEST'; COLUMN_NAME NUM_BUCKETS HISTOGRAM ------------------------------ ----------- --------------- COL1 3 HEIGHT BALANCED 11:01:35 sys@ prod >ALTER SYSTem flush shared_pool; System altered. 3、session 2:以scott建立另一个会话 11:03:44 SCOTT@ prod >select * from v$mystat where rownum=1; SID STATISTIC# VALUE ---------- ---------- ---------- 44 0 0 11:04:01 SCOTT@ prod >create table test1 as select rownum as col1 ,rownum col2 from user_objects; Table created. 11:04:36 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST1',method_opt=>'for columns col1 size 3'); PL/SQL procedure successfully completed. 11:05:04 SCOTT@ prod >select column_name,num_buckets,histogram from user_tab_col_statistics 11:05:19 2 where table_name='TEST1'; COLUMN_NAME NUM_BUCKETS HISTOGRAM ------------------------------ ----------- --------------- COL1 3 HEIGHT BALANCED 11:05:30 sys@ prod >ALTER SYSTem flush shared_pool; System altered. 4、在session 1执行以下操作 11:02:42 SCOTT@ prod >begin 11:06:28 2 for i in 1..50000 loop 11:06:40 3 execute immediate 'select * from test where col1='||i; 11:07:08 4 end loop; 11:07:11 5 end; 11:07:13 6 / 在session 2执行同样地操作 11:07:57 SCOTT@ prod >begin 11:08:01 2 for i in 1..50000 loop 11:08:01 3 execute immediate 'select * from test1 where col1='||i; 11:08:01 4 end loop; 11:08:01 5 end; 11:08:02 6 / 5、查看session event 11:11:36 sys@ prod > select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44) SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 latch: shared pool 537557404 address 293 number Elapsed: 00:00:00.00 11:11:38 sys@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 latch: row cache objects 828539960 address 270 number Elapsed: 00:00:00.00 11:11:39 sys@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 latch: shared pool 537557404 address 293 number Elapsed: 00:00:00.00 11:11:41 sys@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 latch: row cache objects 828007508 address 270 number Elapsed: 00:00:00.00 11:11:42 sys@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 latch: shared pool 537557404 address 293 number 11:12:32 sys@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch free 821793596 address 274 number 44 latch: shared pool 537557404 address 293 number sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44) SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 latch: shared pool 537557404 address 293 number 44 library cache: mutex X 1307903034 idn 65536 value 11:14:58 sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44) SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 library cache: mutex X 3413592168 idn 2883584 value 44 latch: row cache objects 828539960 address 270 number 11:15:18 sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44) SID EVENT P1 P1TEXT P2 P2TEXT ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ 1 SQL*Net message from client 1650815232 driver id 1 #bytes 44 SQL*Net message from client 1650815232 driver id 1 #bytes 从上面的过程可以看到,大量的硬解析将导致严重的 library cache latch（mutex） 和 shared pool latch竞争。 6、查看Library cache中sql情况 sys@ prod >select * 2 from (select sql_id,child_number,child_latch,executions,sql_text 3 from v$sql 4 where sql_text like '%select * from test1 where col1%' 5 and sql_text not like '%v$sql%' 6 and sql_text not like '%begin%' 7 order by child_number desc) 8* where rownum <10 SQL_ID CHILD_NUMBER CHILD_LATCH EXECUTIONS SQL_TEXT ------------- ------------ ----------- ---------- -------------------------------------------------- 6tsrjxza4gvur 1987 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1988 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1989 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1990 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1991 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1992 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1993 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1994 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1995 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1996 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1997 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1998 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 1999 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 2000 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 2001 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 2002 0 1 select * from test1 where col1=:"SYS_B_0" 6tsrjxza4gvur 2003 0 1 select * from test1 where col1=:"SYS_B_0" 11:46:50 SYS@ prod >select sql_id,hash_value,address,version_count from v$sqlarea where sql_id='6tsrjxza4gvur'; SQL_ID HASH_VALUE ADDRESS VERSION_COUNT ------------- ---------- -------- ------------- 6tsrjxza4gvur 3561484119 2E8CF368 3885