2020年(Oracle管理)oracle数据库级别优化分析工具介绍

（Oracle管理）oracle数据库级别优化分析工具介

绍

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

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

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

二类是os级别的统计信息

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

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

目录：

1.statspack

2.ASH

3.AWR

4.ORACLEEXPLAINPLAN的总结(查询sql的执行计划)

a.autotrace

b.explain的使用

1.statspack

a。安装

sql>sqlplus"/assysdba"

SQL>selectfile_namefromdba_data_files;

SQL>createtablespaceperfstatdatafile'e:\oracle\oradata\skate\perfstat.dbf'size2000 m;

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

b。使用

SQL>connperfstat/passwd

收集统计信息

sql>executestatspack.snap

或

SQL>execstatspack.SNAP(i_snap_level=>5);

生成报告

sql>@ORACLE_HOME\rdbms\admin\spreport.sql

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

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

declare

Variablejobnumber;

begin

dbms_job.submit(:job,"statspack.snap;",trunc(sysdate+1/24,'hh24'),"trunc(sysdate+ 1/24,'hh24')");

commit;

end;

/

查看job使用情况

SQL>selectjob,schema_user,next_date,interval,whatfromuser_jobs

自动停止采样job

declare

Variablejobnumber;

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，这可以通过EXECSTATSPACK.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>execstatspack.SNAP(i_snap_level=>6);

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

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

参考文档：

利用statspack来获取生成环境中topSQL及其执行计划

http:///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.ORACLEEXPLAINPLAN的总结(查询sql的执行计划)

a.autotrace

安装

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是：（UNIX:$ORACLE_HOME/rdbms/admin,Windows:%ORACLE_HOME%\rdbms\admin ）ultxplan.sql。

SQL>connectsys/sys@colm2assysdba;

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

SQL>createpublicsynonymplan_tableforplan_table;--建立同义词

SQL>grantallonplan_tabletopublic;--授权所有用户

要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目

录

（UNIX:$ORACLE_HOME/sqlplus/admin,Windows:%ORACLE_HOME%\sqlplus\admi n）中；

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

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

SQL>grantplustracetopublic;

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

AUTOTRACEStatistics常用列解释

dbblockgets:从buffercache中读取的block的数量

consistentgets:从buffercache中读取的undo数据的block的数量physicalreads:从磁盘读取的block的数量

redosize:DML生成的redo的大小

sorts(memory):在内存执行的排序量

sorts(disk):在磁盘上执行的排序量

eg:

SYS@db>setautotrace

Usage:SETAUTOT[RACE]{OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]] SYS@db>settimingon

SYS@db>setautottraceexpstat

SYS@db>select*fromtab;

3809rowsselected.

Elapsed:00:00:00.06

ExecutionPlan

----------------------------------------------------------

---------------------------------------------------------------------

|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|

---------------------------------------------------------------------

|0|SELECTSTATEMENT||1059|46596|216(2)|

|1|NESTEDLOOPSOUTER||1059|46596|216(2)|

|2|TABLEACCESSFULL|OBJ$|1059|39183|158(2)|

|3|TABLEACCESSCLUSTER|TAB$|1|7|1(0)|

|4|INDEXUNIQUESCAN|I_OBJ#|1||0(0)|

---------------------------------------------------------------------

Note

-----

-'PLAN_TABLE'isoldversion

Statistics

----------------------------------------------------------

0recursivecalls

0dbblockgets

9077consistentgets

0physicalreads

0redosize

133502bytessentviaSQL*Nettoclient

3252bytesreceivedviaSQL*Netfromclient

255SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

3809rowsprocessed

b。EXPLAIN的使用

OracleRDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以

及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查

询数据是如何得到的即搜索路径(我们通常称为AccessPath)。从而使我们选择最优的查询方

式达到最大的优化效果。

1.1、安装

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

具体脚本执行如下：

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

1.2、使用

常规使用语法：

explainPLAN[SETSTATEMENT_ID[=]][INTO]

FOR

其中：

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

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

SQL_STATEMENT：是真正的SQL语句。

比如：

SQL>explainplansetstatement_id='T_TEST'forselect*fromt_test;

SQL>

Explained

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

SQL>SELECTA.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID

2FROMPLAN_TABLEa

3WHERESTATEMENT_ID='T_TEST'

4ORDERBYId;

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

----end-----