v$sql_monitor

set trimspool on trim on

set pages 0 linesize 1000

set long 1000000 longchunksize 1000000

-- 查指定时间内
SELECT dbms_sqltune.report_sql_detail(
sql_id => 'xxxx',
report_level => 'ALL',
type=>'ACTIVE',
start_time=>to_date('xxxx','yyyymmddhh24miss'),
duration=>'xxxx'
)
FROM dual;



生成漂亮的html报告

mkdir /home/oracle/dba; cd dba
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

生成SQL语句的ACTIVE报告使用如下的方式即可：

SELECT dbms_sqltune.report_sql_monitor(
sql_id => '${tmp_sql_id}',
report_level => 'ALL',
type=>'ACTIVE',
base_path =>'/home/oracle/dba') 
FROM dual;