1. AWR数据的导出与导入........................................................................................................................................ 3
1.1 采集目的数据库的awr数据..................................................................................................................................... 3
1.2 使用sftp传输文件................................................................................................................................................. 5
1.3 导入采集到的数据库awr数据.................................................................................................................................. 6
2. 生成AWR报告.................................................................................................................................................... 9
2.1 生成awr报告....................................................................................................................................................... 9
2.2 获取awr报告....................................................................................................................................................... 11
3. 生成AWR SQL REPORT....................................................................................................................................... 11
4. 命令行生成sql的执行计划..................................................................................................................................... 13
5. 找出执行计划中有全表扫描的sql id......................................................................................................................... 14
6. 总结............................................................................................................................................................ 15
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 to continue, otherwise enter an alternative.
输入要导出的数据库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 without
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 to continue, otherwise enter
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 to continue, otherwise enter
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 will result in the recommended default
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 will result in the database's default temporary
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 without
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 to continue, otherwise enter an alternative.
输入报告名。
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 without
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 to continue, otherwise enter an alternative.
输入生成报告名。
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29385747/viewspace-1065802/
213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
