当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

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字节,优化效果显著。

本文固定链接: http://www.dbdream.com.cn/2011/12/awr%e4%ba%94-%e5%88%a9%e7%94%a8awr%e7%94%9f%e6%88%90sql%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92%e5%b9%b6%e8%bf%9b%e8%a1%8c%e4%bc%98%e5%8c%96/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2011年12月06日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: AWR(五)-利用AWR生成SQL执行计划(SQLREPORT)并进行优化 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

AWR(五)-利用AWR生成SQL执行计划(SQLREPORT)并进行优化:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter