AWR
Introduction to Performance optimization ToolsThis article mainly introduces oracle10g new awr tool, I often need to use AWR for database performance optimization and fault diagnosis in my work. AWR (Automatic workload Repository) is a new tool for collecting database statistics from Oracle 10g. It mainly includes awr memory area, historical data storage files and ash and other parts. The main contents of the AWR report include the following sections,
Foreword part:This is the first section of the AWR report that describes the environment including database name, database version, whether it is a RAC node, snapshot Report collection time, and so on.
Summary section:Contains the wait event segment, the Load profile section, the instance efficiency statistics segment, the Shared Pool statistics section, the Cache size segment, the most important of which is the waiting event segment, which tells us what performance bottlenecks the database encounters during the snapshot time and which are the primary candidates for performance tuning or problem diagnosis. The following top time event data picks the AWR report generated by the self database.
| Event |
Waits |
Time (s) |
AVG Wait (ms) |
% Total call time |
Wait Class |
| CPU time |
|
17,306 |
|
99.6 |
|
| DB file Scattered read |
19,469,037 |
4,448 |
0 |
25.6 |
User I/O |
| Log file Parallel write |
492,258 |
221 |
0 |
1.3 |
System I/O |
| Log file Sync |
253,519 |
143 |
1 |
.8 |
Commit |
| Read by |
421,942 |
73 |
0 |
.4 |
User I/O |
The report shows that "db file scattered read" is the most important waiting event during this snapshot time because the session is waiting for a "multiblock IO" to complete, usually because of a full table scan or an index quick scan on a large table. You can then see which SQL executes these queries, which tables the query operations are concentrated on, and check SQL plan to see if the indexes are used.
SQL
section:Invalid SQL statements are a major cause of poor performance, this section of the time interval for the SQL according to the execution time, logical reading, disk reading and other indicators to classify and sort, and Statspack is different from the AWR report can directly find the SQL text and send the requested client process information, Greatly improve the efficiency of SQL analysis.
Instance Activity Statistics Section:This section is a snapshot of the various internal modules of the example activities and various resource usage statistics, mainly including CPU usage, sql*net messages, linked row access and PGA usage.
section of the Statistical section:Tell which segments (including tables and indexes) experience the highest disk reads during the snapshot, which can help us decide whether to rebuild the index or partition the segments to reduce the I/O that occurs on those data files. Awr Some common operations mainly include modifying the collection and save time of snapshots, creating baseline, exporting and migrating awr data. Here are some ways to use these features. By default, AWR collects a system load snapshot automatically every 1 hours, and this snapshot is saved for a week by default. However, ORACLE10G provides the following commands to modify the snapshot collection interval and save time. Execute dbms_workload_repository.modify_snapshot_settings (interval => 120,retention => 20160); The above command modifies the snapshot collection interval of 2 hours, with a retention time of two weeks. You can create baseline for AWR data and save it for future analysis and comparison. The specific commands are as follows, execute Dbms_workload_repository.create_baseline (start_snap_id =>1, end_snap_id =>100, baseline_name = > ' Baseline name ', dbid => yourdbid); AWR data can be exported and migrated to other databases for later analysis. oracle10g Release2 provides two new tools to complete the export and migration. The work of AWR data. Dbms_swrf_internal. Awr_extract is used to export data using the following methods, Begin dbms_swrf_internal. Awr_extract ( dmpfile => ' awr_data.dmp ', dmpdir => ' Tmp_dir ', bid => 302,      &NBSp eid => 305); End Where the Dmpfile parameter specifies the name of the AWR data file to be exported, Dmpdir specifies the directory path where the exported file will be stored, bid the starting snapshot number, and the Eid is the ending snapshot number. Dbms_swrf_internal is used to migrate awr data files to other databases. The process of importing AWR data is divided into two steps, first using Dbms_swrf_internal. Awr_load method to import data into a temporary mode, this example is Awr_test (also can define their own name), the specific method is as follows, begin dbms_swrf_internal. Awr_load ( schname => ' awr_test ', Dmpfile => ' Awr_data ', dmpdir => ' Tmp_dir '); End The next step is to transfer the AWR data to the SYS mode, operating as follows, exec dbms_swrf_internal. Move_to_awr (schname => ' TEST '); This method of AWR data import has been completed, and you can now use this approach to create a database dedicated to storing AWR data for centralizing the management and analysis of performance statistics for multiple database instances. Here's an example of my work that shows how to use AWR to adjust and diagnose databases in Oracle 10g. After running for a week, our application found that the CPU utilization of the database server was increasing, further checking the AWR report, the AWR report in the last day shows that the system has a large number of "db file scattered read" Waiting for the event to occur, resulting in a variety of reasons for this phenomenon, This could be an SQL statement problem, a lack of indexes on the related table, or an I/O contention. By checking the "top SQL" of the buffer get and physical reads part, found that in the 5 consecutive days, some SQL has been querying the same log table, while there is a large number of concurrent inserts on this table, after running for a week, the table data volume reached tens of millions. Check these SQL plan to find these checksThe index on the table was not used, so we solved the problem by rebuilding the index, modifying the log table, and applying the design of the periodic dump log table. The general application user mainly uses the data access service. The user does not care about how the database management system realizes these services the evolution of DBMS system is observed from the perspective of evolutionism, and it is found that the types of data that users require are increasing, including structure data and multimedia data, and the function of data access service provided by DBMS system is increasing, Now the advanced DBMS system can use the cluster scheme, according to the different application types and workload changes, automatically adjust the computing Resources Redistribution Computing task, and can load balance between multiple instances, automatically choose which instance to provide access services. There are no formulas for optimization and fault diagnosis of database and application systems, and they vary with application types and application attributes.
