ADDM Report for Task 'TASK_125' ------------------------------- Analysis Period --------------- AWR snapshot range from 70 to 71. Time period starts at 05-OCT-10 04.00.40 PM Time period ends at 05-OCT-10 05.00.05 PM Analysis Target --------------- Database 'DB11G' with DB ID 196335337. Database version 11.2.0.2.0. ADDM performed an analysis of instance DB11G, numbered 1 and hosted at oel5-11gR2-demo.localdomain. Activity During the Analysis Period ----------------------------------- Total database time was 1522 seconds. The average number of active sessions was .43. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity --------------------- ------------------- --------------- 1 Virtual Memory Paging .43 | 100 1 2 Top SQL Statements .19 | 44.19 4 3 PL/SQL Execution .08 | 19.77 2 4 Commits and Rollbacks .04 | 9.15 2 5 "User I/O" wait Class .02 | 3.93 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Virtual Memory Paging Impact is .43 active sessions, 100% of total activity. ------------------------------------------------------ Significant virtual memory paging was detected on the host operating system. Recommendation 1: Host Configuration Estimated benefit is .43 active sessions, 100% of total activity. ----------------------------------------------------------------- Action Host operating system was experiencing significant paging but no particular root cause could be detected. Investigate processes that do not belong to this instance running on the host that are consuming significant amount of virtual memory. Also consider adding more physical memory to the host. Finding 2: Top SQL Statements Impact is .19 active sessions, 44.19% 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 .09 active sessions, 22.09% of total activity. ------------------------------------------------------------------- Action Investigate the PL/SQL statement with SQL_ID "0w2qpuc6u2zsp" 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 0w2qpuc6u2zsp. BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; Rationale The SQL Tuning Advisor cannot operate on PL/SQL statements. Rationale Database time for this SQL was divided as follows: 37% for SQL execution, 0% for parsing, 63% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "0w2qpuc6u2zsp" was executed 7094 times and had an average elapsed time of 0.1 seconds. Recommendation 2: SQL Tuning Estimated benefit is .04 active sessions, 9.3% of total activity. ----------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "c13sma6rkr27c". Related Object SQL statement with SQL_ID c13sma6rkr27c. SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.CATEGORY_ID = :B3 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND INVENTORIES.WAREHOUSE_ID = :B2 AND ROWNUM < :B1 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 "c13sma6rkr27c" was executed 21305 times and had an average elapsed time of 0.0079 seconds. Rationale Top level calls to execute the PL/SQL statement with SQL_ID "0w2qpuc6u2zsp" are responsible for 100% of the database time spent on the SELECT statement with SQL_ID "c13sma6rkr27c". Related Object SQL statement with SQL_ID 0w2qpuc6u2zsp. BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END; Recommendation 3: SQL Tuning Estimated benefit is .03 active sessions, 6.98% of total activity. ------------------------------------------------------------------ Action Investigate the PL/SQL statement with SQL_ID "147a57cxq3w5y" 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 147a57cxq3w5y. BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; Rationale The SQL Tuning Advisor cannot operate on PL/SQL statements. Rationale Database time for this SQL was divided as follows: 17% for SQL execution, 0% for parsing, 83% for PL/SQL execution and 0% for Java execution. Rationale SQL statement with SQL_ID "147a57cxq3w5y" was executed 8494 times and had an average elapsed time of 0.036 seconds. Recommendation 4: SQL Tuning Estimated benefit is .02 active sessions, 5.81% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "0y1prvxqc2ra9". Related Object SQL statement with SQL_ID 0y1prvxqc2ra9. SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL, QUANTITY_ON_HAND FROM PRODUCTS, INVENTORIES WHERE PRODUCTS.PRODUCT_ID = :B2 AND INVENTORIES.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND ROWNUM < :B1 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 "0y1prvxqc2ra9" was executed 25388 times and had an average elapsed time of 0.0023 seconds. Rationale Top level calls to execute the PL/SQL statement with SQL_ID "147a57cxq3w5y" are responsible for 100% of the database time spent on the SELECT statement with SQL_ID "0y1prvxqc2ra9". Related Object SQL statement with SQL_ID 147a57cxq3w5y. BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END; Finding 3: PL/SQL Execution Impact is .08 active sessions, 19.77% of total activity. -------------------------------------------------------- PL/SQL execution consumed significant database time. Recommendation 1: SQL Tuning Estimated benefit is .06 active sessions, 13.95% of total activity. ------------------------------------------------------------------- Action Tune the entry point PL/SQL "SOE.ORDERENTRY.NEWORDER" of type "PACKAGE" and ID 77490. Refer to the PL/SQL documentation for addition information. Rationale 106 seconds spent in executing PL/SQL "SOE.ORDERENTRY.NEWORDER" of type "PACKAGE" and ID 77490. Recommendation 2: SQL Tuning Estimated benefit is .02 active sessions, 5.81% of total activity. ------------------------------------------------------------------ Action Tune the entry point PL/SQL "SOE.ORDERENTRY.BROWSEPRODUCTS" of type "PACKAGE" and ID 77490. Refer to the PL/SQL documentation for addition information. Finding 4: Commits and Rollbacks Impact is .04 active sessions, 9.15% of total activity. ------------------------------------------------------- Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .04 active sessions, 9.15% of total activity. ------------------------------------------------------------------ Action Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. Rationale The application was performing 589 transactions per minute with an average redo size of 2072 bytes per transaction. Recommendation 2: Host Configuration Estimated benefit is .04 active sessions, 9.15% 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 2 K and the average time per write was 1 milliseconds. Rationale The total I/O throughput on redo log files was 0 K per second for reads and 22 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 .04 active sessions, 9.15% of total activity. Finding 5: "User I/O" wait Class Impact is .02 active sessions, 3.93% of total activity. ------------------------------------------------------- Wait class "User I/O" was consuming significant database time. The throughput of the I/O subsystem was not significantly lower than expected. The Oracle instance memory (SGA and PGA) was adequately sized. No recommendations are available. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" 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 99% of the analysis period.