oracle11g library cache-mutex x的处理测试

最新推荐文章于 2024-01-24 18:00:02 发布

clg10051

最新推荐文章于 2024-01-24 18:00:02 发布

阅读量158

点赞数

文章标签：数据库

---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引用表进行删除列ddl
SQL> alter table t_version drop column b;

Table altered.

---管理会话,loads及invalidations有变化；
SQL> /

---测试会话，对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> truncate table t_version;

Table truncated.

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

no rows selected

---管理会话,truncate sql引用基表,不影响loads及invalidations
SQL> /

----测试会话,对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的影响
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> 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 */%';

小结：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个数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-764190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-764190/

clg10051

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle11g library cache-mutex x的处理测试

---library cache:mutex x--library cache:mutex x产生的原因What causes 'library cache: mutex X' wait?1,Frequent Hard ...
复制链接

扫一扫