通过案例学调优之--和 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 的结构如下图:
650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151209/11522B346-0.jpg" title="lat.png" alt="wKioL1RkGtWg3qX4AAMVCoYd3pI249.jpg" />
可以看到其结构和 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 网站上也有一张简洁的图:
650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151209/11522B039-1.jpg" title="lat7.png" alt="wKiom1RkGtzz2Kf-AAGj-k3jkoQ459.jpg" />
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 CacheLatch,直到找到对应的执行计划。然后释放 Latch。(软解析)
(N) 如果不存在,就要去 SHARE POOL 里面获得可用空间,来生生成对应的 LibraryCache 对象。这个时候就要获得 Shared Pool Latch 在 SHARE POOL 的 Free Lis(SHRAEPOOL 通过 Free List 管理 Free Chunk)查找可用的空间,之后释放 Shared Pool Latch。接下来就开始进行硬解析过程,将执行解析后的执行计划等信息记录到 LibraryCache 中,这个整个过程消耗大量的 CPU,同时将一直持有 Library Cache Latch,一直到硬解析结束。(硬解析)3) 根据获得的执行计划,开始执行 SQL,如:到 BUFFER CACHE 查询数据等。
3.4 整个逻辑如下如:
650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151209/11522AZ6-2.jpg" title="lat8.png" alt="wKiom1RkGzzA6588AAG5zh2DItM951.jpg" />
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 a3012:07:27 SYS@ prod >col p1text for a2012:07:33 SYS@ prod >col p2text for a2012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0012: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 valueElapsed: 00:00:00.0112: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 valueElapsed: 00:00:00.0012: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 value12:09:04 SYS@ prod >/ SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- -------------------- 38 cursor: mutex S 3561484119 idn 2555904 value 39 cursor: pin S &n