通过案例学调优之–和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———- —————————— ———- —————————— ———-本文来源gaodaimacom搞#^代%!码&网* —————————— 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
转载请注明原文链接：通过案例学调优之–和 SHARED POOL 相关的主要 Latch
