13518219792
The purpose of this document is to inform the user how to:
创新互联公司长期为上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为庆城企业提供专业的做网站、网站制作，庆城网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。
Configure AWR for best results.
Produce AWR and ASH Reports.
Interpret AWR and ASH reports.
Automate or batch the production of AWR reports.
Obtain more detailed information from the AWR views.
Although this document has been written with Oracle E-Business Suite in mind, almost all of it is applicable to other applications (e.g. Oracle Fusion Applications). Where there is instruction or advice specific to Oracle E-Business Suite then this is clearly stated.
From 10g onwards, the Oracle database automatically collects and stores workload information in the Automatic Workload Repository (AWR). Prior to that use Statspack.
The AWR has many advantages over Statspack. It has much more information (e.g. wait classes, metrics, OS stats). In addition the repository is maintained automatically and html reports can be produced.
The AWR can be used to identify
SQLs or Modules with heavy loads or potential performance issues. These could be from other processes than the one with reported issues.
Symptoms of those heavy loads (e.g. logical I/O (buffer gets), Physical I/O, contention, waits).
SQLs that could be using sub-optimal execution plans (e.g. buffer gets, segment statistics).
Numbers of executions.
Parsing issues.
General performance issues, e.g. system capacity (I/O, memory, CPU), system/DB configuration.
SGA (shared pool/buffer cache) and PGA sizing advice.
The results are stored in tables and then accessed using the AWR reports.
The AWR stores and reports statistics for each snapshot (identified by snapshot IDs). This is the minimum granularity of time. An AWR report can only report the total of each statistic between two completed snapshots. So the choice of snapshot duration is critical.
For more information see Oracle Database Performance Tuning Guide and My Oracle Support documents:
How to Generate an AWR Report and Create Baselines (Document 748642.1)
FAQ: Automatic Workload Repository (AWR) Reports (Document 1599440.1)
AWR data collection is enabled by default and some database features (such as Automatic Segment Advisor and Undo Advisor) use AWR data, without needing additional pack licenses.
However, the Diagnostic Pack License is required to be able to use AWR reports or any of the information from the AWR (or it’s views).
Statspack is still available from 10g onwards and can be used if the Diagnostic Pack is not licensed.
The Diagnostic Pack can only be purchased with the Enterprise Edition of the Oracle Database.
See My Oracle Support document "AWR Reporting - Licensing Requirements Clarification (Document 1490798.1)" or Oracle Database Licensing Information in the documentation library.
These should be decided before capturing and using AWR data.
The snapshot interval, retention and the number of SQLs captured for each "Top" criteria can be controlled by using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.
This has 4 arguments. Interval, retention, topnsql and dbid.
If NULL is specified for retention, interval or topnsql then the existing values are preserved.
For more information see Oracle Database PL/SQL Packages and Types Reference and Oracle Database Performance Tuning Guide in the documentation library.
This controls how often snapshots are captured (automatically). It is specified in minutes. The minimum is 10 minutes, the maximum is 1 year. The default is 60 minutes (1 hour). If zero is specified then automatic and manual snapshots are disabled.
The interval should be short enough to provide the granularity required.
Normally 1 hour is sufficient, however it may be necessary to analyze the performance for shorter periods. For short programs (or processes of indeterminate length) it is often best to manually create snapshots just before the program starts and just after it has completed.
For long running processes made up of many smaller stages, where many of the stages may only take short amounts of time (e.g. 15 minutes), then (provided the overall process is not too long) it can be advisable to have a shorter interval (e.g. 15 minutes, 30 minutes). This is often recommended for upgrades. This will increase the overheads incurred by the AWR, but if they are excessive it is likely that there is a performance issue with AWR snapshot creation.
This controls how long snapshots are retained in the workload. It is specified in days. The default is 8 days for 11g and above (7 days for 10g). The minimum is 1 day, the maximum 100 years. If zero is specified then snapshots will be retained forever.
The retention period should be long enough to allow full analysis of the performance issues to be carried out. Quite often the performance analyst will need to run subsequent reports from the AWR (e.g. AWR SQL Reports, ASH, ADDM or querying AWR views directly).
This controls the number of Top SQLs to flush into the AWR for each of the "Top" criteria (Elapsed Time, CPU Time etc). This can be set to a numeric value, or DEFAULT, MAXIMUM. The minimum value is 30 and the maximum 50,000. DEFAULT will capture the top 30 (statistics_level=TYPICAL) or the top 100 (statistics_level=ALL). MAXIMUM will capture the complete set of SQL in the cursor cache.
Normally the default will suffice. However, there may be situations where a larger set of SQL needs to be monitored.
Note that TOPNSQL can be set to a very high value. However, there will be much more data to capture and store; this could cause space or performance issues.
This is the database identifier (in AWR). NULL specifies the local database.
The values of these arguments can be accessed by querying the view DBA_HIST_WR_CONTROL.
The view sys.wrm$_wr_control gives a little more information.
Note the meaning of the following values in column sys.wrm$_wr_control.topnsql:
2000000000 = DEFAULT
2000000001 = MAXIMUM
From 11g Release 2 onwards, the amount of information (rows) in an AWR report can be controlled by using the DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS procedure.
Note that this is only at session level. So the procedure needs to be run again for new sessions.
See My Oracle Support document "How to Control the Number of SQL Statements and other information displayed in AWR Report (Document 1357637.1)"
Automatic statistics gathering by the AWR is disabled if the STATISTICS_LEVEL initialization parameter is set to BASIC. So setting STATISTICS_LEVEL to BASIC is not recommended.
Note that (if STATISTICS_LEVEL=BASIC) AWR statistics can still be captured manually. However, as the collection of many statistics (in-memory) will be disabled, the AWR reports will omit a lot of information (such as segment statistics or memory advice).
Sometimes it is necessary to restrict the AWR reports to a specific time interval (that is not coincidental with the automatically created snapshots). In this case snapshots can be created manually by using the DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT procedure. e.g.
BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END; /
For more information see Oracle Database PL/SQL Packages and Types Reference and Oracle Database Performance Tuning Guide in the documentation library.
The time period that is required or even the Snapshot IDs may already be known.
However, the following SQLs can be used to identify the Snapshot IDs (the AWR reports do help with the choice of Snapshot IDs; they display the time associated with each Snapshot ID from a specified number of previous days).
Note that the Snapshot ID identifies the time when a snapshot ends. Requesting an AWR Report from Snapshot N to M, will show the activity between the end time for Snapshot N and the end time for Snapshot M.
For the current database instance:
SELECT s.snap_id,
TO_CHAR(s.end_interval_time,'DD-MON-YYYY HH24:MI:SS')
FROM dba_hist_snapshot s,
v$database d,
v$instance i
WHERE s.dbid = d.dbid
AND s.instance_number = i.instance_number
ORDER BY s.snap_id;
For a specified database instance:
SELECT s.snap_id,
TO_CHAR(s.end_interval_time,'DD-MON-YYYY HH24:MI:SS')
FROM dba_hist_snapshot s
WHERE s.dbid = Alternatively, the following SQL can be used to identify the snapshots when a particular SQL was running:
SELECT dhs.sql_id,
dsn.snap_id-1 begin_snap,
dsn.snap_id end_snap,
TO_CHAR(dsn.begin_interval_time,'DD-MON-YYYY HH24:MI:SS') begin_time,
TO_CHAR(dsn.end_interval_time,'DD-MON-YYYY HH24:MI:SS') end_time,
ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs
FROM dba_hist_sqlstat dhs,
v$database d,
v$instance i,
dba_hist_snapshot dsn
WHERE dhs.dbid = d.dbid
AND dsn.snap_id = dhs.snap_id
AND dsn.dbid = dhs.dbid
AND dsn.instance_number = dhs.instance_number
AND dhs.instance_number = i.instance_number
AND dhs.sql_id = 'These scripts are provided for educational purposes only and not supported by Oracle Support Services.
AWR baselines are a series of snapshots that are retained for comparison with subsequent snapshots. The subsequent snapshots may be when performance problems have occurred, or they may be used to compare changes in workload. They can be created by using the procedure DBMS_WORKLOAD_REPOSITORY.create_baseline.
They are retained indefinitely and are not purged from the AWR.
AWR Baselines are not normally used for diagnosing specific performance issues, so they are not covered in much detail here.
For more information see:
Oracle Database PL/SQL Packages and Types Reference and Oracle Database Performance Tuning Guide in the documentation library.
An AWR report can be generated from SQL*Plus by running the AWR SQL scripts. These are stored under $ORACLE_HOME/rdbms/admin.
The APPS (Oracle E-Business Suite), SYS or SYSTEM users can be used to generate the reports.
Note that AWR Reports can also be produced from Enterprise Manager (AWR Page/Browser, ASH Analytics in 12c).
The main scripts available are:
awrrpt.sql – Produces the AWR report for the current (local) database and instance.
awrrpti.sql - Produces the AWR report for a specified database and instance.
awrgrpt.sql (11g Release 2 and above) - Produces the Global AWR report for all available instances in an Oracle Real Application Clusters (RAC) environment. It does this for the current database.
awrgrpti.sql (11g Release 2 and above) - Produces the Global AWR report for available instances in an Oracle Real Application Clusters (RAC) environment. It does this for a specified database and instances (either one instance, a comma delimited list of some instances or all instances can be chosen).
spawrrac.sql (10g to 11g Release 1) - This is a global AWR report (in text format) for all instances in an Oracle Real Application Clusters (RAC) environment. It does this for a specified database.
There are other scripts available for comparing periods (e.g. awrddrpt.sql, awrddrpi.sql) – see Oracle Database Performance Tuning Guide in the documentation library and My Oracle Support document "Note How to Generate an AWR Report and Create Baselines (Document 748642.1)"
All the scripts are interactive. Arguments cannot be specified in the command line. See here for information on automating AWR Reports.
The Oracle Database Performance Tuning Guide has more information about the arguments that the AWR reports prompt for, but here is some additional information.
The HTML format is recommended. It is clearer, easier to read and has complete SQL statements (they are not truncated).
If running for a specified instance (e.g. awrrpti.sql) then the script will list the "Instances in this Workload Repository schema" and prompt for the database id (dbid) and instance number (inst_num).
The current (local) instance will be highlighted with an asterix (*) at the left hand side of the row.
Choose an instance. This should be the database and instance on which the performance issue has been observed.
If running for specified instances on a RAC environment (e.g. awrgrpti.sql) then the instance can be a comma separated list of instance numbers or "ALL".
It is advisable to think about the snapshots required before running these scripts. See Identifying Snapshots to Report section.
It is usually best to produce AWR reports for every snapshot during the period of the performance issue.
If a performance issue is long running (straddles more than 2 snapshots) then it is very useful to produce a report for the whole period of the performance issue, program or process. Ideally for snapshots covering the period from just before the program/process starts until just after program/process completes.
If a series of successive programs or processes is being reported on (e.g. Upgrade) then produce a separate report for the period that each successive program is running (if possible).
Specify the report name or enter null for it to default to
awrrpt_
_ _ .html
or
awrrpt_rac_
_ .html
for the RAC reports (awrrptg.sql/awrrptgi.sql)
If using an Oracle Real Application Clusters (RAC) environment then provide.
A separate AWR report (awrrpti.sql) for each node (instance) where the performance issue is occurring. If the node (with the performance issue) is not yet determined then report for all nodes.
An AWR Global report (awrgrpt.sql / awrgrpti.sql) for all nodes (being used) if on 11g Release 2 or above. This will aggregate the statistics for all nodes and highlight differences between nodes. For 10g and 11g Release 1 use the script spawrrac.sql.
If a process (such as an upgrade) is being run on one node (instance) only then the awrrpti.sql report for that instance will suffice.
For more information see Oracle Database Performance Tuning Guide in the documentation library.
Each section of the AWR report contains useful information. However, the following sections are the ones that are most frequently looked at:
Report Header – This contains information about the Database and environment. Along with the snapshot IDs and times.
Report Summary - Top N Timed Foreground Events and Host/Instance CPU
Wait Event Statistics - Foreground Wait Events and Wait Event Histogram
SQL Statistics - SQL ordered by Elapsed Time, CPU Time, Gets, Reads (SQL Statistics), Complete List of SQL Text
Advisory Statistics - Buffer Pool Advisory, PGA Memory Advisory, Shared Pool Advisory, SGA Target Advisory
Enqueue Activity
Segment Statistics - Segments by Logical Reads, Physical Reads, Physical Writes, DB Block Changes, Row Lock Waits, ITL Waits, Buffer Busy Waits
Init.ora Parameters
This contains information about the Database and environment. Along with the snapshot Ids and times.
This indicates the most time consuming events. These are either DB CPU or wait events.
Note that there could be significant waits that are not listed here, so check the Foreground Wait Events (Wait Event Statistics) section for any other time consuming wait events.
A high level of DB CPU is not usually a reason for concern, unless it is accompanied by a high level of CPU Usage or a few SQLs with high CPU Time (and potentially sub-optimal execution plans).
However, the wait events can give us an indication of performance issues. These performance issues could be due to poor execution plans on individual SQLs, which can be resolved by SQL tuning. Or they could be general performance issues that can be resolved by changes to the DB/system configuration or system resources.
The key things to look for are the total time (and percentage) and the average wait for each event.
For the largest waits look at the Wait Event Histogram to identify the distribution of waits.
A high value of a specific wait does not always have one solution. The wait could have several different causes and solutions. In many cases, the wait could be removed by SQL (or application) tuning and this should be considered first before assuming it is a system configuration or resource issue.
For more information on interpreting the top wait events see the "How to interpret the output? > 3. Understanding AWR and ADDM reports > 3.4 Major top waits" section in My Oracle Support document "Oracle E-Business Suite SQL Tuning Tips for Customers (Document 2098522.1)".
A high level of DB CPU usage in the Top N Foreground Events (or Instance CPU: %Busy CPU) does not necessarily mean that CPU is a bottleneck.
Look at the Host CPU and Instance CPU sections. The key things to look for are the values "%Idle" in the "Host CPU" section and "%Total CPU" in the "Instance CPU" section.
If the "%Idle" is low and "%Total CPU" is high then the instance could have a bottleneck in CPU (be CPU constrained).
Otherwise, the high DB CPU usage just means that the database is spending a lot of time in CPU (processing) compared to I/O and other events.
In either case (CPU is a bottleneck or not) there could be individual expensive SQLs with high CPU time, which could indicate sub-optimal execution plans, especially if accompanied with high (buffer) gets.
This is useful because there could be time consuming report wait events that do not appear in the "Top N Timed Foreground Events".
For the larger waits look at the Wait Event Histogram to identify the distribution of waits. Are they closely clustered around an average value or are there a wide variance of values ? Are there a large number of smaller waits or a few larger waits ?
Just above this in the AWR there is a "Foreground Wait Class" section. This is of less use. A wait could have multiple possible causes (in different classes) depending on the context. There are normally only a handful of time consuming waits, which can be analyzed and investigated separately.
For more information on interpreting wait events see the "How to interpret the output? > 3. Understanding AWR and ADDM reports > 3.4 Major top waits" section in My Oracle Support document "Oracle E-Business Suite SQL Tuning Tips for Customers (Document 2098522.1)".
This can be used to determine the distribution of wait times.
For example, in this case there is a high level of "db file sequential read" waits (21.5%), with an average wait time of 34ms. This is very high. These days less than 5ms is expected and more than 10ms is considered poor.
An analysis of the histogram shows a smooth exponential distribution of wait times beyond a minimum service time clustered around 8ms. This indicates that I/O service times could be slow, but that there is also considerable contention (the I/O load is heavy). There could be a number of reasons and solutions for both.
Incidentally, the absence of waits below 2ms indicates that there is no caching in storage.
Similarly an analysis of the histogram can indicate if a high average time is due to a few individual long waits.
If the following places in the AWR show high levels of RAC wait events ("gc" wait events) then the RAC configuration may need to be changed.
Top N Timed Foreground Events , Foreground Wait Events, Wait Event Histogram
SQL ordered by Cluster Wait Time
Segments by Global Cache Buffer Busy
See the following My Oracle Support documents for more information:
RAC: Frequently Asked Questions (Document 220970.1)
RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Document 810394.1)
Using Oracle 11g Release 2 Real Application Clusters with Oracle E-Business Suite Release 12 (Document 823587.1)
Troubleshooting gc block lost and Poor Network Performance in a RAC Environment (Document 563566.1)
Also see Chapter 13 Monitoring Performance in the "Real Application Clusters Administration and Deployment Guide".
The most useful sections are SQL ordered by Elapsed Time, CPU Time, Gets and Reads. All the sections can be useful in identifying if a particular SQL from a particular module was running during the AWR report period.
For more information on interpreting the SQL Statistics sections see the
"What is the challenging SQL? > 2 From Automatic Workload Repository (AWR)"
or
"How to interpret the output? > 3. Understanding AWR and ADDM reports > 3.5 Top SQLs"
sections in My Oracle Support document "Oracle E-Business Suite SQL Tuning Tips for Customers (Document 2098522.1)".
This can be used to identify the long running SQLs that could be responsible for a performance issue. It can give useful information about the CPU time, the number of executions and the (SQL) Module.
The Top SQLs can be matched to long running or slow Processes in the application.
The Elapsed time can indicate if a SQL is multi-threaded (either Parallel DML/SQL or multiple workers). In this case the elapsed time will be multiple times the AWR duration (or the observed clock time of the process/SQL). The elapsed time for multi-threaded SQL will be the total of elapsed time for all workers or parallel slaves.
Note that the "SQL Ordered" sections can often contain the PL/SQL call that contains SQLs. So in this case the procedure WF_ENGINE.BackgroundConcurrent (via procedures Background and ProcessStuckProcess ultimately calls the SQL 1wmz1trqkzhzq).
In most cases this section does not reveal much more information than the "SQL Ordered by Elapsed Time" section. However, it does sort by CPU and can output SQLs that are not in the previous section.
A high number of buffer gets is one of the main indicators of SQLs with sub-optimal execution plans.
Bear in mind that the SQL could have a good execution plan and just be doing a lot of work.
If the physical I/O waits (e.g. db file sequential read, db file scattered read, direct path read) are relatively high then this section can indicate which SQLs are responsible.
This section can help identify the SQLs that are responsible for high physical I/O and may indicate sub-optimal execution plans, particularly if the execution plan contains full table scans or large index range scans (where more selective index scans are preferable).
This shows the complete SQL text for all SQL listed in the "SQL ordered by" sections. The SQL Ids in the "SQL ordered by" sections are hyper links to entries in this list.
Note that the advisory statistics within this section (MTTR Advisory, Buffer Pool Advisory, PGA Memory Advisory, Shared Pool Advisory, SGA Target Advisory, Streams Pool Advisory, Java Pool Advisory) are all reported for the last snapshot interval only. If the AWR report covers more than one snapshot interval and the last snapshot interval has a lower workload (e.g. the process being observed completes a long time before the end snapshot) then the advisory statistics could underestimate the advised values.
The My Oracle Support document "How to Read Buffer Cache Advisory Section in AWR and Statspack Reports (Document 754639.1)" gives an excellent explanation of how to read this section.
It can indicate if the buffer cache (or the SGA that contains it) needs to be increased.
Starting at a "Size Factor" of 1 (this indicates the current size of the buffer pool). If the "Physical Read Factor" decreases significantly as the "Size Factor" increases then increasing the buffer cache (SGA Target) will significantly reduce the physical reads and improve performance.
For 10g and above it is recommended to use the sga_target initialization parameter to specify the size of the SGA, so there is no longer a need to specify the size of the buffer cache. The db_cache_size parameter (if specified) defines the minimum size of the default buffer cache.
So the size of Buffer Cache advised here should be added to the advised Shared Pool size and any Large Pool to determine the advised size for the SGA (sga_target). However, there is also an SGA Target Advisory section (see below).
For more information on the recommended initialization parameter values in Oracle E-Business Suite see My Oracle Support documents:
Database Initialization Parameters for Oracle Applications Release 11i (Document 216205.1)
Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)
The My Oracle Support document "How to Read PGA Memory Advisory Section in AWR and Statspack Reports (Document 786554.1)" gives an excellent explanation of how to read this section.
It can indicate if the PGA (defined by the pga_aggregate_target initialization parameter) needs to be increased.
Starting at a "Size Factor" of 1 (this indicates the current size of the PGA). If the "Estd Extra W/A MB Read/Written to Disk" decreases significantly as the "Size Factor" increases then increasing the PGA will improve performance. The best value to use is a PGA Target at the point where the "Estd Extra W/A MB Read/Written to Disk" stops significantly reducing.
It can indicate if the Shared Pool (defined by the shared_pool_size initialization parameter) needs to be increased.
Starting at a "Size Factor" of 1 (this indicates the current size of the shared pool). If the "Est LC Time Saved Factr" increases as the "Size Factor" increases then increasing the shared pool will improve performance.
It can indicate if the SGA (defined by the sga_target initialization parameter) needs to be increased.
Starting at a "Size Factor" of 1 (this indicates the current size of the SGA). If the "Est DB Time (s)" decreases significantly as the "Size Factor" increases then increasing the SGA will significantly reduce the physical reads and improve performance.
Note that the SGA Target Advisory (and Buffer Pool Advisory) may underestimate the performance improvement gained from increasing SGA for situations where there are many workers accessing the same objects at the same time (e.g. AD Parallel jobs (on upgrades), multiple workers (concurrent requests) for batch processes).
This is in the Wait Statistics section.
This can give some more information for enqueue waits (e.g. Requests, Successful gets, Failed gets), which can give an indication of the percentage of times that an enqueue has to wait and the number of failed gets.
For more information on interpreting the SQL Statistics sections see the "How to interpret the output? > 3. Understanding AWR and ADDM reports > 3.6 Top segments" sections in My Oracle Support document "Oracle E-Business Suite SQL Tuning Tips for Customers (Document 2098522.1)".
Note that the Segment Statistics section (see below) of the AWR will contain no data if the hidden parameter "_object_statistics" is set to FALSE.
If "_object_statistics" is set to FALSE then the object level statistics (logical and physical reads, writes, block changes, locks, ITL etc) will not be gathered. However, the default is "TRUE".
See My Oracle Support document ""No data exists for this section of the report" in Segment Statistics Section of AWR Report (Document 1570007.1)".
If a SQL is sub-optimal then this can indicate the tables and indexes where the workload or throwaway occurs and where the performance issue lies. It can be particularly useful if there are no actual statistics elsewhere (e.g. Row Source Operation Counts (STAT lines) in the SQL Trace or no actuals in the SQLT/Display Cursor report).
If there are a high number of physical read waits (db file scattered read, db file sequential read and direct path read) then this section can indicate on which segments (tables or indexes) the issue occurs.
This can help identify sub-optimal execution plan lines. It can also help identify changes to tablespace and storage management that will improve performance.
If there are long running Inserts, Deletes or Updates during the AWR period then this section can help identify those segments (tables or indexes) where most of the workload is occurring.
If there are long running Inserts, Deletes or Updates during the AWR period then this section can help identify those segments (tables or indexes) where most of the workload is occurring.
If there is a high level of "enq: TX - row lock contention" waits then this section can identify the segments (tables/indexes) on which they occur.
If there is a high level of "enq: TX - allocate ITL entry" waits then this section can identify the segments (tables/indexes) on which they occur.
If there is a high level of "Buffer Busy Waits" waits then this section can identify the segments (tables/indexes) on which they occur.
This section can be used to check the values of initialization parameters on the instance. It can also indicate if they have been changed during the AWR period.
For Oracle E-Business Suite any unusual or unexpected values can be checked against My Oracle Support documents:
Database Initialization Parameters for Oracle Applications Release 11i (Document 216205.1)
Database Initialization Parameters for Oracle E-Business Suite Release 12 (Document 396009.1)
Although, it can be easier to use script bde_chk_cbo.sql from My Oracle Support document "EBS initialization parameters - Healthcheck (Document 174605.1)".
For more advice on initilization parameters see the "How to interpret the output? > 3. Understanding AWR and ADDM reports > 3.3 Basic instance configuration" section in My Oracle Support document "Oracle E-Business Suite SQL Tuning Tips for Customers (Document 2098522.1)".
This gives some advice on the parameters:
In some cases Active Session History (ASH) reports can be used to give more detailed information on which SQLs, Wait Events, Objects and Row Sources the performance issue occurs.
Analysis of the AWR and ASH reports may lead to one or more of the following conclusions:
One or more expensive SQLs could have sub-optimal execution plans that require tuning.
The database requires configuration changes. This could simply be initialization parameters that require changing or it could be architectural changes such as partitioning, OATM etc.
Underlying system performance issues that could require additional or improved system resources.
There are potential performance issues on the applications tier.
This will dictate which additional diagnostics are required (if any).
There can often be several actions that can improve performance, each to a differing extent.
Just because a small improvement has been identified, possibly by changing an initialization parameter to a recommended value, does not mean that the performance issue has been fixed.
If possible all potential improvements should be prioritized and the largest ones pursued.
In a high percentage of cases there will be one or more expensive SQLs (with potentially sub-optimal execution plans) and SQL specific diagnostics (SQL Trace/TKPROF, Display Cursor/SQL Monitor or SQLT) will be required.
In other cases Operating System Statistics or Application Tier diagnostics may be required.
The AWR reports are interactive. However, they could be automated in the following way.
Input (in the standard AWR reports) is provided using substitution variables, some of which are then populated into bind variables (for use inside anonymous PL/SQL blocks).
A wrapper such as the following could be used to populate the substation variables (e.g. begin_snap, end_snap, dbid, inst_num (or instance_numbers_or_ALL for RAC global reports) and report_name) from command line arguments and then call the AWR report scripts awrrpt.sql or awrgrpt.sql.
-- fndawrrpt.sql
-- Generic script used to automate AWR Reports
-- Call this script with the following arguments
-- &1 begin snap
-- &2 end snap
-- &3 dbid - use 0 for current instance connected-to
-- &4 inst_num use 0 for current instance connected-to
-- (Use ALL or comma separated list of instances (no spaces) if calling awrgrpti.sql)
-- &5 report name - include extension (if 0 then awrrpti/awrgrpti defaults are used)
-- The following variables can be set in this script.
-- and can be changed here - they are likely to be the same for all invocations
define num_days = 0;
define report_type = 'html'; -- could be 'text';
define report_name = '';
-- Populate begin_snap, end_snap, dbid, inst_num and report_name substitution variables
-- Populate dbid and inst_num with current DB and instance if not already specified
-- if running awrgrpti.sql - then the subst var is instance_numbers_or_ALL and not inst_num
-- Populate report_name with NULL (it will then default) if not already specified
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column dbid new_value dbid
column inst_num new_value inst_num
column instance_numbers_or_ALL new_value instance_numbers_or_ALL
column report_name new_value report_name
SELECT &&1 begin_snap,
&&2 end_snap,
DECODE(&&3,0,d.dbid,&&3) dbid,
DECODE('&&4','0',TO_CHAR(i.instance_number),'&&4') inst_num,
DECODE('&&4','0',TO_CHAR(i.instance_number),'&&4') instance_numbers_or_ALL,
DECODE('&&5','0',NULL,'&&5') report_name
-- It is also possible to define a non-default report name combining text and subst variables 1-4
-- e.g. 'awrrpt_'||'&&4'||'_'||&&1||'_'||&&2||'.html' report_name
FROM v$database d,
v$instance i;
-- It is possible to include date and time of the begin and end snapshots in the report name.
-- as follows
-- column report_name new_value report_name
-- SELECT
-- 'awrrpt_'||'&&4'||'_'||TO_CHAR(sb.end_interval_time,'DD_MON_YYYY_HH24MI')
-- ||'_to_'||TO_CHAR(se.end_interval_time,'DD_MON_YYYY_HH24MI')
-- ||'.html' report_name
-- FROM dba_hist_snapshot sb,
-- dba_hist_snapshot se,
-- v$database d,
-- v$instance i
-- WHERE sb.dbid = d.dbid
-- AND sb.instance_number = i.instance_number
-- AND sb.snap_id = &&1
-- AND se.dbid = d.dbid
-- AND se.instance_number = i.instance_number
-- AND se.snap_id = &&2;
-- Note that an automated script is only needed for awrgrpti.sql and awrrpti.sql
-- As awrgrpt.sql calls awrgrpti.sql for ALL instances of the current database.
-- awrrpt.sql calls awrrpti.sql for the current database and instance.
WHENEVER SQLERROR CONTINUE;
WHENEVER OSERROR CONTINUE;
@@?/rdbms/admin/awrrpti
-- @@?/rdbms/admin/awrgrpti -- if running the RAC global report
WHENEVER SQLERROR EXIT;
WHENEVER OSERROR EXIT;
undefine 1
undefine 2
undefine 3
undefine 4
undefine 5A SQL*Plus script containing lines such as the following could then be written to produce a series of reports.
@@fndawrrpt 3030 3034 1447007017 1 0 @@fndawrrpt 3034 3038 1447007017 1 0 @@fndawrrpt 3038 3042 1447007017 1 0
Or a SQL*Plus script, similar to the following, could be used to generate and run a SQL*Plus script that contains command lines to run the wrapper above and produce AWR reports (for the local database and instance) for each snapshot within a defined time period.
-- fndawrbld.sql
-- example script for building script to run AWR for all snapshots on the local DB
-- between two times
-- it then runs the script that was built !
set echo off heading off feedback off verify off
PROMPT When entering start and end times choose:
PROMPT A start time just before the begin time of the first report required.
PROMPT A finish time just before the end time of the last report required.
PROMPT Otherwise the first report may be missed or an extra report included at the end
-- Note that the snap id identifies when a snapshot ends (not when it starts)
ACCEPT START_TIME CHAR FORMAT 'A20' -
PROMPT 'Please enter the date/time to start from (in format YYYY/MM/DD HH24:MI) : '
ACCEPT END_TIME CHAR FORMAT 'A20' -
PROMPT 'Please enter the date/time to end at (in format YYYY/MM/DD HH24:MI) : '
set pages 0 termout off
spool fndawrbatch.sql
SELECT '@@fndawrrpt '
||s.snap_id
||' '
||(s.snap_id+1)
||' '
||s.dbid
||' '
||s.instance_number
||' 0' -- argument5/report_name. 0 = use awrrpti/awrgrpi defaults
-- To include the date/time of the (start of the) period in the report name use the following instead
-- ||' awrrpt_'||s.instance_number||'_'||TO_CHAR(s.end_interval_time,'DD_MON_YYYY_HH24MI')||'.html'
FROM dba_hist_snapshot s,
v$database d,
v$instance i
WHERE s.dbid = d.dbid
AND s.instance_number = i.instance_number
AND s.end_interval_time>=to_date('&&START_TIME','YYYY/MM/DD HH24:MI')
AND s.end_interval_time<=to_date('&&END_TIME','YYYY/MM/DD HH24:MI')
ORDER BY s.snap_id;
spool off
set termout on echo on verify on heading on feedback on
-- Now run the script that has been built above
@@fndawrbatchIt is also possible to automate many of the other AWR reports (e.g. ASH Reports, AWR SQL Reports, AWR Compare Periods report) in a similar way.
These scripts are provided for educational purposes only and not supported by Oracle Support Services.
Note that the DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS procedure could be used in the scripts above to change the AWR report thresholds for the number of top SQLs or events output.
e.g.
exec dbms_workload_repository.awr_set_report_thresholds(top_n_events=>40, top_n_sql=>50,top_n_sql_max=>50);
However, this does not apply to the RAC Global AWR reports, where the thresholds are reset to defaults.
An AWR HTML report for a specific database and instance can be produced by directly using DBMS_WORKLOAD_REPOSITORY functions as follows:
set linesize 8000 termout on feedback off heading off echo off veri off trimspool on trimout on SPOOL awrrpt_1_3030_3034.html SELECT output FROM TABLE(dbms_workload_repository.awr_report_html(l_dbid =>, l_inst_num => , l_bid => , l_eid => )); SPOOL OFF set linesize 80 feedback 6 heading on veri on trimspool off
This script is provided for educational purposes only and not supported by Oracle Support Services.
It is also possible to produce other AWR reports using other procedures such as AWR_REPORT_TEXT, AWR_GLOBAL_REPORT_HTML, AWR_GLOBAL_REPORT_TEXT etc.
See Oracle Database PL/SQL Packages and Types Reference in the documentation library.
The AWR is held in a number of tables on the database.
Here are some of the top views that can be used for more detailed analysis of issues or for comparing/tracking values across snapshots.
See here for a few examples of how SQL can be used on these views to get more detail.
See the Oracle Database Reference for more detailed information and column descriptions.
This contains snapshots of the active session history information taken from memory (V$ACTIVE_SESSION_HISTORY).
It can be used to analyze many things :-
The main wait events for a SQL.
The main SQLs on which a wait event occurs.
When (which snapshots) particular SQLs were running.
On which objects wait events are occurring.
Note that this view contains samples of the activities that are occurring at specific intervals (sample times). It can be used to indicate the main events/activities, however the percentages indicate the number of times an activity/event was sampled and not the actual percentage of time spent in that activity/event.
This is the wait time for the last wait event (0 if currently waiting). So cannot be used to analyze wait events.
For waits that are always less than the sample interval (typically 10 seconds) then this is probably the best indication of activity. However, it can be inaccurate if there are individual waits over 10 seconds.
The count of the number of rows (samples) where a wait occurs can also give some idea of the most popular waits.
Multiplying by the sample interval (10 seconds) gives a very rough approximation of time spent. Note that it is subject to a significant statistical margin of error, particularly for smaller time periods. So it can only give an indication of the most popular waits.
The columns current_obj=# and current_block# can be used to analyze the objects and blocks on which wait events are occurring. The view DBA_HIST_SEG_STAT_OBJ can be used to identify the object names.
In addition the columns p1, p3 and p3 can give additional information about waits, in particular the file, object and block. The p1text, p2text and p3text columns will indicate the content of the p1, p2 and p3 columns for each event.
This contains high level information (DB Version, DB Name, Instance Name, Host Name, Platform Name, Startup Time) for each database instance (stored in the repository). This will normally be just the instances in the cluster (RAC). However, AWR information from other databases can be imported into the AWR for analysis.
This contains execution plans used for each SQL in the AWR.
Unfortunately there are no actual statistics or filters/access predicates stored for each execution plan line.
This can also be useful in identifying whether an index has been used in any execution plans.
This view along with the view DBA_HIST_SEG_STAT_OBJ is used in the Segment Statistics section of the AWR report. The AWR only reports the top objects, so this view can be useful if the statistics are spread across a lot of objects (e.g. partitions/sub-partitions).
Use the delta columns, which have the totals since the previous snapshot.
This is used in the "Top N Timed Foreground Events" and "Wait Events Statistics" section of the AWR report.
Note that the _FG columns refer to the foreground waits. Also, the totals for the previous snapshot have to be deducted to obtain the totals between the two snapshots.
This is used in the "Wait Event Histogram" section of the AWR report.
The totals for the previous snapshot have to be deducted to obtain the totals for the current snapshot.
This is used in the "SQL Statistics" section of the AWR report.
Use the delta columns, which have the totals since the previous snapshot.
This contains information, including the object name, on all the objects referenced in the repository. It is useful for obtaining the object name with many of the views above.
This contains the SQL Text for each SQL ID referenced in the repository.
This contains the snapshot information (e.g. begin and end interval times). So it can be used to identify the snapshot ids for a particular time interval (or vice versa).
This contains some AWR settings (snap interval, retention period, top n sql).
The view SYS.WRM$_WR_CONTROL gives a little more information (note the topnsql values: 2000000000 = DEFAULT, 2000000001 = MAXIMUM).
This contains the classification (e.g. Concurrency, User I/O Configuration) of wait events.
Here are some examples of how SQL can be used on the AWR Tables.
To report on the top SQL between two snapshots in the AWR (without running an AWR report) run the following SQL. This example does it for the local database and instance
Note that this particular example outputs the top 100 SQLs ordered by elapsed time, but this can easily be changed to order by other statistics (e.g. CPU time) or output fewer or more SQLs.
SELECT * FROM
(SELECT dhs.sql_id,
ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs,
ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs,
SUM(dhs.disk_reads_delta) disk_reads,
SUM(dhs.buffer_gets_delta) buffer_gets,
SUM(dhs.px_servers_execs_delta) px_server_execs,
SUM(dhs.rows_processed_delta) rows_processed,
SUM(dhs.executions_delta) executions,
ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs,
ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs,
ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs,
ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs
FROM dba_hist_sqlstat dhs,
v$database d,
v$instance i
WHERE dhs.dbid = d.dbid
AND dhs.instance_number = i.instance_number
AND dhs.snap_id > The following shows the SQL IDs on which a particular wait occurs between two snapshots in the AWR for the local database and instance.
SELECT ss.sql_id,
ss.time_waited,
ss.counts_waited,
tt.total_time,
ROUND((ss.time_waited*100/tt.total_time),1) percent
FROM
(SELECT s.sql_id,
COUNT(*) counts_waited,
SUM(time_waited) time_waited
FROM dba_hist_active_sess_history s,
dba_hist_seg_stat_obj o,
v$database d,
v$instance i
WHERE s.dbid = d.dbid
AND s.instance_number = i.instance_number
AND o.dbid (+) = s.dbid
AND o.obj# (+) = s.current_obj#
AND s.event = 'The following SQL shows the objects on which a particular wait occurs for a given SQL ID between two snapshots in the AWR for the local database and instance.
SELECT ss.sql_id,
ss.event,
ss.current_obj#,
ss.owner,
ss.object_name,
ss.object_type,
ss.time_waited,
ss.counts_waited,
tt.total_time,
ROUND((ss.time_waited*100/tt.total_time),1) percent
FROM
(SELECT s.sql_id,
s.event,
s.current_obj#,
o.owner,
o.object_name,
o.object_type,
COUNT(*) counts_waited,
SUM(time_waited) time_waited
FROM dba_hist_active_sess_history s,
dba_hist_seg_stat_obj o,
v$database d,
v$instance i
WHERE s.dbid = d.dbid
AND s.instance_number = i.instance_number
AND s.sql_id = ''
AND s.event = ''
AND o.dbid (+) = s.dbid
AND o.obj# (+) = s.current_obj#
AND s.snap_id > '
AND t.event = ''
AND t.snap_id > Note that the view DBA_HIST_ACTIVE_SESS_HISTORY contains samples of the activities that are occurring at specific intervals (sample times). It can be used to indicate the main events/activities, however the percentages indicate the number of times an activity/event was sampled and not the actual percentage of time spent in that activity/event.
These scripts are provided for educational purposes only and not supported by Oracle Support Services.
Automatic Workload Repository (AWR) reports are only available from 10g onwards. They are only available if the Diagnostic Pack is licensed.
It will be necessary to use Statspack prior to 10g.
Statspack is still available from 10g onwards and can be used if the Diagnostic Pack is not licensed.
See My Oracle Support document "AWR Reporting - Licensing Requirements Clarification (Document 1490798.1)" or Oracle Database Licensing Information in the documentation library.
The My Oracle Support document "Systemwide Tuning using STATSPACK Reports (Document 228913.1)" gives an excellent explanation of the information contained in a Statspack report.
The following My Oracle Support documents are useful further reading:
Installing and Configuring StatsPack Package (Document 149113.1)
Gathering a StatsPack snapshot (Document 149121.1)
Statistics Package (STATSPACK) Guide (Document 394937.1)
The My Oracle Support document "How to Identify Resource Intensive SQL ("TOP SQL") (Document 232443.1)" explains how Statspack can be used to identify resource intensive SQLs.
The AWR Report for a SQL Statement can be used to report on the actual runtime execution plan for long running or resource intensive SQLs that have been identified in the AWR (or elsewhere).
Note that this information can also be obtained from the Display AWR report (dbms_xplan.display_awr).
There are two SQL scripts in $ORACLE_HOME/rdbms/admin that can be used to generate this report:
awrsqrpt.sql - Produces the report for the current (local) database and instance and the specified SQL.
awrsqrpi.sql - Produces the report for a specified database, instance and SQL.
The arguments are very similar to the AWR Reports. And they can be automated in a similar way.
See Oracle Database Performance Tuning Guide for more information.
This can be used to report the Active Session History for specified SQLs, Sessions, Wait Classes, Modules etc. It can also be used to report on all activity on a database instance.
It can seamlessly report from both the AWR (view DBA_HIST_ACTIVE_SESS_HISTORY) and memory (view v$ACTIVE_SESSION_HISTORY).
If specific SQLs or Wait Events (classes) have been identified from the AWR Reports then this report can give more information on where the performance issue occurs with those SQLs and Wait Events and (in some cases) why.
See Oracle Database Performance Tuning Guide for more information.
An ASH report can be generated from SQL*Plus by running the ASH SQL scripts. These are stored under $ORACLE_HOME/rdbms/admin.
The APPS (Oracle E-Business Suite), SYS or SYSTEM users can be used to generate the reports.
Note that ASH Reports can also be produced from Enterprise Manager.
The main scripts available are:
ashrpt.sql – Produces the ASH report for the current (local) database and instance for all targets (all activity). Also for the default slot width.
ashrpti.sql - Produces the ASH report for a specified database and instance.
These scripts can also be used to produce reports for some or all instances in an Oracle Real Application Clusters (RAC) environment.
All the scripts are interactive. Arguments cannot be specified in the command line. The ASH reports could be automated in a similar way to the AWR reports.
The Oracle Database Performance Tuning Guide has more information about the arguments that the AWR reports prompt for, but here is some additional information.
The HTML format is recommended. It is clearer, easier to read and has complete SQL statements (they are not truncated).
If running for a specified instance (e.g. awrrpti.sql) then the script will list the "Instances in this Workload Repository schema " and prompt for the database id (dbid) and instance number (inst_num).
The current (local) instance will be highlighted with an asterix (*) at the left hand side of the row.
Choose an instance. This should be the database and instance on which the performance issue has been observed.
For a RAC environment then the instance can be a comma separated list of instance numbers or "ALL".
The begin time can accept relative times (before the current system time) using the format –HH24:MI or absolute start time using various date/time formats.
The duration is in minutes only (not hours and minutes).The default is a duration up to the current system time.
It is advisable to think about the begin time and duration required before running these scripts. This will normally be the time when a particular program or SQL is running.
This will be limited to the minimum granularity available (typically 10 seconds for active session history from the AWR).
This can only be specified if using ashrpti.sql.
If running ashrpti.sql the active session history can be limited to specific SQLs, Sessions, Wait Classes, Services, Modules, Actions, Client Identifiers or PL/SQL entries by specifying values for these targets. If they are all omitted (NULL) then the active session history for all activity on the specified database instance will be reported.
