showstats command performance slow with security enabled

[Paul Low]

Showstats is taking 5 times longer to execute when security is enabled.

Original issue reported by Gary:

In the overnight run testing baseline git150410 (before turning on security), the group of 17 showstats commands required 5 minutes 44 seconds to complete. After turning on security, the same group of showstats commands now take 29 minutes 18 seconds to complete…an increase of over 5-fold.

Reply from Roberta:

Basically, when showstat starts, we verify that the user has privs. If so, then no additional privilege checks are performed.
When we verify privileges, we first verify that the user has the MANAGE_STATISTICS component privilege. This call reads two tables, one to get the component privilege information and one to get the list of roles the user belongs to. For showstats, we also check for SHOW component privilege, this adds two more I/Os. We then check for SELECT privilege. Select privilege information is cached, component privilege information is not.
I assume these extra I/O’s could be causing performance issues.

I could reorder the checks to first verify that the user has SELECT priv and then component privs. I should be doing this anyway.
I could also add a CQD to not check for component privilege to see if this is the cause. Let me play a bit with the code and see if it makes any difference.

Update:

I have run some tests. It takes about 1 second to perform showstats as DB__ROOT.
It takes about 5 seconds to perform showstats as any other user.
This matches what Gary is seeing.

I removed the code to check for authorized user both in show statistics code and in the mainline code.
However, I am still seeing the longer times by non DB__ROOT users. This makes no sense since all authorization checks have been removed.

Found the issue:

The degradation is occurring while we are gathering privileges for the histogram tables.

Histogram tables are considered system metadata and their definitions are hardcoded. However, there are several attributes of these tables that cannot be hard coded such as object owner, object UID, and privilege information.
Today there is a special path to retrieve schema/object owner and object UID, and cache it. However privilege information is not considered. Therefore, I/Os are performed at compilation time for metadata objects instead of using cached information.
The fix is to add privilege information to this special path. Therefore, privileges will only be gathered once and used multiple times for metadata objects just like user objects.
This change should improve accesses to all metadata tables including repository tables when security is enabled.
As part of this fix, I will also hardcode definitions of privilege manager metadata which will avoid I/O when accessing these tables.

[Roberta Marton (roberta-marton)]

The cause of the problem was extra privilege checks made every time histogram information was gathered. These extra
checks were performed for both UPDATE STATISTICS and SHOWSTATS. SHOWSTATS is such a fast command, the checks
made more of a difference.

With this change, performance for SHOWSTATS improved noticeably both when authorization is and is not enabled.
SHOWSTATS times with and without authorization are the same.

Several changes were made to improve performance:

Performance optimization:

If the current user is the object owner, then default the privilege bitmap
to object Owner values - no need to call PrivMgr to get privileges

Caching optimizations:

1 - We are now caching privmgr metadata tables in compiler cache when the compiler
 context is instantiated. This avoids a metadata lookup for these tables.

2 - Removed extraneous re-compilations of HISTOGRAM tables:

 Today, update statistics and showstats are reloading NATable entries for HISTOGRAM tables on
  every access. This is because the parserflag ALLOW_SPECIALTABLETYPE is turned on. Changed
  code to not set ALLOW_SPECIALTABLETYPE and ALLOW_PHONYCHARACTERS parserflags by
  default. Individual statements are setting these flags as needed.