Re: fragmented sysauth$ table

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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 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