with aa as
(SELECT IID,
USERNAME,
to_char(BEGIN_TIME,'mm/dd hh24:mi') begin_time,
SQL_ID,
decode(COMMAND_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL_TYPE",
executions "EXEC_NUM",
rows_processed "Change_NUM"
FROM (SELECT s.INSTANCE_NUMBER IID,
PARSING_SCHEMA_NAME USERNAME,COMMAND_TYPE,
cast(BEGIN_INTERVAL_TIME as date) BEGIN_TIME,
s.SQL_ID,
executions_DELTA executions,
rows_processed_DELTA rows_processed,
(IOWAIT_DELTA) /
1000000 io_time,
100*ratio_to_report(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) RATIO,
sum(rows_processed_DELTA) over(partition by s.INSTANCE_NUMBER, BEGIN_INTERVAL_TIME) totetime,
elapsed_time_DELTA / 1000000 ETIME,
CPU_TIME_DELTA / 1000000 CPU_TIME,
(CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA+PLSEXEC_TIME_DELTA+JAVEXEC_TIME_DELTA)/1000000 OTIME,
row_number() over(partition by s.INSTANCE_NUMBER,BEGIN_INTERVAL_TIME order by rows_processed_DELTA desc) TOP_D
FROM dba_hist_sqlstat s, dba_hist_snapshot sn,dba_hist_sqltext s2
where s.snap_id = sn.snap_id
and s.INSTANCE_NUMBER = sn.INSTANCE_NUMBER
and rows_processed_DELTA is not null
and s.sql_id = s2.sql_id and COMMAND_TYPE in (2,6,7,189)
and sn.BEGIN_INTERVAL_TIME > sysdate - nvl(180,1)/1440 and PARSING_SCHEMA_NAME<>'SYS')
WHERE TOP_D <= nvl(20,1)
)
select aa.*,s.sql_fulltext "FULL_SQL" from aa left join v$sql s on aa.sql_id=s.sql_id ORDER BY IID, BEGIN_TIME desc,"Change_NUM" desc
Oracle查看导致归档日志变大的语句
原创dber_ablewang 博主文章分类:Oracle ©著作权
©著作权归作者所有:来自51CTO博客作者dber_ablewang的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
查看oracle归档日志路径
查看oracle归档日志路径
查看oracle归档日志路径 -
Oracle 开启归档日志以及关闭归档日志
归档模式(archivelog),非归档模式(noarchivelog)
归档模式(archivelog)非归档 -
Oracle归档日志(二)
1、归档模式下配置05:32:58 SQL> show parameter archiveNAME TYPE VAL
Oracle Oracle归档日志 Oracle日志 -
Oracle归档日志删除oracle archive disk delete report