通过案例学调优之--和 SHARED POOL 相关的主要 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 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