Oracle AWR資料採集
1 匯出awr資料
很多時候我們直接在客戶機器上分析awr不太方便,需要通過收集客戶awr資訊到另一臺機器上進行分析資料庫效能等。這種情況下,就需要對客戶的awr資料進行匯出,然後匯入到其他機器上,再進行深入分析。
1.1 檢視所有awr指令碼
[oracle@rac1 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -l awr*
-rw-r--r-- 1 oracle oinstall 1148 Dec 1 2006 awrblmig.sql
-rw-r--r-- 1 oracle oinstall 20892 May 23 2005 awrddinp.sql
-rw-r--r-- 1 oracle oinstall 7450 Jul 25 2011 awrddrpi.sql
-rw-r--r-- 1 oracle oinstall 2005 May 27 2005 awrddrpt.sql
-rw-r--r-- 1 oracle oinstall 11082 Mar 24 2009 awrextr.sql
-rw-r--r-- 1 oracle oinstall 16457 Mar 13 2008 awrgdinp.sql
-rw-r--r-- 1 oracle oinstall 7393 Jul 25 2011 awrgdrpi.sql
-rw-r--r-- 1 oracle oinstall 1897 Apr 29 2009 awrgdrpt.sql
-rw-r--r-- 1 oracle oinstall 7440 Mar 13 2008 awrginp.sql
-rw-r--r-- 1 oracle oinstall 6444 Jul 25 2011 awrgrpti.sql
-rw-r--r-- 1 oracle oinstall 1523 Apr 29 2009 awrgrpt.sql
-rw-r--r-- 1 oracle oinstall 49166 Sep 1 2004 awrinfo.sql
-rw-r--r-- 1 oracle oinstall 2462 Jan 5 2005 awrinpnm.sql
-rw-r--r-- 1 oracle oinstall 8603 Mar 3 2006 awrinput.sql
-rw-r--r-- 1 oracle oinstall 10368 Jul 15 2009 awrload.sql
-rw-r--r-- 1 oracle oinstall 7704 Jul 25 2011 awrrpti.sql
-rw-r--r-- 1 oracle oinstall 1999 Oct 24 2003 awrrpt.sql
-rw-r--r-- 1 oracle oinstall 6803 Jul 25 2011 awrsqrpi.sql
-rw-r--r-- 1 oracle oinstall 1469 Jan 5 2005 awrsqrpt.sql
1.2 使用sys使用者或DBA角色使用者登入sqlplus
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 16 10:43:02 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
1.3 執行awrextr.sql指令碼匯出awr資料
SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
1.4 按提示輸入dbid
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name Host
------------ ------------ ------------
* 831572945 RACDB rac1
* 831572945 RACDB rac2
The default database id is the local one: ' 831572945'. To use this
database id, press
Enter value for dbid: 831572945
Using 831572945 for Database ID
1.5 輸入天數
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
specifying a number lists all completed snapshots.
Enter value for num_days: 7
Listing the last 7 days of Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
RACDB 1 12 Dec 2013 08:00
2 12 Dec 2013 09:00
3 14 Dec 2013 06:03
4 15 Dec 2013 09:47
5 15 Dec 2013 20:08
6 15 Dec 2013 21:00
7 15 Dec 2013 22:00
8 15 Dec 2013 23:00
9 16 Dec 2013 00:00
10 16 Dec 2013 01:00
11 16 Dec 2013 02:00
DB Name Snap Id Snap Started
------------ --------- ------------------
RACDB 12 16 Dec 2013 03:00
13 16 Dec 2013 04:00
14 16 Dec 2013 05:01
15 16 Dec 2013 06:00
16 16 Dec 2013 07:00
17 16 Dec 2013 08:00
18 16 Dec 2013 09:00
19 16 Dec 2013 10:00
1.6 輸入開始和結束快照id
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16
Begin Snapshot Id specified: 16
Enter value for end_snap: 19
End Snapshot Id
specified: 19
1.7 輸入生成AWR資料檔案路徑
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log
/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_2/ccr/state
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/xml
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
1.8 輸入檔名
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_16_19.
To use this name, press
an alternative.
Enter value for file_name: myawrdata
Using the dump file prefix: myawrdata
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/
| myawrdata.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/
| myawrdata.log
|
End of AWR Extract
1.9 連線sftp將生成的dmp檔案複製到本機
sftp> lcd d:/AWR_data/
sftp> get /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp
Downloading myawrdata.dmp from /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp
100% 7660KB 1532KB/s 00:00:05
/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp:
7843840 bytes transferred in 5 seconds (1532 KB/s)
2 匯入awr資料
將生成的myawrdata.dmp檔案匯入到自己的資料庫中。
2.1 登入本地資料庫執行awrload.sql指令碼
[oracle@ENMOEDU admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 14:08:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging
schema name to load AWR data into
~
2.2 輸入路徑dmp檔案存放路徑
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/sales_history/
DATA_PUMP_DIR /u01/app/oracle/admin/ENMOEDU/dpdump/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/product_media/
MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/product_media/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
Directory Name Directory Path
------------------------------ -------------------------------------------------
QUEST_SOO_ADUMP_DIR /u01/app/oracle/admin/ENMOEDU/adump/
QUEST_SOO_BDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/
QUEST_SOO_CDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/cdump/
QUEST_SOO_UDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry/
SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry//2002/Sep
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
2.3 將dmp檔案拷貝到DATA_PUMP_DIR路徑下
sftp> lcd d:/AWR_data
sftp> cd /u01/app/oracle/admin/ENMOEDU/dpdump
sftp> put myawrdata.dmp
Uploading myawrdata.dmp to /u01/app/oracle/admin/ENMOEDU/dpdump/myawrdata.dmp
100% 7660KB 7660KB/s 00:00:00
d:/AWR_data/myawrdata.dmp: 7843840 bytes transferred in
0 seconds (7660 KB/s)
2.4 輸入檔名
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: myawrdata
Loading from the file name: myawrdata.dmp
2.5 輸入臨時使用者名稱(預設AWR_STAGE)
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
2.6 輸入預設表空間（預設SYSAUX）
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
EXAMPLE PERMANENT
MGMT_AD4J_TS PERMANENT
MGMT_ECM_DEPOT_TS PERMANENT
MGMT_TABLESPACE PERMANENT
SYSAUX PERMANENT *
TEST PERMANENT
USERS PERMANENT
Pressing
tablespace (identified by *) being used.
Enter value for default_tablespace:
Using tablespace SYSAUX as the default tablespace for
the AWR_STAGE
2.7 輸入臨時表空間（預設TEMP）
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP TEMPORARY *
Pressing
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace for
AWR_STAGE
2.8 等待匯入完成
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u01/app/oracle/admin/ENMOEDU/dpdump/
| myawrdata.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /u01/app/oracle/admin/ENMOEDU/dpdump/
| myawrdata.log
|
... Dropping AWR_STAGE user
End of AWR Load
3 檢視awr報告
3.1 執行awrrpti.sql指令碼檢視awr報告
SQL> @?/rdbms/admin/awrrpti
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified:
html
3.2 輸入dbid
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 87396644 1 ENMOEDU ENMOEDU ENMOEDU
831572945 1 RACDB RACDB1 rac1
Enter value for dbid: 831572945
Using 831572945 for database Id
3.3 輸入例項號
Enter value for inst_num: 1
Using 1 for instance number
3.4 輸入日期（不填顯示全部）
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
3.5 輸入快照開始和結束id
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
RACDB1 RACDB 16 16 Dec 2013 07:00 1
17 16 Dec 2013 08:00 1
18 16 Dec 2013 09:00 1
19 16 Dec 2013 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16
Begin Snapshot Id specified: 16
Enter value for end_snap: 19
End Snapshot Id
specified: 19
3.6 輸入將生成的awr報告名稱
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_16_19.html. To use this name,
press
Enter value for report_name: awr_racdb1.html
Using the report name awr_racdb1.html
…….(此處略)
Report written to awr_racdb1.html
3.7 將生成的awr拷貝到本機
sftp> cd /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin
sftp> lcd d:/AWR_data
sftp> ls *.html
awr_racdb1.html
sftp> get awr_racdb1.html
Downloading awr_racdb1.html from /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/awr_racdb1.html
100% 522KB 522KB/s 00:00:00
/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/awr_racdb1.html: 535105 bytes transferred in 0 seconds (522 KB/s)
附：DBMS_XPLAN中的display_awr函式
DBMS_XPLAN包包括一系列函式，主要是用於顯示特定SQL語句的執行計劃, 對於awr中的執行計劃，則是用display_awr函式顯示儲存在AWR歷史資料的執行計劃。
例如：select *from table(DBMS_XPLAN. display_awr(SQL_ID, PLAN_HASH_VALUE, DB_ID, FORMAT));
DISPLAY_AWR函式提示：要正常呼叫DISPLAY_AWR引數，必須對以下檢視有許可權：DBA_HIST_SQL_PLAN和DBA_HIST_SQLTEXT的SELECT。
引數描述：
SQL_ID：所顯示執行計劃的SQL語句的ID。該ID可以從DBA_HIST_SQL_PLAN.SQL_ID或DBA_HIST_SQLTEXT.SQL_ID獲得，該引數必須指定非空值，沒有預設值；
PLAN_HASH_VALUE：執行計劃的雜湊值。我們之前提到，每個執行計劃都有一個雜湊值。通過該值，可以顯示SQL語句的特定執行計劃。如果該引數未指定或為NULL，則會顯示語句的所有執行計劃；
DB_ID：指定顯示哪個資料庫的執行計劃，預設為本地資料庫ID；
提示：我們可以將其他資料庫的AWR資料匯入本地資料庫進行分析。
FORMAT：格式化控制字串。與DISPLAY的相同選項類似。
DBA_建瑾
2013.12.26
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29324876/viewspace-1064768/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- AWR資料採集方法
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- Oracle資料倉儲的實時資料採集XSOracle
- 資料採集知識分享|4大資料採集方式都有什麼？大資料
- phpQuery採集網站資料PHP網站
- 遊戲平臺採集資料遊戲
- amazon產品採集資料
- 工商資訊資料採集思路
- 網頁資料採集器網頁
- 大資料_資料採集_網頁01大資料網頁
- 【京東】商品list列表採集+類目下的商品列表資料採集
- 大資料技術之資料採集篇大資料
- 大資料採集：fillna函式大資料函式
- 資料採集的方法有哪些
- 資料採集工具是什麼
- 高效採集資料業務更安心
- 電商平臺資料採集介面
- Android 效能資料採集（概要）Android
- 主機日常效能資料採集
- 多執行緒資料採集執行緒
- oracle資料集Oracle
- 採集淘寶商品詳情頁資料
- PHP 資料採集的一種思路PHP
- 爬蟲資料採集的工作原理爬蟲
- Labview軟體、NI資料採集卡、汽車發動機資料採集學習總結(二)View
- 獲取Oracle資料庫awr報告方法Oracle資料庫
- ViCANdo — 智慧駕駛資料採集及資料分析平臺
- 讓前端監控資料採集更高效前端
- PLC實時資料採集如何實現？
- JavaCV FFmpeg採集攝像頭YUV資料Java
- Python網路資料採集（爬蟲）Python爬蟲
- JSTracker 之前端異常資料採集JS前端
- Python資料科學（五） 資料處理和資料採集Python資料科學
- 大資料如何採集資料？大資料的資料從何而來？大資料
- 網路分流器-移動信令採集裝置-大資料採集方案大資料
- 使用火蜘蛛採集器Firespider採集天貓商品資料並上傳到微店IDE
- [英語流利說]管理資料採集需求～在英語流利說，我們這樣管理資料採集需求...
- 玩轉大資料系列之一：資料採集與同步大資料