A Brief Introduction to awr-related scripts for discovering the beauty of Oracle

A Brief Introduction to awr-related scripts for discovering the beauty of Oracle


As you know, under $ ORACLE_HOME/rdbms/admin, there are the following scripts (my environment is 11.2.0.4.2 ):
[Oracle @ rh64 ~] $ Cd $ ORACLE_HOME/rdbms/admin
[Oracle @ rh64 admin] $ ls-ltr awr *
-Rw-r -- 1 oracle oinstall 1999 Oct 24 2003 awrrpt. SQL
-Rw-r -- 1 oracle oinstall 49166 Sep 1 2004 awrinfo. SQL
-Rw-r -- 1 oracle oinstall 1469 Jan 5 2005 awrsqrpt. SQL
-Rw-r -- 1 oracle oinstall 2462 Jan 5 2005 awrinpnm. SQL
-Rw-r -- 1 oracle oinstall 20892 May 23 2005 awrddindium. SQL
-Rw-r -- 1 oracle oinstall 2005 May 27 2005 awrddrpt. SQL
-Rw-r -- 1 oracle oinstall 8603 Mar 3 2006 awrinput. SQL
-Rw-r -- 1 oracle oinstall 1148 Dec 1 2006 awrblmig. SQL
-Rw-r -- 1 oracle oinstall 7440 Mar 13 2008 awrginp. SQL
-Rw-r -- 1 oracle oinstall 16457 Mar 13 2008 awrgdindium. SQL
-Rw-r -- 1 oracle oinstall 11082 Mar 24 2009 awrextr. SQL
-Rw-r -- 1 oracle oinstall 1523 Apr 29 2009 awrgrpt. SQL
-Rw-r -- 1 oracle oinstall 1897 Apr 29 2009 awrgdrpt. SQL
-Rw-r -- 1 oracle oinstall 10368 Jul 15 2009 awrload. SQL
-Rw-r -- 1 oracle oinstall 6803 Jul 25 2011 awrsqrpi. SQL
-Rw-r -- 1 oracle oinstall 7704 Jul 25 2011 awrrpti. SQL
-Rw-r -- 1 oracle oinstall 6444 Jul 25 2011 awrkgti. SQL
-Rw-r -- 1 oracle oinstall 7393 Jul 25 2011 awrgdrpi. SQL
-Rw-r -- 1 oracle oinstall 7450 Jul 25 2011 awrddrpi. SQL


Let's introduce them one by one:
1. awrrpt. SQL
This script is no stranger to everyone. We almost need to execute it when analyzing system performance. Let's take a look at some interesting things.
[Oracle @ rh64 admin] $ cat awrrpt. SQL


Rem $ Header: awrrpt. SQL 24--2003.12: 04: 53 pbelknap Exp $
Rem
Rem awrrpt. SQL
Rem
Rem Copyright (c) 1999,200 3, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem awrrpt. SQL
Rem
Rem DESCRIPTION
Rem This script defaults the dbid and instance number to that of
Rem current instance connected-to, then CILS awrrpti. SQL to produce
Rem the Workload Repository report.
Rem
Rem NOTES
Rem Run as select_catalog privileges.
Rem This report is based on the Statspack report.
Rem
Rem If you want to use this script in an non-interactive fashion,
Rem see the 'customer-mizmizable report setting' section in
Rem awrrpti. SQL
Rem
Rem MODIFIED (MM/DD/YY)
Rem pbelknap 10/24/03-swrfrpt to awrrpt
Rem pbelknap 10/14/03-moving params to rpti
Rem pbelknap 10/02/03-adding non-interactive mode cmcnt
Rem mlfeng 09/10/03-heading on
Rem aime 04/25/03-aime_going_to_main
Rem mlfeng 01/27/03-mlfeng_swrf_reporting
Rem mlfeng 01/13/03-Update comments
Rem mlfeng 07/08/02-swrf flushing
Rem mlfeng 06/12/02-Created
Rem


--
-- Get the current database/instance information-this will be used
-- Later in the report along with bid, eid to lookup snapshots


Set echo off heading on underline on;
Column inst_num heading "Inst Num" new_value inst_num format 99999;
Column inst_name heading "Instance" new_value inst_name format a12;
Column db_name heading "DB Name" new_value db_name format a12;
Column dbid heading "DB Id" new_value dbid format 9999999999 just c;


Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~


Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v $ database d,
V $ instance I;


@ Awrrpti


Undefine num_days;
Undefine report_type;
Undefine report_name;
Undefine begin_snap;
Undefine end_snap;
--
-- End of file






------ >>> See the description? This script can only capture the performance information of this example. In fact, this script is called awrrpti. SQL, and AWR is developed based on the Statspack report of 9i.
AWR is available from 10 Gb. In the past, 9I was statspack. In the past, I often heard the old DBA say statspack, indicating that the experience is unknown. At the same time, you must have the select_catalog permission to run this script.
Because this is a delivery type, if you do not want to use interactive, you can only use awrrpti. SQL.


2. awrrpti. SQL
This is the original AWR generated script. It indicates that two corresponding time points are required to run this script, this script can be used to retrieve
AWR report. If you want to use non-interactive mode, refer to the following example:
Rem If you want to use this script in an non-interactive fashion,
Rem without executing the script through awrrpt, then
Rem do something similar to the following:
Rem
Rem define inst_num = 1;
Rem define num_days = 3;
Rem define inst_name = 'instance ';
Rem define db_name = 'database ';
Rem define dbid = 4;
Rem define begin_snap = 10;
Rem define end_snap = 11;
Rem define report_type = 'text ';
Rem define report_name =/tmp/swrf_report_10_11.txt
Rem @@? /Rdbms/admin/awrrpti


3. awrinfo. SQL
This script outputs some awr information, the report system platform, and the space occupied by AWR. The default format is txt.


4. awrsqrpt. SQL
This script is the script used to output the SQL Report. awrsqrpti. SQL called in the background


5. awrsqrpi. SQL
This script is used to obtain the SQL information report. It can also be customized and can adopt non-delivery mode.


6. awrinpnm. SQL
This script runs an article and does not show the specific effect.


7. awrddindium. SQL
Why?


8. awrgrpt. SQL/awrkgti. SQL
This is easy to use. Obtain the global AWR report. But it takes more than 11 GB. It's easy to see what the RAC Nic traffic is, but you don't know if it's accurate.


9. awrddrpt. SQL/awrddrpi. SQL
Compared with the AWR report script, it is commonly used for analysis performance.


10. awrgdrpt. SQL/awrgdrpi. SQL
Global AWR report


11. awrinput. SQL
Nothing valuable


12. awrblmig. SQL
The AWR baseline migration script may be used during upgrade.


13. awrginp. SQL
AWR global input script


14. awrgdindium. SQL
Awrgdindium. SQL-AWR Glopal Compare Period Report Input variables


15. awrextr. SQL
The AWR script for exporting database information may be used during migration.


16. awrload. SQL
AWR import information script, used with the above


----------- It's too bad, there's no dry goods, and there's no dry goods to write in the future!