Skip to Main Content
  • Questions
  • DB stuck at check point not complete.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vijaya.

Asked: March 28, 2018 - 2:21 pm UTC

Last updated: April 02, 2018 - 7:53 am UTC

Version: 11G enterprise 11.2.0.4.0 -- 64 bit

Viewed 1000+ times

You Asked

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.


and Connor said...

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.


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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.