为了诊断数据库性能问题，首先查看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 AMTime period ends at 28-SEP-16 06.00.39 AMAnalysis 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 atlocalhost.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 Recommendations Percent of Activity ---------------------------------------- ------------------- --------------- 1 Top SQL Statements .91 | 69.99 62 Top Segments by "User I/O" and "Cluster" .17 | 13.43 33 Undersized Redo Log Buffer .13 | 10.23 14 Log File Switches .1 | 7.59 25 Buffer Busy - Hot Objects .06 | 4.37 36 Commits and Rollbacks .04 | 2.72 17 "Network" Wait Class .03 | 2.24 0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ----------------------------Finding 1: Top SQL StatementsImpact is .91 active sessions, 69.99% of total activity.--------------------------------------------------------SQL statements consuming significant database time were found. Thesestatements 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 BufferImpact 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 SwitchesImpact is .1 active sessions, 7.59% of total activity.------------------------------------------------------Log file switch operations were consuming significant database time whilewaiting for checkpoint completion.This problem can be caused by use of hot backup mode on tablespaces. DML totablespaces 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 ObjectsImpact is .06 active sessions, 4.37% of total activity.-------------------------------------------------------Read and write contention on database blocks was consuming significantdatabase 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 RollbacksImpact is .04 active sessions, 2.72% of total activity.-------------------------------------------------------Waits on event "log file sync" while performing COMMIT and ROLLBACK operationswere 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 ClassImpact 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 databasetime.Hard parsing of SQL statements was not consuming significant database time.