通过案例学调优之--和sharedpool相关的主要latch3.1、和sharedpool相关的主要latch有:latch:sharedpoollatch:librarycache我们知道oracle通过sharedpool来实现sq
的相关信息,如:
,也是通过将不同的
的相关
library cachelatchlibrarycache shraepool 。接下来就开始进行硬解析过程,将执行解析后的执行计划等信息记录到 librarycache ,一直到硬解析结束。(硬解析)
的话,根据上面的逻辑那说明数据库中存在大量的硬解析,这个时候就要查找那些
10:56:01 scott@ prod >show parameter cursorname type value------------------------------------ ----------- ------------------------------cursor_sharing string similarcursor_space_for_time boolean falseopen_cursors integer 300session_cached_cursors integer 501、session1：以scott建立测试表11:44:26 sys@ prod >conn scott/tigerconnected.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_objects10: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_statistics11:00:43 2 where table_name='test';column_name num_buckets histogram------------------------------ ----------- ---------------col1 3 height balanced11: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 011: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_statistics11:05:19 2 where table_name='test1';column_name num_buckets histogram------------------------------ ----------- ---------------col1 3 height balanced11:05:30 sys@ prod >alter system flush shared_pool;system altered.4、在session 1执行以下操作11:02:42 scott@ prod >begin11:06:28 2 for i in 1..50000 loop11: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 >begin11:08:01 2 for i in 1..50000 loop11: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 event11: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 numberelapsed: 00:00:00.0011: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 numberelapsed: 00:00:00.0011: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 numberelapsed: 00:00:00.0011: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 numberelapsed: 00:00:00.0011: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 number11: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 value11: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 number11: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