Awr Basics
1. Automatic Working Load Warehouse
Oracle collects a large amount of statistical information about performance and motion. This information accumulates in memory and is written regularly to the disk (that is, to the table that makes up the awr). Eventually, this information expires and is rewritten.
1.1. Collect statistics
The level of statistics collection is controlled by the instance parameter statistics_level. This parameter may be set to basic, typical, or all. (Default typical)
- Typical: Allows you to collect all the statistics required for normal adjustments without collecting a collection of statistics that adversely affects performance.
- BASIC: It is virtually forbidden to collect statistics, and there is no performance benefit to evaluate.
- All: Collects extremely detailed statistics related to SQL execution. With advanced SQL statement tuning, you can use the all level, but when you collect statistics, the performance degrades slightly.
The statistics accumulate in memory (in the data structure within the SGA). Statistics only reflect the actions of the instance, so it does not affect the performance of the instance. Statistics are written to the disk at timed intervals (by default, once per hour), which is written to awr. This is referred to as a "snapshot (snapshot)". The amount of statistics that are written to disk is done by the background process (a manageable monitor or mmon process).
The Mmon process directly accesses the memory structure that makes up the SGA, which can also access the statistics in these memory structures. This process can extract data from the SGA without having to pass the session. The only overhead at this time is to actually write the data to AWR. By default, this operation is performed hourly, so it should not have a noticeable impact on runtime performance.
1.2, the size and location of the AWR
AWR is a set of tables located in the Sysaux table space. These tables cannot be repositioned and exist in the Sysman mode. Although we can log in to the database as a user Sysman, we cannot view the AWR. The simplest way to access AWR is the database Control.
Snapshots are stored in AWR for 7 days by default, and this time period is configurable. As a general guideline for allocating storage space, if snapshot collection is performed hourly and the snapshot retention time is 7 days, AWR may require 200~300 space within the Sysaux tablespace. However, this data will always change, depending on the number of sessions will be greatly improved.
1.3. Save the snapshot
The snapshot is cleared after a specific time period, and by default, this period is 7 days. For long-term adjustments, snapshots must be saved over a longer period of time. By default, the Awr snapshot is saved for 7 days, and the ADDM report is saved for 30 days.
2. Diagnosis and adjustment consultant procedure
7 Consultant Programs:
- Automatic Database Diagnostic Monitor (DB Automatic Diagnostic Monitor, abbreviated to ADDM)
- SQL Tuning Advisor (SQL Tuning Advisor)
- SQL Access Advisor (SQL Provider)
- Memory Advisor (RAM Advisor)
- Mean time to Recover (MTTR) Advisor (Avg. recovery Advisor)
- Segment Advisor (Section Advisor)
- Undo Advisor (Revocation Advisor)
2.1. ADDM Consultant Program
The Mmon process automatically runs ADDM whenever a snapshot is generated.
View Reports
[img=500,404 alt=em1 src=]http://www.itpub.net/[/img]
[img=500,266 alt=em2 src=]http://www.itpub.net/[/img]
The most recent operation of all advisors is shown here.
2.2. SQL Tuning Advisor with SQL Access Advisor
SQL Tuning Advisor takes one or more SQL statements as input and studies the structure and execution of these statements. These SQL statements are called SQL Tuning Set, and this advisor involves the following:
- To collect optimizer statistics for the objects involved
- Generate a SQL configuration file using statistics related to statement execution
- Modify the code to use SQL constructs more efficiently
- Rewrite the code to remove possible design errors
SQL Access Advisor also takes SQL Tuning set as its input. This advisor studies whether adding an indexed or materialized view improves SQL execution performance, and also studies whether certain indexes and materialized views actually hinder performance improvements and should be removed.
2.3. Memory Advisor
Memory Advisor is usually implemented: if more memory is allocated for the SGA structure or PGA, performance will be further improved, but the benefits will be reduced. If you need to reduce memory usage because of switching systems, you can save memory. However, if you save too much memory, performance will degrade.
2.4. MTTR Advisor
An instance must be restored after a crash, so it can take a considerable amount of time, the average recovery time (Mean time to Recover, or mttr).
The instance parameter set in seconds Fast_start_recovery_target can control mttr. The shorter the time this parameter is set, the faster the database can be opened after the instance crashes, but the online performance will be even worse.
2.5. Segment Advisor
Segment Advisor looks at the segment and is able to determine whether the amount of space allocated for unused segments is sufficient to perform the shrink space operation.
2.6. Undo Advisor
All DML commands generate undo data. The retention time of the undo data is at least the length of the transaction, and the revocation data is usually stored for a considerable amount of time after the transaction ends.
The algorithm for determining the size of a tablespace is based on the following: the rate at which the undo is generated per second, the number of seconds to store the data that satisfies the query's longest run time, and the possibility of using a flashback query.
3, server-generated alarms
3.1. Alarm system Architecture
The 10G version of the Oracle database is capable of monitoring itself. The Mmon daemon is a manageable monitor that can observe instances and databases. If an indicator is too deviated from expectations, then the Mmon process generates an alarm. All alarms generated by the Mmon process are placed into the queue Alert_que in sys mode.
There are two types of alarms: thresholds (stateful) or no thresholds (stateless). When you configure threshold alarms, you must set some indication values to monitor, such as the percentage of space used in the table space. When the threshold is crossed, an alarm is raised, and the alarm persists until some action, such as adding more space to the table space, is used to make the indicator value lower than the trigger value. No threshold alarm is triggered by an event that does not persist after a occurrence, such as an "ora-1555:snapshot too old" error.
3.2. Set the threshold value
Some alarms are pre-configured with thresholds, and other alarms must be set before they are enabled. For example, for the "tablespace percent full" alarm, the default is to send a warning alarm when 85% of the tablespace is filled and a critical alarm when 97% of the table space is filled. However, the "Average File Read Time" Alarm does not have a default configuration.
3.3. Use baseline
Allowing Oracle to raise alarms when the performance-acceptable performance bias is not compared with a manually selected value, which eliminates the need to calculate an accurate threshold value. In order to do this, you need to create a "baseline".
ASH Basic Knowledge
Eygle
Performance tuning and problem diagnosis are the biggest challenges that any database manager must face and the important administrative tasks that must be accomplished. Based on management simplification and ease-of-use efforts, Oracle has launched the Autometic database Diagnostic Monitor (ADDM), which attempts to make database maintenance easier and easier by addm,oracle.
AWR is a central element of the new management architecture that provides acquisition, processing, maintenance, and access to performance statistics for Oracle internal service components in order to identify problems and self-tuning.
AWR takes a snapshot every 60 minutes, so the most recent snapshot may be an hour ago, so that AWR does not have enough information to perform the current analysis. Typically, the current analysis requires the most recent 5-10 minute details. ASH (Active session history) is therefore introduced to retain historical information about the most recent session activity.
Because the activity of recording sessions is very expensive, ash samples v$session per second and logs events waiting for the session. Inactive sessions are not sampled. This sampling tool is very effective because it directly accesses the ORACLE10G internal structure.
Ash is designed to scroll in memory, and early information is overwritten when needed. Ash can be accessed through the V$active_session_history view. This instance has one row per active session per sample.
Because of the huge amount of data, it is unacceptable to write all of the ash data to disk. This data is typically filtered when writing to a disk. This is done automatically through Mmon and MMNL.
| Sql> SELECT * from V$sgastat where name like '%ash% '; POOL NAME BYTES------------------------------------------------shared pool ASH Buffe RS 6291456 |
Note that the size of ASH buffers is allocated according to the following algorithm:
Min (Shared_pool_size*5%,2m*cpu_count)
| sql> Select Name,value,display_value from v$p Arameter 2 where name in (' Shared_pool_size ', ' Cpu_count ');name VALUE & nbsp; display_value-------------------------------------------------------- --------------cpu_count 4& nbsp 4shared_pool_size 125829120 120m< /td> |
According to this algorithm, the system-assigned ash buffers is 6M. These historical information is recorded in the database and can be queried through V$session_wait_history:
| Sql> desc v$session_wait_historyname type nullable Default Comments---------------------------------------------sid number y & nbsp seq# number y &nb sp; event# NUMBER y event& nbsp varchar2 (y ) & nbsp; p1text varchar2 (+) y & nbsp &NBsp p1 NUMBER y p2text varchar2 (+) y & nbsp; p2 & nbsp; NUMBER y & nbsp p3text varchar2 (+) y & nbsp p3 NUMBER y W ait_time number y &NBsp Wait_count number y |
Clearly ASH/ADDM is another significant improvement in Oracle's management
Generate AWR Ash Report
@?rdbms/admin/awrrpt.sql (@d:\oracle\product\10.2.0\db_1\rdbms\admin\awrrpt.sql) is a previously statspack extension that collects information in more detail, View long-term database conditions, relative to ash.
Note: The document format is easy to read by means of HTML.
@?rdbms/admin/ashrpt.sql View the current database situation because Ash is sampling from v$session per second, and AWR collects much more data than ash.
General collection of database information to combine AWR and ash, date input is incorrect, enter set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK to change the encoding.
@?rdbms/admin/addmrpt. SQL is the equivalent of an expert residing in Oracle and is a self-diagnosing engine. Generates Symptom,problem,infomation, provides recommendations for problem solving, and automatically fixes some specific failures. @?rdbms/admin/awrinfo.sql displays information about AWR, including snapshot information, Sysaux space usage, AWR components, Ash, and more.
Oracle AWR ASH
