Claudia left a comment on my last post asking whether or not the recycle bin has affect performance of my entire GL system. From my knowledge we haven't experienced any performance degradation over the past year but I don't have any concrete numbers to back that up since we only keep 30 days of request history. I contacted our functional team and they don't recall of any issues.
If you create an object and there is enough space to fulfill that request then the response time is a few hundredths of a second. However, if Oracle has to purge the recycle bin to free up space then this could affect performance dramatically. Since we monitor frequently for next extent, then chances are there is enough continuous free space for the request. But I can't rule it out.
A quick test:
--Query to show the amount of fragmentation in GLD:
SQL> select bytes/1024/1024, count(*)
2 from dba_free_space
3 where tablespace_name = 'GLD'
4 group by bytes/1024/1024
5 /
BYTES/1024/1024 COUNT(*)
--------------- ----------
.0390625 9
.1953125 16325
.5078125 15742
.078125 20
.2734375 42
405.390625 1
6 rows selected.
Elapsed: 00:05:45.43
-- Create a table with an intial size that will fit in one of the above chunks
SQL> create table t1 (name varchar2(10)) tablespace GLD storage (initial 300M);
Table created.
Elapsed: 00:00:00.74
-- How about a request larger than 405MB?
SQL> create table t1 (name varchar2(10)) tablespace GLD storage (initial 600M);
^Ccreate table t1 (name varchar2(10)) tablespace GLD storage (initial 600M)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
Elapsed: 00:18:44.55
I killed it after 18minutes! So, if your recycle bin contains alot of objects and there isn't a large enough chunk of space to fulfill a request then performance can be severely affected. As I mentioned in one of my previous posts, to clean up GL it took 4 or so hours. Maybe over the weekend when there isn't as much activity on our test system I'll let it run to completion.
For those interested, after 18minutes here is a breakdown of freespace in GLD :
BYTES/1024/1024 COUNT(*)
--------------- ----------
48.984375 1
1.015625 1
2.03125 1
.0390625 9
2.5390625 1
58.671875 1
8.6328125 1
.1953125 14614
51.2890625 1
.5078125 14744
403.75 1
11.6796875 1
56.3671875 1
56.4453125 1
15.7421875 1
.078125 17
.2734375 45
66.875 1
19.8046875 1
1.5234375 1
35.0390625 1
405.390625 1
10 1
23 rows selected.
Elapsed: 00:03:57.03
2 comments:
Although "Recycle bin again", thanks for the extra mile. At least now we know and although the problem is very obvious if you think about it, you have to realize that there is an issue first.
Thank you for the clarification. I had made a guess that I now see was wrong. I guessed that since dba_free_space is a view based on internal data dictionary tables, and since these same tables are referenced many places in database operation, that the performance problem would turn up in other places as well.
Thank you for the follow up.
Post a Comment