Is your slowness reading from or writing?
IF the slowness is reading via full table scan, and you have purged a lot,
it is possible that you are experiencing the "empty front" problem.
As Tim wrote "fragmentation" is a myth as a performance problem.
It is possible after purging to have very many completely empty blocks at
the beginning of a table and Oracle has no low water mark.
If you query up your non-zero session stats, query from sysauth$ where
rownum < 2, and query up your non-zero session stats again, then if your
consistent gets is significantly more than 1 you possibly are experiencing
overhead due to "empty front." The undo session stats may also be of
interest. I don't know whether it is any longer possible to have to apply
undo to determine that a block has zero rows, and if your query is into your
pga rather than into the sga, those blocks will not get permanently cleaned.
Since completely empty blocks are not found by index lookups, they tend to
be read only by full table scans unless they hit the insert point again.
Since full table scans often go to pga, they may never get cleaned.
This is extremely easy to test. Probably your answer will be "that's not my
problem" but it is so easy to test I would just do it. I would probably do
it even before running the trace that Tim mentions (but the trace will also
show a pant load more of consistent gets, so you may just want to follow
Tim's recommendation anyway.) If you have a problem with getting access to
trace files (as consultants often do), then checking for empty front avoids
that problem.
The existence of the "empty front" problem and the deletion patterns of Ebiz
and SAP being eliminated by table unloads/reloads tended to cause the myth
Tim references to persist. It's not the honeycomb unused blocks that makes
much difference. But if you scan a lot of empty blocks to find a few rows in
a row limited query (especially like a single row), then as a percentage of
the overhead the empty front blocks may matter.
Oracle has no method (unless they've added one since I stopped watching
carefully) to raise the position of the "first block" and no method to sort
out the existing freelist (or bitmap of free storage) so that "low" blocks
completely empty are used before other blocks.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Wednesday, April 28, 2021 11:25 AM
To: willyk@xxxxxxxxxxx; 'ORACLE-L'
Subject: Re: fragmented sysauth$ table
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
