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@xxxxxxxxx] ;
Gesendet: Mittwoch, 28. April 2021 18:17
An: willyk@xxxxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx
<mailto:willyk@xxxxxxxxxxx> > 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
Attachment:
tkprof.out
Description: Binary data
