Re: enq: TT contention / recyclebin

I should check my own blog more thoroughly before writing replies. You may find that it takes a long time to purge the recyclebin if you try to do that as a workaround. If you're not allowed to do that, though, then gathering stats on the x$ktfbue might help. Read the following for the background information:
https://jonathanlewis.wordpress.com/2019/08/08/free-space-3/

Regards
Jonathan Lewis

On Sat, 4 Jun 2022 at 10:45, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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