statistics_level 参数的应用
来源:互联网 发布:数据挖掘学什么 编辑:程序博客网 时间:2024/05/29 18:50
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够挣断99%的性能问题了.
example: seting statistics_level
alter system set statistics_level=basic;alter system set statistics_level=typical;alter system set statistics_level=all;oralter session set statistics_level=basic;alter session set statistics_level=typical;alter session set statistics_level=all; oracle 还提供了v$statistics_level视图用来记录各个参数下oracle可以收集的统计信息的类别: SQL> ALTER SYSTEM SET statistics_level=basic; System altered. SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable—————————— ———- ———- ———- ———-Buffer Cache Advice DISABLED DISABLED TYPICAL NOMTTR Advice DISABLED DISABLED TYPICAL NOPGA Advice DISABLED DISABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED ALL YESSegment Level Statistics DISABLED DISABLED TYPICAL NOShared Pool Advice DISABLED DISABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics DISABLED DISABLED TYPICAL YES 8 rows selected. statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了; SQL> ALTER SYSTEM SET statistics_level=typical; System altered. SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable—————————— ———- ———- ———- ———-Buffer Cache Advice ENABLED ENABLED TYPICAL NOMTTR Advice ENABLED ENABLED TYPICAL NOPGA Advice ENABLED ENABLED TYPICAL NOPlan Execution Statistics DISABLED DISABLED ALL YESSegment Level Statistics ENABLED ENABLED TYPICAL NOShared Pool Advice ENABLED ENABLED TYPICAL NOTimed OS Statistics DISABLED DISABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES 8 rows selected. statistics_level=typical的时候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;
SQL> ALTER SYSTEM SET statistics_level=all; System altered. SQL> SELECT statistics_name, 2 session_status, 3 system_status, 4 activation_level, 5 session_settable 6 FROM v$statistics_level 7 ORDER BY statistics_name; Session System Activation SessionStatistics Name Status Status Level Settable—————————— ———- ———- ———- ———-Buffer Cache Advice ENABLED ENABLED TYPICAL NOMTTR Advice ENABLED ENABLED TYPICAL NOPGA Advice ENABLED ENABLED TYPICAL NOPlan Execution Statistics ENABLED ENABLED ALL YESSegment Level Statistics ENABLED ENABLED TYPICAL NOShared Pool Advice ENABLED ENABLED TYPICAL NOTimed OS Statistics ENABLED ENABLED ALL YESTimed Statistics ENABLED ENABLED TYPICAL YES 如果当statistics_level=ALL,系统收集所有的统计信息.from:http://blog.csdn.net/zengmuansha/article/details/5149398
- statistics_level 参数的应用
- statistics_level 参数的应用
- statistics_level参数的介绍
- 关于参数STATISTICS_LEVEL
- statistics_level参数说明
- STATISTICS_LEVEL
- STATISTICS_LEVEL
- statistics_level
- 浅谈统计信息参数statistics_level和自动共享内存(ASMM)的关系
- oracle statistics_level
- V$STATISTICS_LEVEL
- statistics_level相关
- 参数的应用
- 不定参数的应用
- 不定参数的应用
- 不定参数的应用
- 不定参数的应用
- 不定参数的应用
- Linux下磁盘操作
- HTTP协议之状态码详解
- 信息检索导论学习笔记(2)——词项词典及倒排记录表
- 小记近日调试代码的两件怪事
- Hadoop namenode无法启动
- statistics_level 参数的应用
- iOS多线程编程指南(二)线程管理
- HTTP协议之Cookie
- 【译】TFS 2013服务中云端负载测试简介
- ArrayAdapter适配器和SimpleAdapter适配器以及ListView的使用
- Android开发必看知识,不看后悔
- 动态规划
- 【Hadoop】Hadoop开发中wrong key/value与type mismatch错误
- 解析Caliburn.Micro(一)