Oracle performance tuning-ASH, AWR, and ADDM

The three major advantages of Oracle performance adjustment are ASH, AWR, and addm ash (Active Session History). Based on the V $ SESSION, the ASH sample is performed once per second to record the event waiting for the Active Session. Non-active sessions are not sampled, and the sampling is completed by the newly introduced background process MMNL. The minimum and maximum values of ASH buffers are 1 MB and 30 MB. Records Data in the memory. The expected value is to record the content for one hour. Generate an ASH Report: SQLPLUS> @? /Rdbms/ashrpt. SQL ASH memory records are always limited. To save historical data, the automatic load information library (AWR) is introduced to be completed by the background process MMON. ASH information is also collected and written into the AWR Load Library. Because the memory is not enough, the MMNL process writes the information to the AWR Load Library after the ASH is full. It is unacceptable to write all ASH data. Therefore, only 10% of the collected data is written, and direct-pathinsert is used to reduce log generation as much as possible, thus minimizing the impact on database performance. The ASH information written to the AWR load database is recorded in the wrh $ active_session_hist basic table of AWR. wrh $ active_session_hist is a partition table, and Oracle will automatically clean up the data. AWR (Automatic Workload Repository) is a new feature in Oracle 10 Gb, similar to statspack before 10 Gb. However, it is easier to use than statspack, and provides many more performance indicators than statspack, which can help DBAs better identify database performance bottlenecks. AWR is automatically started after Oracle is installed, and no special settings are required. The collected statistics are stored in the SYSAUX tablespace SYS mode and named in WRM $ _ * And WRH $ _ * formats. By default, the collected statistics are retained for the last seven days. Every hour, the collected information is written to the database. This operation is completed by an MMON process. Data Classification stored in AWR: WRM $ table stores AWR metadata (awrinfo. SQL script) WRH $ table store historical data of the sampled snapshot (awrrpt. SQL script) WRI $ table store data related to the recommended functions of the database (ADDM-related data) I. generate an AWR Report: SQL> @? /Rdbms/admin/awrrpt generate an AWR report based on the wizard. Note that there is no downtime in the middle when you select a time range (if there is a blank line in the displayed time range, there is a downtime ). When selecting a report type, the default HTML is generally used for viewing. II. view the database AWR settings: SQL> select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL---------------------------------------------------------------------------RETENTION ----------------------------------------------------------------- + 00000 01:00:00. 0 (collected every hour) + 00007 00:00:00. 0 (retain 7 days) 3. modify the default settings: beginDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval => 20, retention => 2*24*60); en D. Change it to collect statistics every 20 minutes, and retain the metering information of the last two days. 4. Manually collect the statistical information of the database: exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; you can also use the DBMS_WORKLOAD_REPOSITORY package to complete baseline and default settings modification. ADDM (Automatic Database Diagnostic Monitor AWR) is an internal advisor system in Oracle. It can automatically complete some optimization suggestions for the most Database and provide SQL optimization and index creation, statistics Collection and other suggestions. ADDM report generation: SQLPLUS> @? /Rdbms/addmrpt. SQL Oracle performance adjustment the most important thing is to adjust the SQL statements that affect the performance most. In an application, only SQL statements or SQL statements can be affected to the database. We cannot rely solely on enhanced hardware to modify system and database parameters to improve database performance. Pay more attention to the SQL statements that most affect performance. The ASH report, AWR report, and ADDM report can all be used to find the SQL tool that most affects performance. When analyzing ASH and AWR reports, the most important thing is to pay attention to SQL Statistics. In SQL Statistics, the two most important indicators are SQL ordered byGets and SQL ordered byReads. A large number of Gets (logical reads) occupy a large amount of CPU time. A large number of Reads may cause IO bottlenecks. In general, a large number of Gets will come along with a large number of Reads. Of course, we can reduce the number of Reads by increasing the size of the SGA. These two indicators are used to find the SQL statements that most affect performance. This is the first and necessary indicator. In the next step, you can create indexes and adjust the SQL statements to Improve the Performance of SQL statement execution. Reduce the high Gets and Reads that occur during SQL Execution. Of course, the overall performance impact is also related to excutions. If this SQL statement is executed too many times, the total amount will be large. Therefore, you can reduce the number of SQL executions by caching the application. Another issue that needs to be noted is that during the development process, SQL must use bind variables to reduce hard parsing (a large amount of hard parsing also consumes a lot of CPU time, occupies a large number of Latch ). There is a principle in the development process: small transactions. The operation is completed and submitted in a timely manner. We have only one unique purpose in using these methods and reports: to find the SQL statements that affect the system performance the most. The next step for finding SQL is to adjust it. When we monitor the database, if it is a problem that is currently occurring, we can use v $ session + v $ sqlarea to find the SQL statement with the worst performance. If this happens within an hour, we can generate an ASH report to find the SQL statement. If it is more than one hour or a few days, we can use the AWR report to find out how many hours, the most influential SQL statement in the system for a few days. The ADDM report is based on the AWR library and can be saved for 30 days by default. We can also directly query the attempt: v $ session (currently in progress) v $ session_wait (currently in progress) v $ session_wait_history (the last 10 waiting events of the session) v $ active_session_history (The ASH Collection information in the memory, theoretically 1 hour) wrh $ _ active_session_history (the ASH information written into the AWR library, theoretically more than 1 hour) dba_hist_active_sess_history (view generated based on wrh $ _ active_session_history)