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