根据AWR报告中sql_id查看sql执行的时间段和对应的快照时间段

SQL> create table t01 as select * from dba_objects;

Table created.
手动创建快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

PL/SQL procedure successfully completed.
生成AWR报告
SQL> @awrrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  356383658 TID002              1 tid002

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

Type Specified:  html

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

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  356383658         1 TID002       tid002       OL354S01
* 356383658         1 TID002       tid002       OL354V01

Using  356383658 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: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
tid002       TID002             870 04 9?  2012 00:00      1
                                871 04 9?  2012 01:00      1
                                872 04 9?  2012 02:00      1
                                873 04 9?  2012 03:00      1
                                874 04 9?  2012 04:00      1
                                875 04 9?  2012 05:00      1
                                876 04 9?  2012 06:00      1
                                877 04 9?  2012 07:00      1
                                878 04 9?  2012 08:00      1
                                879 04 9?  2012 09:00      1
                                880 04 9?  2012 09:01      1
                                881 04 9?  2012 10:00      1
                                882 04 9?  2012 11:00      1
                                883 04 9?  2012 12:01      1
                                884 04 9?  2012 13:00      1
                                885 04 9?  2012 13:31      1
                                886 04 9?  2012 13:33      1
                                887 04 9?  2012 14:21      1

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 884
Begin Snapshot Id specified: 884

Enter value for end_snap: 887
End   Snapshot Id specified: 887

根据AWR报告可以看出create table t01 as select * from dba_objects 语句对应的sql_id 是
62971k306jfzz     create table t01 as select * from dba_objects 62971k306jfzz

SQL>  select sql_id from v$sql where sql_text='create table t01 as select * from dba_objects';

SQL_ID
-------------
62971k306jfzz

SQL> select sql_id,first_load_time,last_load_time from v$sql where sql_id='62971k306jfzz';

SQL_ID
-------------
FIRST_LOAD_TIME
----------------------------------------------------------------------------
LAST_LOAD_TIME
----------------------------------------------------------------------------
62971k306jfzz
2012-09-04/14:20:47
2012-09-04/14:20:47

SQL> column BEGIN_INTERVAL_TIME format a30
SQL> select sql.sql_id,sql.snap_id,snap.begin_interval_time,snap.end_interval_time from dba_hist_sqlstat sql,dba_hist_snapshot snap where sql_id='62971k306jfzz' and sql.snap_id=snap.snap_id;

SQL_ID           SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
------------- ---------- ------------------------------ ---------------------------------------------------------------------------
62971k306jfzz        887 12-09-04 13:33:17.904          12-09-04 14:21:03.578

与sql相关的历史记录视图
select t.view_name from dba_views t where t.view_name like 'DBA_HIST_SQL%';

DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_PLAN
DBA_HIST_SQL_BIND_METADATA
DBA_HIST_SQLBIND
DBA_HIST_SQL_WORKAREA_HSTGRM

 

参考文档：

V$SQL
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm

dba_hist_sqlstat
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3183.htm

How to get execution statistics and history for a SQL [ID 1371778.1]

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

转载于:http://blog.itpub.net/24756186/viewspace-742642/