1 Reply Latest reply: Jul 20, 2009 11:41 AM by damorgan RSS

    how to apply recommendations given by addm report

    user11221081
      Hi Gurus

      Actually the addm report of my test database is giving some recommendations and i am not getting ,how to apply those on my database.

      so i am putting some data here,if anybody could give me some hint regarding that it would be of great help as i am new in dba.



      Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time. RECOMMENDATION 1: Application Analysis, 9.9% benefit (147 seconds) ACTION: Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions. RATIONALE: The application was performing 112 transactions per minute with an average redo size of 3655 bytes per transaction. RECOMMENDATION 2: Host Configuration, 9.9% benefit (147 seconds) 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 3 K and the average time per write was 4 milliseconds. SYMPTOMS THAT LED TO THE FINDING: Wait class "Commit" was consuming significant database time. (9.9% impact [147 seconds]) FINDING 6: 8% impact (119 seconds) ---------------------------------- Wait event "process startup" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 8% benefit (119 seconds) ACTION: Investigate the cause for high "process startup" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation. RATIONALE: The SQL statement with SQL_ID "NULL-SQLID" was found waiting for "process startup" wait event. RELEVANT OBJECT: SQL statement with SQL_ID NULL-SQLID RECOMMENDATION 2: Application Analysis, 8% benefit (119 seconds) ACTION: Investigate the cause for high "process startup" waits in Service "SYS$BACKGROUND". FINDING 7: 6.3% impact (93 seconds)

      NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: Hard parses due to cursor environment mismatch were not consuming significant database time. Hard parsing SQL statements that encountered parse errors was not consuming significant database time. Parse errors due to inadequately sized shared pool were not consuming significant database time. Hard parsing due to cursors getting aged out of shared pool was not consuming significant database time. Hard parses due to literal usage and cursor invalidation were not consuming significant database time. FINDING 8: 4.3% impact (63 seconds) ----------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 4.3% benefit (63 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. SYMPTOMS THAT LED TO THE FINDING: Wait class "User I/O" was consuming significant database time. (13% impact [191 seconds]) FINDING 9: 4.1% impact (60 seconds) ----------------------------------- Buffer cache writes due to small log files were consuming significant database time. NO RECOMMENDATIONS AVAILABLE SYMPTOMS THAT LED TO THE FINDING: The throughput of the I/O subsystem was significantly lower than expected. (4.3% impact [63 seconds]) Wait class "User I/O" was consuming significant database time. (13% impact [191 seconds]) FINDING 10: 3.5% impact (51 seconds) ------------------------------------ Wait event "class slave wait" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 3.5% benefit (51 seconds) ACTION: Investigate the cause for high "class slave wait" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Administrative" was not consuming significant database time. Wait class "Application" was not consuming significant database time. Wait class "Cluster" was not consuming significant database time. Wait class "Concurrency" 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 "Scheduler" was not consuming significant database time. 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. An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail.

      regards
      Richa
        • 1. Re: how to apply recommendations given by addm report
          damorgan
          I'm not sure what about the recommendations you don't understand as what you posted seems quite clear. Take #1 for example:
          Investigate application logic for possible reduction in the number of COMMIT operations by increasing the size of transactions.
          is telling you that it appears you are doing incremental commits. Are you? Can you change it if you are?

          When you respond please include full version information.