oracle数据库级别优化分析工具介绍-阿里云开发者社区

开发者社区> 数据库> 正文

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及其执行计划
http://www.hellodba.com/Doc/statspack_report_sql.htm

 

 

 

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,最后写入系统视图


ash包括两部分内容,一部分是SGA中的,这部分反映是本次系统启动以来的数据,并且ASH尽量保留1小时的内容,这部分内容保存在v$active_session_history另一部分保存在系统字典表里dba_hist_active_sess_history,是永久的数据


ASH也有生成报告的脚本,在目录下$ORACLE_HOME/rdbms/admin/

 

ashrpt.sql: 生成数据库级别的ASH统计报表
ashrpti.sql: 生成数据库实例级别的ASH统计报表,常用于RAC单实例

 

 

 

4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划)

 

a.autotrace


安装

 

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是:(UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%/rdbms/admin)ultxplan.sql。

 

SQL> connect sys/sys@colm2 as sysdba;

 

SQL> @C:/oracle/ora92/rdbms/admin/utlxplan.sql;

 

SQL> create public synonym plan_table for plan_table;--建立同义词

 

SQL> grant all on plan_table to public;--授权所有用户

 

要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色,这个脚本在目
录(UNIX:$ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%/sqlplus/admin)中;

 

SQL> @C:/oracle/ora92/sqlplus/admin/plustrce.sql;

然后将角色plustrace授予需要autotrace的用户;

SQL>grant plustrace to public;


经过以上步骤的设置,就可以在sql*plus中使用autotrace了,autotrace功能只能在SQL*PLUS里使用

 


AUTOTRACE Statistics常用列解释

 

 db block gets :从buffer cache中读取的block的数量
 consistent gets:从buffer cache中读取的undo数据的block的数量
 physical reads: 从磁盘读取的block的数量
 redo size: DML生成的redo的大小
 sorts (memory):在内存执行的排序量
 sorts (disk):在磁盘上执行的排序量
 

eg:

 

SYS@db>set autotrace           
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

 

SYS@db>set timing on

 

SYS@db>set autot trace exp stat


SYS@db>select * from tab;

3809 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  1059 | 46596 |   216   (2)|
|   1 |  NESTED LOOPS OUTER   |        |  1059 | 46596 |   216   (2)|
|   2 |   TABLE ACCESS FULL   | OBJ$   |  1059 | 39183 |   158   (2)|
|   3 |   TABLE ACCESS CLUSTER| TAB$   |     1 |     7 |     1   (0)|
|   4 |    INDEX UNIQUE SCAN  | I_OBJ# |     1 |       |     0   (0)|
---------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9077  consistent gets
          0  physical reads
          0  redo size
     133502  bytes sent via SQL*Net to client
       3252  bytes received via SQL*Net from client
        255  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3809  rows processed

 

b。EXPLAIN的使用

 

    Oracle RDBMS执行每一条SQL语句,都必须经过Oracle优化器的评估。所以,了解优化器是如何选择(搜索)路径以
    及索引是如何被使用的,对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查
    询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

 

1.1 、安 装                                                                                                                      

 要使用EXPLAIN首先要执行相应的脚本,创建出Explain_plan表。

 

具体脚本执行如下:

   $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)  该脚本后会生成一个表这个程序会创建一个名为plan_table的表。

 

1.2 、使用


常规使用语法:
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >

 

其中:


STATEMENT_ID:是一个唯一的字符串,把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。


TABLE_NAME:是plan表名,它结构如前所示,你可以任意设定这个名称。


SQL_STATEMENT:是真正的SQL语句。  

                                                                 

比如:                                                                                                               

SQL>explain plan set statement_id='T_TEST' for select * from t_test;            

SQL>

Explained

 

执行下面语句可以查询到执行计划

SQL>SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
  2  FROM PLAN_TABLE  a
  3  WHERE STATEMENT_ID='T_TEST'
  4  ORDER BY Id;


也可以用这句话 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE里的数据罗列出来。

分类: OralceRac

本文转自einyboy博客园博客,原文链接:http://www.cnblogs.com/einyboy/archive/2012/07/12/2588514.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章