Shulou(Shulou.com)06/01 Report--

Optimize it through case study-- Oracle ADDM

Application environment:

Operating system: RedHat EL55

Oracle: Oracle 10gR2

A brief introduction to ADDM

Before Oracle9i, DBA already had many useful performance analysis tools, such as tkprof, sql_trace, statspack, set event 10046, 10053, and so on. These tools can help DBA quickly locate performance problems. But these tools only give some statistics, which are then optimized by DBA based on their own experience.

Can the machine automatically give optimization suggestions on the basis of statistics? New optimization diagnosis tools have been introduced in Oracle10g: database automatic diagnosis Monitoring tool (Automatic Database Diagnostic Monitor ADDM) and SQL Optimization recommendation tool (SQL Tuning Advisor STA). The combination of these two tools can not only save a lot of optimization time for DBA, but also greatly reduce the risk of system downtime. To put it simply, ADDM is to collect relevant statistics into the automatic workload knowledge base (Automatic Workload Repository AWR), and STA gives optimization recommendations based on these data. For example, if there is a shortage of system resources and there are obvious performance problems, take a statspack snapshot as usual, wait 30 minutes, and do it again. Check the report and find that the'db file scattered read' event is in top 5 events. As a rule of thumb, this event may generally be caused by lack of indexes, lack of up-to-date statistical analysis information, and IO contention when hot tables are placed on a single data file. Based on these experiences, we need to locate the exclusion one by one, such as looking at the query plan of the statement, viewing the last_analysed sub-section of user_tables, checking hot blocks, and so on to finally locate the cause and give optimization suggestions. However, with STA, it can directly give optimization suggestions based on the data collected by ADDM, and even give optimized statements (stealing the job of DBA).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.