本文章为网络笔记,看了warehouse老师的视频受益匪浅,更是感觉自己技术太过初级,特写了本笔记,方便以后反复学习!
如有任何不妥,请发邮件至102448567@qq.com删除文章!
关于warehouse:
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 8_02_addm
也是利用AWR
收集的快照信息,AWR
要人为的分析,ADDM
直接说明问题在哪,更智能
生成
$ORACLE_HOME/rdbms/admin/addmrpt.sql
这个报告只有text
格式,没有html
格式
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
xxxxxxxxx ORCLxx 2 orclxx2
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* xxxxxxxxx 2 ORCLxx orclxx2 racxx02
xxxxxxxxx 1 ORCLxx orclxx1 racxx01
Using xxxxxxxxx for database Id
Using 2 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orclxx2 ORCLxx 16703 14 Mar 2021 00:00 1
16722 14 Mar 2021 09:30 1
16723 14 Mar 2021 10:00 1
16724 14 Mar 2021 10:30 1
16725 14 Mar 2021 11:00 1
16726 14 Mar 2021 11:30 1
16727 14 Mar 2021 12:00 1
输入开始快照和结束快照,报告名字默认
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16723
Begin Snapshot Id specified: 16723
Enter value for end_snap: 16725
End Snapshot Id specified: 16725
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_2_16723_16725.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
报告输入完成
End of Report
Report written to addmrpt_2_16723_16725.txt
报告如下:
生成报告的时候Oracle
生成了一个任务,任务也是Oracle
中的一个对象,结合调度机制可以让Oracle
每天都生成一个ADDM
报告
ADDM Report for Task 'TASK_136712'
----------------------------------
Analysis Period ##分析期间
---------------
AWR snapshot range from 16723 to 16725. ##快照的范围
Time period starts at 14-MAR-21 10.00.20 AM ##开始时间点
Time period ends at 14-MAR-21 11.00.25 AM ##结束时间点
Analysis Target ##分析目标
---------------
Database 'XXXXX' with DB ID xxxxxxxxx.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance orclxx2, numbered 2 and hosted at
racxx02.
Activity During the Analysis Period ##在分析期间内数据库的活动情况
-----------------------------------
Total database time was 69399 seconds. ##全部的DB TIME时间
The average number of active sessions was 19.25. ##平均活动session的数量是19.25个
##我们这个addm报告的时间区间大概是3605s,DB TIME=3605*19.25=69396
Summary of Findings ##发现的问题的汇总
-------------------
Description Active Sessions Recommendations
Percent of Activity ##建议
##描述 ##活动session占用活动的
百分比
##一共发现了5个问题
---------------------------------------- ------------------- --------------
-
1 Top SQL Statements 11.16 | 58 5
2 Top Segments by "User I/O" and "Cluster" 5.6 | 29.08 2
3 Global Cache Messaging 4.87 | 25.29 1
4 Buffer Busy - Hot Objects 4.18 | 21.7 0
5 Undersized SGA 1.86 | 9.67 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations ##发现的问题的描述以及建议
----------------------------
Finding 1: Top SQL Statements
Impact ##(影响) is 11.17 active sessions, 58% of total activity.
-------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is 6.15 active sessions, 31.92% of total activity.
--------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"5w01g46fu8d27".
Related Object
SQL statement with SQL_ID 5w01g46fu8d27.
select sum(HJJE) as HJJE,sum(YLSL) as YLSL from (select c.brxm as
BRXM, b.fymc as FYMC, a.yldj as YLDJ, a.ylsl * nvl(a.TS,1) as YLSL,
a.hjje as HJJE, e.personname as YSXM, d.sfrq as SFRQ,c.KDRQ as KDRQ
from ms_yj02 a, gy_ylsf b, ms_yj01 c, ms_mzxx d, sys_personnel e
where a.ylxh = b.fyxh and a.yjxh = c.yjxh and c.mzxh = d.mzxh and
c.ysdm = e.personid and a.JGID = 'xxxxxxxxxxxx' and b.FYMC like
'%多普勒%' and c.ZFPB = 0 and d.SFRQ <= to_date( '2021-01-31
23:59:59', ' yyyy-mm-dd HH24:mi:ss')and d.SFRQ >=to_date('2021-01-01
00:00:00', ' yyyy-mm-dd HH24:mi:ss') order by d.sfrq desc )
Rationale
The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "5w01g46fu8d27" was executed 249 times and had
an average elapsed time of 85 seconds.
Rationale
I/O and Cluster wait for TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
93657 consumed 98% of the database time spent on this SQL statement.
Recommendation 2: SQL Tuning
Estimated benefit is 1.81 active sessions, 9.41% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"bh8hjuragdnkh".
Related Object
SQL statement with SQL_ID bh8hjuragdnkh.
select count(*) as col_0_0_ from MS_CF01 ms_cf01x0_, MS_BRDA
ms_brda1_ where ms_cf01x0_.BRID=ms_brda1_.BRID and ms_cf01x0_.FYBZ=0
and (ms_cf01x0_.FPHM is not null) and ms_cf01x0_.ZFPB=0 and (YFSB=:1
or SJYF=:2)
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "bh8hjuragdnkh" was executed 12114 times and
had an average elapsed time of 0.59 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is 1.33 active sessions, 6.91% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"08sv5tu39ayzf".
Related Object
SQL statement with SQL_ID 08sv5tu39ayzf.
select a.CFSB as CFSB,c.FPHM as FPHM,b.BRXM as BRXM,b.BRXB as
BRXB,b.CSNY as CSNY,a.YFSB as YFSB,a.CFHM as CFHM,a.CFLX as
CFLX,a.SJJG as SJJG,a.SJYF as SJYF,a.SJFYBZ as SJFYBZ,c.BRXZ as BRXZ
from MS_CF01 a left join MS_CF01_WS m on a.cfsb = m.cfsb,MS_BRDA
b,MS_MZXX c where a.BRID=b.BRID and a.MZXH = c.MZXH and ((a.FYBZ = 0
and a.FPHM is not null) and a.ZFPB = 0) and ( YFSB=:1 or SJYF=:2 )and
a.JGID=:3 and (m.wsfs is null or m.wsfs = 5) order by c.SFRQ
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "08sv5tu39ayzf" was executed 12117 times and
had an average elapsed time of 0.4 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .98 active sessions, 5.08% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"dgtr5zj23crnb".
Related Object
SQL statement with SQL_ID dgtr5zj23crnb.
SELECT to_date(to_char(INPUTDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') as
INPUTDATE FROM mdc_hpts4visit WHERE SERIALNUMBER = :1
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "dgtr5zj23crnb" was executed 16186 times and
had an average elapsed time of 0.21 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .89 active sessions, 4.63% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"67gpyhh6jw4pn".
Related Object
SQL statement with SQL_ID 67gpyhh6jw4pn.
select sum(HJJE) as HJJE,sum(YLSL) as YLSL from (select c.brxm as
BRXM, b.fymc as FYMC, a.yldj as YLDJ, a.ylsl * nvl(a.TS,1) as YLSL,
a.hjje as HJJE, e.personname as YSXM, d.sfrq as SFRQ,c.KDRQ as KDRQ
from ms_yj02 a, gy_ylsf b, ms_yj01 c, ms_mzxx d, sys_personnel e
where a.ylxh = b.fyxh and a.yjxh = c.yjxh and c.mzxh = d.mzxh and
c.ysdm = e.personid and a.JGID = '440306008003' and b.FYMC like
'%动态心电图%' and c.ZFPB = 0 and d.SFRQ <= to_date( '2021-03-14
23:59:59', ' yyyy-mm-dd HH24:mi:ss')and d.SFRQ >=to_date('2021-03-01
00:00:00', ' yyyy-mm-dd HH24:mi:ss') order by d.sfrq desc )
Rationale
The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "67gpyhh6jw4pn" was executed 38 times and had
an average elapsed time of 83 seconds.
Rationale
I/O and Cluster wait for TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
93657 consumed 99% of the database time spent on this SQL statement.
Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is 5.6 active sessions, 29.08% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is 5.06 active sessions, 26.27% of total activity.
--------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "BSSZSK_BANEW.MS_YJ02" with object ID
93657.
Related Object
Database object with ID 93657.
Action
Investigate application logic involving I/O on TABLE
"BSSZSK_BANEW.MS_YJ02" with object ID 93657.
Related Object
Database object with ID 93657.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the SELECT statement with
SQL_ID "5w01g46fu8d27" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 288 full object scans,
96427346 physical reads, 996 physical writes and 0 direct reads.
Recommendation 2: Segment Tuning
Estimated benefit is .54 active sessions, 2.82% of total activity.
------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE
"BSSZSK_BANEW.MS_BCJL" with object ID 93650.
Related Object
Database object with ID 93650.
Rationale
The I/O usage statistics for the object are: 0 full object scans, 117769
physical reads, 2730 physical writes and 0 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 2.39 active sessions, 12.43% of total activity.
Finding 3: Global Cache Messaging
Impact is 4.87 active sessions, 25.29% of total activity.
---------------------------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Recommendation 1: Application Analysis
Estimated benefit is 4.87 active sessions, 25.29% of total activity.
--------------------------------------------------------------------
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant time on Cluster waits. For example, the SELECT statement
with SQL_ID "5w01g46fu8d27" is responsible for 79% of Cluster wait
during the analysis period.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is 4.88 active sessions, 25.35% of total activity.
Finding 4: Buffer Busy - Hot Objects
Impact is 4.18 active sessions, 21.7% of total activity.
--------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
Read and write contention on database blocks was consuming significant
database time.
Impact is 4.18 active sessions, 21.7% of total activity.
Inter-instance messaging was consuming significant database time on
this instance.
Impact is 4.87 active sessions, 25.29% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 4.88 active sessions, 25.35% of total activity.
Finding 5: Undersized SGA
Impact is 1.86 active sessions, 9.67% of total activity.
--------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "122880 M" during the analysis period.
Recommendation 1: Database Configuration
Estimated benefit is 1.86 active sessions, 9.67% of total activity.
-------------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
222720 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is 2.39 active sessions, 12.43% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information ##附加的信息、额外的信息
----------------------
Miscellaneous Information ##多样化的信息
##Oracle把等待事件大概归成了十多类
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
##等待类commit没有消耗大量的数据库时间(commit会触发LGWR工作)
Wait class "Concurrency" was not consuming significant database time.
##等待类并发没有消耗过多的数据库时间
Wait class "Configuration" was not consuming significant database time.
##等待类配置没有消耗过多的数据库时间,也就是说配置没有问题
CPU was not a bottleneck for the instance.
##总结一句话,CPU不是这个实例的瓶颈
Wait class "Network" was not consuming significant database time.
##等待类网络没有消耗过多的数据库时间
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
##群集互连的网络延迟在可接受的范围内1毫秒
Session connect and disconnect calls were not consuming significant database
time.
##session的连接、断开、调用都没有消耗过多的数据库时间(大量的session连接会调用process对性能会有影响,断开的时候pmon进程要释放大量的资源所以说session的连接、断开会影响性能)
Hard parsing of SQL statements was not consuming significant database time.
##sql语句的硬解析没有消耗大量的数据库时间
The database's maintenance windows were active during 99% of the analysis
period.
##在99%的分析期间内,数据库的维护窗口处于活动状态
如果是windows
上跑addm
报告的话发现问题的第一条通常是Virtual Memory Paging
(虚拟内存换页),这是因为收到了Oracle
参数的影响
SQL> show parameter sga;
NAME TYPE VALUE
------------------ ----------- -------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE ##就是这个参数
sga_max_size big integer 596M
sga_target big integer 596M
这里我们指定了sga_target
为596M
,但是当Oracle
启动的时候并没有立马给Oracle
分配596M
,而是分配了一部分内存,足够让Oracle
实例起来,因为如果立马分配所有的内存CPU
扫描内存需要较长的时间,这样就延长了实例启动的时间,Oracle
的策略是当pre_page_sga
是false
的时候实例启动的时候没有立即分配所有的内存,如果是true
的话就是立即分配所有的sga
内存
这个参数是一个静态参数
SQL> alter system set pre_page_sga = TRUE scope = spfile;
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 234882128 bytes
Database Buffers 377487360 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 596M
sga_target big integer 596M