Two important questions...
1. What makes you assume that "table fragmentation" is a problem?
2. Can you explain how 40% space unused within a table may impact
performance?
Answers: there is no such issue as "table fragmentation" in Oracle
database -- it is a myth carried over from other technologies -- and
having 40% of space allocated to a table going unused almost certainly
has no impact whatsoever on performance.
Instead, please try to understand the execution plan for the SQL
statements against SYSAUTH$, and then try to determine if that execution
plan can be improved? If the best execution is a full table scan on the
table, then it is possible that coalescing or "compacting" this table
may help somewhat, at least temporarily, but if the optimal execution
plan on the table uses indexes, then coalescing or "compacting" will not
and cannot improve anything about performance. The access method is
important to know, and as of now, we don't know what it is. Let's find out?
Instead of assuming that a non-existent problem is the cause of your
performance issues and then acting to corrupt your data dictionary by
attempting to "fix" it, please instead perform a SQL trace on a
problematic SET ROLE operation, hopefully in SQL*Plus? There is a lot
of online documentation, belonging to Oracle and excellent blog sites
such as HERE
<https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof>,
on doing this. If you need help capturing SQL trace, understanding
traces, or interpreting them, this list is a great resource too.
On 4/28/2021 1:24 AM, Willy Klotz wrote:
Hi all,
we are on Oracle 19.8. customer has several large databases, with several tens of thousands users and a number of roles.
We are experiencing slow „set role“ (15 seconds and up), and we see some “select … from sysauth$” in top10 SQL of the AWR Reports . I can see that sys.sysauth$ is fragmented, 40% wasted space in there.
Is there a way to compact this table? Alter table move or similar do not work on it.
Thank you and best regards
Willy
