Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 2316E10032A714 for ; Thu, 29 Apr 2021 21:38:14 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 6282E47479; Thu, 29 Apr 2021 19:38:13 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 34C284090B; Thu, 29 Apr 2021 19:38:13 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1619725093; bh=S02eRYe+TwDn3pg4gNco6MYfK+SeAn6aWGPKZA98870=; h=From:Sender:Sender:From; b=ESr0bJkWV0p5GP/dHQrW1LGPcgatZcvCVnb+X097ZySnMdDpGXkxO/ArzWXxzJbOp hNnN6n7aJBCUwJnFp+RmghcM1EFewAIyL/eGiWIryma0w32azSlFdMM3QQZa/4K5sZ vTlq7IvXvpfYhgVtRGmSTvFfdxPyHSN+hI/u8SAo= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id nb-pq4SvoFXO; Thu, 29 Apr 2021 19:38:13 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 264C040905; Thu, 29 Apr 2021 19:38:07 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1619725089; bh=S02eRYe+TwDn3pg4gNco6MYfK+SeAn6aWGPKZA98870=; h=From:Sender:Sender:From; b=D9MnrRNAWmaTOZUSPUJHeIlVZ3nxFtu5ul6qo16QINpkKq/ZcOi8JE9a/PheZOrd0 tZ0OUWkZu+Gn3SkNatuiEpd3g+4Lu7nEyUWkWVRcWEw1YPtM1Jd6XjNZ58EduwWaW+ xv9Q0WYmnM3M6XJPCekLtTPukbglkqe180qH/zcg= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Apr 2021 19:38:05 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 5163B3FFCC for ; Thu, 29 Apr 2021 19:38:05 +0000 (UTC) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id mvv5qM11zFMr for ; Thu, 29 Apr 2021 19:38:05 +0000 (UTC) Received: from gw2.tidalhosting.net (gateway.tidalhosting.net [155.130.128.124]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 16E943FF4E for ; Thu, 29 Apr 2021 19:38:04 +0000 (UTC) Received: from mrtr.tidalhosting.net (UnknownHost [10.0.150.3]) by gw2.tidalhosting.net with SMTP (version=TLS\Tls12 cipher=Aes256 bits=256); Thu, 29 Apr 2021 15:37:55 -0400 Received: from mx1.tidalhosting.net (unknown [10.0.100.9]) by mrtr.tidalhosting.net (Postfix) with ESMTPS id E5BD1304B94C; Thu, 29 Apr 2021 15:37:50 -0400 (EDT) Received: from mwf4500 (c-73-238-99-41.hsd1.ma.comcast.net [73.238.99.41]) by mx1.tidalhosting.net with SMTP (version=Tls cipher=Aes256 bits=256); Thu, 29 Apr 2021 15:37:31 -0400 From: "Mark W. Farnham" To: , "'ORACLE-L'" References: <337d01d73d1c$e1f509b0$a5df1d10$@rsiz.com> In-Reply-To: Subject: RE: fragmented sysauth$ table Date: Thu, 29 Apr 2021 15:37:23 -0400 Message-ID: <33c501d73d2f$14262380$3c726a80$@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_33C6_01D73D0D.8D1A01C0" Content-Language: en-us X-Exim-Id: 33c501d73d2f$14262380$3c726a80$ X-archive-position: 79754 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l ------=_NextPart_000_33C6_01D73D0D.8D1A01C0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I completely agree with your analysis.=20 =20 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. =20 If and when a useful patch is delivered, of course the index would be = recreated back to its official name. =20 Your emp and dept comment harkens also to sql.bsq comments that = =E2=80=9Cthis should be plenty for 25 columns=E2=80=9D regarding cluster = sizes. =20 When e-biz came out I laughed and I laughed. Since the databases come = predelivered these days, it=E2=80=99s even more of a high wire act to = substitute large.bql. Sigh. =20 All the best, =20 mwf =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, April 29, 2021 1:50 PM To: ORACLE-L Subject: Re: fragmented sysauth$ table =20 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 ;) =20 =20 =20 Regards Jonathan Lewis =20 =20 On Thu, 29 Apr 2021 at 18:27, Mark W. Farnham wrote: Just to save everyone unpacking the trace, the rounded elapsed time = reported is 26 seconds and this bit accounts for over 26 seconds: =20 I highlighted yellow the ones I thought were interesting and one in = green that you might be able to trivially do something about. =20 (If some pathology has gotten your i_sysauth1 into a horrible state and=20 INDEX FULL SCAN I_SYSAUTH1 (cr=3D8338 pr=3D8305 pw=3D0 time=3D928512 us = starts=3D1 cost=3D8640 size=3D340 card=3D34)(object id 147) =20 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. =20 You can=E2=80=99t 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=E2=80=99m moderately impressed by 1.5 million rows of privilege.=20 =20 I *hope* this makes it through with the no line wrapping and indentation = preserved. =20 mwf =20 =20 SQL ID: f8pj3d8v9wz1b Plan Hash: 3253291735 =20 select /*+ index(d i_sysauth1) */ d.privilege#=20 from sys.sysauth$ d=20 where d.privilege# in=20 (select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ = grantee#=20 from sys.sysauth$=20 start with privilege#=3D:1=20 connect by prior grantee#=3Dprivilege#=20 group by grantee# )=20 and d.grantee# =3D :2 =20 union =20 select /*+ index(sysauth$ i_sysauth1) */ privilege#=20 from sys.sysauth$ =20 where grantee#=3D:2 and privilege#=3D:1 =20 =20 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 =20 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 =20 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- = --------------------------------------------------- 1 1 1 SORT UNIQUE (cr=3D24936 pr=3D17627 = pw=3D9322 time=3D26078964 us starts=3D1 cost=3D26756 size=3D286 = card=3D13) 1 1 1 UNION-ALL (cr=3D24936 pr=3D17627 = pw=3D9322 time=3D26078922 us starts=3D1) 0 0 0 HASH JOIN (cr=3D24933 pr=3D17627 = pw=3D9322 time=3D26078852 us starts=3D1 cost=3D26753 size=3D276 = card=3D12) 60 60 60 JOIN FILTER CREATE :BF0000 (cr=3D3 = pr=3D0 pw=3D0 time=3D128 us starts=3D1 cost=3D3 size=3D140 card=3D14) 60 60 60 INDEX RANGE SCAN I_SYSAUTH1 = (cr=3D3 pr=3D0 pw=3D0 time=3D73 us starts=3D1 cost=3D3 size=3D140 = card=3D14)(object id 147) 100 100 100 VIEW VW_NSO_1 (cr=3D24930 = pr=3D17627 pw=3D9322 time=3D26077987 us starts=3D1 cost=3D26750 = size=3D1662440 card=3D127880) 100 100 100 SORT GROUP BY (cr=3D24930 = pr=3D17627 pw=3D9322 time=3D26077972 us starts=3D1 cost=3D26750 = size=3D2301840 card=3D127880) 1380 1380 1380 JOIN FILTER USE :BF0000 = (cr=3D24930 pr=3D17627 pw=3D9322 time=3D26077061 us starts=3D1) 1546820 1546820 1546820 CONNECT BY WITH FILTERING = (UNIQUE) (cr=3D24930 pr=3D17627 pw=3D9322 time=3D25848983 us starts=3D1) 34 34 34 INDEX FULL SCAN I_SYSAUTH1 = (cr=3D8338 pr=3D8305 pw=3D0 time=3D928512 us starts=3D1 cost=3D8640 = size=3D340 card=3D34)(object id 147) 1546786 1546786 1546786 HASH JOIN (cr=3D16592 pr=3D195 = pw=3D195 time=3D2150822 us starts=3D2 cost=3D17284 size=3D3984428 = card=3D173236) 114245 114245 114245 NESTED LOOPS (cr=3D0 pr=3D0 = pw=3D0 time=3D47915 us starts=3D2 cost=3D17284 size=3D3984428 = card=3D173236) 114245 114245 114245 STATISTICS COLLECTOR (cr=3D0 = pr=3D0 pw=3D0 time=3D33041 us starts=3D2) 114245 114245 114245 CONNECT BY PUMP (cr=3D0 = pr=3D0 pw=3D0 time=3D17928 us starts=3D2) 0 0 0 INDEX FULL SCAN I_SYSAUTH1 = (cr=3D0 pr=3D0 pw=3D0 time=3D0 us starts=3D0 cost=3D8634 size=3D50950 = card=3D5095)(object id 147) 3637952 3637952 3637952 INDEX FULL SCAN I_SYSAUTH1 = (cr=3D16592 pr=3D0 pw=3D0 time=3D720023 us starts=3D2 cost=3D8634 = size=3D18189760 card=3D1818976)(object id 147) 1 1 1 INDEX UNIQUE SCAN I_SYSAUTH1 (cr=3D3 = pr=3D0 pw=3D0 time=3D45 us starts=3D1 cost=3D2 size=3D10 = card=3D1)(object id 147) =20 =20 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 *************************************************************************= ******* =20 =20 From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@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 =20 =20 I read your email, take another look at what I said in mine: =20 >=20 > the 4 queries that (I assume) will have accessed sysauth$ in your = trace file.=20 >=20 =20 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 =20 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. =20 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. =20 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. =20 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). =20 Regards Jonathan Lewis =20 =20 =20 =20 On Thu, 29 Apr 2021 at 13:42, Willy Klotz wrote: Hi Jonathan, =20 there are no queries in this test on the sysauth$ table. =20 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 that =E2=80=9Cset role=E2=80=9D in turn does the = =E2=80=9Cselect .. from sysauth$=E2=80=9D, which runs for 26 seconds.=20 =20 ------=_NextPart_000_33C6_01D73D0D.8D1A01C0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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 = =E2=80=9Cthis should be plenty for 25 columns=E2=80=9D regarding cluster = sizes.

 

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

 

All the best,

 

mwf

 

From:= = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = 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@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=3D8338 pr=3D8305 = pw=3D0 time=3D928512 us starts=3D1 cost=3D8640 size=3D340 = card=3D34)(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=E2=80=99t 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=E2=80=99m 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#=3D:1 =

      connect by prior = grantee#=3Dprivilege#

      group by = grantee#

   ) =

   and d.grantee# =3D :2  =

   union 

   select /*+ index(sysauth$ i_sysauth1) */ = privilege#

      from sys.sysauth$  =

      where grantee#=3D:2 and = privilege#=3D: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=3D24936 pr=3D17627 pw=3D9322 time=3D26078964 us starts=3D1 = cost=3D26756 size=3D286 card=3D13)

         = 1          = 1          1   = UNION-ALL  (cr=3D24936 pr=3D17627 pw=3D9322 time=3D26078922 us = starts=3D1)

         = 0          = 0          = 0    HASH JOIN  (cr=3D24933 pr=3D17627 pw=3D9322 = time=3D26078852 us starts=3D1 cost=3D26753 size=3D276 = card=3D12)

        = 60         = 60         = 60     JOIN FILTER CREATE :BF0000 (cr=3D3 pr=3D0 = pw=3D0 time=3D128 us starts=3D1 cost=3D3 size=3D140 = card=3D14)

        = 60         = 60         = 60      INDEX RANGE SCAN I_SYSAUTH1 (cr=3D3 = pr=3D0 pw=3D0 time=3D73 us starts=3D1 cost=3D3 size=3D140 = card=3D14)(object id 147)

       = 100        = 100        = 100     VIEW  VW_NSO_1 (cr=3D24930 pr=3D17627 = pw=3D9322 time=3D26077987 us starts=3D1 cost=3D26750 size=3D1662440 = card=3D127880)

       = 100        = 100        = 100      SORT GROUP BY (cr=3D24930 pr=3D17627 = pw=3D9322 time=3D26077972 us starts=3D1 cost=3D26750 size=3D2301840 = card=3D127880)

      = 1380       = 1380       = 1380       JOIN FILTER USE :BF0000 = (cr=3D24930 pr=3D17627 pw=3D9322 time=3D26077061 us = starts=3D1)

   = 1546820    1546820    = 1546820        CONNECT BY WITH = FILTERING (UNIQUE) (cr=3D24930 pr=3D17627 pw=3D9322 time=3D25848983 us = starts=3D1)

        = 34         = 34         = 34         INDEX FULL SCAN I_SYSAUTH1 (cr=3D8338 = pr=3D8305 pw=3D0 time=3D928512 us starts=3D1 cost=3D8640 size=3D340 = card=3D34)(object id 147)

   1546786    = 1546786    = 1546786         HASH JOIN  = (cr=3D16592 pr=3D195 pw=3D195 time=3D2150822 us starts=3D2 = cost=3D17284 size=3D3984428 card=3D173236)

    = 114245     114245     = 114245          NESTED = LOOPS  (cr=3D0 pr=3D0 pw=3D0 time=3D47915 us starts=3D2 = cost=3D17284 size=3D3984428 card=3D173236)

    = 114245     114245     = 114245           = STATISTICS COLLECTOR  (cr=3D0 pr=3D0 pw=3D0 time=3D33041 us = starts=3D2)

    = 114245     114245     = 114245            = CONNECT BY PUMP  (cr=3D0 pr=3D0 pw=3D0 time=3D17928 us = starts=3D2)

         = 0          = 0          = 0           INDEX FULL = SCAN I_SYSAUTH1 (cr=3D0 pr=3D0 pw=3D0 time=3D0 us starts=3D0 cost=3D8634 = size=3D50950 card=3D5095)(object id 147)

   = 3637952    3637952    = 3637952          INDEX FULL = SCAN I_SYSAUTH1 (cr=3D16592 pr=3D0 pw=3D0 time=3D720023 us starts=3D2 = cost=3D8634 size=3D18189760 card=3D1818976)(object id = 147)

         = 1          = 1          = 1    INDEX UNIQUE SCAN I_SYSAUTH1 (cr=3D3 pr=3D0 pw=3D0 = time=3D45 us starts=3D1 cost=3D2 size=3D10 card=3D1)(object id = 147)

 

 

Elapsed times = include waiting on following events:

  Event waited = on            = ;            =      Times   Max. Wait  Total = Waited

  = ----------------------------------------   Waited  = ----------  ------------

  PGA memory = operation          &nbs= p;            = ;   272        = 0.00          = 0.00

  db file = parallel = read           &nb= sp;           &nbs= p;  76        = 0.04          = 0.56

  db file = sequential = read           &nb= sp;            = 53        = 0.00          = 0.01

  Disk file = operations = I/O           &nbs= p;            = 1        = 0.00        =   0.00

  direct path = write = temp           &nb= sp;           &nbs= p; 15        = 0.00          = 0.04

  direct path = read = temp           &nb= sp;           &nbs= p; 696        = 0.01          = 0.29

*******************************************************************= *************

 

 

From:= = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@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

 <= /o:p>

 <= /o:p>

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

 <= /o:p>

> 

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

> 

 <= /o:p>

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

 <= /o:p>

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.

 <= /o:p>

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.

 <= /o:p>

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.

 <= /o:p>

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).

 <= /o:p>

Regards=

Jonathan = Lewis

 <= /o:p>

 <= /o:p>

 <= /o:p>

 <= /o:p>

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 that =E2=80=9Cset role=E2=80=9D in turn does the = =E2=80=9Cselect .. from sysauth$=E2=80=9D, which runs for 26 seconds. =

 

------=_NextPart_000_33C6_01D73D0D.8D1A01C0-- -- http://www.freelists.org/webpage/oracle-l