Oracle awr sqlid,通过AWR报告中记录的 SQL Id获得SQL语句的执行计划

:oracleora92>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 3 15:16:03 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys as sysdba请输入口令:已连接。

SQL> @?rdbmsadminutlxplan

表已创建。

SQL> create public synonym plan_table for plan_table;

同义词已创建。

SQL> grant all on plan_table to public ;

授权成功。

SQL> @?sqlplusadminplustrce

SQL>

SQL> drop role plustrace;

drop role plustrace

*

ERROR 位于第 1 行:

ORA-01919: 角色'PLUSTRACE'不存在

SQL> create role plustrace;

角色已创建

SQL>

SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$session to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>

SQL> set echo off

DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public

这样所有用户都将拥有plustrace角色的权限.

SQL> grant plustrace to public ;

授权成功。

然后我们就可以使用AutoTrace的功能了.

SQL> connect eqsp/eqsp已连接。

SQL> set autotrace on

SQL> set timing on

SQL>

关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

SQL> set autotrace traceonly

SQL> select table_name from user_tables;

已选择98行。

已用时间: 00: 00: 00.04

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 NESTED LOOPS (OUTER)

3 2 NESTED LOOPS (OUTER)

4 3 NESTED LOOPS (OUTER)

5 4 NESTED LOOPS (OUTER)

6 5 NESTED LOOPS

7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'

10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)

11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)

14 3 TABLE ACCESS (CLUSTER) OF 'USER$'

15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'

17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)

18 1 TABLE ACCESS (CLUSTER) OF 'TS$'

19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1389 consistent gets

0 physical reads

0 redo size

2528 bytes sent via SQL*Net to client

569 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

在一些特殊情况下，生产环境中top SQL会让DBA捉摸不定。比如一个由复杂分支条件所生成的动态语句；第三方软件或者代码本身对会话环境进行了修改，导致无法正确重现问题语句的查询计划；语句过长导致无法获取正确的全部语句，等等。这时，我们可以考虑设置statspack为更高级别(默认级别为5)，以获取相关语句的详细信息。具体过程如下：

获取级别6的statspack的快照：

SQL> exec statspack.SNAP(i_snap_level => 6);

N分钟后….

SQL> exec statspack.SNAP(i_snap_level => 6);

获取statspack报告

SQL> @?/rdbms/admin/spreport.sql

这里给大家介绍另外一种方法。这种方法可以很方便和AWR报告相结合。对于在生成的AWR报告中被发现的消耗资源较多的SQL语句，我们可以使用AWR提供的awrsqrpt.sql脚本达到获取SQL语句执行计划的目的。

1.下面是以获得SQL ID为“8p23kcbgfqnk4”的SQL语句的执行计划为例，展示一下这个过程。

SQL> @?/rdbms/admin/awrsqrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1922648773 HSW 1 hsw

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: text

Type Specified: text

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

* 1922648773 1 HSW hsw HOUSW-LT

Using 1922648773 for database Id

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: 3

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

hsw HSW 1 14 Sep 2010 12:00 1

2 14 Sep 2010 13:00 1

3 14 Sep 2010 14:00 1

4 14 Sep 2010 15:00 1

5 14 Sep 2010 16:04 1

6 14 Sep 2010 17:00 1

7 14 Sep 2010 22:50 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 6

Begin Snapshot Id specified: 6

Enter value for end_snap: 7

End Snapshot Id specified: 7

Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id: 8p23kcbgfqnk4

SQL ID specified: 8p23kcbgfqnk4

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrsqlrpt_1_6_7.txt. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: 8p23kcbgfqnk4

Using the report name 8p23kcbgfqnk4

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst Num Release RAC Host

------------ ----------- ------------ -------- ----------- --- ------------

HSW 1922648773 hsw 1 10.2.0.3.0 NO HOUSW-LT

Snap Id Snap Time Sessions Curs/Sess

--------- ------------------- -------- ---------

Begin Snap: 6 14-Sep-10 17:00:12 15 2.3

End Snap: 7 14-Sep-10 22:50:29 17 2.3

Elapsed: 350.29 (mins)

DB Time: 0.02 (mins)

SQL Summary DB/Inst: HSW/hsw Snaps: 6-7

Elapsed

SQL Id Time (ms)

------------- ----------

8p23kcbgfqnk4 19,672

select file#, block#, ts# from seg$ where type# = 3

-------------------------------------------------------------

SQL ID: 8p23kcbgfqnk4 DB/Inst: HSW/hsw Snaps: 6-7

-> 1st Capture and Last Capture Snap IDs

refer to Snapshot IDs witin the snapshot range

-> select file#, block#, ts# from seg$ where type# = 3

Plan Hash Total Elapsed 1st Capture Last Capture

# Value Time(ms) Executions Snap ID Snap ID

--- ---------------- ---------------- ------------- ------------- --------------

1 1605285479 19,672 1 7 7

-------------------------------------------------------------

Plan 1(PHV: 1605285479)

-----------------------

Plan Statistics DB/Inst: HSW/hsw Snaps: 6-7

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100