Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘Script to know which SQL’s are generating redo’

Script to know which SQL’s are generating redo

Posted by Pavan DBA on February 7, 2012


SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed 
FROM ( 
      SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when, 
             dbms_lob.substr(sql_text,4000,1) sql, 
             dhss.instance_number inst_id, 
             dhss.sql_id, 
             sum(executions_delta) exec_delta, 
             sum(rows_processed_delta) rows_proc_delta 
        FROM dba_hist_sqlstat dhss, 
             dba_hist_snapshot dhs, 
             dba_hist_sqltext dhst 
        WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’ 
          AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
          AND dhss.snap_id=dhs.snap_id 
          AND dhss.instance_Number=dhs.instance_number 
          AND dhss.sql_id = dhst.sql_id  
          AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                      AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
        GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′), 
            dbms_lob.substr(sql_text,4000,1), 
              dhss.instance_number, 
             dhss.sql_id 

group by when, sql;

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: