oracle数据库级别优化分析工具介绍

当我们对数据库优化诊断时,需要收集相应的信息以供参考,从个人的使用经验来说,这种统计数据分为两大类

一类是数据库级别的统计信息
二类是os级别的统计信息

下面就分别介绍在不同的级别下,常用什么工具来收集信息帮助优化诊断

首先是oracle数据库级别优化分析工具介绍

目录:

1.statspack
2.ASH
3.AWR
4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划)
   a.autotrace
   b.explain的使用


1.statspack

a。安装

sql> sqlplus "/ as sysdba"

SQL> select file_name from dba_data_files;

SQL> create tablespace perfstat datafile \'e:/oracle/oradata/skate/perfstat.dbf\' size 2000m;

sql> @ORACLE_HOME/rdbms/admin/spcreate.sql

b。使用


SQL> conn perfstat/passwd

收集统计信息
sql> execute statspack.snap

SQL> exec statspack.SNAP(i_snap_level =>5);

生成报告
sql> @ORACLE_HOME/rdbms/admin/spreport.sql

定时收集信息有两种方式,一种是oracle job,一种是os的crontab,我比较习惯用os级别的crontab

设定其每个小时自动收集一次采样的job


declare
Variable  job  number ; 
begin 
  dbms_job.submit(:job, "statspack.snap;" ,trunc( sysdate + 1/24 , \'hh24\' ), "trunc(sysdate+1/24,\'hh24\')" ); 
  commit ; 
end ; 
/

查看job使用情况

SQL> select job,schema_user,next_date,interval,what from user_jobs

自动停止采样job


declare
Variable  job  number ; 
begin 
  dbms_job.submit(:job, "dbms_job.broken(44,true);" ,trunc( sysdate + 1 ), "null" ); 
  commit ; 
end ; 
/

清空所有stats统计信息表里的数据


sql> @ORACLE_HOME/rdbms/admin/sptrunc.sql

snapshot的level,这可以通过EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=N)来修改,N可以为0,5,6,7,10,缺省为5。

   0 仅提供一般性能统计
   5 增加了对SQL语句总体分析
   6 增加了SQL计划和使用
   7 增加了分段(Segments)级的统计
   10增加了对闩锁(Latches)的分析


  其中文档建议对10要慎重,因为代价较高。

eg:
SQL> exec statspack.SNAP(i_snap_level =>6);

oracle不仅提供生成数据库报告的脚本spreport.sql,还提供另一个statspack 报告脚本sprepsql.sql来生成SQL的报告

sql> @ORACLE_HOME/rdbms/admin/sprepsql.sql

参考文档:
利用statspack来获取生成环境中top SQL及其执行计划

2.AWR

awr是建库是自动配置和启用的,他对性能数据的收集默认是一小时,awr对历史数据的分析

生成报告脚本在目录下生成报告使用$ORACLE_HOME/rdbms/admin/,如下:

awrrpt.sql :生成指定快照区间的统计报表;
awrrpti.sql :生成指定数据库实例,并且指定快照区间的统计报表;
awrsqlrpt.sql :生成指定快照区间,指定SQL语句(实际指定的是该语句的SQLID)的统计报表;
awrsqrpi.sql :生成指定数据库实例,指定快照区间的指定SQL语句的统计报表;
awrddrpt.sql :指定两个不同的时间周期,生成这两个周期的统计对比报表;
awrddrpi.sql :指定数据库实例,并指定两个的不同时间周期,生成这两个周期的统计对比报表;


修改Snapshots设置
  通过MODIFY_SNAPSHOT_SETTINGS过程,DBA可以调整包括快照收集频率、快照保存时间、以及捕获的SQL数量三个方面的设置。 分别对应MODIFY_SNAPSHOT_SETTINGS的三个参数:

Retention :设置快照保存的时间,单位是分钟。可设置的值最小为1天,最大为100年。设置该参数值为0的话,就表示永久保留收集的快照信息。


Interval :设置快照收集的频率,以分钟为单位。可设置的值最小为10分钟,最大为1年。如果设置该参数值为0,就表示禁用AWR特性。


Topnsql :指定收集的比较占用资源的SQL数量,可设置的值最小为30,最大不超过100000000。

AWR相关几个视图:

DBA_HIST_WR_CONTROL:查看当前快照收集的相关设置
v$active_session_history:由ASH自动在内存中维护,以每秒一次的频率收集当前系统中活动session的信息
dba_hist_active_sess_history:是视图v$active_session_history的历史数据,保存在硬盘上
dba_hist_database_instance:显示数据库是实例的信息
dba_hist_snapshot:当前数据库收集到的快照信息


3.ASH

ash和awr不是完全分离的两个功能,ash以秒为单位从v$session中收集信息并保存在内存中,这块内存可以重用,内存满时,ASH数据交给AWR,最后写入系统视图

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zwzdsx.html