0
enmo_sql_rpt_single
emcs
49次下载
440次浏览
2019-07-24
4.5

脚本内容

--单独使用,生成指定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  
Meta Data
prompt - - - - - - - - - - - - - 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)*/ ' ' 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 OwnerTable NameTable BlocksTable RowsIndex NameIndex BlocksIndex KeysColumn PosColumn NameColumn KeysColumn NullsLast Analyzed
','c" >')|| owner ||'','c" >')|| table_name ||'','c" >')|| blocks ||'','c" >')|| num_rows ||'','c" >')|| index_name ||'','c" >')|| leaf_blocks ||'','c" >')|| distinct_keys ||'','c" >')|| column_position ||'','c" >')|| column_name ||'','c" >')|| num_distinct ||'','c" >')|| num_nulls ||'','c" >')|| last_analyzed ||'

--========================================================================= --| Bind Value --========================================================================= prompt prompt Bind Value

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(''); for i in 1..t_bind_pos.count loop dbms_output.put_line(''); end loop; dbms_output.put_line(''); for i in 1..t_bind_pos.count loop dbms_output.put_line(''); 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(''); 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(''); end loop; dbms_output.put_line(''); j:=j+1; exit when j>= 5; end loop; end if; end; / prompt
Bind Name'|| t_bind_name(i) ||'
Data Type'||t_datatype(i) || '
Snap ID:'||bind.snap_id ||''|| v_value_string ||'


Back to Top
--========================================================================= --| SQL Stats --========================================================================= prompt

prompt SQL Stats

prompt
prompt prompt prompt prompt prompt prompt prompt 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(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); dbms_output.put_line(''); end loop; end; / prompt
Snap id Snap time plan hash_value elapsed time cpu time buffer gets disk reads rows processed io wait Cluster wait app wait Concurrent wait
'||sqlstats.snap_id ||''|| sqlstats.SNAP_TIME ||''|| sqlstats.plan_hash_value ||''|| sqlstats.elapsed_time_delta ||''|| sqlstats.cpu_time_delta ||''|| sqlstats.buffer_gets_delta ||''|| sqlstats.disk_reads_delta ||''|| sqlstats.rows_processed_delta ||''|| sqlstats.iowait_delta ||''|| sqlstats.clwait_delta ||''|| sqlstats.apwait_delta ||''|| sqlstats.ccwait_delta ||'


Back to Top
--========================================================================= --| Game Over ! --========================================================================= spool off; set termout on prompt Report written to enmo_topsql_&&sqlid..html

评论

热门资源
热门脚本
近期活动
全部
暂无活动,敬请期待...
相关课程
全部