RE: fragmented sysauth$ table

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <jlewisoracle@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Apr 2021 15:37:23 -0400

I completely agree with your analysis. 

 

Which brings to mind another possible work-around. Presuming the large numbers 
of rows in the table means specified index use on that particular table should 
be avoided, it *may* be useful to avoid the hint being operative by dropping 
that index and replacing it with the same index specification but with a 
different index name.

 

If and when a useful patch is delivered, of course the index would be recreated 
back to its official name.

 

Your emp and dept comment harkens also to sql.bsq comments that “this should be 
plenty for 25 columns” regarding cluster sizes.

 

When e-biz came out I laughed and I laughed. Since the databases come 
predelivered these days, it’s even more of a high wire act to substitute 
large.bql. Sigh.

 

All the best,

 

mwf

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Thursday, April 29, 2021 1:50 PM
To: ORACLE-L
Subject: 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@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 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@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. 

 

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2021