--===================================================================
1. 数据库性能分析及优化
------------------------------------------------------------
DB 性能分析
目的：快速准确查找核心性能问题，并分析问题原因。
需要的能力： 1.通过发生的现象，分析因果关系的能力。
2.性能分析所必要的数据收集，筛选，分析的能力。
能力1，需要理解 Oracle 架构
能力2，10g以后可以使用如下方法。
1）AWR 数据
2）动态视图，数据字典，元数据
3）生成SQL跟踪文件
------------------------------------------------------------
AWR 概念
AWR(Automatic Workload Repository)
直译：自动工作负荷关联储存仓库
数据库生成后Open时，无需其他设置自动执行，通过 MMON，MMNL 后台进程收集数据，存储在 SYSAUX 表空间。
AWR快照是默认周期是1小时，保存7天。
AWR收集的信息： 1.DB等待事件 及 统计信息
2.系统统计信息
3.数据库负荷信息
4.SQL执行信息
5.活动Session信息
AWR数据保存在表 WRH$_* , WRM$_* 里， 可以通过字典 DBA_HIST_* 查询。
------------------------------------------------------------
活用 AWR 数据
可以做：1)掌握 DB 运行状态 (DBA_HIST_OSSTAT)
2）解决性能问题
3）分析性能趋势
--===================================================================
2. AWR 管理
------------------------------------------------------------
AWR 快照管理
快照：是从内存保存到硬盘时，保存点的AWR数据。
包：DBMS_WORKLOAD_REPOSITORY
快照生成：
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT([FLUSH_LEVEL => 'flush_level']);
END;
/
快照删除：
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID => snap_id , HIGH_SNAP_ID => snap_id [,DBID => dbid]) ;
END;
/
快照设定变更：
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS([RETENTION => retention_time][,INTERVAL => interval_time][,TOPNSQL => topn_sql_number]) ;
END;
/
BASELINE 生成：
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID => snap_id , END_SNAP_ID => snap_id , BASELINE_NAME => 'baseline_name'[,DBID => dbid]) ;
END;
/
BASELINE 删除：
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(BASELINE_NAME => 'baseline_name' [,CASCADE => true|false][,DBID => dbid]) ;
END;
/
------------------------------------------------------------
快照生成：
--AWR 收集信息使用默认参数：TYPICAL
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
--AWR 收集信息使用参数：ALL
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(FLUSH_LEVEL => 'ALL');
STATISTICS_LEVEL 参数：
该参数是设定 AWR收集信息等级，10g以后开始增加使用。
TYPICAL: AWR基本统计数据收集，默认值。
ALL: AWR基本统计数据 + OS 统计数据 + 执行计划数据
BASIC: 不收集统计数据
如果设定为 BASIC 不收集统计数据，而且下面10g新功能不能使用。
1. AWR 快照自动执行
2. 数据库自动诊断监视工具 ADDM（Automatic Database Diagnostic Monitor）
3. 自动共享内存管理 ASMM（Automatic Shared Memory Management）
4. 优化器自动统计信息收集
5. MTTR Advisor
------------------------------------------------------------
查看快照：
SELECT SNAP_ID
,INSTANCE_NUMBER
,TO_CHAR(STARTUP_TIME,'YYYY-MM-DD HH24:MI') INST_START
,TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') BEGIN
,TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') END
,SNAP_LEVEL
FROM DBA_HIST_SNAPSHOT
ORDER BY INSTANCE_NUMBER,SNAP_ID DESC ;
/
其中：SNAP_LEVEL 1 : TYPICAL , 2：ALL
------------------------------------------------------------
查看收集快照时发生的错误信息：
SELECT * FROM DBA_HIST_SNAP_ERROR
ORDER BY SNAP_ID DESC ;
/
------------------------------------------------------------
快照删除：
-- 删除 ID 为 1 到 10 的 快照
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID => 1 , HIGH_SNAP_ID => 10 ) ;
END;
/
LOW_SNAP_ID：要删除的快照开始ID
HIGH_SNAP_ID：要删除的快照结束ID
DBID：DB的ID，默认null，默认值时选择当前登录数据库ID
------------------------------------------------------------
快照设定变更：
默认设定：
AWR快照周期：60分钟
AWR快照保存期间：7天
项目部收集SQL数：TOP 30个
-- 保留60天，周期30分钟，收集前 100个SQL 的设定例子
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION => 60*24*60 ,INTERVAL => 30 ,TOPNSQL =>'100' ) ;
END;
/
参数说明：
INTERVAL: 快照周期是分钟为单位，默认值是NULL，NULL是维持上次设定结果，接收参数类型是 NUMBER
RETENTION: 快照保存期间是分钟为单位，默认值是NULL，NULL是维持上次设定结果，接收参数类型是 NUMBER
TOPNSQL: 指定 Elapse时间，CPU使用时间，ParseCall次数，Sharable Memore，Version Count 的项目收集的SQL数量，
各项目别倒序排序，保存设定数量的SQL。接收参数类型是 NUMBER 或 VARCHAR2，默认值是NULL，NULL是维持上次设定结果。
DBID: 默认null，默认值时选择当前登录数据库ID。
参数详细说明：
INTERVAl：可以设定，最小10分钟， 最大1年，如设定为0，系统将不会自动收集快照信息，而且手工收集快照也无法使用。
RETENTION：可以设定，最小1天，最大100年，如设定为0，系统将永久保存。
TOPNSQL：1）NUMBER 时，可以设定，最少30个，最多1亿个（100，000，000）。
2）VARCHAR2 时，DEFAULT：默认值，如果AWR收集等级是 TYPICAL，则30个，如果是ALL,则100个。
MAXINUM：保存游标缓冲里的所有SQL。
数字：与NUMBER类型用法相同。
------------------------------------------------------------
查看快照设定：
SELECT * FROM DBA_HIST_WR_CONTROL
------------------------------------------------------------
BASELINE 生成：
BASELINE: 是为了AWR数据比较用设定的基础快照区间。
-- 设定 快照ID 为 1000 到 1010 的区间为快照基本区间，起名为：BL_NAME
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID => 1000 , END_SNAP_ID => 1010 , BASELINE_NAME => 'BL_NAME') ;
END;
/
参数说明：
START_SNAP_ID: 区间开始快照ID，接收参数类型是 NUMBER ，没有默认值。
END_SNAP_ID: 区间结束快照ID，接收参数类型是 NUMBER ，没有默认值。
BASELINE_NAME: 基础快照区间名称，接收参数类型是 VARCHAR2 ，没有默认值。
DBID: 默认null，默认值时选择当前登录数据库ID。
awrddrpt.sql
awrdrpti.sql
基础快照区间, 即使超过了 RETENTION 指定的区间，也不会被删除。
------------------------------------------------------------
BASELINE 删除：
-- 删除 名为 BL_NAME 的 BASELINE，并删除相对应快照。
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(BASELINE_NAME => 'BL_NAME' ,CASCADE => true) ;
END;
/
参数说明：
BASELINE_NAME：基础快照区间名称，接收参数类型是 VARCHAR2 ，没有默认值。
CASCADE：删除BASELINE时，指定是否一起删除区间对应的快照，接收参数类型是 BOOLEAN，如果选择 TRUE，则对应快照一起删除，
默认值是 FALSE。
DBID: 默认null，默认值时选择当前登录数据库ID。
------------------------------------------------------------
查看 BASELINE 信息：
SELECT * FROM DBA_HIST_BASELINE;
------------------------------------------------------------
AWR 数据备份及恢复
AWR 备份
awrextr.sql
步骤：
1） SQL>@?/rdbms/admin/awrextr.sql
2） 输入 DBID
3） 输入要显示今天到几天内的快照。如输入1，则最近24小时内快照。
4） 输入要备份快照区间，如输入 123 ~ 133 则备份这个区间的快照。
5） 输入输出目录名
6） 输入备份镜像文件名
--
CREATE OR REPLACE DIRECTORY PUMP_AWR AS '/PUMP';
--
GRANT READ, WRITE ON DIRECTORY PUMP_AWR TO PUBLIC ;
--
DROP DIRECTORY PUMP_AWR
AWR 恢复
awrload.sql
限制：不能恢复到与备份时的DBID相同的DBID上，即只能恢复到其他DBID上. 否则 ORA-20107 .
步骤：
1） SQL>@?/rdbms/admin/awrload.sql
2） 输入输出目录名
3） 输入备份镜像文件名
4） 输入临时用户名，如果直接回车，默认用户 AWR_STAGE
5） 输入表空间
6） 输入临时表空间
--===================================================================
3. 生成 AWR 报告
------------------------------------------------------------
生成 AWR 报告
V$ 开始的动态视图是实例开始的时候开始收集，到终止为止持续增加。实例终止的时候重置为0.
执行AWR快照的时候，内存的数据会保存在硬盘上，AWR报告是通过设定开始快照ID与结束快照ID，
计算这个区间段等待事件发生次数及统计信息差异值来生成报告。
如果10：00实例重启了一次，那将无法生成9：00到11：00的AWR报告的，因为等待事件及统计信息都重置过了，
AWR报表就没有意义了。
AWR 报告可以通过包 DBMS_WORKLOAD_REPOSITORY 或Oracle提供的脚本生成。所有 AWR相关脚本在下面目录。
$ORACLE_HOME/rdbms/admin/
用途别分类如下；
用途 脚本
AWR DB 报告 awrrpt.sql , awrrpti.sql
AWR DB 比较报告 awrddrpt.sql , awrddrpi.sql
AWR SQL 报告 awrsqrpt.sql , awrsqrpi.sql
AWR 信息查询报告 awrinfo.sql
ASH 报告 ashrpt.sql , ashrpti.sql
ADDM 报告 addmrpt.sql , addmrpti.sql
其它 awrddinp.sql , awrinput.sql , awrinpnmp.sql
------------------------------------------------------------
生成 AWR DB 报告
需要的权限：
SELECT ANY DICTIONARY 权限
SYS.DBMS_WORKLOAD_REPOSITORY 包执行权限
awrrpt.sql ：执行脚本的实例的AWR DB 报告
awrrpti.sql ：指定DBID或实例的AWR DB 报告
awrrpt.sql 步骤：
1) SQL>@?/rdbms/admin/awrrpt.sql
2) 选择输出类型，HTML 或 TEXT， 默认HTML
3）选择输出几天内的快照
4）输入开始快照ID和结束快照ID
5）指定报表输出文件名
AWR_REPORT_TEXT
AWR_REPORT_HTML
查看 DBID：
SELECT * FROM V$DATABASE ;
生成 AWR DB 报告：（HTML）
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1345435618,1,91020,91028));
结果保存到记事本，保存为 .html 文件即可。
生成 AWR DB 报告：（TEXT）
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(1345435618,1,91020,91028));
------------------------------------------------------------
生成 AWR DB 比较报告
awrddrpt.sql
awrddrpti.sql
awrddrpt.sql 步骤：
1) SQL>@?/rdbms/admin/awrddrpt.sql
2) 选择输出类型，HTML 或 TEXT， 默认HTML,推荐用 HTML 。
3）选择第一个统计输出几天内的快照
4）输入第一个统计开始快照ID和结束快照ID
5）选择第二个统计输出几天内的快照
6）输入第二个统计开始快照ID和结束快照ID
7）指定报表输出文件名
AWR_DIFF_REPORT_HTML
AWR_DIFF_REPORT_TEXT
生成 AWR DB 比较报告：（HTML）
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(1345435618,1,91020,91028,1345435618,1,90804,90812));
结果保存到记事本，保存为 .html 文件即可。
------------------------------------------------------------
生成 AWR SQL 报告
awrsqrpt.sql
awrsqrpti.sql
awrsqrpt.sql 步骤：
1) SQL>@?/rdbms/admin/awrsqrpt.sql
2) 选择输出类型，HTML 或 TEXT， 默认HTML,推荐用 HTML 。
3）选择输出几天内的快照
4）输入开始快照ID和结束快照ID
5) 输入 SQL ID
6）指定报表输出文件名
------------------------------------------------------------
生成 AWR 信息查询报告
awrinfo.sql
通过这个可以确认的信息有；
* AWR 快照信息
* 顾问构成信息
* ASH使用信息
awrinfo.sql 步骤：
1) SQL>@?/rdbms/admin/awrinfo.sql
2）指定报表输出文件名
------------------------------------------------------------
其他生成 AWR 报告相关脚本
awrddinp.sql : awrddrpti.sql 里被调用。
awrinput.sql : awrrpti.sql , awrsqrpi.sql 里被调用。
awrinpnm.sql : awrrpti.sql , awrsqrpi.sql 里被调用。
--===================================================================
4. AWR DB 报告分析及优化
------------------------------------------------------------
AWR DB 报告分析
可分为两类：
1）简报 ： AWR 收集信息，实例环境状态等简报，可以快速定位问题所在。
2）详细报告：DB状态项目别详细列出，可以对问题详细分析。
分析方法如下；
------------------------------------------------------------
简报
简报可以分为5种类型；
1） DB环境 ： 开始到 Cache Sizes 前。
2） 内存 ： Cache Sizes
Instance Efficiency Percentages (Target 100%)
3） 负载 ： Load Profile
4） 等待事件 ：Top 5 Timed Foreground Events
5） RAC ：Global Cache Load Profile
Global Cache Efficiency Percentages (Target local+remote 100%)
Global Cache and Enqueue Services - Workload Characteristics
Global Cache and Enqueue Services - Messaging Statistics
------------------------------------------------------------
Instance 信息：
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
MCS 1345435618 mcs1 1 27-Mar-16 16:48 11.2.0.3.0 YES
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
bjmcspd1 AIX-Based Systems (64-bit) 64 16 124.50
DB Name ：DB 名 DB_NAME
DB Id ：DB ID DBID
Instance ：Instance 名 INSTANCE_NAME
Inst Num ：Instance 号 INSTANCE_NUMBER
Startup Time ：Instance 开始时间 STARTUP_TIME
Release ：Oracle 版本信息 VERSION
RAC ：是否 RAC PARALLEL
Host Name ：操作系统名 HOST_NAME
Platform ：平台
CPUs ：逻辑CPU数
Cores ：CPU核数
Sockets ：CPU插槽
Memory(GB) ：物理内存
SELECT * FROM V$INSTANCE;
------------------------------------------------------------
AWR 快照信息：
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 91020 20-Jun-16 08:20:10 474 11.2
End Snap: 91028 20-Jun-16 11:00:40 495 11.1
Elapsed: 160.50 (mins)
DB Time: 3,076.18 (mins)
Snap Id ： 快照ID
Snap Time ： 快照执行时间
Sessions ： 快照执行时Session数
Curs/Sess ： 快照执行时Sessions使用的平均游标数
Elapsed ： 两个快照之间的时间（单位：分钟）
DB Time ： 两个快照之间的 DB Time （单位：分钟）
CUP 利用率 = DB Time/CPUs/Elapsed = 3040.76/64/160.04 = 0.296875
SELECT T1.SNAP_ID
,T1.END_INTERVAL_TIME
,T3.SESSIONS
,ROUND(T3.CURSORS/T3.SESSIONS,1) AS "Curs/Sess"
,ROUND(EXTRACT(DAY FROM T1.END_INTERVAL_TIME - LAG(T1.END_INTERVAL_TIME)OVER(ORDER BY T1.SNAP_ID)) * 60 *24 +
EXTRACT(HOUR FROM T1.END_INTERVAL_TIME - LAG(T1.END_INTERVAL_TIME)OVER(ORDER BY T1.SNAP_ID)) * 60 +
EXTRACT(MINUTE FROM T1.END_INTERVAL_TIME - LAG(T1.END_INTERVAL_TIME)OVER(ORDER BY T1.SNAP_ID)) +
EXTRACT(SECOND FROM T1.END_INTERVAL_TIME - LAG(T1.END_INTERVAL_TIME)OVER(ORDER BY T1.SNAP_ID))/60
,2) AS "Elapsed(mins)"
,ROUND((T2.VALUE - LAG(T2.VALUE)OVER(ORDER BY T1.SNAP_ID))/1000000/60,2) AS "DB Time(mins)"
FROM DBA_HIST_SNAPSHOT T1
,DBA_HIST_SYS_TIME_MODEL T2
,(
SELECT T3.DBID,T3.INSTANCE_NUMBER,T3.SNAP_ID
,SUM(DECODE(T3.STAT_NAME,'logons current',VALUE)) Sessions
,SUM(DECODE(T3.STAT_NAME,'opened cursors current',VALUE)) cursors
FROM DBA_HIST_SYSSTAT T3
WHERE T3.STAT_NAME IN ( 'logons current','opened cursors current')
AND T3.SNAP_ID IN (91020,91028)
AND T3.INSTANCE_NUMBER = 1
AND T3.DBID = 1345435618
GROUP BY T3.DBID,T3.INSTANCE_NUMBER,T3.SNAP_ID
) T3
WHERE T1.SNAP_ID IN (91020,91028)
AND T1.INSTANCE_NUMBER = 1
AND T1.DBID = 1345435618
AND T1.DBID = T2.DBID
AND T1.INSTANCE_NUMBER = T2.INSTANCE_NUMBER
AND T1.SNAP_ID = T2.SNAP_ID
AND T2.STAT_NAME = 'DB time'
AND T1.DBID = T3.DBID
AND T1.INSTANCE_NUMBER = T3.INSTANCE_NUMBER
AND T1.SNAP_ID = T3.SNAP_ID
ORDER BY T1.SNAP_ID;
------------------------------------------------------------
Cache Sizes：
Begin End
---------- ----------
Buffer Cache: 46,080M 46,080M Std Block Size: 8K
Shared Pool Size: 10,240M 10,240M Log Buffer: 110,148K
Buffer Cache ： Buffer Cache 大小
Shared Pool Size ： 共享池大小
Std Block Size ： db_block_size 参数设定的值，基本Block大小
Log Buffer ： Redo Log Buffer 大小
如果使用 SGA 的自动管理（ASMM）功能，开始和结束两个点的快照 Buffer Cache 和 Shared Pool Size 可能不一样，但 Log Buffer 不变。
SELECT T1.DBID,T1.INSTANCE_NUMBER,T1.SNAP_ID
,MAX(CASE WHEN T1.PARAMETER_NAME = 'db_cache_size' THEN T1.VALUE/1024/1024 END) AS "Buffer Cache(MB)"
,MAX(CASE WHEN T1.PARAMETER_NAME = 'shared_pool_size' THEN T1.VALUE/1024/1024 END) AS "Shared Pool Size(MB)"
,MAX(CASE WHEN T1.PARAMETER_NAME = 'db_block_size' THEN T1.VALUE/1024 END) AS "Std Block Size(K)"
,MAX(CASE WHEN T2.NAME = 'log_buffer' THEN T2.BYTES/1024 END) AS "Log Buffer(K)"
FROM DBA_HIST_PARAMETER T1
,DBA_HIST_SGASTAT T2
WHERE T1.PARAMETER_NAME IN ('db_cache_size','shared_pool_size','db_block_size')
AND T2.NAME = 'log_buffer'
AND T1.DBID = T2.DBID
AND T1.INSTANCE_NUMBER = T2.INSTANCE_NUMBER
AND T1.SNAP_ID = T2.SNAP_ID
AND T1.DBID = 1345435618
AND T1.INSTANCE_NUMBER = 1
AND T1.SNAP_ID IN (91020,91028)
GROUP BY T1.DBID,T1.INSTANCE_NUMBER,T1.SNAP_ID
ORDER BY T1.SNAP_ID;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13696961/viewspace-2120849/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13696961/viewspace-2120849/
451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
