Re: enq: TT contention / recyclebin

Another part of the answer comes from the way that the recyclebin (at least in some versions of Oracle - which one are you using) isn't consistent about reporting free and used space. ,Here's a blog note I wrote on the topic a few years ago:
https://jonathanlewis.wordpress.com/2017/05/10/quantum-space/ (it may no longer be 100% accurate).

The answer to question 2 - yes, having a lot of items in the recyclebin could result in a lot of waits for TT enqueue because a session may have to remove some items from the recyclebin to make space, and it may take some time (holding the TT enqueue for that tablespace) to search for the most appropriate thing to drop, and then update the tablespace free space map - and any concurrent attempts to create (or drop, possibly) another object in the same tablespace at the same time will have to wait on the first one. (You could try enabling SQL trace while creating a large object that needs this to happen - or you could search for the SQL that is seeing a lot of elapsed time for very little CPU time and looks like data dictionary SQL) (SQL ordered by Elapsed in the AWR might help)..

Regards
Jonathan Lewis

On Sat, 4 Jun 2022 at 04:19, blair storminson <dmarc-noreply_at_freelists.org> wrote:

> 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
> DB CPU 20.6K 28.1
> library cache lock 222 11.5K 51.98s 15.8
>
> I know TT is about tablespace management, so I started with my usual query
> to show free versus used space...which came back with *negative* usage!
>
> SQL> select d.tablespace_name, lpad(round(tot_size/1024/1024)||'m',10)
> alloc_size, lpad(round(f.tot_free/1024/1024)||'m',10) tot_free,
> round(100-100*tot_free/tot_size) pct_use
> 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;
>
>
> TABLESPACE_NAME ALLOC_SIZE TOT_FREE PCT_USE
> --------------------- ---------------- --------------- ----------
> [chopped]
> TS1 156928m 336482m -114
>
> I thought my script must be buggy so I cut down the components parts, and
> my tablespace is *definitely* 156G in size.
>
> SQL> select tablespace_name, sum(bytes)/1024/1024 ALLOC_MB
> 2 from dba_data_files
> 3 where tablespace_name = 'TS1'
> 4 group by tablespace_name
>
> TABLESPACE_NAME ALLOC_MB
> ------------------------------ -------------
> TS1 156928
>
>
> SQL> select file_id, bytes, autoextensible, maxbytes
> 2 from dba_data_files
> 3 where tablespace_name = 'TS1';
>
> FILE_ID BYTES AUT MAXBYTES
> -------------------- -------------------- --- --------------------
> 19 33285996544 YES 33564917760
> 18 33285996544 YES 31457280000
> 194 32212254720 YES 33564917760
> 195 32212254720 YES 33564917760
> 123 33554432000 NO 0
>
> The tablespace is more or less empty currently....
>
> SQL> select tablespace_name, sum(bytes)/1024/1024 mb
> 2 from dba_segments
> 3 where tablespace_name = 'TS1'
> 4 group by tablespace_name;
>
> TABLESPACE_NAME MB
> ------------------------------ --------------------
> TS1 113.25
>
> *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(*)
> ---------------- ----------
> TS1 233
>
> But!! Looking inside the DDL for DBA_FREE_SPACE, I found all those
> surplus entries came from SYS.RECYCLEBIN$.
>
> 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(*)
> ----------
> 4422097
>
> 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