写在前面

好久不见，许久没有更新了，账号密码也是刚找回来，手机都换了，感觉物是人非。我依然是当年的ERP小开发一枚，不过时间是我不仅仅局限于java及报表中的设计开发，个人的兴趣爱好也越来越偏向于数据库，所以这次打算给大家分享一下在项目中遇到过的ORACLE数据库报告收集及调优的内容，共勉进步。

ORACLE启动配置文件说明

Oracle中的参数文件是一个包含一系列参数以及参数对应值的操作系统文件。它们是在数据库实例启动时候加载的，决定了数据库的物理 结构、内存、数据库的限制及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件。可以分为两种类型ORACLE启动配置文件说明

PFile

初始化参数文件（Initialization Parameters Files），Oracle 9i之前，ORACLE一直采用pfile方式存储初始化参数，pfile 默认的名称为“init+例程名.ora”文件路径：/data/app/oracle/product/12.1.0/dbhome_1/dbs，这是一个文本文件，可以用任何文本编辑工具打开

SPFile

服务器参数文件（Server Parameter Files），从Oracle 9i开始，Oracle引入了spfile文件，spfile 默认的名称为“spfile+例程名.ora”文件路径：/data/app/oracle/product/12.1.0/dbhome_1/dbs 以二进制文本形式存在，不能用vi编辑器对其中参数进行修改，只能通过SQL命令在线修改。

系统配置修改

数据库在安装过程中会设置许多默认参数，这些参数往往无法发挥服务器最大的性能，从而限制了数据库的性能上限以及处理速度。一般我们在项目中用到的常用系统参数如下：

1. 实例名称 ，所有的配置修改都要执行具体实例名，尤其是多实例系统；
2. sga_target:SGA_TARGET是oracle10g中用于实现自动SGA内存管理而新增加的；
3. sga_max_size:SGA_MAX_SIZE指的是可动态分配的最大值﹐而SGA_TARGET是当前已分配的最大sga,两者相辅相成，正常情况SGA_MAX_SIZE=SGA_TARGET；
4. pga_aggregate_target:此参数用来指定所有session总计可以使用最大PGA内存；
5. dml_locks:设置系统中允许DML锁的数量；
6. open_cursors:指定一个会话一次最多可以拥有的游标数。缺省值为 50；
7. processes:此参数与连接数有关，sessions=(1.1*process+5)；

参数设置执行过程

查询实例名称：select instance_name from v$instance;
备份spfile文件：create pfile from spfile;
修改sga_max_size参数：alter system set sga_max_size=20G scope=spfile sid=‘实例名’
修改sga_target参数：alter system set sga_target=20G scope=spfile sid=‘实例名’
修改pga_aggregate_target参数：alter system set pga_aggregate_target=2G scope=spfile sid=‘实例名’
修改dml_locks参数：alter system set dml_locks=12000 scope=spfile sid=‘实例名’
修改open_cursors参数：alter system set open_cursors=3000 scope=spfile sid=‘实例名’
修改processes参数：alter system set processes=1200 scope=spfile sid=‘实例名’

修改参数后启动失败解决方案

优化脚本会存在数据库启动失败等问题，如果是因为内存不足启动失败，可以修改系统参数，扩展数据库可用内存：
修改

/etc/sysctl.conf
kernel.shmmax=15461882265
kernel.shmall=3774873
kernel.msgmax=65535
kernel.msgmnb=65535
执行 sudo sysctl –p

如果是其他问题，可通过从pfile启动数据库，然后修改spfile文件，再重启数据库来解决。
在sqlplus中指向启动配置文件：

startup pfile=/data/app/oracle/product/11.2.0/dbhome_1/dbs/initxjmy.ora

AWR和ADDM报告收集

AWR全称Automatic Workload Repository,自动负载信息库,是Oracle 10g版本后推出的一种性能收集和分析工具,提供了一个时间段内整个系统的报表数据。
ADDM 是基于对 AWR 日志进行分析的，所以要先执行 AWR 后执行 ADDM。

命令下收集报告

收集时会有文件保存类型设置，建议输入： html。
提示要收集信息的天数，如收集一天，则输入 1。之后弹出已有的快照列表，输入起始快照 id 与结束快照 id 即输入收集时间点对应的起始快照与结束快照 id，然后进行收集，收集完后报告
文件会生成在登录 sqlplus 时的当前目录下。
备注：awrrpt.sql 与 addmrpt.sql 文件在不同系统下的路径是不同的，要根据数据库实际的安装路径及操作系统来修改路径。
Windows 操作系统下执行脚本中如下数据库路径
@C/D/E:\app\oracle\product\11.2.0.4\dbhome_1 可改为@?，
例 SQL>@?\RDBMS\ADMIN\awrrpt.sql
非 Windows 操作系统(如 linux、AIX 等)下执行脚本中如下数据库路径
@\app\oracle\product\11.2.0.4\dbhome_1 可改为@?，
在sqlplus或者psql中执行：
@?/RDBMS/ADMIN/awrrpt.sql

收集 AWR 日志方法如下：

@D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql

收集 ADDM 日志方法如下：

@ D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\addmrpt.sql

具体顺序及流程网上都有具体的介绍，这里就不贴图了。
生成的日志文件有两个，arw生成的是html文件，addm生成的为lst文件。
其中arw的的html文件可直接开打查看，导入格式为html的原因也是有目录、链接、图形分析等内容，比单纯的文本要清晰明了很多。

日志内容截取

文件的头部内容会有当前服务器及数据库的相应硬件配置及各类参数。

这里面有许多性能及时间段的数据，各位小伙伴们可以自己研究下，很有意思，我这边就只重点提一下几个sql部分的内容，如下图所示：

在文件中，有好多个关于sql执行时间/消耗性能排序的数据，可以点击SQL Id来迅速指向具体的sql内容。如下图所示：

此处为当时间段内总耗时最长的SQL内容，此sql是一条未加任何条件的update语句，这在常识中都是不能接受的。随着改表数据量越来越大，执行的耗时只会越来越久。
找到问题所在，去项目或者程序中修改或者优化掉问题sql，就是我们所要做的。

自动调优脚本

在数据库AWR报告中存在耗时的多条SQL语句自动优化：
通过收集Oracle数据库的AWR报告，获取到报告的起始与结束的快照ID号后，建议在PL/SQL DEVELOPER工具以SQL Window方式打开，执行SQL自动调优脚本，如下所示：
将以下脚本中的数据库AWR报告BEGIN_SNAP与END_SNAP,即耗时SQL语句在收集的AWR报告中起始快照ID,结束ID的值需要进行替换,还有耗时SQL语句的SQL_ID进行替换。执行脚本进行调优诊断：

DECLARE
V_COUNT NUMBER;
V_TASK_NAME VARCHAR2(30) := ‘TUNING_FOR_SQL100’;
V_SQLSET_NAME VARCHAR2(30) := ‘SQL_SET3’;
V_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM DBA_ADVISOR_LOG A
WHERE A.TASK_NAME = V_TASK_NAME;
IF V_COUNT <> 0 THEN
DBMS_SQLTUNE.DROP_TUNING_TASK(TASK_NAME => V_TASK_NAME);
END IF;
SELECT COUNT(1)
INTO V_COUNT
FROM DBA_SQLSET A
WHERE A.NAME = V_SQLSET_NAME;
IF V_COUNT <> 0 THEN
DBMS_SQLTUNE.DROP_SQLSET(SQLSET_NAME => V_SQLSET_NAME);
END IF;
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => V_SQLSET_NAME,
DESCRIPTION => ‘FROM USER,FROM SNAP’);
OPEN V_CURSOR FOR
SELECT *
FROM (SELECT VALUE§
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP => 9974,
END_SNAP => 9984)) P
WHERE P.SQL_ID = ‘b96ycnjcmf0uu’
ORDER BY CPU_TIME DESC) ;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => V_SQLSET_NAME,
POPULATE_CURSOR => V_CURSOR);
V_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(TASK_NAME => V_TASK_NAME,
SQLSET_NAME => V_SQLSET_NAME,
RANK1 => ‘CPU_TIME’,
TIME_LIMIT => 2400,
DESCRIPTION => ‘TUNE SQLSET ORDERED BY CPU_TIME’);
—— V_TASK_NAME得到的值就是函数执行完返回的任务名称，即是TASK_NAME的值
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(V_TASK_NAME);
END;
/

获取调优建议

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_FOR_SQL100’) FROM DUAL;

调优建议脚本

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(‘TUNING_FOR_SQL100’) FROM DUAL;

把上面查出来的调优建议脚本拿出来，在PL/SQL DEVELOPER工具中执行或登录数据库，在SQL>下执行，进行SQL语句自动调优。
删除已建的调优任务方法：

BEGIN dbms_sqltune.drop_tuning_task(‘TUNING_FOR_SQL100’); END;
sqlplus / as sysdba
exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>300);
@?/rdbms/admin/awrrpt