Home >Database >Mysql Tutorial >通过案例学调优之--和SHAREDPOOL相关的主要Latch

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

WBOYOriginal: 2016-06-07 14:53:161295browse

通过案例学调优之--和 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) 当客户端执行一条 SQL,这时候 Oracle 首先将 SQL 文本转换成 ASCII 值,然后根据 HASH函数计算该 SQL 对应的 Hash Value。
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) 根据获得的执行计划,开始执行 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

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：SQLServer2014内存优化表（1）实现内存优化表Next article：解析一个通过添加本地分区索引提高SQL性能的案例

See more

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

Related articles