--单独使用，生成指定SQL sql rpt, 包含表结构、绑定变量、每个snap的执行情况等
 set echo off veri off feedback on heading off pages 8000 lines 5000 trimspo on trimout on  termout on
 undefine sqlid;
var dbid number
var inst_num number
var bid number
var eid number
var sqlid varchar2(32)

begin
	:sqlid:='&&sqlid';
	select dbid,instance_number,bid,eld into :dbid,:inst_num,:bid,:eid
from (
select sq.dbid,sq.instance_number,max(sq.snap_id) eid,min(sq.snap_id) bid
from dba_hist_sqlstat sq,dba_hist_snapshot sn
where sql_id = :sqlid
and sn.begin_interval_time > (select max(startup_time) from dba_hist_database_instance)
group by sql_id,sq.dbid,sq.instance_number order by instance_number
) where rownum=1;
end;
/
 spool enmo_topsql_&&sqlid..html
 select output from table(dbms_workload_repository.awr_sql_report_html( :dbid, :inst_num, :bid, :eid, :sqlid));
 --|   Metadata Statistics 
 prompt <a class='awr' name="contents"></a> <hr width="100%"> <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Meta Data</b></font><hr align="left" width="460"> 
 prompt <table width='80%' border="1"><tr> -
	<th class='awrbg'>Table Owner</th> -
	<th class='awrbg'>Table Name</th> - 
	<th class='awrbg'>Table Blocks</th> -
	<th class='awrbg'>Table Rows</th> - 
	<th class='awrbg'>Index Name</th> - 
	<th class='awrbg'>Index Blocks</th> -
	<th class='awrbg'>Index Keys</th> -
	<th class='awrbg'>Column Pos</th> -
	<th class='awrbg'>Column Name</th> -
	<th class='awrbg'>Column Keys</th> -
	<th class='awrbg'>Column Nulls</th> -
	<th class='awrbg'>Last Analyzed</th> -
	</tr>
       with table_list as
(select  /*+ materialize */ OBJECT_OWNER table_owner,OBJECT_NAME table_name
 from dba_hist_sql_plan
where  dbid = :dbid
and sql_id =:sqlid and object_type like 'TABLE%'
union
select  TABLE_OWNER,TABLE_NAME table_name
 from dba_hist_sql_plan sql,dba_indexes i
where  dbid = :dbid
and sql_id =:sqlid and object_type like 'INDEX%'
and i.owner = sql.object_owner and i.index_name = sql.object_name and object_type like 'INDEX%'
),t as (select /*+ materialize */ owner,t.table_name,num_rows,last_analyzed ,blocks
from dba_tables t, table_list p
where t.owner = p.table_owner  and t.table_name = p.table_name
) ,
ic as (select /*+ materialize */ ic.table_owner,ic.table_name,column_name,index_owner,index_name,column_position  
from dba_ind_columns ic, table_list p
where ic.table_owner = p.table_owner  and ic.table_name = p.table_name
) ,
tc as (select  /*+ materialize */table_owner,tc.table_name,column_name,num_distinct,num_nulls
from dba_tab_columns tc, table_list p
where tc.owner = p.table_owner  and tc.table_name = p.table_name
union all
select table_owner,table_name,column_name,null,null
from ic
where column_name like 'SYS_NC%'
) ,
i as (select  /*+ materialize */i.table_owner,i.table_name,index_name,owner,distinct_keys,leaf_blocks
from dba_indexes i, table_list p
where i.table_owner = p.table_owner  and i.table_name = p.table_name
)
select /*+ordered use_hash(t tc)*/
                  ' <tr> <td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
owner            ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
table_name       ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
blocks           ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
num_rows         ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
index_name       ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
leaf_blocks      ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
distinct_keys    ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
column_position  ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
column_name      ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
num_distinct     ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
num_nulls       ||'</td><td class="awr'||decode(mod(rownum,2),0,'nc">','c" >')||
last_analyzed     ||'</td></tr>'
from
(
select  /*+ordered use_hash(t tc)*/ 
t.owner,
t.table_name ,
t.num_rows,
t.blocks,
i.index_name ,
i.distinct_keys ,
i.leaf_blocks,
ic.column_position,
tc.column_name,
tc.num_distinct ,
tc.num_nulls ,
t.last_analyzed
 from   t ,  tc ,  ic,  i
 where tc.table_owner = t.owner and tc.table_name = t.table_name
 and ic.table_owner(+) = tc.table_owner and ic.table_name(+) = tc.table_name and ic.column_name(+)=tc.column_name
 and i.table_owner(+) = ic.table_owner and i.table_name(+) = ic.table_name and i.index_name(+)=ic.index_name
 order by t.owner,t.table_name,i.index_name,column_position,num_distinct desc
)
/
prompt </table></br>
 --=========================================================================
 --|   Bind Value
 --=========================================================================
prompt <table width='80%' border="1">
 prompt <a class='awr' name="bind_value"></a><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Bind Value</b></font><hr align="left" width="460"> </br>
set serverout on size 50000
set serverout on size unlimited
declare
style varchar2(10);
v_value_string varchar2(128);
v_date TIMESTAMP;
t_bind_pos dbms_sql.number_table;
t_bind_name dbms_sql.varchar2_table;
t_datatype dbms_sql.varchar2_table;
v_VALUE_ANYDATA sys.anydata;
j number;
begin
select distinct position,name,datatype_string bulk collect into t_bind_pos,t_bind_name,t_datatype
		from dba_hist_sql_bind_metadata
		where sql_id=:sqlid and dbid = :dbid
		order by position;
if(t_bind_pos.count>0)then
	dbms_output.put_line('<th class="awrbg">Bind Name</th>');
	for i in 1..t_bind_pos.count loop
	  dbms_output.put_line('<th class="awrbg">'|| t_bind_name(i) ||'</th>');
	end loop;
	dbms_output.put_line('<tr><th class="awrnobg">Data Type</th>');
	for i in 1..t_bind_pos.count loop
	  dbms_output.put_line('<th class="awrnobg">'||t_datatype(i) || '</th>');
	end loop;
	j:=0;
	for bind in (select bind_data,snap_id from dba_hist_sqlstat
			where sql_id=:sqlid
			and dbid =:dbid  and instance_number=:inst_num
			and snap_id >= :bid and snap_id<=:eid
			order by snap_id desc) loop
  if(mod(j,2))=1 then
    style:='awrnc';
  else
    style:='awrc';
  end if;
  dbms_output.put_line('<tr> <td align="center"  class="' || style||'">Snap ID:'||bind.snap_id ||'</td>');
	for i in 1..t_bind_pos.count loop
    if(t_datatype(i) like 'TIMES%') then
	    v_VALUE_ANYDATA := dbms_sqltune.extract_bind(bind.bind_data,t_bind_pos(i)).VALUE_ANYDATA ;
		if(v_VALUE_ANYDATA is not null) then
			v_value_string := to_char(v_VALUE_ANYDATA.getTimestamp(v_date));
			v_value_string := to_char(v_date,'YYYYMMDD HH24:MI:SS.ff6');
		else
			v_value_string:='NULL';
		end if;
	else
	 v_value_string:= dbms_sqltune.extract_bind(bind.bind_data,t_bind_pos(i) ).value_string;
	end if;
    dbms_output.put_line('<td align="center" class="' ||style ||'">'|| v_value_string  ||'</td>');
  end loop;
  dbms_output.put_line('</tr>');
  j:=j+1;
  exit when j>= 5;
end loop;
end if;
end;
/
 prompt </table> <p> <br /><a class='awr' HREF="#top">Back to Top</a> <br />
  --=========================================================================
 --|   SQL Stats
 --=========================================================================
prompt <table width='80%' border="1">
 prompt <a class='awr' name="bind_value"></a><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SQL Stats</b></font><hr align="left" width="460"> </br>
prompt <table width='80%' border="1">
prompt <th class="awrbg"> Snap id</th>  <th class="awrbg"> Snap time</th>
prompt  <th class="awrbg"> plan hash_value</th> <th class="awrbg"> elapsed time</th> 
prompt  <th class="awrbg"> cpu time</th> <th class="awrbg"> buffer gets</th>
prompt  <th class="awrbg"> disk reads</th> <th class="awrbg"> rows processed</th>
prompt  <th class="awrbg"> io wait</th>  <th class="awrbg"> Cluster wait</th> 
prompt  <th class="awrbg"> app wait</th>  <th class="awrbg"> Concurrent wait</th> 
 prompt <a class='awr' name="SQL Stats by SnapID and PlanHashValue"></a>
set serverout on size 50000
set serverout on size unlimited
declare 
style varchar2(10);
j number;
begin
j:=0;
for sqlstats in (select st.snap_id,plan_hash_value,executions_delta,elapsed_time_delta,cpu_time_delta,buffer_gets_delta,
						disk_reads_delta,rows_processed_delta,iowait_delta,clwait_delta,apwait_delta,ccwait_delta,to_char(begin_interval_time,'MMDD HH24MI') as SNAP_TIME
 from dba_hist_sqlstat st,dba_hist_snapshot sn
        where sql_id=:sqlid 
        and st.dbid =:dbid  and st.instance_number=:inst_num
		and st.snap_id >= :bid and st.snap_id<=:eid 
		and st.snap_id = sn.snap_id and st.instance_number = sn.instance_number and st.dbid = sn.dbid
        order by sn.snap_id desc) loop
  if(mod(j,2))=1 then
    style:='awrnc';
  else
    style:='awrc';
  end if;
  j:=j+1;
  dbms_output.put_line('<tr> <td align="center"  class="' || style||'">'||sqlstats.snap_id ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.SNAP_TIME  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.plan_hash_value  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.elapsed_time_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.cpu_time_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.buffer_gets_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.disk_reads_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.rows_processed_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.iowait_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.clwait_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.apwait_delta  ||'</td>');
  dbms_output.put_line('<td align="center" class="' ||style ||'">'|| sqlstats.ccwait_delta  ||'</td>');
  dbms_output.put_line('</tr>');
end loop;
end;
/
 prompt </table> <p> <br /><a class='awr' HREF="#top">Back to Top</a> <br />
 --=========================================================================
 --|   Game Over !
 --=========================================================================
 spool off;
set termout on
 prompt Report written to  enmo_topsql_&&sqlid..html