Basic AWR knowledge

Source: Internet
Author: User

Basic AWR knowledge

Since Oracle began to support AWR for 10 Gb, in addition to more new content for reference, compared with Statspack, HTML saving format also provides a lot of convenience for viewing documents.

I recently read the books of nimiek and Nishi. Although I haven't finished reading the AWR chapter, I have thoroughly explained some common methods used in AWR, I personally feel very practical, at least no nonsense, so I will summarize it here to facilitate future retrieval.

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

1. AWR stands for Automatic Workload Repository. The content is based on the data stored in the AWR database, provided that you have purchased the relevant license.
 
2. AWR collects statistical data every 60 minutes by default. It is saved for one week and then deleted. The statistical data is stored in the database.
 
3. To correctly collect statistics, STATISTICS_LEVEL is set to TYPICAL (default) or ALL.
 
4. AWR consists of many tables, which belong to the SYS mode and are usually stored in the SYSAUX tablespace. All AWR table names start with the identifier "WR": Metadata (WRM $), history/variable data (WRH $, WRR $, and WRI $), and advisor) feature-related AWR table (WRI $ ). The DBA view that can be queried for the AWR repository, starting with DBA_HIST.
 
5. You can use the DBMS_WORKLOAD_REPOSITORY package to modify the snapshot collection interval.
 
Exec dbms_workload_repository.modify_snapshot_settings-
 
(Retention = & gt; 20160, interval = & gt; 15 );
 
Use the modify_snapshot_settings process of the dbms_workload_repository package to modify the snapshot collection parameters, that is, to modify the collection time for 15 minutes, and retain the time for 20160 minutes (14 days ).
 
When the interval is set to 0, it indicates that all statistics are stopped. (in this case, I think the translation is ambiguous. The AWR snapshot is stopped, not collected ).
 
6. view the current retention time and Interval Settings of AWR:
 
Select * from dba_hist_wr_control;
 
7. Create or delete a snapshot:
 
Exec dbms_workload_repository.create_snapshot;
 
Exec dbms_workload_repository.drop_snapshot_range (low_snap_id = & gt; 1107, high_snap_id = & gt; 1108 );
 
8. View All snapshots:
 
Select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
 
9. The 11g uses the scheduling job named GATHER_STATS_JOB to collect AWR statistics. When you create an Oracle database, the job is automatically created and activated.
 
10. To view a job, refer to the view:
 
Select a. job_name, a. enabled, c. window_name, c. repeat_interval
 
From dba_scheduler_jobs a, dba_scheduler_wingroup_members B, dba_scheduler_windows c
 
Where job_name = 'Collect _ STATS_JOB'
 
And a. schedule_name = B. window_group_name
 
And B. window_name = c. window_name;
 
JOB_NAME ENABL WINDOW_NAME REPEAT_INTERVAL
 
Certificate ------------------------------------------------------------------------------------------------------------------------------------
 
GATHER_STATS_JOB TRUE WEEKEND_WINDOW freq = daily; byday = SAT; byhour = 0; byminute = 0; bysecond = 0


GATHER_STATS_JOB TRUE WEEKNIGHT_WINDOW freq = daily; byday = MON, TUE, WED, THU, FRI; byhour = 22; byminute = 0; bysecond = 0

Indicates a job that collects statistics in two windows. WEEKEND_WINDOW is executed at every Saturday. WEEKNIGHT_WINDOW is executed at every Monday to Friday.

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.