Forum Stats

  • 3,826,752 Users
  • 2,260,703 Discussions
  • 7,897,071 Comments

Discussions

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

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)

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Is there a question here?

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

  • Hi,

    This is question-

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

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    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.

  • 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
    EdStevens Member Posts: 28,778 Gold Crown

    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.