Re: enq: TT contention / recyclebin

If we assume for the moment that this isn't a bug, the difference in number could simply be because dba_recyclebin is counting segments while dba_free_space will be showing one entry for each extent of the segments in dba_recyclebin:

SQL> select ts_name, object_name, space from dba_recyclebin;

TS_NAME                        OBJECT_NAME                         SPACE
------------------------------ -------------------------------- --------
TEST_8K_ASSM                   BIN$4JzBt3/7Ls7gUwEAAH96vQ==$0      1,280
                                                                --------
sum                                                                1,280

1 row selected.

SQL> select blocks, count(*), sum(blocks) space from dba_free_space where tablespace_name = 'TEST_8K_ASSM' and blocks < 1025 group by blocks;

    BLOCKS COUNT(*) SPACE
---------- ---------- --------

         8         16      128
       128          9    1,152
                      --------
sum                      1,280

2 rows selected.

(The SQL happens to work for me because it's a new tablespace and I know exactly what's in it - it's not generally useful, just making a point.)

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