Controls the maximum percentage of a user’s temp memory that a hash object can pin.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
HASH_PINNABLE_CACHE_PERCENT controls the percentage of a user’s temp memory allocation that any one hash object can pin in memory. The default is 20%, but you should reduce this number to 10% if you are running complex queries, or increase this number to 50% if you have simple queries that need a single large hash object to run, such as a large IN subquery.
HASH_PINNABLE_CACHE_PERCENT is for use by primarily Technical Support. If you change the value of it, do so with extreme caution; first analyze the effect on a wide variety of queries.