Analysis of Oracle 10 Gb automatic workload Database

Oracle Database 10 Gb provides a tool for significant improvement: Automatic workload information library (AWR ). AWR is installed with the database to collect statistics and export metrics.

Fast driver testing

By running the awrrpt. SQL script in the $ ORACLE_HOME/rdbms/admin directory, AWR can immediately get the best description of the reports generated from the collected statistics and measurements. This script looks and feels similar to Statspack. It displays all existing AWR snapshots and requests two specific snapshots as the time interval boundary. It generates two types of output: The text format is similar to the text format of the Statspack report, but it comes from the AWR Information Library) and the default HTML format has all the hyperlinks of parts and child parts ), this provides very user-friendly reports. Run the script to view the report.

Implementation

Now let's take a look at how AWR is designed and built. AWR is essentially an Oracle built-in tool that collects performance-related statistics and exports performance measurements from those statistics to track potential problems. Unlike Statspack, snapshots are automatically collected hourly by a new background process called MMON and its slave processes. To save space, the collected data is automatically cleared 7 days later. You can modify the snapshot frequency and retention time. To view the current settings, you can use the following statement:

select snap_interval, retentionfrom dba_hist_wr_control;SNAP_INTERVAL　 RETENTION------------------- -------------------+00000 01:00:00.0　 +00007 00:00:00.0

　

These SQL statements show that snapshots are collected every hour and the collected data is retained for seven days. To modify the settings-for example, the snapshot interval is 20 minutes and the retention time is two days-you can issue the following command. The parameter is in minutes.

begin　 dbms_workload_repository.modify_snapshot_settings (　interval => 20,　retention => 2*24*60　 );end;

AWR uses several tables to store collected statistics. All Tables are stored in the SYS mode in a specific tablespace named SYSAUX, it is named in the format of WRM $ _ * And WRH $. The former stores metadata information such as checked databases and collected snapshots. The latter stores the actual collected statistics. As you may have guessed, H represents "historical" and M represents "metadata )".) Several views with DBA_HIST _ prefix are built on these tables. These views can be used to compile your own performance diagnostic tools. The view name is directly related to the table. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built on the WRH $ _ SYSMETRIC_SUMMARY table.

The AWR history table collects much more information than Statspack, including tablespace usage, file system usage, and even operating system statistics. The complete list of these tables can be seen from the data dictionary using the following command:

select view_name from user_views where view_name like 'DBA\_HIST\_%' escape '\';

　

View DBA_HIST_METRIC_NAME defines the important Metrics collected by AWR, their groups, and their units. For example, the following is a vertical record format ):

DBID　: 4133493568GROUP_ID　: 2GROUP_NAME: System Metrics Long DurationMETRIC_ID : 2075METRIC_NAME　 : CPU Usage Per SecMETRIC_UNIT　 : CentiSeconds Per Second

　

It displays a metric "CPU usage per second" measured in "per second", and this metric belongs to a measurement group "System Metrics Long Duration ". This record can be combined with other tables such as DBA_HIST_SYSMETRIC_SUMMARY to obtain the activity information of the database. The format is as follows:

select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd from dba_hist_sysmetric_summary where metric_id = 2075;BEGININTSIZE NUM_INTERVAL　 MINVAL　MAXVAL　AVERAGE　 SD----- ---------- ------------　 ------- ------- --------　----------11:39 179916　 30 0　333　9.8155354811:09 180023　 3021　35　 28　5.91543912... and so on ...

　

Next we will look at how the CPU time is consumed in the unit of per second ). The standard deviation is added to our analysis to determine whether the average number reflects the actual workload. In the first record, the average value is the CPU time consumed per second, but the standard deviation is 9.81, which means that the average value 3 does not reflect the workload. In the second example, the average value is 28 and the standard deviation is 5.9, which is more representative. This type of information trend helps you understand the impact of several Environment Parameters on performance measurements.

Usage Statistics

So far, we have seen the content collected by AWR. Now let's see how it will process data.

Most performance problems do not exist in isolation, but are indicative, which leads to the final root cause of the problem. Let's use a typical adjustment practice to illustrate this: you notice that the system is slow, so you decide to check the reason for waiting. Check to find that the "buffer busy waiting" is very high. Where is the problem? There are several possibilities: there may be a monotonic increasing index, maybe a table is too full, so that a single data block needs to be quickly loaded into the memory, or some other factors. In either case, you must first determine the problematic section. If it is an index segment, you can decide to re-build it and change it to a reverse key index, or convert it into a hash partition index introduced in Oracle Database 10 Gb. If it is a table, you can consider modifying storage parameters to make it less intensive, or transferring it to a tablespace using automatic segment space management.

Your processing plan is generally regular and generally based on your understanding of various events and your experience in handling them. Now imagine that the same thing is done by an engine that collects metrics and launches possible plans based on predefined logic. Isn't your work easier?

This engine, which is now available in Oracle Database 10g, is called the automatic Database diagnosis and monitoring program (ADDM ). To make a decision, ADDM uses data collected by AWR. In the above discussion, ADDM can see that there is a buffer busy wait, and then take out the corresponding data to view the buffer busy wait segment, evaluate its features and scores, and finally provide a solution for the database administrator. After each snapshot collection by AWR, ADDM is called to check the measurement and generate suggestions. Therefore, you actually have an automatic database administrator working 24 hours a day. It actively analyzes data and generates suggestions to free you from the situation, this allows you to focus on more strategic issues.

To view ADDM recommendations and AWR database data, use the new Enterprise Manager 10g console on the page named DB Home. To view AWR reports, you can go from management to workload information library and then to Snapshots to view them. In the future, we will discuss ADDM in more detail.

You can also specify to generate alarms based on specific situations. These alarms are called server-generated alarms, which are pushed to the advanced queue, where they can be used by any clients that listen to them. One such client is Enterprise Manager 10 Gb, in which the alarm is highlighted.

Time Model

When you have performance problems, what do you first think of to shorten the response time? Obviously, you want to eliminate or reduce the root cause of the increased time. How do you know where time is spent-not waiting, but actually working?

Oracle Database 10 Gb introduces a time model to determine the time spent in various places. The total system time spent is recorded in view V $ SYS_TIME_MODEL. The following are the query and output results.

STAT_NAME VALUE------------------------------------- --------------DB time　 58211645DB CPU54500000background cpu time　 254490000sequence load elapsed time0parse time elapsed1867816hard parse elapsed time　 1758922sql execute elapsed time　57632352connection management call elapsed time　 288819failed parse elapsed time 50794hard parse (sharing criteria) elapsed time220345hard parse (bind mismatch) elapsed time　 5040PL/SQL execution elapsed time 197792inbound PL/SQL rpc elapsed time　 0PL/SQL compilation elapsed time

(