Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jack.

Asked: February 06, 2017 - 8:34 pm UTC

Last updated: February 10, 2017 - 3:38 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,
We have an application performing many inserts and updates from many machines. At peak time, we may have the application running on 300 machines performing inserts and updates. Once in a while, we saw some active sessions blocking other sessions. Sometimes, these blocking sessions were running a long scan on sys.obj$ or sys_recyclebin for reasons we do not know. These scans will not finish, at least for a long time. Our trick to resolve this issue is to terminate all sessions, flush buffer cache and shared pool then everything will work fine again. Do you know why inserts or updates caused long scan on system views such as sys.obj$ or sys_recyclebin and why session scanning system views would block other sessions and why flush buffer cache and shared pool would resolve the issue?
We are using a three node RAC with 11G.
Thanks.
Jack

and Connor said...

Are your datafiles getting full ? As datafiles get filled, we'll look at (silently) clearing out stuff from the recyclebin in order to accommodate segment growth.

If this is the case, you might want to consider setting recyclebin to off (but obviously be aware of what this means), or at least ensuring that the bin is purged.

When the blocking occurs, check out v$active_session_history to see what they are blocked on.

Hope this helps.

Rating

  (1 rating)

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

Comments

Concerns

A reader, February 08, 2017 - 6:32 am UTC

What is relationship btw dml and recyclebin? ?
The later is growing because of truncates/drops/...

Connor McDonald
February 10, 2017 - 3:38 pm UTC

Yes but the objects in the recyclebin are still there - consuming space.

When your "real* objects grow (with DML, ie, inserts), and they need to get new extents...then the database will clean out recyclebin stuff to make room.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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