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