Mark,
The reason for that index full scan is probably the hint in the subquery to
use it.
Unfortunately the subquery has been unnested and the only predicate
available is on privilege# when the index starts with grantee#.
This suggests to me that the programmer who put that hint in was assuming
something about the data that simply isn't true in this case (and 1.8M rows
in the table may be the critical difference, course - because who tests
anything on tables bigger than emp and dept ;)
Regards
Jonathan Lewis
On Thu, 29 Apr 2021 at 18:27, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Just to save everyone unpacking the trace, the rounded elapsed time
reported is 26 seconds and this bit accounts for over 26 seconds:
I highlighted yellow the ones I thought were interesting and one in green
that you might be able to trivially do something about.
(If some pathology has gotten your i_sysauth1 into a horrible state and
INDEX FULL SCAN I_SYSAUTH1 (cr=8338 pr=8305 pw=0 time=928512 us starts=1
cost=8640 size=340 card=34)(object id 147)
seems unreasonable for an index full scan for the amount of contents you
have, it is possible that rebuilding this index might be useful. It seems
like a low cost test. And there is another, even more expensive index full
scan of this index.
You can’t really do anything about their query code other than telling
them it is taking 26 seconds and showing them this trace (or just the bit I
yanked out). I just thought that grouping by and a non-all union might not
be the optimal ways to get a distinct list. I’m moderately impressed by 1.5
million rows of privilege.
I **hope** this makes it through with the no line wrapping and
indentation preserved.
mwf
SQL ID: f8pj3d8v9wz1b Plan Hash: 3253291735
select /*+ index(d i_sysauth1) */ d.privilege#
from
sys.sysauth$ d
where d.privilege# in
(select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ grantee#
from sys.sysauth$
start with privilege#=:1
connect by prior grantee#=privilege#
group by grantee#
)
and d.grantee# = :2
union
select /*+ index(sysauth$ i_sysauth1) */ privilege#
from sys.sysauth$
where grantee#=:2 and privilege#=:1
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 25.19 26.07 17627 24936
6306221 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 25.20 26.08 17627 24936
6306221 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1 1 1 SORT UNIQUE (cr=24936 pr=17627 pw=9322
time=26078964 us starts=1 cost=26756 size=286 card=13)
1 1 1 UNION-ALL (cr=24936 pr=17627 pw=9322
time=26078922 us starts=1)
0 0 0 HASH JOIN (cr=24933 pr=17627 pw=9322
time=26078852 us starts=1 cost=26753 size=276 card=12)
60 60 60 JOIN FILTER CREATE :BF0000 (cr=3 pr=0
pw=0 time=128 us starts=1 cost=3 size=140 card=14)
60 60 60 INDEX RANGE SCAN I_SYSAUTH1 (cr=3
pr=0 pw=0 time=73 us starts=1 cost=3 size=140 card=14)(object id 147)
100 100 100 VIEW VW_NSO_1 (cr=24930 pr=17627
pw=9322 time=26077987 us starts=1 cost=26750 size=1662440 card=127880)
100 100 100 SORT GROUP BY (cr=24930 pr=17627
pw=9322 time=26077972 us starts=1 cost=26750 size=2301840 card=127880)
1380 1380 1380 JOIN FILTER USE :BF0000 (cr=24930
pr=17627 pw=9322 time=26077061 us starts=1)
1546820 1546820 1546820 CONNECT BY WITH FILTERING (UNIQUE)
(cr=24930 pr=17627 pw=9322 time=25848983 us starts=1)
34 34 34 INDEX FULL SCAN I_SYSAUTH1
(cr=8338 pr=8305 pw=0 time=928512 us starts=1 cost=8640 size=340
card=34)(object id 147)
1546786 1546786 1546786 HASH JOIN (cr=16592 pr=195
pw=195 time=2150822 us starts=2 cost=17284 size=3984428 card=173236)
114245 114245 114245 NESTED LOOPS (cr=0 pr=0 pw=0
time=47915 us starts=2 cost=17284 size=3984428 card=173236)
114245 114245 114245 STATISTICS COLLECTOR (cr=0
pr=0 pw=0 time=33041 us starts=2)
114245 114245 114245 CONNECT BY PUMP (cr=0 pr=0
pw=0 time=17928 us starts=2)
0 0 0 INDEX FULL SCAN I_SYSAUTH1
(cr=0 pr=0 pw=0 time=0 us starts=0 cost=8634 size=50950 card=5095)(object
id 147)
3637952 3637952 3637952 INDEX FULL SCAN I_SYSAUTH1
(cr=16592 pr=0 pw=0 time=720023 us starts=2 cost=8634 size=18189760
card=1818976)(object id 147)
1 1 1 INDEX UNIQUE SCAN I_SYSAUTH1 (cr=3
pr=0 pw=0 time=45 us starts=1 cost=2 size=10 card=1)(object id 147)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
PGA memory operation 272 0.00
0.00
db file parallel read 76 0.04
0.56
db file sequential read 53 0.00
0.01
Disk file operations I/O 1 0.00
0.00
direct path write temp 15 0.00
0.04
direct path read temp 696 0.01
0.29
********************************************************************************
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Thursday, April 29, 2021 10:56 AM
*To:* Willy Klotz
*Cc:* ORACLE-L
*Subject:* 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 tracefile.
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@xxxxxxxxxxx> 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.
