Pavan DBA's Blog

The DBA Knowledge Store

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: