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

通过案例学调优之--和 SHARED POOL 相关的主要 Latch 3.1 、和 SHARED POOL 相关的主要 Latch 有: Latch: shared pool Latch: library cache 我们知道 Oracle 通过 SHARED POOL 来实现 SQL 共享,减少硬解析等。而 SQL 的相关信息,如: SQL 语句文本, SQL 执行

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

3.1、和 SHARED POOL 相关的主要 Latch 有:
Latch: shared pool
Latch: library cache
我们知道 Oracle 通过 SHARED POOL 来实现 SQL 共享,减少硬解析等。而 SQL 的相关信息, 如:SQL 语句文本,SQL 执行计划等都存放在 SHARED POOL 的 Library Cache 部分。

3.2、其中 Library Cache 的结构如下图:

      可以看到其结构和 BUFFER CACHE 类似,为了能够在 Library Cache 中快速的查找到对应的 SQL, 也是通过将不同的 SQL 语句通过 HASH 函数 HASH 后放置到对应 Hash Bucket 来保存的。 

下面看看图中***的块(右上角标注着:Object Handle):
1) 这个块也就是所谓的 Library Cache Object Handle,这个 Handle 描述 Library Cache 中对象的一些属性,如名称(Name),所属的命名空间(Namespace)、标记(Flags)、指向对象所处的内存地址的指针(Heap 0)等。对应 SQL 来说,这个可以算是父游标。

2) Heap 0 用来存放与对象有直接关系的一些信息,比如对象类型、对象相关的表、实际的执行计划等。

3) 同一个 Hash Bucket 中的 Object Handle 相互链接形成一条 Chain。
关于 Library Cache 更详细的可以查阅 Julian Dyke 的 Library Cache Internals.ppt。

Eygle 网站上也有一张简洁的图:

3.3下面先看SQL的的整个执行过程来,然后再看看执行过程中是怎么用到SHARED POOL的相 关 Latch。

1. 1)  当客户端执行一条 SQL,这时候 Oracle 首先将 SQL 文本转换成 ASCII 值,然后根据 HASH函数计算该 SQL 对应的 Hash Value。

2. 2)  根据得到的 Hash Value 到 Library Cache 中查找对应的 Bucket,然后查找 Bucket 里是否存

   在该 SQL?
   (Y) 如果存在,则接下来查找对应的子游标,这个时候将一直持有 Library Cache Latch,直到找到对应的执行计划。然后释放 Latch。(软解析)
   (N) 如果不存在,就要去 SHARE POOL 里面获得可用空间,来生生成对应的 Library Cache 对象。这个时候就要获得 Shared Pool Latch 在 SHARE POOL 的 Free Lis(SHRAE POOL 通过 Free List 管理 Free Chunk)查找可用的空间,之后释放 Shared Pool Latch。 接下来就开始进行硬解析过程,将执行解析后的执行计划等信息记录到 Library Cache 中,这个整个过程消耗大量的 CPU,同时将一直持有 Library Cache Latch,一 直到硬解析结束。(硬解析)

3. 3)  根据获得的执行计划,开始执行 SQL,如:到 BUFFER CACHE 查询数据等。

3.4 整个逻辑如下如:

3.5 当出现Latch竞争严重的时候:
3.5.1如果同时出现大量的 Share Pool Latch 和 Library Cache Latch 的话,根据上面的逻辑那说明数 据库中存在大量的硬解析,这个时候就要查找那些 SQL 没有绑定变量。
3.5.2如果只是出现大量的 Library Cache Latch 的话,那么可能有两种情况:
1) 当持有 Library Cache Latch 查找 Bucket 对应的 Chain 时候,发现存在高 Version 的 SQL,这个时候就要扫描这些对应的子游标,整个过程将一直持有 Latch,导致其他会话获取不到 Latch 进行操作。

2) 大量的并发请求,而且不能实现 SQL 一次 Parse Call 多次 Execution。 

案例分析：

3.6 测试模拟为硬解析和 SQL 的 Version Count 高的情况。
3.6.1Oracle 10g 有方法可以让 SQL 产生很多的子游标,必须具备下面几种的条件:

1)cursor_sharing = similar
2)收集了列上的 histogram
3)SQL 中使用到了此列作为条件,并且条件是“等于”
4)这个 SQL 是没有绑定变量的
这时候,Oracle 会认为每条 SQL 的 literal 变量都是 unsafe 的,因此就不重用以前的 cursor而新产生一个 version,也就会重新硬解析一次。

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

可以看到 SQL 的 Version_Count 很高,而且 V$SQL 视图里面也能查到对应的子游标。

案例分析：

模拟高并发下,对 Version Count 高 SQL 查询:

• session 1：

• 11:42:41 SYS@ prod >conn scott/tiger
  Connected.
  12:05:10 SCOTT@ prod >select * from v$mystat where rownum=1;
         SID STATISTIC#      VALUE
  ---------- ---------- ----------
          38          0          0
  
  12:06:03 SCOTT@ prod >begin
  12:06:23   2  for i in 1..500000 loop
  12:06:23   3   execute immediate 'select * from test1 where col1=1';
  12:06:23   4    end loop;
  12:06:23   5    end;
  12:06:23   6   /
  PL/SQL procedure successfully completed.

• session 2：
  11:30:32 SYS@ prod >conn scott/tiger
  Connected.
  12:05:15 SCOTT@ prod >select * from v$mystat where rownum=1;
         SID STATISTIC#      VALUE
  ---------- ---------- ----------
          39          0          0
  
  12:06:11 SCOTT@ prod >begin
  12:06:29   2  for i in 1..500000 loop
  12:06:29   3   execute immediate 'select * from test1 where col1=1';
  12:06:29   4    end loop;
  12:06:29   5    end;
  12:06:29   6   /
  PL/SQL procedure successfully completed.

• 12:07:17 SYS@ prod >col event for a30
  12:07:27 SYS@ prod >col p1text for a20
  12:07:33 SYS@ prod >col p2text for a20
  12:07:38 SYS@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (38,39)
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 cursor: mutex S                3561484119 idn                     2490369 value
  Elapsed: 00:00:00.00
  12:07:38 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 library cache: mutex X         3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:07:43 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2555904 value
          39 cursor: mutex S                3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:07:45 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 latch: shared pool              537557404 address                     293 number
          39 library cache: mutex X         3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:07:46 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 library cache: mutex X         3561484119 idn                     2621440 value
  Elapsed: 00:00:00.00
  12:07:47 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 cursor: mutex S                3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:07:49 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 library cache: mutex X         3561484119 idn                     2621440 value
  Elapsed: 00:00:00.00
  12:07:50 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 library cache: mutex X         3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:07:51 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2555904 value
          39 cursor: mutex S                3561484119 idn                     2490368 value
  Elapsed: 00:00:00.01
  12:08:11 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 cursor: pin S                  3561484119 idn                     2555905 value
          39 library cache: mutex X         3561484119 idn                     2490368 value
  Elapsed: 00:00:00.00
  12:08:15 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 library cache: mutex X         3561484119 idn                     2621440 value
          39 library cache: mutex X              64028 idn                     2490368 value
  12:09:04 SYS@ prod >/
         SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
  ---------- ------------------------------ ---------- -------------------- ---------- --------------------
          38 cursor: mutex S                3561484119 idn                     2555904 value
          39 cursor: pin S                &n