AW: fragmented sysauth$ table

Hi all, and thank you for your answers.  

You are correct that it is not the best idea to tamper with the DD first. Maybe I should explain the actual cause of the problem.  

Enclosed a short tkprof output. As you can see, this test is doing one single “set role” statement, which in turn does a “select .. from sysauth$”, which runs for 26 seconds. The database in this test is idle otherwise, we have plenty of memory and cpu available.  

We encountered this problem first when switching to Oracle 12.2. There are several production databases which I am monitoring, and “set role” and “select from sysauth” started to show up in AWR Top-SQL. I opened an SR with Oracle, which now runs for 18 months. We tried several patches and db-parameters, nothing helped. We were moving to 19.6, which made the situation worse. Currently we are on Oracle 19.8, and I think it is going more worse.  

As Oracle-Development does not have additional ideas currently, I thought we can give reorganisation a try. However, I am not insisting on this, it is only a shoot in the dark. I thought it could be worth a try.  

Any ideas to speed up this “set role”?  

Regards
Wilhelm Klotz        

Von: Jonathan Lewis [mailto:jlewisoracle_at_gmail.com] Gesendet: Mittwoch, 28. April 2021 18:17 An: willyk_at_kbi-gmbh.de
Cc: ORACLE-L <oracle-l_at_freelists.org> Betreff: Re: fragmented sysauth$ table    

Without seeing a trace of an attempt to "set role" it's a pretty safe bet that Tim's analysis is correct.  

You might like to look at this blog note, though https://jonathanlewis.wordpress.com/2007/09/09/set-role/ It's dated 2007 but has updates for 18.3 saying the problems get worse, and an update in Feb 2020 that reports a known bug, fixed in 20.1 with backports to 12, 18, and 19. There's also a comment connecting the problem with non-default roles.    

Regards
Jonathan Lewis    

On Wed, 28 Apr 2021 at 09:25, Willy Klotz <willyk_at_kbi-gmbh.de <mailto:willyk_at_kbi-gmbh.de> > 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




application/octet-stream attachment: tkprof.out