Re: enq: TT contention / recyclebin
Date: Sun, 5 Jun 2022 02:53:26 +0000 (UTC)
Message-ID: <1130258104.5762335.1654397606054_at_mail.yahoo.com>
------------------------------ -------------------------------- --------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.)
RegardsJonathan 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.3DB 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 33564917760195 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-lReceived on Sun Jun 05 2022 - 04:53:26 CEST