AWR(五)-利用AWR生成SQL执行计划(SQLREPORT)并进行优化
有些时候，我们需要分析占用资源比较大的sql的执行计划，也需要将sql的执行计划以报告的形式反馈给客户，由于AWR报告里的SQL通常都是些变量，因此以命令行方式生成sql的执行计划就很麻烦，而且也不美观，利用awrsqrpt.sql脚本可以生成HTML格式的SQL执行计划，既美观又方便。以下是我写的AWR的一些其他文章，详见列表：
生成HTML的执行计划很简单，如果是生成本地数据库的SQL执行计划，执行awrsqrpt.sql脚本就可以，但是如果需要生成由AWR迁移到本地的数据库的SQL执行计划，就需要使用awrsqrpi.sql脚本出马啦，下面是我演示的步骤。
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' 输入 report_type 的值:
输入您想将SQL执行计划保存为什么格式，目前支持文本和html格式。
输入 report_type 的值: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1520519778 1 STREAM stream STREAM 1645778343 2 B1ACDB B1ACDB2 acdbs 1645778343 1 B1ACDB B1ACDB1 acdbm
输入 dbid 的值:
选择你要对哪个数据库操作，DB_ID前面有个*号的是本地的数据库，没有*号的是迁移到本地的数据库信息，AWR数据迁移详见上面列表。我选择DB_ID为1645778343的数据库。
输入 dbid 的值: 1645778343 Using 1645778343 for database Id 输入 inst_num 的值:
选择生成哪个节点的SQL执行计划，ORACLE已经列出了一共有DB_ID为1645778343的数据库是RAC环境，一共有2个节点，这里选择对节点1操作。
输入 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. 输入 num_days 的值:
输入你想让ORACLE列出哪些快照，这些快照要包含你想生成的SQL执行计划的的快照范围，比如我需要生成9407和9408这两个快照号范围内的一条SQL的执行计划。
输入 num_days 的值: 10
Listing the last 10 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
B1ACDB1 B1ACDB 9373 10 11月 2011 00:00 1
9374 10 11月 2011 01:00 1
9375 10 11月 2011 02:00 1
9376 10 11月 2011 03:00 1
... ...
9406 11 11月 2011 09:00 1
9407 11 11月 2011 10:00 1
9408 11 11月 2011 11:00 1
9409 11 11月 2011 12:00 1
... ...
9551 17 11月 2011 10:00 1
9552 17 11月 2011 11:00 1
9553 17 11月 2011 12:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:
输入起始快照号，由于我要生成在9407和9408这个时间段的SQL执行计划，所以起始快照号输入9407，结束快照号输入9408。
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 begin_snap 的值: 9407 Begin Snapshot Id specified: 9407 输入 end_snap 的值: 9408 End Snapshot Id specified: 9408 Specify the SQL Id ~~~~~~~~~~~~~~~~~~ 输入 sql_id 的值:
输入您要生成的SQL执行计划的SQL_ID，这个SQL_ID一般是AWR报告里占用资源比较大的SQL，比如我想查看AWR报告中SQL ordered by Reads部分占据第一位的SQL的执行计划，下面是AWR报告中SQL ordered by Reads部分占据第一位的SQL信息。
AWR报告显示，SQL_ID为ddjfun7qrf86a的SQL产生了大量的逻辑读，我们要生成这个SQL的执行计划，那么就输入这个SQL_ID–ddjfun7qrf86a。
输入 sql_id 的值: ddjfun7qrf86a SQL ID specified: ddjfun7qrf86a Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrsqlrpt_1_9407_9408.html. To use this name, press to continue, otherwise enter an alternative. 输入 report_name 的值:
输入你要将这个执行计划保存到哪个路径下的哪个文件，也就是保存的文件名。
输入 report_name 的值: d:\SQL-9407-9408.html Using the report name d:\SQL-9407-9408.html
输入完路径之后，开始刷屏（这个通常会很快），跑完之后就可以去刚才输入的路径下去找SQL执行计划的那个文件啦，下面摘录生成SQL执行计划日志的起始和结尾部分。
AWR SQL Report for DB: B1ACDB, Inst: B1ACDB1, Snaps: 9407-9 408, SQL Id: ddjfun7qrf86a
上面是日志的起始部分，列出了DB_ID、操作节点的实例名、快照起止号、SQL_ID。这些部分在生成的SQL执行计划的文件里都有体现。
Report written to d:\SQL-9407-9408.html
上面是日志的结尾部分，也就是告诉您这个SQL的执行计划保存在哪里，叫什么名字，当然这个结尾部分的上面还列出了这个SQL_ID的SQL语句，因为下面有提，此处略。
下面就分析下这个SQL的执行计划，下面是SQL语句。
下面是这个SQL的执行计划：
这个执行计划显示，COST最大的部分就是DSPTRANSFERCOMMAND表的全表扫描，很直观很好看吧，下面摘录我司权威、资深专家对这个SQL的优化案例。
1. 执行该SQL语句，测试SQL的返回结果及消耗时间，发现该SQL语句仅返回一条结果，但耗时较长。
SQL> SELECT MACHINENAME, (processcapacity - processcount) capa,
2 (select count(*) cnt from dsptransfercommand
3 where destinationmachine = '1AFBF01B' and factoryname = 'ARRAY'
4 and requesttype = 'UNLOAD' and CASSETTETYPE = 'FULL' and state in
('MOVING', 'OICCOMMAND') ) as cnt FROM MACHINE WHERE MACHINENAME =
'1AFBF01B' and machinestatename <> 'DOWN';
5 MACHINENAME CAPA CNT
--------------------------------------- ------- ----------
1AFBF01B 0 1
Elapsed: 00:00:11.86
2. 查询dsptransfercommand表的总数据量，在53万条左右，可以考虑在相关列建立索引。
SQL> select count(*) cnt from dsptransfercommand;
CNT
----------
537967
3. 分析dsptransfercommand表上的索引，发现在本SQL语句的相关列destinationmachine、factoryname、requesttype、cassettetype和state上都没有索引。
SQL> select index_name, column_name, column_position from user_ind_columns
where table_name = upper('dsptransfercommand') order by 1, 3;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------------- --------------------- ---------------------------
DSPTRANSFERCMD_IDX_01 CASSETTENAME 1
DSPTRANSFERCMD_IDX_02 LOTNAME 1
DSPTRANSFERCMD_IDX_03 CREATETIME 1
PK_DSPTRANSFERCOMMAND TRANSFERID 1
PK_DSPTRANSFERCOMMAND CASSETTENAME 2
4. 具体分析destinationmachine，factoryname，factoryname，cassettetype，state这五列，对比这五列唯一键的数量和表的总数据量，发现单列上唯一键的数量都远远小于表的总数据量，因此在单列上建索引，索引的选择性很差，价值不大。考虑以destinationmachine为前导列建复合索引。
SQL> select count(distinct destinationmachine) cdd, count(distinct factoryname) cdf, count(distinct requesttype) cdr, count(distinct CASSETTETYPE) cdc, count(distinct state) cds from dsptransfercommand; CDD CDF CDR CDC CDS ---------- --------- ---------- ---------- ---------- 259 4 4 2 8
5. 以下是复合索引的创建过程
SQL> CREATE INDEX DSPTRANSFERCMD_IDX_04 ON dsptransfercommand( destinationmachine, factoryname, requesttype, CASSETTETYPE) 2 TABLESPACE MES_DATA_IDX;
6. 测试调优效果
SQL> SELECT MACHINENAME, (processcapacity - processcount) capa,
2 (select count(*) cnt from dsptransfercommand
3 where destinationmachine = '1AFBF01B' and factoryname = 'ARRAY'
4 and requesttype = 'UNLOAD' and CASSETTETYPE = 'FULL' and state
in ('MOVING', 'OICCOMMAND') ) as cnt
5 FROM MACHINE WHERE MACHINENAME = '1AFBF01B' and
machinestatename <> 'DOWN';
MACHINENAME CAPA CNT
----------------- ------------------- ----------
1AFBF01B 0 1
Elapsed: 00:00:00.02
7. 优化结果报告
通过在表dsptransfercommand上建立复合索引，大大降低了SQL语句的执行时间：从原来的11.86秒降低到了0.02秒，优化效果显著。
优化前：
Elapsed: 00:00:11.86
优化后：
Elapsed: 00:00:00.02
8. 优化总结：通过在表dsptransfercommand上建立复合索引，大大降低了SQL语句的执行时间（从原来的11.86秒降低到了0.02秒），逻辑读也从原来的约5万字节降到了4600字节，优化效果显著。
AWR(五)-利用AWR生成SQL执行计划(SQLREPORT)并进行优化：等您坐沙发呢！
发表评论
