how to reduce the high number of SELECTs on sysauth$ table

Parwez A-Oracle

High number of concurrent execution of the audit related query.appearance of these sysauth querys are a symptom of too much load on the database(12.2.0.1.0)

EdStevens

Is there a question here?

Or is this just a statement of "fact" without citing sources? And to what end?

Parwez A-Oracle

Hi,

This is question-

how to reduce the high number of SELECTs on sysauth$ table?

EdStevens

how to reduce the high number of SELECTs on sysauth$ table?

At what point do the number of queries become "high", and in need of being reduced?

What actual analysis have you done that definitively, or even strongly suggests, 'high number of SELECTs on sysauth$ table' is the root cause of an actual, demonstrated business problem?

I think you are asking an XY question.

Parwez A-Oracle

The database became unresponsive when over 2000 sessions were concurrently executing the below system query

--------------------------------------------------------------------------------

select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand

(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior p

rivilege# and privilege#>0 start with grantee#=:1 and privilege#>0

number of queries become "high", and in need of being reduced? yes , its business problem

EdStevens

So it was executing a query. Readers don't block writers, and writers don't block readers. And readers certainly do not block other readers.

You need to gather a trace and see exactly what wait event the database is waiting on.