MIME-Version: 1.0
References: <!&!AAAAAAAAAAAYAAAAAAAAAJxRhRLvfEhBnlOLJd92a2XCgAAAEAAAAMySQHDPo19ImOKPJlpK8owBAAAAAA==@kbi-gmbh.de>
 <CAGtsp8ntbsnqbTFe-Mra7qkoFK8zeysin-dnMhStX7mvW6qvbA@mail.gmail.com>
 <!&!AAAAAAAAAAAYAAAAAAAAAJxRhRLvfEhBnlOLJd92a2XCgAAAEAAAAOsutvvuySpLgukjkxjS8lcBAAAAAA==@kbi-gmbh.de>
 <CAGtsp8=qgQYmKPdv4wjBE8A=Gv5otFDES8hNGZ1n+tzqJpB0cA@mail.gmail.com> <!&!AAAAAAAAAAAYAAAAAAAAAJxRhRLvfEhBnlOLJd92a2XCgAAAEAAAALv3/GqBnNpGltukP6vjVOcBAAAAAA==@kbi-gmbh.de>
In-Reply-To: <!&!AAAAAAAAAAAYAAAAAAAAAJxRhRLvfEhBnlOLJd92a2XCgAAAEAAAALv3/GqBnNpGltukP6vjVOcBAAAAAA==@kbi-gmbh.de>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Thu, 29 Apr 2021 15:56:07 +0100
Message-ID: <CAGtsp8=Z5JK5jsNQCAE0WLrxEAkUBkQgxZbdrui8QiGWJbPoXg@mail.gmail.com>
Subject: Re: fragmented sysauth$ table
To: Willy Klotz <willyk@kbi-gmbh.de>
Cc: ORACLE-L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000d7caa205c11db29b"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--000000000000d7caa205c11db29b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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@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 =E2=80=
=9Cset
> role XXXX=E2=80=9D statement (this test is run with sqlplus). I assume th=
at =E2=80=9Cset
> role=E2=80=9D in turn does the =E2=80=9Cselect .. from sysauth$=E2=80=9D,=
 which runs for 26
> seconds.
>
>
>

--000000000000d7caa205c11db29b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div>I read your email, take an=
other look at what I said in mine:</div><div><br></div><div dir=3D"ltr">&gt=
;<br>
</div><div dir=3D"ltr">&gt; the 4 queries that (I assume) will have accesse=
d sysauth$ in your trace file.

</div><div>&gt;</div><div><br></div><div>The 4 queries that were in the tra=
ce file you generated (and the tkprof output you supplied) had the followin=
g SQL IDs</div><div><span style=3D"font-family:monospace">SQL ID: f8pj3d8v9=
wz1b Plan Hash: 3253291735<br>SQL ID: cjjjhss73p058 Plan Hash: 3316561731<b=
r>SQL ID: 9jbkynurzb23x Plan Hash: 2486390249<br>SQL ID: 5dqz0hqtp9fru Plan=
 Hash: 1435697754</span></div><div><br></div><div>None of them match the 4 =
that I listed from 19.3 in my previous post. Was this an instance running w=
ith the patch from 20.1 or an unpatched 19.8 ?</div><div>It is possible to =
apply an SQL Patch to sys-recursive SQL, but it&#39;s not really desirable,=
 but you could do some testing to see if you can find a better path for the=
 query.</div><div><br></div><div>I assume the table stats reasonable accura=
te and the problem is related to the number of privileges per role or the n=
umber of roles, or the number of roles per grantee. In short, something abo=
ut the pattern of roles and privileges is probably not one that Oracle expe=
cts, so if you&#39;re unlucky the statement may be untunable, if you&#39;re=
 lucky there may be a statistical reason why the optimizer is choosing a ba=
d path.</div><div><br></div><div>I think I would experiment with three appr=
oaches - cutting the first part of the UNION out of the statement and setti=
ng up a framework with a couple of numeric bind variables.</div><div>a) wha=
t happens if you force a nested loop instead of a hash join</div><div>b) wh=
at happens if you block unnesting (add no_unnest hint to the connect-by sub=
query)</div><div>c) is there much change if you try the other connect-by hi=
nts in the subquery.</div><div><br></div><div>You might also check the stat=
e of the i_sysauth1 index - the large number CU gets might indicate that it=
s much larger than it needs to be and is causing a lot of excess work on th=
e connect by pump with index full scan (that&#39;s a long-shot, though).<br=
></div><div><br></div><div>Regards</div><div>Jonathan Lewis</div><div><br><=
/div><br><div><br></div><div><br></div><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Thu, 29 Apr 2021 at 13:42, Willy Klotz &lt=
;<a href=3D"mailto:willyk@kbi-gmbh.de" target=3D"_blank">willyk@kbi-gmbh.de=
</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div lang=3D"DE"><div><p class=3D"MsoNormal"><span style=3D"font-size:11pt;=
font-family:&quot;Calibri&quot;,sans-serif;color:rgb(31,73,125)">Hi Jonatha=
n,<u></u><u></u></span></p><p class=3D"MsoNormal"><span style=3D"font-size:=
11pt;font-family:&quot;Calibri&quot;,sans-serif;color:rgb(31,73,125)"><u></=
u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span style=3D"font-size:1=
1pt;font-family:&quot;Calibri&quot;,sans-serif;color:rgb(31,73,125)" lang=
=3D"EN-GB">there are no queries in this test on the sysauth$ table.<u></u><=
u></u></span></p><p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-=
family:&quot;Calibri&quot;,sans-serif;color:rgb(31,73,125)" lang=3D"EN-GB">=
<u></u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span style=3D"font-s=
ize:11pt;font-family:&quot;Calibri&quot;,sans-serif;color:rgb(31,73,125)" l=
ang=3D"EN-GB">As I said: the only thing which is done in this test is </spa=
n><span style=3D"font-size:11pt;font-family:&quot;Calibri&quot;,sans-serif;=
color:rgb(31,73,125)" lang=3D"EN-GB">one single =E2=80=9Cset role XXXX=E2=
=80=9D statement (this test is run with sqlplus). I assume that =E2=80=9Cse=
t role=E2=80=9D in turn does the =E2=80=9Cselect .. from sysauth$=E2=80=9D,=
 which runs for 26 seconds. <u></u><u></u></span></p><p class=3D"MsoNormal"=
><span style=3D"font-size:11pt;font-family:&quot;Calibri&quot;,sans-serif;c=
olor:rgb(31,73,125)" lang=3D"EN-GB">=C2=A0</span></p></div></div></blockquo=
te></div></div>

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