Re: fragmented sysauth$ table

Can you supply the SQL IDs for the 4 queries that (I assume) will have accessed sysauth$ in your trace file.
In my 19.3 they are:

SQL ID: gvswaxnymjk0c Plan Hash: 915465967
SQL ID: 444fa6tbwr7pr Plan Hash: 392164284
SQL ID: gcqnk1731gvva Plan Hash: 2797497565 ***
SQL ID: 5dqz0hqtp9fru Plan Hash: 1227530427

The bug number I referenced in my blog note refers to the SQL_ID I've flagged with *** and the fix is first available 19.9. Its access path include a full tablescan, but the SQL you've supplied isn't in my set - so I can't tell if this is the one that's supposed to have been fixed (with a backport since you're on 19.8) or whether it's another of the statements that has been hacked. I suspect the latter.

Either way, I think you need to contact Oracle Support, the way this query is hinted is appalling; if a query needs hints it needs a full set of hints to be stable; and that applies even more to the data dictionary than to anything else.

Regards
Jonathan Lewis

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

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