Skip to Main Content
  • Questions
  • Search for top 10 queries generating huge redo

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Kundan.

Asked: August 21, 2017 - 11:33 am UTC

Last updated: June 07, 2022 - 3:10 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Dear Experts,

Please can you help in knowing how to find for top 10 queries/sessions generating huge redo.

I tried with the below queries to check redo generated per day, but not able to find top 10 such sessions which generates so.

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024/1024/1024)) "REDO PER DAY (TB)"
from Gv$archived_log
group by trunc(completion_time)
order by 1;


And then by sorting object wise and their respective SQL_ID and respective queries, but not able to figure out the huge redo generating queries or sessions. Please help ....

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
sum(db_block_changes_delta) as maxchages
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by maxchages asc;


SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text,4000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%MGMT_POLICY_ASSOC_CFG%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
AND dhss.sql_id = dhst.sql_id;



Regards,
Kundan

and Connor said...

I'd start simple - find those SQL's that processing lots of rows, but are not SELECT statements, eg

SQL> select executions, rows_processed, sql_text
  2  from v$sql
  3  where rows_processed > 10
  4  and upper(sql_text) not like 'SELECT%'
  5  and parsing_user_id != 0               -- to ignore SYS
  6  and command_type != 47                 -- to ignore PL/SQL
  7  order by rows_processed desc;

EXECUTIONS ROWS_PROCESSED SQL_TEXT
---------- -------------- ----------------------------------------------------------------
         1            100 insert into t select rownum ,rpad(rownum,2000,'x') from dual con
                          nect by level <= 100

        11             77 DELETE FROM ATE_QUESTIONS_TOP20
        11             77 INSERT INTO ATE_QUESTIONS_TOP20 ( QUESTION_ID, SUBJECT, THECOUNT
                           ) SELECT Y.* FROM ( SELECT Q.ID, Q.SUBJECT, X.THECOUNT FROM ATE
                          _SUBMITTED_QUESTIONS Q, ( SELECT QUESTION_ID, COUNT(*) THECOUNT
                          FROM ATE_QUESTION_VIEWS GROUP BY QUESTION_ID ) X WHERE Q.ID = X.
                          QUESTION_ID AND Q.STATUS = 5 ORDER BY X.THECOUNT DESC ) Y WHERE
                          ROWNUM <= 20
...
...



Obviously with LOB's and the like, there is not a strict relationship between "rows" and "redo", but the above query normally is a good starting point, and is obviously can be trivially transposed to work against dba_hist_sqlstat if you want to do it on a time range basis.

Once you have identified a SQL_ID (or a set of them) that you want to explore further, you can go to more details, even to the extent of tracing the entire database for *just* those statements, eg

ALTER SYSTEM SET EVENTS sql_trace [sql: sql_id=bqfkskp93rqzc] bind=true, wait=true';

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kundan Sinha, August 22, 2017 - 9:11 am UTC

Very informative.

How can I further inquire about our interaction on this. As I do not see a reply button anywhere :)


Regards,
Kundan
Connor McDonald
August 23, 2017 - 2:28 am UTC

Once a question is answered, we continue via Review/Followup

What about SELECT FOR UPDATE statement, it won't get listed.

Tapas, August 06, 2019 - 2:25 am UTC

Hello,
The SQL you have mentioned is great, but it won't list any statement which is in the form "SELECT FOR UPLDATE".

In our database one such statement which was generating huge redo is not part of the output.

Thank You
Tapas Dash
Connor McDonald
August 08, 2019 - 3:29 am UTC

Valid point.

Valid Point? but ...?

Jed, April 24, 2020 - 10:05 pm UTC

On "Valid Point" on SELECT FOR UPDATE.
The select for update wouldn't be causing the redo though, it is just retrieving the rows and preventing them from being changed, the actual culprit would be the resulting update statements.
Wouldn't that be correct, and if so, is there an easy way to link them together other than going through code or correlating by session ID?
Connor McDonald
April 28, 2020 - 3:11 am UTC

SELECT FOR UPDATE will cause *some* redo because we change the block (but marking the row as locked).

SQL> create table t as select * from dba_objects;

Table created.

SQL> @mystat
Enter value for statname: redo size

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      13261508

SQL> variable rc refcursor
SQL> exec open :rc for select * from t for update;

PL/SQL procedure successfully completed.

SQL> @mystat
Enter value for statname: redo size

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      31041164


Suggestion for Query-Improvement

Ulrich, June 02, 2022 - 6:27 am UTC

This question and answer are some years old but they just helped me to find some poorly programmed statements that generated a lot of unneccessary redo in our database but wouldn't it be better to exclude SELECTs by
command_type not in (3,47)  

instead of
upper(sql_text) not like 'SELECT%'

in our database for example I find many SELECTs that start with
with ... as (select ...

which wouldn't be found by your where-clause.

Connor McDonald
June 07, 2022 - 3:10 am UTC

Valid point.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database