oracle11g library cache-mutex x的處理測試

---library cache:mutex x

--library cache:mutex x產生的原因
What causes 'library cache: mutex X' wait?

1,Frequent Hard Parses - If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.
2,High Version Counts - When Version counts become excessive, a long chain of versions needs to be examined and this can lead to contention on this event
3,Invalidations and reloads
4,Known Bugs

SQL> conn wisdomone/system
Connected.

SQL> select sid,event,total_waits,time_waited,average_wait,wait_class from v$session_event where  wait_class!='Idle';

       SID EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT WAIT_CLASS
---------- ------------------------------ ----------- ----------- ------------ ----------------------------------------------------------------
        30 library cache: mutex X                   5         855       171.05 Concurrency
        38 library cache: mutex X                   4        2505       626.23 Concurrency
        40 library cache: mutex X                   8        3398       424.79 Concurrency
        42 library cache: mutex X                   3        3380      1126.59 Concurrency
        44 library cache: mutex X                  10        3935       393.47 Concurrency
        46 library cache: mutex X                   3        1642       547.17 Concurrency
        48 library cache: mutex X                   3        3871      1290.39 Concurrency
        49 library cache: mutex X                   6        4028       671.31 Concurrency

 

---如下plsql指令碼同時在7到8個會話同時執行，則產生library cache:mutex x

---此指令碼為大量產生硬解析sql的指令碼
declare
v_sql clob;
begin
v_sql:='select a from t_version';
for i in 1..10000000 loop
  v_sql:=v_sql||chr(32);
  --dbms_output.put_line(v_sql||length(v_sql));
  execute immediate v_sql;
end loop;
end;
/

1,library cache:mutex x必須併發會話到達一定程度
2,library cache:mutex x產生一個原因為：大量硬解析出現；

---------測試下reload與invalidation

--reload與parse_calls的一些測試
http://space.itpub.net/9240380/viewspace-762248

--invalidation
---sql子游標無效的總次數
INVALIDATIONS NUMBER Total number of invalidations over all the child cursors

---sql child cursor如何會變為無效
1,sql引用的表物件ddl或刪除
2,sql引用的表許可權變化
3,其它

--處理方法：
Check for invalidations under Library Cache Activity.  If the invalidation has high number,
then check ddl's performed during the time such as truncate, drop, grants, dbms_stats, etc.

--測試會話
SQL> select /*+ test */ a from t_version;

no rows selected

--管理會話
select sql_text,version_count,loads,parse_calls,executions,invalidations from v$sqlarea where sql_text like '%/*+ test */%';

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- -------------
select /*+ test */ a from t_version                  1          1           1             0

---管理會話：如多次執行測試會話,parse_calls與executions相同，即此parse_calls為軟硬解析總和，此引數＝executions
SQL> select sql_text,version_count,loads,parse_calls,executions,invalidations from v$sqlarea where sql_text like '%/*+ test */%';

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          1           4          4             0

---測試會話，對sql引用表進行新增列ddl
SQL> alter table t_version add b int;

Table altered.

---管理會話,sql引用表發生ddl後,其相關引數資訊重置,invalidations加1；executions重新由4初始化為1;parse_calls也重新初始化為1;
---且loads重新進行了載入，由1變為2
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          2           1          1             1

---測試會話，對sql引用表進行刪除列ddl
SQL> alter table t_version drop column b;

Table altered.

---管理會話,loads及invalidations有變化；
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          3           1          1             2

---測試會話，對sql引用表進行重新命名列ddl
SQL> alter table t_version rename column a to newa;

Table altered.

--測試會話，執行sql直接報錯
SQL> select /*+ test */ a from t_version;
select /*+ test */ a from t_version
                   *
ERROR at line 1:
ORA-00904: "A": invalid identifier

---管理會話,重新命名錶列後，對應sql消失
SQL> /

no rows selected

---測試會話：把表列重新命名回去
SQL> alter table t_version rename column newa to a;

Table altered.

SQL> select /*+ test */ a from t_version;

no rows selected

---管理會話,把列重新命名回來，其sql仍在共享池中，未清空，在其基礎上累加資訊，比如：loads累加；invalidations累加
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          5           1          1             4

 

--測試會話
SQL> truncate table t_version;

Table truncated.

SQL> select /*+ test */ a from t_version;

no rows selected

---管理會話,truncate sql引用基表,不影響loads及invalidations
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          5           2          2             4

----測試會話,對sql基表進行授權
SQL> grant select on t_version to system;

Grant succeeded.

SQL> select /*+ test */ a from t_version;

no rows selected

----管理會話,loads及invalidations累加；其它值重新初始化
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          6           1          1             5

----測試會話,查詢收集統計對sql的影響
SQL> exec dbms_stats.gather_table_stats(user,'t_version');

PL/SQL procedure successfully completed.

SQL> select /*+ test */ a from t_version;

no rows selected

----管理會話,對sql之loads and invalidations無影響
SQL> /

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          6           2          2             5

----測試會話,刪除刪除表
SQL> drop table t_version;

Table dropped.

SQL> flashback table t_version to before drop;

Flashback complete.

SQL> select /*+ test */ a from t_version;

no rows selected

----管理會話,drop table也會影響loads及invalidations
SQL> select sql_text,version_count,loads,parse_calls,executions,invalidations from v$sqlarea where sql_text like '%/*+ test */%';

SQL_TEXT                                 VERSION_COUNT      LOADS PARSE_CALLS EXECUTIONS INVALIDATIONS
---------------------------------------- ------------- ---------- ----------- ---------- -------------
select /*+ test */ a from t_version                  1          7           1          1             6

小結：1,loads與invalidations密切相關
      2,sql引用基表發生ddl,grants會影響loads及invalidations
      3,truncate,dbms_stats不會影響loads及invalidations
      3,drop sql引用基表也會影響loads及invalidations
      5,發生loads及invalidations同時，其他如parse_calls及executions也會重新初始化為1
     
----version_count與v$sqlarea測試
http://space.itpub.net/9240380/viewspace-763828   

---cursor_sharing與v$sqlarea_v$sql_v$sql_shared_cursor及version_count
http://space.itpub.net/9240380/viewspace-763840 

小結：1,cursor_sharing與version_count有關係；在11g最好配置為force,而similar會產生很大的version_count
      2,cbo mode變化也影響version_count
      3,version_count即sql child cursor個數