I mean low performance because of high concurrency.

Is there a way to prevent DDL of locking the table ? Because we need to do DDL's on operational time, so is it posible that DML (select, insert, update, etc) are not locked during DDL(grant, revoke, synoymo) on same table ?

Hi,

Assuming your concurrency wait events are things like
cursor: pin S wait on X
library cache lock

you might hit a problem we also have intermittedly. Namely massive parsing by extensive changes to the dictionary and/or statistics.

One scenario was analyzing many tables (with stale statistics). In production this happens at night or weekends but in the testsystem it ran (unintentionally) during the tests and slowed everything down. It used NO_INVALIDATE=false so many statements had to be reparsed (sometimes again and again if they used many tables). We are considering Publish=FALSE + PUBLISH_PENDING_STATISTICS to reduce the impact.

Another was ALTER INDEX xxx MONITORING USAGE. We do it if the index is used but the monitoring startet more than a week ago to get fresh infos. Apparently this also triggers reparsing. Probably any ALTER statement will or it is specific because at least in 12.1 index usage comes from parsing not from runtime so if statements aren't reparsed we would see indexes unused that are still used. We hope for the new 12.2 technique when we upgrade.

Apart from moving/spreading the reasons around in time there were also a few specific statements whose complexity could be sensibly reduced to reduce the impact.

Still there remains Connors query : why do you need to do the grants/revokes at all (let alone during uptime)?

regards,

When an user is created in our system it triggers 50 grants more or less. We need to give him access to different tables (select, update, delete). The thing is that users are created all the time, cause it's a distributed administration.

Do DDL's always generate locks ? Can those locks be disabled ?

Is it better for performance to use roles instead ?