Our DB stuck and check poin not complete. As per the logs DBA says.. ELECT for update running on the db causing the high number of block changes and caused the issue
After this issue we are unable to generate ASH or AWR for that time.
How to identify which query/resource causing this issue.
How to identify which table is caused the issue.
If the transaction is still uncommitted, you can run:
select * from v$transaction order by used_urec desc;
to see which sessions have got a lot of outstanding changes.
If the transaction has committed some time ago, then places to look:
select * from v$sqlstats
where lower(sql_text) like '%for update%'
order by rows_processed desc
select * from v$segment_statistics
where statistic_name = 'db block changes'
order by value desc
This gives you an insight into the segments and SQL statements to be the most likely culprits.