enq: TT contention / recyclebin

Greetings all,
I'm trying to see if the recyclebin is linked to a bad wait performance issue (enq: TT contention) we're having. Here's a snippet from a 1hr AWR report (I dont think my company will let me post the whole report - waiting on a email from boss)

Event                 Waits  TotalWaitTime(sec)   AvgWait   %DBtime

==================== ======  =================    =======   =======
enq: TT - contention    350                36K    102.99s      49.3

  2  from
  3  ( select tablespace_name, sum(tot_free) tot_free
  4    from
  5    ( select tablespace_name, sum(bytes) tot_free
  6      from dba_free_space
  7      group by tablespace_name
  8    )
  9    group by tablespace_name
 10  ) f,
 11  ( select tablespace_name, sum(bytes) tot_size
 12    from dba_data_files
 13    group by tablespace_name
 14  ) d
 15  where f.tablespace_name(+) = d.tablespace_name
 16  order by 1;

--------------------- ---------------- --------------- ----------

  2  from dba_data_files
  3  where tablespace_name = 'TS1'
  4  group by tablespace_name

------------------------------ -------------

-------------------- -------------------- --- --------------------
                  19          33285996544 YES          33564917760
                  18          33285996544 YES          31457280000
                 194          32212254720 YES          33564917760

  2  from dba_segments
  3  where tablespace_name = 'TS1'
  4  group by tablespace_name;
  

------------------------------ --------------------

*Here* is where it all starts to get really strange.  When I look at *free* space, I'm getting HUNDREDS of gigabytes more than the size of the datafiles, hence the negative usage figures.                                                     SQL> select file_id, count(*), sum(bytes)/1024/1024 mb

  2  from dba_free_space
  3  where tablespace_name = 'TS1'
  4  group by file_id
  5  order by 1;

   FILE_ID   COUNT(*)         MB ---------- ---------- ----------

        18     748982      78530
        19    1035729      96439
       123      45426      34824
       194     532200      63956
       195     559498      65661

Compare that to below!

SQL> select file#, bytes/1024/1024 mb from v$datafile   2  where file# in (18,19,123,194,195)   3  order by 1;

     FILE#         MB
---------- ----------

        18      31744  
        19      31744  
       123      32000  
       194      30720  
       195      30720  

In trying to see why the free space values are silly, I found thousands of entries in DBA_FREE_SPACE which have a *multiple* rows for a file/block_id position. That explains the over-counting, but how can this happen?

SQL> select file_id, block_id, count(*)

  2  from dba_free_space
  3  where tablespace_name = 'TS1'
  4  group by file_id, block_id
  5  having count(*) > 1
  6  order by 1,2;

3131 rows selected
My theory is leaning to one of our apps which is a port from another platform so it is all the time creating tables, dropping them, even creating/dropping schemas on the fly. Our recyclebin is on by default (we are looking to changing this for this app) On first look, the recycle bin seems small ....

SQL> select TS_NAME, count(*)
  2  from dba_recyclebin
  3  group by ts_name;

TS_NAME             COUNT(*)

---------------- ----------

SQL> select

  2           ts.name, fi.file#, u.ktfbuebno,
  3           u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
  4    from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
  5    where ts.ts# = rb.ts#
  6      and rb.ts# = fi.ts#
  7      and u.ktfbuefno = fi.relfile#
  8      and u.ktfbuesegtsn = rb.ts#
  9      and u.ktfbuesegfno = rb.file#
 10      and u.ktfbuesegbno = rb.block#
 11      and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
 12      and bitand(ts.flags, 4503599627370496) <> 4503599627370496
 13  and ts.name = 'TS1';

   COUNT(*)

Still reading?

Thus my questions:

Q1) How do I have entries in SYS.RECYCLEBIN$ to never make it to DBA_RECYCLEBIN? Q2) Can this (what looks to me like a dictionary corruption) combined with the terrible way our app works explain the enq:TT contention? The two places I see it are: i) create/drop commands on table or schemma, and ii) when EM is doing tablespace metrics collection

--
http://www.freelists.org/webpage/oracle-l