Oracle ADDM --dbms_addm执行oracle数据库诊断
为了诊断数据库性能问题，首先查看ADDM分析报告，通常它是在生成AWR快照时自动创建的。如果缺 省的分析不能满足，可以手动执行ADDM分析操作。ADDM可以对任何两个AWR快执行分析，只要快照仍然存储在数据库中而没有被清除掉。当在生成AWR快 照时如果出现了严重错误，ADDM将不会对实例进行分析。在这种情况下，ADDM将只能对实例的最大子集（没有出错的部分）进行分析。
手动执行ADDM分析可以使用dbms_addm包来执行操作，ADDM分析主要包括以下几种模式:
.以数据库模式来执行ADDM分析
.以实例模式来执行ADDM分析
.以部分模式来执行ADDM分析
.显式ADDM分析报告
以数据库模式来执行ADDM分析
对于Oracle RAC来说，可以以数据库模式来执行ADDM来分析数据库的所有实例。对于单实例数据库， 仍然可以以数据库模式来执行ADDM分析，如果以实例模式运行那么ADDM将简化其行为。
使用dbms_addm.analyze_db过程来以数据库模式执行ADDM:
begin dbms_addm.analyze_db( task_name in out varchar2, begin_snapshot in number, end_snapshot in number, db_id in number :=null ); end; /
task_name参数指定将要被创建的分析任务名称。begin_snapshot参数指定分析周期的开始快照。 end_snapshot参数指定分析周期的快照。db_id参数指定将要被分析的数据库标识。如果没有指定， 这个参数将使用当前所连接的数据库标识。
下面的例了创建一个以数据库模式来执行ADDM的任务，并且对快照481到484之间的时间周期对整个数 据库执行性能诊断。
SQL> var tname varchar2(30) SQL> begin 2 :tname:='ADDM for snapshot 481 to 484'; 3 dbms_addm.analyze_db(:tname,481,484); 4 end; 5 / PL/SQL procedure successfully completed.
以实例模式执行ADDM
为了对数据库的特定实例进行分析，可以以实例模式来执行ADDM。使用dbms_addm.analyze_inst过程 来进行操作:
begin dbms_addm.analyze_inst( task_name in out varchar2, begin_snapshot in number, end_snapshot in number, instance_number in number :=null, db_id in number :=null ); end; /
task_name参数指定将要被创建的分析任务名称。begin_snapshot参数指定分析周期的开始快照。 end_snapshot参数指定分析周期的快照。instance_number参数指定将会被分析的实例编号，如果没 有指定，将会使用当前所连接的实例。db_id参数指定将要被分析的数据库标识。如果没有指定，这 个参数将使用当前所连接的数据库标识。
下面的例子以实例模式来执行ADDM，并且对实例1的471到474的快照执行性能论断:
SQL> var tname varchar2(30) SQL> begin 2 :tname:='my addm for 471 to 474'; 3 dbms_addm.analyze_inst(:tname,471,474,1); 4 end; 5 / PL/SQL procedure successfully completed.
以部分模式来执行ADDM
为了对所有实例中的部分实例执行分析，可以以部分模式来执行ADDM。可以使用 dbms_addm.analyze_partial过程来执行:
begin dbms_addm.analyze_partial( task_name in out varchar2, instance_number in number, begin_snapshot in number, end_snapshot in number, db_id in number :=null ); end; /
task_name参数指定将要被创建的分析任务名称。instance_number参数用分号来隔开将会被分析的实 例编号。begin_snapshot参数指定分析周期的开始快照。end_snapshot参数指定分析周期的快照。 db_id参数指定将要被分析的数据库标识。如果没有指定，这个参数将使用当前所连接的数据库标识 。
下面的例子将以部分模式来创建ADDM诊断任务，并且对实例1,2,4的137到145之间的快照执行性能诊 断：
var tname varchar2(30) begin :tname:='my addm for 137 to 145'; dbms_addm.analyze_partial(:tname,'1,2,4',137,145); end; /
显示ADDM报告
为了以文本方式显示一个已经执行的ADDM任务的报告，可以使用dbms_addm.get_report函数:
dbms_addm.get_report(task_name in varchar2 return clob);
下面的例子使用tname变量指定addm任务名，使用dbms_addm.get_report来以文本方式来显示ADDM报 告:
SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
ADDM Report for Task 'my addm for 471 to 474'
---------------------------------------------
Analysis Period
---------------
AWR snapshot range from 471 to 474.
Time period starts at 28-SEP-16 03.00.18 AM
Time period ends at 28-SEP-16 06.00.39 AM
Analysis Target
---------------
Database 'SJJH' with DB ID 4134995129.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance sjjh, numbered 1 and hosted at
localhost.localdomain.
Activity During the Analysis Period
-----------------------------------
Total database time was 13999 seconds.
The average number of active sessions was 1.29.
Summary of Findings
-------------------
Description Active Sessions Recommendation
s Percent of Activity
---------------------------------------- ------------------- --------------
- 1 Top SQL Statements .91 | 69.99 6
2 Top Segments by "User I/O" and "Cluster" .17 | 13.43 3
3 Undersized Redo Log Buffer .13 | 10.23 1
4 Log File Switches .1 | 7.59 2
5 Buffer Busy - Hot Objects .06 | 4.37 3
6 Commits and Rollbacks .04 | 2.72 1
7 "Network" Wait Class .03 | 2.24 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .91 active sessions, 69.99% 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 .27 active sessions, 21.07% of total activity.
-------------------------------------------------------------------
Action
Investigate the CREATE MATERIALIZED VIEW statement with SQL_ID
"7v89hvfv38196" for possible performance improvements. You can
supplement the information given here with an ASH report for this
SQL_ID.
Related Object
SQL statement with SQL_ID 7v89hvfv38196.
create materialized view mt_fee_fin build immediate
refresh fast with primary key on demand
start with sysdate next sysdate+1
as select * from mt_fee_fin@dbl_yb
Rationale
The SQL Tuning Advisor cannot operate on CREATE MATERIALIZED VIEW
statements.
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.
Recommendation 2: SQL Tuning
Estimated benefit is .17 active sessions, 13.41% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"f64qufxuu0r5g".
Related Object
SQL statement with SQL_ID f64qufxuu0r5g.
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
"SJGX_YB"."DWGRBTXX"("个人保险号","姓名","身份证","性别","人员类别","单
位代码","单位名称","应缴
期间","险种","款项","补退金额","计算基数") SELECT
"I"."INSR_CODE","I"."NAME","I"."IDCARD",DECODE("I"."SEX",0,'女','男'),"
BPT"."PERS_NAME","C"."CORP_CODE","C"."CORP_NAME","IP"."PERIOD","PDI".
"INSR_DETAIL_NAME","PMI"."MONEY_NAME","IP"."PAY_MONEY","IP"."CALC_BAS
E" FROM "LV_INDIPAR" "IP","LV_CROPFUNDPAR" "CF","LV_INSR_TOPAY"
"IT","BS_CORP" "C","BS_INSURED" "I","BS_PERSON_TYPE"
"BPT","PFS_INSUR_DETAIL_INFO" "PDI","PFS_MONEY_INFO" "PMI" WHERE
"IT"."PAY_INFO_NO"="CF"."PAY_INFO_NO" AND
"CF"."MONEY_NO"="IP"."MONEY_NO" AND "PMI"."MONEY_ID"="CF"."MONEY_ID"
AND "C"."CORP_ID"="IT"."CORP_ID" AND "I"."INDI_ID"="IP"."INDI_ID" AND
"IT"."INSR_DETAIL_CODE"="PDI"."INSR_DETAIL_CODE"(+) AND
("IT"."BUSI_ASG_NO"=(-999) OR "IT"."BUSI_ASG_NO"=(-998) OR
"IT"."BUSI_ASG_NO"=(-997) OR "IT"."BUSI_ASG_NO"=(-981) OR
"IT"."BUSI_ASG_NO"=(-980) OR NVL("IT"."BUSI_ASG_NO",0)=0) AND
"IT"."INDI_PAY_FLAG"=0 AND "BPT"."PERS_TYPE"="I"."PERS_TYPE" AND
"BPT"."CENTER_ID"='430701' AND "IT"."TOPAY_TYPE"=3 AND
"IT"."INSR_DETAIL_CODE"<>'21'
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 "f64qufxuu0r5g" was executed 18 times and had
an average elapsed time of 87 seconds.
Rationale
Full scan of TABLE "SJGX_YB.LV_INDIPAR" with object ID 89473 consumed
77% of the database time spent on this SQL statement.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"2wkfgbnhtqcj9" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "f64qufxuu0r5g".
Related Object
SQL statement with SQL_ID 2wkfgbnhtqcj9.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
dbms_refresh.refresh('"SJGX_YB"."DWGRBTXX"'); :mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
Recommendation 3: SQL Tuning
Estimated benefit is .16 active sessions, 12.12% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"c1fw0514uxxrs".
Related Object
SQL statement with SQL_ID c1fw0514uxxrs.
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SJGX_YB"."V_DWJKXX" select
单位名称,
单位编码,
组织机构代码,
业务单号,
缴款来源,
经办人,
缴款日期,
缴款金额,
险种名称,
分配金额,
使用待转金额,
生成待转金额,
审核状态,
凭证号
from
(
select distinct(a.busi_bill_sn) as 业务单号,
bc.corp_name 单位名称,
bc.corp_code 单位编码,
bc.insur_org_code 组织机构代码,
pm.pay_method_name as 缴款来源,
a.make_bill 经办人,
to_char(a.make_bill_tm, 'yyyy-mm-dd') 缴款日期,
c.pay_money 缴款金额,
d.insr_detail_name 险种名称,
c.pay_money-c.bld_wait_money+c.use_wait_money as 分配金额,
c.use_wait_money 使用待转金额,
c.bld_wait_money 生成待转金额,
(case a.audit_flag
when 1 then
'已审核'
else
'未审核'
end) 审核状态,
id.cred_no 凭证号
from lv_busi_bill a,
lv_busi_record b,
lv_busi_assign c,
(select insr_detail_code, insr_detail_name
from pfs_insur_detail_info
union
select 999, '铺底险种' from dual) d,
inte_data id,
bs_corp bc,
bs_pay_method pm
where a.busi_bill_sn = b.busi_bill_sn
and bc.corp_id = b.pay_object_id
and bc.center_id = id.center_id
and id.obj_code=bc.corp_id
-- and a.center_id='430701'
and a.pay_object = 1
-- and b.pay_object_id='989'
and id.bill_no = to_char(a.busi_bill_sn)
and c.busi_reco_no = b.busi_reco_no
and d.insr_detail_code = c.insr_detail_code
and a.pay_method = pm.pay_method(+)
-- and to_char(a.make_bill_tm, 'yyyymm') between '201601' and
'201601'
--and to_char(b.fact_pay_date, 'yyyymm') between '201601' and
'201601'
order by to_char(a.make_bill_tm,'yyyy-mm-dd'))
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
I/O and Cluster wait for TABLE "SJGX_YB.INTE_DATA" with object ID 89405
consumed 100% of the database time spent on this SQL statement.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"2h0f0svtyt4c7" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "c1fw0514uxxrs".
Related Object
SQL statement with SQL_ID 2h0f0svtyt4c7.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
dbms_refresh.refresh('"SJGX_YB"."V_DWJKXX"'); :mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
Recommendation 4: SQL Tuning
Estimated benefit is .16 active sessions, 12.04% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the DELETE statement with SQL_ID
"3bvqft1u53xqz". Additionally, investigate this statement for possible
performance improvements. You can supplement the information given here
with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 3bvqft1u53xqz.
/* MV_REFRESH (DEL) */ delete from "SJGX_YB"."GRCBXX"
Rationale
The SQL spent 51% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Look at data given below and an ASH report for further performance
improvements.
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 "3bvqft1u53xqz" was executed 18 times and had
an average elapsed time of 79 seconds.
Rationale
Waiting for event "log buffer space" in wait class "Configuration"
accounted for 46% of the database time spent in processing the SQL
statement with SQL_ID "3bvqft1u53xqz".
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"8fn8wsfvd344t" are responsible for 100% of the database time spent on
the DELETE statement with SQL_ID "3bvqft1u53xqz".
Related Object
SQL statement with SQL_ID 8fn8wsfvd344t.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Recommendation 5: SQL Tuning
Estimated benefit is .1 active sessions, 7.74% of total activity.
-----------------------------------------------------------------
Action
Investigate the INSERT statement with SQL_ID "0b6acnpktxcqd" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 0b6acnpktxcqd.
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
"SJGX_YB"."GRCBXX"("姓名","身份证号","社保编号","险种子项类别","开始时
间","视同缴费月数","个人险种状
态") SELECT "BI"."NAME","BI"."IDCARD","BI"."INSR_CODE","DI"."INSR_DETA
IL_NAME",TO_CHAR("PI"."BEGIN_DATE",'yyyy-mm-dd'),"PI"."ALI_PAY_MONS",
CASE "PI"."INDI_JOIN_STA" WHEN 0 THEN '无效' ELSE '有效' END FROM
"BS_PRES_INSUR" "PI","PFS_INSUR_DETAIL_INFO" "DI","BS_INSURED" "BI"
WHERE "PI"."INDI_ID"="BI"."INDI_ID" AND
"PI"."INSR_DETAIL_CODE"="DI"."INSR_DETAIL_CODE"
Rationale
The SQL spent only 22% of its database time on CPU, I/O and Cluster
waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
Look at performance data for the SQL to find potential improvements.
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 "0b6acnpktxcqd" was executed 18 times and had
an average elapsed time of 49 seconds.
Rationale
Waiting for event "log buffer space" in wait class "Configuration"
accounted for 51% of the database time spent in processing the SQL
statement with SQL_ID "0b6acnpktxcqd".
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"8fn8wsfvd344t" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "0b6acnpktxcqd".
Related Object
SQL statement with SQL_ID 8fn8wsfvd344t.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Recommendation 6: SQL Tuning
Estimated benefit is .05 active sessions, 3.61% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"35vy818ghp687".
Related Object
SQL statement with SQL_ID 35vy818ghp687.
SELECT
lutt.name 姓名,
lutt.idcard 身份证,
'城镇居民医疗保险' as 险种,
lutt.calc_prd as 计算年月,
lutt.curr_year 所属期间,
lutt.pay_money as 缴费基数,
lutt.pay_money as 缴费金额,
lutt.urban_type_name as 缴款类型,
lutt.policy_item_name as 款项类别,
Case
When Nvl(lutt.busi_asg_no, 0) = 0 Then
'未缴'
When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
'未缴'
Else
'已缴'
End as 缴费标志,
pt.pers_name as 人员类别,
'个体' as 个体缴费标志,
bc.corp_name 缴费单位,
bc.corp_code 单位编码,
lbr.reco_time 计算时间,
to_char(lutt.fac_pay_date, 'yyyy-mm-dd') as 实际缴款时间,
Case
When Nvl(lutt.busi_asg_no, 0) = 0 Then
'未注资'
When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
'未注资'
Else
'已注资'
End as 注资标志,
lbr.reco_staff 计算人
FROM lv_urban_topay_tmp lutt,
bs_corp bc,
bs_person_type pt,
/* lv_busi_bill lbb,*/
lv_busi_record lbr,
lv_busi_assign lba
WHERE
bc.corp_id=lutt.corp_id
and pt.pers_type=lutt.pers_type and pt.center_id=lutt.center_id
and lba.busi_asg_no(+)=lutt.busi_asg_no
and lba.busi_reco_no=lbr.busi_reco_no(+)
ORDER BY lutt.curr_year, lutt.src_type, lutt.policy_item_code
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 "35vy818ghp687" was executed 1 times and had
an average elapsed time of 427 seconds.
Rationale
I/O and Cluster wait for TABLE "SJGX_YB.LV_URBAN_TOPAY_TMP" with object
ID 90256 consumed 100% of the database time spent on this SQL statement.
Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is .17 active sessions, 13.43% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is .12 active sessions, 9.1% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE "SJGX_YB.INTE_DATA"
with object ID 89405.
Related Object
Database object with ID 89405.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the INSERT statement with
SQL_ID "c1fw0514uxxrs" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 1 full object scans, 263894
physical reads, 414 physical writes and 0 direct reads.
Recommendation 2: Segment Tuning
Estimated benefit is .03 active sessions, 2.35% of total activity.
------------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE
"SJGX_YB.LV_URBAN_TOPAY_TMP" with object ID 90256.
Related Object
Database object with ID 90256.
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 "35vy818ghp687" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 4 full object scans,
1361966 physical reads, 682018 physical writes and 680863 direct reads.
Recommendation 3: Segment Tuning
Estimated benefit is .03 active sessions, 1.98% of total activity.
------------------------------------------------------------------
Action
Run "Segment Advisor" on TABLE "SJGX_YB.LV_INDIPAR" with object ID
89473.
Related Object
Database object with ID 89473.
Action
Investigate application logic involving I/O on TABLE
"SJGX_YB.LV_INDIPAR" with object ID 89473.
Related Object
Database object with ID 89473.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the INSERT statement with
SQL_ID "f64qufxuu0r5g" is responsible for 100% of "User I/O" and
"Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 18 full object scans,
36784620 physical reads, 0 physical writes and 0 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .25 active sessions, 19.52% of total activity.
Finding 3: Undersized Redo Log Buffer
Impact is .13 active sessions, 10.23% of total activity.
--------------------------------------------------------
Waits for redo log buffer space were consuming significant database time.
Recommendation 1: Host Configuration
Estimated benefit is .13 active sessions, 10.23% of total activity.
-------------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 1590 K and
the average time per write was 129 milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Configuration" was consuming significant database time.
Impact is .23 active sessions, 17.94% of total activity.
Finding 4: Log File Switches
Impact is .1 active sessions, 7.59% of total activity.
------------------------------------------------------
Log file switch operations were consuming significant database time while
waiting for checkpoint completion.
This problem can be caused by use of hot backup mode on tablespaces. DML to
tablespaces in hot backup mode causes generation of additional redo.
Recommendation 1: Database Configuration
Estimated benefit is .1 active sessions, 7.59% of total activity.
-----------------------------------------------------------------
Action
Verify whether incremental shipping was used for standby databases.
Recommendation 2: Database Configuration
Estimated benefit is .1 active sessions, 7.59% of total activity.
-----------------------------------------------------------------
Action
Increase the size of the log files to 2048 M to hold at least 20 minutes
of redo information.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Configuration" was consuming significant database time.
Impact is .23 active sessions, 17.94% of total activity.
Finding 5: Buffer Busy - Hot Objects
Impact is .06 active sessions, 4.37% of total activity.
-------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.
Recommendation 1: Schema Changes
Estimated benefit is 0 active sessions, .35% of total activity.
---------------------------------------------------------------
Action
Consider using ORACLE's recommended solution of automatic segment space
management in a locally managed tablespace for the tablespace "SYSTEM"
containing the TABLE "SYS.AUD$" with object ID 407. Alternatively, you
can move this object to a different tablespace that is locally managed
with automatic segment space management.
Related Object
Database object with ID 407.
Rationale
There was significant read and write contention on TABLE "SYS.AUD$" with
object ID 407.
Related Object
Database object with ID 407.
Recommendation 2: Schema Changes
Estimated benefit is 0 active sessions, .35% of total activity.
---------------------------------------------------------------
Action
Consider partitioning the TABLE "SYS.AUD$" with object ID 407 in a
manner that will evenly distribute concurrent DML across multiple
partitions.
Related Object
Database object with ID 407.
Recommendation 3: Schema Changes
Estimated benefit is 0 active sessions, .35% of total activity.
---------------------------------------------------------------
Action
A temporary solution may be achieved by increasing the number of free
lists in segment "SYS.AUD$".
Related Object
Database object with ID 407.
Action
A temporary solution may be achieved by increasing the number of free
list groups in segment "SYS.AUD$".
Related Object
Database object with ID 407.
Rationale
There was significant read and write contention on TABLE "SYS.AUD$" with
object ID 407.
Related Object
Database object with ID 407.
Symptoms That Led to the Finding:
---------------------------------
Read and write contention on database blocks was consuming significant
database time.
Impact is .06 active sessions, 4.37% of total activity.
Wait class "Concurrency" was consuming significant database time.
Impact is .06 active sessions, 4.38% of total activity.
Finding 6: Commits and Rollbacks
Impact is .04 active sessions, 2.72% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Recommendation 1: Host Configuration
Estimated benefit is .04 active sessions, 2.72% of total activity.
------------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 1590 K and
the average time per write was 129 milliseconds.
Rationale
The total I/O throughput on redo log files was 0 K per second for reads
and 2.9 M per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 100% by Log Writer, 0% by Archiver, 0% by Streams AQ and 0% by
all other activity.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .04 active sessions, 2.72% of total activity.
Finding 7: "Network" Wait Class
Impact is .03 active sessions, 2.24% of total activity.
-------------------------------------------------------
Wait class "Network" was consuming significant database time.
No recommendations are available.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.