AW: fragmented sysauth$ table

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.  

Attached the complete tkprof output, and my “testcase”. Maybe this can help.  

Regards
Wilhelm Klotz        

Von: Jonathan Lewis [mailto:jlewisoracle_at_gmail.com] Gesendet: Donnerstag, 29. April 2021 11:48 An: Willy Klotz <willyk_at_kbi-gmbh.de>
Cc: ORACLE-L <oracle-l_at_freelists.org> Betreff: Re: fragmented sysauth$ table    

Can you supply the SQL IDs for the 4 queries that (I assume) will have accessed sysauth$ in your trace file. In my 19.3 they are:  

SQL ID: gvswaxnymjk0c Plan Hash: 915465967
SQL ID: 444fa6tbwr7pr Plan Hash: 392164284
SQL ID: gcqnk1731gvva Plan Hash: 2797497565 ***
SQL ID: 5dqz0hqtp9fru Plan Hash: 1227530427
 

The bug number I referenced in my blog note refers to the SQL_ID I've flagged with *** and the fix is first available 19.9. Its access path include a full tablescan, but the SQL you've supplied isn't in my set - so I can't tell if this is the one that's supposed to have been fixed (with a backport since you're on 19.8) or whether it's another of the statements that has been hacked. I suspect the latter.  

Either way, I think you need to contact Oracle Support, the way this query is hinted is appalling; if a query needs hints it needs a full set of hints to be stable; and that applies even more to the data dictionary than to anything else.    

Regards
Jonathan Lewis    

On Thu, 29 Apr 2021 at 10:24, Willy Klotz <willyk_at_kbi-gmbh.de <mailto:willyk_at_kbi-gmbh.de> > wrote: Hi all, and thank you for your answers.  

You are correct that it is not the best idea to tamper with the DD first. Maybe I should explain the actual cause of the problem.  

Enclosed a short tkprof output. As you can see, this test is doing one single “set role” statement, which in turn does a “select .. from sysauth$”, which runs for 26 seconds. The database in this test is idle otherwise, we have plenty of memory and cpu available.  

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




application/octet-stream attachment: testcase.sql



application/octet-stream attachment: tkprof-1.out