Re: fragmented sysauth$ table

Isn’t this bug 29796335? It’s fixed in 19.9.

Not sure what the bug fix entails, but I imagine there’s no reason for this query to fire for set role.

Thanks,
Andy

On Thu, 29 Apr 2021 at 10:24, Willy Klotz <willyk_at_kbi-gmbh.de> wrote:

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