Impact of Direct Grants
While monitoring a critical database there was a large concurrency spike that resulted in an application performance impact. Investigation revealed that a direct GRANT performed on a single table was the root cause. Could a simple GRANT cause a performance impact? One would not think so, at least until we analyze what is actually going on.
The concurrency spike pictured above was Library Cache Locks. The direct GRANT caused all of the dependent SQL statements in the library cache to be invalidated. Therefore, every statement had to be parsed which ultimately caused the contention.
What do you mean by Grants?
In the Oracle database, permission is given on database objects via the GRANT command (GRANT <permission> ON <object> TO <user|role>). The permissions can be granted directly to a user or to a role (and the role granted to users). In this example we are talking about a grant performed directly to a user. Grants performed to a role will not have the same impact.
Illustration
The following is an example of what happened during this spike that you can use in a lab environment to investigate for yourself using the HR example schema provided by Oracle. The following SQL statement is executed (SQL ID 3jn29k7301m49). After execution, details are pulled from V$SQL.
Before performing the direct GRANT, pull information from DBA_OBJECTS on the HR.EMPLOYEES table. Notice the LAST_DDL_TIME before the GRANT.
Now perform a GRANT on the HR.EMPLOYEES table.
Execute the query against DBA_OBJECTS again. Notice the LAST_DDL_TIME after the grant has changed to the time of the grant. The GRANT modifies the LAST_DDL_TIME in DBA_OBJECTS (actually underlying tables) which triggers an invalidation of all SQL statements (including PL/SQL objects) in the Library Cache that are based on the impacted table. This invalidation then causes each cached SQL statement (and PL/SQL object) to be parsed during the next execution. It is the parsing that creates the bottleneck. In the use case pictured above, it was large PL/SQL objects used for Virtual Private Database (VPD).
Looking back at V$SQL data shows that indeed the statement has been invalided and parsed.
Summary
Do not take GRANTs for granted. They are not harmless actions when they are direct grants. Performing direct GRANTs on a busy database should be done with extreme caution. When possible and as a standard, perform grants to roles. This will avoid invalidations.
If you have strange spikes, take a look if a GRANT performed at the very beginning of the spike caused a snowball effect.
______
Brian is an experienced Oracle Database Engineer with a strong ability to translate business goals and needs into technical solutions. With over 25 years in the financial industry, Brian has supported Oracle databases since version 7. In addition, he has developed many security and compliance solutions within the financial industry to resolve data privacy issues, global support staffing restrictions, PCI DSS, and other regulatory concerns. With a proven history supporting missions critical online banking databases through performance tuning, performance pattern analysis, zero downtime migrations and upgrades, and high availability architecture designs and implementations. Many solutions leveraged Oracle engineered systems, GoldenGate, SharePlex, Data Guard, Oracle RAC, Database Vault, and various performance metric analysis techniques.
With a passion for being business centric in the support of databases, Brian has implemented global Oracle Enterprise Manager based solutions, advanced automation, database self-service, and proactive IT data mining and modeling solutions.