当前位置: 首页 > news >正文

StatsPack 使用初解

3 StatsPack的管理和调整
Oracle还提供了系统脚本用于Truncate这些统计信息表,这个脚本名字是: sptrunc.sql (8i、9i都相同)
execute statspack.modify_statspack_parameter -
(i_snap_level => 10, -
i_buffer_gets_th => 10000, -
i_disk_reads_th => 1000);

sppurge.sql - Purge a range of Snapshot Id’s between the specified begin and end Snap Id’s
sptrunc.sql - Truncates all data in Statspack tables

删除其他用户的job,不一定非要这个用户登陆,可以使用dbms_ijob包。

配置StatsPack参数

- 快照级别

StatsPack可以通过设置level级别来改变收集统计信息的多少。 以下是各个级别收集信息的内容:

Levels = 0 一般性能统计
统计内容:
这个级别及更高会收集一般的性能统计,例如:等待,系统事件,系统统计,回滚段数据,SGA,后台事件,会话事件,锁,buffer pool 统计,parent latch统计。

Levels = 5 增加:SQL 语句
包含了所有低级别的统计信息,还增加了使用资源较高的SQL 语句信息。

SQL ‘Thresholds’
The SQL 语句按照以下预定义的阀值进行统计:
- number of executions of the SQL statement (default 100)
- number of disk reads performed by the SQL statement (default 1,000)
- number of parse calls performed by the SQL statement (default 1,000)
- number of buffer gets performed by the SQL statement (default 10,000)

当 SQL 语句使用的资源超过了以上任何一个阀值,就会被统计到快照中。
SQL语句阀值使用 stats$statspack_parameter 表中的记录或者执行快照时输入的参数。

Levels = 6
比上一级别增加了,Top SQL 的执行计划及使用率的信息。适用于执行计划变化的场合。如果要收集shared pool 中所有的SQL语句,需要把上面的阀值设置为0。

Level = 7
Levels >= 7 增加: Segment 级别统计
有了segment的统计信息,可以知道哪些segment使用频率高,调整segment的物理布局,优化I/O负载。对于RAC环境,可以发现实例争用的热点对象。
Level 7 包含以下 segment 统计:

Logical reads
Db block changes
Physical reads
Physical writes
Physical reads direct
Physical writes direct
Global cache consistent read blocks served (RAC specific)
Global cache current blocks served (RAC specific)
Buffer busy waits
ITL waits
Row lock waits

Levels = 10 增加:Child latches
这个级别收集的信息最全,也最耗时,除非需要child latch的信息,一般不建议使用。

- Snapshot SQL thresholds

There are other parameters which can be configured in addition to the level.
These parameters are used as thresholds when collecting SQL statements;
if any SQL statements breach the threshold, these are the statements which
are captured during the snapshot.

Snapshot level and threshold information used by the package is stored
in the stats$statspack_parameter table.

- Changing the default values for Snapshot Level and SQL Thresholds

The default parameters used for taking snapshots can be adjusted/modified so
that they better capture data about an instance’s workload.

This can be done either by:

o Taking a snapshot, and specifying the new defaults to be saved to the
database (using statspack.snap, and using the i_modify_parameter
input variable).

SQL> execute statspack.snap -
(i_snap_level=>10, i_modify_parameter=>’true’);

Setting the i_modify_parameter value to true will save the new
thresholds in the stats$statspack_parameter table; these thresholds
will be used for all subsequent snapshots.

If the i_modify_parameter was false or omitted, the snapshot taken at
that point will use the specified values, any subsequent snapshots
use the preexisting values in the stats$statspack_parameter table.

o Changing the defaults immediately without taking a snapshot, using the
statspack.modify_statspack_parameter procedure. For example to change
the snapshot level to 10, and the SQL thresholds for buffer_gets and
disk_reads, the following statement can be issued:

SQL> execute statspack.modify_statspack_parameter -
(i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);

This procedure changes the values permananently, but does not
take a snapshot.

The full list of parameters which can be passed into the
modify_statspack_parameter procedure are the same as those for
the snap procedure.

- Specifying a Session Id

If session statistics are needed for a particular session, it is possible to
specify the session id in the call to StatsPack. The statistics gathered for
the session will include session statistics, session events and lock activity.
The default behavior is to not to gather session level statistics.

SQL> execute statspack.snap(i_session_id=>3);

How to automatically gather StatsPack snapshots:
————————————————

To be able to make comparisons of performance from one day, week or year to
the next, there must be multiple snapshots taken over a period of time. A
minimum of two snapshots are required before any performance characteristics of
the application and database can be made.

The best method to gather snapshots is to automate the collection on a
regular time interval. It is possible to do this:

- Within the database, using the Oracle dbms_job procedure to schedule the
snapshots

- Using Operating System utilities (such as ‘cron’ on Unix or ‘at’ on NT) to
schedule the snapshot. Please contact the System Administrator for more
information about using the OS utilities for automating this data collection.

- Scheduling StatsPack snapshots using DBMS_JOB package

To use an Oracle-automated method for collecting statistics, you can use
dbms_job. A sample script on how to do this is supplied in spauto.sql,
which schedules a snapshot every hour, on the hour.

In order to use dbms_job to schedule snapshots, the job_queue_processes
initialization parameter must be set to greater than 0 in the configuration
file used to start the instance for the job to be run automatically.

Example of an init.ora entry:
# Set to enable the job queue process to start. This allows dbms_job
# to schedule automatic statistics collection using STATSPACK
job_queue_processes=1

If using statsauto.sql in OPS environment, the statsauto.sql script must be
run once on each instance in the cluster. Similarly, the job_queue_processes
parameter must also be set for each instance.

Changing the interval of statistics collection
———————————————–
To change the interval of statistics collection use the dbms_job.interval
procedure

e.g.
execute dbms_job.interval(,’SYSDATE+(1/48)’);

Where ‘SYSDATE+(1/48)’ will result in the statistics being gathered each 1/48
hours (i.e. every half hour).

To force the job to run immediately,
execute dbms_job.run();

To remove the autocollect job,
execute dbms_job.remove();

For more information on dbms_job, see the Supplied Packages Reference Manual.

To gather a STATSPACK report :
==============================

SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport

You will be prompted for:
1. The beginning snapshot Id
2. The ending snapshot Id
3. The name of the report text file to be created

相关文章:

  • ArcGIS中对一组查询结果同时闪烁的实现
  • 鸟哥的基础学习-习题与答案
  • 我的人生谚语
  • 梅德克领导AMD成功转变,明年或将在中国市场持续发力?
  • 基于网络数据采集系统的社交媒体/网络营销
  • SQL创建用户及角色
  • Net设计模式实例之单例模式( Singleton Pattern)
  • vxvm之卷管理
  • mapextreme 2008 的helloworld (1)
  • 真的要对Sun说道别了……
  • 互联网产品一门户网站奥运频道UI分析
  • 用JSON 和 Google 实现全文翻译
  • 【编程好习惯】借助隐式初始化简化程序逻辑
  • 思科网络学院CCNA网络基础知识精华
  • 基于兴趣和信任评价的P2P语义社区模型
  • python3.6+scrapy+mysql 爬虫实战
  • 【JavaScript】通过闭包创建具有私有属性的实例对象
  • Android 初级面试者拾遗(前台界面篇)之 Activity 和 Fragment
  • CSS 三角实现
  • java2019面试题北京
  • Mac 鼠须管 Rime 输入法 安装五笔输入法 教程
  • node-sass 安装卡在 node scripts/install.js 解决办法
  • Python_网络编程
  • session共享问题解决方案
  • Solarized Scheme
  • Synchronized 关键字使用、底层原理、JDK1.6 之后的底层优化以及 和ReenTrantLock 的对比...
  • Webpack入门之遇到的那些坑,系列示例Demo
  • 开发基于以太坊智能合约的DApp
  • 三分钟教你同步 Visual Studio Code 设置
  • 使用common-codec进行md5加密
  • 思考 CSS 架构
  • 微信小程序--------语音识别(前端自己也能玩)
  • 深度学习之轻量级神经网络在TWS蓝牙音频处理器上的部署
  • #Js篇:单线程模式同步任务异步任务任务队列事件循环setTimeout() setInterval()
  • (14)学习笔记:动手深度学习(Pytorch神经网络基础)
  • (3)nginx 配置(nginx.conf)
  • (k8s中)docker netty OOM问题记录
  • (二)PySpark3:SparkSQL编程
  • (分享)自己整理的一些简单awk实用语句
  • (附源码)springboot电竞专题网站 毕业设计 641314
  • (全注解开发)学习Spring-MVC的第三天
  • ***测试-HTTP方法
  • .apk 成为历史!
  • .Net Attribute详解(上)-Attribute本质以及一个简单示例
  • .NET DevOps 接入指南 | 1. GitLab 安装
  • .net 打包工具_pyinstaller打包的exe太大?你需要站在巨人的肩膀上-VC++才是王道
  • .net 使用ajax控件后如何调用前端脚本
  • .Net 转战 Android 4.4 日常笔记(4)--按钮事件和国际化
  • .NET(C#、VB)APP开发——Smobiler平台控件介绍:Bluetooth组件
  • .NetCore部署微服务(二)
  • .Net中间语言BeforeFieldInit
  • @Autowired 与@Resource的区别
  • @LoadBalanced 和 @RefreshScope 同时使用,负载均衡失效分析
  • @Transactional 详解
  • [ CTF ] WriteUp- 2022年第三届“网鼎杯”网络安全大赛(朱雀组)