[Turn]oracle AWR report generation and analysis

Transferred from: http://blog.csdn.net/cuker919/article/details/8767328

Recently, due to the high CPU usage of the database, VCs often switch automatically, causing many problems.

Recently learn about the database AWR Analysis database SQL execution Performance Analysis report. Here's a preliminary explanation:

1. Log in to the database first and generate the AWR report.

linux:~ # Su-oracle
[Email protected]:~> sqlplus '/as sysdba '

Sql*plus:release 11.1.0.6.0-production on Sun Apr 7 14:02:38 2013

Copyright (c) 1982, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

2. Input Analysis command
Sql> @?/rdbms/admin/awrrpt

Current Instance
~~~~~~~~~~~~~~~~

DB Id db Name Inst Num Instance
----------- ------------ -------- ------------
2045388596 UTF8 1 UTF8


Specify the report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would the HTML report, or a plain the text report?
Enter ' HTML ' for the HTML report, or ' text ' for plain text
Defaults to ' HTML '
Enter value for report_type:html

3. Enter the file format to generate the report
Type specified:html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num db Name Instance Host
------------ -------- ------------ ------------ ------------
* 2045388596 1 UTF8 UTF8 Linux

Using 2045388596 for Database Id
Using 1 For instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) would result in the most recent
(n) days of snapshots being listed. Pressing <return> without
Specifying a number lists all completed snapshots.

4. Enter the number of days to generate the report
Enter value for Num_days:1

Listing the last day ' s completed snapshots

Snap
Instance DB Name snap Id snap Started level
------------ ------------ --------- ------------------ -----
UTF8 UTF8 2809 Oct 2014 00:00 1
2810 Oct 2014 01:00 1
2811 OCT 2014 02:00 1
2812 OCT 2014 03:00 1
2813 OCT 2014 04:00 1
2814 OCT 2014 05:00 1
2815 OCT 2014 06:00 1
2816 OCT 2014 07:00 1
2817 OCT 2014 08:00 1
2818 OCT 2014 09:00 1
2819 OCT 2014 10:00 1
2820 OCT 2014 11:00 1
2821 OCT 2014 12:00 1


5. Enter the snap ID start and end numbers between snapshots that are separated

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:2809

Enter value for end_snap:2821
End Snapshot Id specified:2821



Specify the report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report, file name, is awrrpt_1_2809_2821.html. To use the this name,
Press <return> to continue, otherwise enter an alternative.

6. Enter the name of the generated report:

Enter value for report_name:20130407awr.html

Perform a database SQL statement performance analysis after you get the 20130407awr.html report. The following is the AWR concept and the main Analysis name interpretation (online copy of the material, not necessarily all the right):

Find several good articles on the Internet:

For an explanation of the awr parameters, compare the entire article

Http://wenku.baidu.com/view/ae2ddbc29ec3d5bbfd0a7429.html

Time model in http://468302.blog.51cto.com/458302/998786 AWR (db times vs. CPU hours)

Http://www.oracledatabase12g.com/oracle expert Liu Xiang Bing Blog

http://www.askmaclean.com/archives/maclean-little-contribute.html Liu Xiang related videos and articles

Http://www.5ienet.com/note/html/stat/index.shtml

http://blog.itpub.net/26686207/Daniel Blog: Liucheng Network Name leonarding

Learn to use Oracle_awr and Ash properties

Http://www.5ienet.com/note/html/ash_awr/index.shtml

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Automatic Workload Repository (AWR) is an important component of the 10g introduction. It stores detailed information about the status of the database activity for a short period of time (default is 7 days).
1. Generate AWR report
Log on as an Oracle user
Sqlplus/as SYSDBA
@?/rdbms/admin/awrrpt.sql
2. Analysis Report
SQL ordered by Elapsed time
Resources reported for PL/SQL code includes the resources used by the "all" statements called by the code.
% Total DB time is the Elapsed time of the SQL statement divided to the total Database time multiplied by 100
Elapsed time (s) CPU time (s) executions Elap per Exec (s)% total DB timesql idsql modulesql Text
Elapsed Time (S): The total length of the SQL statement execution, this sort is done by this field. Note that this time is not a single SQL run time, but a sum of the number of SQL executions that are monitored within the range. Unit time is seconds. Elapsed time = CPU time + Wait time
CPU time (s): The total duration of CPU elapsed for SQL statement execution, which is less than or equal to elapsed time. Unit time is seconds.
The total number of executions of the EXECUTIONS:SQL statement within the monitoring scope.
Elap per Exec (s): Average time to execute SQL once. Unit time is seconds.
% Total DB time: Elapsed time for SQL is the percentage of the database.
The ID number of the SQL Id:sql statement, which can then be navigated to the SQL detail list below, click on the return of IE to go back to the current SQL ID.
SQL Module: Shows how the SQL is connected to the database for execution, and if it is linked with sql*plus or PL/SQL, it is basically someone debugging the program. SQL that is typically executed with the foreground app link is empty.
SQL Text: Simple SQL hint, detailed need to click on SQL ID.
SQL ordered by CPU time
Resources reported for PL/SQL code includes the resources used by the "all" statements called by the code.
% Total DB time is the Elapsed time of the SQL statement divided to the total Database time multiplied by 100
CPU time (s) Elapsed time (s) executions CPU per Exec (s)% total DB timesql idsql modulesql Text
The top SQL that executes the longest total CPU time is recorded (note that the execution of the SQL in the monitoring scope is the sum of the CPU time, not the single SQL execution time).
SQL ordered by Gets
Resources reported for PL/SQL code includes the resources used by the "all" statements called by the code.
Total Buffer gets:964,486
Captured SQL account for 103.6% of total
Buffer gets executions gets per Exec%totalcpu time (s) Elapsed time (s) SQL idsql modulesql Text
The top SQL that performed the total buffer gets (logical IO) was recorded (note that the execution of the SQL in the monitoring range is the sum of the total, rather than a single SQL execution of the gets).
SQL ordered by Reads
Total Disk reads:5,606
Captured SQL account for 168.4% of total
Physical readsexecutionsreads per Exec%totalcpu time (s) Elapsed time (s) SQL idsql Modulesql Text[nextpage]
Records the top SQL that performs physical read (physical IO) for the total disk (note that the SQL execution in the monitoring scope is the sum of the physical reads of the disk, not the physical read of the disk as a single SQL execution).
SQL ordered by executions
Total executions:20,124
Captured SQL account for 59.3% of total
Executions Rows processedrows per execcpu per exec (s) elap per exec (s) SQL idsql modulesql Text
A top SQL that is sorted by the number of executions of SQL is recorded. This sort shows the number of SQL executions that are within the scope of the monitoring.
SQL ordered by Parse Calls
Total Parse calls:14,635
Captured SQL account for 69.0% of total
Parse callsexecutions% Total parsessql idsql modulesql Text
Top SQL that records the number of soft parse times for SQL.
SQL ordered by sharable Memory
Only statements with sharable Memory greater than 1048576 is displayed
Sharable Mem (b) executions% Totalsql idsql modulesql Text
The top SQL that SQL occupies the size of the library cache is logged.
Sharable Mem (b): occupies the size of the library cache. Unit is byte.
SQL ordered by Version Count
Only statements with Version Count greater than is displayed
Version Count executions SQL idsql modulesql Text
Top SQL that records open child cursors for SQL.

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Manually modify AWR's execution plan while generating reports manually

Sql> @?/rdbms/admin/awrrpt.sql (can go to two snapshots between the AWR report)

Note: Awr retains a snapshot of the database for 7 days by default, producing one snapshot per hour

First, manually modify the AWR implementation plan

Adjust the frequency and retention policy of the AWR to generate snapshot, such as changing the collection interval to 30 minutes. and keep for 5 days (units are minutes):

sql> exec dbms_workload_repository.modify_snapshot_settings (interval=>30, retention=>5*24*60);

　　

Second, the test system:

1, manually first create a snapshot in the database

Sql> execute Dbms_workload_repository.create_snapshot (); PL/SQL procedure successfully completed.

2, open the production system, click "Single Well Summary information" more than 20 seconds to come out of the page

3. Reproduce a Snapshot

Sql> execute Dbms_workload_repository.create_snapshot (); PL/SQL procedure successfully completed.

4. According to the AWR report above, we can find out the execution plan of a single SQL statement which takes a long time.

Example: SQL ID for C0yffdyps8uk9 took 26 seconds

Sql> @?/rdbms/admin/awrsqrpt.sql
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:1679
Begin Snapshot Id specified:1679
Enter value for end_snap:1680
End Snapshot Id specified:1680
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: (Input C0yffdyps8uk9)

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

1.AWR Introduction

AWR (Automatic Workload Repository) is a built-in tool provided by Oracle 10g that captures DB performance-related statistics, names tables in wrm$_* and wrh_* formats, All tables are stored in the SYS mode in the Sysaux table space; The AWR report is an important means for DBAs to evaluate database performance and discover problematic SQL.

AWR saves a week of data statistics by default, and the resulting snapshot frequency and retention time can be adjusted manually, which can completely replace Statspack.

2.AWR report Generation steps

2.1 Using the tool toad to connect to the AWR report generation interface

Database->monitor->addm/awrreports (OEM)

2.2 Select the time period for snapshot generation click the Green button to execute the Generate AWR report

2.3 Generate an AWR report from a database script file and execute the following script

$ORACLE _home\rdbms\admin\awrrpt.sql

3. Analyzing the AWR report

3.1 AWR report Header information

Elapsed: Sampling time period

DB time: How long it takes the user to operate

DB time is much less than elapsed time indicates database is more idle

3.2 Awr Load profile information

Redo size: Flag How busy the database is

Parses: Less than 300 indicates normal

Hard parses: Rigid parsing, less than 100 means normal

From the above data can be initially explained that the database throughput and load normal

3.3 Awr instance Efficiency

Buffer nowait%: No wait scale for memory to get data

Buffer hit%: Less than 80% to add memory

Library hit%: If less than 90%, you need to increase the share pool

In-memory sort%: The memory sort scale, too low sort will be in the temporary table, you need to adjust the PGA

Soft parse%: Make sure it's greater than 99%, otherwise it means share pool latch contention

The above data can indicate that the DB instance hit ratio is in a healthy state

3.4 AWR Top Wait Event

In a database with no problem, CPU time is always listed in the first position

DB file Sequential read: This wait event has a problem with the order of multiple table joins and may not have the correct use of the base table or an optional use index

DB file scattered read: This wait event is a significant indication of the presence of a large number of full-table scans or the creation of an appropriate index

ARCH wait on SENDERQ: Remote delivery archivelog Wait Event

Oracle has roughly 100 + idle and non-idle wait events,
Top 5 Timed Events is the first five wait events in the DB system that have a large impact.
The CPU time is idle, it is in the first name stating that the DB is more idle,
Db file Sequentail Read and scattered read indicate that the index selection is not very reasonable,

3.5 AWR TOP SQL Tuning

There are several order by objects for Turning,top SQL for the top-ranked SQL:
SQL ordered by Elapsed time: Sort by sum of SQL execution times
SQL ordered by CPU time: Sort by SQL consumption CPU time sum
SQL ordered by Gets: Sort by SQL consumption logical IO sum
SQL ordered by Reads: Sort by SQL consumption physical IO sum
SQL ordered by executions: Sort by SQL execution count
SQL ordered by Parse Calls: Based on SQL Soft parse count

Mainly for ordered by Elapsed Time,orderedby CPU Time,orderedbygets,orderedby read ranked top three SQL for observation and tuning.

Oracle's Steps for SQL processing:

1. Grammar check (check that the spelling grammar of SQL is correct)

2. Semantic check (check if the Access object in SQL exists and has appropriate permissions)

3. Parsing (parse) (using internal algorithms for SQL parsing, generating the parse tree (parsetree) and execution plan (execution plan)) à soft and hard parsing occurs during this process

4. Execute SQL, return results

Oracle awr and Ash differences are available (reference)

Http://www.cnblogs.com/rootq/archive/2009/09/24/1573196.html

[Turn]oracle AWR report generation and analysis