AWR資料採集方法
1. AWR資料的匯出與匯入
1.1採集目的資料庫的awr資料
進入資料庫執行awrextr.sql語句。
SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name Host
------------ ------------ ------------
* 87396644 ENMOEDU ENMOEDU
The default database id is the local one: '87396644'. To use this
database id, press
輸入要匯出的資料庫DBID 。
Enter value for dbid: 87396644
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
DB Name Snap Id Snap Started
------------ --------- ------------------
ENMOEDU 22 18 Dec 2013 16:00
23 18 Dec 2013 17:00
24 18 Dec 2013 20:48
25 18 Dec 2013 22:00
26 18 Dec 2013 23:00
27 19 Dec 2013 00:00
28 19 Dec 2013 23:28
29 20 Dec 2013 00:00
30 26 Dec 2013 14:52
31 26 Dec 2013 16:00
32 26 Dec 2013 17:00
輸入要採集的Snap Id的起始與結束值。
Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 22
Begin Snapshot Id specified: 22
Enter value for end_snap: 32
End Snapshot Id specified: 32
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
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 Directory Name from the above list (case-sensitive).
已列出所有路徑名選擇儲存檔案的路徑名 注：不能直接輸入路徑。
Enter value for directory_name: QUEST_SOO_ADUMP_DIR
Using the dump directory: QUEST_SOO_ADUMP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_22_32.
To use this name, press
an alternative.
輸入檔案的名字，回車自動儲存。
Enter value for file_name:
Using the dump file prefix: awrdat_22_32
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file: /u01/app/oracle/admin/ENMOEDU/adump/
| awrdat_22_32.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/admin/ENMOEDU/adump/
| awrdat_22_32.log
|
End of AWR Extract
1.2使用sftp傳輸檔案
sftp從目的主機獲取檔案到本地主機。
在linux命令前加l表示對本地主機操作。
sftp> lpwd
C:/Users/h/Documents
到本地主機的要儲存的路徑。
sftp> lcd E:/
sftp> pwd
/root
到儲存dmp檔案的路徑。
sftp> cd /u01/app/oracle/admin/ENMOEDU/adump/
sftp> ls
awrdat_22_32.dmp awrdat_22_32.log ENMOEDU_ora_10398_1.aud
get命令取得檔案把目的主機檔案複製到本地主機（均為當前路徑下）。
sftp> get awrdat_22_32.dmp
Downloading awrdat_22_32.dmp from /u01/app/oracle/admin/ENMOEDU/adump/awrdat_22_32.dmp
100% 11400KB 11400KB/s 00:00:00
/u01/app/oracle/admin/ENMOEDU/adump/awrdat_22_32.dmp: 11673600 bytes transferred in 0 seconds (11400 KB/s)
已經取得dmp檔案。
sftp> lls
Adobe Photoshop CS3 awrdat_22_32.dmp cs
put命令同理把本地主機的檔案上傳到目的主機。
1.3匯入採集到的資料庫awr資料
在資料庫中執行awrload語句。
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
----------------------- -------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/admin/ENMOEDU/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
Choose a Directory Name from the list above (case-sensitive).
選擇dmp檔案的儲存路徑名。注：只能填寫路徑名，不能直接填寫路徑。
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
選擇dmp檔名。
Enter value for file_name: awrdat_22_32
Loading from the file name: awrdat_22_32.dmp
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.
選擇schema_name，回車預設為AWR_STAGE。
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
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
------------------------------ --------- ------------------
SYSAUX PERMANENT *
ULTRANMS_BIG PERMANENT
ULTRANMS_DEFAULT PERMANENT
ULTRANMS_INDEX PERMANENT
ULTRANMS_PERFORMANCE PERMANENT
ULTRANMS_SMALL PERMANENT
USERS PERMANENT
Pressing
tablespace (identified by *) being used.
選擇儲存表空間，預設為SYSAUX。
Enter value for default_tablespace:
Using tablespace SYSAUX as the default tablespace for the AWR_STAGE
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 *
ULTRANMS_TEMPORARY TEMPORARY
Pressing
tablespace (identified by *) being used.
選擇臨時表空間，預設為TEMP。
Enter value for temporary_tablespace:
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file: /u01/app/oracle/admin/ENMOEDU/dpdump/ awrdat_22_32.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/
| awrdat_22_32.log
|
... Dropping AWR_STAGE user
End of AWR Load
資料匯入成功。
2. 生成AWR報告
2.1生成awr報告
因為資料庫有多組資料庫awr資料，要使用awrrpti指令碼。
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'
輸入儲存awr報告格式，預設為html型別。
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
92943150 1 ENMOEDU ENMOEDU ENMOEDU
* 92943150 1 ENMOEDU ENMOEDU HU
87396644 1 ENMOEDU ENMOEDU ENMOEDU
輸入要生成報告的資料庫DBID。
Enter value for dbid: 87396644
Using 87396644 for database Id
輸入例項號。
Enter value for inst_num: 1
Using 1 for instance number
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.
選擇awr資訊的天數，回車顯示全部snapshots。
Enter value for num_days:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ENMOEDU ENMOEDU 22 18 Dec 2013 16:00 1
23 18 Dec 2013 17:00 1
24 18 Dec 2013 20:48 1
25 18 Dec 2013 22:00 1
26 18 Dec 2013 23:00 1
27 19 Dec 2013 00:00 1
28 19 Dec 2013 23:28 1
29 20 Dec 2013 00:00 1
30 26 Dec 2013 14:52 1
31 26 Dec 2013 16:00 1
32 26 Dec 2013 17:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
選擇生成報告的起始點。
Enter value for begin_snap: 30
Begin Snapshot Id specified: 30
選擇生成報告的終點。
Enter value for end_snap: 31
End Snapshot Id specified: 31
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_30_31.html. To use this name,
press
輸入報告名。
Enter value for report_name:
Using the report name awrrpt_1_30_31.html
2.2獲取awr報告
把報告獲取到本地主機上可通過瀏覽器檢視。
sftp> cd /home/oracle
sftp> ls
awrrpt_1_30_31.html p_awr_info.sql script
sqlnet.log
sftp> lcd E:\
sftp> lpwd
E:/
sftp> get awrrpt_1_30_31.html
Downloading awrrpt_1_30_31.html from /home/oracle/awrrpt_1_30_31.html
100% 430KB 430KB/s 00:00:00
/home/oracle/awrrpt_1_30_31.html: 441242 bytes transferred in 0 seconds (430 KB/s)
3. 生成AWR SQL REPORT
因為有多組資料庫awr資訊使用awrsqrpi指令碼。
SQL> @?/rdbms/admin/awrsqrpi
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
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
92943150 1 ENMOEDU ENMOEDU ENMOEDU
* 92943150 1 ENMOEDU ENMOEDU HU
87396644 1 ENMOEDU ENMOEDU ENMOEDU
Enter value for dbid: 87396644
Using 87396644 for database Id
Enter value for inst_num: 1
Using 1 for instance number
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
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ENMOEDU ENMOEDU 22 18 Dec 2013 16:00 1
23 18 Dec 2013 17:00 1
24 18 Dec 2013 20:48 1
25 18 Dec 2013 22:00 1
26 18 Dec 2013 23:00 1
27 19 Dec 2013 00:00 1
28 19 Dec 2013 23:28 1
29 20 Dec 2013 00:00 1
30 26 Dec 2013 14:52 1
31 26 Dec 2013 16:00 1
32 26 Dec 2013 17:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 30
Begin Snapshot Id specified: 30
Enter value for end_snap: 31
End Snapshot Id specified: 31
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
輸入要生成報告的sql id。
Enter value for sql_id: 5yv7yvjgjxugg
SQL ID specified: 5yv7yvjgjxugg
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_30_31.html. To use this name,
press
輸入生成報告名。
Enter value for report_name: 5yv7yvjgjxugg.html
Using the report name 5yv7yvjgjxugg.html
4. 命令列生成sql的執行計劃
使用指令碼在命令列生成執行計劃，減少生成sql report的時間。
編輯指令碼，使用sbms_xplan.display_awr。四個引數分別為
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
編輯指令碼
[oracle@HU ~]$ vi sq.sql
set linesize 150
set pagesize 999
select * from table(dbms_xplan.display_awr('&SQLID',null,'&DBID','BASIC'));
"sq.sql" 4L, 109C written
[oracle@HU ~]$ exit
exit
在資料庫中執行指令碼。
SQL> @sq
輸入sql id。
Enter value for sqlid: 5yv7yvjgjxugg
輸入dbid。
Enter value for dbid: 87396644
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5yv7yvjgjxugg
--------------------
select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO
Pool Memory'
Plan hash value: 1461717084
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | FIXED TABLE FULL | X$KSLED |
| 3 | SORT JOIN | |
| 4 | FIXED TABLE FULL| X$KSLEI |
--------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5C160134
2 - SEL$5C160134 / D@SEL$3
4 - SEL$5C160134 / S@SEL$3
Note
-----
- rule based optimizer used (consider using cbo)
28 rows selected.
5. 找出執行計劃中有全表掃描的sql id
編輯指令碼利用v$sql_plan檢視檢視有權表掃描的sql。
[oracle@HU ~]$ vi sqlid.sql
select SQL_ID from v$sql_plan v where v.operration = 'TABLE ACCESS' and v.OPTIONS = 'FULL';
"sqlid.sql" 2L, 92C written
[oracle@HU ~]$ exit
exit
SQL> @sqlid
SQL_ID
-------------
4hdr665tv81by
25fu98m3hw2ts
25fu98m3hw2ts
77mtwmnd4c4pk
8rc705wcac4qn
6.總結
對於資料庫進行分析的方法，可以使用這些方法提高工作效率，也是最基本的技術。
大部分是對sql語句的分析，所以要掌握對sql分析的方法。先從awr報告中找出出問題或待分析的sql語句的sql id，生成sql report或直接使用指令碼在命令列中顯示。檢視執行計劃等資訊有助於對語句的分析。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29385747/viewspace-1065802/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle AWR資料採集Oracle
- 資料採集的方法有哪些
- 資料採集知識分享|4大資料採集方式都有什麼？大資料
- 地圖資料採集，包括百度地圖採集，高德地圖採集，360地圖採集地圖
- phpQuery採集網站資料PHP網站
- 遊戲平臺採集資料遊戲
- amazon產品採集資料
- 工商資訊資料採集思路
- 網頁資料採集器網頁
- 資料採集作業2
- 資料採集作業二
- 資料採集作業3
- 資料採集實驗四
- 大資料_資料採集_網頁01大資料網頁
- PHP採集商家資訊及採集方法概述（下）PHP
- 【京東】商品list列表採集+類目下的商品列表資料採集
- 大資料技術之資料採集篇大資料
- 大資料採集：fillna函式大資料函式
- 資料採集工具是什麼
- 高效採集資料業務更安心
- 電商平臺資料採集介面
- Android 效能資料採集（概要）Android
- 主機日常效能資料採集
- 多執行緒資料採集執行緒
- PHP採集商家資訊及採集方法概述（上）薦PHP
- 前端埋點資料採集（一）採集系統架構設計前端架構
- 學會System Generator（22）影像採集與輸出（資料流方法）
- 獲取Oracle資料庫awr報告方法Oracle資料庫
- 程式採集裝置資料，不穩定，突然不採集， 程式崩潰
- 採集淘寶商品詳情頁資料
- PHP 資料採集的一種思路PHP
- 爬蟲資料採集的工作原理爬蟲
- 資料採集實踐作業2
- Labview軟體、NI資料採集卡、汽車發動機資料採集學習總結(二)View
- 【Python資料採集】國家自然科學基金大資料知識管理服務門戶資料採集Python大資料
- ViCANdo — 智慧駕駛資料採集及資料分析平臺
- Oracle資料倉儲的實時資料採集XSOracle
- 讓前端監控資料採集更高效前端