One of the tablespaces with fragmentation problems is GLD and GLX, data and index tablespaces for the GL module. As I noticed yesterday, most of the objects in the recycle bin were owned by GL and shortly after I posted the article I wondered if recycle bin was the source of our problems here as well.
Here is a query from dba_free_space which shows you the available free chunks of space and how many of them there are:
SQL> Select bytes/1024/1024, count(*)
2 From dba_free_space
3 Where tablespace_name = 'GLX'
4 group by bytes/1024/1024
5 /
BYTES/1024/1024 COUNT(*)
--------------- ----------
.0390625 25351
55.5859375 1
.1953125 9
.078125 10072
Lots of very small unusable chunks. So what happens to the chunks of free space if I purge the recycle bin?
1 Select bytes/1024/1024, count(*)
2 From dba_free_space
3 Where tablespace_name = 'GLX'
4 group by bytes/1024/1024
5* order by 1 DESC
SQL> /
BYTES/1024/1024 COUNT(*)
--------------- ----------
202.929688 1
138.984375 1
122.578125 1
107.109375 1
104.726563 1
103.554688 1
90.4296875 1
81.484375 1
[SNIP]
After the purge the largest chunk of same size fragments is 6. Quite a significant difference.... So the question is, why isn't Oracle reusing space of objects in the recycle bin? The answer is very simple. We have a custom alarm script which monitors next extent sizes and runs twice a day. If the alarm triggers, a page is sent to the DBA on call and typically they should perform the following steps:
1. Check and see how much free space is left in the tablespace.
2. Check the next_extent size of the object and make sure its not an unreasonably large request. (Some objects still have pct_increase > 0. Grr!)
3. If number 2 is true resize the next_extent, otherwise add more space to the tablespace.
Recycle bin is a "non-instrusive" feature and space will be reclaimed if:*
- A user creates a new table or adds data that causes their quota to be exceeded.
- The tablespace needs to extend its file size to accommodate create/insert operations
*Note: 265253.1
So, when Oracle comes along and needs to grow an object, we have already added a large chunk of space. Logically, Oracle will fulfill its request from this new chunk before reclaiming space from the recycle bin. If i'm mistaken in this conclusion please correct me.
I wonder if we were using OATM with uniform extent sizes would I have noticed this sooner? If I had 25000 extents of a uniform size (which would be the same as the request size) showing in dba_free_space i'm sure I would have investigated why they weren't being reused.
While I was reading up on the recycle bin and flashback drop preparing my last post I didn't recall seeing any gotcha's like this. So heads up, if your using GL (or have an application which using alot of interim tables) then this is a potential gotcha.
3 comments:
Hello Dave,
Thank you for your excellent posts about the recyclebin. I too have an interest in a system that is
making a lot of use of the recyclebin.
What I am wondering is.... You, and metalink, speak about slowness with dba_free_space. But the way you
write it seems that the effect must be much more basic than queries to one view. Dba_free_space
is built on a number of internal tables, some of which must be growing unreasonably large lacking purge
and effecting every query that touches that internal table, not just dba_free_space. My first glance to learn about some
of these internal tables referenced two, sys.ts$ and sys.file$ in looking at i/o hot spots. I wonder whether
oracle has to access these files - overgrown because of the unpurged recyclebin - every time it wants to do
physical i/o? Do you think that you are having a slowdown of your entire GL system, or even your entire
system from this recyclebin problem?
Thanks,
Claudia Zeiler
Hi Claudia, I don't believe it has affect performance in our GL system for the same reason why we have 'fragmentation'.
If I create a table and there is enough space to fulfill the request it takes a few hundredths of a second. Since we add more space (because of our next extent alarm) then this is always the case.
However, if there is a request for an amount larger than the largest chunk of freespace, then oracle will be forced to purge space from the recycle bin. This can take a considerable amount of time.
Thanks for the comments. I'll update the post with stats of a test I just ran.
Hey Dave,
First off, just wanted to say thanks for taking the time to do some write-ups on your experiences as an Oracle APPs DBA!
Secondly, I am new to this gig as well (try less than 1yr!).
I came across your Blog researching about GL interim tables/indexes and the new Oracle10g recyclebin (user,dba_recyclebin) features. Prior to this, the constant drop and recreate would occur wherever the DBA would direct the interim objects to go
(as defined by Setup -> System -> Storage), preferably it’s own “interim” tablespace.
That is the first option in addressing interim tables/indexes, which is to isolate them to their own purgatory tablespace.
The question now becomes how to address the ones that will no longer be needed, since it appears that in order to keep track of their uniqueness, Oracle renames them and treats them all as unique. Whereas I suspect prior to ‘recyclebin’ features, it simply reused the same tables over and over (I could be wrong here – perhaps this too was a cause of much tablespace fragmentation?). But, I must say, that with the Oracle10g recyclebin features, it appears Oracle has put the onus on the DBA as to what to do with them now. It would be great to find out how these ‘interim’ tables were handled prior to Oracle10g RDBMS by an APPs DBA?
At this time, I have investigated and found that at my shop an interim tablespace is not being used (which does and doesn’t surprises me). And although there appears to not be any contention (there is only 489 of these suckers), I still would like to address this issue before it becomes a thorn on my side. So, this is what “I” will be doing to address this dilemma.
1. Create an “interim” tablespace for these GL owned objects. However, I suspected that
they will still show up in the recyclebin, etc. Nonetheless, they will be segregated for
the sole purpose of performance – that’s a good enough reason, in my opinion.
2. Schedule a job, that looks for dropped tables after a given date, and truly drops them.
Of course, the job could be a shell script, a dbms_scheduler job, etc. My reasoning is
because even though they are in their own separate tablespace, the whole “uniqueness”
of them all leads me to believe that the Oracle GL Module will not be doing any
‘flashback’ of the tables to reuse them, but simply as the Oracle documentation states
“recreates” them on demand. I have interim tables owned by GL going back to
09/2008. I believe it is safe to say these interim tables will no longer be used and are
needlessly occupying space.
Please are some Oracle Notes I came across, perhaps you or your users have read these already. However, if anyone has additional idea’s or experiences to share, I would welcome their comments.
Thanks again,
Sergio
Resources:
Oracle General Ledger User’s Guide, page 7-176
Note: 1054934.6
Subject: How are interim tables handled by General Ledger?
Note: 1061025.6
Subject: Clen up the GL_POSTING_INTERIM Tables and other Interim Tables
Note: 1075627.6
Subject: Interim Tables Used by GL Posting
Note: 1017541.102
Subject: GL: Location of Interim Tables May Be the Cause of Poor Performance
Note: 287534.1
Subject: Many Interim Tables and Indexes are filling up the GL and GL_NDX tablespaces
Post a Comment