In my last post I have discussed issues seen in a Demantra system after upgrade to 18.104.22.168. We have had many issues, see earlier post This is about another issue.
Not the major contributor to our problems but I still think it is interesting to know.
In our first AWR report analyzing the issue we had the following scenario
98,6 of the time is spent on CPU
We also noticed that a majority of the DB CPU was spent on parsing and that is addressed in my previous post. In this post I will focus on another issue.
Looking at SQL ordered by CPU Time for our period we can see that we have several internal sqls on the top list. select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn, is the number one sql. It is executed ~83000 times consuming ~16% of the CPU time. We can also see two delete statements from recycle$bin. I found this odd since I know we haven’t executed any purge of recycle$bin during our batch run. I did suspect it to be some kind of background process doing this.
Looking at SQL ordered by gets it is reading a hefty 25 billion buffer gets.
I did check the recycle$bin for deleted objects and I found that we had a lot of objects in the recycle$bin
SQL> select count(1) from dba_recyclebin; COUNT(1) ---------- 810896
I also checked the oldest entry and it was
SQL> select min(CREATETIME) from dba_recyclebin; MIN(CREATETIME) ------------------- 2016-03-09:13:40:58
This is about the time when we moved to 22.214.171.124. I asked our developers about this and it seems that the batch job creates and drops “temp” tables used during the batch process. That is the reason for the huge amount of objects in the recycle$bin.
I asked Oracle Support why this sql is executed so often and consuming so much cpu.
The answer I got was that it is related to space pressure on the tablespaces.
The batch we run is heavy so it make sense. When tablespace space pressure occurs Oracle starts a job in the background purging objects. It starts with indexes and then tables. So far I haven’t found any detailed information explaining how it works, nor in the documentation or in Oracle Support. I do see a few bugs mentioning that the purge of recycle$bin does not occur during space pressure, so it seems it is expected that it should happen. Those I found was for earlier releases.
We have purged the whole recycle$bin now and we do a purge prior to starting the batch job. After that we do not see any of these sqls executing in the database during our batch.
If you find this and other posts interesting feel free to follow me on twitter !
There is a link on the blog start page.