ADDM Report for Task 'TASK_751' ------------------------------- Analysis Period --------------- AWR snapshot range from 688 to 689. Time period starts at 18-SEP-09 03.17.33 AM Time period ends at 18-SEP-09 03.27.22 AM Analysis Target --------------- Database 'LINUXS' with DB ID 570420430. Database version 11.2.0.1.0. ADDM performed an analysis of instance linuxs, numbered 1 and hosted at stadf26. Activity During the Analysis Period ----------------------------------- Total database time was 3304 seconds. The average number of active sessions was 5.61. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity -------------------------------- ------------------- --------------- 1 Top SQL Statements 2.17 | 38.67 2 2 Session Connect and Disconnect 1.8 | 32.11 1 3 Commits and Rollbacks 1.23 | 22.01 1 4 Buffer Busy - Hot Objects .68 | 12.06 2 5 Shared Pool Latches .56 | 9.96 1 6 Buffer Busy - Hot Block .56 | 9.94 2 7 Soft Parse .27 | 4.87 2 8 CPU Usage .27 | 4.83 2 9 Unusual "Concurrency" Wait Event .12 | 2.18 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is 2.17 active sessions, 38.67% 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 2.02 active sessions, 35.94% of total activity. -------------------------------------------------------------------- Action Investigate the UPDATE statement with SQL_ID "b6vvz2u69xdma" 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 b6vvz2u69xdma. update myemp set sal = :sal where empno = :id Rationale The SQL spent only 28% 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: 98% for SQL execution, 2% for parsing, 0% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "b6vvz2u69xdma" was executed 10000 times and had an average elapsed time of 0.075 seconds. Rationale Waiting for event "buffer busy waits" in wait class "Concurrency" accounted for 29% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Rationale Waiting for event "log file sync" in wait class "Commit" accounted for 20% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Rationale Waiting for event "latch: In memory undo latch" in wait class "Concurrency" accounted for 9% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Recommendation 2: SQL Tuning Estimated benefit is .15 active sessions, 2.73% of total activity. ------------------------------------------------------------------ Action Investigate the SELECT statement with SQL_ID "087hdmn8v6ur7" 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 087hdmn8v6ur7. select empno, ename from myemp where empno > :EMPNO order by empno Action Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID "087hdmn8v6ur7". Related Object SQL statement with SQL_ID 087hdmn8v6ur7. select empno, ename from myemp where empno > :EMPNO order by empno Rationale The SQL spent only 28% 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 "087hdmn8v6ur7" was executed 10000 times and had an average elapsed time of 0.01 seconds. Finding 2: Session Connect and Disconnect Impact is 1.8 active sessions, 32.11% of total activity. -------------------------------------------------------- Session connect and disconnect calls were consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is 1.8 active sessions, 32.11% of total activity. ------------------------------------------------------------------- Action Investigate application logic for possible reduction of connect and disconnect calls. For example, you might use a connection pool scheme in the middle tier. Finding 3: Commits and Rollbacks Impact is 1.23 active sessions, 22.01% 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 1.23 active sessions, 22.01% 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 4 K and the average time per write was 4 milliseconds. Rationale The total I/O throughput on redo log files was 0 K per second for reads and 365 K 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 1.23 active sessions, 22.01% of total activity. Finding 4: Buffer Busy - Hot Objects Impact is .68 active sessions, 12.06% of total activity. -------------------------------------------------------- Read and write contention on database blocks was consuming significant database time. Recommendation 1: Schema Changes Estimated benefit is .56 active sessions, 9.94% of total activity. ------------------------------------------------------------------ Action Consider rebuilding the TABLE "OCIHOL.MYEMP" with object ID 64829 using a higher value for PCTFREE. Related Object Database object with ID 64829. Rationale The UPDATE statement with SQL_ID "b6vvz2u69xdma" was significantly affected by "buffer busy" waits. Related Object SQL statement with SQL_ID b6vvz2u69xdma. update myemp set sal = :sal where empno = :id Recommendation 2: Schema Changes Estimated benefit is .1 active sessions, 1.77% of total activity. ----------------------------------------------------------------- Action Consider rebuilding the TABLE "SYS.AUD$" with object ID 383 using a higher value for PCTFREE. Related Object Database object with ID 383. Symptoms That Led to the Finding: --------------------------------- Read and write contention on database blocks was consuming significant database time. Impact is .68 active sessions, 12.06% of total activity. Wait class "Concurrency" was consuming significant database time. Impact is 1.45 active sessions, 25.9% of total activity. Finding 5: Shared Pool Latches Impact is .56 active sessions, 9.96% of total activity. ------------------------------------------------------- Contention for latches related to the shared pool was consuming significant database time. Waits for "library cache: mutex X" amounted to 7% of database time. Waits for "latch: row cache objects" amounted to 1% of database time. Hard parsing of SQL statements was not consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .56 active sessions, 9.96% of total activity. ------------------------------------------------------------------ Action Investigate the cause for latch contention using the given blocking sessions or modules. Rationale The session with ID 40 and serial number 20660 in instance number 1 was the blocking session responsible for 14% of this recommendation's benefit. Symptoms That Led to the Finding: --------------------------------- Wait class "Concurrency" was consuming significant database time. Impact is 1.45 active sessions, 25.9% of total activity. Finding 6: Buffer Busy - Hot Block Impact is .56 active sessions, 9.94% of total activity. ------------------------------------------------------- A hot data block with concurrent read and write activity was found. The block belongs to segment "OCIHOL.MYEMP" and is block 63974 in file 1. Recommendation 1: Application Analysis Estimated benefit is .56 active sessions, 9.94% of total activity. ------------------------------------------------------------------ Action Investigate application logic to find the cause of high concurrent read and write activity to the data present in this block. Related Object Database block with object number 64829, file number 1 and block number 63974. Rationale The SQL statement with SQL_ID "b6vvz2u69xdma" spent significant time on "buffer busy" waits for the hot block. Related Object SQL statement with SQL_ID b6vvz2u69xdma. update myemp set sal = :sal where empno = :id Recommendation 2: Schema Changes Estimated benefit is .56 active sessions, 9.94% of total activity. ------------------------------------------------------------------ Action Consider rebuilding the TABLE "OCIHOL.MYEMP" with object ID 64829 using a higher value for PCTFREE. Related Object Database object with ID 64829. Symptoms That Led to the Finding: --------------------------------- Read and write contention on database blocks was consuming significant database time. Impact is .68 active sessions, 12.06% of total activity. Wait class "Concurrency" was consuming significant database time. Impact is 1.45 active sessions, 25.9% of total activity. Finding 7: Soft Parse Impact is .27 active sessions, 4.87% of total activity. ------------------------------------------------------- Soft parsing of SQL statements was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .27 active sessions, 4.87% of total activity. ------------------------------------------------------------------ Action Investigate application logic to keep open the frequently used cursors. Note that cursors are closed by both cursor close calls and session disconnects. Recommendation 2: Database Configuration Estimated benefit is .27 active sessions, 4.87% of total activity. ------------------------------------------------------------------ Action Consider increasing the session cursor cache size by increasing the value of parameter "session_cached_cursors". Rationale The value of parameter "session_cached_cursors" was "50" during the analysis period. Symptoms That Led to the Finding: --------------------------------- Contention for latches related to the shared pool was consuming significant database time. Impact is .56 active sessions, 9.96% of total activity. Wait class "Concurrency" was consuming significant database time. Impact is 1.45 active sessions, 25.9% of total activity. Finding 8: CPU Usage Impact is .27 active sessions, 4.83% of total activity. ------------------------------------------------------- Host CPU was a bottleneck and the instance was consuming 73% of the host CPU. All wait times will be inflated by wait for CPU. Host CPU consumption was 96%. Recommendation 1: Application Analysis Estimated benefit is 1.8 active sessions, 32.03% of total activity. ------------------------------------------------------------------- Action Look at the "Top SQL Statements" finding for SQL statements consuming significant time on CPU. For example, the UPDATE statement with SQL_ID "b6vvz2u69xdma" is responsible for 32% of CPU usage during the analysis period. Recommendation 2: Host Configuration Estimated benefit is .27 active sessions, 4.83% of total activity. ------------------------------------------------------------------ Action Consider adding more CPUs to the host or adding instances serving the database on other hosts. Action Also consider using Oracle Database Resource Manager to prioritize the workload from various consumer groups. Finding 9: Unusual "Concurrency" Wait Event Impact is .12 active sessions, 2.18% of total activity. ------------------------------------------------------- Wait event "latch: In memory undo latch" in wait class "Concurrency" was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .12 active sessions, 2.18% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "latch: In memory undo latch" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Action Look at the "Top SQL Statements" finding for SQL statements consuming significant time on the "latch: In memory undo latch" wait event. For example, the UPDATE statement with SQL_ID "b6vvz2u69xdma" is responsible for 100% of these waits. Recommendation 2: Application Analysis Estimated benefit is .12 active sessions, 2.18% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "latch: In memory undo latch" waits in Module "stage2@stadf26 (TNS V1-V3)". Recommendation 3: Application Analysis Estimated benefit is .12 active sessions, 2.18% of total activity. ------------------------------------------------------------------ Action Investigate the cause for high "latch: In memory undo latch" waits in Service "SYS$USERS". Symptoms That Led to the Finding: --------------------------------- Wait class "Concurrency" was consuming significant database time. Impact is 1.45 active sessions, 25.9% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. Wait class "Network" was not consuming significant database time. Wait class "User I/O" was not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time.