ADDM Report for Task 'TASK_982' ------------------------------- Analysis Period --------------- AWR snapshot range from 876 to 877. Time period starts at 25-SEP-09 11.46.46 AM Time period ends at 25-SEP-09 11.51.34 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 4761 seconds. The average number of active sessions was 16.53. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity ------------------------- ------------------- --------------- 1 Top SQL Statements 15.92 | 96.31 2 2 Buffer Busy - Hot Objects 9.51 | 57.51 1 3 Buffer Busy - Hot Block 9.22 | 55.76 2 4 Row Lock Waits 3.07 | 18.55 1 5 Commits and Rollbacks .91 | 5.51 1 6 Buffer Cache Latches .34 | 2.03 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is 15.92 active sessions, 96.31% 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 14.77 active sessions, 89.37% 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 8% 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 "b6vvz2u69xdma" was executed 20000 times and had an average elapsed time of 0.2 seconds. Rationale Waiting for event "buffer busy waits" in wait class "Concurrency" accounted for 68% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Rationale Waiting for event "enq: TX - row lock contention" in wait class "Application" accounted for 19% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Rationale Waiting for event "latch: cache buffers chains" in wait class "Concurrency" accounted for 2% of the database time spent in processing the SQL statement with SQL_ID "b6vvz2u69xdma". Recommendation 2: SQL Tuning Estimated benefit is 1.15 active sessions, 6.94% of total activity. ------------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "087hdmn8v6ur7". 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 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 46% 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 "087hdmn8v6ur7" was executed 20000 times and had an average elapsed time of 0.02 seconds. Rationale Waiting for event "buffer busy waits" in wait class "Concurrency" accounted for 43% of the database time spent in processing the SQL statement with SQL_ID "087hdmn8v6ur7". Finding 2: Buffer Busy - Hot Objects Impact is 9.51 active sessions, 57.51% of total activity. --------------------------------------------------------- Read and write contention on database blocks was consuming significant database time. Recommendation 1: Schema Changes Estimated benefit is 9.22 active sessions, 55.76% 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 Symptoms That Led to the Finding: --------------------------------- Read and write contention on database blocks was consuming significant database time. Impact is 9.51 active sessions, 57.51% of total activity. Wait class "Concurrency" was consuming significant database time. Impact is 9.96 active sessions, 60.28% of total activity. Finding 3: Buffer Busy - Hot Block Impact is 9.22 active sessions, 55.76% 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 9.22 active sessions, 55.76% 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 Rationale The SQL statement with SQL_ID "087hdmn8v6ur7" spent significant time on "buffer busy" waits for the hot block. Related Object SQL statement with SQL_ID 087hdmn8v6ur7. select empno, ename from myemp where empno > :EMPNO order by empno Recommendation 2: Schema Changes Estimated benefit is 9.22 active sessions, 55.76% 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 9.51 active sessions, 57.51% of total activity. Wait class "Concurrency" was consuming significant database time. Impact is 9.96 active sessions, 60.28% of total activity. Finding 4: Row Lock Waits Impact is 3.07 active sessions, 18.55% of total activity. --------------------------------------------------------- SQL statements were found waiting for row lock waits. Recommendation 1: Application Analysis Estimated benefit is 3.07 active sessions, 18.55% of total activity. -------------------------------------------------------------------- Action Significant row contention was detected in the TABLE "OCIHOL.MYEMP" with object ID 64829. Trace the cause of row contention in the application logic using the given blocked SQL. Related Object Database object with ID 64829. Rationale The SQL statement with SQL_ID "b6vvz2u69xdma" was blocked on row locks. Related Object SQL statement with SQL_ID b6vvz2u69xdma. update myemp set sal = :sal where empno = :id Rationale The session with ID 29 and serial number 6 in instance number 1 was the blocking session responsible for 14% of this recommendation's benefit. Rationale The session with ID 109 and serial number 14 in instance number 1 was the blocking session responsible for 12% of this recommendation's benefit. Rationale The session with ID 25 and serial number 6 in instance number 1 was the blocking session responsible for 11% of this recommendation's benefit. Rationale The session with ID 111 and serial number 6 in instance number 1 was the blocking session responsible for 11% of this recommendation's benefit. Symptoms That Led to the Finding: --------------------------------- Wait class "Application" was consuming significant database time. Impact is 3.07 active sessions, 18.55% of total activity. Finding 5: Commits and Rollbacks Impact is .91 active sessions, 5.51% 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 .91 active sessions, 5.51% 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 13 K and the average time per write was 2 milliseconds. Rationale The total I/O throughput on redo log files was 0 K per second for reads and 1.5 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 .91 active sessions, 5.51% of total activity. Finding 6: Buffer Cache Latches Impact is .34 active sessions, 2.03% of total activity. ------------------------------------------------------- Contention on buffer cache latches was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .34 active sessions, 2.03% of total activity. ------------------------------------------------------------------ Action Look at the "Top SQL Statements" finding for SQL statements consuming significant time on the "latch: cache buffers chains" wait event. For example, the UPDATE statement with SQL_ID "b6vvz2u69xdma" is responsible for 100% of these waits. Symptoms That Led to the Finding: --------------------------------- Wait class "Concurrency" was consuming significant database time. Impact is 9.96 active sessions, 60.28% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- 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. Wait class "User I/O" 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.