Re: fragmented sysauth$ table

I read your email, take another look at what I said in mine:

>
> the 4 queries that (I assume) will have accessed sysauth$ in your trace
file.
>

The 4 queries that were in the trace file you generated (and the tkprof output you supplied) had the following SQL IDs

SQL ID: f8pj3d8v9wz1b Plan Hash: 3253291735
SQL ID: cjjjhss73p058 Plan Hash: 3316561731
SQL ID: 9jbkynurzb23x Plan Hash: 2486390249
SQL ID: 5dqz0hqtp9fru Plan Hash: 1435697754

None of them match the 4 that I listed from 19.3 in my previous post. Was this an instance running with the patch from 20.1 or an unpatched 19.8 ? It is possible to apply an SQL Patch to sys-recursive SQL, but it's not really desirable, but you could do some testing to see if you can find a better path for the query.

I assume the table stats reasonable accurate and the problem is related to the number of privileges per role or the number of roles, or the number of roles per grantee. In short, something about the pattern of roles and privileges is probably not one that Oracle expects, so if you're unlucky the statement may be untunable, if you're lucky there may be a statistical reason why the optimizer is choosing a bad path.

I think I would experiment with three approaches - cutting the first part of the UNION out of the statement and setting up a framework with a couple of numeric bind variables.
a) what happens if you force a nested loop instead of a hash join b) what happens if you block unnesting (add no_unnest hint to the connect-by subquery)
c) is there much change if you try the other connect-by hints in the subquery.

You might also check the state of the i_sysauth1 index - the large number CU gets might indicate that its much larger than it needs to be and is causing a lot of excess work on the connect by pump with index full scan (that's a long-shot, though).

Regards
Jonathan Lewis

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

> Hi Jonathan,
>
>
>
> there are no queries in this test on the sysauth$ table.
>
>
>
> As I said: the only thing which is done in this test is one single “set
> role XXXX” statement (this test is run with sqlplus). I assume that “set
> role” in turn does the “select .. from sysauth$”, which runs for 26
> seconds.
>
>
>

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