ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10gR2中awrsqrpt.sql介绍

10gR2中awrsqrpt.sql介绍

原创 Linux操作系统 作者:todayboy 时间:2012-05-17 11:57:56 0 删除 编辑

 在RDBMS/admin/awrsqrpt.sql中,这个脚本可以方便地取出某个sql在某两个快照间隔内,它总的消耗的cpu时间,执行次数,逻辑读,物理读,sql的执行计划以及sql的full sql text等

 

生成HTML的执行计划很简单,如果是生成本地数据库的sql执行计划,执行awrsqrpt.sql就可以,但是如果需要生成由AWR迁移到本地数据库的分析数据,就需要使用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 的值:  html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1520519778        1 STREAM       stream       STREAM
  2400249746        1 CNDERPDB     cnderpdb1    p5a1
  2400249746        2 CNDERPDB     cnderpdb2    p5b1

输入 dbid 的值:  2400249746       --输入要生成执行计划的数据库ID
Using 2400249746 for database Id
输入 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 的值:  7

Listing the last 7 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cnderpdb1    CNDERPDB         50063 16 6月  2011 08:00     1
                              50064 16 6月  2011 09:00     1
                              50065 16 6月  2011 10:00     1
                              50066 16 6月  2011 11:00     1
                              50067 16 6月  2011 12:00     1

... ...

                              50206 22 6月  2011 07:00     1
                              50207 22 6月  2011 08:00     1
                              50208 22 6月  2011 09:00     1
                              50209 22 6月  2011 10:00     1

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  50063               --输入开始快照号
Begin Snapshot Id specified: 50063

输入 end_snap 的值:  50209                  --输入结束快照号
End   Snapshot Id specified: 50209

 


Specify the SQL Id
~~~~~~~~~~~~~~~~~~
输入 sql_id 的值:  8hm5s0k011450      --在AWR报告中看到的占用资源较大的SQL ID
SQL ID specified:  8hm5s0k011450

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_50063_50209.html.  To use this name,

press to continue, otherwise enter an alternative.

输入 report_name 的值:  d:\stream.html   --保存路径和名字

Using the report name d:\stream.html

Report written to d:\stream.html
SQL>

 

之后打开D盘下的stream.html就可以很直观的看到SQL_ID为8hm5s0k011450的执行计划

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 18,121,198 4.89 3.20
CPU Time (ms) 17,874,450 4.82 3.33
Executions 3,707,839    
Buffer Gets 404,447,392 109.08 3.85
Disk Reads 0 0.00 0.00
Parse Calls 6 0.00 0.00
Rows 9,831,284 2.65  
User I/O Wait Time (ms) 0    
Cluster Wait Time (ms) 0    
Application Wait Time (ms) 0    
Concurrency Wait Time (ms) 0    
Invalidations 0    
Version Count 38    
Sharable Mem(KB) 713    

 

Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT       3 (100)  
1    FOR UPDATE          
2      SORT ORDER BY   1 32 3 (34) 00:00:01
3        TABLE ACCESS FULL TEMPSK 1 32 2 (0) 00:00:01

 

 

source:http://streamsong.iteye.com/blog/1109377

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7583803/viewspace-730007/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle 分面查询
请登录后发表评论 登录
全部评论

注册时间:2009-02-24

  • 博文量
    75
  • 访问量
    242710