RE: fragmented sysauth$ table

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, April 28, 2021 11:25 AM To: willyk_at_kbi-gmbh.de; '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      

--
http://www.freelists.org/webpage/oracle-l