DETAILED ADDM REPORT FOR TASK 'TASK_70193' WITH ID 70193 -------------------------------------------------------- Analysis Period: 05-MAR-2009 from 20:10:46 to 23:00:21 Snapshot Range: from 18116 to 18119 Database Time: 3450 seconds Average Database Load: .3 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 54% impact (1864 seconds) ------------------------------------ Wait class "User I/O" was consuming significant database time. NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: The SGA was adequately sized. FINDING 2: 16% impact (546 seconds) ----------------------------------- PL/SQL execution consumed significant database time. RECOMMENDATION 1: SQL Tuning, 16% benefit (562 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3". RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and PLAN_HASH 1947143079 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')) RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 35 times and had an average elapsed time of 15 seconds. RATIONALE: Average time spent in PL/SQL execution was 15 seconds. FINDING 3: 16% impact (544 seconds) ----------------------------------- Individual SQL statements responsible for significant user I/O wait were found. RECOMMENDATION 1: SQL Tuning, 16% benefit (562 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3". RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and PLAN_HASH 1947143079 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')) RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 35 times and had an average elapsed time of 15 seconds. RATIONALE: Average time spent in User I/O wait events per execution was 15 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (54% impact [1864 seconds]) INFO: The SGA was adequately sized. FINDING 4: 15% impact (503 seconds) ----------------------------------- Individual database segments responsible for significant user I/O wait were found. RECOMMENDATION 1: Segment Tuning, 6.9% benefit (237 seconds) ACTION: Run "Segment Advisor" on TABLE "CCMS.TB_CIF" with object id 42816. RELEVANT OBJECT: database object with id 42816 ACTION: Investigate application logic involving I/O on TABLE "CCMS.TB_CIF" with object id 42816. RELEVANT OBJECT: database object with id 42816 RATIONALE: The I/O usage statistics for the object are: 6 full object scans, 607628 physical reads, 0 physical writes and 0 direct reads. RECOMMENDATION 2: Segment Tuning, 3.1% benefit (108 seconds) ACTION: Run "Segment Advisor" on TABLE "CCMS.WE_TRANSACTION_VARS" with object id 41922. RELEVANT OBJECT: database object with id 41922 ACTION: Investigate application logic involving I/O on TABLE "CCMS.WE_TRANSACTION_VARS" with object id 41922. RELEVANT OBJECT: database object with id 41922 RATIONALE: The I/O usage statistics for the object are: 4 full object scans, 313764 physical reads, 1 physical writes and 0 direct reads. RECOMMENDATION 3: Segment Tuning, 2.3% benefit (79 seconds) ACTION: Investigate application logic involving I/O on TABLE "CCMS.TB_CIF_APP_WF" with object id 41815. RELEVANT OBJECT: database object with id 41815 RATIONALE: The I/O usage statistics for the object are: 4 full object scans, 143308 physical reads, 0 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "8szmwam7fysa3" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')) RECOMMENDATION 4: Segment Tuning, 2.3% benefit (79 seconds) ACTION: Investigate application logic involving I/O on INDEX "CCMS.WE_TRANS_ACT_IDX001" with object id 48062. RELEVANT OBJECT: database object with id 48062 RATIONALE: The I/O usage statistics for the object are: 1 full object scans, 22407 physical reads, 1 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "5dqazqgx3bjzr" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 5dqazqgx3bjzr select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"TRANS_UID" val, ntile(254) over (order by "TRANS_UID") bkt from "CCMS"."WE_TRANSACTION_ACTIVITIES" t where "TRANS_UID" is not null) group by val) group by maxbkt order by maxbkt SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (54% impact [1864 seconds]) INFO: The SGA was adequately sized. FINDING 5: 2.6% impact (91 seconds) ----------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 2.6% benefit (91 seconds) ACTION: Consider increasing the throughput of the I/O subsystem. Oracle's recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance. Alternatively, consider using Oracle's Automatic Storage Management solution. RATIONALE: During the analysis period, the average data files' I/O throughput was 1.9 M per second for reads and 8 K per second for writes. The average response time for single block reads was 4.2 milliseconds. RECOMMENDATION 2: Host Configuration, 1.7% benefit (59 seconds) ACTION: The performance of file /pbccms/data/pccms/sysaux01.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "/pbccms/data/pccms/sysaux01.dbf" RATIONALE: The average response time for single block reads for this file was 27 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (54% impact [1864 seconds]) INFO: The SGA was adequately sized. FINDING 6: 1.4% impact (49 seconds) ----------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. RECOMMENDATION 1: DB Configuration, 0.87% benefit (30 seconds) ACTION: Increase the size of the PGA by setting the value of parameter "pga_aggregate_target" to 240 M. ADDITIONAL INFORMATION: The value of parameter "pga_aggregate_target" was "200 M" during the analysis period. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (54% impact [1864 seconds]) INFO: The SGA was adequately sized. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Application" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. 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. Wait class "Network" was not consuming significant database time. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time. The database's maintenance windows were active during 35% of the analysis period. The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TERMINOLOGY ----------- DATABASE TIME: This is the ADDM's measurement of throughput. From the user's point of view: this is the total amount of time spent by users waiting for a response from the database after issuing a call (not including networking). From the database instance point of view: this is the total time spent by forground processes waiting for a database resource (e.g., read I/O), running on the CPU and waiting for a free CPU (run-queue). The target of ADDM analysis is to reduce this metric as much as possible, thereby reducing the instance's response time. AVERAGE DATABASE LOAD: At any given time we can count how many users (also called 'Active Sessions') are waiting for an answer from the instance. This is the ADDM's measurement for instance load. The 'Average Database Load' is the average of the the load measurement taken over the entire analysis period. We get this number by dividing the 'Database Time' by the analysis period. For example, if the analysis period is 30 minutes and the 'Database Time' is 90 minutes, we have an average of 3 users waiting for a response. IMPACT: Each finding has an 'Impact' associated with it. The impact is the portion of the 'Database Time' the finding deals with. If we assume that the problem described by the finding is completely solved, then the 'Database Time' will be reduced by the amount of the 'Impact'. BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM analysis estimates that the 'Database Time' can be reduced by the 'benefit' amount if all the actions of the recommendation are performed.